Yleinen kaava
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Yhteenveto
Voit laskea solut, jotka eivät ole yhtä monista asioista, käyttämällä MATCH-, ISNA- ja SUMPRODUCT-funktioihin perustuvaa kaavaa. Esitetyssä esimerkissä solun F5 kaava on:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
missä "data" on nimetty alue B5: B16 ja "poissulkeminen" on nimetty alue D5: D7.
Selitys
Ensinnäkin pieni asiayhteys. Tavallisesti, jos sinulla on vain pari asiaa, joita et halua laskea, voit käyttää COUNTIFS: ää näin:
=COUNTIFS(range,"apple",range,"orange")
Mutta tämä ei ole kovin skaalautuva, jos sinulla on luettelo monista asioista, koska sinun on lisättävä ylimääräinen alue / kriteeripari jokaiselle, jota et halua laskea. Olisi paljon helpompaa rakentaa luettelo ja lisätä viite tähän luetteloon osana kriteerejä. Juuri tämän sivun kaava tekee.
Ytimessä tämä kaava käyttää MATCH-funktiota solujen, jotka eivät ole yhtä suuret kuin "a", "b" tai "c", etsimiseen tällä lausekkeella:
MATCH(data,exclude,0)
Huomaa, että hakuarvo ja hakutaulukko ovat "päinvastaiset" normaalista kokoonpanosta - tarjoamme kaikki nimetyn alueen "data" arvot hakuarvoina ja annamme kaikki arvot, jotka haluamme jättää nimettyyn alueeseen "poissulkea". Koska annamme MATCH: lle useamman kuin yhden hakuarvon, saamme useamman kuin yhden tuloksen tällaisessa taulukossa:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Pohjimmiltaan MATCH antaa meille vastaavien arvojen sijainnin lukuna ja palauttaa # N / A kaikille muille arvoille.
# N / A-tulokset ovat niistä, joista olemme kiinnostuneita, koska ne edustavat arvoja, jotka eivät ole yhtä suuria kuin "a", "b" tai "c". Vastaavasti käytämme ISNA: ta pakottaaksemme nämä arvot TOSI, ja numerot TOSI:
ISNA(MATCH(data,exclude,0)
Sitten käytämme kaksoisnegatiivia pakottaaksemme TOSI arvoksi 1 ja EPÄTOSI nollaksi. Tuloksena oleva taulukko SUMPRODUCTin sisällä näyttää tältä:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Kun prosessoitava vain yksi taulukko, SUMPRODUCT summaa ja palauttaa lopputuloksen 4.
Huomaa: SUMPRODUCTin käyttäminen SUM: n sijaan välttää tarvetta käyttää control + shift + enter.
Laske miinus ottelu
Toinen tapa laskea soluja, jotka eivät ole yhtä suuria monista asioista, on laskea kaikki arvot ja vähentää ottelut. Voit tehdä tämän seuraavalla kaavalla:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Tässä COUNTA palauttaa kaikkien tyhjien solujen määrän. COUNTIF-funktio, kun nimetty alue on "poissuljettu", palauttaa kolme lukua, yhden jokaiselle luettelon kohdalle. SUMPRODUCT laskee yhteen summan, ja tämä luku vähennetään kaikkien ei-tyhjien solujen lukumäärästä. Lopputulos on solujen lukumäärä, jotka eivät vastaa arvoja "poissulkemisessa".
Literal sisältää tyyppilogiikan
Tämän sivun kaava lasketaan logiikalla "on yhtä suuri". Jos joudut laskemaan soluja, jotka eivät sisällä paljon merkkijonoja, joissa on tarkoittaa, että merkkijono voi näkyä missä tahansa solussa, tarvitset monimutkaisemman kaavan.
