Wenn Sie die Liste sortieren müssen, stehen Ihnen viele Möglichkeiten zur Verfügung, von denen die einfachsten die Sortierschaltflächen auf der Registerkarte oder im Menü sind Datum (Daten — Sortieren). Es gibt jedoch Situationen, in denen die Sortierung der Liste automatisch erfolgen muss, z. B. Formeln. Dies kann beispielsweise beim Generieren von Daten für eine Dropdown-Liste, beim Berechnen von Daten für Diagramme usw. erforderlich sein. Wie sortiert man eine Liste mit einer Formel im Handumdrehen?
Methode 1. Numerische Daten
Wenn die Liste nur numerische Informationen enthält, kann die Sortierung einfach mit den Funktionen durchgeführt werden AM WENIGSTEN (KLEIN) и LINE (DIE ZEILE):
Funktion AM WENIGSTEN (KLEIN) zieht aus dem Array (Spalte A) das n-te kleinste Element in einer Reihe. Diese. SMALL(A:A;1) ist die kleinste Zahl in der Spalte, SMALL(A:A;2) ist die zweitkleinste und so weiter.
Funktion LINE (DIE ZEILE) gibt die Zeilennummer für die angegebene Zelle zurück, also ZEILE(A1)=1, ZEILE(A2)=2 usw. In diesem Fall dient es einfach als Generator einer Zahlenfolge n=1,2,3… für unsere sortierte Liste. Mit gleichem Erfolg war es möglich, eine zusätzliche Spalte zu machen, diese manuell mit der Zahlenfolge 1,2,3 … zu füllen und statt der ROW-Funktion darauf zu verweisen.
Methode 2. Textliste und reguläre Formeln
Wenn die Liste keine Zahlen, sondern Text enthält, funktioniert die Funktion SMALL nicht mehr, sodass Sie einen anderen, etwas längeren Weg gehen müssen.
Lassen Sie uns zunächst eine Service-Spalte mit einer Formel hinzufügen, in der die Seriennummer jedes Namens in der zukünftigen sortierten Liste mithilfe der Funktion berechnet wird COUNTIF (ZÄHLENWENN):
In der englischen Version wird es sein:
=ZÄHLENWENN(A:A,»<"&A1)+ZÄHLENWENN($A$1:A1,"="&A1)
Der erste Term ist eine Funktion zum Zählen der Anzahl von Zellen, die kleiner als die aktuelle ist. Das zweite ist ein Sicherheitsnetz für den Fall, dass ein Name mehr als einmal vorkommt. Dann haben sie nicht die gleichen, sondern sukzessive steigende Nummern.
Nun müssen die empfangenen Nummern der Reihe nach aufsteigend angeordnet werden. Dazu können Sie die Funktion verwenden AM WENIGSTEN (KLEIN) vom ersten weg:
Nun, schließlich bleibt es nur, die Namen anhand ihrer Nummern aus der Liste herauszuziehen. Dazu können Sie die folgende Formel verwenden:
Funktion AUSGESETZTER (PASSEN) sucht in Spalte B nach der gewünschten Seriennummer (1, 2, 3 usw.) und gibt tatsächlich die Nummer der Zeile zurück, in der sich diese Nummer befindet. Funktion INDEX (INDEX) zieht aus Spalte A den Namen an dieser Zeilennummer heraus.
Methode 3: Array-Formel
Diese Methode ist tatsächlich derselbe Platzierungsalgorithmus wie in Methode-2, jedoch implementiert durch eine Matrixformel. Zur Vereinfachung der Formel wurde dem Zellbereich C1:C10 der Name gegeben Liste (Zellen auswählen, drücken Strg + F3 und Knopf Erstellen):
Kopieren Sie in Zelle E1 unsere Formel:
=INDEX(Liste; VERGLEICH(KLEIN(ZÄHLENWENN(Liste; „<“&Liste); ZEILE(1:1)); ZÄHLENWENN(Liste; „<“&Liste); 0))
Oder in der englischen Version:
=INDEX(Liste, MATCH(KLEIN(ZÄHLENWENN(Liste, «<"&Liste), ZEILE(1:1)), ZÄHLENWENN(Liste, "<"&Liste), 0))
und drücken Strg + Umschalt + Enterals Matrixformel einzugeben. Dann kann die resultierende Formel über die gesamte Länge der Liste kopiert werden.
Wenn Sie möchten, dass die Formel keinen festen Bereich berücksichtigt, sondern beim Hinzufügen neuer Elemente zur Liste angepasst werden kann, müssen Sie die Strategie geringfügig ändern.
Zunächst muss der Listenbereich dynamisch festgelegt werden. Dazu müssen Sie beim Erstellen keinen festen Bereich C3:C10 angeben, sondern eine spezielle Formel, die sich auf alle verfügbaren Werte bezieht, unabhängig von ihrer Anzahl. Klicken Alt + F3 oder öffnen Sie die Registerkarte Formeln – Namensmanager (Formeln – Name Manager), erstellen Sie einen neuen Namen und im Feld Link (Referenz) Geben Sie die folgende Formel ein (ich gehe davon aus, dass der zu sortierende Datenbereich ab Zelle C1 beginnt):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Zweitens muss die obige Array-Formel mit einer Marge nach unten gestreckt werden – mit der Erwartung, dass in Zukunft zusätzliche Daten eingegeben werden. In diesem Fall beginnt die Array-Formel, einen Fehler #ZAHL für Zellen auszugeben, die noch nicht gefüllt sind. Um es abzufangen, können Sie die Funktion verwenden IFERROR, die „um“ unsere Matrixformel hinzugefügt werden muss:
=EIFERROHR(INDEX(Liste; MATCH(KLEIN(ZÄHLENWENN(Liste; "<"&Liste); ZEILE(1:1)); ZÄHLENWENN(Liste; "<"&Liste); 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
Es fängt den #NUMBER-Fehler ab und gibt stattdessen ein void (leere Anführungszeichen) aus.
:
- Sortiment nach Farbe sortieren
- Was sind Matrixformeln und warum werden sie benötigt?
- SORT-Sortierung und dynamische Arrays im neuen Office 365