EINDEUTIG mal etwas anderes 7

Eine dynamische Array-Funktion in Excel 365 bietet interessante Möglichkeiten
 

Die Excel-Funktion, die ich heute vorstelle, gehört zur Kategorie der dynamischen Array-Funktionen, welche mit Microsoft 365 / Office 365 neu eingeführt wurden: EINDEUTIG.

Wie der Name schon vermuten lässt, kann man damit eindeutige Werte ermitteln. Hört sich erst einmal unspektakulär an, aber die Funktion hat es in sich und bietet praktische Möglichkeiten, die bisher nur umständlich mit komplizierten Formeln umsetzbar waren – oder eben überhaupt nicht.

Wofür man die EINDEUTIG-Funktion in der Praxis nutzen kann, zeigt der heutige Artikel.

Und so geht’s:

EINDEUTIG für Einsteiger

Wie eingangs erwähnt, ist Microsoft 365 zwingende Voraussetzung. Wenn du eine andere Excel-Version als Excel 365 einsetzt, steht dir diese Funktion leider nicht zur Verfügung (nein, auch nicht in Excel 2019).

Einen ersten Überblick zu diesen neuen dynamischen Arrayfunktionen kannst du in dieser zweiteiligen Artikelserie nachlesen: Ein Blick in die Zukunft: Dynamische Arrayfunktionen und Dynamische Arrayfunktionen (Teil 2)

Für ein einführendes Beispiel habe ich eine Liste mit beliebigen Städtenamen, die teilweise mehrfach vorkommen (die Beispieldatei kannst du dir hier herunterladen). Damit mit man die doppelten Einträge besser erkennt, habe ich sie farblich markiert. Mit der EINDEUTIG-Funktion kannst du daraus eine Liste erzeugen, in der jeder Wert nur noch ein einziges Mal enthalten ist:

=EINDEUTIG(A2:A30)

Die Städteliste: Original und bereinigt.

Die Städteliste: Original und bereinigt.

Wie man sieht, wird jeder in der Originalliste mehrfach vorkommende Wert nur noch einmal aufgeführt.

Wenn man der Funktion aber noch einen zusätzlichen Parameter übergibt, erhält man die Liste der Städte, die exakt ein einziges Mal in der Originalliste enthalten sind.

=EINDEUTIG(A2:A30;;WAHR)

Die EINDEUTIG-Funktion nur für einmalige Ergebnisse

Die EINDEUTIG-Funktion nur für einmalige Ergebnisse

Das ist ein kleiner aber feiner Unterschied. Denn die Städte München, Ortenburg und Kusel tauchen in dieser neuen Liste überhaupt nicht auf. So etwas könnte zum Beispiel in der Praxis interessant sein für die Marketingabteilung beim Auswerten von Online-Kampagnen. Damit ließen sich Kunden für eine Verkaufsaktion identifizieren, die nur ein einziges Mal in unserem Onlineshop gekauft haben, danach aber nie wieder.

EINDEUTIG für Fortgeschrittene

Im Einführungsbeispiel habe ich mit einer einfachen einspaltigen Liste gearbeitet. Aber selbstverständlich kann man die Funktion auch auf mehrspaltige Tabellen loslassen. Im folgenden Beispiel habe ich eine kleine Auftragsliste, in die sich zwei Duplikate eingeschlichen haben. So etwas kommt gerne mal vor, wenn man mehrere Zeiträume in seinem ERP-System abfragt und dabei die selektierten Tage nicht sauber abgrenzt.

Kein Problem für die EINDEUTIG-Funktion. Meine Auftragsliste ist dabei eine formatierte Tabelle mit dem Namen „Auftragsliste“.

=EINDEUTIG(Auftragsliste)

Dublettenbereinigung mit EINDEUTIG

Dublettenbereinigung mit EINDEUTIG

Wie bei allen dynamischen Array-Funktionen wird die Funktion nur in die erste Zelle eingegeben (oben im Bild gelb markiert) und die Formel läuft automatisch in die angrenzenden Zellen soweit über, wie eben benötigt wird (englisch: Spill).

Das hat insbesondere in Verbindung mit formatierten Tabellen einen riesigen Vorteil: Wenn man, wie in diesem Beispiel, als Bezug den Namen der formatierten Tabelle verwendet (also „Auftragsliste“) und nicht einen Zellenbezug (z.B. A2:G23), erweitert sich die Ergebnisliste automatisch und wie von Zauberhand, sobald in der Ausgangsliste neue Daten dazukommen oder sich vorhandene ändern.

