Nashville'is asuv Ian saab iga päev andmeid süsteemi allalaadimisest. Kuupäeva veerus on kuupäev + kellaaeg. See muudab pöördtabeli ühe lahtri kokkuvõtte asemel mitu rida päevas.
See artikkel näitab probleemi lahendamiseks kolme võimalust.
Alloleval joonisel on veerg Kuupäev koos kuupäeva ja kellaajaga.
Üks lahendus on selle fikseerimine pöördtabelis.
- Lohistage väli Kuupäev alale Read. See kuvatakse kuupäeva + kellaajaga.
- Valige mis tahes lahter, mis sisaldab kuupäeva ja kellaaega.
- Vahekaardil Pivot Table Tools Analysis valige Rühma väli.
- Valige dialoogis Grupeerimine päevad. Tühista valik Kuud. Kui klõpsate nuppu OK, kuvatakse pöördtabelis üks rida päevas.
Teine tüütu lahendus on pärast allalaadimist lisada uus veerg. Nagu sellel joonisel näidatud, on veerus J olev valem =INT(D2).
Kuupäev + kellaaeg on täisarv, millele järgneb kümnendkoht. Kuupäeva + kellaaja täisarvu saamiseks funktsiooni INT abil eraldate kuupäeva.
See töötab hästi, kuid nüüd peate iga kord, kui andmed alla laadite, lisama uue veeru.
Minu eelistatud lahendus on Power Query kasutamine. Power Query töötab ainult Exceli Windowsi väljaannetes. Power Queryt ei saa kasutada Androidis, IOS-is ega Macis.
Power Query on sisse ehitatud rakendusse Excel 2016. Exceli 2010 ja 2013 jaoks saate Power Query Microsofti alla laadida.
Kõigepealt teisendage allalaaditud andmed tabeliks, kasutades klahvikombinatsiooni Ctrl + T. Seejärel valige vahekaardil Andmed tabelist / vahemikust.
Valige võrgus Power Query veerg Kuupäev. Minge vahekaardile Muuda. Avage rippmenüü Kuupäeva tüüp ja valige Kuupäev. Kui saate teate asenda samm või uus samm, valige käsk Asenda samm.
Valige Power Query vahekaardil Avaleht Sule ja laadige.
Teie tulemus ilmub uuel lehel. Looge sellest lehest pöördtabel.
Järgmine kord, kui saate uusi andmeid, toimige järgmiselt.
- Kleepige uued andmed algsele andmekogumile.
- Minge lehele Power Query tulemusega. Laiendage paneeli Päring, et näeksite värskendust. Klõpsake nuppu Värskenda.
- Värskendage pöördtabelit.
Vaata videot
Video ärakiri
Õppige Exceli rakendust Podcast, osa 2203: lühendage kuupäeva ja kellaaega lihtsalt kuupäevaks.
Tere, tere tulemast tagasi netisaate juurde, ma olen Bill Jelen. Tänane küsimus Ianilt minu Nashville Power Exceli seminaril. Vaadake seda: Ian laadib oma süsteemist iga päev alla ja sellel on - veerus Kuupäev - kuupäev ja kellaaeg ning see keerutab asju, sest kui Ian loob Pivot-tabeli ja paneb kuupäevad kaasa vasakul küljel, selle asemel, et saada ainult üks rida kuupäeva kohta, saab ta ühe rea iga kuupäeva ja kellaaja kohta.
Nüüd oleks võimalik tulla siia ja öelda: Grupi väli ja grupeerida see päevaks, klõpsake nuppu OK. Ja tead, see on veel paar klikki, mida ma arvan, et me ei pea tegema. Ja nii, selle Podcasti, paari järgmise Podcasti jaoks vaatame Power Query. Nüüd on see sisseehitatud Excel 2016-sse jaotises Andmete hankimine ja teisendamine. Kui teil töötab 2010 või 2013 Windowsis, mitte Macis, saate Power Query Excelist tasuta alla laadida. Ja Power Query soovib töötada tabelis või vahemikus.
Nii et valin ühe andmerakke, vajutage tabeli Ctrl + T, täiuslik. Ja siis vahekaardil Power Query, Data, From Table / Range valin veeru Date ja Transform ning ütlen, et ma ei taha, et see oleks kuupäev ja kellaaeg, vaid lihtsalt kuupäev. Ma valin Asenda praegune ja seejärel Kodu, Sule ja laadige ning saame uhiuue lehevormi, kus andmed on teisendatud. Nüüd saame muidugi siit pöördelt kokku võtta ja see saab täiuslik.
Ma arvan, et selle meetodi, Power Query, eeliseks on see, kui Ian saab rohkem andmeid … Nii et siin on mul uued andmed, võtan need uued andmed, kopeerin need ja tulen siia lihtsalt oma vanade andmete juurde ja kleepige veidi alla. Ja pange tähele, et tabeli markeri ots on praegu real 474. Kui kleebin, liigub tabeli markeri ots uute andmete põhja. Hästi? Ja see päring on kirjutatud tabeli põhjal. Hästi? Niisiis, kui laud kasvab.
Nüüd on siin üks probleem: kui kasutate Power Query esimest korda, algab Power Query nii laia - ainult poole laiusega - ja te ei näe ikooni Värskenda. Nii et peate selle siin niimoodi välja lohistama ja hankima selle suurepärase värskendamise ikooni ning klõpsama lihtsalt Värskenda. Praegu on meil 473 rida laaditud ja nüüd 563 rida. Looge oma pöördtabel ja elu on suurepärane.
Nüüd, hei, vaata, ma tean, et siia oleks olnud võimalik lisada uus veerg = INT (D2) - - kuupäevast - kopeerige see alla, kopeerige ja kleepige eriväärtused. Kuid siis olete iga päev selle nimel konksul. Hästi? Nii et kuigi selle video lahendamiseks on räägitud vähemalt kolmel erineval viisil, on minu jaoks Power Query ja lihtsalt võimalus Värskenda klõpsamine.
Power Query, räägin sellest oma uues raamatus MrExcel LIVe, The 54 Greatest Exceli näpunäidet läbi aegade.
Tänase osa kokkuvõte: Nashville'is asuv Ian saab iga päev süsteemist andmeid, veerus Kuupäev on tüütult mõlemad kuupäev ja kellaaeg - see kruvib tema pöördtabeleid, nii et ta peab… Noh, kolm võimalikku asja: üks, kasutage funktsioon INT, väärtuste kopeerimine, kleepimine; teiseks jätke see lihtsalt kuupäeva ja kellaajaks ning rühmitage pöördtabel kuupäeva järgi; või parem lahendus, Power Query, teeb allalaaditud andmekomplekti tabeliks, kasutades klahvikombinatsiooni Ctrl + T ja seejärel vahekaardil Andmed valige Tabelist, valige veerg Kuupäev ja kellaaeg, minge Power Query vahekaardile Muuda, teisendage see kuupäevaks, sulgemiseks ja laadimiseks, ehitage sellest pöördtabel. Järgmine kord, kui saate andmeid, kleepige algsesse tabelisse, minge paneelile Päring, klõpsake käsku Värskenda ja kõik on korras.
Hei, töövihiku tänasest videost allalaadimiseks külastage YouTube'i kirjelduses olevat URL-i.
Ma tahan tänada Ianit selle eest, et ta ilmus minu seminarile Nashville'is. Ma tahan tänada teid peatumast. Järgmisel korral näeme järgmise Netcast'i jaoks.
Laadige alla Exceli fail
Exceli faili allalaadimiseks: date-time-to-date.xlsx
Power Query protsessi edasiseks täiustamiseks toimige järgmiselt.
- Veenduge, et allalaaditav töövihik oleks alati salvestatud sama nimega kausta.
- Alustage tühjast töövihikust. Salvesta nimega nagu DownloadedFileTransformed.xlsx
- Tühjalt töövihikult andmed, failist, töövihikust. Osutage 1. toiminguga failile.
- Valige Puhasta andmed. Teisendage kuupäev / kellaaeg kuupäevaks. Sule ja laadige.
- Paremklõpsake päringupaneelil ja valige Atribuudid. Valige faili avamisel Värskenda andmeid.
- Iga päev minge tööle. Tooge oma kohv. Veenduge, et kaustas oleks # 1 uus fail.
- Avage oma töövihik DownloadFileTransformed.xlsx. Sellel on uued andmed ja kuupäevad on õiged.
Exceli päeva mõte
Olen küsinud oma Exceli meistri sõpradelt Exceli kohta nõu. Tänane mõte mõelda:
"Valideerimine on teie sõber."
Duane Aubin
