Excel

Tæl unikke værdier med kriterier

Count Unique Values With Criteria

Excel-formel: Tæl unikke værdier med kriterierGenerisk 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å ENESTÅENDE 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 , kun tilgængelig 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.

hvordan man vælger flere kolonner i Excel Mac

Arbejder indefra og ud, den FILTER-funktion 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 et array sådan her:

 
{'Jim''Jim''Carl''Sue''Carl'}

Dernæst UNIK funktion bruges 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))

Det LEN-funktion får længden af ​​hvert element på listen og returnerer en række længder:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

Dernæst kontrollerer vi, om længder er større end nul:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

Og brug en dobbelt negativ for at tvinge de SANDE og FALSE værdier til 1s og 0s:

 
--({TRUETRUETRUE}) // returns {111}

Endelig tilføjer vi resultaterne med SUM-funktion :

vba større end eller lig med
 
= SUM ({111}) // 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 af ​​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 'inkludere' 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 boolsk logik at anvende mere end en betingelse. Tilgangen er forklaret mere detaljeret her .

kopier pivottabeldata til et andet regneark

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-funktion . Imidlertid er en vigtig advarsel, 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 COUNTA-formel 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 .

Dynamiske matrixformler er tilgængelige i Office 365 kun. Forfatter Dave Bruns


^