Generisk formel
=SUMPRODUCT(1/COUNTIF(data,data))
Sammanfattning
För att räkna antalet unika värden i ett cellområde kan du använda en formel baserad på COUNTIF- och SUMPRODUCT-funktionerna. I exemplet visar formeln i F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Förklaring
Arbetar inifrån och ut, COUNTIF är konfigurerad till värden i intervallet B5: B14, med alla dessa samma värden som kriterier:
COUNTIF(B5:B14,B5:B14)
Eftersom vi ger 10 värden för kriterier får vi tillbaka en matris med tio resultat så här:
(3;3;3;2;2;3;3;3;2;2)
Varje nummer representerar ett antal - "Jim" visas 3 gånger, "Sue" visas 2 gånger, och så vidare.
Denna matris är konfigurerad som en delare med 1 som täljare. Efter delning får vi en annan array:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Alla värden som förekommer bara en gång i intervallet visas som 1s, men värden som förekommer flera gånger visas som bråkvärden som motsvarar multipeln. (dvs ett värde som visas fyra gånger i data genererar 4 värden = 0,25).
Slutligen summerar SUMPRODUCT-funktionen alla värden i matrisen och returnerar resultatet.
Hantera tomma celler
Ett sätt att hantera tomma eller tomma celler är att justera formeln enligt följande:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Genom att sammanfoga en tom sträng ("") till data förhindrar vi att nollor hamnar i den matris som skapats av COUNTIF när det finns tomma celler i data. Detta är viktigt, eftersom en noll i delaren kommer att få formeln att kasta ett # DIV / 0-fel. Det fungerar för att använda en tom sträng ("") för kriterier räknar tomma celler.
Men även om den här versionen av formeln inte ger ett # DIV / 0-fel när det finns tomma celler, kommer det att inkludera tomma celler i räkningen. Om du vill utesluta tomma celler från räkningen, använd:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Detta har till följd att antalet tomma celler avbryts genom att räknaren blir noll för associerade räkningar.
Långsam prestanda?
Detta är en cool och elegant formel, men den beräknar mycket långsammare än formler som använder FREQUENCY för att räkna unika värden. För större datamängder kanske du vill byta till en formel baserad på FREKVENS-funktionen. Här är en formel för numeriska värden och en för textvärden.
