Zhrnutie
Ak chcete vytvoriť priebežný počet v tabuľke programu Excel, môžete na zväčšenie rozsahu použiť funkciu INDEX so štruktúrovaným odkazom. V zobrazenom príklade je vzorec v F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Pri kopírovaní nadol do stĺpca tento vzorec vráti priebežný počet jednotlivých farieb v stĺpci Farba.
V niektorých verziách Excelu ide o maticový vzorec a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Tento vzorec v jadre používa INDEX na vytvorenie rozširujúceho odkazu, ako je tento:
INDEX((Color),1):(@Color) // expanding range
Na ľavej strane dvojbodky (:) vráti funkcia INDEX odkaz na prvú bunku v stĺpci stĺpca.
INDEX((Color),1) // first cell in color
Funguje to preto, lebo funkcia INDEX vráti odkaz na prvú bunku, nie skutočnú hodnotu. Na pravej strane dvojbodky dostaneme odkaz na aktuálny riadok farebného stĺpca takto:
(@Color) // current row of Color
Toto je štandardná štruktúrovaná referenčná syntax pre „tento riadok“. Tieto dva odkazy v spojení s dvojbodkou vytvárajú rozsah, ktorý sa rozširuje pri kopírovaní vzorca do tabuľky. Takže tieto referencie zameníme za funkciu SUM, máme:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Každý z vyššie uvedených výrazov generuje pole hodnôt TRUE / FALSE a dvojitý zápor (-) sa používa na prevod týchto hodnôt na 1 s a 0 s. Takže v poslednom rade skončíme s:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Zvyšok vzorca jednoducho zreťazí farbu z aktuálneho riadku na počet vrátený súčtom:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Jednoduché rozšírenie rozsahu?
Prečo nepoužívať jednoduchý rozširujúci sa rozsah ako je tento?
SUM(--($B$5:B5=(@Color)))
Z nejakého dôvodu sa tento druh zmiešanej referencie poškodí v tabuľke Excel, keď sa pridajú riadky. Problém vyrieši použitie INDEXU so štruktúrovaným odkazom.
