U našem članku, Brojanje ćelija koje sadrže određeni tekst, prebrojali smo svaku ćeliju koja sadrži određeni tekst. U ovom ćemo članku naučiti kako brojati ćelije koje sadrže ovu ili onu vrijednost. Drugim riječima, brojanje logikom ILI.
Možda mislite da možete koristiti funkciju COUNTIF dva puta, a zatim ih zbrojiti. Ali to je pogrešan zaokret. Znat ćete zašto.
Opća formula
= SUMPRODUCT (-((ISNUMBER (SEARCH ("this", raspon)) + ISNUMBER (SEARCH ("that", raspon)))> 0))
"Ovaj": to je prvi tekst koji želite brojati u rasponu. To može biti bilo koji tekst.
"Da": to je drugi tekst koji želite brojati u rasponu. To može biti bilo koji tekst.
Raspon: Ovo je raspon ili niz koji sadrži tekst u kojem ćete računati za svoje određene tekstove.
Pogledajmo primjer:
Primjer:
Ovdje imam povratne komentare u rasponu od A2 do A8. Želim brojati broj komentara koji sadrže "dobre" ili "lijepe" riječi.
Za prebrojavanje ćelija koje sadrže određene tekstove "dobro" ili "lijepo" napišite ovu formulu:
= SUMPRODUCT (-((ISNUMBER (SEARCH ("dobro", A2: A8)) + ISNUMBER (SEARCH ("lijepo", A2: A8)))> 0))
Možete vidjeti da postoje četiri komentara koji sadrže "dobro" ili "lijepo".
Kako radi?
Počnimo iznutra.
SEARCH ("dobro", A2: A8): ovaj dio vraća niz pogrešaka #VALUE i brojeva, koji predstavljaju položaj pronađenog teksta. {8; #VREDNOST!;#VRIJEDNOST!;#VRIJEDNOST!;#VRIJEDNOST!; 30; 24}
ISNUMBER (SEARCH ("dobro", A2: A8)): ovaj dio formule provjerava svaku vrijednost u nizu vraćenu pomoću funkcije SEARCH, ako je to broj ili ne, i vraća niz TRUE i FALSE. U ovom primjeru vraća {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (SEARCH ("lijepo", A2: A8)): ovaj dio formule radi isto, ali ovaj put traži "lijepu" riječ u ćeliji i vraća niz baze TRUE i FALSE na ćeliji koja sadrži "nice . ”
Zatim smo te nizove dodali znakom +. Logičke vrijednosti pretvara u 1 i 0 i zbraja ih. Interno će to izgledati ovako, {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Možete vidjeti da komentar u A7 ima i tekst "lijepo" i "dobro". I to se ovdje dvostruko računa. Mi to ne želimo. Stoga napravimo još jednu usporedbu za pretvaranje svakog broja u Točno i Netočno.
((ISNUMBER (SEARCH ("dobro", A2: A8)) + ISNUMBER (SEARCH ("lijepo", A2: A8)))>0): Ovaj dio formule pretvara rezultirajući niz u TRUE i FALSE. Ako je vrijednost veća od 0, to je TRUE, inače FALSE. Ovdje će biti {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
Broj TRUE u nizu je naziv niza koji sadrži "dobre" ili "lijepe" riječi.
SUMPRODUCT (-((ISNUMBER (SEARCH ("dobro", A2: A8)) + ISNUMBER (SEARCH ("lijepo", A2: A8)))> 0))
Zatim koristimo - negativne simbole da ih pretvorimo u brojeve. I na kraju SUMPRODUCT sažima niz kako bi vratio broj ćelija koje sadrže "dobro" ili "lijepo".
Također možete koristiti funkciju ZBOR, ali tada ćete morati unijeti ovu formulu kao formulu niza.
Zašto ne biste koristili COUNTIFS?
Zbog dvostrukog broja.
Ako ćelija sadrži oba teksta, tada će se brojati dva puta, što u ovom scenariju nije točno.
Ali ako želite da se to dogodi, upotrijebite ovu formulu,
= ZBIR (COUNTIFS (A2: A8, {"*lijepo*", "*dobro*"}))
Vratit će 5 u našem primjeru. Ovdje sam to objasnio.
Određivanje osjetljivosti na velika i mala slova
Predloženo rješenje broji dati tekst bez obzira na velika slova. Ako želite brojati velika i mala slova, zamijenite funkciju SEARCH funkcijom FIND.
= SUMPRODUCT (-((ISNUMBER (PRONAĆI("ovo", raspon)) + ISNUMBER (PRONAĆI("to", raspon)))> 0))
ThePRONAĆI funkcija je osjetljiva na velika i mala slova. Vraća položaj pronađenog teksta.
Pa da, dečki, ovako možete izbrojati broj ćelija koje sadrže ovaj ili onaj tekst. Također možete kliknuti na nazive funkcija u formuli da biste pročitali o toj funkciji. Razumljivo sam ih razradio.
Kako provjeriti sadrži li ćelija specifičan tekst u Excelu
Kako provjeriti popis tekstova u nizu u Excelu
Nabavite COUNTIFS za podudaranje dva kriterija u Excelu
Nabavite BROJKE S ILI za više kriterija u Excelu
Popularni članci:
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.