Povzetek
Če želite ustvariti tekoče štetje v Excelovi tabeli, lahko s funkcijo INDEX s strukturirano referenco ustvarite razširjeni obseg. V prikazanem primeru je formula v F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Pri kopiranju v stolpec bo ta formula vrnila tekoče število za vsako barvo v stolpcu Barva.
V nekaterih različicah Excela je to matrična formula in jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
V osnovi ta formula uporablja INDEX za ustvarjanje razširitvene reference, kot je ta:
INDEX((Color),1):(@Color) // expanding range
Na levi strani debelega črevesa (:) funkcija INDEX vrne sklic na prvo celico v stolpcu stolpca.
INDEX((Color),1) // first cell in color
To deluje, ker funkcija INDEX vrne sklic na prvo celico in ne na dejansko vrednost. Na desni strani debelega črevesa dobimo sklic na trenutno vrstico barvnega stolpca, kot je ta:
(@Color) // current row of Color
To je standardna strukturirana referenčna sintaksa za "to vrstico". Ta dva sklica skupaj s dvopičjem ustvarjata obseg, ki se širi, ko se formula kopira po tabeli. Torej, te reference zamenjamo v funkcijo SUM, imamo:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Vsak od zgornjih izrazov ustvari niz TRUE / FALSE vrednosti, dvojni negativ (-) pa se uporablja za pretvorbo teh vrednosti v 1s in 0s. Torej, v zadnji vrstici končamo z:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Preostali del formule preprosto poveže barvo iz trenutne vrstice s številom, ki ga vrne SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Enostavno razširitev obsega?
Zakaj ne bi uporabili preprostega razširitvenega obsega, kot je ta?
SUM(--($B$5:B5=(@Color)))
Iz nekega razloga se ta vrsta mešanega sklica poškoduje v Excelovi tabeli, ko se dodajo vrstice. Uporaba INDEX-a s strukturiranim sklicem reši problem.
