Mēs jau esam iemācījušies saskaitīt šūnas, kas satur noteiktu tekstu, izmantojot funkciju COUNTIF. Šajā rakstā mēs iemācīsimies saskaitīt, cik reizes vārds parādās Excel diapazonā. Citiem vārdiem sakot, mēs saskaitīsim, cik reižu vārds radās Excel diapazonā.
Vispārīgā formula
= SUMPRODUCT (LEN (diapazons) -LEN (SUBSTITUTE (diapazons, vārds, "")))/LEN (vārds)
Diapazons: Diapazons, kurā jūs mēģināt saskaitīt konkrēto vārdu.
Vārds: Vārds, kuru vēlaties saskaitīt.
Ņemsim piemēru un sapratīsim, kā tas darbojas.
Piemērs: saskaitiet “laimīgu” vārdu Excel diapazonā.
Šeit mums ir daži teikumi dažādās šūnās. Mums ir jāuzskaita vārda “laimīgs” sastopamība šajā diapazonā.
Ierakstiet šo formulu šūnā D2.
= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUTE (A2: A4, D1, "")))/LEN (D1)
Izmantojot šo funkciju, Excel saskaita, cik reizes konkrētais vārds “laimīgs” parādās diapazonā A2: A4. Šī Excel formula atgriež skaitli kā 4.
Kā tas strādā?
Ideja ir iegūt konkrētā vārda rakstzīmju skaitu diapazonā un pēc tam dalīt ar vārda garumu. Piemēram, ja laimīgais ir 4 reizes diapazonā, tā kopējais garums ir 20 (4*5) diapazonā. Ja mēs dalām 20 ar 5, mēs iegūstam 4. Kāds ir vārdu skaits diapazonā.
Izjauksim to no iekšpuses.
LEN (A2: A4): tiek atgriezts rakstzīmju skaits katrā šūnā kā masīvs {49; 27; 34}.
Nākamais LEN (AIZSTĀJIS (A2: A4, D1, "")): Aizstājējfunkcija aizstāj vārdu D1 ar "" katrā diapazona A2: A4 šūnā. Tad funkcija Len atgriež rakstzīmju skaitu no šiem aizstātajiem teikumiem masīvā {39; 22; 29}.
LEN (D1): tas atgriež vārda garumu D1, kas ir 5 (laimīgs).
Tagad formula ir vienkāršota līdz SUMPRODUCT ({49; 27; 34} - {39; 22; 29})/5. Pēc masīvu atņemšanas SUMPRODUCT ir SUMPRODUCT ({10; 5; 5})/5. Funkcija pievieno masīvu, un mēs iegūstam 20/5. Kas dod mums rezultātu 4.
Skaitīšana, neņemot vērā reģistrus
Tā kā SUBSTITUTE ir reģistrjutīgs, augšējā formulā tiks ignorēti visi vārdi, kuriem nav vienādu un lielo burtu, t.i., “laimīgs”. Lai augstākminētā formula neņemtu vērā lielo un mazo burtu lietojumu, mums ir jāmaina katra vārda burts uz meklējamo vārdu. Šādi tas tiek darīts.
= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUTE (UPPER (A2: A4), UPPER (D1), "")))/LEN (D1)
Tagad lietai vairs nav nozīmes. Šī funkcija skaitīs katru vārdu D1 neatkarīgi no gadījuma.
Iespējamās kļūdas:
Tiks skaitīta cita vārda daļa: Šajā piemērā, ja mums būtu vārds “laime” (tikai, piemēram, es zinu, ka nav tāda vārda kā šis), tas arī tiktu ieskaitīts. Lai no tā izvairītos, jūs varētu būt apņēmis vārdus ar burtiem “”, “D1” un “”. Bet, kad vārds teikumā parādās pirmais vai pēdējais, tas arī neizdosies.
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.