Už sme sa naučili počítať bunky, ktoré obsahujú konkrétny text, pomocou funkcie COUNTIF. V tomto článku sa naučíme počítať, koľkokrát sa slovo objaví v programe Excel. Inými slovami, spočítame, koľkokrát sa slovo vyskytlo v programe Excel.
Generický vzorec
= SUMPRODUCT (LEN (rozsah) -LEN (SUBSTITUTE (rozsah, slovo, "")))/LEN (slovo)
Rozsah: Rozsah, v ktorom sa pokúšate spočítať konkrétne slovo.
Slovo: Slovo, ktoré chcete spočítať.
Vezmime si príklad a pochopíme, ako to funguje.
Príklad: Počítajte „šťastné“ slovo v programe Excel.
Tu máme niekoľko viet v rôznych bunkách. Musíme započítať výskyty slova „šťastný“ v tomto rozsahu.
Napíšte tento vzorec do bunky D2.
= SUMPRODUCT (LEN (A2: A4) -LEN (NÁHRADA (A2: A4, D1, "")))/LEN (D1)
Použitie tejto funkcie Excel počíta, koľkokrát sa konkrétne slovo „šťastný“ objaví v dosahu A2: A4. Tento vzorec programu Excel vráti počet ako 4.
Ako to funguje?
Cieľom je získať súčet počtu znakov daného slova v rozsahu a potom ho rozdeliť podľa dĺžky slova. Ak sa napríklad Happy vyskytne v rozsahu 4 -krát, je to celková dĺžka 20 (4*5) v rozsahu. Ak vydelíme 20 číslom 5, dostaneme 4. Čo je počet slov v rozsahu.
Zbúrajme to zvnútra.
LEN (A2: A4): toto vráti počet znakov v každej bunke ako pole {49; 27; 34}.
Ďalší LEN (NÁHRADA (A2: A4, D1, ""))): Náhradná funkcia prepisuje slovo v D1 na „“ v každej bunke rozsahu A2: A4. Potom funkcia Len vráti počet znakov z týchto substituovaných viet v poli {39; 22; 29}.
LEN (D1): toto vráti dĺžku slova v D1, ktorá 5 (šťastná).
Teraz je vzorec zjednodušený na SUMPRODUCT ({49; 27; 34} - {39; 22; 29})/5. Po odčítaní polí má SUMPRODUCT SUMPRODUCT ({10; 5; 5})/5. Funkcia pridá pole a dostaneme 20/5. To nám dáva výsledok 4.
Počítanie nerozlišuje malé a veľké písmená
Pretože v SUBSTITUTE sa rozlišujú malé a veľké písmená, vyššie uvedený vzorec bude ignorovať každé slovo, ktoré nemá rovnaké písmená, tj. „Šťastný“. Aby vyššie uvedený vzorec ignoroval malé a veľké písmená, musíme zmeniť veľkosť písmen na každé slovo na slovo, ktoré hľadáme. Takto sa to robí.
= SUMPRODUCT (LEN (A2: A4) -LEN (NÁHRADA (HORNÁ (A2: A4), UPPER (D1), "")))/LEN (D1)
Teraz na prípade už nezáleží. Táto funkcia spočíta každé slovo v D1 bez ohľadu na prípad.
Možné chyby:
Bude sa počítať slovná časť iného slova: V tomto prípade, keby sme mali slovo „šťastie“ (napríklad napríklad viem, že neexistuje žiadne slovo ako toto), započítalo by sa to tiež. Aby ste tomu zabránili, mohli ste slová obklopiť špecifikáciami „“ & D1 & „“. Ale keď sa slovo objaví ako prvé alebo posledné vo vete, tiež to zlyhá.
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu: Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel: Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel: Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel: Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.