Ian a Nashville riceve dati ogni giorno da un download di sistema. La colonna della data contiene Data + Ora. Ciò fa sì che la tabella pivot abbia più righe al giorno invece di un riepilogo di una cella al giorno.
Questo articolo mostra tre modi per risolvere il problema.
La figura seguente mostra la colonna Data con Data più ora.
Una soluzione è risolverlo nella tabella pivot.
- Trascina il campo Data nell'area Righe. Si presenta con Data + Ora.
- Seleziona una cella che contiene una data e un'ora.
- Nella scheda Analizza Strumenti tabella pivot, scegli Campo di gruppo.
- Nella finestra di dialogo Raggruppamento, seleziona Giorni. Deseleziona mesi. Quando fai clic su OK, la tabella pivot mostrerà una riga al giorno.
Un'altra soluzione noiosa è aggiungere una nuova colonna dopo il download. Come mostrato in questa figura, la formula nella colonna J è =INT(D2).
Una data + ora è un giorno intero seguito da un'ora decimale. Utilizzando la funzione INT per ottenere la parte intera di data + ora, si isola la data.
Funzionerà bene, ma ora devi aggiungere la nuova colonna ogni volta che scarichi i dati.
La mia soluzione preferita è usare Power Query. Power Query funziona solo nelle edizioni Windows di Excel. Non puoi usare Power Query su Android, IOS o Mac.
Power Query è integrato in Excel 2016. Per Excel 2010 e 2013, è possibile scaricare Power Query da Microsoft.
Innanzitutto, converti i dati scaricati in una tabella utilizzando Ctrl + T. Quindi, nella scheda Dati, scegli Da tabella / intervallo.
Nella griglia di Power Query scegliere la colonna Data. Vai alla scheda Trasforma. Apri il menu a discesa Tipo di data e scegli Data. Se ricevi un messaggio su Sostituisci passaggio o Nuovo passaggio, scegli Sostituisci passaggio.
Nella scheda Home di Power Query scegliere Chiudi e carica.
Il tuo risultato apparirà su un nuovo foglio. Crea la tabella pivot da quel foglio.
La prossima volta che ottieni nuovi dati, segui questi passaggi:
- Incolla i nuovi dati sul set di dati originale.
- Vai al foglio con il risultato di Power Query. Allarga il pannello delle query in modo da poter vedere Aggiorna. Fare clic su Aggiorna.
- Aggiorna la tabella pivot.
Guarda un video
Trascrizione del video
Impara Excel da Podcast, episodio 2203: Troncare data e ora a una data.
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi di Ian nel mio seminario Power Excel di Nashville. Guarda questo: Ian riceve un download dal suo sistema ogni giorno, e ha - nella colonna Data - ha data e ora, e questo sta rovinando le cose perché quando Ian crea una tabella pivot e mette insieme le date il lato sinistro, invece di ottenere solo una riga per data, ottiene una riga per ogni data e ora.
Ora, sarebbe possibile venire qui e dire, Campo di gruppo, e raggrupparlo su Giorno, fare clic su OK. E, sai, sono pochi clic in più che non penso dobbiamo fare. Quindi, per questo podcast, i prossimi podcast, daremo un'occhiata a Power Query. Ora è integrato in Excel 2016 qui, in Ottieni e trasforma dati. Se hai il 2010 o il 2013 in esecuzione su Windows, non su un Mac, puoi scaricare Power Query gratuitamente da Excel. E Power Query vuole lavorare su una tabella o un intervallo.
Quindi sceglierò una cella di dati, premere Ctrl + T per Tabella, perfetto. E poi, nella mia scheda Power Query, Dati, Da tabella / intervallo, sceglierò la colonna Data e Trasforma e dirò che non voglio che questa sia una data e un'ora, ma solo una data. Sceglierò Sostituisci corrente, quindi Home, Chiudi e carica, e otterremo un nuovo modulo di foglio che ha convertito i dati. Ora, da qui, ovviamente, possiamo riassumere con un perno e sarà perfetto.
Il vantaggio, credo, di questo metodo, Power Query, è quando Ian ottiene più dati … Quindi qui ho nuovi dati, prenderò questi nuovi dati, copierò i dati e tornerò qui ai miei vecchi dati e incolla appena sotto. E, nota che il marcatore di fine tabella in questo momento si trova nella riga 474. Quando incollo, il marcatore di fine tabella si sposta fino alla fine dei nuovi dati. Tutto a posto? E questa query viene scritta in base alla tabella. Tutto a posto? Quindi, man mano che la tavola cresce.
Ora, una seccatura qui è che, se stai usando Power Query per la prima volta, Power Query inizia così largo - solo metà largo - e non puoi vedere l'icona Aggiorna. Quindi, devi trascinarlo qui in questo modo e ottenere quella fantastica icona di aggiornamento e fare clic su Aggiorna. In questo momento, abbiamo 473 righe caricate e ora 563 righe caricate. Crea la tua tabella pivot e la vita è fantastica.
Ora, ehi, guarda, so che sarebbe stato possibile aggiungere una nuova colonna qui, = INT (D2) - --della data - copiala, copia e incolla i valori speciali. Ma poi sei pronto per farlo ogni singolo giorno. Tutto a posto? Quindi, sebbene ci siano almeno tre modi diversi di cui abbiamo parlato in questo video per risolverlo, per me Power Query e, semplicemente, la possibilità di fare clic su Aggiorna, è la strada da percorrere.
Power Query, ne parlo nel mio nuovo libro, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Conclusione dell'episodio di oggi: Ian a Nashville riceve dati da un sistema ogni giorno, la colonna Data ha fastidiosamente sia Data che Ora - rovina le sue tabelle pivot, quindi deve … Beh, tre cose possibili: Uno, usa la funzione INT, copia, incolla i valori; secondo, lascialo come Data e ora e quindi raggruppa la tabella pivot per Data; oppure, una soluzione migliore, Power Query, trasforma il set di dati scaricato in una tabella con Ctrl + T, quindi nella scheda Dati, scegli Da tabella, seleziona la colonna Data e ora, vai alla scheda Trasforma in Power Query, converti in una data, chiudere e caricare, costruire la tabella pivot da quello. La prossima volta che ottieni dati, incolla nella tabella originale, vai al pannello delle query, fai clic su Aggiorna e sei a posto.
Ehi, per scaricare la cartella di lavoro dal video di oggi, visita l'URL nella descrizione di YouTube.
Voglio ringraziare Ian per essersi presentato al mio seminario a Nashville, voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: date-time-to-date.xlsx
Per migliorare ulteriormente il processo di Power Query:
- Assicurati che la cartella di lavoro del download sia sempre salvata nella stessa cartella con lo stesso nome.
- Inizia da una cartella di lavoro vuota. Salva con nome con un nome come DownloadedFileTransformed.xlsx
- Dalla cartella di lavoro vuota, Dati, Da file, Da cartella di lavoro. Seleziona il file del passaggio 1.
- Scegli Clean Data. Trasforma la data / ora in una data. Chiudi e carica.
- Fare clic con il pulsante destro del mouse sul pannello delle query e scegliere Proprietà. Scegli Aggiorna dati all'apertura del file.
- Ogni giorno, vai a lavorare. Prendi il tuo caffè. Assicurati che un nuovo file per # 1 sia nella cartella.
- Apri la cartella di lavoro DownloadFileTransformed.xlsx. Avrà i nuovi dati e le date saranno corrette.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"La convalida è tuo amico."
Duane Aubin
