Bendroji formulė
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Santrauka
Apibendrinant, jei langeliuose yra viena ar kita teksto eilutė (ty yra „katė“ arba „žiurkė“), galite naudoti funkciją SUMPRODUCT kartu su ISNUMBER + SEARCH arba FIND. Parodytame pavyzdyje formulė langelyje F5 yra:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
kuri grąžina C4: C8 reikšmių sumą, kai B4: B8 ląstelėse yra „katė“ arba „žiurkė“.
Paaiškinimas
Kai susumuojate langelius pagal „ARBA“ kriterijus, turite būti atsargūs, kad dvigubai nesuskaičiuotumėte, kai yra tikimybė, kad abu kriterijai bus teisingi. Parodytame pavyzdyje norime susumuoti C stulpelio reikšmes, kai B stulpelio langeliuose yra „cat“ arba „žiurkė“. Negalime naudoti SUMIF pagal du kriterijus, nes SUMIFS remiasi logika IR. Ir jei bandysime naudoti du SUMIFS (ty SUMIFS + SUMIFS), mes skaičiuosime dvigubai, nes yra ląstelių, kuriose yra „katė“ ir „žiurkė“.
Vietoj to mes naudojame tokią formulę:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Ši šios formulės šerdis pagrįsta čia paaiškinta formule, kurioje langelio viduje randamas tekstas su ISNUMBER ir SEARCH:
ISNUMBER(SEARCH("text",range)
Kai bus pateiktas langelių diapazonas, šis fragmentas pateiks TRUE / FALSE reikšmių masyvą, po vieną vertę kiekvienai diapazono ląstelei. Šioje formulėje šį fragmentą naudojame du kartus, vieną kartą - „katė“, o kitą - „žiurkė“, taigi gausime du masyvus. Šiuo metu mes turime:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Tada mes pridėsime šias masyvus kartu, nes pridėjimas naudojamas loginės algebros OR logikai. Matematikos operacija TRUE ir FALSE reikšmes automatiškai priverčia į 1s ir 0s, todėl galiausiai pateikiame toliau pateiktą masyvą:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Kiekvienas šio masyvo skaičius yra gautų sumavus TRUE ir FALSE reikšmes dviejose pradinėse masyvuose. Pateiktame pavyzdyje masyvas atrodo taip:
(2;0;2;1;0)
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 verčiame visas reikšmes į TRUE arba FALSE, patikrindami masyvą su „> 0“. Tai grąžina TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Kurį tada konvertuosime į 1/0, naudodami dvigubą neigiamą (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
ir, galiausiai:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT padaugina atitinkamus dviejų masyvų elementus kartu ir susumuoja rezultatą, grąžindamas 70.
Didžiųjų ir mažųjų raidžių parinktis
Funkcija PAIEIGA nepaiso didžiųjų raidžių. Jei jums reikia jautrios parinkties, pakeiskite PAIEŠKA funkcija RASTI.
