V našem članku Štejemo celice, ki vsebujejo določeno besedilo, smo prešteli vsako celico, ki vsebuje določeno besedilo. V tem članku se bomo naučili šteti celice, ki vsebujejo to ali ono vrednost. Z drugimi besedami, štetje z logiko OR.
Morda mislite, da lahko funkcijo COUNTIF uporabite dvakrat in jih nato seštejete. Toda to je napačen obrat. Vedeli boste zakaj.
Splošna formula
= SUMPRODUCT (-((ISNUMBER (SEARCH ("to", obseg)) + ISNUMBER (SEARCH ("to", obseg)))> 0))
"To": to je prvo besedilo, ki ga želite šteti v obsegu. Lahko je poljubno besedilo.
"To": je drugo besedilo, ki ga želite šteti v obsegu. Lahko je poljubno besedilo.
Obseg: To je obseg ali matrika, ki vsebuje besedilo, v katerem boste šteli svoja določena besedila.
Poglejmo primer:
Primer:
Tukaj imam povratne komentarje v razponu od A2 do A8. Prešteti želim število komentarjev, ki vsebujejo "dobre" ali "lepe" besede.
Če želite šteti celice, ki vsebujejo določena besedila "dobro" ali "lepo", napišite to formulo:
= SUMPRODUCT (-((ISNUMBER (SEARCH ("dobro", A2: A8)) + ISNUMBER (ISKANJE ("lepo", A2: A8)))> 0))
Vidite lahko, da obstajajo štirje komentarji, ki vsebujejo "dobro" ali "lepo".
Kako deluje?
Začnimo od znotraj.
ISKANJE ("dobro", A2: A8): ta del vrne polje z napako in številkami #VALUE, ki predstavlja položaj najdenega besedila. {8; #VREDNOST!;#VREDNOST!;#VREDNOST!;#VREDNOST!; 30; 24}
ISNUMBER (SEARCH ("dobro", A2: A8)): ta del formule preveri vsako vrednost v matriki, ki jo vrne funkcija SEARCH, če je to število ali ne, in vrne niz TRUE in FALSE. V tem primeru vrne {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (SEARCH ("lepo", A2: A8)): ta del formule dela enako, vendar tokrat išče "lepo" besedo v celici in vrne niz vrednosti TRUE in FALSE na celici, ki vsebuje "nice . "
Nato smo te matrike dodali z znakom +. Logične vrednosti pretvori v 1 in 0 ter jih sešteje. Notranje bo videti tako: {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Vidite lahko, da ima komentar v A7 besedilo "lepo" in "dobro". In tukaj se šteje dvakrat. Tega nočemo. Tako naredimo še eno primerjavo za pretvorbo vsakega števila v True in False.
((ISNUMBER (SEARCH ("dobro", A2: A8)) + ISNUMBER (SEARCH ("lepo", A2: A8)))>0): Ta del formule pretvori nastalo matriko v TRUE in FALSE. Če je vrednost večja od 0, je TRUE ali pa FALSE. Tukaj bo {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
Število TRUE v matriki je ime niza, ki vsebuje "dobre" ali "lepe" besede.
SUMPRODUCT (-((ISNUMBER (SEARCH ("dobro", A2: A8)) + ISNUMBER (ISKANJE ("lepo", A2: A8)))> 0))
Nato uporabimo negativne simbole, da jih pretvorimo v številke. In na koncu SUMPRODUCT povzame niz, da vrne število celic, ki vsebujejo "dobro" ali "lepo".
Uporabite lahko tudi funkcijo SUM, potem pa boste morali to formulo vnesti kot formulo matrike.
Zakaj ne bi uporabili COUNTIFS?
Zaradi dvojnega štetja.
Če celica vsebuje obe besedili, se bo štela dvakrat, kar v tem scenariju ni pravilno.
Če pa želite, da se to zgodi, uporabite to formulo,
= SUM (COUNTIFS (A2: A8, {"*lepo*", "*dobro*"}))
V našem primeru bo vrnil 5. Tukaj sem razložil.
Štetje velikih in malih črk
Predlagana rešitev šteje dano besedilo ne glede na velike črke. Če želite šteti ujemanja z velikimi in malimi črkami, zamenjajte funkcijo SEARCH s funkcijo FIND.
= SUMPRODUCT (-((ISNUMBER (NAJTI("to", obseg)) + ISNUMBER (NAJTI("to", obseg)))> 0))
TheNAJTI funkcija je občutljiva na velike in male črke. Vrne položaj najdenega besedila.
Torej ja, fantje, tako lahko preštejete število celic, ki vsebujejo to ali ono besedilo. Če želite prebrati o tej funkciji, lahko tudi kliknete imena funkcij v formuli. Razumljivo sem jih razložil.
Kako preveriti, ali celica vsebuje določeno besedilo v Excelu
Kako preveriti seznam besedil v nizu v Excelu
Pridobite ujemanja dveh meril COUNTIFS v Excelu
Pridobite COUNTIFS z OR za več meril v Excelu
Priljubljeni članki:
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.