Ian i Nashville får data hver dag fra en systemnedlasting. Datokolonnen inneholder dato + tid. Dette gjør at pivottabellen har flere rader per dag i stedet for et sammendrag av en celle per dag.
Denne artikkelen viser tre måter å løse problemet på.
Figuren nedenfor viser kolonnen Dato med Dato pluss tid.
En løsning er å fikse det i pivottabellen.
- Dra datofeltet til radområdet. Det vises med Date + Time.
- Velg en celle som inneholder en dato og et klokkeslett.
- Velg Gruppefelt på Analyser-fanen for pivottabell.
- Velg Dager i grupperingsdialogen. Ikke velg måneder. Når du klikker OK, vil pivottabellen vise en rad per dag.
En annen kjedelig løsning er å legge til en ny kolonne etter nedlastingen. Som vist i denne figuren, er formelen i kolonne J =INT(D2).
En dato + tid er et heltall etterfulgt av et desimaltid. Ved å bruke INT-funksjonen for å få heltallsdelen av dato + tid, isolerer du datoen.
Dette fungerer bra, men nå må du legge til den nye kolonnen hver gang du laster ned dataene.
Min foretrukne løsning er å bruke Power Query. Power Query fungerer bare i Windows-utgaver av Excel. Du kan ikke bruke Power Query på Android, IOS eller Mac.
Power Query er innebygd i Excel 2016. For Excel 2010 og 2013 kan du laste ned Power Query fra Microsoft.
Konverter først nedlastede data til en tabell ved hjelp av Ctrl + T. Velg deretter Fra tabell / område i kategorien Data.
Velg datakolonnen i Power Query-rutenettet. Gå til Transform-fanen. Åpne rullegardinmenyen Datotype og velg Dato. Hvis du får en melding om Erstatt trinn eller Nytt trinn, velger du Erstatt trinn.
Velg Lukk og last inn på Hjem-fanen i Power Query.
Resultatet ditt vises på et nytt ark. Lag pivottabellen fra arket.
Følg disse trinnene neste gang du får nye data:
- Lim inn de nye dataene over det originale datasettet.
- Gå til arket med resultatet av Power Query. Utvid spørringspanelet slik at du kan se Oppdater. Klikk på Oppdater.
- Oppdater pivottabellen.
Se på video
Videoutskrift
Lær Excel fra Podcast, episode 2203: Avkort dato og tid til bare en dato.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål fra Ian i mitt Nashville Power Excel-seminar. Se på dette: Ian får nedlasting fra systemet sitt hver dag, og det har - i dato-kolonnen - det har dato og klokkeslett, og det er å skru opp ting, for når Ian lager et pivottabell og legger datoer sammen på venstre side, i stedet for å bare få en rad per dato, får han en rad for hver dato og tid.
Nå ville det være mulig å komme hit og si, Group Field, og gruppere dette til Day, klikk OK. Og du vet, det er ekstra få klikk som jeg ikke tror vi må gjøre. Og så, for denne Podcasten, de neste få Podcasts, skal vi ta en titt på Power Query. Nå er det innebygd i Excel 2016 her, under Get & Transform Data. Hvis du har 2010 eller 2013 i Windows, ikke på en Mac, kan du laste ned Power Query gratis fra Excel. Og Power Query ønsker å jobbe på et bord eller et utvalg.
Så jeg skal velge en celle med data, trykk Ctrl + T for Table, perfekt. Og så, i kategorien Power Query, Data, From Table / Range, skal jeg velge Dato-kolonnen og Transform, og si at jeg vil at dette ikke skal være en dato og tid, men bare en dato. Jeg velger Erstatt nåværende, og deretter Hjem, Lukk og last, og vi får et helt nytt arkform som har konvertert dataene. Nå, herfra, kan vi selvfølgelig oppsummere med en pivot, og det vil være perfekt.
Fordelen, tror jeg, med denne metoden, Power Query, er når Ian får mer data … Så her har jeg nye data, jeg skal ta disse nye dataene, kopiere dataene og bare komme hit til mine gamle data og lim inn rett nedenfor. Og legg merke til at slutten av tabellmarkøren akkurat nå er i rad 474. Når jeg limer inn, flyttes enden av tabellmarkøren ned til bunnen av de nye dataene. Ok? Og dette spørsmålet er skrevet basert på tabellen. Ok? Så når bordet vokser.
Nå er det et problem her, hvis du bruker Power Query for første gang, starter Power Query så bredt - bare halvt bredt - og du kan ikke se Oppdater-ikonet. Så du må dra det ut her slik og få det flotte Oppdater-ikonet, og bare klikk på Oppdater. Akkurat nå har vi lastet 473 rader, og nå 563 rader lastet. Lag ditt pivottabell, så blir livet bra.
Nå, hei, se, jeg vet at det hadde vært mulig å legge til en ny kolonne her ute, = INT (D2) - - av datoen - kopier den ned, kopier og lim inn spesielle verdier. Men så er du på kroken for å gjøre det hver eneste dag. Ok? Så selv om det er minst tre forskjellige måter vi snakket om i denne videoen for å løse det, er det for meg Power Query og bare muligheten til å klikke på Oppdater.
Power Query, jeg snakker om det i min nye bok, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Avslutning av dagens episode: Ian i Nashville får data fra et system hver dag, dato-kolonnen har irriterende både dato pluss tid - den skruer opp pivottabellene, så han må … Vel, tre mulige ting: En, bruk INT-funksjonen, kopier, lim inn verdier; For det andre, bare la det være som dato og klokkeslett, og grupper deretter pivottabellen etter dato; eller, en bedre løsning, Power Query, gjør nedlastede datasett til en tabell med Ctrl + T, og deretter velger du Fra tabell i Data-fanen, velger kolonne Dato og tid, går til Transform-fanen i Power Query, konverterer det til en dato, lukk og last, bygg pivottabellen fra det. Neste gang du får data, lim inn i den opprinnelige tabellen, gå til spørringspanelet, klikk på Oppdater, så er du klar.
Hei, for å laste ned arbeidsboken fra dagens video, besøk URL-en i YouTube-beskrivelsen.
Jeg vil takke Ian for at du møtte opp i seminaret mitt i Nashville, jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: date-time-to-date.xlsx
For å forbedre Power Query-prosessen ytterligere:
- Sørg for at nedlastingsarbeidsboken alltid er lagret i samme mappe med samme navn.
- Start fra en tom arbeidsbok. Lagre som med et navn som DownloadedFileTransformed.xlsx
- Fra den tomme arbeidsboken, Data, Fra fil, Fra arbeidsbok. Pek på filen fra trinn 1.
- Velg Rens data. Forvandle dato / klokkeslett til en dato. Lukk og last.
- Høyreklikk på spørringspanelet og velg Egenskaper. Velg Oppdater data når du åpner filen.
- Gå på jobb hver dag. Få kaffen din. Forsikre deg om at en ny fil for nr. 1 er i mappen.
- Åpne DownloadFileTransformed.xlsx-arbeidsboken. Den vil ha de nye dataene, og datoene vil være riktige.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Validering er din venn."
Duane Aubin
