Mūsų straipsnyje „Skaičiuoti ląsteles, kuriose yra tam tikras tekstas“ suskaičiavome kiekvieną langelį, kuriame yra konkretus tekstas. Šiame straipsnyje mes sužinosime, kaip skaičiuoti ląsteles, kuriose yra viena ar kita vertė. Kitaip tariant, skaičiuojant pagal ARBA logiką.
Galbūt manote, kad COUNTIF funkciją galite naudoti du kartus ir tada sudėti. Bet tai neteisingas posūkis. Sužinosite kodėl.
Bendra formulė
= SUMPRODUCT (-((ISNUMBER (PAIEŠKA („šis“, diapazonas)) + ISNUMBER (PAIEŠKA („tas“, diapazonas)))> 0))
"Šis": tai pirmasis tekstas, kurį norite įtraukti į diapazoną. Tai gali būti bet koks tekstas.
"Tai": tai antras tekstas, kurį norite skaičiuoti diapazone. Tai gali būti bet koks tekstas.
Diapazonas: Tai diapazonas arba masyvas, kuriame yra teksto, į kurį skaičiuosite savo konkrečius tekstus.
Pažiūrėkime pavyzdį:
Pavyzdys:
Čia aš turiu atsiliepimų nuo A2 iki A8. Noriu suskaičiuoti komentarų, kuriuose yra „gerų“ ar „gražių“ žodžių, skaičių.
Norėdami suskaičiuoti langelius, kuriuose yra tam tikras tekstas „geras“ arba „gražus“, parašykite šią formulę:
= SUMPRODUCT (-((ISNUMBER (SEARCH („geras“, A2: A8)) + ISNUMBER (SEARCH („nice“, A2: A8)))> 0))
Matote, kad yra keturi komentarai, kuriuose yra „geras“ arba „gražus“.
Kaip tai veikia?
Pradėkime nuo vidaus.
SEARCH („geras“, A2: A8): ši dalis grąžina #VALUE klaidos ir skaičių masyvą, nurodantį rasto teksto padėtį. {8; #VALUE!;#VALUE!;#VALUE!;#VALUE!; 30; 24}
ISNUMBER (SEARCH („SEARCH“ („gerai“, A2: A8))): ši formulės dalis patikrina kiekvieną reikšmę masyvo grąžoje pagal SEARCH funkciją, ar ji yra skaičius, ar ne, ir pateikia masyvą TRUE ir FALSE. Šiame pavyzdyje grąžinama {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (PAIEŠKA („nice“, A2: A8)): ši formulės dalis daro tą patį, tačiau šį kartą ląstelėje ieško „gražaus“ žodžio ir pateikia langelio TRUE ir FALSE masyvo masyvą, kuriame yra „nice“ . “
Toliau šiuos masyvus pridėjome naudodami + ženklą. Tai loginės vertės į 1 ir 0 ir jas sumuoja. Viduje tai atrodys taip: {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Matote, kad A7 komentare yra tekstų „gražus“ ir „geras“. Ir čia tai skaičiuojama dvigubai. Mes to nenorime. Taigi mes dar kartą palyginame, kaip kiekvieną skaičių paversti teisingu ir klaidingu.
((ISNUMBER (PAIEŠKA („gera“, A2: A8)) + ISNUMBER (PAIEŠKA („gražu“, A2: A8)))>0): Ši formulės dalis konvertuoja gautą masyvą į TRUE ir FALSE. Jei vertė yra didesnė nei 0, tai yra TIKRA KLAIDA. Čia bus {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
TRUE skaičius masyve yra eilutės, kurioje yra „geri“ arba „gražūs“ žodžiai, pavadinimas.
SUMPRODUCT (-((ISNUMBER (SEARCH („geras“, A2: A8)) + ISNUMBER (SEARCH („nice“, A2: A8)))> 0))
Toliau mes naudojame neigiamus simbolius, kad juos paverstume skaičiais. Galiausiai „SUMPRODUCT“ apibendrina masyvą ir pateikia ląstelių, kuriose yra „geras“ arba „gražus“, skaičių.
Taip pat galite naudoti funkciją SUM, bet tada turėsite įvesti šią formulę kaip masyvo formulę.
Kodėl nepasinaudojus COUNTIFS?
Dėl dvigubo skaičiavimo.
Jei langelyje yra abu tekstai, jis bus skaičiuojamas du kartus, o tai nėra teisinga.
Bet jei norite, kad tai įvyktų, naudokite šią formulę,
= SUMA (SKAIČIUS (A2: A8, {"*gražus*", "*geras*"}))
Mūsų pavyzdyje jis grįš 5. Aš tai paaiškinau čia.
Skaičiuojant didžiosios ir mažosios raidės
Siūlomas sprendimas skaičiuoja pateiktą tekstą, neatsižvelgiant į raidžių raidę. Jei norite suskaičiuoti didžiosios ir mažosios raidės, pakeiskite SEARCH funkciją FIND funkcija.
= SUMPRODUCT (-((ISNUMBER (RASTI(„šis“, diapazonas)) + ISNUMBER (RASTI(„tas“, diapazonas)))> 0))
TheRASTI funkcija yra didžiųjų ir mažųjų raidžių funkcija. Tai grąžina rasto teksto padėtį.
Taigi taip, vaikinai, šitaip galite suskaičiuoti langelių, kuriuose yra šis arba kitas tekstas, skaičių. Taip pat galite spustelėti funkcijų pavadinimus formulėje ir perskaityti apie šią funkciją. Supratau, kad juos išplėtojau.
Kaip patikrinti, ar langelyje yra tam tikras tekstas „Excel“
Kaip patikrinti tekstų sąrašą eilutėje „Excel“
Gaukite „COUNTIFS“ dviejų kriterijų atitiktį „Excel“
Gaukite COUNTIFS su keliais kriterijais „Excel“
Populiarūs straipsniai:
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.