Estrarre dati da elenco Excel

In questa guida vediamo come estrarre tutti i dati possibili da una survey con delle domande a risposta aperta utilizzando Excel. La survey ha una domanda molto specifica, aperta: "Qual è la skill che vorresti migliorare quest'anno?".

Il problema che abbiamo è trovare un senso alle risposte aperte per creare poi dei cluster con i temi più importanti, anche per analizzare in maniera dettagliata le necessità arrivate dalla survey. Il database fittizio ci aiuterà a trovare una guida per definire le attività da organizzare nei prossimi anni.

Il file è compatibile unicamente con la versione 365, ma puoi sempre utilizzarlo nella versione online di Excel, che è completamente gratuita!

I file di partenza per estrarre i dati da un elenco

Ci servono, in questo caso, due tabelle: TBL e TBL_OUT.

La nostra tabella di partenza, chiamata TBL, ha tutte le risposte aperte su una sola colonna.

TBL_OUT è la tabella dove inseriamo tutte le parole che non ci interessano (una colonna)

La seconda tabella è necessaria perchè la nostra procedura prevede di "esplodere" ogni risposta con le singole parole. Avremo quindi tutti gli articoli come "i, il, la" e altre parole poco significative quali "competenze, abilità, migliorare". Nella nostra visione finale vogliamo evitare di vedere queste parole, dato che non aggiungono niente all'analisi.

Come estrarre tutte le parole di varie celle su Excel

Vediamo che, fra le parole chiave, pare che PowerPoint ed Excel siano fra le skill più richieste. Abbiamo anche parole come analisi, sviluppo, team che potrebbero darci qualche informazione in più.

La procedura per estrarre tutte le parole inserite in una tabella su Excel è, però, piuttosto complicata:

=LET(words;UNICI(A.COL(DIVIDI.TESTO(TESTO.UNISCI("|";VERO;TBL[ANSWERS]);" ";"|";;;"")));
words_filter;FILTRO(words;(words<>"")*(VAL.NON.DISP(CONFRONTA.X(words;TBL_OUT[WORDS])))); words_calc;CONTA.SE(TBL[ANSWERS];" "&words_filter&" ")+ CONTA.SE(TBL[ANSWERS];words_filter&"*")+CONTA.SE(TBL[ANSWERS];"*"&words_filter);
words_ranking;DATI.ORDINA(STACK.ORIZ(words_filter;words_calc);2;-1);words_ranking)

Questa formula, come dicevamo, è piuttosto complessa e utilizza diverse funzioni di Excel. Vediamo i singoli passaggi nel dettaglio, ricordando che la funzione LET viene utilizzata per assegnare un nome a diverse espressioni che verranno utilizzate nella formula.

In questo caso avremo words (le parole), words_filter (le parole precedenti ma escludendo le parole inserite nella tabella chiamata TBL_OUT), words_calc (per contare quante volte si ripetano le parole precedenti nelle nostre risposte) e words_ranking (che le mette in ordine da quella più presente a quella meno comune).

Vediamo le singole componenti nel dettaglio, iniziando con words:

