Yleinen kaava
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Yhteenveto
Yhteenvetona, jos solut sisältävät joko yhden tai toisen tekstimerkkijonon (ts. Sisältävät "kissa" tai "rotta"), voit käyttää SUMPRODUCT-funktiota yhdessä ISNUMBER + SEARCH tai FIND -toiminnon kanssa. Esitetyssä esimerkissä solun F5 kaava on:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
joka palauttaa C4: C8: n arvojen summan, kun B4: B8: n solut sisältävät joko "kissa" tai "rotta".
Selitys
Kun summaat solut "OR" -ehdoilla, sinun on oltava varovainen, ettet lasketa kaksinkertaista määrää, jos on mahdollista, että molemmat kriteerit palaavat totta. Esitetyssä esimerkissä haluamme laskea yhteen arvot sarakkeessa C, kun sarakkeen B solut sisältävät joko "kissa" tai "rotta". Emme voi käyttää SUMIF-arvoja kahdella ehdolla, koska SUMIFS perustuu AND-logiikkaan. Ja jos yritämme käyttää kahta SUMIFIA (eli SUMIFS + SUMIFS), laskemme kaksinkertaisen määrän, koska on soluja, jotka sisältävät sekä "kissa" että "rotta".
Sen sijaan käytämme tällaista kaavaa:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Tämän kaavan tämä ydin perustuu tässä selitettyyn kaavaan, joka etsii tekstin solun sisältä ISNUMBER ja SEARCH:
ISNUMBER(SEARCH("text",range)
Kun annetaan solualue, tämä katkelma palauttaa TOSI / EPÄTOSI-arvot, yhden arvon kullekin alueen solulle. Tässä kaavassa käytämme tätä katkelmaa kahdesti, kerran "kissa" ja toinen "rotta", joten saamme kaksi matriisia. Tässä vaiheessa meillä on:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Seuraavaksi lisätään nämä taulukot yhteen, koska lisäystä käytetään loogisessa algebrassa OR-logiikkaa varten. Matemaattinen operaatio pakottaa TOSI- ja EPÄTOSI-arvot automaattisesti arvoon 1s ja 0s, joten päädymme seuraavaan ryhmään:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Tämän taulukon kukin numero on seurausta alkuperäisten kahden matriisin TOSI- ja EPÄTOSI-arvojen lisäämisestä yhteen. Esitetyssä esimerkissä taulukko näyttää tältä:
(2;0;2;1;0)
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 pakotetaan kaikki arvot TOSI tai EPÄTOSI tarkistamalla taulukko "> 0". Tämä palauttaa TOSI / EPÄTOSI:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Muunna sitten 1/0 kaksinkertaisen negatiivisen (-) avulla:
=SUMPRODUCT((1;0;1;1;0),C4:C8)
ja lopuksi:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT kertoo näiden kahden ryhmän vastaavat elementit yhteen ja summaa tuloksen palauttaen 70.
Kirjainkoko merkitsevä vaihtoehto
SEARCH-toiminto jättää kirjainkoon huomiotta. Jos tarvitset arkaluontoisen vaihtoehdon, korvaa HAKU toiminnolla ETSI.
