Ian din Nashville primește date în fiecare zi dintr-o descărcare a sistemului. Coloana dată conține Data + Ora. Acest lucru face ca tabelul pivot să aibă mai multe rânduri pe zi în loc de un rezumat al unei celule pe zi.
Acest articol prezintă trei moduri de a rezolva problema.
Figura de mai jos arată coloana Data cu Data plus ora.
O soluție este să o remediați în tabelul pivot.
- Trageți câmpul Data în zona Rânduri. Se afișează cu Data + Ora.
- Selectați orice celulă care conține o dată și o oră.
- În fila Analize Instrumente tabel pivot, alegeți Câmp grup.
- În dialogul de grupare, selectați Zile. Deselectați lunile. Când faceți clic pe OK, tabelul pivot va afișa un rând pe zi.
O altă soluție obositoare este să adăugați o nouă coloană după descărcare. După cum se arată în această figură, formula din coloana J este =INT(D2).
O dată + oră este o zi întreagă urmată de o oră zecimală. Folosind funcția INT pentru a obține porțiunea întreagă de dată + oră, izolați data.
Acest lucru va funcționa bine, dar acum trebuie să adăugați noua coloană de fiecare dată când descărcați datele.
Soluția mea preferată este să folosesc Power Query. Power Query funcționează numai în edițiile Windows ale Excel. Nu puteți utiliza Power Query pe Android, IOS sau Mac.
Power Query este integrat în Excel 2016. Pentru Excel 2010 și 2013, puteți descărca Power Query de la Microsoft.
Mai întâi, convertiți datele descărcate într-un tabel folosind Ctrl + T. Apoi, în fila Date, alegeți Din tabel / interval.
În grila Power Query, alegeți coloana Data. Accesați fila Transformare. Deschideți meniul derulant Tip dată și alegeți Data. Dacă primiți un mesaj despre Pasul de înlocuire sau Pasul nou, alegeți Înlocuiește pasul.
În fila Acasă din Power Query, alegeți Închidere și încărcare.
Rezultatul dvs. va apărea pe o foaie nouă. Creați tabelul pivot din foaia respectivă.
Data viitoare când obțineți date noi, urmați acești pași:
- Lipiți noile date peste setul de date original.
- Accesați foaia cu rezultatul Power Query. Lărgiți panoul Interogare pentru a putea vedea Reîmprospătare. Faceți clic pe Reîmprospătare.
- Reîmprospătați tabelul pivot.
Urmăriți videoclipul
Transcriere video
Aflați Excel din Podcast, Episodul 2203: Trunchiați data și ora la doar o dată.
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi de la Ian la seminarul meu Nashville Power Excel. Uită-te la acest lucru: Ian primește o descărcare din sistemul său în fiecare zi și are - în coloana Data - are dată și oră, iar asta înșelă lucrurile, pentru că atunci când Ian creează un tabel pivot și pune datele de-a lungul partea stângă, în loc să obțină doar un rând pe dată, primește un rând pentru fiecare dată și oră.
Acum, ar fi posibil să veniți aici și să spuneți, Câmp de grup și grupați acest lucru în Ziua, faceți clic pe OK. Și, știți, sunt câteva clicuri în plus pe care nu cred că trebuie să le facem. Și, așadar, pentru acest Podcast, următorii Podcast-uri, vom arunca o privire la Power Query. Acum, este încorporat în Excel 2016 aici, sub Obțineți și transformați date. Dacă aveți 2010 sau 2013 în Windows, nu pe un Mac, puteți descărca gratuit Power Query din Excel. Și Power Query dorește să lucreze pe o masă sau o gamă.
Deci, voi alege o celulă de date, apăsați Ctrl + T pentru Tabel, perfect. Și apoi, în fila Power Query, Date, Din tabel / interval, voi alege coloana Data și Transform și voi spune că vreau ca aceasta să nu fie o dată și o oră, ci doar o dată. Voi alege Înlocuiți curentul, apoi Acasă, Închideți și încărcați și vom primi un formular de foaie nou, care va converti datele. Acum, de aici, desigur, putem rezuma cu un pivot și va fi perfect.
Avantajul, cred, al acestei metode, Power Query, este atunci când Ian primește mai multe date … Deci, aici am date noi, voi lua aceste date noi, voi copia datele și voi veni aici la vechile mele date și lipiți chiar mai jos. Și, observați că sfârșitul marcatorului tabelului se află acum în rândul 474. Când lipesc, capătul marcatorului tabelului se deplasează în josul noilor date. Bine? Și această interogare este scrisă pe baza tabelului. Bine? Deci, pe măsură ce masa crește.
Acum, o problemă aici este că, dacă utilizați Power Query pentru prima dată, Power Query începe atât de larg - doar pe jumătate - și nu puteți vedea pictograma Reîmprospătare. Deci, trebuie să îl trageți aici astfel și să obțineți acea pictogramă Reîmprospătare și să faceți clic pe Reîmprospătare. Chiar acum, avem 473 de rânduri încărcate, iar acum 563 de rânduri încărcate. Creați-vă tabelul pivot, iar viața este grozavă.
Acum, hei, uite, știu că ar fi fost posibil să adăugăm o nouă coloană aici, = INT (D2) - - a datei - copiați în jos, copiați și lipiți valorile speciale. Dar atunci ești la curent pentru a face asta în fiecare zi. Bine? Deci, deși există cel puțin trei moduri diferite despre care am vorbit în acest videoclip de rezolvare, pentru mine, Power Query și, doar, posibilitatea de a da clic pe Reîmprospătare, este calea de urmat.
Power Query, vorbesc despre asta în noua mea carte, MrExcel LIVe, Cele mai mari 54 de sfaturi Excel din toate timpurile.
Încheierea episodului de astăzi: Ian din Nashville primește date dintr-un sistem în fiecare zi, coloana Data are atât Data cât și Ora - îi înșurubează tabelele pivot, așa că trebuie să … Ei bine, trei lucruri posibile: Unul, utiliza funcția INT, copie, lipire valori; în al doilea rând, lăsați-l ca dată și oră și apoi grupați tabelul pivot după dată; sau, o soluție mai bună, Power Query, transformă setul de date descărcate într-un tabel cu Ctrl + T, apoi în fila Date, alegeți Din tabel, selectați coloana Dată și oră, accesați fila Transformare din Power Query, convertiți la o dată, închidere și încărcare, construiți tabelul pivot din acesta. Data viitoare când obțineți date, lipiți în tabelul original, accesați panoul Interogare, faceți clic pe Reîmprospătare și sunteți bine să mergeți.
Bună, pentru a descărca registrul de lucru din videoclipul de astăzi, accesați adresa URL din descrierea YouTube.
Vreau să-i mulțumesc lui Ian că a apărut la seminarul meu din Nashville, vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: data-timp-până-la-data.xlsx
Pentru a îmbunătăți în continuare procesul de interogare Power:
- Asigurați-vă că registrul de lucru pentru descărcare este întotdeauna salvat în același folder cu același nume.
- Începeți de la un registru de lucru gol. Salvați ca cu un nume precum DownloadedFileTransformed.xlsx
- Din registrul de lucru gol, Date, Din fișier, Din registrul de lucru. Arată spre fișier de la pasul 1.
- Alegeți date curate. Transformați data / ora la o dată. Închideți și încărcați.
- Faceți clic dreapta pe Panoul de interogare și alegeți Proprietăți. Alegeți Reîmprospătare date când deschideți fișierul.
- În fiecare zi, du-te la muncă. Ia-ți cafeaua. Asigurați-vă că un dosar nou pentru # 1 este în dosar.
- Deschideți registrul de lucru DownloadFileTransformed.xlsx. Va avea noile date și datele vor fi corecte.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Validarea este prietenul tău”.
Duane Aubin
