Mūsu rakstā Šūnu skaits, kas satur specifisku tekstu, mēs saskaitījām katru šūnu, kurā ir noteikts teksts. Šajā rakstā mēs uzzināsim, kā saskaitīt šūnas, kurās ir šī vai tā vērtība. Citiem vārdiem sakot, skaitot ar VAI loģiku.
Jūs domājat, ka varat divas reizes izmantot funkciju COUNTIF un pēc tam tās saskaitīt. Bet tas ir nepareizs pagrieziens. Jūs zināt, kāpēc.
Vispārīgā formula
= SUMPRODUCT (-((ISNUMBER (SEARCH ("šis", diapazons)) + ISNUMBER (SEARCH ("tas", diapazons)))> 0))
“Šis”: tas ir pirmais teksts, kuru vēlaties iekļaut diapazonā. Tas var būt jebkurš teksts.
“Tas”: tas ir otrais teksts, kuru vēlaties skaitīt diapazonā. Tas var būt jebkurš teksts.
Diapazons: Šis ir diapazons vai masīvs, kurā ir teksts, kurā tiks ņemti vērā jūsu konkrētie teksti.
Apskatīsim piemēru:
Piemērs:
Šeit man ir atsauksmju komentāri diapazonā no A2 līdz A8. Es vēlos saskaitīt to komentāru skaitu, kuros ir “labi” vai “jauki” vārdi.
Lai saskaitītu šūnas, kurās ir konkrēti teksti “labi” vai “jauki”, uzrakstiet šo formulu:
= SUMPRODUCT (-((ISNUMBER (SEARCH ("labs", A2: A8)) + ISNUMBER (SEARCH ("nice", A2: A8)))> 0))
Var redzēt, ka ir četri komentāri, kas satur vai nu “labu”, vai “jauku”.
Kā tas strādā?
Sāksim no iekšpuses.
MEKLĒT ("labi", A2: A8): šī daļa atgriež #VALUE kļūdas un skaitļu masīvu, kas attēlo atrastā teksta pozīciju. {8; #VALUE!;#VALUE!;#VALUE!;#VALUE!; 30; 24}
ISNUMBER (SEARCH ("MEKLĒT (" labi ", A2: A8))): šī formulas daļa pārbauda katru vērtību masīva atdevē, izmantojot funkciju MEKLĒT, ja tas ir skaitlis vai nē, un atgriež masīvu TRUE un FALSE. Šajā piemērā tas atgriež vērtību {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (MEKLĒT ("jauki", A2: A8)): šī formulas daļa dara to pašu, bet šoreiz šūnā tiek meklēts "jauks" vārds un šūnā tiek atrasts patiesas un nepatiesas bāzes masīvs, kas satur "jauku" . ”
Tālāk mēs esam pievienojuši šos masīvus, izmantojot + zīmi. Tā Būla vērtības 1 un 0 un saskaita tos. Iekšēji tas izskatīsies šādi: {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Jūs varat redzēt, ka komentārā A7 ir gan teksti “jauki”, gan “labi”. Un šeit tas tiek skaitīts divreiz. Mēs to nevēlamies. Tāpēc mēs veicam vēl vienu salīdzinājumu, lai katru skaitli pārvērstu par patiesu un nepatiesu.
((SKAITS (MEKLĒT ("labi", A2: A8)) + ISNUMBER (MEKLĒT ("jauki", A2: A8)))>0): Šī formulas daļa pārveido iegūto masīvu par TRUE un FALSE. Ja vērtība ir lielāka par 0, tā ir PATIESA FALSE. Šeit tas būs {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
TRUE skaits masīvā ir virknes nosaukums, kurā ir “labi” vai “jauki” vārdi.
SUMPRODUCT (-((ISNUMBER (SEARCH ("labs", A2: A8)) + ISNUMBER (SEARCH ("nice", A2: A8)))> 0))
Tālāk mēs izmantojam negatīvos simbolus, lai tos pārvērstu skaitļos. Visbeidzot, SUMPRODUCT apkopo masīvu, lai atgrieztu šūnu skaitu, kas satur “labu” vai “jauku”.
Varat arī izmantot funkciju SUM, bet pēc tam šī formula būs jāievada kā masīva formula.
Kāpēc neizmantot COUNTIFS?
Dubultā skaita dēļ.
Ja šūna satur abus tekstus, tā tiks skaitīta divas reizes, kas šajā gadījumā nav pareizi.
Bet, ja vēlaties, lai tas notiktu, izmantojiet šo formulu,
= SUMMA (COUNTIFS (A2: A8, {"*jauki*", "*labi*"}))
Mūsu piemērā tas atgriezīs 5. Es to šeit paskaidroju.
Ievērojot reģistrjutību
Piedāvātais risinājums ņem vērā doto tekstu neatkarīgi no burtu burtiem. Ja vēlaties saskaitīt burtu reģistrjutības, nomainiet funkciju MEKLĒT ar funkciju FIND.
= SUMPRODUCT (-((ISNUMBER (ATRAST("šis", diapazons)) + ISNUMBER (ATRAST("tas", diapazons)))> 0))
TheATRAST funkcija ir reģistrjutīga funkcija. Tas atgriež atrastā teksta pozīciju.
Jā, puiši, šādi jūs varat saskaitīt šūnu skaitu, kurās ir vai nu šis, vai cits teksts. Varat arī noklikšķināt uz funkciju nosaukumiem formulā, lai lasītu par šo funkciju. Esmu tos saprotami izstrādājis.
Kā pārbaudīt, vai šūnā ir īpašs teksts programmā Excel
Kā pārbaudīt virkņu tekstu sarakstu programmā Excel
Iegūstiet COUNTIFS divu kritēriju atbilstību programmā Excel
Iegūstiet COUNTIFS ar VAI vairākiem kritērijiem programmā Excel
Populāri raksti:
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā programmā Excel izmantot funkciju COUNTIF : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.