Samenvatting
Om een lopende telling in een Excel-tabel te maken, kunt u de functie INDEX met een gestructureerde verwijzing gebruiken om een uitbreidend bereik te creëren. In het getoonde voorbeeld is de formule in F5:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Wanneer deze formule naar beneden in de kolom wordt gekopieerd, retourneert deze een lopende telling voor elke kleur in de kolom Kleur.
In sommige versies van Excel is dit een matrixformule die moet worden ingevoerd met control + shift + enter.
Uitleg
In de kern gebruikt deze formule INDEX om een uitbreidende referentie als volgt te maken:
INDEX((Color),1):(@Color) // expanding range
Aan de linkerkant van de dubbele punt (:) retourneert de functie INDEX een verwijzing naar de eerste cel in de kolomkolom.
INDEX((Color),1) // first cell in color
Dit werkt omdat de functie INDEX een verwijzing naar de eerste cel retourneert, niet de werkelijke waarde. Aan de rechterkant van de dubbele punt krijgen we als volgt een verwijzing naar de huidige rij van de kleurkolom:
(@Color) // current row of Color
Dit is de standaard gestructureerde referentiesyntaxis voor "deze rij". Samengevoegd met de dubbele punt, creëren deze twee verwijzingen een bereik dat groter wordt naarmate de formule door de tabel wordt gekopieerd. Dus we wisselen deze verwijzingen om in de SOM-functie, we hebben:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Elk van de bovenstaande uitdrukkingen genereert een reeks TRUE / FALSE-waarden, en het dubbele negatieve (-) wordt gebruikt om deze waarden om te zetten in 1s en 0s. Dus in de laatste rij eindigen we met:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
De rest van de formule voegt eenvoudig de kleur van de huidige rij samen met het aantal geretourneerd door SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Eenvoudig uitbreidend assortiment?
Waarom zou u niet zo'n eenvoudig uitbreidend assortiment gebruiken?
SUM(--($B$5:B5=(@Color)))
Om de een of andere reden raakt dit soort gemengde verwijzing beschadigd in een Excel-tabel wanneer rijen worden toegevoegd. Door INDEX te gebruiken met een gestructureerde referentie, wordt het probleem opgelost.
