Generisk formel
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Sammanfattning
För att räkna unika numeriska värden i ett intervall kan du använda en formel baserad på FREKVENS-, SUMMA- och IF-funktionerna. I exemplet som visas visas antalet anställda i intervallet B5: B14. Formeln i G6 är:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
som returnerar 2, eftersom det finns 2 unika anställda id i byggnad A.
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, såvida du inte använder Excel 365.
Förklaring
Obs! Innan Excel 365 hade Excel inte en dedikerad funktion för att räkna unika värden. Denna formel visar ett sätt att räkna unika värden, så länge de är numeriska. Om du har textvärden eller en blandning av text och siffror måste du använda en mer komplicerad formel.
Excel FREQUENCY-funktionen returnerar en frekvensfördelning, som är en sammanfattningstabell som innehåller frekvensen för numeriska värden, organiserade i "lagerplatser". Vi använder det här som ett rondell för att räkna unika numeriska värden. För att tillämpa kriterier använder vi funktionen IF.
Arbetar inifrån och ut, filtrerar vi först värden med IF-funktionen:
IF(C5:C14="A",B5:B14) // filter on building A
Resultatet av den här operationen är en array som denna:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Observera att alla id i byggnad B nu är FALSKA. Denna array levereras direkt till FREQUENCY-funktionen som data_array . För bins_array levererar vi själva id: n:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Med den här konfigurationen returnerar FREQUENCY arrayen nedan:
(4;0;0;0;2;0;0;0;0;0;0)
Resultatet är lite kryptiskt, men innebörden är 905 visas fyra gånger och 773 två gånger. FALSE-värden ignoreras automatiskt.
FREQUENCY har en speciell funktion som automatiskt returnerar noll för alla siffror som redan har dykt upp i dataarrayen, varför värdena är noll när ett tal har påträffats. Det här är funktionen som gör att detta tillvägagångssätt fungerar.
Därefter testas vart och ett av dessa värden större än noll:
(4;0;0;0;2;0;0;0;0;0;0)>0
Resultatet är en array som denna:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Varje SANT i listan representerar ett unikt nummer i listan, och vi behöver bara lägga till de Sanna värdena med SUM. SUM kommer dock inte att lägga till logiska värden i en matris, så vi måste först tvinga värdena till 1 eller noll. Detta görs med det dubbla negativa (-). Resultatet är en matris av endast 1 eller 0:
(1;0;0;0;1;0;0;0;0;0;0)
Slutligen lägger SUM upp dessa värden och returnerar summan, som i detta fall är 2.
Flera kriterier
Du kan utöka formeln för att hantera flera kriterier så här:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
