Pulire database Excel

Parliamo di come pulire un database Excel e renderlo utilizzabile. Ci può capitare di aver scaricato delle informazioni da un sito che siano corrette ma disposte in una maniera dalla quale sia impossibile ricavare informazioni utili.

In questo caso, utilizzeremo informazioni prese dalla lista dei 50 film migliori secondo IMDB, un importante sito di cinema internazionale. La sfida è partire da zero e modificare il file in modo che sia possibile farci delle analisi.

Seguendo l'articolo, scopriremo come compiere azioni quali dividere una cella in più parti, estrarre informazioni da una cella e rimuovere velocemente informazioni che non ci interessano da una selezione di celle.

File iniziale:

File finale:

Se vuoi seguire questa guida passo passo, puoi scaricare il file iniziale qui (il file finale è in coda all'articolo):

1. Mettiamo un = che prenda le informazioni dalle righe successive, in modo da avere tutte le informazioni su una riga sola. In B2 avremo =A3 e così via, trascinando la formula.

2. Selezioniamo le colonne da B a I per eliminare le formule appena create. Basta copiare e incollare come valori le intere colonne. Terremo come corrette le righe che abbiano nella colonna A il nome del film e poi dalla colonna B le informazioni relative al film stesso.

 

In pratica:

3A. Forza bruta: ordiniamo il file secondo la colonna B e cancelliamo le celle che non ci interessano (sono quasi tutte assieme a parte i film 6., 7., 8., 9.). Ricordiamo che i film devono essere 50.

3B. Le celle in A che vogliamo mantenere hanno il nome del film nella colonna B. Questo perchè ci interessa avere sulla stessa riga tutte le informazioni relative al film e come vediamo, nella riga 2 abbiamo:

Nome del film / Nome del film ripetuto con numero / censura / durata etc.

Potremmo utilizzare le formule VAL.NUMERO e TROVA per definire quali siano le righe corrette per noi. Ne ho parlato in questo articolo. Procediamo poi a cancellare tutte le righe con "NO" e rimaniamo con i 50 film.

SE(VAL.NUMERO(TROVA(A2;B2));"OKAY";"NO")

4. Verifichiamo che c'è un errore, dato che nella colonna con i Metascore per i film Dangal e Le luci della città abbiamo delle descrizioni di film. Questo perchè nel database di partenza non hanno il Metascore! Quindi spostiamo le celle corrette e sistemiamo il database mettendo "N/A" nel Metascore. Passiamo quindi da:

 

A:

STEP INTERMEDIO

5. Ragioniamo adesso su quali siano le prossime azioni che possiamo fare. Consideriamo le colonne da A a E, poi le colonne da F a K.

In pratica:

Colonna A: Nome. Già corretta, non richiede altre modifiche.

Colonna B: Nome con numero. Ci interessa il numero dato che è l'anno.

Colonna C: Censura, durata, genere. Ci interessano tutti e 3, ma non nella stessa colonna

Colonna D: Voto. Voto medio di IMDB, è già corretto

Colonna E: Rate This. Inutile ai nostri scopi

Quindi le prime action saranno:

Colonne A e D: immutate

Colonne B e C: richiedono delle operazioni aggiuntive per essere fruibili

Colonna E: da rimuovere

Colonne da F a K:

Colonna F: Metascore. Ci interessa il valore, ma non la scritta Metascore

Colonna G: Sinossi. Già corretta

Colonna H: Regista e attori. Vogliamo dividerli in diverse colonne

Colonna I: Numero di voti e boxoffice. Vogliamo dividerli in diverse colonne

Quindi le altre action saranno:

Colonna F: rimuovere la scritta Metascore

Colonna G: immutata

Colonne H e I: dividere i valori all'interno.

Riassumendo:

Colonne A, D, G: immutate

Colonne C, H, I: dividere le informazioni su diverse colonne

Colonna B: estrarre l'anno

Colonna E: eliminare

Colonna F: cancellare la scritta Metascore

6. Colonne A,D,E,G. Non modifichiamo A,D,G e cancelliamo la E.

 

7. Colonna B: estrarre l'anno. L'anno è l'unico elemento che ci interessa in questo caso. Quindi possiamo utilizzare la funzione STRINGA.ESTRAI per estrarre informazioni all'interno di una certa cella. Praticamente diremo alla funzione STRINGA.ESTRAI, utilizzando anche la funzione TROVA, di estrarre i 5 caratteri che troverà dopo che avrà individuato il carattere "(". Ci aspettiamo quindi di avere una intera colonna con risultati quali (1994, (2010 etc. Una prima versione della nostra soluzione è la seguente:

STRINGA.ESTRAI([cella_origine];TROVA([carattere];[cella_origine);[numero_caratteri]

In questo caso specifico:

STRINGA.ESTRAI(B2;TROVA("(";B2;5)

Non siamo ancora soddisfatti dato che abbiamo da riscontrare due potenziali problemi. Il primo è che abbiamo il carattere "(" che ci disturba e ci è inutile per la nostra analisi, quindi dobbiamo rimuoverlo. Il secondo è che con questi metodi di estrazione, spesso i caratteri non verranno considerati come numeri.

Risolviamo entrambe queste problematiche con la funzione DESTRA e un piccolo accorgimento. Praticamente vogliamo far sì che la funzione DESTRA peschi i 4 caratteri finali della nostra cella e che la cella venga considerata come numero. Formula pratica:

DESTRA(STRINGA.ESTRAI(B2;TROVA("(";B2);5);4)+1-1

DESTRA prende le 4 lettere finali, mentre eseguiamo il +1 e -1 finale per non intaccare i dati. Questa operazione sembra inutile ma segnala ad Excel che stiamo lavorando con un numero. In pratica:

L'ultimo passaggio è fare Copia / Incolla Valori nella colonna B per arrivare a questo risultato intermedio:

8. Cancellare il valore Metascore dalla colonna dedicata. Abbiamo due strade in questo caso. La strada A è selezionare la colonna -> Home -> Sostituisci -> Trova: " Metascore" Sostituisci con: [da tenere vuoto]. Da tenere a mente anche lo spazio prima di Metascore. Sicuramente è un metodo valido per procedere, bisogna solo ricordarsi di modificare il valore "N/A" con qualcosa di nostro gradimento, per esempio 0.

La seconda strada prevede l'utilizzo di formule con SINISTRA e TROVA. In sostanza dobbiamo selezionare la colonna che ci interessa e prendere solo i valori prima dello spazio, dato che sono i rating medi. Il +1 e -1 è sempre per il motivo precedente, per far sì che diventino numeri senza far passaggi aggiuntivi.

SINISTRA(K2;TROVA(" ";K2))+1-1

Abbiamo ancora un problema da risolvere. Come ricordiamo, alcuni dei film non hanno il valore Metascore. Decidiamo quindi di inserire 0 al posto di un errore, utilizzando la funzione SE.ERRORE.

Facendo il solito Copia / Incolla Valori, possiamo riassumere il nostro database come segue. In arancione, le colonne corrette da non modificare:

9. Colonne C, H, I (diventate C, G, H avendo eliminato la colonna E). In questo caso vogliamo dividere le informazioni di ognuna di loro secondo alcune informazioni che ci interessano. Partiamo con la colonna censura,durata,genere, inserendo due colonne vuote accanto e spostandola nel database in modo da non essere distratti dalle altre colonne (motivo per il quale passa da essere C a I, ma non è fondamentale):

 

Selezioniamo la colonna I -> Dati -> Testo in colonne -> Delimitato.

Da qui, possiamo definire i delimitatori. Questi definiscono i punti nei quali la cella si deve dividere, quindi in questo caso il valore "|". In altri casi, potremo utilizzare questa formula con gli spazi, con le virgole o altro.

Clicchiamo su Avanti -> Fine -> Okay (quando richiede di sostituire i dati). Le due colonne in aggiunta ci permettono di dividere la colonna in 3 parti senza influenzare il resto del database. Risultato:

Ancora un accorgimento. Dobbiamo modificare le celle per i film Dangal e La parola ai giurati, perchè c'è un errore in quanto non hanno alcun valore per la censura e quindi sono sballati. Dobbiamo eseguire una operazione simile a quella fatta per il Metascore (spostando le celle) per avere un database coerente.

L'operazione per le colonne "Regista e attori" e "Voti e box-office" è esattamente la stessa, quindi non la ripeterò passo passo in questo articolo. Vediamo però come dovrebbe essere il nostro file dopo questo passaggio:

X Punto 10. Ci siamo quasi. Servono però alcuni accorgimenti per rendere il file veramente fruibile, segnalati per singola colonna:

Colonna C "Censura": c'è uno spazio di troppo dovuto all'aver messo il testo in colonne, che eliminiamo con la funzione ANNULLA.SPAZI. In questo modo siamo sicuri che il database sia coerente, ne ho parlato in questo articolo.

Colonna D "Durata": eliminiamo " min" dato che è inutile ai nostri scopi, il procedimento è lo stesso del punto 8, possiamo sia fare con Trova Sostituisci sia con le formule. Attenzione perchè, se usate le formule, c'è da considerare anche il fatto che è scritto " 142 min" e non "142 min" (cella D2) essendoci uno spazio. Spesso vengono trascinati spazi quando mettiamo i dati in colonne, dovremo farci attenzione.

Colonna E "Genere": abbiamo uno spazio in più prima del genere. Lo eliminiamo nello stesso modo fatto per la colonna C.

Colonne I e J "Regista" e "Attori": usando Trova Sostituisci, rimuoviamo le scritte "Director: " e "Directors: " dalla colonna I oltre al " Stars: " della colonna J. Nel caso della colonna I, seguiamo la stessa procedura delle colonne E e C rimuovendo lo spazio di troppo.

Colonna K "Numero di voti su IMDB": come sopra, eliminiamo "Votes: " con il Trova Sostituisci che converte direttamente i valori in numero.

Colonna L "Box-office": ripetiamo l'operazione precedente della colonna K ma dovremo usare Trova Sostituisci varie volte, eliminando" Gross: $" e "M". Poi usiamo sempre Trova Sostituisci facendo Trova "." e Sostituisci con ",", in questo modo avremo il numero di milioni corretto. Tenendo il punto (ad esempio 28.34), questo dato non viene riconosciuto da Excel come numero quindi ci renderebbe più difficile la parte di analisi. Come ultimo passaggio, moltiplichiamo tutti i risultati al box-office per 100000 per trovare il risultato complessivo.

L'accorgimento di togliere tutti gli elementi quali la M di milioni, il $, il "min" di minuti è per rendere il file quanto più preciso possibile. Lavorando con grandi database, queste modifiche fanno una differenza enorme dato che è sempre meglio indicare queste informazioni nella intestazione piuttosto che ripeterle per tutte le celle.

In definitiva: