Üldine valem
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Kokkuvõte
Lahtrite loendamiseks, mis sisaldavad kas x või y, saate kasutada funktsiooni SUMPRODUCT põhinevat valemit. Näidatud näites on lahtris F5 valem:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
See on ühe raku valemi lahendus, mida selgitatakse allpool. Samuti on võimalik kasutada lihtsamat valemit, mis põhineb abistaja veerul, mida on ka allpool selgitatud.
Selgitus
Kui loete lahtrid "VÕI loogikaga", peate olema ettevaatlik, et loendamist topelt ei tehta. Näiteks kui loete lahtrid, mis sisaldavad "abc" või "def", ei saa te lihtsalt ühendada kahte COUNTIF-funktsiooni, sest võite kahekordistada loendit, mis sisaldab nii "abc" kui ka "def".
Üherakuline lahus
Ühe valemi jaoks võite kasutada SUMPRODUCTi koos numbriga ISNUMBER + FIND. F5 valem on:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
See valem põhineb siin selgitatud valemil, mis otsib lahtri sees oleva teksti:
ISNUMBER(FIND("abc",B5:B11)
Lahtrivahemiku määramisel tagastab see koodilõik TRUE / FALSE väärtuste massiivi, üks väärtus iga vahemiku lahtrite kohta. Kuna me kasutame seda kaks korda (üks kord "abc" ja üks kord "def"), saame kaks massiivi.
Järgmisena lisame need massiivid kokku (koos +), mis loob uue ühe massiivi numbritest. Selle massiivi iga number tuleneb kahe algse massiivi TRUE ja FALSE väärtuste liitmisest. Näidatud näites näeb saadud massiiv välja selline:
(2;0;2;0;1;0;2)
Peame need arvud kokku liitma, kuid me ei taha topeltarvestust teha. Seega peame veenduma, et kõik nullist suuremad väärtused loetakse vaid üks kord. Selleks sunnime kõik väärtused väärtusele TÕENE või VALE väärtusega "> 0", seejärel topeltnegatiiviga (-) väärtuseni 1/0.
Lõpuks tagastab SUMPRODUCT massiivi kõigi väärtuste summa.
Abikolonni lahendus
Abistaja veeru abil, et kontrollida iga lahtrit eraldi, pole probleem nii keeruline. Saame kasutada COUNTIF-i kahe väärtusega (ette nähtud "massiivikonstandina"). C5 valem on:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF tagastab massiivi, mis sisaldab kahte üksust: loendit "abc" ja loendit "def". Topeltloendamise vältimiseks lisame üksused kokku ja sunnime seejärel tulemuse väärtusele TRUE / FALSE väärtusega "> 0". Lõpuks teisendame TRUE / FALSE väärtused 1-ks ja 0-ks topeltnegatiiviga (-).
Lõpptulemus on iga lahtri jaoks kas 1 või 0. Vahemiku kõigi lahtrite kogusumma saamiseks liidetakse lihtsalt abistaja veerg.
