Generisk formel
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Resumé
For at tælle unikke numeriske værdier i et interval kan du bruge en formel baseret på funktionerne FREKVENS, SUM og HVIS. I det viste eksempel vises medarbejdernumre i området B5: B14. Formlen i G6 er:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
der returnerer 2, da der er 2 unikke medarbejder-id'er i bygning A.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, medmindre du bruger Excel 365.
Forklaring
Bemærk: Før Excel 365 havde Excel ikke en dedikeret funktion til at tælle unikke værdier. Denne formel viser en måde at tælle unikke værdier på, så længe de er numeriske. Hvis du har tekstværdier eller en blanding af tekst og tal, skal du bruge en mere kompliceret formel.
Excel FREQUENCY-funktionen returnerer en frekvensfordeling, som er en oversigtstabel, der indeholder hyppigheden af numeriske værdier, organiseret i "bakker". Vi bruger det her som en rundkørsel til at tælle unikke numeriske værdier. For at anvende kriterier bruger vi IF-funktionen.
Når vi arbejder indefra og ud, filtrerer vi først værdier med IF-funktionen:
IF(C5:C14="A",B5:B14) // filter on building A
Resultatet af denne operation er en matrix som denne:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Bemærk, at alle id'er i bygning B nu er FALSKE. Denne matrix leveres direkte til FREKVENS-funktionen som dataarray . Til bins_array leverer vi id'erne selv:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Med denne konfiguration returnerer FREQUENCY arrayet nedenfor:
(4;0;0;0;2;0;0;0;0;0;0)
Resultatet er lidt kryptisk, men betydningen er 905 vises fire gange, og 773 vises to gange. FALSE-værdierne ignoreres automatisk.
FREKVENS har en speciel funktion, der automatisk returnerer nul for alle tal, der allerede er vist i dataarrayet, hvorfor værdier er nul, når et nummer er stødt på. Dette er den funktion, der gør det muligt for denne tilgang at arbejde.
Dernæst testes hver af disse værdier til at være større end nul:
(4;0;0;0;2;0;0;0;0;0;0)>0
Resultatet er en matrix som denne:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Hver SAND på listen repræsenterer et unikt nummer på listen, og vi skal bare tilføje de SANDE værdier med SUM. SUM tilføjer dog ikke logiske værdier i en matrix, så vi skal først tvinge værdierne til 1 eller nul. Dette gøres med det dobbelte negative (-). Resultatet er en matrix på kun 1 eller 0:
(1;0;0;0;1;0;0;0;0;0;0)
Endelig tilføjer SUM disse værdier og returnerer det samlede antal, som i dette tilfælde er 2.
Flere kriterier
Du kan udvide formlen til at håndtere flere kriterier som dette:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
