Sammendrag
For å opprette en løpende telling i en Excel-tabell, kan du bruke INDEX-funksjonen med en strukturert referanse for å lage et utvidende område. I eksemplet vist er formelen i F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Når du kopierer ned kolonnen, returnerer denne formelen et løpende antall for hver farge i kolonnen Farge.
I noen versjoner av Excel er dette en matriseformel og må angis med kontroll + skift + enter.
Forklaring
I kjernen har denne formelen brukt INDEX til å lage en utvidende referanse som denne:
INDEX((Color),1):(@Color) // expanding range
På venstre side av kolon (:) returnerer INDEX-funksjonen en referanse til den første cellen i kolonnekolonnen.
INDEX((Color),1) // first cell in color
Dette fungerer fordi INDEX-funksjonen returnerer en referanse til den første cellen, ikke den faktiske verdien. På høyre side av tykktarmen får vi en referanse til den gjeldende raden i fargekolonnen slik:
(@Color) // current row of Color
Dette er standard strukturert referansesyntaks for "denne raden". Sammen med kolon, oppretter disse to referansene et område som utvides når formelen kopieres nedover tabellen. Så vi bytter disse referansene til SUM-funksjonen, vi har:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Hvert av uttrykkene ovenfor genererer en matrise med SANNE / FALSE verdier, og det dobbelte negative (-) brukes til å konvertere disse verdiene til 1s og 0s. Så på siste rad ender vi med:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Resten av formelen sammenkobler ganske enkelt fargen fra gjeldende rad til antallet som returneres av SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Enkelt utvidende rekkevidde?
Hvorfor ikke bruke et enkelt utvidende utvalg som dette?
SUM(--($B$5:B5=(@Color)))
Av en eller annen grunn blir denne typen blandet referanse ødelagt i en Excel-tabell når rader legges til. Å bruke INDEX med en strukturert referanse løser problemet.