Maximale Dynamik bei formatierten Tabellen

Maximale Dynamik bei formatierten Tabellen

Und wenn mich als Ergebnis nicht die bereinigte Auftragsliste interessiert, sondern ich nur wissen möchte, wie viele verschiedene Artikelnummern verkauft wurden? Kein Problem. Wir kombinieren die EINDEUTIG-Funktion mit der ANZAHL2-Funktion:

=ANZAHL2(EINDEUTIG(Auftragsliste[Artikel]))

Jeden Wert nur einmal zählen

Jeden Wert nur einmal zählen

EINDEUTIG für Spezialfälle

Wer seine Tabellen gerne mit Dropdown-Listen ausstattet, wird sich ebenfalls über die EINDEUTIG-Funktion freuen. Denn damit lassen sich auch vollautomatisch und höchst dynamisch Dropdown-Liste auf Basis der bereits vorhandenen Elemente erstellen.

Bleiben wir dazu beim Beispiel mit der Auftragsliste. Ich möchte in der Spalte mit den Artikelnummern ein Dropdown-Feld mit allen vorhandenen Artikeln einrichten. Die Auswahlliste soll selbstverständlich sortiert sein und außerdem erlauben, dass man bei der Eingabe auch neue Artikel hinzufügt.

Die Quelle für meine Dropdown-Liste kommt in Spalte J. Um eine sortierte eindeutige Liste aller Artikelnummern zu erhalten, kombinieren ich die EINDEUTIG-Funktion mit der ebenfalls neuen Array-Funktion SORTIEREN:

=SORTIEREN(EINDEUTIG(Auftragsliste[Artikel]))

Die sortierte eindeutige Artikelliste

Die sortierte eindeutige Artikelliste

Sieht schon mal ganz gut aus, oder?

Nun richten wir die Datenüberprüfung für die Spalte C ein. Als Quelle verweisen wir im Eingabefeld auf die erste Zelle unserer sortierten Artikelliste und hängen an die Zelladresse ein Rautezeichen an:

=$J$2#

Datenprüfung einrichten

Datenprüfung einrichten

Dieses unscheinbare Rautezeichen ist sozusagen das Überlauf-Zeichen und bedeutet, dass eben nicht nur auf Zelle J2 zugegriffen wird, sondern auf den gesamten dynamisch erzeugten Bereich. Das heißt, die Auswahlliste passt sich dynamisch an, je nachdem, wie viele Artikel eben vorhanden sind. Probieren wir das gleich einmal aus.

Sobald ich eine neue Zeile an die Auftragsliste anfüge, erweitert sich auch die Artikelliste um einen Eintrag, der zunächst nur eine Null anzeigt, da wir ja noch nichts eingegeben haben. Ich kann jetzt aus der Dropdownliste einen Artikel auswählen (ja, auch der Nullwert wird angezeigt) oder einfach einen neuen Artikel eintippen.

Dynamische Dropdown-Liste im Einsatz

Dynamische Dropdown-Liste im Einsatz

Und wie man sieht, wird der neue Artikel automatisch in die Auswahlliste übernommen und steht damit ab sofort zur Verfügung. Sehr cool, oder?

Ein kleiner Haken soll aber nicht verschwiegen werden:

Durch diese maximale Dynamik kann ich nicht verhindern, dass auf diesem Weg tatsächlich neue Artikel erfasst werden. Das heißt, die sonst in Dropdown-Listen übliche Fehlermeldung, die bei nicht vorhandenen Einträgen erscheint, funktioniert hier nicht. Denn in dem Moment, in dem ich den neuen Artikel eintippe und mit der Eingabetaste bestätige, wird ja die dynamische Artikelliste schon erweitert. Und damit steht der Artikel auch in der Datenprüfung schon zur Verfügung.

Wenn du mit dieser Einschränkung leben kannst, ist die vorgestellt Variante aber auf jeden Fall eine Überlegung wert.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

