souhrn
Chcete-li vytvořit průběžný počet v tabulce Excel, můžete použít funkci INDEX se strukturovaným odkazem k vytvoření rozšiřujícího se rozsahu. V zobrazeném příkladu je vzorec v F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Při kopírování dolů do sloupce vrátí tento vzorec průběžný počet pro každou barvu ve sloupci Barva.
V některých verzích aplikace Excel se jedná o maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
V podstatě tento vzorec používá INDEX k vytvoření rozšiřujícího odkazu, jako je tento:
INDEX((Color),1):(@Color) // expanding range
Na levé straně dvojtečky (:) vrátí funkce INDEX odkaz na první buňku ve sloupci sloupce.
INDEX((Color),1) // first cell in color
To funguje, protože funkce INDEX vrací odkaz na první buňku, nikoli na skutečnou hodnotu. Na pravé straně dvojtečky dostaneme odkaz na aktuální řádek barevného sloupce takto:
(@Color) // current row of Color
Toto je standardní strukturovaná referenční syntaxe pro „tento řádek“. Tyto dva odkazy spojené s dvojtečkou vytvářejí rozsah, který se rozšiřuje při kopírování vzorce do tabulky. Takže tyto odkazy vyměníme za funkci SUM, máme:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Každý z výše uvedených výrazů generuje pole TRUE / FALSE hodnot a dvojitý zápor (-) se používá k převodu těchto hodnot na 1 s a 0 s. Takže v poslední řadě skončíme s:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Zbytek vzorce jednoduše zřetězí barvu z aktuálního řádku na počet vrácený součtem:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Jednoduché rozšiřování dosahu?
Proč nepoužívat jednoduchý rozšiřující se rozsah, jako je tento?
SUM(--($B$5:B5=(@Color)))
Z nějakého důvodu se tento druh smíšené reference v tabulce Excel po přidání řádků poškodí. Problém vyřeší použití INDEXU se strukturovanou referencí.
