Üldine valem
=COUNTIF(range,"*txt*")
Kokkuvõte
Teatud teksti sisaldavate lahtrite lugemiseks võite kasutada funktsiooni COUNTIF koos metamärgiga. Näidatud näites on valem E5-s järgmine:
=COUNTIF(B5:B15,"*a*")
Selgitus
Funktsioon COUNTIF loeb lahtrid kriteeriumidele vastavas vahemikus. Näiteks vahemikus "a" sisaldavate lahtrite loendamiseks võite kasutada järgmist.
=COUNTIF(range,"a") // exact match
Pange tähele, et see on täpne vaste. Loendamiseks peab lahter sisaldama täpselt "a". Kui lahter sisaldab muid märke, siis seda ei arvestata.
Näidatud probleemi puhul tahame loendada lahtrid, mis sisaldavad konkreetset teksti, see tähendab, et tekst võib olla lahtris kõikjal. Selleks peame tärn (*) kasutama metamärgina. Näiteks a-tähte sisaldavate lahtrite lugemiseks kõikjal, mida me kasutame:
=COUNTIF(range,"*a*")
Tärn valemis tähendab "sobitab suvalise arvu tähemärke, sealhulgas nulli", nii et see muster loeb kõik lahtrid, mis sisaldavad mis tahes asendis tähti "a", vasakul või paremal koos muude tähtedega või ilma. Näites kasutatud valemid järgivad sama mustrit:
=COUNTIF(B5:B15,"*a*") // contains "a" =COUNTIF(B5:B15,"*2*") // contains "2" =COUNTIF(B5:B15,"*-S*") // contains "-s" =COUNTIF(B5:B15,"*x*") // contains "x"
Märkus. COUNTIF ei ole tõstutundlik.
Lahtriviitega
Seda valemit saate hõlpsalt kohandada, et kriteeriumide jaoks kasutada mõne teise lahtri sisu. Näiteks kui A1 sisaldab teksti, mida soovite sobitada, võite kasutada järgmist.
=COUNTIF(range,"*"&A1&"*")
Loendage konkreetseid numbreid
Ülaltoodud valem COUNTIF ei toimi, kui otsite kindlat arvu ja lahtrid sisaldavad arvandmeid. Selle põhjuseks on see, et metamärk paneb COUNTIF-i automaatselt otsima ainult teksti (st otsima "2" mitte ainult 2). Kuna tekstiväärtust ei leita kunagi õiges arvus, tagastab COUNTIF nulli.
Konkreetse arvu arvulise sisu lugemiseks võite kasutada funktsiooni SEARCH ja funktsiooni ISNUMBER põhjal erinevat valemit:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
Selles valemis teksti on tekst, mida otsite, ja valikut on lahtrivahemik soovite arvestada. Kui OTSI leiab vaste, tagastab see numbri ja funktsioon ISNUMBER teisendab numbri TÕENE või VÄÄR väärtuseks. Kuna kontrollime rohkem kui ühte lahtrit, on tulemuseks massiiv või TÕENE ja VALE väärtus. Topeltnegatiivi kasutatakse TRUE ja FALSE väärtuste teisendamiseks 1-ks ja 0-ks ning funktsioon SUMPRODUCT tagastab lõpptulemusena massiivi summa.
SEARCH käsitleb kõiki numbreid automaatselt tekstina, nii et see leiab näiteks numbri 201 ühe. Samuti ei ole positsiooni tähistamiseks vaja kasutada metamärke, sest SEARCH vaatab kogu lahtris oleva teksti automaatselt läbi.
Suur- ja väiketähtedega versioon
Suur- ja väiketähtede loendamiseks saate kasutada funktsiooni FIND põhinevat valemit.
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Siin kasutatakse funktsiooni LEIDA otsingu asemel, kuna FIND on tõstutundlik. Muus osas töötab valem nagu ülalkirjeldatud valem ISNUMBER + LEIA.
