Általános képlet
SUMPRODUCT(--(A:A=A1))
Összegzés
Előszó
Ez egy bosszantóan hosszú bevezetés, de a kontextus fontos, sajnálom!
Ha nagyon hosszú (16+ számjegyű) számokat próbál meg számlálni egy tartományban a COUNTIF segítségével, akkor hibás eredményeket láthat, mivel hibát okozhat bizonyos funkciók kezelése a hosszú számokkal, még akkor is, ha ezeket a számokat szövegként tárolja. Vegye figyelembe az alábbi képernyőt. A D oszlop összes számlája helytelen - bár a B oszlop minden egyes száma egyedi, a COUNTIF által visszaadott szám azt sugallja, hogy ezek a számok duplikátumok.
=COUNTIF(data,B5)
Ez a probléma azzal függ össze, hogy az Excel hogyan kezeli a számokat. Az Excel csak 15 jelentős számjegyet képes kezelni, és ha 15-nél nagyobb számot ad meg az Excelben, akkor a záró számok némán konvertálódnak. A fent említett számlálási probléma ebből a határból fakad.
Normál esetben elkerülheti ezt a korlátot úgy, hogy hosszú számokat ír be szövegként, akár úgy, hogy a számot egyetlen idézettel kezdi ('999999999999999999), vagy ha a cellákat cellákká formázza szövegként a bevitel előtt. Mindaddig, amíg nem kell matematikai műveleteket végrehajtania egy számnál, ez jó megoldás, és lehetővé teszi extra hosszú számok megadását például hitelkártya-számokhoz és sorozatszámokhoz anélkül, hogy számokat veszítene.
Ha azonban a COUNTIF számítógépről megpróbál 15-nél hosszabb számokat számlálni (még szövegként is tárolva), akkor megbízhatatlan eredményeket láthat. Ez azért történik, mert a COUNTIF a hosszú értéket belsőleg egy számgá alakítja vissza a feldolgozás során, kiváltva a fent leírt 15 jegyű határt. Ha nincs minden számjegy, néhány szám duplikátumokként kerülhet beszámításra, ha COUNTIF-el számoljuk.
Megoldás
Az egyik megoldás a COUNTIF képlet helyettesítése egy olyan képlettel, amely SUM vagy SUMPRODUCT alkalmazást használ. A bemutatott példában az E5 képlete így néz ki:
=SUMPRODUCT(--(data=B5))
A képlet az elnevezett "data" tartományt (B5: B9) használja, és a SUMPRODUCT segítségével minden számhoz megfelelő számot generál.
Magyarázat
Először is, a SUMPRODUCT belsejében lévő kifejezés összehasonlítja a nevezett "data" tartomány összes értékét az aktuális sor B oszlopának értékével. Ennek eredményeként TRUE / FALSE eredmények tömbje keletkezik.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Ezután a kettős negatív a TRUE / FALSE értékeket 1/0 értékekre kényszeríti.
=SUMPRODUCT((1;0;0;0;0))
Végül a SUMPRODUCT egyszerűen összegzi a tömb elemeit, és visszaadja az eredményt.
Tömb képletváltozat
A SUMPRODUCT helyett használhatja a SUM függvényt is, de ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni:
(=SUM(--(B:B=B5)))
Egyéb funkciók ezzel a problémával
Magam még nem ellenőriztem, de úgy tűnik, hogy több függvénynek ugyanaz a problémája, mint a SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF és AVERAGEIFS.
