Stel dat je unieke items uit een lijst wilt kunnen tellen, maar dan met een twist. En stel dat u met dit werkblad werkt:
Kolom D telt het aantal rijen in elk van de secties van kolom B, en kolom C telt het aantal unieke secties op basis van de eerste vijf tekens van kolom A voor die sectie. Cellen B2: B11 bevatten ARG en u kunt acht unieke items tellen in de eerste vijf tekens van A2: A11 omdat A7: A9 elk 11158 bevatten, dus de twee duplicaten worden niet meegeteld. Evenzo geeft de 5 in D12 aan dat er vijf rijen zijn voor BRD, maar in rij 12:16 zijn er drie unieke items van de eerste vijf tekens, aangezien 11145 wordt herhaald en 11173 wordt herhaald.
Maar hoe vertel je Excel dit te doen? En welke formule zou je in C2 kunnen gebruiken die naar C12 en C17 kan worden gekopieerd?
De eenvoudige telformule in D2,, =COUNTIF(B:B,B2)telt het aantal keren dat B2 (ARG) voorkomt in kolom B.
U gebruikt een hulpkolom om de eerste vijf tekens van kolom A te isoleren, zoals in deze afbeelding:
Vervolgens moet u op de een of andere manier aangeven dat u voor ARG alleen geïnteresseerd bent in de cellen F2: F11 om het aantal unieke items te vinden. Over het algemeen zou u deze waarde vinden met behulp van de matrixformule die in deze afbeelding wordt weergegeven:
U gebruikt cel C3 tijdelijk alleen om de formule weer te geven; je kunt zien dat het niet aanwezig is in C3 in eerdere figuren. (U leert binnenkort hoe deze formule werkt.)
Dus wat is de formule in C2, C12 en C17? Het verrassende (en coole) antwoord wordt getoond in deze figuur:
Whoa! Hoe werkt dit?
Bekijk Antwoord in de gedefinieerde namen in deze afbeelding:
Het is dezelfde formule uit een eerdere afbeelding, maar in plaats van het bereik F2: F11 te gebruiken, gebruikt het een bereik met de naam Rg. De formule was ook een matrixformule, maar benoemde formules worden behandeld alsof het matrixformules zijn! Dat wil zeggen, =Answerwordt niet ingevoerd met Ctrl + Shift + Enter, maar wordt gewoon ingevoerd zoals gewoonlijk.
Dus hoe wordt Rg gedefinieerd? Als cel C1 is geselecteerd (wat een belangrijke stap is om deze truc te begrijpen), wordt deze gedefinieerd zoals in deze afbeelding:
Dat is =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details is de naam van het blad, maar u kunt deze formule bekijken zonder de lange bladnaam. Een gemakkelijke manier om dit te doen, is door het blad tijdelijk iets eenvoudigs te noemen, zoals x, en vervolgens opnieuw naar de gedefinieerde naam te kijken:
Deze formule is gemakkelijker te lezen!
U kunt zien dat deze formule overeenkomt met $ B1 (let op de relatieve verwijzing naar de huidige rij) voor kolom B en 1 aftrekt. U trekt 1 af omdat u OFFSET gebruikt van F1. Nu je de formule voor C kent, kijk eens naar die voor C2:
Het MATCH($B2,$B:$B,0)deel van de formule is 2, dus de formule (zonder de verwijzing naar de bladnaam) is:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
of:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
of:
=OFFSET($F$1,1,0,10,1)
Omdat het COUNTIF($B:$B,$B2)10 is, zijn er 10 ARG's. Dit is bereik F2: F11. Als cel C2 is geselecteerd en u drukt op F5 om naar Rg te gaan, ziet u dit:
Als de startcel C12 was, geeft het drukken op F5 om naar Rg te gaan dit:
Dus nu, met Answer gedefinieerd als =SUM(1/COUNTIF(rg,rg)), bent u helemaal klaar!
Laten we aan de hand van een veel eenvoudiger voorbeeld eens nader bekijken hoe deze formule werkt. Normaal gesproken is de syntaxis voor AANTAL.ALS =COUNTIF(range,criteria), zoals =COUNTIF(C1:C10, "b")in deze afbeelding:
Dit zou 2 geven als het aantal b's in het bereik. Maar als u het bereik zelf als criterium doorgeeft, wordt elk item in het bereik als criterium gebruikt. Als u dit gedeelte van de formule markeert:
en druk op F9, je ziet:
Elk item in de reeks wordt geëvalueerd, en deze reeks getallen betekent dat er één a is en dat er twee b's, drie c's en vier d's zijn. Deze getallen zijn onderverdeeld in 1, wat 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼ oplevert, zoals je hier kunt zien:
Dus je hebt 2 helften, 3 derde, 4 vierde en 1 geheel, en als je ze optelt, levert dit 4 op. Als een item zeven keer werd herhaald, dan zou je 7 zevende hebben enzovoort. Best wel gaaf! (Petje af voor David Hager voor het ontdekken / uitvinden van deze formule.)
Maar wacht even. Zoals het er nu uitziet, hoeft u deze formule alleen in C2, C12 en C17 in te voeren. Zou het niet beter zijn als je het in C2 zou kunnen invoeren en invullen en alleen in de juiste cellen zou laten zien? In feite kunt u dit doen. U kunt de formule in C2 wijzigen =IF(B1B2,Answer,"")en als u dat invult, doet het het werk:
Maar waarom zou je hier stoppen? Waarom verandert u de formule niet in een benoemde formule, zoals hier wordt weergegeven:
Om dit te laten werken, moet cel C2 de actieve cel zijn (of de formule moet anders zijn). Nu kunt u de formules van kolom C vervangen door =Answer2:
Je kunt zien dat C3 dat wel heeft =Answer2, net als alle cellen in kolom C. Waarom ga je hiermee niet verder in kolom D? De formule in D2, na ook de vergelijking op B1 en B2 toe te passen, wordt hier weergegeven:
Dus als je cel D2 geselecteerd houdt en een andere formule definieert, zeg dan Answer3:
dan kun je =Answer3cel D2 invoeren en invullen:
Hier is het bovenste deel van het werkblad, met formules die worden weergegeven, gevolgd door hetzelfde screenshot met waarden die worden weergegeven:
Wanneer andere mensen dit proberen uit te zoeken, kunnen ze in eerste instantie hun hoofd krabben!
Dit gastartikel is van Excel MVP Bob Umlas. Het komt uit het boek More Excel Outside the Box. Om de andere onderwerpen in het boek te zien, klik hier.
