Generisk formel
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Resumé
For at tælle unikke værdier med en eller flere betingelser kan du bruge en formel baseret på UNIQUE og FILTER. I det viste eksempel er formlen i H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
der returnerer 3, da der er tre unikke navne i B6: B15 tilknyttet Omega-projektet.
Bemærk: denne formel kræver dynamiske matrixformler, der kun er tilgængelige i Excel 365. Med en ældre version af Excel kan du bruge mere komplekse alternative formler.
Forklaring
I kernen bruger denne formel UNIQUE-funktionen til at udtrække unikke værdier, og FILTER-funktionen anvender kriterier.
Arbejder indefra og ud, FILTER-funktionen bruges til at anvende kriterier og udtrække kun navne, der er knyttet til "Omega" -projektet:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Resultatet fra FILTER er en matrix som denne:
("Jim";"Jim";"Carl";"Sue";"Carl")
Dernæst bruges UNIQUE-funktionen til at fjerne dubletter:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
hvilket resulterer i et nyt array som dette:
("Jim";"Carl";"Sue") // after UNIQUE
På dette tidspunkt har vi en unik liste over navne, der er knyttet til Omega, og vi skal bare tælle dem. Af nedenstående forklaringer gør vi dette med LEN-funktionen og SUM-funktionen. For at gøre tingene klare omskriver vi først formlen for at inkludere den unikke liste:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
LEN-funktionen får længden af hvert element på listen og returnerer en række længder:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Dernæst kontrollerer vi, om længder er større end nul:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
Og brug en dobbelt negativ til at tvinge de SANDE og FALSE værdier til 1s og 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Endelig tilføjer vi resultaterne med SUM-funktionen:
=SUM((1;1;1)) // returns 3
Denne matrix leveres direkte til COUNTA-funktionen, som returnerer en endelig optælling:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Bemærk, at fordi vi kontrollerer længden på hver vare, der returneres af UNIK, ignoreres tomme eller tomme celler, der opfylder kriterierne. Denne formel er dynamisk og genberegnes straks, hvis kildedata ændres.
Tæl unik med flere kriterier
Hvis du vil tælle unikke værdier baseret på flere kriterier, kan du udvide "inkluder" -logikken i FILTER. Hvis du f.eks. Kun vil tælle unikke navne til Omega-projektet i juni, skal du bruge:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Dette er et eksempel på brug af boolsk logik til at anvende mere end en betingelse. Fremgangsmåden forklares mere detaljeret her.
For flere detaljer, se denne træningsvideo: Sådan filtreres med flere kriterier.
COUNTA
Det er muligt at skrive en enklere formel, der svarer på COUNTA-funktionen. En vigtig advarsel er dog, at COUNTA returnerer 1, når der ikke er nogen matchende værdier. Dette skyldes, at FILTER-funktionen returnerer en fejl, når ingen data matcher kriterierne, og denne fejl ender med at blive talt af funktionen COUNTA. Den grundlæggende formel for COUNTA ser sådan ud:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Igen returnerer denne formel 1, når der ikke er nogen matchende data. Det vil også omfatte tomme celler, der opfylder kriterierne. Formlen baseret på LEN og SUM er en bedre mulighed.
Ingen dynamiske arrays
Hvis du bruger en ældre version af Excel uden understøttelse af dynamisk array, kan du bruge en mere kompleks formel. For en mere generel diskussion af dynamiske matrixalternativer, se: Alternativer til dynamiske matrixformler.
