Generisk formel
SUMPRODUCT(--(A:A=A1))
Sammendrag
Forord
Dette er en irriterende lang introduksjon, men konteksten er viktig, beklager!
Hvis du prøver å telle veldig lange tall (16+ sifre) i et område med COUNTIF, kan du se feil resultater på grunn av en feil i hvordan visse funksjoner håndterer lange tall, selv når disse tallene er lagret som tekst. Tenk på skjermen nedenfor. Alle tellinger i kolonne D er feil - selv om hvert tall i kolonne B er unikt, antyder antallet som returneres av COUNTIF at disse tallene er duplikater.
=COUNTIF(data,B5)
Dette problemet er relatert til hvordan Excel håndterer tall. Excel kan bare håndtere 15 signifikante sifre, og hvis du skriver inn et tall med mer enn 15 sifre i Excel, ser du de etterfølgende sifrene konvertert til null. Telleproblemet nevnt ovenfor kommer fra denne grensen.
Normalt kan du unngå denne grensen ved å legge inn lange tall som tekst, enten ved å starte nummeret med et enkelt tilbud ('999999999999999999) eller ved å formatere cellen (e) som tekst før du skriver inn. Så lenge du ikke trenger å utføre matteoperasjoner på et nummer, er dette en god løsning, og det lar deg skrive inn ekstra lange tall for ting som kredittkortnumre og serienumre uten å miste noen tall.
Hvis du prøver å bruke COUNTIF til å telle et tall med mer enn 15 sifre (selv når det er lagret som tekst), kan du imidlertid se upålitelige resultater. Dette skjer fordi COUNTIF internt konverterer den lange verdien tilbake til et tall på et tidspunkt under behandlingen, og utløser den 15-sifrede grensen beskrevet ovenfor. Uten alle sifrene til stede kan noen tall telles som duplikater når de telles med COUNTIF.
Løsning
En løsning er å erstatte COUNTIF-formelen med en formel som bruker SUM eller SUMPRODUCT. I eksemplet vist ser formelen i E5 slik ut:
=SUMPRODUCT(--(data=B5))
Formelen bruker det nevnte området "data" (B5: B9) og genererer riktig antall for hvert tall med SUMPRODUCT.
Forklaring
Først sammenligner uttrykket i SUMPRODUCT alle verdiene i det nevnte området "data" med verdien fra kolonne B i den gjeldende raden. Dette resulterer i en rekke SANTE / FALSE resultater.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Deretter tvinger det dobbelte negative de SANNE / FALSE verdiene til 1/0 verdiene.
=SUMPRODUCT((1;0;0;0;0))
Til slutt summerer SUMPRODUCT bare elementene i matrisen og returnerer resultatet.
Array formel variant
Du kan også bruke SUM-funksjonen i stedet for SUMPRODUCT, men dette er en matriseformel og må angis med kontroll + shift + enter:
(=SUM(--(B:B=B5)))
Andre funksjoner med dette problemet
Jeg har ikke bekreftet dette selv, men det ser ut til at flere funksjoner har det samme problemet, inkludert SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF og AVERAGEIFS.
