Generieke formule
SUMPRODUCT(--(A:A=A1))
Samenvatting
Voorwoord
Dit is een irritant lange inleiding, maar de context is belangrijk, sorry!
Als u zeer lange getallen (16+ cijfers) in een bereik probeert te tellen met AANTAL.ALS, kunt u onjuiste resultaten zien, vanwege een fout in de manier waarop bepaalde functies lange getallen verwerken, zelfs als die getallen als tekst worden opgeslagen. Beschouw het onderstaande scherm. Alle tellingen in kolom D zijn onjuist, hoewel elk getal in kolom B uniek is, suggereert het aantal dat wordt geretourneerd door AANTAL.ALS dat deze getallen duplicaten zijn.
=COUNTIF(data,B5)
Dit probleem houdt verband met hoe Excel omgaat met getallen. Excel kan slechts 15 significante cijfers verwerken en als u in Excel een getal met meer dan 15 cijfers invoert, ziet u de achterliggende cijfers stilzwijgend naar nul worden geconverteerd. Het bovengenoemde telprobleem komt voort uit deze limiet.
Normaal gesproken kunt u deze limiet vermijden door lange getallen als tekst in te voeren, ofwel door het nummer te beginnen met een enkele aanhalingsteken ('999999999999999999) of door de cel (len) op te maken als Tekst voordat u ze invoert. Zolang u geen wiskundige bewerkingen op een getal hoeft uit te voeren, is dit een goede oplossing en kunt u extra lange getallen invoeren voor zaken als creditcardnummers en serienummers zonder nummers te verliezen.
Als u echter AANTAL.ALS probeert te gebruiken om een getal met meer dan 15 cijfers te tellen (zelfs als het als tekst is opgeslagen), ziet u mogelijk onbetrouwbare resultaten. Dit gebeurt omdat COUNTIF de lange waarde op een bepaald punt tijdens de verwerking intern naar een getal converteert, waardoor de hierboven beschreven limiet van 15 cijfers wordt geactiveerd. Zonder alle cijfers aanwezig, kunnen sommige getallen als duplicaten worden geteld wanneer ze worden geteld met AANTAL.ALS.
Oplossing
Een oplossing is om de AANTAL.ALS-formule te vervangen door een formule die SOM of SOMPRODUCT gebruikt. In het getoonde voorbeeld ziet de formule in E5 er als volgt uit:
=SUMPRODUCT(--(data=B5))
De formule gebruikt het benoemde bereik "gegevens" (B5: B9) en genereert het juiste aantal voor elk getal met SOMPRODUCT.
Uitleg
Ten eerste vergelijkt de uitdrukking in SOMPRODUCT alle waarden in het benoemde bereik "data" met de waarde uit kolom B in de huidige rij. Dit resulteert in een reeks TRUE / FALSE-resultaten.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Vervolgens dwingt het dubbele negatieve de TRUE / FALSE-waarden naar 1/0-waarden.
=SUMPRODUCT((1;0;0;0;0))
Ten slotte telt SOMPRODUCT eenvoudig de items in de array op en retourneert het resultaat.
Matrixformule variant
U kunt ook de functie SOM gebruiken in plaats van SOMPRODUCT, maar dit is een matrixformule en moet worden ingevoerd met control + shift + enter:
(=SUM(--(B:B=B5)))
Andere functies met dit probleem
Ik heb dit zelf niet geverifieerd, maar het lijkt erop dat verschillende functies hetzelfde probleem hebben, waaronder SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF en AVERAGEIFS.
