Ho sempre usato Excel per calcoli rapidi e per creare tabelle semplici. Ma a parte le formule più comuni e le tecniche di base per la manipolazione dei dati, non ho mai sentito il bisogno di imparare altre funzioni di Excel, finché i miei progetti non hanno iniziato a diventare più complessi.
Link veloci
Il problema che alla fine mi ha fatto prestare attenzione
A causa di diversi fattori di mercato e dei dazi doganali, acquistare componenti per computer nella mia zona è spesso più costoso che negli Stati Uniti. Volevo sapere quanto stavo pagando in più per gli stessi componenti e se fosse meglio ordinarli direttamente da Amazon o Newegg invece che dai rivenditori locali. Così, ho raccolto dati sui prezzi nell'arco di alcuni mesi per i principali componenti per computer (CPU, GPU e RAM) che i negozi locali in genere importano. Un semplice progetto di monitoraggio, giusto? Sbagliato.
Mi sono ritrovato rapidamente con un caos di dati. Ogni rivenditore esportava le proprie informazioni utilizzando convenzioni di formattazione diverse, rendendo quasi impossibile unire i file. Amazon forniva le date nel formato MM/GG/AAAA, Newegg usava AAAAMMGG e Shopee (il mio negozio locale) usava GG-MM-AAAA.
Le incongruenze non si fermavano qui. I nomi delle colonne variavano notevolmente. Newegg etichettava i prezzi come "retail_price", mentre Amazon usava "unit_price_usd" e Shopee optava per "price_php". Anche la formattazione dei prezzi era problematica, con alcuni file che mostravano "₱18,600" con simboli di valuta inclusi, mentre altri mostravano numeri normali come "320". Persino i nomi dei marchi mancavano di coerenza, apparendo come "gigabyte", "GIGABYTE INC." o "Gigabyte Tech" per lo stesso produttore in file diversi.
Pulire e unire manualmente questi dati mi ha già preso ore. Ho dovuto copiare e incollare tra i file, trovare e sostituire valori incoerenti ed eliminare le righe vuote una per una. Convertire PHP in USD per confrontare i prezzi significava dover controllare costantemente i tassi di cambio su un'altra schermata. Nel complesso, il lavoro era noioso, soggetto a errori e mi ha quasi fatto rinunciare.
Fu allora che finalmente pensai di usare una delle funzionalità di cui gli appassionati di Excel parlano sempre: Power Query. Lì Molte altre potenti funzionalità offerte da ExcelMa avevo sentito dire che Power Query era lo strumento perfetto per il mio problema specifico. Così, dopo aver guardato alcuni tutorial su YouTube, mi sono subito reso conto di quanto tempo avrei potuto risparmiare iniziando a usare l'editor di Power Query per riordinare tutti i dati disordinati che avevo raccolto da Internet. Con Power Query, ora posso importare facilmente dati da diverse fonti, convertirli in un formato standardizzato e analizzarli in modo efficiente, risparmiando tempo e fatica preziosi nei miei progetti di analisi dei prezzi dei componenti informatici.
Come posso usare Power Query per ripulire i dati non strutturati?
Dopo un po', ho optato per una semplice procedura passo passo nell'editor di Power Query. Ecco esattamente come ho riordinato le mie esportazioni CSV disordinate e le ho trasformate in un foglio di calcolo coerente e ben organizzato.
Per prima cosa, ho importato i miei dati nell'editor di Power Query aprendo una cartella di lavoro vuota, facendo clic su Dati Nella barra multifunzione, seleziona Da testo/CSV.Quindi ho selezionato il mio file CSV e ho fatto clic Trasforma i dati Per aprirlo utilizzando l'editor di Power Query.
Ho iniziato sistemando la colonna delle date. Dato che raccoglievo dati da due fonti con una differenza oraria di 12 ore, avevo bisogno di unificare le date. Si è rivelato piuttosto semplice. Ho definito la colonna Data, fare clic con il pulsante destro del mouse per aprire il menu contestuale e scegliere Cambia tipo > Utilizzo delle impostazioni localiNel menu a comparsa, ho impostato il tipo su Data e identificato Inglese (Stati Uniti) Per garantire una formattazione coerente, Power Query riconosce automaticamente diversi formati, ad esempio MM/GG/AAAA, AAAA/MM/GG e variabili che utilizzano simboli come GG-MM-AA, quindi li unifica tutti in un unico formato di data.
Ora che avevo corretto il formato della data, non mi restava che ripulire la colonna. Laggiù Diversi modi per pulire un foglio di calcolo ExcelMa poiché tutti gli errori erano voci errate generate dal mio scraper, ho semplicemente scelto di utilizzare un filtro. Rimuovi errori Per rimuovere queste voci. Questo passaggio ha rimosso i valori nulli e tutti i dati problematici rimanenti che non erano stati registrati correttamente, lasciandomi date pulite e coerenti in tutti i miei file.
Poi ho risolto il problema del disordine del marchio con una funzione. Sostituisci valoriCome prima, ho selezionato la colonna di destinazione, quindi ho fatto clic con il pulsante destro del mouse per aprire il menu contestuale e ho selezionato Sostituisci valoriNella finestra pop-up, immettere il valore incoerente nel campo. Valore da trovare e il mio valore standard nel campo Sostituisci con il campo.
Ho ripetuto l'operazione altre due volte e alla fine ho convertito tutte le voci "gigabyte" e "GIGABTYE Inc." in un unico "GIGABYTE" coerente per tutti i miei file. Ho fatto la stessa cosa con AMD e ora l'intera colonna "Marca" per le GPU utilizza nomi di marca standard.
Power Query: come mi ha fatto risparmiare ore di lavoro
Uno dei motivi per cui ho evitato Power Query è che pensavo che sarebbe stata un'altra funzionalità complessa e che avrebbe richiesto molto tempo per essere appresa. Ma si è rivelata molto più semplice del previsto. Invece di eseguire infiniti comandi di ricerca e sostituzione, posso usare Power Query per ripulire rapidamente e automaticamente i dati dai miei strumenti di raccolta dati.
Ciò che mi ha sorpreso di più di Power Query è che ogni comando eseguito veniva registrato e poteva essere ripetuto più volte. In pratica, si ottiene uno script di pulizia automatizzato che può trasformare file CSV disordinati in fogli di calcolo puliti e organizzati, perfetto se si lavora su un Crea set di dati personalizzati utilizzando il web scraping, poiché questi strumenti spesso producono dati non puliti.
Per chiunque abbia a che fare con pulizie ricorrenti dei dati, formati incoerenti o più origini dati, Power Query trasforma queste problematiche in un processo semplice e automatizzato. Invece di dedicare ore ogni settimana a correzioni manuali, è sufficiente premere "Aggiorna" e iniziare ad analizzare. È una funzionalità di Excel che avrei voluto adottare molto tempo fa. Una volta sperimentata la potenza di uno script di pulizia automatico e ripetibile, non si torna più indietro. Power Query è uno strumento potente per risparmiare tempo e fatica nell'elaborazione dei dati, offrendo soluzioni avanzate per una pulizia e una trasformazione efficaci.
