Categorizzare le stringhe in base ad alcune parole era uno dei miei compiti fondamentali nell'analisi dei dati. Ad esempio, in un sondaggio se chiedi alle persone cosa gli piace di un particolare smartphone, le stesse risposte avranno una varietà di parole. Per la fotocamera, possono usare parole come foto, video, selfie ecc. Tutti implicano fotocamera. Quindi è molto importante classificare le frasi prima, per ottenere alcune informazioni significative.
In questo articolo, impareremo come classificare in Excel utilizzando parole chiave.
Prendiamo l'esempio del sondaggio di cui abbiamo parlato.
Esempio: categorizzare i dati raccolti da un sondaggio in Excel
Quindi, abbiamo fatto un sondaggio sul nostro nuovo smartphone xyz. Abbiamo chiesto ai nostri clienti cosa gli piace del telefono xyz e abbiamo catturato la loro risposta in excel. Ora dobbiamo sapere a chi è piaciuto il nostro schermo LED, l'altoparlante e la fotocamera.
Per questo, abbiamo preparato un elenco di parole chiave che possono fare riferimento a una categoria, come puoi vedere nell'immagine qui sotto. Per capire, l'ho tenuto piccolo.
I feedback sono nell'intervallo A2: A9, le parole chiave sono in E2: E10 e la categoria è in F2: F10.
La formula generica per creare le categorie sarà:
{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(parole,frase)),0))}
Nota le parentesi graffe, è una formula di matrice. Deve essere inserito utilizzando CTRL+MAIUSC+INVIO.
Categoria: È l'intervallo che contiene l'elenco delle categorie. Le frasi o i feedback saranno classificati in base a questi valori. Nel nostro caso è F2:F10.
Parole: è l'intervallo che contiene l'elenco di parole chiave o frasi. Questi verranno cercati nel frasi. Ecco E2:E10.
Frase: è la frase che verrà categorizzata. È una singola cellula relativa.
Poiché ora conosciamo ogni variabile e funzione utilizzata per la categorizzazione in Excel, implementiamola nel nostro esempio.
Nella cella B2 scrivi questa formula e premi CTRL+MAIUSC+INVIO.
{=INDICE($F$2:$F$10,CONFRONTA(VERO,VAL.NUMERO(RICERCA($E$2:$E$10,A2)),0))}
Copia la formula per vedere la categoria di ogni feedback.
Abbiamo bisogno di un elenco di parole e categorie fisse, non dovrebbero cambiare mentre copiamo la formula, quindi ho dato un riferimento assoluto a parole chiave e categorie. Mentre vogliamo che le frasi cambino mentre copiamo la formula, ecco perché ho usato il riferimento relativo di come A2. Puoi leggere informazioni sui riferimenti in Excel qui.
Ora puoi preparare un rapporto su quanti utenti apprezzano lo schermo LED, l'altoparlante e la fotocamera.
Come funziona?
Il nucleo della formula è ISNUMBER(SEARCH($E$2:$E$10,A2)): L'ho spiegato in dettaglio qui. La funzione CERCA ricerca ogni valore delle parole chiave ($E$2:$E$10) nella frase di A2. Restituisce un array di posizione trovata della parola o #VALORE (per la parola non trovata). Infine avremo un array di 9 elementi per questo esempio. {#VALUE!;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}. Successivamente usiamo la funzione ISNUMBER per convertire questo array in dati utili. Lo converte in array di VERO e FALSO. {FALSO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}.
D'ora in poi, tutto è semplice index match. CONFRONTA(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0): la funzione CONFRONTA cerca TRUE, nell'array risultante e restituisce l'indice del primo TRUE trovato. che è 2 per questo caso.
INDICE($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0)): Successivamente, la funzione INDEX esamina la seconda posizione nella categoria ($F$2:$F$10) che è Schermo LED. Infine, questa formula classifica questo testo o feedback come schermo LED.
Rendere sensibile alle maiuscole:
Per rendere questa funzione distinzione tra maiuscole e minuscole, utilizzare la funzione TROVA invece della funzione CERCA. La funzione TROVA fa distinzione tra maiuscole e minuscole per impostazione predefinita.
{=INDICE(Categoria,MATCH(TRUE,ISNUMBER(FIND(parole,frase)),0))}
I punti deboli:
1.Se due parole chiave vengono trovate nella stessa frase, la frase verrà classificata in base alla prima parola chiave nell'elenco.
Catturare il testo all'interno di un'altra parola. Supponiamo di cercare LAD in un intervallo. Quindi verranno conteggiate le parole contenenti LAD. Ad esempio, le scale verranno conteggiate per KOP poiché contiene KOP. Quindi stai attento. La migliore pratica consiste nel normalizzare i dati il più possibile.
Quindi questo è stato un breve tutorial su come classificare i dati in Excel. Ho cercato di spiegarlo nel modo più semplice possibile. Per favore fatemi sapere se avete dei dubbi su questo articolo o su altri articoli relativi a Excel.
Download file:
Classifica la stringa in base alle parole chiaveCome verificare se la cella contiene testo specifico in Excel
Come controllare un elenco di testi in stringa in Excel
Ottieni la corrispondenza di due criteri COUNTIFS in Excel
Ottieni COUNTIFS con OR per più criteri in Excel
Articoli popolari:
50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.