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:

• 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:

Fler artiklar: