Bendroji formulė
=COUNTIF(range,"*txt*")
Santrauka
Norėdami suskaičiuoti langelius, kuriuose yra tam tikras tekstas, galite naudoti funkciją COUNTIF su pakaitos simboliu. Parodytame pavyzdyje formulė E5 yra:
=COUNTIF(B5:B15,"*a*")
Paaiškinimas
Funkcija COUNTIF skaičiuoja kriterijus atitinkančio diapazono langelius. Pavyzdžiui, norėdami suskaičiuoti langelių skaičių diapazone, kuriame yra „a“, galite naudoti:
=COUNTIF(range,"a") // exact match
Tačiau atkreipkite dėmesį, kad tai tiksli atitiktis. Kad būtų galima suskaičiuoti, langelyje turi būti tiksliai „a“. Jei langelyje yra kitų simbolių, jis nebus skaičiuojamas.
Dėl parodytos problemos norime suskaičiuoti langelius, kuriuose yra konkretaus teksto, o tai reiškia, kad tekstas gali būti bet kurioje langelio vietoje. Norėdami tai padaryti, turime naudoti žvaigždutę (*) kaip pakaitą. Pavyzdžiui, norėdami suskaičiuoti langelius, kuriuose yra raidė „a“, naudokite bet kur:
=COUNTIF(range,"*a*")
Žvaigždutė formulėje reiškia „sutapti su bet kokiu simbolių skaičiumi, įskaitant nulį“, taigi šis modelis skaičiuos kiekvieną langelį, kuriame bet kurioje pozicijoje yra „a“, kairėje arba dešinėje yra arba be kitų simbolių. Pavyzdyje naudojamos formulės atitinka tą patį modelį:
=COUNTIF(B5:B15,"*a*") // contains "a" =COUNTIF(B5:B15,"*2*") // contains "2" =COUNTIF(B5:B15,"*-S*") // contains "-s" =COUNTIF(B5:B15,"*x*") // contains "x"
Pastaba: „COUNTIF“ neskiria didžiųjų ir mažųjų raidžių.
Su langelio nuoroda
Šią formulę galite lengvai pakoreguoti, kad pagal kriterijus būtų naudojamas kito langelio turinys. Pvz., Jei A1 yra tekstas, kurį norite suderinti, galite naudoti:
=COUNTIF(range,"*"&A1&"*")
Suskaičiuokite konkrečius skaičius
Aukščiau pateikta COUNTIF formulė neveiks, jei ieškote konkretaus skaičiaus, o langeliuose yra skaitiniai duomenys. Taip yra todėl, kad pakaitos simbolis COUNTIF automatiškai priverčia ieškoti tik teksto (ty ieškoti „2“, o ne tik 2). Kadangi tikruoju skaičiumi niekada nebus rasta teksto reikšmė, COUNTIF grąžins nulį.
Norėdami suskaičiuoti konkretų skaičių skaitiniame turinyje, galite naudoti kitą formulę, pagrįstą funkcija PAIEŠKA ir funkcija ISNUMBER, kaip tai buvo:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
Šioje formulėje tekstas yra jūsų ieškomas tekstas, o diapazonas yra norimų suskaičiuoti langelių diapazonas. Kai PAIEŠKA nustato atitiktį, ji grąžins skaičių, o funkcija ISNUMBER pavers skaičių TIKRA arba NETIESA. Kadangi tikriname daugiau nei vieną langelį, rezultatas bus masyvas arba TRUE ir FALSE reikšmės. Dvigubas neigiamas yra naudojamas konvertuojant TRUE ir FALSE reikšmes į 1s ir 0s, o funkcija SUMPRODUCT grąžins masyvo sumą kaip galutinį rezultatą.
PAIEŠKA automatiškai traktuoja visus skaičius kaip tekstą, todėl ras, pavyzdžiui, 1 skaičių 201. Be to, norint nurodyti vietą, nereikia naudoti pakaitos simbolių, nes PAIEŠKA automatiškai peržiūrės visą langelio tekstą.
Didžiųjų ir mažųjų raidžių versija
Skaičiuojant didžiųjų ir mažųjų raidžių skaičių, galite naudoti formulę, pagrįstą funkcija RASTI:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Čia vietoj SEARCH naudojama funkcija FIND, nes FIND skiria didžiosioms ir mažosioms raidėms. Kitais atžvilgiais formulė veikia kaip aukščiau aprašyta formulė ISNUMBER + FIND.
