Resumé
For at oprette et løbende antal i en Excel-tabel kan du bruge INDEX-funktionen med en struktureret reference til at oprette et ekspanderende interval. I det viste eksempel er formlen i F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Når den kopieres ned i kolonnen, returnerer denne formel et løbende antal for hver farve i kolonnen Farve.
I nogle versioner af Excel er dette en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
Kernen har denne formel brugt INDEX til at oprette en ekspanderende reference som denne:
INDEX((Color),1):(@Color) // expanding range
På venstre side af tyktarmen (:) returnerer INDEX-funktionen en reference til den første celle i kolonnekolonnen.
INDEX((Color),1) // first cell in color
Dette fungerer, fordi INDEX-funktionen returnerer en reference til den første celle, ikke den faktiske værdi. På højre side af tyktarmen får vi en henvisning til den aktuelle række i farvesøjlen som denne:
(@Color) // current row of Color
Dette er den standardstrukturerede referencesyntaks for "denne række". Sammen med tyktarmen skaber disse to referencer et interval, der udvides, når formlen kopieres ned i tabellen. Så vi bytter disse referencer til SUM-funktionen, vi har:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Hvert af udtrykkene ovenfor genererer en matrix med SAND / FALSK-værdier, og det dobbelte negative (-) bruges til at konvertere disse værdier til 1s og 0s. Så i sidste række ender vi med:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Resten af formlen sammenkæder simpelthen farven fra den aktuelle række til antallet, der returneres af SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Simpelt udvidet rækkevidde?
Hvorfor ikke bruge et simpelt udvidelsesområde som dette?
SUM(--($B$5:B5=(@Color)))
Af en eller anden grund ødelægges denne form for blandet reference i en Excel-tabel, når der tilføjes rækker. Brug af INDEX med en struktureret reference løser problemet.