=LET(words;UNICI(A.COL(DIVIDI.TESTO(TESTO.UNISCI("|";VERO;TBL[ANSWERS]);" ";"|";;;"")))

• La funzione TESTO.UNISCI prende tutto il contenuto dell'intera tabella, dividendo le singole righe con il carattere "|". In questo caso avremmo una singola, lunghissima cella con tutto il contenuto
• La funzione DIVIDI.TESTO viene utilizzata per dividere i testi in parole utilizzando il carattere "|". Questo è utile per poi filtrare le parole univoche e calcolare il conteggio di ciascuna parola. L'intuizione di utilizzo della funzione DIVIDI.TESTO in maniera dinamica arriva da questo interessante video di Wyn Hopkins

Al momento quindi abbiamo preso tutte le parole e poi le abbiamo nuovamente divise per singola riga. Avremo quindi, per ogni riga, una serie di celle con le singole parole divise fra di loro. Dobbiamo però far sì che siano tutte nella stessa colonna:

• La funzione A.COL ci permette di restituire un array solamente in colonna. Quindi in questo caso la funzione creerà una singola, lunghissima colonna con tutte le parole viste in precedenza. Rispetto a prima c'è una differenza, perchè ora tutte le parole sono separate in singole celle e quindi possono essere ulteriormente riviste
• La funzione UNICI recupera tutti questi valori e ci restituisce soltanto quelli univoci - abbiamo quindi tutte le parole, separate da uno spazio, inserite nelle risposte precedenti

Vediamo ora words_filter:

words_filter;FILTRO(words;(words<>"")*(VAL.NON.DISP(CONFRONTA.X(words;TBL_OUT[WORDS]))))

• La funzione FILTRO viene utilizzata per filtrare le parole (words) trovate in precedenza, in modo da rimuovere eventuali celle vuote o parole che sono già presenti nella colonna TBL_OUT[WORDS]. La moltiplicazione con * si rende necessaria perchè vogliamo che entrambe le condizioni siano soddisfatte, viceversa potevamo inserire un +. Se vuoi approfondire la funzione FILTRO, dai un'occhiata qui
• La funzione CONFRONTA.X viene utilizzata per confrontare ogni parola filtrata con quelle presenti nella colonna TBL_OUT[WORDS] e restituire un valore booleano che indica se la parola è già presente o meno. La funzione CONFRONTA.X agisce come la funzione CONFRONTA, restituendo la posizione relativa di una stringa all'interno di un intervallo - in questo caso, lo farebbe solo se la parola fosse presente nell'altra tabella. La formula, adattata, arriva da questo interessante video di Mike Girvin
• La funzione VAL.NON.DISP viene utilizzata per verificare, con un VERO/FALSO, il risultato della funzione CONFRONTA.X. Quindi sarà vera se la parola NON esiste nella tabella TBL_OUT (dato che in questo caso avremo un errore nella funzione precedente con CONFRONTA.X), mentre sarà falsa se la parola ESISTE nella seconda tabella (perchè come risultato avremo una posizione della parola nella tabella complessiva)
• Per semplificare: in questo caso words_filter recupera tutte le parole che o non sono vuote oppure non sono presenti nella tabella chiamata TBL_OUT, che ci serve proprio per filtrare le informazioni che ci servono. La formula funziona anche mettendo solo TBL_OUT, ma per motivi di lettura penso abbia senso mantenere l'intestazione

Vediamo adesso come contare quante volte si ripetano le parole all'interno del nostro array con words_calc:

words_calc;CONTA.SE(TBL[ANSWERS];" "&words_filter&" ")+ CONTA.SE(TBL[ANSWERS];words_filter&"*")+CONTA.SE(TBL[ANSWERS];"*"&words_filter)

• La funzione CONTA.SE viene utilizzata per contare quante volte ogni parola filtrata appare nella colonna TBL[ANSWERS]. Ci sono tre diverse chiamate a CONTA.SE che considerano la parola con uno spazio a sinistra e a destra, oppure i casi nei quali la parola è la prima (words_filter&"*") o l'ultima della stringa ("*"&words_filter). Questo è utile per catturare tutte le parole, prendendo le varie combinazioni possibili

A questo punto non ci resta che creare un array, che va anche ordinato, chiamato words_ranking:

words_ranking;DATI.ORDINA(STACK.ORIZ(words_filter;words_calc);2;-1);words_ranking)

• STACK.ORIZ crea un array che comprenda sia le nostre parole filtrate (words_filter) e il relativo conteggio (words_calc).

• La funzione DATI.ORDINA viene utilizzata per ordinare l'elenco di parole filtrate in base al conteggio di ciascuna parola. Inserendo 2 stiamo spiegando che vogliamo che siano ordinate secondo il loro conteggio (words_calc), mentre -1 equivale a dire che le vogliamo in ordine decrescente.

Inseriamo poi, alla fine, words_ranking dato che è quello che desideriamo sia visualizzato: ovvero un solo array con le parole e il relativo conteggio, ignorando tutti i passaggi intermedi descritti in precedenza.

Un'ulteriore evoluzione della formula poteva essere l'inserimento delle intestazioni - in questo caso sarebbe bastato inserirli con le varie combinazioni possibili di STACK.VERT e STACK.ORIZ.

Come ordinare su Excel con varie condizioni

Se volessimo invece avere prima l'ordine decrescente e poi tutte le skill in ordine alfabetico, la formula sarebbe leggermente diversa:

=LET(words;UNICI(A.COL(DIVIDI.TESTO(TESTO.UNISCI("|";VERO;TBL[ANSWERS]);" ";"|";;;"")));
words_filter;FILTRO(words;(words<>"")*(VAL.NON.DISP(CONFRONTA.X(words;TBL_OUT[WORDS])))); words_calc;CONTA.SE(TBL[ANSWERS];" "&words_filter&" ")+ CONTA.SE(TBL[ANSWERS];words_filter&"*")+CONTA.SE(TBL[ANSWERS];"*"&words_filter);
words_ranking;DATI.ORDINA.PER(STACK.ORIZ(words_filter;words_calc);words_calc;-1;words_filter;1);words_ranking)

In questo caso words_ranking richiederebbe la funzione DATI.ORDINA.PER in modo da ordinare il nostro array prima per words_calc (che è la seconda colonna) in ordine decrescente (-1) per poi ordinare per words_filter (le parole, cioè la prima colonna) in ordine crescente (1).

Le barre dei dati che vedi per i valori complessivi sono creati con la formattazione condizionale - se vuoi approfondire, dai un'occhiata a questa guida dedicata!

Spero che la guida ti sia stata utile! Penso possa essere un ottimo procedimento per recuperare velocemente informazioni da database molto testuali e corposi, anche se poi servirà sempre un nostro passaggio per dare veramente un senso ai dati.

In questo senso, il file può aiutare a fare una prima scrematura, ma dovremo poi essere noi a capire se abbia senso analizzare solamente "Power" oppure "Power BI", valutando come le combinazioni di parole abbiano un impatto rilevante anche nella parte finale di racconto delle evidenze.

In questo senso, ricordati sempre che la funzione CONTA.SE, come vista nella guida, permette di evidenziare tutti i casi nei quali vi sia una certa stringa in una cella, grazie all'utilizzo di "*" che indica qualsiasi valore.