Yleinen kaava
=SUMPRODUCT(--NOT(ISERROR(range)))
Yhteenveto
Voit laskea virheitä sisältämättömien solujen lukumäärän käyttämällä SUMPRODUCT-funktioon käärittyjä toimintoja ISERROR ja NOT. Esitetyssä esimerkissä kaava kohdassa E5 on:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Selitys
Tässä esimerkissä tavoitteena on laskea solujen määrä alueella, joka ei sisällä virheitä.
Työskentelemällä sisältä ulospäin, käytämme ensin ISERROR-toimintoa koko alueella:
ISERROR(B5:B14) // check all 10 cells
Koska alueella B5: B14 on kymmenen solua, ISERROR palauttaa taulukon, jolla on kymmenen tällaista tulosta:
(FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tässä jokainen TOSI-arvo osoittaa solun arvon, joka on virhe. Koska tavoitteena on laskea soluja, jotka eivät sisällä virheitä, käännämme nämä tulokset EI-funktiolla:
NOT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE))
joka palauttaa:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Huomaa, että jokainen TOSI-arvo vastaa nyt solua, joka ei sisällä virhettä. Tämä taulukko on nyt oikeassa muodossa - TOSI-arvot osoittavat solut ilman virheitä, FALSE-arvot osoittavat soluja, joissa on virheitä.
Koska SUMPRODUCT toimii vain numeeristen tietojen kanssa, seuraava askel on muuntaa TOSI- ja EPÄTOSI-arvot numeerisiksi ekvivalenteiksi 1 ja 0. Teemme tämän kaksinkertaisella negatiivisella (-):
--(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE)
Tuloksena oleva taulukko näyttää tältä:
(1;0;1;1;1;0;1;1;1;0)
Lopuksi SUMPRODUCT laskee yhteen tämän taulukon kohteet ja palauttaa kokonaismäärän, joka esimerkissä on numero 3:
=SUMPRODUCT((1;0;1;1;1;0;1;1;1;0)) // returns 7
ISERR-toiminto
Kuten ISERROR-funktio, ISERR-funktio palauttaa arvon TOSI, kun arvo on virhe. Erona on, että ISERR jättää huomioimatta # N / A-virheet. Jos haluat laskea solut, joissa ei ole virheitä, ja ohittaa # ei käytössä -virheen, voit korvata ISERR: llä ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // ignore #N/A
SUM-vaihtoehto
Voit käyttää SUM-toimintoa myös virheiden laskemiseen. Kaavan rakenne on sama:
=SUM(--NOT(ISERROR(B5:B14)))
Huomaa: tämä on matriisikaava, ja se on syötettävä näppäimillä + + shift + enter paitsi Excel 365: ssä.
