Kategorizace řetězce na základě některých slov byla jedním z mých základních úkolů při analýze dat. Pokud se například v průzkumu zeptáte lidí, co se jim na konkrétním chytrém telefonu líbí, stejné odpovědi budou obsahovat různá slova. U fotoaparátu mohou používat slova jako fotografie, videa, selfie atd. Všechny znamenají fotoaparát. Je tedy velmi důležité kategorizovat věty předtím, abyste získali nějaké smysluplné informace.
V tomto článku se naučíme, jak kategorizovat v Excelu pomocí klíčových slov.
Vezměme si příklad průzkumu, o kterém jsme mluvili.
Příklad: Kategorizujte data shromážděná z průzkumu v aplikaci Excel
Provedli jsme tedy průzkum o našem novém smartphonu xyz. Zeptali jsme se našich zákazníků, co se jim na telefonu xyz líbí, a zachytili jsme jejich odpověď v Excelu. Nyní potřebujeme vědět, komu se naše LED obrazovka, reproduktor a kamera líbila.
Za tímto účelem jsme připravili seznam klíčových slov, která mohou odkazovat na kategorii, jak můžete vidět na obrázku níže. Abych to pochopil, nechal jsem to malé.
Zpětná vazba je v rozsahu A2: A9, klíčová slova jsou v E2: E10 a kategorie je v F2: F10.
Obecný vzorec pro vytváření kategorií bude:
{= INDEX (kategorie, MATCH (TRUE, ISNUMBER (SEARCH (slova, věta)), 0))}
Všimněte si složených závorek, je to maticový vzorec. Je třeba zadat pomocí kláves CTRL+SHIFT+ENTER.
Kategorie: Je to rozsah, který obsahuje seznam kategorií. Věty nebo zpětné vazby budou kategorizovány podle těchto hodnot. V našem případě je to F2: F10.
Slova: je to rozsah, který obsahuje seznam klíčových slov nebo frází. Ty budou prohledány v věty. Tady je E2: E10.
Věta: je to věta, která bude kategorizována. Je to jedna relativní buňka.
Protože nyní známe každou proměnnou a funkci použitou pro kategorizaci v Excelu, implementujme ji na našem příkladu.
Do buňky B2 napište tento vzorec a stiskněte CTRL+SHIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Zkopírujte vzorec a zobrazte kategorii každé zpětné vazby.
Potřebujeme, aby byl seznam slov a kategorií opraven, neměly by se měnit, když kopírujeme vzorec, a proto jsem uvedl absolutní odkaz na klíčová slova a kategorie. Zatímco chceme, aby se věty při kopírování vzorce měnily, proto jsem použil relativní odkaz na A2. Zde si můžete přečíst informace o odkazování v aplikaci Excel.
Nyní můžete připravit zprávu o tom, kolik uživatelů má rád LED obrazovku, reproduktor a kameru.
Jak to funguje?
Jádrem vzorce je ISNUMBER (HLEDAT($ E $ 2: $ E $ 10, A2)): Zde jsem to podrobně vysvětlil. Funkce HLEDAT vyhledává každou hodnotu klíčových slov ($ E $ 2: $ E $ 10) ve větě A2. Vrací pole nalezeného umístění slova nebo #HODNOTA (pro slovo nenalezeno). Nakonec pro tento příklad budeme mít řadu 9 prvků. {#HODNOTA!; 5; #HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA;#HODNOTA! Dále použijeme funkci ISNUMBER k převodu tohoto pole na užitečná data. Převádí jej na pole TRUE a FALSE. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Nyní je vše jednoduchá shoda indexů. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): funkce MATCH hledá TRUE, ve výsledném poli a vrací index prvního nalezeného TRUE. což je 2 pro tento případ.
INDEX($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Další, funkce INDEX se dívá na 2. pozici v kategorii ($ F $ 2: $ F $ 10) což je LED obrazovka. Nakonec tento vzorec kategorizuje tento text nebo zpětnou vazbu jako LED obrazovku.
Rozlišování malých a velkých písmen:
Aby byla tato funkce rozlišována velká a malá písmena, použijte funkci HLEDAT místo funkce HLEDAT. Funkce FIND ve výchozím nastavení rozlišuje velká a malá písmena.
{= INDEX (Kategorie, MATCH (PRAVDA, ČÍSLO (NAJÍT (slova,věta)),0))}
Slabé body:
1. Pokud jsou dvě klíčová slova nalezena ve stejné větě, věta bude kategorizována podle prvního klíčového slova v seznamu.
Zachycení textu do jiného slova. Předpokládejme, že hledáme LAD v rozsahu. Poté budou započítána slova obsahující LAD. Například žebříky budou započítány pro LAD, protože obsahuje LAD. Dávejte si na to tedy pozor. Osvědčeným postupem je normalizovat data co nejvíce.
Toto byl tedy rychlý návod, jak kategorizovat data v Excelu. Snažil jsem se to vysvětlit tak jednoduše, jak to jen šlo. Prosím, dejte mi vědět, pokud máte nějaké pochybnosti o tomto článku nebo o jiných článcích souvisejících s Excelem.
Stáhnout soubor:
Kategorizujte řetězec podle klíčových slovJak zkontrolovat, zda buňka obsahuje konkrétní text v aplikaci Excel
Jak zkontrolovat seznam textů v řetězci v aplikaci Excel
Získejte shodu dvou kritérií COUNTIFS v aplikaci Excel
Získejte COUNTIFS s NEBO pro více kritérií v aplikaci Excel
Oblíbené č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.