Sažetak
Da biste stvorili tekući broj u Excel tablici, možete upotrijebiti funkciju INDEX sa strukturiranom referencom za stvaranje proširivog raspona. U prikazanom primjeru formula u F5 je:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Kad se kopira prema stupcu, ova će formula vratiti tekući broj za svaku boju u stupcu Boja.
U nekim verzijama Excela ovo je formula niza i mora se unijeti s control + shift + enter.
Obrazloženje
U osnovi, ova formula koristi INDEX za stvaranje proširene reference poput ove:
INDEX((Color),1):(@Color) // expanding range
Na lijevoj strani dvotočke (:), funkcija INDEX vraća referencu na prvu ćeliju u stupcu stupca.
INDEX((Color),1) // first cell in color
To djeluje jer funkcija INDEX vraća referencu na prvu ćeliju, a ne stvarnu vrijednost. Na desnoj strani debelog crijeva dobivamo referencu na trenutni redak stupca boja ovako:
(@Color) // current row of Color
Ovo je standardna strukturirana referentna sintaksa za "ovaj redak". Združene s dvotačkom, ove dvije reference stvaraju raspon koji se širi dok se formula kopira u tablicu. Dakle, zamijenimo ove reference u funkciju SUM, imamo:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Svaki od gore navedenih izraza generira niz vrijednosti TRUE / FALSE, a dvostruki negativ (-) koristi se za pretvaranje tih vrijednosti u 1s i 0s. Dakle, u posljednjem redu završavamo sa:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Ostatak formule jednostavno spaja boju iz trenutnog retka u broj koji vraća SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Jednostavno širenje asortimana?
Zašto ne upotrijebiti jednostavan opseg poput ovog?
SUM(--($B$5:B5=(@Color)))
Iz nekog razloga, ova vrsta mješovite reference oštećuje se u Excel tablici dodavanjem redaka. Korištenje INDEX-a sa strukturiranom referencom rješava problem.
