Tidligere lærte vi, hvordan man tæller unikke værdier i et område. Vi lærte også, hvordan man udtrækker unikke værdier fra et område. I denne artikel lærer vi, hvordan man tæller unik værdi inden for rækkevidde med tilstand i excel.
Generisk formel
{= SUM (-(FREKVENS (HVIS (betingelse, MATCH (område, område, 0)), RÆKKE (område) -ROW (førsteCelle i område) +1)> 0))}
Det er en matrixformel, brug CTRL+SKIFT+ENTER
Tilstand : Kriterierne, du vil have unikke værdier efter.
Rækkevidde : område, hvor du vil have unikke værdier.
firstCelle i rækkevidde: Det er referencen til den første celle i rækkevidde. Hvis området er A2: A10, er det A2.
Eksempel:
Her har jeg disse data med navne. De tilsvarende klasser er nævnt i den tilstødende kolonne. Vi skal tælle unikke navne i hver klasse.
Brug ovenstående generiske formel til at skrive denne formel i E2
{= SUM (-(FREKVENS (HVIS (B2: B19 = "Klasse 1", MATCH (A2: A19, A2: A19,0)), RÆK (A2: A19) -ROW (A2) +1)> 0 ))}}
Ovenstående formel returnerer den unikke værdi i excel -område A2: A19 på betingelse af B2: B19 = "Klasse 1".
For at få unikke værdier i forskellige klasser skal du ændre kriterierne. Vi har hardkodet det her, men du kan også give cellehenvisning. Brug navngivne områder eller absolut henvisning til områder, hvis du ikke vil have, at de også ændres.
Hvordan det virker?
Lad os bryde det ned indefra.
HVIS(B2: B19 = "Klasse 1",MATCH(A2: A19, A2: A19,0))
B2: B19 = "Klasse 1": Denne del returnerer en vifte af sand og falsk. SAND for hver kamp.
{SAND; FALSK; SAND; FALSK; SAND; SAND; FALSK….}
MATCH(A2: A19, A2: A19,0): denne del returnerer den første placering af hver værdi i området A2: A19 ifølge MATCHs ejendom.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Nu for hver SAND værdi får vi positionen, og for falsk får vi FALSK. Så for hele IF -erklæringen får vi
{1; FALSK; 1; FALSK; 5; 4; FALSK; FALSK; FALSK; 2; FALSK; FALSK; 5; FALSK; 1; 8; FALSK}.
Dernæst går vi til frekvensdel.
FREKVENS(HVIS(B2: B19 = "Klasse 1",MATCH(A2: A19, A2: A19,0)),RÆKKE(A2: A19)-RÆKKE(A2) +1)
RÆKKE (A2: A19): Dette returnerer rækkenummeret for hver celle i område A2: A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
RÆKKE (A2: A19) -ROW (A2): Nu trækker vi det første rækkenummer fra hvert rækkenummer. Dette returnerer en matrix med serienummer fra 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Da vi vil have serienummer fra 1, tilføjer vi 1 til det.
RÆK (A2: A19) -ROW (A2) +1. Dette giver os en række serienumre fra 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Dette vil hjælpe os med at få en unik regning på tilstand.
Nu har vi: FREKVENS({1; FALSK; 1; FALSK; 5; 4; FALSK; FALSK; FALSK; 2; FALSK; FALSK; 5; FALSK; 1; 8; FALSK},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Dette returnerer frekvensen af hvert tal i et givet array. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Her angiver hvert positivt tal forekomst af unik værdi, når kriterierne er opfyldt. Vi skal tælle værdier større end 0 i dette array. For at kontrollere det ved> 0. Dette vil returnere SANDT og FALSKT. Vi konverterer sand falsk ved hjælp af - (dobbelt binær operator).
SUM(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) dette oversættes til SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
Og endelig får vi det unikke antal navne inden for rækkevidde på kriterier som 5.
Jeg ved, at det er lidt komplekst at forstå, men du tjekker det fra formelevalueringsindstilling.
For at tælle unikke værdier med flere kriterier kan vi bruge boolsk logik:
Tæl unik værdi med flere kriterier med og logik
{= SUM (-(FREKVENS (HVIS (betingelse1 * Betingelse2, MATCH (område, område, 0)), RÆKKE (område) -ROW (førsteCelle i område) +1)> 0))}
Ovenfor generisk formel kan tælle unikke værdier på flere betingelser, og når alle er sande.
Tæl unik værdi med flere kriterier med eller logik
{= SUM (-(FREKVENS (HVIS (betingelse1 + Betingelse2, MATCH (område, område, 0)), RÆKKE (rækkevidde) -ROW (førsteCelle i område) +1)> 0))}
Denne generiske formel kan bruges til at tælle unikke værdier med Or logik. Det betyder, at det vil tælle, om nogen af betingelserne er sande.
Så ja fyre, sådan tæller du unikke værdier i et område på flere betingelser. Dette er lidt kompliceret, men det er hurtigt. Når du begynder at bruge det, får du at vide, hvordan det fungerer.
Hvis du er i tvivl om denne Excel -formelartikel, så lad mig vide det i kommentarfeltet herunder.
Download fil:
Sådan tælles unikke værdier i Excel med kriterierExcel -formel til at udtrække unikke værdier fra en liste
Tæl unikke værdier i Excel
Populære artikler:
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Sådan bruges SUMIF -funktionen i Excel
