Zusammenfassung
Um eine laufende Anzahl in einer Excel-Tabelle zu erstellen, können Sie die INDEX-Funktion mit einer strukturierten Referenz verwenden, um einen erweiterten Bereich zu erstellen. In dem gezeigten Beispiel lautet die Formel in F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Beim Kopieren in die Spalte gibt diese Formel eine laufende Anzahl für jede Farbe in der Spalte Farbe zurück.
In einigen Excel-Versionen ist dies eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Im Kern verwendet diese Formel INDEX, um eine expandierende Referenz wie folgt zu erstellen:
INDEX((Color),1):(@Color) // expanding range
Auf der linken Seite des Doppelpunkts (:) gibt die INDEX-Funktion einen Verweis auf die erste Zelle in der Spaltenspalte zurück.
INDEX((Color),1) // first cell in color
Dies funktioniert, weil die INDEX-Funktion einen Verweis auf die erste Zelle zurückgibt, nicht auf den tatsächlichen Wert. Auf der rechten Seite des Doppelpunkts erhalten wir einen Verweis auf die aktuelle Zeile der Farbspalte wie folgt:
(@Color) // current row of Color
Dies ist die standardmäßige strukturierte Referenzsyntax für "diese Zeile". Zusammen mit dem Doppelpunkt erstellen diese beiden Referenzen einen Bereich, der erweitert wird, wenn die Formel in die Tabelle kopiert wird. Also tauschen wir diese Referenzen in die SUMME-Funktion aus, die wir haben:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Jeder der obigen Ausdrücke erzeugt ein Array von TRUE / FALSE-Werten, und das doppelte Negativ (-) wird verwendet, um diese Werte in 1s und 0s umzuwandeln. In der letzten Reihe haben wir also:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Der Rest der Formel verkettet einfach die Farbe aus der aktuellen Zeile mit der von SUM zurückgegebenen Anzahl:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Einfacher Bereich erweitern?
Warum nicht einen einfachen Erweiterungsbereich wie diesen verwenden?
SUM(--($B$5:B5=(@Color)))
Aus irgendeinem Grund wird diese Art von gemischter Referenz in einer Excel-Tabelle beschädigt, wenn Zeilen hinzugefügt werden. Die Verwendung von INDEX mit einer strukturierten Referenz löst das Problem.
