Excel Funktion
EINDEUTIG
für Excel 2007 bis 2019
Download der Exceldatei mit dem VBA-Code der Funktionen
SORTIEREN, SORTIERENNACH, XVERWEIS, XVERGLEICH, FILTER, EINDEUTIG, SEQUENZ, ZUFALLSMATRIX
und allen auf diesen Seiten dargestellten Beispielen:
▸Download der Exceldatei
(Version 3.8 - Letzte Änderung: 24.10.2024 - XVerweis und XVergleich nun fit für den Einsatz in Matrixformeln ▸mehr dazu)
Worum geht es?
In Excel 365 und ab Excel 2021 hat Microsoft die lange vermissten Zellfunktionen SORTIEREN, SORTIERENNACH, FILTER, XVERWEIS und weitere implementiert. Allerdings wurden sie für ältere Excel-Versionen nicht nachgerüstet, so dass alle Benutzer von Excel 2007 bis einschließlich Excel 2019 darauf verzichten mussten.
Hier gibt es nun (seit Ende Januar 2022) eine Lösung für die Benutzer älterer Excel-Versionen.
Mit Hilfe von VBA wurden benutzerdefinierte Zellfunktionen - sog. UDFs (User Defined Functions) - implementiert, die genau dieselben Parameterlisten haben wie die entsprechenden Microsoft-Funktionen.
In den Fällen, wo die hier zur Verfügung gestellten Funktionen mehrere Ergebnisse zurückgeben, muss ihre Eingabe mit STRG-UMSCHALT-EINGABE abgeschlossen werden. Vor der Eingabe der Formel muss auf dem Tabellenblatt ein Bereich selektiert werden, der die zurückgegebenen Daten aufnimmt - wie bei den sog. CSE-Matrixformeln üblich (CSE = Control Shift Enter).
Eine grundlegende und ausführliche Einführung in das Thema Matrixformeln gibt es in ▸diesem Tutorial.
Bei Funktionsnamen von UDFs ist es egal, ob man die Buchstaben groß oder klein schreibt.
Anleitung zum Einbinden der Funktionen in die eigene Exceldatei siehe ▸Einbinden der Funktionen.
Die Funktion EINDEUTIG
Die Funktion Eindeutig ist ein Ersatz für die nur in Excel 365 verfügbare Funktion EINDEUTIG. Sie erwartet genau dieselbe Parameterliste wie die Excel 365-Funktion.
= Eindeutig ( Matrix; [nach_Spalte]; [genau_einmal] )
| Parameter | Erläuterung |
|---|---|
| Matrix | Matrix oder Bereich, aus der/dem eindeutige Zeilen oder Spalten zurückgegeben werden sollen |
| nach_Spalte (optional) |
Ein boolescher Wert, der angibt, ob Zeilen oder Spalten verglichen werden sollen WAHR: Es werden Spalten verglichen und zurückgegeben FALSCH: Es werden Zeilen verglichen und zurückgegeben (Standard) |
| genau_einmal (optional) |
Ein boolescher Wert WAHR: Es werden alle Zeilen bzw. Spalten zurückgegeben, die genau einmal vorkommen FALSCH: Es werden aus den Zeilen bzw. Spalten die Duplikate entfernt (Standard) |
Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
▸https://support.microsoft.com/de-de/office/eindeutig-funktion-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
Diese Funktion erfüllt je nach Wert des dritten Parameters zwei verschiedene Aufgaben. Wenn dieser Parameter nicht angegeben oder FALSCH ist, werden aus den Zeilen/Spalten alle Duplikate entfernt und jede vorkommende Zeile/Spalte nur einmal aufgeführt. Wenn dieser Parameter WAHR ist, werden aus den Zeilen/Spalten alle diejenigen herausgesucht, die genau einmal vorkommen. Mehrfach auftretende Zeilen/Spalten werden somit ganz weggelassen.
Übereinstimmung zweier Zeilen/Spalten bedeutet, dass alle Zellen der ersten Zeile/Spalte mit den entsprechenden Zellen der zweiten Zeile/Spalte übereinstimmen.
Beispiele mit der Funktion Eindeutig
Hier sind einige Beispiele für den Einsatz der Funktion Eindeutig zu sehen, im zweiten Beispiel auch die Kombination mit Sortieren.
Die verwendeten Formeln lauten:
= Eindeutig ($C$7:$C$26)
= Sortieren (Eindeutig ($D$7:$D$26))
= Eindeutig ($B$7:$B$26; ; WAHR)
= Eindeutig ($B$7:$C$26)
Beispiel für die Kombination von Sortieren, Filter_ und Eindeutig
Dieses Beispiel zeigt, wie man alle Mitarbeiter der Region "Süd" in alphabetischer Reihenfolge auflisten kann.
Mit dem Formelausdruck
Filter_ (Tabelle[Mitarbeiter]; Tabelle[Region]=$I$4)
werden zuerst alle Mitarbeiter der Region "Süd" herausgefiltert (in dem Drop-Down-Auswahlfeld I4 soll die Region "Süd" ausgewählt sein).
Damit kein Mitarbeiter mehrfach aufgeführt wird, wird der ganze Formelausdruck in die Funktion Eindeutig eingesetzt:
Eindeutig (Filter_ (Tabelle[Mitarbeiter]; Tabelle[Region]=$I$4)).
Um die Mitarbeiterliste noch zu sortieren, wird wiederum dieser zweite Formelausdruck in die Funktion Sortieren eingesetzt:
= Sortieren (Eindeutig (Filter_ (Tabelle[Mitarbeiter]; Tabelle[Region]=$I$4))).
Beispiel: Gesamtumsätze aller Mitarbeiter
Dieses Beispiel beantwortet die Frage: Welchen Gesamtumsatz hat jeder Mitarbeiter erzielt?
Die erste Liste ist nach Mitarbeitern alphabetisch sortiert, die zweite Liste nach Umsätzen.
Die erste Liste besteht aus zwei Formeln.
Spalte 'Mitarbeiter':
=Sortieren(Eindeutig($B$7:$B$26))
Die Funktion EINDEUTIG gibt die Liste der Mitarbeiter zurück (jeden nur einmal), und die Funktion SORTIEREN sortiert diese Liste alphabetisch.
Spalte 'Umsatz':
=WENN(G7:G26="";"";SUMMEWENNS($E$7:$E$26;$B$7:$B$26;G7:G26))
Mit Hilfe der Funktion SUMMEWENNS werden die Gesamtumsätze der Mitarbeiter ermittelt.
Die zweite Liste besteht aus einer einzigen Formel:
=Sortieren(WAHL({1.2};Eindeutig($B$7:$B$26);WENN(Eindeutig($B$7:$B$26)="";"";SUMMEWENNS($E$7:$E$26;$B$7:$B$26;Eindeutig($B$7:$B$26))));2;-1)
Im ▸Tutorial 'Matrixformeln' - Beispiel 9 werden diese Formeln ausführlich erklärt.
Für Interessierte, denen vielleicht die Funktionen EINDEUTIG und SORTIEREN nicht zur Verfügung stehen, weil sie ein Open-Source-Programm verwenden, ist auch noch eine konventionelle Lösung ohne diese beiden Funktionen beigefügt:
Zusatz:
Gesamtumsätze aller Mitarbeiter - konventionelle Lösung
In der folgenden Abbildung sind die Spalten G bis M ausgeblendet.
Es handelt sich um eine Lösung für den Fall 'Sortiert nach Mitarbeitern' (erste Liste).
Die folgenden Formeln werden verwendet:
Zelle N7:
=ZÄHLENWENN ($B$7:$B7; $B7) + ZEILE() / 10000
Zelle O7:
=KKLEINSTE (N$7:N$26; ZEILE (A1))
Zelle P7:
=WENN (O7 > 2 ;"" ;INDEX ($B$7:$B$26; VERGLEICH (O7; $N$7:$N$26; 0)))
Zelle Q7:
=ZÄHLENWENN ($P$7:$P$26; "<=" & $P7) + ZEILE() * 0,0001 + ($P7 = "") * 10000
Zelle R7:
=KKLEINSTE (Q$7:Q$26; ZEILE (A1))
Zelle S7:
=INDEX ($P$7:$P$26; VERGLEICH (R7; $Q$7:$Q$26; 0))
Zelle T7:
=WENN (S7 = ""; ""; SUMMEWENNS ($E$7:$E$26; $B$7:$B$26; S7))
Alle sieben Formeln werden nach unten kopiert.
Die einzelnen Formeln werden ebenfalls im ▸Tutorial 'Matrixformeln' - Abschnitt 9.3 ausführlich erklärt.
Wissen teilen ist die Zukunft der Menschheit