Bendroji formulė
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Santrauka
Norėdami suskaičiuoti langelius, kuriuose yra x arba y, galite naudoti formulę, pagrįstą funkcija SUMPRODUCT. Parodytame pavyzdyje formulė langelyje F5 yra:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Tai vienos ląstelės formulės tirpalas, paaiškintas toliau. Taip pat galima naudoti paprastesnę formulę pagal pagalbinį stulpelį, taip pat paaiškintą toliau.
Paaiškinimas
Kai skaičiuojate langelius su „ARBA logika“, turite būti atsargūs, kad dvigubai nesuskaičiuotumėte. Pvz., Jei skaičiuojate langelius, kuriuose yra „abc“ arba „def“, galite ne tik susieti dvi COUNTIF funkcijas, nes galite dvigubai skaičiuoti langelius, kuriuose yra „abc“ ir „def“.
Vieno langelio tirpalas
Norėdami naudoti vieną formulę, galite naudoti SUMPRODUCT su ISNUMBER + FIND. F5 formulė yra:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Ši formulė pagrįsta čia paaiškinta formule, kurioje langelio viduje randamas tekstas:
ISNUMBER(FIND("abc",B5:B11)
Kai bus pateiktas langelių diapazonas, šis fragmentas pateiks TRUE / FALSE reikšmių masyvą, po vieną vertę kiekvienai diapazono ląstelei. Kadangi tai naudojame du kartus (vieną kartą "abc" ir vieną kartą "def"), gausime du masyvus.
Tada mes pridėsime šiuos masyvus kartu (su +), kuris sukuria naują vieną masyvą skaičių. Kiekvienas šio masyvo skaičius yra gautų sumavus TRUE ir FALSE reikšmes dviejose pradinėse masyvuose. Parodytame pavyzdyje gautas masyvas atrodo taip:
(2;0;2;0;1;0;2)
Turime susumuoti šiuos skaičius, bet nenorime dvigubai skaičiuoti. Taigi turime įsitikinti, kad bet kuri didesnė už nulį reikšmė yra tik kartą suskaičiuota. Norėdami tai padaryti, mes priverčiame visas reikšmes PATIKTI arba NETIESA su „> 0“, tada priverčiame iki 1/0 dvigubai neigiamai (-).
Galiausiai, SUMPRODUCT pateikia visų masyvo reikšmių sumą.
Pagalbinės kolonos sprendimas
Naudojant pagalbinį stulpelį, norint patikrinti kiekvieną langelį atskirai, problema nėra tokia sudėtinga. Galime naudoti COUNTIF su dviem reikšmėmis (pateikiama kaip „masyvo konstanta“). C5 formulė yra:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF grąžins masyvą, kuriame yra du elementai: „abc“ ir „def“. Norėdami išvengti dvigubo skaičiavimo, sudedame elementus ir priverčiame rezultatą į TIKRA / NETIESA su „> 0“. Galiausiai, TRUE / FALSE reikšmes konvertuojame į 1 ir 0 su dvigubai neigiama (-).
Galutinis kiekvieno langelio rezultatas yra 1 arba 0. Norėdami gauti visų diapazono langelių sumą, tiesiog susumuokite pagalbinio stulpelio duomenis.
