Domanda di oggi: Marcia ha bisogno di ottenere un elenco di tutti i file PDF delle fatture fiscali da una cartella in un foglio di calcolo Excel. Questa operazione è facile se utilizzi Excel 2016 su un PC Windows utilizzando i nuovi strumenti Ottieni e trasforma dati.
Se si dispone di Excel 2010 per Windows o Excel 2013 per Windows, sarà necessario scaricare il componente aggiuntivo Power Query gratuito di Microsoft. Vai al tuo motore di ricerca preferito e digita "Download Power Query" per trovare il collegamento corrente. (Microsoft ama cambiare gli URL ogni trimestre e il mio fantastico web guy odia quando i nostri collegamenti non sono aggiornati, quindi non cercherò nemmeno di inserire un collegamento qui.)
Il video qui sotto ti mostrerà i passaggi completi, ma ecco la panoramica:
- Inizia da un foglio di lavoro vuoto
- Dati, Ottieni dati, Da file, Da cartella
- Vai alla cartella
- Fare clic su Modifica invece di Carica
- Apri il menu a discesa del filtro sul tipo di file e rimuovi tutto ciò che non è un PDF
- Apri il filtro sulla cartella e rimuovi tutte le sottocartelle spazzatura
- Mantieni solo il nome del file e la cartella: fai clic con il pulsante destro del mouse su ciascuna intestazione di colonna e scegli Rimuovi
- Trascina l'intestazione della cartella a sinistra dell'intestazione del file. Ciò consente all'unione di funzionare.
- Seleziona entrambe le colonne. Fare clic su un'intestazione. Maiusc + clic sull'altra intestazione.
- Scegli Aggiungi colonna, Unisci colonne, digita un nuovo nome per la colonna. Fare clic su OK.
- Fare clic con il pulsante destro del mouse sull'intestazione della nuova colonna e Rimuovi altre colonne
- Home, Chiudi e carica
- La parte straordinaria … puoi aggiornare la query in un secondo momento. Fare clic sull'icona Aggiorna nel pannello Query e connessioni.
Sebbene Power Query sia incredibilmente potente, questa è una delle mie attività preferite. Spesso voglio eseguire una macro VBA su ogni file in una cartella. Ottenere un elenco di tutti i PDF in una cartella è un buon punto di partenza.
Guarda un video
Trascrizione del video
Impara Excel dal podcast, episodio 2181 - Elenca i file delle cartelle in Excel!
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi, qualcuno ha un elenco di file PDF di fatture fiscali in una cartella e deve ottenere l'elenco di tutti quei nomi di file in Excel. Va bene, e un modo per farlo è digitarli tutti o copiarli e incollarli da Windows Explorer, ma c'è un ottimo strumento che può risolverlo. E la mia prima domanda è stata "Bene, quale versione di Excel possiedi?" Perché se ti capita di avere Excel 2016, avranno questa nuova straordinaria funzionalità chiamata "Ottieni e trasforma dati"! Ora, in Office 365, è sul lato sinistro, penso che nella versione originale di Excel 2016 fosse nel terzo gruppo, va bene, quindi cerca Recupera e trasforma. Se sei in Excel 2010 o Excel 2013 per Windows, puoi scaricare Power Query e avrai la tua scheda con questa roba esatta.
Ora diamo una rapida occhiata a questa cartella, va bene, ho appena creato una cartella falsa qui con alcuni dati falsi. Vedrai che ci sono file Excel in questa cartella e PDF, voglio solo i PDF e ci sono anche alcune sottocartelle, non voglio questi PDF, voglio solo i PDF nella cartella principale. Quindi C: Budgets, lo copierò, quindi tornerò qui in Excel e diremo che vogliamo ottenere dati, da un file, da un'intera cartella, in questo modo, e poi digita il percorso della cartella o usa il pulsante Sfoglia, uno dei due. E quando ottieni questa prima schermata, vuoi decisamente modificare, e ora siamo nell'editor di Power Query.
Va bene, quindi il mio obiettivo qui, non ho bisogno del contenuto, quindi farò clic con il pulsante destro del mouse e dirò Rimuovi quella colonna. C'è il mio elenco di file, voglio solo file PDF, quindi se c'è qualcosa che non è un PDF, voglio solo PDF, fai clic su OK, posso vedere che sono solo i file PDF. Ah, e poi guarda qui, vedi, ora stanno estraendo cose solo dalla cartella originale e dalla cartella Garbage, quindi apro questa e deseleziono tutto ciò che non è la cartella originale. Va bene, quindi ora ho una bella piccola lista, e questa lista è, sai cosa, 9 record, ma nella vita reale scommetto che probabilmente ne hanno, sai, dozzine o centinaia. Va bene, non ho bisogno di altre cose ora, quindi posso fare clic con il tasto destro e rimuovere quelle colonne.
Va bene, ora ciò di cui ho davvero bisogno qui è che ho bisogno del percorso della cartella e del nome del file insieme. Va bene, quindi prenderò FolderPath e lo trascinerò a sinistra e lo rilascerò lì, quindi il passaggio magico qui: In Excel normale dovremmo fare la concatenazione per questo, ma quello che farò è, io sto per unire le colonne. Quindi vado su Aggiungi colonna e scegli Unisci colonne, il separatore sarà Nessuno, la nuova colonna si chiamerà FileName e fai clic su OK, va bene, quindi abbiamo il nome della cartella, la barra e il nome del file , come quello. Ora, in realtà è l'unica cosa di cui abbiamo bisogno, quindi farò clic con il pulsante destro del mouse e dirò Rimuovi le altre colonne, quindi alla fine Home, Chiudi e carica, e avremo un foglio nuovo di zecca con i nostri dati. Va bene ora, viene fornito come tabella, quindi copierò semplicemente questo, Ctrl + C,e poi vieni qui dove volevo davvero i dati qui, e Incolla valori speciali, fai clic su OK. Ora non è più una tabella, sono solo i miei dati puri, così, e ora, ecco la cosa davvero bella di questo.
Quindi l'abbiamo impostato una volta, e wow, ci sono voluti meno di 3 minuti per configurarlo, ma torniamo alla cartella dei budget e spostiamo alcune cose. Prendiamo uno di questi record Garbage e lo copieremo nella cartella principale, Ctrl + V, va bene quindi ora ci sono più cose qui, ci sono 10 file PDF invece di 9. Se vengo qui dove si trova la query, e sul lato destro dello schermo, in Query & Connections, potresti dover allargarlo, ho già allargato il mio, vedrai i nostri budget con 9 righe caricate. Farò clic sulla piccola icona Aggiorna qui e molto rapidamente Budget ora ha 10 righe caricate. Quindi sta raccogliendo i nuovi record, lo imposti una volta e poi sarai in grado di aggiornare per ottenere i nuovi dati.
Bene, questo è il punto del podcast in cui di solito ti chiedo di acquistare il mio libro, ma invece oggi ti chiederò di acquistare questo libro "M is for (DATA) MONKEY" di Ken Puls e Miguel Escobar. Un libro INCREDIBILE che ti insegnerà tutto sull'uso di Power Query o su Ottieni e trasforma dati, tutto ciò che ho imparato su Power Query l'ho imparato da questo libro.
OK, concludere da questo episodio: il nostro obiettivo è come importare un elenco di nomi di file in Excel, se hai Excel 2016, puoi utilizzare il nuovo Ottieni e trasforma dati. Se non disponi del 2016, ma disponi di una versione reale di Excel in esecuzione su Windows, puoi scaricare il componente aggiuntivo Power Query gratuito per Excel 2010 o Excel 2013. Non funzionerà sul tuo telefono Android o il tuo iPad, o il tuo iPhone, o Surface RT, o il tuo Mac, giusto, è solo per le versioni Windows di Excel. Quindi inizieremo da un foglio di lavoro vuoto, Dati, Ottieni dati, Da file, Da cartella, inserisci il nome della cartella o Sfoglia, assicurati di fare clic su Modifica invece di Carica. E poi su Filtro, filtra sul tipo di file per eliminare tutto ciò che non è un PDF, filtra sul nome della cartella per eliminare tutte le sottocartelle spazzatura. Conserva solo il nome del file e la cartella,quindi fai clic con il pulsante destro del mouse su quegli altri e pronuncia Rimuovi colonna, quindi trascina l'intestazione della cartella a sinistra del file, che consente il funzionamento dell'unione. Seleziona entrambe le colonne, quindi nella scheda Aggiungi colonna scegli Unisci colonne, digita un nuovo nome, fai clic su OK e fai clic con il pulsante destro del mouse su quella nuova colonna e Rimuovi le altre colonne, Home, Chiudi e Carica, e ti darà il tuo elenco. La parte straordinaria è che puoi aggiornare la query in un secondo momento utilizzando questa icona Aggiorna in Query e connessioni.puoi aggiornare la query in un secondo momento utilizzando questa icona Aggiorna in Query e connessioni.puoi aggiornare la query in un secondo momento utilizzando questa icona Aggiorna in Query e connessioni.
Ehi, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da!
Per conoscere Power Query, consiglio questo libro di Ken Puls e Miguel Escobar.
M sta per (DATA) MONKEY »
