Meie artiklis „Konkreetset teksti sisaldavate lahtrite loendamine” loendasime iga lahtri, mis sisaldab konkreetset teksti. Selles artiklis õpime, kuidas loendada lahtreid, mis sisaldavad seda või teist väärtust. Teisisõnu, VÕI loogikaga loendamine.
Võite arvata, et saate funktsiooni COUNTIF kasutada kaks korda ja seejärel need kokku liita. Kuid see on vale pööre. Saate teada, miks.
Üldine valem
= SUMPRODUCT (-((ISNUMBER (OTSI ("see", vahemik)) + ISNUMBER (OTSI ("see", vahemik)))> 0))
"See": see on esimene tekst, mida soovite vahemikku lugeda. See võib olla mis tahes tekst.
"See": see on teine tekst, mida soovite vahemikus lugeda. See võib olla mis tahes tekst.
Vahemik: See on teksti sisaldav vahemik või massiiv, millesse arvestate oma konkreetsete tekstide puhul.
Vaatame näidet:
Näide:
Siin on mul tagasiside kommentaare vahemikus A2 kuni A8. Ma tahan kokku lugeda, kui palju on kommentaare, mis sisaldavad “häid” või “toredaid” sõnu.
Konkreetset teksti sisaldavate lahtrite loendamiseks „hea” või „tore” kirjutage see valem:
= SUMPRODUCT (-((ISNUMBER (OTSI ("hea", A2: A8)) + ISNUMBER (OTSI ("tore", A2: A8)))> 0))
Näete, et on neli kommentaari, mis sisaldavad kas “head” või “kena”.
Kuidas see töötab?
Alustame seestpoolt.
SEARCH ("hea", A2: A8): see osa tagastab massiivi #VALUE veast ja numbritest, mis esindavad leitud teksti asukohta. {8; #VALUE!;#VALUE!;#VALUE!;#VALUE!; 30; 24}
ISNUMBER (SEARCH ("OTSI" ("hea", A2: A8))): see valemi osa kontrollib massiivi tagastamisel iga väärtust funktsiooni OTSING abil, kas see on arv või mitte, ning tagastab massiivi TRUE ja FALSE. Selle näite puhul tagastab see väärtuse {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (OTSI ("kena", A2: A8)): see valemi osa teeb sama, kuid seekord otsib ta lahtrist "kena" sõna ja tagastab lahtris oleva tõelise ja vale aluse massiivi "kena" . ”
Järgmisena oleme need massiivid lisanud + märgiga. See loeb väärtused 1 ja 0 ja liidab need kokku. Sisemiselt näeb see välja selline: {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Näete, et kommentaaris A7 on nii tekstid „tore” kui ka „hea”. Ja seda loetakse siin topelt. Me ei taha seda. Seega teeme teise võrdluse iga numbri teisendamiseks tõeks ja valeks.
((ISNUMBER (OTSI ("hea", A2: A8)) + ISNUMBER (OTSI ("tore", A2: A8)))>0): See valemi osa teisendab saadud massiivi tõeks ja valeks. Kui väärtus on suurem kui 0, on see tõsi VÄÄR. Siin on see {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
Massiivi TRUE arv on stringi nimi, mis sisaldab “häid” või “toredaid” sõnu.
SUMPRODUCT (-((ISNUMBER (OTSI ("hea", A2: A8)) + ISNUMBER (OTSI ("tore", A2: A8)))> 0))
Järgmisena kasutame negatiivseid sümboleid nende teisendamiseks numbriteks. Ja lõpuks võtab SUMPRODUCT massiivi kokku, et saada tagasi rakkude arv, mis sisaldavad sõna „hea” või „tore”.
Võite kasutada ka funktsiooni SUM, kuid siis peate selle valemi sisestama massiivivalemina.
Miks mitte kasutada COUNTIFS -i?
Topeltarvestuse tõttu.
Kui lahter sisaldab mõlemat teksti, loetakse see kaks korda, mis pole selle stsenaariumi puhul õige.
Aga kui soovite, et see juhtuks, kasutage seda valemit,
= SUMMA (COUNTIFS (A2: A8, {"*kena*", "*hea*"}))
Meie näites naaseb see 5. Olen seda siin selgitanud.
Väiketähtede tundlikuks muutmine
Kavandatud lahendus arvestab antud teksti olenemata tähtede tähestikust. Kui soovite arvestada väiketähtedega seotud vasteid, asendage funktsioon OTSI funktsiooniga FIND.
= SUMPRODUCT (-((ISNUMBER (LEIA("see", vahemik)) + ISNUMBER (LEIA("see", vahemik)))> 0))
TheLEIA funktsioon on tõstutundlik funktsioon. Tagastab leitud teksti asukoha.
Nii et jah, poisid, nii saate loendada nende teksti sisaldavate lahtrite arvu. Selle funktsiooni kohta lugemiseks võite valemis ka funktsioonide nimedel klõpsata. Olen neid arusaadavalt täpsustanud.
Kuidas kontrollida, kas lahter sisaldab Excelis konkreetset teksti
Kuidas kontrollida Excelis stringidega tekstide loendit
Hankige Excelis COUNTIFS kahe kriteeriumi vaste
Hankige Excelis COUNTIFS mitme kriteeriumiga
Populaarsed artiklid:
50 Exceli otsetee tootlikkuse suurendamiseks : Saate oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada tta funktsioon VLOOKUP Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.