Yleinen kaava
SUMPRODUCT(--(A:A=A1))
Yhteenveto
Esipuhe
Tämä on ärsyttävän pitkä johdanto, mutta asiayhteys on tärkeä, anteeksi!
Jos yrität laskea hyvin pitkiä lukuja (yli 16 numeroa) alueella COUNTIF, saatat nähdä virheellisiä tuloksia johtuen virheestä, jolla tietyt toiminnot käsittelevät pitkiä numeroita, vaikka nämä numerot tallennettaisiin tekstinä. Harkitse alla olevaa näyttöä. Kaikki sarakkeen D laskelmat ovat virheellisiä - vaikka kukin sarakkeen B numero on yksilöllinen, COUNTIF: n palauttama määrä viittaa siihen, että nämä numerot ovat kaksoiskappaleita.
=COUNTIF(data,B5)
Tämä ongelma liittyy siihen, miten Excel käsittelee numeroita. Excel pystyy käsittelemään vain 15 merkitsevää numeroa, ja jos syötät Excelissä yli 15-numeroisen luvun, näet loppumerkinnät muunnettu hiljaa nollaksi. Edellä mainittu laskentaongelma johtuu tästä rajasta.
Normaalisti voit välttää tämän rajoituksen syöttämällä pitkiä numeroita tekstinä joko aloittamalla numeron yhdellä lainauksella ('999999999999999999) tai muotoilemalla solut tai tekstit Tekstiksi ennen kirjoittamista. Niin kauan kuin sinun ei tarvitse suorittaa matemaattisia operaatioita numerolle, tämä on hyvä ratkaisu, ja sen avulla voit syöttää erityisen pitkiä numeroita esimerkiksi luottokortin numeroille ja sarjanumeroille menettämättä yhtään numeroa.
Jos kuitenkin yrität käyttää COUNTIF-numeroa yli 15-numeroisten numeroiden laskemiseen (jopa tekstinä tallennettuna), saatat nähdä epäluotettavia tuloksia. Tämä johtuu siitä, että COUNTIF muuntaa sisäisesti pitkän arvon takaisin numeroksi jossain vaiheessa käsittelyn aikana, mikä laukaisee edellä kuvatun 15-numeroisen rajan. Ilman kaikkia numeroita, jotkut numerot voidaan laskea kaksoiskappaleina, kun lasketaan COUNTIF: llä.
Ratkaisu
Yksi ratkaisu on korvata COUNTIF-kaava kaavalla, joka käyttää SUM tai SUMPRODUCT. Esitetyssä esimerkissä E5: n kaava näyttää tältä:
=SUMPRODUCT(--(data=B5))
Kaava käyttää nimettyä aluetta "data" (B5: B9) ja tuottaa oikean määrän jokaiselle luvulle SUMPRODUCT-toiminnolla.
Selitys
Ensinnäkin SUMPRODUCTin sisällä oleva lauseke vertaa kaikkia nimettyjen alueiden "data" arvoja nykyisen rivin sarakkeen B arvoon. Tuloksena on joukko TOSI / EPÄTOSI -tuloksia.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Seuraavaksi kaksoisnegatiivi pakottaa TOSI / EPÄTOSI-arvot arvoon 1/0.
=SUMPRODUCT((1;0;0;0;0))
Lopuksi SUMPRODUCT vain laskee yhteen matriisin kohteet ja palauttaa tuloksen.
Matriisikaavan muunnos
Voit käyttää myös SUM-funktiota SUMPRODUCT-toiminnon sijaan, mutta tämä on matriisikaava, ja se on syötettävä painikkeilla control + shift + enter:
(=SUM(--(B:B=B5)))
Muita tämän ongelman toimintoja
En ole vahvistanut tätä itse, mutta näyttää siltä, että useilla toiminnoilla on sama ongelma, mukaan lukien SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF ja AVERAGEIFS.
