Excel formula: Broj izvršavanja u tablici -

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.

Zanimljivi članci...