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.
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
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
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
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
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
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
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
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.
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.
DANKE! Wieder einmal klasse erklärt. Das bringt wirklich für sehr viele Fälle eine wesentliche Erleichterung.
Hallo erichm,
vielen Dank für das tolle Feedback!
Schöne Grüße,
Martin
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
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
Hallo Steffen,
du musst nur die EINDEUTIG-Funktion mit der FILTER-Funktion kombinieren, dann geht das:
Schöne Grüße,
Martin
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
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
Hallo,
ich dachte ich hätte für mein Problem eine elegante Lösung gefunden, aber leider zählt mein Formel nicht so ganz wie ich es wollte:
=ANZAHL2(EINDEUTIG(FILTER(INDEX(Tabelle2;;1);(INDEX(Tabelle2;;3)=$G$16)*(INDEX(Tabelle2;;2)=$F$16);FALSCH);0;0))
Das Problem an meiner Formel ist, dass selbst wenn Kriterium 1 und Kriterium 2 beide _nicht_ erfüllt sind, die Ausgabematrix immer einer Zeile entspricht und somit auch bei 0 Treffern mir der Wert 1 ausgegeben wird. Also egal ob es genau einen Treffer oder gar keinen Treffer gibt, meine Formel gibt mir den Wert 1 aus. Bei Werten größer gleich 1 funktioniert die Formel wunderbar.
Nun bin ich bei der Internetrecherche auf diese Website gestoßen und dachte ich zeige hier mal mein Problem, vielleicht kann mir ja wer helfen.
Ich selbst komme einfach auf keine passende Lösung. Wie kann ich in meiner Datei auch das nicht vorkommen eines Wertes mit 0 anzeigen?
Hallo Stefan,
das ist aus der Ferne etwas schwierig, wenn man die Tabellenstruktur nicht kennt, die sich hinter deiner Formel verbirgt. Ein guter Ausgangspunkt wäre aber vielleicht die Formelauswertung (im Menü Formeln). Damit kannst du schrittweise deine Formel auflösen und dann vielleicht erkennen, wo das Problem liegt.
Noch ein Tipp:
Die Funktion ANZAHL2 liefert auch den Wert 1, wenn am Ende ein Fehlerwert gezählt wird. Sollte das bei dir der Fall sein (siehe Formelauswertung), könntest du innerhalb der ANZAHL2-Funktion den Fehler mit der WENNFEHLER-Funktion abfangen und z.B. durch den Wert 0 ersetzen).
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Hallo Martin,
ja vielleicht habe ich mich nicht gut genug ausgedrückt. Ich weiß wie sich meine Formel auflöst und verstehe was passiert. Nur funktioniert die Formel nicht, wenn beide Suchkriterien nicht gefunden werden. Das liegt daran, dass mir die Formel EINDEUTIG eine Matrix zurück gibt, die den Wert 0 enthält. Und die Formel ANZAHL2 zählt die Werte der Matrix, daher kann sie nicht bei 0 liegen. Demnach kann meine Formel nicht zwischen 0 Treffern und einem Treffer unterscheiden, bzw. beide Fälle geben den Wert 0 aus. Also kann ich hier auch nicht mit WENNFEHLER arbeiten, denn keine eindeutigen Treffer geben keinen Fehler sondern eine 0 aus.
Meine Tabelle ist nur aus 3 Spalten aufgebaut. In der ersten steht die Auftragsnummer, die ich als eindeutigen Wert zählen möchte, da diese öfters vorkommen kann. In der zweiten Spalte steht eine Region, z.B. Deutschland, und in der 3. Spalte steht ein Produkt. Nun möchte ich zählen wie viele Aufträge es pro Region pro Produkt gibt, allerdings nur die Eindeutigen Werte der Auftragsnummern, die wie schon gesagt öfters vorkommen können.
Wie der Zufall es so will habe ich beim Schreiben dieser Nachricht nun die Lösung gefunden. Und zwar packe ich die bisherige Formel ohne Anzahl2 in eine Wenn-Formel und bilde die Summe. Wenn die Summe 0 ergibt, dann gibt es keine Treffer und er soll eine 0 schreiben, ansonsten macht er die alte Formel:
=WENN(SUMME(EINDEUTIG(FILTER(INDEX(Tabelle2;;1);(INDEX(Tabelle2;;3)=$G$16)*(INDEX(Tabelle2;;2)=$F$16);FALSCH);0;0))=0;0;
ANZAHL2(EINDEUTIG(FILTER(INDEX(Tabelle2;;1);(INDEX(Tabelle2;;3)=$G$16)*(INDEX(Tabelle2;;2)=$F$16);FALSCH);0;0))
Nochmals danke, vielleicht wäre ich sonst gar nicht auf die Lösung gekommen 😉
Hallo,
die Formel ist super.
Ich stehe nur vor dem Problem, dass ich sie Tabellenblatt-übergreifend verwenden möchte. Das klappt irgendwie nicht.
=EINDEUTIG(materials[description])
Die Tabelle materials steht in einem anderen Blatt.
Gibt es dazu eine Lösung?
Vielen DAnk.
Fehler gerade gefunden. Problem gelöst. 🙂
Das Problem an Excel ist halt doch immer der Anwender :-D.
Hallo,
vielen Dank für die sehr ausführlichen Infos, die mir schon oft weitergeholfen haben.
Aktuell habe ich eine allgemeine Frage zu Arrayfunktionen und intelligenten Tabellen.
Wenn ich in einer intelligenten Tabelle eine Arrayfunktion nutzen möchte, dann erhalte ich nur den Fehler #ÜBERLAUF.
Hat vielleicht jemand eine „elegante“ Lösung, wie ich mit den Ergebnissen, die die EINDEUTIG()-Funktion liefert trotzdem in einer intelligenten Tabelle weiterarbeiten kann?
Danke schon mal vorab!
Viele Grüße
Dieter
Hallo Dieter,
es ist wirklich so, dass sich intelligente Tabellen und die neuen Arrayfunktionen gegenseitig ausschließen. Das heißt, es gibt leider keine Möglichkeit, innerhalb einer intelligenten Tabelle eine dieser Arrayfunktionen zu nutzen.
Es ist bestenfalls möglich, zum Beispiel eine Datenüberprüfung innerhalb einer intelligenten Tabelle einzurichten, die auf die Ergebnisse der EINDEUTIG-Funktion an anderer Stelle zugreift. Aber das ist vermutlich nicht, was du dir erhofft hast.
Schöne Grüße,
Martin
Hallo Ihr Lieben,
vielleicht kann mir jemand weiter helfen, den mein Schädel platz gerade.
Ich habe 2 Spalten: 1 Datumsspalte und 1 Namensspalte ( die Namenspalte wird gefiltert WENN(ZÄHLENWENN([Column2];[@Column2])>1;[@Column2];““), damit ich alle Namen bekomme die ich 2 mal oder öfters vorhanden sind)
Ich möchte jetzt ein Array generieren, dass mir alle Namen einmal ausgibt mit dem kleinsten Datum zum Namen Dazu.
(Jeder Name der öfters vorkommt hat ein anderes Datum), dass Heißt er soll erst nach dem Namen eindeutig suchen und dann dazu das passende Kleinste Datum wiedergeben.
Vielen Dank im Voraus
Lg Sebastian
Hallo Sebastian,
eine Formellösung habe ich nicht parat, aber eine Pivot-Tabelle sollte das gewünschte Ergebnis liefern:
Column2 (= Name) in den Zeilenbereich
Column1 (= Datum) in den Wertebereich. Dann die Wertfeldeinstellungen ändern von Anzahl in Minimum und hinterher noch als Datum formatieren. Fertig.
Schöne Grüße,
Martin