Kategorizácia reťazca na základe niektorých slov bola jednou z mojich základných úloh pri analýze údajov. Napríklad v prieskume, ak sa ľudí opýtate, čo sa im páči na konkrétnom smartfóne, rovnaké odpovede budú obsahovať rôzne slová. Vo fotoaparáte môžu používať slová ako fotografie, videá, selfie atď. Všetky znamenajú fotoaparát. Preto je veľmi dôležité vopred kategorizovať vety, aby ste získali zmysluplné informácie.
V tomto článku sa naučíme, ako kategorizovať v programe Excel pomocou kľúčových slov.
Zoberme si príklad prieskumu, o ktorom sme hovorili.
Príklad: Kategorizujte údaje zhromaždené z prieskumu v programe Excel
Urobili sme teda prieskum o našom novom smartfóne xyz. Opýtali sme sa našich zákazníkov, čo sa im páči na telefóne xyz, a zachytili sme ich odpoveď v programe Excel. Teraz musíme vedieť, komu sa páčila naša LED obrazovka, reproduktor a kamera.
Na tento účel sme pripravili zoznam kľúčových slov, ktoré môžu odkazovať na kategóriu, ako vidíte na obrázku nižšie. Aby som to pochopil, ponechal som to malé.
Spätná väzba je v rozsahu A2: A9, kľúčové slová sú v E2: E10 a kategória v F2: F10.
Obecný vzorec na vytváranie kategórií bude:
{= INDEX (Kategória, ZÁPAS (PRAVDA, ČÍSLO (VYHĽADÁVANIE (slová, veta)), 0))}
Všimnite si zložených zátvoriek, je to vzorec poľa. Je potrebné zadať pomocou klávesov CTRL+SHIFT+ENTER.
Kategória: Je to rozsah, ktorý obsahuje zoznam kategórií. Vety alebo spätné väzby budú kategorizované podľa týchto hodnôt. V našom prípade je to F2: F10.
Slová: je to rozsah, ktorý obsahuje zoznam kľúčových slov alebo fráz. Budú sa hľadať v vety. Tu je E2: E10.
Veta: je to veta, ktorá bude kategorizovaná. Je to jedna relatívna bunka.
Pretože teraz poznáme každú premennú a funkciu použitú na kategorizáciu v programe Excel, implementujme ju na našom príklade.
Do bunky B2 napíšte tento vzorec a stlačte CTRL+SHIFT+ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Skopírujte vzorec a zobrazte kategóriu každej spätnej väzby.
Potrebujeme opraviť zoznam slov a kategórií, ktoré by sa pri kopírovaní vzorca nemali meniť, a preto som poskytol absolútny odkaz na kľúčové slová a kategórie. Aj keď chceme, aby sa vety pri kopírovaní vzorca menili, preto som použil relatívny odkaz na A2. Tu si môžete prečítať informácie o odkazovaní v programe Excel.
Teraz môžete pripraviť správu o tom, koľkým používateľom sa páči LED obrazovka, reproduktor a kamera.
Ako to funguje?
Jadrom vzorca je ISNUMBER (VYHĽADÁVANIE($ E $ 2: $ E $ 10, A2)): Tu som to podrobne vysvetlil. Funkcia VYHĽADÁVAŤ vyhľadáva každú hodnotu kľúčových slov ($ E $ 2: $ E $ 10) vo vete A2. Vráti pole nájdenej polohy slova alebo #HODNOTA (pre slovo nenájdené). Nakoniec budeme mať pre tento príklad pole 9 prvkov. {#HODNOTA!; 5; #HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA;#HODNOTA!;#HODNOTA!} Ďalej použijeme funkciu ISNUMBER na konverziu tohto poľa na užitočné údaje. Konvertuje ho na pole TRUE a FALSE. {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Teraz je všetko jednoduchá zhoda indexov. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): funkcia MATCH hľadá TRUE, vo výslednom poli a vráti index prvého nájdeného TRUE. čo je v tomto prípade 2.
INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Ďalej sa funkcia INDEX pozerá na 2. pozíciu v kategórii ($ F $ 2: $ F $ 10) čo je LED obrazovka. Nakoniec tento vzorec kategorizuje tento text alebo spätnú väzbu ako LED obrazovku.
Rozlišovanie malých a veľkých písmen:
Ak chcete, aby bola táto funkcia rozlišovaná medzi veľkými a malými písmenami, použite funkciu HLEDAŤ namiesto funkcie VYHĽADÁVAŤ. Funkcia FIND štandardne rozlišuje veľké a malé písmena.
{= INDEX (Kategória, MATCH (TRUE, ISNUMBER (FIND (slová,veta)),0))}
Slabé body:
1. Ak sa dve kľúčové slová nachádzajú v tej istej vete, veta bude kategorizovaná podľa prvého kľúčového slova v zozname.
Zachytenie textu do iného slova. Predpokladajme, že hľadáme LAD v rozsahu. Potom sa započítajú slová obsahujúce LAD. Napríklad rebríky sa budú počítať pre LAD, pretože obsahujú LAD. Dávajte si na to teda pozor. Osvedčeným postupom je normalizovať údaje čo najviac.
Toto bol teda rýchly návod, ako kategorizovať údaje v programe Excel. Snažil som sa to vysvetliť čo najjednoduchšie. Ak máte akékoľvek pochybnosti o tomto článku alebo akýchkoľvek článkoch súvisiacich s Excelom, dajte nám vedieť.
Stiahnuť súbor:
Kategorizujte reťazec na základe kľúčových slovAko skontrolovať, či bunka obsahuje konkrétny text v programe Excel
Ako skontrolovať zoznam textov v reťazci v programe Excel
Získajte zhodu dvoch kritérií COUNTIFS v Exceli
Získajte COUNTIFS s ALEBO pre viac kritérií v programe Excel
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.