Generieke formule
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Samenvatting
Om unieke tekstwaarden in een bereik met criteria te tellen, kunt u een matrixformule gebruiken op basis van de functies FREQUENCY en MATCH. In het getoonde voorbeeld is de formule in G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
wat 3 oplevert, aangezien er drie verschillende mensen aan project Omega hebben gewerkt.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
Dit is een complexe formule die FREQUENCY gebruikt om numerieke waarden te tellen die zijn afgeleid met de MATCH-functie. Werkend van binnen naar buiten, wordt de MATCH-functie gebruikt om de positie van elke waarde die in de gegevens verschijnt, te krijgen:
MATCH(B5:B11,B5:B11,0)
Het resultaat van MATCH is een array als deze:
(1;1;3;1;1;6;7)
Omdat VERGELIJKEN altijd de positie van de eerste overeenkomst retourneert, retourneren waarden die meer dan eens in de gegevens voorkomen dezelfde positie. Bijvoorbeeld, omdat "Jim" 4 keer in de lijst voorkomt, verschijnt hij 4 keer in deze array als het nummer 1.
Buiten de MATCH-functie wordt de ALS-functie gebruikt om criteria toe te passen, wat in dit geval inhoudt dat wordt getest of het project "omega" is (uit cel G5):
IF(C5:C11=G5 // filter on "omega"
De IF-functie werkt als een filter en laat alleen de waarden van MATCH door als ze zijn geassocieerd met "omega". Het resultaat is een array als deze:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
De gefilterde array wordt rechtstreeks aan de functie FREQUENCY geleverd als het argument data_array . Vervolgens wordt de functie RIJ gebruikt om een opeenvolgende lijst met getallen op te bouwen voor elke waarde in de gegevens:
ROW(B3:B12)-ROW(B3)+1
Dit creëert een array zoals deze:
(1;2;3;4;5;6;7;8;9;10)
die het argument bins_array in FILTER wordt. Op dit punt hebben we:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY retourneert een reeks getallen die een telling aangeven voor elke waarde in de gegevensmatrix, geordend op bak. Als een getal al is geteld, retourneert FREQUENCY nul. Het resultaat van FREQUENCY is een array als deze:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Opmerking: FREQUENCY retourneert altijd een array met één item meer dan de bins_array .
Op dit punt kunnen we de formule als volgt herschrijven:
=SUM(--((2;0;0;0;0;1;1;0)>0))
We controleren op waarden groter dan nul, waardoor de getallen worden geconverteerd naar WAAR of ONWAAR:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Vervolgens gebruiken we een dubbel negatief om de logische waarden naar 1s en 0s te dwingen:
=SUM((1;0;0;0;0;1;1;0))
Ten slotte retourneert de functie SOM 3 als het eindresultaat.
Opmerking: dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Omgaan met lege cellen in het bereik
Als cellen in het bereik leeg zijn, moet u de formule aanpassen om te voorkomen dat lege cellen worden doorgegeven aan de MATCH-functie, wat een fout veroorzaakt. U kunt dit doen door nog een geneste ALS-functie toe te voegen om te controleren op lege cellen:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Met twee criteria
Als u twee criteria heeft, kunt u de logica van de formule uitbreiden door nog een geneste IF toe te voegen:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Waarbij c1 = criterium1, c2 = criterium2 en vals = het waardenbereik.
Met booleaanse logica
Met booleaanse logica kunt u geneste IF's verminderen:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Dit maakt het gemakkelijker om aanvullende criteria toe te voegen en te beheren.
