Angenommen, Sie möchten eindeutige Elemente aus einer Liste zählen können, jedoch mit einer Wendung. Angenommen, Sie arbeiten mit diesem Arbeitsblatt:
Spalte D zählt die Anzahl der Zeilen in jedem der Abschnitte aus Spalte B, und Spalte C zählt die Anzahl der eindeutigen Abschnitte basierend auf den ersten fünf Zeichen der Spalte A für diesen Abschnitt. Die Zellen B2: B11 enthalten ARG, und Sie können acht eindeutige Elemente in den ersten fünf Zeichen von A2: A11 zählen, da A7: A9 jeweils 11158 enthalten, sodass die beiden Duplikate nicht gezählt werden. In ähnlicher Weise gibt die 5 in D12 an, dass es fünf Zeilen für BRD gibt, aber innerhalb der Zeilen 12:16 gibt es drei eindeutige Elemente der ersten fünf Zeichen, da 11145 wiederholt und 11173 wiederholt wird.
Aber wie weist man Excel an, dies zu tun? Und welche Formel könnten Sie in C2 verwenden, die nach C12 und C17 kopiert werden könnte?
Die einfache Zählformel in D2 ,, =COUNTIF(B:B,B2)zählt, wie oft B2 (ARG) in Spalte B vorhanden ist.
Sie verwenden eine Hilfsspalte, um die ersten fünf Zeichen der Spalte A zu isolieren, wie in dieser Abbildung dargestellt:
Als nächstes müssen Sie irgendwie angeben, dass Sie für ARG nur an den Zellen F2: F11 interessiert sind, um die Anzahl der eindeutigen Elemente zu ermitteln. Im Allgemeinen würden Sie diesen Wert mithilfe der in dieser Abbildung gezeigten Array-Formel ermitteln:
Sie verwenden Zelle C3 vorübergehend nur, um die Formel anzuzeigen. Sie können sehen, dass es in C3 in früheren Abbildungen nicht vorhanden ist. (Sie werden in Kürze erfahren, wie diese Formel funktioniert.)
Wie lautet die Formel in C2, C12 und C17? Die überraschende (und coole) Antwort ist in dieser Abbildung dargestellt:
Whoa! Wie funktioniert das?
Schauen Sie sich Antwort in den definierten Namen in dieser Abbildung an:
Es ist die gleiche Formel wie in einer früheren Abbildung, aber anstatt den Bereich F2: F11 zu verwenden, wird ein Bereich namens Rg verwendet. Die Formel war auch eine Array-Formel, aber benannte Formeln werden so behandelt, als wären sie Array-Formeln! Das heißt, =Answerwird nicht mit Strg + Umschalt + Eingabetaste eingegeben, sondern einfach wie gewohnt eingegeben.
Wie ist Rg definiert? Wenn Zelle C1 ausgewählt ist (was ein wichtiger Schritt zum Verständnis dieses Tricks ist), wird sie wie in dieser Abbildung definiert:
Das ist =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details ist der Name des Blattes, aber Sie können diese Formel ohne den langen Blattnamen anzeigen. Eine einfache Möglichkeit, dies zu tun, besteht darin, dem Blatt vorübergehend einen einfachen Namen wie x zu geben und dann den definierten Namen erneut zu überprüfen:
Diese Formel ist leichter zu lesen!
Sie können sehen, dass diese Formel mit $ B1 (beachten Sie den relativen Verweis auf die aktuelle Zeile) mit der gesamten Spalte B übereinstimmt und 1 subtrahiert. Sie subtrahieren 1, weil Sie OFFSET von F1 verwenden. Nachdem Sie die Formel für C kennen, sehen Sie sich die Formel für C2 an:
Der MATCH($B2,$B:$B,0)Teil der Formel ist 2, daher lautet die Formel (ohne Bezugnahme auf den Blattnamen):
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
oder:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
oder:
=OFFSET($F$1,1,0,10,1)
Da COUNTIF($B:$B,$B2)es 10 ist, gibt es 10 ARGs. Dies ist der Bereich F2: F11. Wenn Zelle C2 ausgewählt ist und Sie F5 drücken, um zu Rg zu gelangen, sehen Sie Folgendes:
Wenn die Startzelle C12 wäre, wird durch Drücken von F5 zu Rg Folgendes erzeugt:
Wenn die Antwort als definiert ist =SUM(1/COUNTIF(rg,rg)), sind Sie fertig!
Schauen wir uns anhand eines viel einfacheren Beispiels genauer an, wie diese Formel funktioniert. Normalerweise lautet die Syntax für COUNTIF =COUNTIF(range,criteria)wie =COUNTIF(C1:C10, "b")in dieser Abbildung:
Dies würde 2 als Anzahl der bs im Bereich ergeben. Wenn Sie jedoch den Bereich selbst als Kriterium übergeben, wird jedes Element im Bereich als Kriterium verwendet. Wenn Sie diesen Teil der Formel markieren:
und drücken Sie F9, Sie sehen:
Jedes Element im Bereich wird bewertet, und diese Zahlenreihe bedeutet, dass es ein a und zwei b, drei c und vier d gibt. Diese Zahlen sind in 1 unterteilt und ergeben 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, wie Sie hier sehen können:
Sie haben also 2 Hälften, 3 Drittel, 4 Viertel und 1 Ganzes, und wenn Sie diese addieren, erhalten Sie 4. Wenn ein Gegenstand 7 Mal wiederholt würde, hätten Sie 7 Siebtel und so weiter. Ziemlich cool! (Hut ab vor David Hager, der diese Formel entdeckt / erfunden hat.)
Aber warte eine Minute. Derzeit müssen Sie diese Formel nur in C2, C12 und C17 eingeben. Wäre es nicht besser, wenn Sie es in C2 eingeben und ausfüllen und nur in den richtigen Zellen anzeigen könnten? In der Tat können Sie dies tun. Sie können die Formel in C2 so ändern =IF(B1B2,Answer,""), dass sie lautet, und wenn Sie sie ausfüllen, erledigt sie die Aufgabe:
Aber warum hier aufhören? Warum machen Sie die Formel nicht zu einer benannten Formel, wie hier gezeigt:
Damit dies funktioniert, muss Zelle C2 die aktive Zelle sein (oder die Formel muss anders sein). Jetzt können Sie die Formeln der Spalte C ersetzen durch =Answer2:
Sie können sehen, dass C3 =Answer2wie alle Zellen in Spalte C hat. Warum nicht in Spalte D fortfahren? Die Formel in D2 wird hier gezeigt, nachdem auch der Vergleich mit B1 und B2 angewendet wurde:
Wenn Sie also Zelle D2 ausgewählt lassen und eine andere Formel definieren, sagen Sie Antwort3:
dann können Sie =Answer3in Zelle D2 eingeben und ausfüllen:
Hier ist der obere Teil des Arbeitsblatts mit angezeigten Formeln, gefolgt von demselben Screenshot mit folgenden Werten:
Wenn andere Leute versuchen, das herauszufinden, kratzen sie sich vielleicht zuerst am Kopf!
Dieser Gastartikel stammt von Excel MVP Bob Umlas. Es ist aus dem Buch More Excel Outside the Box. Klicken Sie hier, um die anderen Themen im Buch anzuzeigen.
