Generische Formel
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Zusammenfassung
Um eindeutige Werte dynamisch zu sortieren und aus einer Datenliste zu extrahieren, können Sie mithilfe einer Array-Formel einen Rang in einer Hilfsspalte festlegen und anschließend mithilfe einer speziell erstellten INDEX- und MATCH-Formel eindeutige Werte extrahieren. In dem gezeigten Beispiel lautet die Formel zum Festlegen des Ranges in C5: C13:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
wobei "Daten" der benannte Bereich B5: B13 ist.
Hinweis: Dies ist eine mehrzellige Array-Formel, die mit Strg + Umschalt + Eingabe eingegeben wird.
Erläuterung
Hinweis: Die Kernidee dieser Formel basiert auf einem Beispiel in Mike Girvins ausgezeichnetem Buch Control + Shift + Enter.
Das gezeigte Beispiel verwendet mehrere Formeln, die unten beschrieben werden. Auf hoher Ebene wird die MMULT-Funktion verwendet, um einen numerischen Rang in einer Hilfsspalte (Spalte C) zu berechnen, und dieser Rang wird dann von einer INDEX- und MATCH-Formel in Spalte G verwendet, um eindeutige Werte zu extrahieren.
Ranking-Datenwerte
Die MMULT-Funktion führt eine Matrixmultiplikation durch und wird verwendet, um jedem Wert einen numerischen Rang zuzuweisen. Das erste Array wird mit dem folgenden Ausdruck erstellt:
--(data>TRANSPOSE(data))
Hier nutzen wir die Transponierfunktion eine horizontale Anordnung zu schaffen , Daten und alle Werte werden miteinander verglichen. Im Wesentlichen wird jeder Wert mit jedem anderen Wert verglichen, um die Frage zu beantworten, ob dieser Wert größer ist als jeder andere Wert. Dies führt zu einem zweidimensionalen Array mit 9 Spalten x 9 Zeilen, das mit TRUE- und FALSE-Werten gefüllt ist. Das doppelte Negativ (-) wird verwendet, um die TRUE FALSE-Werte auf 1s und Nullen zu zwingen. Sie können das resultierende Array folgendermaßen visualisieren:
Die Matrix von 1s und Nullen oben wird array1 innerhalb der MMULT Funktion. Array2 wird mit diesem Ausdruck erstellt:
ROW(data)^0
Hier wird jede Zeilennummer in "Daten" auf die Potenz Null angehoben, um ein eindimensionales Array zu erstellen, 1 Spalte x 9 Zeilen, gefüllt mit der Nummer 1. MMULT gibt dann das Matrixprodukt der beiden Arrays zurück, die zum Werte in der Rangspalte.
Wir erhalten alle 9 Ranglisten gleichzeitig in einem Array zurück, daher müssen wir die Ergebnisse auf einmal in verschiedene Zellen einfügen. Andernfalls zeigt jede Zelle nur den ersten Rangwert im zurückgegebenen Array an.
Hinweis: Dies ist eine mehrzellige Array-Formel, die mit Strg + Umschalt + Eingabe im Bereich C5: C13 eingegeben wird.
Umgang mit leeren Zellen
Leere Zellen werden mit diesem Teil der Rangfolge behandelt:
=IF(data="",ROWS(data)
Bevor wir MMULT ausführen, prüfen wir hier, ob die aktuelle Zelle in "Daten" leer ist. In diesem Fall weisen wir einen Rangwert zu, der der Zeilenanzahl in Daten entspricht. Dies geschieht, um leere Zellen an den unteren Rand der Liste zu zwingen, wo sie später leicht ausgeschlossen werden können, wenn eindeutige Werte extrahiert werden (siehe unten).
Eindeutige Werte zählen
Um eindeutige Werte in den Daten zu zählen, lautet die Formel in E5:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Da die obige Rangfolgeformel jedem Wert einen numerischen Rang zuweist, können wir die FREQUENCY-Funktion mit SUM verwenden, um eindeutige Werte zu zählen. Diese Formel wird hier ausführlich erläutert. Wir subtrahieren dann 1 vom Ergebnis, wenn die Daten leere Zellen enthalten:
-(blank>0)
Dabei ist "leer" der benannte Bereich E8 und enthält die folgende Formel:
=COUNTBLANK(data)
Im Wesentlichen reduzieren wir die Anzahl der eindeutigen Werte um eins, wenn die Daten leere Zellen enthalten, da wir diese nicht in die Ergebnisse einbeziehen. Die eindeutige Anzahl in Zelle E5 wird als "eindeutig" (für die eindeutige Anzahl) bezeichnet und von der INDEX- und MATCH-Formel verwendet, um leere Zellen herauszufiltern (siehe unten).
Eindeutige Werte extrahieren
Um eindeutige Werte zu extrahieren, enthält G5 die folgende Formel, die nach unten kopiert wurde:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Bevor wir die INDEX- und MATCH-Formel ausführen, prüfen wir zunächst, ob die aktuelle Zeilenanzahl im Extraktionsbereich größer ist als die eindeutige Anzahl des benannten Bereichs "unique" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Wenn dies der Fall ist, extrahieren wir eindeutige Werte und geben eine leere Zeichenfolge ("") zurück. Wenn nicht, führen wir die Extraktionsformel aus:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Beachten Sie, dass hier zwei MATCH-Funktionen ineinander liegen. Das innere MATCH verwendet einen expandierenden Bereich für ein Array und den benannten Bereich "data" für den Suchwert:
MATCH(data,$G$4:G4,0)
Beachten Sie, dass der Erweiterungsbereich in der "Zeile oben", Zeile 4 im Beispiel, beginnt. Das Ergebnis aus dem inneren MATCH ist ein Array, das für jeden Wert in Daten entweder eine numerische Position (der Wert wurde bereits extrahiert) oder den Fehler # N / A (der Wert wurde noch nicht extrahiert) enthält. Wir verwenden dann IF und ISNA, um diese Ergebnisse zu filtern, und geben den Rangwert für alle Werte in "Daten" zurück, die noch nicht extrahiert wurden:
IF(ISNA(results),rank))
Diese Operation führt zu einem Array, das in die MIN-Funktion eingespeist wird, um den "minimalen Rangwert" für noch nicht extrahierte Datenwerte zu erhalten. Die MIN-Funktion gibt diesen Wert als Suchwert an den äußeren MATCH und den benannten Bereich "rank" als Array zurück:
MATCH(min_not_extracted,rank)),rank,0)
Schließlich gibt MATCH die Position des niedrigsten Rangwerts als Zeilennummer an INDEX zurück, und INDEX gibt den Datenwert in der aktuellen Zeile des Extraktionsbereichs zurück.
