Sammanfattning
Om du vill skapa ett löpande antal i en Excel-tabell kan du använda INDEX-funktionen med en strukturerad referens för att skapa ett expanderande intervall. I exemplet som visas är formeln i F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
När du kopierar ner kolumnen returnerar denna formel ett löpande antal för varje färg i kolumnen Färg.
I vissa versioner av Excel är detta en matrisformel och måste anges med kontroll + shift + enter.
Förklaring
I grunden har denna formel använt INDEX för att skapa en expanderande referens så här:
INDEX((Color),1):(@Color) // expanding range
På vänster sida av kolon (:) returnerar INDEX-funktionen en referens till den första cellen i kolumnkolumnen.
INDEX((Color),1) // first cell in color
Detta fungerar eftersom INDEX-funktionen returnerar en referens till den första cellen, inte det verkliga värdet. På höger sida av kolon får vi en hänvisning till den aktuella raden i färgkolumnen så här:
(@Color) // current row of Color
Detta är standardstrukturerad referenssyntax för "denna rad". Förenat med kolon, dessa två referenser skapar ett intervall som expanderar när formeln kopieras ner i tabellen. Så vi byter dessa referenser till SUM-funktionen, vi har:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Var och en av uttrycken ovan genererar en matris med SANT / FALSKA värden, och det dubbla negativa (-) används för att konvertera dessa värden till 1s och 0s. Så i den sista raden slutar vi med:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Resten av formeln sammanfogar helt enkelt färgen från den aktuella raden till antalet som returneras av SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Enkelt utökat sortiment?
Varför inte använda ett så enkelt expanderande sortiment?
SUM(--($B$5:B5=(@Color)))
Av någon anledning skadas denna typ av blandad referens i en Excel-tabell när rader läggs till. Att använda INDEX med en strukturerad referens löser problemet.
