Īans Našvilā katru dienu iegūst datus no sistēmas lejupielādes. Datuma kolonnā ir Datums + Laiks. Tādējādi rakurstabulai ir vairākas rindas dienā, nevis vienas šūnas kopsavilkums dienā.
Šajā rakstā parādīti trīs problēmas risināšanas veidi.
Zemāk redzamais attēls parāda kolonnu Datums ar Datums plus laiks.
Viens no risinājumiem ir to salabot pagrieziena tabulā.
- Velciet lauka Datums apgabalā Rindas. Tas parādās ar datumu + laiku.
- Atlasiet jebkuru šūnu, kurā ir datums un laiks.
- Cilnē Rakurstabulas rīku analīze izvēlieties Grupas lauks.
- Dialoglodziņā Grupēšana atlasiet Dienas. Noņemt atlasi Mēneši. Noklikšķinot uz Labi, rakurstabulā tiks parādīta viena rinda dienā.
Vēl viens garlaicīgs risinājums ir pievienot jaunu kolonnu pēc lejupielādes. Kā parādīts šajā attēlā, formula kolonnā J ir =INT(D2).
Datums + laiks ir vesels skaitlis, kam seko decimālais laiks. Izmantojot INT funkciju, lai iegūtu datuma + laika veselu skaitli, jūs izolējat datumu.
Tas darbosies lieliski, taču tagad jums ir jāpievieno jaunā kolonna katru reizi, kad lejupielādējat datus.
Mans vēlamais risinājums ir izmantot Power Query. Power Query darbojas tikai Windows Excel izdevumos. Power Query nevar izmantot operētājsistēmā Android, IOS vai Mac.
Power Query ir iebūvēts programmā Excel 2016. Programmām Excel 2010 un 2013 varat lejupielādēt Power Query no Microsoft.
Vispirms konvertējiet lejupielādētos datus tabulā, izmantojot Ctrl + T. Pēc tam cilnē Dati izvēlieties No tabulas / diapazona.
Power Query režģī izvēlieties kolonnu Datums. Dodieties uz cilni Pārveidot. Atveriet nolaižamo izvēlni Datuma tips un izvēlieties Datums. Ja saņemat ziņojumu par Replace Step vai New Step, izvēlieties Replace Step.
Power Query cilnē Sākums izvēlieties Aizvērt un ielādēt.
Jūsu rezultāts tiks parādīts jaunā lapā. Izveidojiet rakurstabulu no šīs lapas.
Nākamreiz, kad iegūsit jaunus datus, rīkojieties šādi:
- Ielīmējiet jaunos datus virs sākotnējās datu kopas.
- Pārejiet uz lapu ar Power Query rezultātu. Paplašiniet vaicājuma paneli, lai jūs varētu redzēt Atsvaidzināt. Noklikšķiniet uz Atsvaidzināt.
- Atsvaidziniet rakurstabulu.
Skatīties video
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2203. sērija: Saīsiniet datumu un laiku līdz datumam.
Sveiki, laipni aicināti atgriezties netcast ēterā, es esmu Bils Jelens. Šodienas Jana jautājums manā Nešvilas Power Excel seminārā. Paskaties uz to: Īans katru dienu saņem lejupielādi no savas sistēmas, un kolonnā Datums ir - datums un laiks, un tas ir viss, kas tiek izjaukts, jo, kad Īans izveido rakurstabulu un ievieto datumus kreisajā pusē tā vietā, lai iegūtu tikai vienu rindu katrā datumā, viņš saņem vienu rindu par katru datumu un laiku.
Tagad būtu iespējams ierasties šeit un teikt: Grupēt lauku un grupēt to dienā, noklikšķiniet uz Labi. Un, jūs zināt, tas ir daži papildu klikšķi, kas, manuprāt, mums nav jādara. Tāpēc šim Podcast, nākamajiem Podcast apraides veidiem, mēs apskatīsim Power Query. Tagad tas ir iebūvēts programmā Excel 2016 sadaļā Saņemt un pārveidot datus. Ja jums ir 2010. vai 2013. gads, kas darbojas sistēmā Windows, nevis Mac, varat bez maksas lejupielādēt Power Query no Excel. Un Power Query vēlas strādāt pie galda vai diapazona.
Tāpēc es izvēlēšos vienu datu šūnu, lai tabulā nospiediet Ctrl + T, ideāls. Un pēc tam cilnē Power Query Dati, No tabulas / diapazona es izvēlēšos kolonnu Datums un Pārveidot un saku, ka vēlos, lai tas nebūtu Datums un Laiks, bet tikai Datums. Es izvēlēšos Aizstāt pašreizējo un pēc tam Sākums, Aizvērt un ielādēt, un mēs iegūstam pavisam jaunu lapas formu, kurā dati ir konvertēti. No šejienes, protams, mēs varam apkopot ar pagrieziena punktu, un tas būs ideāli.
Manuprāt, šīs metodes, Power Query, priekšrocība ir tad, kad Īans iegūst vairāk datu … Tātad šeit man ir jauni dati, es ņemšu šos jaunos datus, kopēšu datus un vienkārši nākšu šeit pie saviem vecajiem datiem un ielīmējiet tieši zemāk. Ievērojiet, ka tabulas marķiera beigas šobrīd atrodas 474. rindā. Kad es ielīmēju, tabulas marķiera beigas pārvietojas uz leju jauno datu apakšdaļā. Labi? Un šis vaicājums tiek rakstīts, pamatojoties uz tabulu. Labi? Tātad, pieaugot galdam.
Šeit ir viena problēma: ja jūs pirmo reizi izmantojat Power Query, Power Query sākas tik plaši - tikai uz pusi - un jūs nevarat redzēt ikonu Atjaunot. Tātad, jums tas ir jāvelk šeit šādi un jāiegūst šī lieliskā atsvaidzināšanas ikona un vienkārši noklikšķiniet uz Atsvaidzināt. Pašlaik mums ir ielādētas 473 rindas, un tagad ir ielādētas 563 rindas. Izveidojiet savu pagrieziena galdu, un dzīve ir lieliska.
Tagad, hei, izskatās, es zinu, ka šeit būtu bijis iespējams pievienot jaunu kolonnu = INT (D2) - - of the date - nokopējiet to uz leju, kopējiet un ielīmējiet īpašās vērtības. Bet tad jūs esat uz āķa, lai to izdarītu katru dienu. Labi? Tātad, lai gan šajā videoklipā ir vismaz trīs dažādi veidi, kā mēs to runājām, man tas ir Power Query un tikai spēja noklikšķināt uz Atsvaidzināt.
Power Query, es par to runāju savā jaunajā grāmatā MrExcel LIVe, 54 visu laiku lieliskākie Excel padomi.
Šodienas sērijas apkopojums: Īnss Nešvilā katru dienu iegūst datus no sistēmas, kolonnā Datums kaitinoši ir gan Datums, gan Laiks - tas pieskrūvē viņa pagrieziena tabulas, tāpēc viņam ir … Nu, trīs iespējamās lietas: Viena, izmantojiet funkcija INT, kopēt, ielīmēt vērtības; otrkārt, vienkārši atstājiet to kā datumu un laiku un pēc tam grupējiet rakurstabulu pēc datuma; vai, labāks risinājums, Power Query, lejupielādēto datu kopu izveido tabulā, izmantojot Ctrl + T, un pēc tam cilnē Dati izvēlieties No tabulas, atlasiet kolonnu Datums un laiks, Power Query dodieties uz cilni Pārveidot, pārveidot to uz datumu, aizvērt un ielādēt, no tā izveidojiet rakurstabulu. Nākamreiz, kad iegūsit datus, ielīmējiet sākotnējā tabulā, dodieties uz paneli Vaicājums, noklikšķiniet uz Atsvaidzināt, un viss ir kārtībā.
Sveiki, lai lejupielādētu darbgrāmatu no šodienas videoklipa, apmeklējiet vietni YouTube YouTube aprakstā.
Es vēlos pateikties Ianam par parādīšanos manā seminārā Nešvilā, es vēlos pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast no.
Lejupielādējiet Excel failu
Lai lejupielādētu Excel failu: date-time-to-date.xlsx
Lai vēl vairāk uzlabotu Power Query procesu:
- Pārliecinieties, vai lejupielādes darbgrāmata vienmēr tiek saglabāta vienā mapē ar tādu pašu nosaukumu.
- Sāciet no tukšas darbgrāmatas. Saglabāt kā ar tādu nosaukumu kā DownloadedFileTransformed.xlsx
- No tukšās darbgrāmatas Dati, No faila, No darbgrāmatas. Norādiet uz failu no 1. darbības.
- Izvēlieties Clean Data. Pārveidojiet datumu / laiku par datumu. Aizvērt un ielādēt.
- Ar peles labo pogu noklikšķiniet uz Vaicājumu panelis un izvēlieties Rekvizīti. Atverot failu, izvēlieties Atsvaidzināt datus.
- Katru dienu dodieties uz darbu. Iegūstiet kafiju. Pārliecinieties, ka mapē ir jauns fails Nr. 1.
- Atveriet darbgrāmatu DownloadFileTransformed.xlsx. Tam būs jauni dati, un datumi būs pareizi.
Excel dienas doma
Esmu lūdzis saviem Excel Master draugiem padomu par Excel. Šodienas doma apdomāt:
"Apstiprināšana ir jūsu draugs."
Duāns Aubins