7 Gedanken zu “EINDEUTIG mal etwas anderes

  • Steffen Herrmann

    Lieber Martin,

    herzlichen Dank für die tolle und verständliche Erklärung!

    Falls in Ordnung würde ich gerne eine kurze Nachfrage bzgl. dieser Funktion stellen:
    Wenn man einen Datensatz (z.B. mit Automarken und dem Herstellungsjahr) in eine intelligente Tabelle formatiert und auf einem Extra-Arbeitsblatt mit dem Befehl „Eindeutig“ sich nur diejenigen Automarken anzeigen lassen will, welche in einem gegebenen Jahr produziert wurden – wie kann ich so etwas mit „Eindeutig“darstellen?

    Vielleicht noch einmal mit konkretem Beispiel: Wenn ich nur ‚=Eindeutig(Tabelle1[Automarken]) benutze, dann zeigt es mir alle Automarken einmal an. In meinem Datensatz wurde jedoch nicht jedes Jahr jede Automarke produziert. Mein Wunsch wäre daher, dass gegeben ich schaue mir das Jahr 2010 an auch nur die Automarken angezeigt werden, welche in dem Jahr 2010 produziert wurden. Funktioniert das mittels „Eindeutig“? Für die Art und Weise wie ich in der Folge die Daten weiter bearbeiten möchte wäre eine Spilled-range (welche wie du beschrieben hast aus der Nutzung von „Eindeutig“ resultiert) super.

    Herzlichen Dank schon einmal im Voraus und viele Grüße
    Steffen

    • Hermann Baum

      Hallo Steffen,
      mit
      = FILTERN(Tabelle1[Automarken];Tabelle1[Jahr]=2010)
      filterst du zuerst alle Automarken des Jahres 2010 heraus.
      Dann setzt du diesen Filter-Ausdruck als Parameter in die Funktion EINDEUTIG ein:
      =EINDEUTIG(FILTERN(Tabelle1[Automarken];Tabelle1[Jahr]=2010))
      Wenn du die Automarken noch sortiert haben willst, setzt du diesen ganzen Ausdruck noch in die Funktion SORTIEREN ein:
      =SORTIEREN(EINDEUTIG(FILTERN(Tabelle1[Automarken];Tabelle1[Jahr]=2010)))

      Ich habe kein Excel 365, sondern nur Excel 2019, was diese Funktionen leider nicht besitzt.
      Ich habe aber mit Hilfe von VBA die Funktionen SORTIEREN, SORTIERENNACH, FILTERN und EINDEUTIG nachgebaut.
      Damit habe ich die Formeln oben getestet.

      Herzliche Grüße
      Hermann

    • Martin Weiß Autor des Beitrags

      Hallo Steffen,

      du musst nur die EINDEUTIG-Funktion mit der FILTER-Funktion kombinieren, dann geht das:
      EINDEUTIG + FILTER

      Schöne Grüße,
      Martin

  • Wolfgang

    Hi Martin,

    danke für die gute Erklärung. Ich verzweifle gerade an der Tatsache, das =EINDEUTIG nicht die Ursprungsformatierung mitnimmt – habe ich ein Datum, kommen nur die Zahlwerte des Datums.(hier: ausgewählter Filter)
    Kategorie 1 Kategorie 2
    Kandidat von Prio
    ausgewählter Filter 1 44523 hoch

    Im Normalfall ist das kein Problem, da formatiere ich halt die Zellen händisch. Dies ist aber bei mir nicht möglich, da die Zellen unter Kategorie 1 und Kategorie 2 Dropdownauswahlfelder sind. Und auch die darunter liegende Zeile ist ein Dropdownauswahlfeld, welches von dem darüberliegenden Feld abhängig ist (also unter „Prio“ werden hoch, mittel und niedrig als Auswahl angezeigt, unter „Kandidat“ von werden Datum angezeigt. Die Dropdownauswahl habe ich über =EINDEUTIG festgelegt.
    Ich vermute, dass das nur über VBA geht, oder hast Du noch eine Idee?

    Gruß

    Wolfgang

    • Martin Weiß Autor des Beitrags

      Hallo Wolfgang,

      ja, das ist tatsächlich ein Schwachpunkt, das Ursprungsformat wird nicht übernommen und muss leider von Hand eingestellt werden. Aber ich verstehe noch nicht ganz, warum das bei dir für die Dropdown-Zellen nicht gehen sollte.
      Wie sieht denn die Datenüberprüfung für deine Dropdown-Felder genau aus?

      Schöne Grüße,
      Martin