Yleinen kaava
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Yhteenveto
Voit laskea soluja, jotka sisältävät joko x tai y, käyttämällä SUMPRODUCT-funktioon perustuvaa kaavaa. Esitetyssä esimerkissä solun F5 kaava on:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Tämä on yksisoluinen kaavan ratkaisu, joka selitetään alla. On myös mahdollista käyttää yksinkertaisempaa kaavaa, joka perustuu auttajasarakkeeseen, joka selitetään myös alla.
Selitys
Kun lasket soluja "TAI-logiikalla", sinun on oltava varovainen, ettet kaksinkertaista. Jos esimerkiksi lasket soluja, jotka sisältävät "abc" tai "def", et voi vain lisätä kahta COUNTIF-funktiota yhteen, koska voit kaksinkertaistaa laskennan soluihin, jotka sisältävät sekä "abc" että "def".
Yksisoluinen liuos
Yhdelle kaavalle voit käyttää SUMPRODUCT-numeroa ISNUMBER + FIND -toiminnon kanssa. F5: n kaava on:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Tämä kaava perustuu tässä selitettyyn kaavaan, joka etsii tekstiä solun sisällä:
ISNUMBER(FIND("abc",B5:B11)
Kun annetaan solualue, tämä katkelma palauttaa TOSI / EPÄTOSI-arvot, yhden arvon kullekin alueen solulle. Koska käytämme tätä kahdesti (kerran "abc" ja kerran "def"), saamme kaksi taulukkoa.
Seuraavaksi lisätään nämä taulukot yhteen (+: n kanssa), mikä luo uuden yksittäisen numeroryhmän. Tämän taulukon kukin numero on seurausta alkuperäisten kahden matriisin TOSI- ja EPÄTOSI-arvojen lisäämisestä yhteen. Esitetyssä esimerkissä tuloksena oleva taulukko näyttää tältä:
(2;0;2;0;1;0;2)
Meidän on lisättävä nämä numerot yhteen, mutta emme halua kaksinkertaista laskentaa. Joten meidän on varmistettava, että kaikki nollaa suuremmat arvot lasketaan vain kerran. Tätä varten pakotamme kaikki arvot TOSI tai EPÄTOSI arvoon "> 0", sitten pakotetaan arvoon 1/0 kaksois-negatiivisella (-).
Lopuksi SUMPRODUCT palauttaa taulukon kaikkien arvojen summan.
Apupylvään ratkaisu
Auttajapylvään avulla jokainen solu tarkistetaan erikseen, ongelma on vähemmän monimutkainen. Voimme käyttää COUNTIF-arvoa kahdella arvolla (toimitetaan "matriisivakiona"). C5: n kaava on:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF palauttaa taulukon, joka sisältää kaksi kohdetta: "abc": n ja "def": n. Kaksinkertaisen laskennan estämiseksi lisäämme kohteet yhteen ja pakotamme tuloksen TOSI / EPÄTOSI-arvoon "> 0": lla. Lopuksi muunnamme TOSI / EPÄTOSI-arvot arvoksi 1 ja 0 kaksinkertaisella negatiivisella (-).
Lopputulos on joko 1 tai 0 kullekin solulle. Saadaksesi summa kaikille alueen soluille, yksinkertaisesti summa helper-sarake.
