Ianas Našvilyje kiekvieną dieną gauna duomenis iš sistemos atsisiuntimo. Datos stulpelyje yra Data + Laikas. Dėl to „Pivot“ lentelėje yra kelios eilutės per dieną, o ne vienos langelio per dieną suvestinė.
Šiame straipsnyje parodomi trys problemos sprendimo būdai.
Žemiau esančiame paveikslėlyje parodytas stulpelis Data su data ir laiku.
Vienas iš sprendimų yra sutvarkyti jį suvestinėje lentelėje.
- Vilkite datos lauką į eilutę. Tai rodoma su data + laikas.
- Pasirinkite bet kurį langelį, kuriame yra data ir laikas.
- Skirtuke „Pivot Table Tools Analyze“ pasirinkite „Group Field“.
- Dialogo grupavimo dialogo lange pasirinkite Dienos. Panaikinti mėnesių pasirinkimą. Spustelėjus Gerai, suvestinėje lentelėje bus rodoma viena eilutė per dieną.
Kitas varginantis sprendimas - po atsisiuntimo pridėti naują stulpelį. Kaip parodyta šiame paveiksle, J stulpelio formulė yra =INT(D2).
Data + laikas yra sveika diena po dešimtainio laiko. Naudodami INT funkciją, kad gautumėte sveiką dienos ir laiko dalį, išskirsite datą.
Tai bus gerai, bet dabar kiekvieną kartą atsisiųsdami duomenis turite pridėti naują stulpelį.
Mano pageidaujamas sprendimas yra naudoti „Power Query“. „Power Query“ veikia tik „Windows“ leidimuose „Excel“. Negalite naudoti „Power Query“ „Android“, IOS ar „Mac“.
„Power Query“ yra integruota į „Excel 2016.“. „Excel 2010“ ir „2013“ galite atsisiųsti „Power Query“ iš „Microsoft“.
Pirmiausia konvertuokite atsisiųstus duomenis į lentelę naudodami Ctrl + T. Tada skirtuke Duomenys pasirinkite Iš lentelės / diapazono.
„Power Query“ tinklelyje pasirinkite stulpelį Data. Eikite į skirtuką „Transformuoti“. Atidarykite išskleidžiamąjį meniu Datos tipas ir pasirinkite Data. Jei gaunate pranešimą apie Pakeisti veiksmą arba Naujas veiksmas, pasirinkite Pakeisti veiksmą.
„Power Query“ skirtuke Pagrindinis pasirinkite Uždaryti ir įkelti.
Rezultatas bus rodomas naujame lape. Iš to lapo sukurkite suvestinę lentelę.
Kitą kartą, kai gausite naujų duomenų, atlikite šiuos veiksmus:
- Įklijuokite naujus duomenis ant pradinio duomenų rinkinio.
- Eikite į lapą su „Power Query“ rezultatu. Išplėskite užklausos skydelį, kad galėtumėte matyti „Atnaujinti“. Spustelėkite Atnaujinti.
- Atnaujinkite suvestinę lentelę.
Žiūrėti video
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2203 serija: sutrumpinkite datą ir laiką iki tiesiog datos.
Ei, sveiki sugrįžę į internetą, aš Billas Jelenas. Šiandienos Iano klausimas „Nashville Power Excel“ seminare. Pažvelkite į tai: Ianas kiekvieną dieną atsisiunčia iš savo sistemos, o stulpelyje „Data“ turi datą ir laiką, o tai kenkia, nes kai Ianas sukuria suvestinę lentelę ir įtraukia datas kairėje pusėje, užuot gavęs tik vieną eilutę per datą, jis gauna po vieną eilutę už kiekvieną datą ir laiką.
Dabar būtų galima ateiti čia ir pasakyti: Grupuoti lauką ir sugrupuoti tai į dieną, spustelėkite Gerai. Žinote, tai yra papildomi keli paspaudimai, kurių nemanau, kad turime daryti. Taigi, šiam „Podcast“, keliems kitiems „Podcast“, mes pažvelgsime į „Power Query“. Dabar jis įmontuotas „Excel 2016“ skiltyje „Gauti ir transformuoti duomenis“. Jei „Windows“, o ne „Mac“, veikia 2010 ar 2013 m., Galite nemokamai atsisiųsti „Power Query“ iš „Excel“. „Power Query“ nori dirbti prie stalo ar diapazono.
Taigi aš pasirinksiu vieną duomenų langelį, paspauskite „Ctrl“ + T, jei norite „Table“, tobulas. Tada skirtuke „Power Query“, „Data“, „Table / Range“, pasirenku stulpelį „Data“ ir „Transformuoti“ ir sakau, kad noriu, kad tai būtų ne data ir laikas, o tik data. Pasirinksiu Pakeisti dabartinę, tada - Namai, Uždaryti ir įkelti, ir gausime visiškai naują lapo formą, kurioje bus konvertuoti duomenys. Dabar, žinoma, galime apibendrinti su pasukimu ir tai bus tobula.
Manau, kad šio metodo, „Power Query“, pranašumas yra tas, kai Ianas gauna daugiau duomenų … Taigi čia aš turiu naujų duomenų, aš paimsiu šiuos naujus duomenis, nukopijuosiu duomenis ir tiesiog ateisiu čia prie savo senų duomenų ir įklijuokite šiek tiek žemiau. Ir atkreipkite dėmesį, kad lentelės žymeklio pabaiga šiuo metu yra 474 eilutėje. Kai įklijuoju, lentelės žymeklio galas juda žemyn į naujų duomenų apačią. Gerai? Ir ši užklausa parašyta remiantis lentele. Gerai? Taigi, kai stalas auga.
Dabar vienas vargas yra toks: jei „Power Query“ naudojate pirmą kartą, „Power Query“ prasideda taip plačiai - tik per pusę - ir nematote atnaujinimo piktogramos. Taigi, jūs turite jį taip nuvilkti čia ir gauti tą puikią atnaujinimo piktogramą ir tiesiog spustelėkite Atnaujinti. Šiuo metu turime 473 eiles, o dabar - 563 eiles. Sukurkite sukamą lentelę, ir gyvenimas yra puikus.
Dabar, ei, žiūrėk, aš žinau, kad čia būtų buvę įmanoma pridėti naują stulpelį = INT (D2) - - of the date - nukopijuokite jį žemyn, nukopijuokite ir įklijuokite specialias vertybes. Bet tada jūs esate ant kabliuko, kad tai darytumėte kiekvieną dieną. Gerai? Taigi, nors yra bent trys skirtingi būdai, apie kuriuos kalbėjome šiame vaizdo įraše, man tai padaryti - „Power Query“ ir tiesiog galimybė spustelėti „Atnaujinti“.
„Power Query“, apie tai kalbu savo naujojoje knygoje „MrExcel LIVe“, „54 geriausi visų laikų„ Excel “patarimai“.
Šiandienos epizodo apibendrinimas: Ianas Našvilyje kiekvieną dieną gauna duomenis iš sistemos, stulpelyje Data erzina ir data, ir laikas - jis prisuka jo sukamas lenteles, todėl jis turi … Na, trys galimi dalykai: vienas, naudojimas funkcija INT, kopijuoti, įklijuoti reikšmes; antra, tiesiog palikite jį kaip datą ir laiką, tada sugrupuokite sukimo lentelę pagal datą; arba, geresnis sprendimas, „Power Query“, atsisiųstą duomenų rinkinį padaro į lentelę naudodamas „Ctrl“ + T, tada skirtuke Duomenys pasirinkite Iš lentelės, pasirinkite stulpelį Data ir laikas, eikite į „Power Query“ skirtuką Transformuoti, konvertuoti „Data“, „Uždaryti ir įkelti“, sukurkite suvestinę lentelę. Kitą kartą, kai gausite duomenis, įklijuokite į pradinę lentelę, eikite į skydelį Užklausa, spustelėkite Atnaujinti ir viskas gerai.
Ei, norėdami atsisiųsti darbaknygę iš šiandienos vaizdo įrašo, apsilankykite „YouTube“ apraše esančiame URL.
Noriu padėkoti Ianui, kad jis pasirodė mano seminare Našvilyje, noriu padėkoti už tai, kad užsukote. Pasimatysime kitą kartą kitam internetiniam transliacijai iš.
Atsisiųskite „Excel“ failą
Norėdami atsisiųsti „Excel“ failą: date-time-to-date.xlsx
Norėdami toliau tobulinti „Power Query“ procesą:
- Įsitikinkite, kad atsisiuntimo darbaknygė visada yra išsaugota tame pačiame aplanke tuo pačiu pavadinimu.
- Pradėkite nuo tuščios darbaknygės. Išsaugokite kaip pavadinimą, pvz., DownloadedFileTransformed.xlsx
- Iš tuščios darbaknygės, duomenys, iš failo, iš darbaknygės. Nurodykite failą atlikdami 1 veiksmą.
- Pasirinkite „Išvalyti duomenis“. Pakeiskite datą / laiką į datą. Uždaryti ir įkelti.
- Dešiniuoju pelės mygtuku spustelėkite Užklausų skydelį ir pasirinkite Ypatybės. Pasirinkite Atnaujinti duomenis, kai atidarote failą.
- Kiekvieną dieną eik į darbą. Atsigerk kavos. Įsitikinkite, kad aplanke yra naujas # 1 failas.
- Atidarykite „DownloadFileTransformed.xlsx“ darbaknygę. Jame bus nauji duomenys, o datos bus teisingos.
„Excel“ dienos mintis
Aš paprašiau savo „Excel Master“ draugų patarimo apie „Excel“. Šiandienos mintis apmąstyti:
- Patvirtinimas yra tavo draugas.
Duane'as Aubinas
