Räkna unika numeriska värden baserat på kriterier i Excel
I Excel -kalkylblad kan du drabbas av ett problem som räknar antalet unika numeriska värden baserat på ett specifikt villkor. Till exempel, hur kan jag räkna de unika Qty-värdena för produkten ”T-shirt” från rapporten enligt nedanstående skärmdump? I den här artikeln kommer jag att visa några formler för att uppnå denna uppgift i Excel.
- Räkna unika numeriska värden baserat på kriterier i Excel 2019, 2016 och tidigare
- Räkna unika numeriska värden baserat på kriterier i Excel 365
Räkna unika numeriska värden baserat på kriterier i Excel 2019, 2016 och tidigare
I Excel 2019 och tidigare versioner kan du kombinera funktionerna SUMMA, FREKVENS och IF för att skapa en formel för att räkna unika värden baserat på kriterier, den generiska syntaxen är:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Cellerna som matchar de kriterier du angav;
- criteria: Villkoret som du vill räkna unika värden utifrån;
- range: Cellernas intervall med unika värden som ska räknas.
Applicera nedanstående formel i en tom cell och tryck på Ctrl + Skift + Enter nycklar för att få rätt resultat, se skärmdump:
Förklaring av formeln:
=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))
- IF(A2:A12=E2,C2:C12): Denna IF-funktion returnerar värdet i kolumn C om produkten i kolumn A är "T-shirt", resultatet är en array som denna: {FALSE; 300; 500; FALSE; 400; FALSE; 300; FALSE; FALSE; FALSKT; 350}.
- FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): FREKVENS -funktionen används för att räkna vart och ett av de numeriska värdena i matrislistan och returnera resultatet så här: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Testa om varje värde i arrayen är större än 0 och få resultatet så här: {FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}. Och sedan omvandlar det dubbla negativa tecknet SANT och FALS till 1: or och 0: or och returnerar en matris så här: {0; 1; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Slutligen, använd SUM -funktionen för att lägga till dessa värden och få det totala antalet: 4.
Tips:
Om du vill räkna de unika värdena baserat på mer än ett villkor behöver du bara lägga till andra kriterier i formeln med * tecken:
Räkna unika numeriska värden baserat på kriterier i Excel 365
I Excel 365 kan kombinationen av funktionerna RADER, UNIK och FILTER hjälpa till att räkna unika numeriska värden baserat på kriterier, den generiska syntaxen är:
- range: Cellernas intervall med unika värden som ska räknas.
- criteria_range: Cellerna som matchar de kriterier du angav;
- criteria: Villkoret som du vill räkna unika värden utifrån;
Kopiera eller ange följande formel i en cell och tryck på ange för att returnera resultatet, se skärmdump:
Förklaring av formeln:
=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))
- A2: A12 = E2: Detta uttryck kontrollerar om värdet i cell E2 finns i intervallet A2: A12 och får detta resultat: {FALSKT; SANT; SANT; FALSKT; SANT; FALSKT; SANT; FALSKT; FALSKT; FALSKT} SANT}.
- FILTER(C2:C12,A2:A12=E2): FREKVENS -funktionen används för att räkna vart och ett av de numeriska värdena i matrislistan och returnera resultatet så här: {0; 2; 1; 1; 1; 0; 0; 0; 0; 0; 0; 0} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Här används funktionen UNIK för att extrahera unika värden från listmatrisen för att få detta resultat: {300; 500; 400; 350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Funktionen RADER returnerar antalet rader baserat på ett cellintervall eller matris, så resultatet är: 4.
Tips:
1. Om det matchade värdet inte finns i dataområdet får du ett felvärde, för att ersätta felvärdet med 0, använd följande formel:
2. För att räkna de unika värdena baserade på mer än ett villkor behöver du bara lägga till andra kriterier i formeln med * tecken så här:
Relativ funktion som används:
- BELOPP:
- Excel SUMM -funktionen returnerar summan av angivna värden.
- FREKVENS:
- FREKVENS -funktionen beräknar hur ofta värden förekommer inom ett intervall av värden och returnerar sedan ett vertikalt antal siffror.
- RADER:
- ROWS -funktionen returnerar antalet rader i en given referens eller array.
- UNIK:
- Funktionen UNIK returnerar en lista med unika värden i en lista eller intervall.
- FILTRERA:
- FILTER -funktionen hjälper till att filtrera en rad data baserat på kriterier du definierar.
Fler artiklar:
- Räkna unika numeriska värden eller datum i en kolumn
- Om du antar att du har en lista med nummer som innehåller några dubbletter, nu vill du räkna antalet unika värden eller värdena visas bara en gång i listan som visas nedan. I den här artikeln kommer vi att prata om några användbara formler för att lösa denna uppgift i Excel snabbt och enkelt.
- Räkna alla matchningar / dubbletter mellan två kolumner
- Att jämföra två datakolumner och räkna alla matchningar eller dubbletter i de två kolumnerna kan vara en vanlig uppgift för de flesta av oss. Till exempel har du två namnkolumner, vissa namn visas både i den första och den andra kolumnen, nu vill du räkna alla matchade namn (matchningarna som finns någonstans i de två kolumnerna) mellan två kolumner enligt nedanstående skärmdump, denna handledning kommer att introducera några formler för att uppnå detta mål i Excel.
- Räkna antalet celler är lika med ett av många värden
- Om jag antar att jag har en lista över produkter i kolumn A, nu vill jag få det totala antalet specifika produkter Apple, Grape and Lemon som anges i intervall C4: C6 från kolumn A enligt nedanstående skärmdump. Normalt fungerar inte Excel i de här funktionerna i enkla COUNTIF- och COUNTIFS -funktioner. Denna artikel, jag kommer att prata om hur man löser detta jobb snabbt och enkelt med kombinationen av SUMPRODUCT och COUNTIF -funktioner.
De bästa Office-produktivitetsverktygen
Kutools för Excel - Hjälper dig att sticka ut från mängden
Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)
- En sekund att växla mellan dussintals öppna dokument!
- Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
- Ökar din produktivitet med 50 % när du visar och redigerar flera dokument.
- Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
