Ian v Nashvillu dobi podatke vsak dan s sistemskega prenosa. Stolpec z datumi vsebuje datum + čas. Zaradi tega ima vrtilna tabela več vrstic na dan namesto povzetka ene celice na dan.
Ta članek prikazuje tri načine za rešitev težave.
Spodnja slika prikazuje stolpec Datum z datumom in časom.
Ena od rešitev je, da ga popravite v vrtilni tabeli.
- Povlecite polje Datum v območje Vrstice. Prikaže se z datumom + čas.
- Izberite poljubno celico, ki vsebuje datum in uro.
- Na zavihku Analiza orodij vrtilne tabele izberite Polje skupine.
- V pogovornem oknu Združevanje izberite Dnevi. Prekličite izbor mesecev. Ko kliknete V redu, bo vrtilna tabela prikazala eno vrstico na dan.
Druga dolgočasna rešitev je dodati nov stolpec po prenosu. Kot je prikazano na tej sliki, dobimo s formulo v stolpcu J je =INT(D2).
Datum + ura je celoštevilski dan, ki mu sledi decimalni čas. Z uporabo funkcije INT za pridobitev celoštevilčnega dela datum + čas izolirate datum.
To bo delovalo v redu, zdaj pa morate novi stolpec dodati vsakič, ko prenesete podatke.
Moja najprimernejša rešitev je uporaba Power Query. Power Query deluje samo v izdajah programa Excel v sistemu Windows. Power Query ne morete uporabljati v sistemih Android, IOS ali Mac.
Power Query je vgrajen v Excel 2016. Za Excel 2010 in 2013 lahko Power Query prenesete iz Microsofta.
Najprej s Ctrl + T. prenesene podatke pretvorite v tabelo. Nato na zavihku Podatki izberite Iz tabele / obsega.
V mreži Power Query izberite stolpec Date. Pojdite na zavihek Preoblikovanje. Odprite spustni meni Vrsta datuma in izberite Datum. Če prejmete sporočilo o Replace Step ali New Step, izberite Replace Step.
Na zavihku Domov Power Query izberite Zapri in naloži.
Vaš rezultat bo prikazan na novem listu. Iz tega lista ustvarite vrtilno tabelo.
Ko boste naslednjič dobili nove podatke, sledite tem korakom:
- Prilepite nove podatke v prvotni nabor podatkov.
- Pojdite na list z rezultatom Power Query. Razširite ploščo s poizvedbami, da boste lahko videli Osveži. Kliknite Osveži.
- Osvežite vrtilno tabelo.
Oglejte si video
Video zapis
Naučite se Excel iz podcasta, epizoda 2203: Skrajšajte datum in čas na samo zmenek.
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje Iana na mojem seminarju v Nashville Power Excelu. Poglejte to: Ian vsak dan prenaša prenos iz svojega sistema in ima - v stolpcu Datum - ima datum in čas, to pa zajebava stvari, ker ko Ian ustvari vrtilno tabelo in postavi datume na levi strani, namesto da dobi samo eno vrstico na datum, dobi eno vrstico za vsak datum in čas.
Zdaj bi lahko prišli sem in rekli, Polje skupine in to razvrstili v Dan, kliknite V redu. In veste, to je dodatnih nekaj klikov, za katere mislim, da jih ne moramo storiti. In za ta podcast, naslednjih nekaj podcastov, si bomo ogledali Power Query. Zdaj je tukaj vgrajen v Excel 2016, v razdelku Pridobi in preoblikuj podatke. Če imate leta 2010 ali 2013 v operacijskem sistemu Windows in ne v Macu, lahko Power Query brezplačno prenesete iz Excela. In Power Query želi delati na mizi ali območju.
Torej bom izbral eno celico podatkov, pritisnite Ctrl + T za tabelo, popolno. In nato na zavihku Power Query, Data, From Table / Range, izberem stolpec Date in Transform in rečem, da želim, da to ne sta datum in čas, ampak samo datum. Izbral bom Zamenjaj trenutno, nato Domov, Zapri in naloži, in dobili bomo povsem nov obrazec lista, v katerem so podatki pretvorjeni. Zdaj lahko od tu seveda povzamemo z vrtiščem in bo popolno.
Mislim, da je prednost te metode Power Query v tem, ko Ian dobi več podatkov … Torej, tu imam nove podatke, vzel bom nove podatke, jih kopiral in prišel sem do svojih starih podatkov in prilepite tik spodaj. In opazite, da je konec oznake tabele zdaj v vrstici 474. Ko prilepim, se konec oznake tabele premakne na dno novih podatkov. Vredu? In ta poizvedba je napisana na podlagi tabele. Vredu? Torej, ko miza raste.
Zdaj je ena težava, če Power Query uporabljate prvič, se Power Query začne tako široko - samo na pol - in ne vidite ikone za osvežitev. Torej, takole ga morate povleči sem in dobiti čudovito ikono za osvežitev ter samo klikniti Osveži Trenutno imamo naloženih 473 vrstic, zdaj pa 563 vrstic. Ustvarite svojo vrtilno tabelo in življenje je super.
Zdaj, hej, poglejte, vem, da bi bilo mogoče tukaj dodati nov stolpec, = INT (D2) - - od datuma - kopirajte to, kopirajte in prilepite posebne vrednosti. Ampak potem ste pripravljeni na to vsak dan. Vredu? Torej, čeprav smo v tem videoposnetku reševanja govorili o vsaj treh različnih načinih, je zame pot Power Query in samo možnost, da kliknem Osveži.
Power Query, o tem govorim v svoji novi knjigi MrExcel LIVe, 54 največjih nasvetov za Excel vseh časov.
Zaključek današnje epizode: Ian v Nashvillu vsak dan dobiva podatke iz sistema, stolpec Datum ima nadležno oba, datum in čas - pokvari svoje vrtilne tabele, zato mora … No, tri možne stvari: ena, uporaba funkcija INT, kopiraj, prilepi vrednosti; drugič, pustite to kot datum in čas in nato vrtilno tabelo združite po datumu; ali, boljša rešitev, Power Query, naložene podatke naredi v tabelo s tipkama Ctrl + T, nato pa na zavihku Podatki izberite Iz tabele, izberite stolpec Datum in čas, pojdite na zavihek Preoblikovanje v Power Query, pretvorite na datum, zapri in naloži, iz tega zgradite vrtilno tabelo. Naslednjič, ko dobite podatke, prilepite v izvirno tabelo, pojdite na ploščo Poizvedba, kliknite Osveži in ste pripravljeni.
Hej, če želite prenesti delovni zvezek iz današnjega videoposnetka, obiščite URL v opisu YouTube.
Želim se zahvaliti Ianu, ker se je pojavil na mojem seminarju v Nashvillu, zahvaljujem se vam, da ste se ustavili. Se vidimo naslednjič za še eno oddajo od.
Prenesite datoteko Excel
Za prenos datoteke excel: date-time-to-date.xlsx
Za nadaljnje izboljšanje procesa Power Query:
- Poskrbite, da bo delovni zvezek za prenos vedno shranjen v isti mapi z istim imenom.
- Začnite s praznim delovnim zvezkom. Shrani kot pri imenu, kot je DownloadedFileTransformed.xlsx
- Iz praznega delovnega zvezka, Podatki, Iz datoteke, Iz delovnega zvezka. Kažite na datoteko iz 1. koraka.
- Izberite Clean Data. Pretvorite datum / uro v datum. Zapri in naloži.
- Z desno miškino tipko kliknite ploščo za poizvedbe in izberite Lastnosti. Pri odpiranju datoteke izberite Osveži podatke.
- Vsak dan pojdite v službo. Vzemi si kavo. Prepričajte se, da je v mapi nova datoteka za številko 1.
- Odprite delovni zvezek DownloadFileTransformed.xlsx. Imela bo nove podatke in datumi bodo pravilni.
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Validacija je tvoj prijatelj."
Duane Aubin
