Vispārēja formula
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Kopsavilkums
Lai saskaitītu, cik reižu konkrēts vārds (vai kāds apakšvirsraksts) parādās šūnu diapazonā, varat izmantot formulu, kuras pamatā ir funkcijas SUBSTITUTE, LEN un SUMPRODUCT. Parādītajā piemērā formula C11 ir:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Piezīme. Šajā lapā esošā formula skaita diapazona vārda klātbūtni. Piemēram, ja šūnā ir divi vārda gadījumi, tas veicinās 2 kopējo skaitu. Ja vēlaties tikai saskaitīt šūnas, kurās ir noteikts vārds, skatiet šo vienkāršo formulu, kuras pamatā ir funkcija COUNTIF.
Paskaidrojums
Formulas vispārīgajā versijā rng apzīmē pārbaudāmo diapazonu, bet txt ir vārds vai apakšvirsraksts, kas jāuzskaita.
Parādītajā piemērā B5: B8 ir pārbaudāmais diapazons, un C2 satur tekstu (vārdu vai apakšvirkni), kas jāuzskaita.
Katrai diapazona šūnai SUBSTITUTE noņem apakšvirsrakstu no sākotnējā teksta, un LEN aprēķina teksta garumu bez apakšvirsmas. Pēc tam šis skaitlis tiek atņemts no sākotnējā teksta garuma. Rezultāts ir rakstzīmju skaits, ko SUBSTITUTE noņēma.
Pēc tam noņemto rakstzīmju skaits tiek dalīts ar apakšvirsmas garumu. Tātad, ja apakšvirkne vai vārds ir 5 rakstzīmes garš un pēc tā noņemšanas no sākotnējā teksta trūkst 10 rakstzīmju, mēs zinām, ka apakšvirsraksts / vārds sākotnējā tekstā parādījās divas reizes.
Tā kā iepriekšminētais aprēķins ir iesaiņots funkcijā SUMPRODUCT, rezultāts ir masīvs, kas satur skaitli katrai diapazona šūnai. Šie skaitļi norāda apakšvirknes gadījumu skaitu katrā šūnā. Šajā piemērā masīvs izskatās šādi: (1; 1; 0; 1)
Visbeidzot, SUMPRODUCT summē visus masīva vienumus, lai iegūtu kopējo apakškārtas gadījumu skaitu šūnu diapazonā.
Lietas ignorēšana
SUBSTITUTE ir reģistrjutīga funkcija, tāpēc tā tiks mainīta, lietojot aizstājēju. Ja jums jāuzskaita gan vārda, gan apakšvirknes lielie un mazie burti, pirms aizstāšanas palaidiet funkciju SUBSTITUTE, izmantojot tekstu UPPER, lai tekstu pārveidotu par lielajiem burtiem:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
