Bendroji formulė
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Santrauka
Norėdami suskaičiuoti, kiek kartų konkretus žodis (ar bet kuris pakaitinis elementas) pasirodo langelių diapazone, galite naudoti formulę, pagrįstą funkcijomis PAKEITIMAS, LENAS ir SUMPRODUKTAS. Parodytame pavyzdyje formulė C11 yra:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Pastaba: Šiame puslapyje pateikta formulė skaičiuoja diapazono žodžio esmę. Pvz., Jei langelyje yra du žodžio egzemplioriai, tai duos bendrą skaičių. Jei norite tik suskaičiuoti langelius, kuriuose yra konkretus žodis, peržiūrėkite šią paprastą formulę, pagrįstą funkcija COUNTIF.
Paaiškinimas
Bendrojoje formulės versijoje rng reiškia tikrintiną diapazoną, o txt yra žodis ar poskyris, kurį reikia skaičiuoti.
Parodytame pavyzdyje B5: B8 yra diapazonas, kurį reikia patikrinti, o C2 yra tekstas (žodis ar poskyris), kurį reikia skaičiuoti.
Kiekvienam diapazono langeliui SUBSTITUTE pašalina poskyrį iš pradinio teksto, o LEN apskaičiuoja teksto ilgį be pakraščio. Tada šis skaičius atimamas iš pirminio teksto ilgio. Rezultatas yra simbolių, kuriuos pašalino SUBSTITUTE, skaičius.
Tada pašalintų simbolių skaičius padalijamas iš pakraščio ilgio. Taigi, jei poskyris arba žodis yra 5 simbolių ilgio ir po jo pašalinimo iš pradinio teksto trūksta 10 simbolių, žinome, kad poskyris / žodis originalo tekste pasirodė du kartus.
Kadangi aukščiau pateiktas skaičiavimas yra įtrauktas į funkciją SUMPRODUCT, rezultatas yra masyvas, kuriame yra skaičius kiekvienam diapazono langeliui. Šie skaičiai nurodo kiekvienos ląstelės substravimo atvejų skaičių. Šiame pavyzdyje masyvas atrodo taip: (1; 1; 0; 1)
Galiausiai „SUMPRODUCT“ susumuoja visus masyvo elementus, kad gautų bendrą substravimo atvejį ląstelių diapazone.
Neatsižvelgiama į atvejį
SUBSTITUTE yra didžiųjų ir mažųjų raidžių funkcija, todėl ji atitiks didžiųjų ir mažųjų raidžių vykdant pakeitimą. Jei reikia skaičiuoti tiek žodžio, tiek pakraščio didžiųjų ir mažųjų raidžių atvejus, prieš vykdydami pakeitimą, naudokite funkciją AUKŠTYN, esančią SUBSTITUTE, kad tekstas būtų paverstas didžiosiomis raidėmis:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
