Tidigare har vi lärt oss hur man räknar unika värden i ett intervall. Vi lärde oss också hur man extraherar unika värden från ett intervall. I den här artikeln lär vi oss hur man räknar unikt värde i intervall med skick i excel.
Generisk formel
{= SUMMA (-(FREKVENS (OM (villkor, MATCH (intervall, intervall, 0)), RAD (intervall) -ROW (första cell i intervallet) +1)> 0))}
Det är en matrisformel, använd CTRL+SKIFT+ENTER
Skick : Kriterierna för vilka du vill få unika värden.
Räckvidd : intervall där du vill få unika värden.
firstCell i intervallet: Det är referensen för den första cellen i räckvidd. Om intervallet är A2: A10 är det A2.
Exempel:
Här har jag dessa uppgifter om namn. Motsvarande klasser nämns i den intilliggande kolumnen. Vi måste räkna unika namn i varje klass.
Använd den ovanstående generella formeln och skriv den här formeln i E2
{= SUMMA (-(FREKVENS (OM (B2: B19 = "Klass 1", MATCH (A2: A19, A2: A19,0)), RAD (A2: A19) -ROW (A2) +1)> 0 ))}}
Ovanstående formel returnerar det unika värdet i excelintervall A2: A19 under villkoret B2: B19 = "Klass 1".
För att få unika värden i olika klasser, ändra kriterierna. Vi har hårdkodat det här, men du kan också ge cellreferens. Använd namngivna intervall eller absolut referens för intervall, om du inte vill att de också ska ändras.
Hur det fungerar?
Låt oss bryta ner det inifrån.
OM(B2: B19 = "Klass 1",MATCH(A2: A19, A2: A19,0))
B2: B19 = "Klass 1": Denna del returnerar en uppsättning av sant och falskt. SANT för varje match.
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE….}
MATCH(A2: A19, A2: A19,0): denna del returnerar den första platsen för varje värde i intervallet A2: A19 enligt MATCHs egendom.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Nu för varje TRUE -värde får vi positionen och för false får vi FALSE. Så för hela IF -uttalandet får vi
{1; FALSKT; 1; FALSKT; 5; 4; FALSKT; FALSKT; FALSKT; 2; FALSKT; FALSKT; 5; FALSKT; 1; 8; FALSKT}.
Därefter går vi till frekvensdelen.
FREKVENS(OM(B2: B19 = "Klass 1",MATCH(A2: A19, A2: A19,0)),RAD(A2: A19)-RAD(A2) +1)
RAD (A2: A19): Detta returnerar radnumret för varje cell i intervall 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): Nu subtraherar vi det första radnumret från varje radnummer. Detta returnerar matrisen med serienummer från 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Eftersom vi vill ha serienummer från 1, lägger vi till 1 i det.
RAD (A2: A19) -ROW (A2) +1. Detta ger oss en rad serienummer från 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Detta hjälper oss att få en unik räkna på skick.
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})
Detta returnerar frekvensen för varje nummer i en given matris. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Här indikerar varje positivt tal förekomst av unikt värde när kriterier är uppfyllda. Vi måste räkna värden som är större än 0 i denna array. För det kontrollerar vi det med> 0. Detta returnerar SANT och FALSKT. Vi konverterar true false med - (dubbel binär operator).
BELOPP(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) detta översätts till SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
Och slutligen får vi det unika antalet namn inom intervallet på kriterier som 5.
Jag vet att det är lite komplext att förstå men du kontrollerar det från formelutvärderingsalternativ.
För att räkna unika värden med flera kriterier kan vi använda boolsk logik:
Räkna unikt värde med flera kriterier med och logik
{= SUMMA (-(FREKVENS (OM (villkor1 * Villkor2, MATCH (intervall, intervall, 0)), RAD (intervall) -ROW (första cell i intervallet) +1)> 0))}
Ovan generisk formel kan räkna unika värden på flera villkor och när alla är sanna.
Räkna unikt värde med flera kriterier med eller logik
{= SUMMA (-(FREKVENS (OM (villkor1 + villkor2, MATCH (intervall, intervall, 0)), RAD (intervall) -ROW (första cell i intervallet) +1)> 0))}
Denna generiska formel kan användas för att räkna unika värden med Or -logik. Det betyder att det kommer att räkna om något av villkoren är sant.
Så ja killar, så här räknar du unika värden i ett intervall på flera villkor. Det här är lite komplext men det är snabbt. När du väl börjar använda det får du veta hur det fungerar.
Om du har några tvivel om denna excelformelartikel, låt mig veta i kommentarfältet nedan.
Nedladdning fil:
Hur man räknar unika värden i Excel med kriterierExcel -formel för att extrahera unika värden från en lista
Räkna unika värden i Excel
Populära artiklar:
VLOOKUP -funktionen i Excel
COUNTIF i Excel 2016
Hur man använder SUMIF -funktionen i Excel
