Clasificarea șirului pe baza unor cuvinte a fost una dintre sarcinile mele de bază în analiza datelor. De exemplu, într-un sondaj dacă întrebați ce le place la un anumit telefon inteligent, aceleași răspunsuri vor avea o varietate de cuvinte. Pentru aparatul foto, pot folosi cuvinte precum fotografii, videoclipuri, selfie-uri etc. Toate acestea implică aparat foto. Deci, este foarte important să clasificați propozițiile înainte, pentru a obține niște informații semnificative.
În acest articol, vom învăța cum să clasificăm în Excel folosind cuvinte cheie.
Să luăm exemplul sondajului despre care am vorbit.
Exemplu: clasificați datele colectate dintr-un sondaj în Excel
Deci, am făcut un sondaj despre noul nostru smartphone xyz. Am întrebat clienții noștri ce le place la telefonul xyz și le-am captat răspunsul în Excel. Acum trebuie să știm cine a plăcut ecranului, difuzorului și camerei noastre LED.
Pentru aceasta, am pregătit o listă de cuvinte cheie care pot face referire la o categorie, după cum puteți vedea în imaginea de mai jos. Ca să înțeleg, l-am ținut mic.
Feedback-urile sunt în intervalul A2: A9, cuvintele cheie sunt în E2: E10 și categoria este în F2: F10.
Formula generică pentru a crea categorii va fi:
{= INDEX (Categorie, MATCH (ADEVĂRAT, NUMĂR (CĂUTARE (cuvinte, propoziție)), 0))}
Rețineți acoladele, este o formulă matrice. Trebuie introdus folosind CTRL + SHIFT + ENTER.
Categorie: Este gama care conține lista categoriilor. Propozițiile sau feedback-urile vor fi clasificate după aceste valori. În cazul nostru este F2: F10.
Cuvinte: este gama care conține o listă de cuvinte cheie sau expresii. Acestea vor fi căutate în propoziții. Iată-l pe E2: E10.
Propoziție: propoziția va fi clasificată. Este o singură celulă relativă.
Deoarece acum cunoaștem fiecare variabilă și funcție utilizată pentru clasificarea în Excel, să o implementăm în exemplul nostru.
În celula B2 scrieți această formulă și apăsați CTRL + SHIFT + ENTER.
{= INDEX ($ F $ 2: $ F $ 10, MATCH (ADEVĂRAT, NUMĂR (CĂUTARE ($ E $ 2: $ E $ 10, A2)), 0))}
Copiați formula pentru a vedea categoria fiecărui feedback.
Trebuie să listăm cuvinte și categorii fixe, acestea nu ar trebui să se schimbe pe măsură ce copiem formula, de aceea am dat referințe absolute la cuvinte cheie și categorii. Deși dorim ca propozițiile să se schimbe pe măsură ce copiem formula, de aceea am folosit o referință relativă ca A2. Aici puteți citi înțelegerea referințelor în Excel.
Acum puteți pregăti un raport cu câți utilizatori le plac ecranul LED, difuzorul și camera.
Cum functioneaza?
Nucleul formulei este ISNUMBER (SEARCH($ E $ 2: $ E $ 10, A2)): Am explicat-o în detaliu aici. Funcția CĂUTARE caută fiecare valoare a cuvintelor cheie ($ E $ 2: $ E $ 10) în propoziția lui A2. Returnează o serie de locații găsite ale cuvântului sau #VALOARE (pentru cuvântul care nu a fost găsit). În cele din urmă vom avea o serie de 9 elemente pentru acest exemplu. {#VALUE!; 5; #VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!; # VALUE!}. Apoi folosim funcția ISNUMBER pentru a converti această matrice în date utile. Îl convertește în matrice de ADEVĂRAT și FALS. {FALS; ADEVĂRAT; FALS; FALS; FALS; FALS; FALS; FALS; FALS}.
Acum, totul este o potrivire simplă a indexului. MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): funcția MATCH caută TRUE, în matricea rezultată și returnează indexul primului TRUE găsit. care este 2 pentru acest caz.
INDEX($ F $ 2: $ F $ 10, MATCH (ADEVĂRAT, ISNUMBER (CĂUTARE ($ E $ 2: $ E $ 10, A2)), 0)): În continuare, funcția INDEX se uită la a doua poziție din categorie ($ F $ 2: $ F $ 10) care este ecran LED. În cele din urmă, această formulă clasifică acest text sau feedback ca ecran LED.
Sensibilizarea la majuscule și minuscule:
Pentru a face ca această funcție să fie sensibilă la majuscule, utilizați funcția FIND în loc de funcția SEARCH. Funcția FIND este sensibilă la majuscule și minuscule.
{= INDEX (Categorie, MATCH (ADEVĂRAT, NUMĂR (GĂSEȘTE (cuvinte,propoziție)),0))}
Punctele slabe:
1. Dacă două dintre cuvintele cheie se găsesc în aceeași propoziție, propoziția va fi clasificată în funcție de primul cuvânt cheie din listă.
Captarea textului într-un alt cuvânt. Să presupunem că căutăm LAD într-o gamă. Apoi vor fi numărate cuvintele care conțin LAD. De exemplu, Scările vor fi numărate pentru LAD, deoarece conține LAD în el. Deci, aveți grijă. Cea mai bună practică este să vă normalizați datele cât mai mult posibil.
Deci, acesta a fost un tutorial rapid despre cum să clasificați datele în Excel. Am încercat să-l explic cât de simplu pot. Vă rog să-mi spuneți dacă aveți vreo îndoială cu privire la acest articol sau la orice articole legate de Excel.
Descărcare fișier:
Clasificați șirul pe baza cuvintelor cheieCum să verificați dacă celula conține text specific în Excel
Cum să verificați o listă de texte în șir în Excel
Obțineți COUNTIFS Two Criteria Match în Excel
Obțineți COUNTIFS cu sau pentru criterii multiple în Excel
Articole populare:
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.