Tidligere lærte vi å telle unike verdier i et område. Vi lærte også å trekke ut unike verdier fra et område. I denne artikkelen lærer vi hvordan du teller unik verdi i område med tilstand i excel.
Generisk formel
{= SUMME (-(FREKVENS (HVIS (tilstand, MATCH (område, område, 0)), RAD (område) -ROW (førsteselle i område) +1)> 0))}
Det er en matriseformel, bruk CTRL+SKIFT+ENTER
Tilstand : Kriteriene du vil få unike verdier på.
Område : område der du vil få unike verdier.
firstCelle i rekkevidde: Det er referansen til den første cellen i område. Hvis området er A2: A10, er det A2.
Eksempel:
Her har jeg disse navnene. De tilsvarende klassene er nevnt i den tilstøtende kolonnen. Vi må telle unike navn i hver klasse.
Bruk den generiske formelen ovenfor til å skrive denne formelen i E2
{= SUMME (-(FREKVENS (HVIS (B2: B19 = "Klasse 1", MATCH (A2: A19, A2: A19,0)), RAD (A2: A19) -ROW (A2) +1)> 0 ))}}
Formelen ovenfor returnerer den unike verdien i Excel -område A2: A19 på betingelse av B2: B19 = "Class 1".
For å få unike verdier i forskjellige klasser, endre kriteriene. Vi har hardkodet det her, men du kan også gi cellehenvisning. Bruk navngitte områder eller absolutt referanse for områder, hvis du ikke vil at de skal endres for mye.
Hvordan det fungerer?
La oss bryte det ned fra innsiden.
HVIS(B2: B19 = "Klasse 1",KAMP(A2: A19, A2: A19,0))
B2: B19 = "Klasse 1": Denne delen returnerer en rekke sanne og usanne. SANN for hver kamp.
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE….}
KAMP(A2: A19, A2: A19,0): denne delen vil returnere den første plasseringen av hver verdi i området A2: A19 i henhold til MATCHs eiendom.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Nå for hver SANN verdi vil vi få posisjonen og for falsk vil vi få FALSK. Så for hele IF -uttalelsen får vi
{1; FALSK; 1; FALSK; 5; 4; FALSK; FALSK; FALSK; 2; FALSK; FALSK; 5; FALSK; 1; 8; FALSK}.
Deretter går vi til frekvensdel.
FREKVENS(HVIS(B2: B19 = "Klasse 1",KAMP(A2: A19, A2: A19,0)),RAD(A2: A19)-RAD(A2) +1)
RAD (A2: A19): Dette returnerer radnummeret til 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}
RAD (A2: A19) -ROW (A2): Nå trekker vi det første radnummeret fra hvert radnummer. Dette returnerer matrisen med serienummer fra 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Siden vi ønsker å ha serienummer fra 1, legger vi til 1 i det.
RAD (A2: A19) -ROW (A2) +1. Dette gir oss en rekke serienumre fra 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Dette vil hjelpe oss med å få unik telling på tilstand.
Nå 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 for hvert tall i en gitt matrise. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Her indikerte hvert positivt tall forekomst av unik verdi når kriteriene er oppfylt. Vi må telle verdier større enn 0 i denne matrisen. For det sjekker vi det med> 0. Dette vil returnere SANN og FALSK. Vi konverterer true false ved hjelp av - (dobbel 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 oversetter til SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
Og til slutt får vi det unike antallet navn i rekkevidde på kriterier som 5.
Jeg vet at det er litt komplekst å forstå, men du sjekker det fra formelevalueringsalternativet.
For å telle unike verdier med flere kriterier kan vi bruke boolsk logikk:
Tell unik verdi med flere kriterier med og logikk
{= SUMMER (-(FREKVENS (HVIS (betingelse1 * Betingelse2, MATCH (område, område, 0)), RAD (område) -ROW (førsteCelle i område) +1)> 0))}
Over generisk formel kan telle unike verdier på flere forhold og når alle er sanne.
Tell unik verdi med flere kriterier med eller logikk
{= SUMME (-(FREKVENS (HVIS (betingelse1 + Betingelse2, MATCH (område, område, 0)), RAD (område) -ROW (førsteCelle i område) +1)> 0))}
Denne generiske formelen kan brukes til å telle unike verdier med Or -logikk. Det betyr at det vil telle om noen av betingelsene er sanne.
Så ja gutta, slik teller du unike verdier i et område på flere forhold. Dette er litt komplisert, men det er raskt. Når du begynner å bruke det, får du vite hvordan det fungerer.
Hvis du er i tvil om denne excelformelartikkelen, så gi meg beskjed i kommentarfeltet nedenfor.
Last ned fil:
Hvordan telle unike verdier i Excel med kriterierExcel -formel for å trekke ut unike verdier fra en liste
Tell unike verdier i Excel
Populære artikler:
VLOOKUP -funksjonen i Excel
COUNTIF i Excel 2016
Slik bruker du SUMIF -funksjonen i Excel
