Contenuti
Uno dei video più visti sul mio canale YouTube è un video su Flash Fill in Microsoft Excel. L'essenza di questo strumento è che se hai bisogno di trasformare in qualche modo i tuoi dati di origine, devi solo iniziare a digitare il risultato che vuoi ottenere nella colonna adiacente. Dopo diverse celle digitate manualmente (di solito 2-3 sono sufficienti), Excel "capirà" la logica delle trasformazioni di cui hai bisogno e continuerà automaticamente ciò che hai digitato, completando per te tutto il lavoro monotono:
La quintessenza dell'efficienza. Il pulsante magico "fai bene" che tutti amiamo così tanto, giusto?
In effetti, esiste un analogo di tale strumento in Power Query: viene chiamato Colonna da esempi (colonna da esempi). In effetti, questa è una piccola intelligenza artificiale integrata in Power Query che può imparare rapidamente dai tuoi dati e quindi trasformarli. Diamo un'occhiata più da vicino alle sue capacità in diversi scenari pratici per capire dove può esserci utile in compiti reali.
Esempio 1. Incollare/tagliare testo
Diciamo che abbiamo una tabella così "intelligente" in Excel con i dati sui dipendenti:
Caricalo in Power Query nel modo standard, con il pulsante Dalla tabella/intervallo linguetta Dati (Dati — Da tabella/intervallo).
Supponiamo di dover aggiungere una colonna con cognomi e iniziali per ogni dipendente (Ivanov SV per il primo dipendente, ecc.). Per risolvere questo problema, puoi utilizzare uno dei due metodi:
- fare clic con il tasto destro del mouse sull'intestazione della colonna con i dati di origine e selezionare il comando Aggiungi una colonna dagli esempi (Aggiungi colonna dagli esempi);
- selezionare una o più colonne con i dati e nella scheda Aggiunta di una colonna scegli una squadra Colonna da esempi. Qui, nell'elenco a discesa, è possibile specificare se è necessario analizzare tutte o solo le colonne selezionate.
Quindi tutto è semplice: nella colonna che appare a destra, iniziamo a inserire esempi dei risultati desiderati e l'intelligenza artificiale integrata in Power Query cerca di capire la nostra logica di trasformazione e di continuare da sola:
A proposito, puoi inserire le opzioni corrette in qualsiasi cella di questa colonna, cioè non necessariamente dall'alto verso il basso e in una riga. Inoltre, puoi facilmente aggiungere o rimuovere colonne dall'analisi in un secondo momento utilizzando le caselle di controllo nella barra del titolo.
Presta attenzione alla formula nella parte superiore della finestra: questo è ciò che Smart Power Query crea per ottenere i risultati di cui abbiamo bisogno. Questa, a proposito, è la differenza fondamentale tra questo strumento e Riempimento istantaneo in Excel. Il riempimento istantaneo funziona come una “scatola nera”: non ci mostrano la logica delle trasformazioni, ma danno semplicemente risultati già pronti e li diamo per scontati. Qui tutto è trasparente e puoi sempre capire in modo assolutamente chiaro cosa sta succedendo esattamente con i dati.
Se vedi che Power Query "ha colto l'idea", puoi tranquillamente premere il pulsante OK o scorciatoia da tastiera Ctrl+entrare – verrà creata una colonna personalizzata con una formula inventata da Power Query. A proposito, può essere successivamente facilmente modificata come una normale colonna creata manualmente (con il comando Aggiunta di una colonna – Colonna personalizzata) facendo clic sull'icona a forma di ingranaggio a destra del nome del passaggio:
Esempio 2: Caso come nelle frasi
Se fai clic con il pulsante destro del mouse sull'intestazione della colonna con testo e seleziona il comando Trasformazione (Trasformare), quindi puoi vedere tre comandi responsabili della modifica del registro:
Comodo e interessante, ma in questo elenco, ad esempio, mi è sempre mancata un'altra opzione: caso come nelle frasi, quando la maiuscola (maiuscola) diventa non la prima lettera in ogni parola, ma solo la prima lettera nella cella, e il resto del testo quando Questo viene visualizzato in lettere minuscole (piccole).
Questa funzionalità mancante è facile da implementare con l'intelligenza artificiale Colonne da esempi – inserisci solo un paio di opzioni affinché Power Query continui con lo stesso spirito:
Come formula qui, Power Query usa una serie di funzioni Testo.Superiore и Testo.Inferiore, convertendo rispettivamente il testo in maiuscolo e minuscolo e le funzioni Testo.Inizio и Testo.Mid – analoghi delle funzioni di Excel LEFT e PSTR, in grado di estrarre una sottostringa dal testo da sinistra e dal centro.
Esempio 3. Permutazione di parole
A volte, durante l'elaborazione dei dati ricevuti, diventa necessario riordinare le parole nelle celle in una determinata sequenza. Ovviamente puoi dividere la colonna in colonne di parole separate dal separatore e poi incollarla di nuovo nell'ordine specificato (non dimenticare di aggiungere spazi), ma con l'aiuto dello strumento Colonna da esempi tutto sarà molto più semplice:
Esempio 4: solo numeri
Un altro compito molto importante è estrarre solo numeri (numeri) dal contenuto della cella. Come prima, dopo aver caricato i dati in Power Query, vai alla scheda Aggiunta di una colonna: colonna da esempi e riempi manualmente un paio di celle in modo che il programma capisca esattamente cosa vogliamo ottenere:
Bingo!
Ancora una volta, vale la pena guardare la parte superiore della finestra per assicurarsi che Query abbia generato correttamente la formula: in questo caso contiene una funzione Testo. Selezionare, che, come puoi immaginare, estrae i caratteri dati dal testo di origine in base all'elenco. Successivamente, questo elenco, ovviamente, può essere facilmente modificato nella barra della formula, se necessario.
Esempio 5: solo testo
Analogamente all'esempio precedente, puoi estrarre e viceversa: solo il testo, eliminando tutti i numeri, i segni di punteggiatura, ecc.
In questo caso, viene utilizzata una funzione che ha già un significato opposto: Text.Remove, che rimuove i caratteri dalla stringa originale in base a un determinato elenco.
Esempio 6: estrazione di dati da un porridge alfanumerico
Power Query può aiutare anche nei casi più difficili, quando è necessario estrarre informazioni utili dal porridge alfanumerico in una cella, ad esempio ottenere il numero di conto dalla descrizione della causale di pagamento su un estratto conto:
Tieni presente che la formula di conversione generata da Power Query può essere piuttosto complessa:
Per facilità di lettura e comprensione, può essere convertito in una forma molto più sana utilizzando un servizio online gratuito. Formattatore di query di alimentazione:
Cosa molto utile – rispetto per i creatori!
Esempio 7: Conversione di date
Strumento Colonna da esempi può essere applicato anche alle colonne date o datetime. Quando inserisci le prime cifre di una data, Power Query visualizzerà utilmente un elenco di tutte le possibili opzioni di conversione:
Quindi puoi convertire facilmente la data originale in qualsiasi formato esotico, come "anno-mese-giorno":
Esempio 8: Categorizzazione
Se usiamo lo strumento Colonna da esempi in una colonna con dati numerici, funziona in modo diverso. Supponiamo di avere i risultati dei test dei dipendenti caricati in Power Query (punteggi condizionali nell'intervallo 0-100) e di utilizzare la seguente gradazione condizionale:
- Maestri: coloro che hanno segnato più di 90
- Esperti: punteggi da 70 a 90
- Utenti – da 30 a 70
- Principianti: coloro che hanno segnato meno di 30
Se aggiungiamo una colonna dagli esempi all'elenco e iniziamo a disporre queste gradazioni manualmente, molto presto Power Query riprenderà la nostra idea e aggiungerà una colonna con una formula, in cui gli operatori sono annidati l'uno nell'altro if verrà implementata una logica, molto simile a quella di cui abbiamo bisogno:
Anche in questo caso, non puoi spingere la situazione fino in fondo, ma fare clic su OK e poi correggi i valori di soglia già nella formula – è più veloce in questo modo:
Conclusioni
Sicuramente uno strumento Colonna da esempi non è una “pillola magica” e, prima o poi, ci saranno nei dati situazioni non standard o casi particolarmente trascurati di “fattoria collettiva”, quando Power Query fallirà e non sarà in grado di elaborare ciò che vogliamo correttamente per noi. Tuttavia, come strumento ausiliario, è molto buono. Inoltre, studiando le formule da lui generate, potrai ampliare la tua conoscenza delle funzioni del linguaggio M, che in futuro torneranno sempre utili.
- Analisi del testo con espressioni regolari (RegExp) in Power Query
- Ricerca di testo sfocato in Power Query
- Flash Compila Microsoft Excel