Generisk formel
SUMPRODUCT(--(A:A=A1))
Resumé
Forord
Dette er en irriterende lang introduktion, men konteksten er vigtig, undskyld!
Hvis du forsøger at tælle meget lange tal (16+ cifre) i et interval med COUNTIF, kan du muligvis se forkerte resultater på grund af en fejl i, hvordan visse funktioner håndterer lange tal, selv når disse tal er gemt som tekst. Overvej skærmen nedenfor. Alle optællinger i kolonne D er forkerte - skønt hvert nummer i kolonne B er unikt, antyder antallet, der returneres af COUNTIF, at disse tal er duplikater.
=COUNTIF(data,B5)
Dette problem er relateret til, hvordan Excel håndterer tal. Excel kan kun håndtere 15 signifikante cifre, og hvis du indtaster et tal med mere end 15 cifre i Excel, ser du de efterfølgende cifre lydløst konverteret til nul. Tællingsproblemet nævnt ovenfor stammer fra denne grænse.
Normalt kan du undgå denne grænse ved at indtaste lange tal som tekst, enten ved at starte nummeret med et enkelt citat ('999999999999999999) eller ved at formatere cellen (e) som tekst, inden du indtaster. Så længe du ikke behøver at udføre matematiske operationer på et nummer, er dette en god løsning, og det giver dig mulighed for at indtaste ekstra lange numre til ting som f.eks. Kreditkortnumre og serienumre uden at miste nogen numre.
Men hvis du prøver at bruge COUNTIF til at tælle et tal med mere end 15 cifre (selv når det er gemt som tekst), kan du muligvis se upålidelige resultater. Dette sker, fordi COUNTIF internt konverterer den lange værdi tilbage til et tal på et eller andet tidspunkt under behandlingen, hvilket udløser den 15-cifrede grænse beskrevet ovenfor. Uden alle tilstedeværende cifre kan nogle tal tælles som duplikater, når de tælles med COUNTIF.
Løsning
En løsning er at erstatte COUNTIF-formlen med en formel, der bruger SUM eller SUMPRODUCT. I det viste eksempel ser formlen i E5 sådan ud:
=SUMPRODUCT(--(data=B5))
Formlen bruger det navngivne interval "data" (B5: B9) og genererer det korrekte antal for hvert nummer med SUMPRODUCT.
Forklaring
For det første sammenligner udtrykket inde i SUMPRODUCT alle værdier i det navngivne interval "data" med værdien fra kolonne B i den aktuelle række. Dette resulterer i en række TRUE / FALSE-resultater.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Derefter tvinger det dobbelte negative de SANDE / FALSE-værdier til 1/0-værdier.
=SUMPRODUCT((1;0;0;0;0))
Endelig summerer SUMPRODUCT simpelthen elementerne i arrayet og returnerer resultatet.
Array formel variant
Du kan også bruge SUM-funktionen i stedet for SUMPRODUCT, men dette er en matrixformel og skal indtastes med kontrol + shift + enter:
(=SUM(--(B:B=B5)))
Andre funktioner med dette problem
Jeg har ikke verificeret dette selv, men det ser ud til, at flere funktioner har det samme problem, herunder SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF og AVERAGEIFS.
