We weten dat de AANTAL.ALS-functie in Excel op meerdere criteria kan rekenen. Het neemt argumenten als een aantal criteriabereik en criteria. We kunnen criteria dynamisch wijzigen door de celreferentie op te geven, maar we kunnen de kolom met criteria niet dynamisch wijzigen. Nou, niet direct, maar we kunnen het wel. Dat is wat we vooraf Excel-formules leren. Dingen doen in Excel die normaal niet kunnen. Laten we eens kijken hoe.
Laten we een voorbeeld nemen.
Context
Hier heb ik verkoopgegevens opgesteld die in verschillende maanden van het jaar door onze verkoopadviseurs zijn gedaan. Ze verkopen verschillende modellen van ons product, met de algemene naam model1, model2 enzovoort. Wat we moeten doen, is de verkoop van verschillende modellen in verschillende maanden dynamisch tellen.
In cel I2 kiezen we de maand. In cel I2 kiezen we het model. Deze waarden kunnen veranderen. En de telling moet ook veranderen. De AANTAL.ALS-functie moet zoeken naar de maandkolom die het criteriumbereik zal zijn. Dan zoekt het naar een model in de kolom van die maand.
Dus hier zijn de criteria en het criteriabereik beide variabel. Dus hoe maken we kolomvariabelen in AANTALLEN.ALS? Hier is hoe?
Benoemd bereik gebruiken voor variabele kolom of criteriumbereik
Generieke formule
=AANTALLEN.ALS(INDIRECT(named_range),criteria)
Geef eerst elke kolom een naam volgens hun koppen. Selecteer hiervoor de tabel en druk op CTRL+SHIFT+F3 en geef de kolommen een naam zoals in de bovenste rij. Lees er hier over.
Bereik B3:B11, C3:C11, D3:D11 en E3:E11 worden dus respectievelijk Jan, Feb, Mar en Apr genoemd.
Schrijf deze formule in I4.
=AANTALLEN.ALS(INDIRECT(I2),I3)
Als u nu de maand in I4 wijzigt, wordt de respectieve maandentelling van Model4 weergegeven in I4.
Hoe het werkt?
De formule is eenvoudig. Laten we van binnenuit beginnen.
INDIRECT(I2): Zoals we weten, converteert de functie INDIRECT tekstref naar werkelijke referentie. We hebben het voorzien I2. I2 bevat april. Aangezien we de naam Apr in het bereik E3:E11 hebben, vertaalt INDIRECT(I2) zich naar E3:E11.
De formule vereenvoudigd tot =AANTALLEN.ALS(E3:E11,I3). AANTALLEN.ALS telt wat er in I3 in bereik E3:E11 is.
Wanneer u van maand verandert, verandert de kolom dynamisch. Dit wordt AANTALLEN.ALS met variabele kolommen genoemd. In de gif heb ik wat voorwaardelijke opmaak gebruikt op basis van een andere cel.
De formule kan ook werken met de countif-functie. Maar als u meerdere voorwaarden wilt hebben, gebruik dan de AANTAL.ALS-functie.
Creatieve kolomdiagram met totalen
Overlay-diagram maken in Excel 2016
Pareto-grafiek en -analyse uitvoeren in Excel
Watervalgrafiek uitvoeren in Excel
Excel Sparklines: de kleine grafieken in cel
Snelheidsmeter (meter) grafiek in Excel 2016