Už jsme se naučili počítat buňky, které obsahují konkrétní text, pomocí funkce COUNTIF. V tomto článku se naučíme počítat, kolikrát se slovo objeví v dosahu Excelu. Jinými slovy, spočítáme, kolikrát se slovo vyskytlo v rozsahu aplikace Excel.
Obecný vzorec
= SUMPRODUCT (LEN (rozsah) -LEN (SUBSTITUTE (rozsah, slovo, "")))/LEN (slovo)
Rozsah: Rozsah, ve kterém se pokoušíte počítat konkrétní slovo.
Slovo: Slovo, které chcete počítat.
Vezměme si příklad a pochopíme, jak to funguje.
Příklad: Počítejte „šťastné“ slovo v dosahu Excelu.
Zde máme několik vět v různých buňkách. Musíme počítat výskyty slova „šťastný“ v tomto rozsahu.
Tento vzorec napište do buňky D2.
= SUMPRODUCT (DÉLKA (A2: A4) -LEN (NÁHRADA (A2: A4, D1, "")))/DÉLKA (D1)
Pomocí této funkce Excel počítá, kolikrát se konkrétní slovo „šťastný“ objeví v dosahu A2: A4. Tento vzorec aplikace Excel vrátí počet jako 4.
Jak to funguje?
Cílem je získat součet počtu znaků daného slova v rozsahu a poté vydělit délkou slova. Pokud se například Happy vyskytuje 4krát v rozsahu, je to celková délka 20 (4*5) v dosahu. Vydělíme -li 20 čísly 5, dostaneme 4. Což je počet slov v rozsahu.
Pojďme to strhnout zevnitř.
LEN (A2: A4): vrací počet znaků v každé buňce jako pole {49; 27; 34}.
Další LEN (SUBSTITUTE (A2: A4, D1, "")): Náhradní funkce přepíše slovo v D1 na "" v každé buňce rozsahu A2: A4. Poté funkce Len vrátí počet znaků z těchto substituovaných vět v poli {39; 22; 29}.
LEN (D1): vrací délku slova v D1, která 5 (šťastná).
Nyní je vzorec zjednodušen na SUMPRODUCT ({49; 27; 34} - {39; 22; 29})/5. Po odečtení polí má SUMPRODUCT SUMPRODUCT ({10; 5; 5})/5. Funkce přidá pole a dostaneme 20/5. To nám dává výsledek 4.
Počítání nerozlišuje velká a malá písmena
Protože SUBSTITUTE rozlišuje velká a malá písmena, výše uvedený vzorec bude ignorovat jakékoli slovo, které nemá stejná písmena, tj. „Happy“. Aby výše uvedený vzorec ignoroval velká a malá písmena, musíme změnit velikost písmen každého slova na velikost slova, které hledáme. Takto se to dělá.
= SUMPRODUCT (DÉLKA (A2: A4) -LEN (NÁHRADA (HORNÍ (A2: A4), HORNÍ (D1), "")))/DÉLKA (D1)
Nyní už na případu nezáleží. Tato funkce spočítá každé slovo v D1 bez ohledu na případ.
Možné chyby:
Bude započítána slovní část jiného slova: V tomto příkladu, kdybychom měli slovo „štěstí“ (jen například vím, že neexistuje žádné slovo jako toto), bylo by také započítáno. Abyste tomu zabránili, mohli jste slova obklopit specifikacemi „“ & D1 & „“. Ale když se slovo objeví jako první nebo poslední ve větě, také to selže.
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity: Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu: Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel: Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel: Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.