Ian i Nashville får data hver dag fra en systemdownload. Datokolonnen indeholder Dato + Tid. Dette gør, at pivottabellen har flere rækker om dagen i stedet for en oversigt over en celle om dagen.
Denne artikel viser tre måder at løse problemet på.
Figuren nedenfor viser kolonnen Dato med Dato plus tid.
En løsning er at rette det i pivottabellen.
- Træk datofeltet til rækkeområdet. Det vises med Date + Time.
- Vælg en celle, der indeholder en dato og et klokkeslæt.
- Vælg Gruppefelt på fanen Analyser på pivottabelværktøjer.
- Vælg Dage i dialogboksen Gruppering. Fravælg måneder. Når du klikker på OK, vises pivottabellen en række om dagen.
En anden kedelig løsning er at tilføje en ny kolonne efter download. Som vist i denne figur formlen i kolonne J er =INT(D2).
En dato + tid er et heltal efterfulgt af et decimaltid. Ved at bruge INT-funktionen til at få heltalets del af dato + klokkeslæt, isolerer du datoen.
Dette fungerer fint, men nu skal du tilføje den nye kolonne hver gang du downloader dataene.
Min foretrukne løsning er at bruge Power Query. Power Query fungerer kun i Windows-udgaver af Excel. Du kan ikke bruge Power Query på Android, IOS eller Mac.
Power Query er indbygget i Excel 2016. For Excel 2010 og 2013 kan du downloade Power Query fra Microsoft.
Konverter først de downloadede data til en tabel ved hjælp af Ctrl + T.Vælg derefter Fra tabel / rækkevidde under fanen Data.
Vælg kolonnen Dato i Power Query-gitteret. Gå til fanen Transform. Åbn rullemenuen Datotype, og vælg Dato. Hvis du får en besked om Erstat trin eller Nyt trin, skal du vælge Erstat trin.
Vælg Luk og indlæs på fanen Hjem i Power Query.
Dit resultat vises på et nyt ark. Opret pivottabellen fra det ark.
Følg disse trin næste gang du får nye data:
- Indsæt de nye data over det originale datasæt.
- Gå til arket med resultatet af Power Query. Udvid forespørgselspanelet, så du kan se Opdater. Klik på Opdater.
- Opdater drejetabellen.
Se video
Videoudskrift
Lær Excel fra Podcast, afsnit 2203: Afkort dato og tid til bare en dato.
Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål fra Ian i mit Nashville Power Excel-seminar. Se på dette: Ian får en download fra sit system hver dag, og det har - i datakolonnen - det har dato og klokkeslæt, og det skruer tingene op, for når Ian opretter en drejetabel og sætter datoer sammen i venstre side, i stedet for bare at få en række pr. dato, får han en række for hver dato og tid.
Nu ville det være muligt at komme her og sige, Group Field, og gruppere dette til Day, klik på OK. Og du ved, det er ekstra få klik, som jeg ikke tror, vi skal gøre. Og så, for denne Podcast, de næste par Podcasts, skal vi se på Power Query. Nu er det indbygget i Excel 2016 her under Get & Transform Data. Hvis du kører 2010 eller 2013 i Windows, ikke på en Mac, kan du downloade Power Query gratis fra Excel. Og Power Query ønsker at arbejde på et bord eller et interval.
Så jeg vælger en celle med data, tryk på Ctrl + T for tabel, perfekt. Og derefter, på min Power Query-fane, Data, Fra tabel / rækkevidde, skal jeg vælge datakolonnen og transformere og sige, at jeg ikke vil have, at dette ikke skal være en dato og et klokkeslæt, men kun en dato. Jeg vælger Erstat nuværende og derefter Hjem, Luk og indlæs, og vi får en helt ny arkform, der konverterer dataene. Nu herfra kan vi naturligvis opsummere med en drejning, og det vil være perfekt.
Fordelen, tror jeg, ved denne metode, Power Query, er, når Ian får flere data … Så her har jeg nye data, jeg vil tage disse nye data, kopiere dataene og bare komme her til mine gamle data og indsæt lige nedenfor. Og bemærk, at slutningen af tabelmarkøren lige nu er i række 474. Når jeg indsætter, flyttes slutningen af tabelmarkøren ned til bunden af de nye data. I orden? Og denne forespørgsel er skrevet ud fra tabellen. I orden? Så når bordet vokser.
Nu er et besvær her, hvis du bruger Power Query for første gang, starter Power Query så bredt - kun halvt bredt - og du kan ikke se ikonet Opdater. Så du er nødt til at trække det ud her sådan og få det store Opdater ikon, og bare klik på Opdater. Lige nu har vi indlæst 473 rækker og nu 563 rækker. Opret dit drejebord, og livet er godt.
Nu, hej, se, jeg ved, at det ville have været muligt at tilføje en ny kolonne herude, = INT (D2) - - af datoen - kopier det ned, kopier og indsæt specielle værdier. Men så er du på krogen for at gøre det hver eneste dag. I orden? Så selvom der er mindst tre forskellige måder, vi talte om i denne video for at løse det, er Power Query for mig og bare muligheden for at klikke på Opdater, vejen at gå.
Power Query, jeg taler om det i min nye bog, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Afslutning af dagens episode: Ian i Nashville får data fra et system hver dag, dato-kolonnen har irriterende begge dato plus tid-- den skruer op i sine drejetabeller, så han skal … Nå, tre mulige ting: Én, brug INT-funktionen, kopier, indsæt værdier; For det andet skal du bare lade det være dato og klokkeslæt og derefter gruppere drejetabellen efter dato; eller, en bedre løsning, Power Query, gør det downloadede datasæt til en tabel med Ctrl + T, og vælg derefter Fra fanen Data, vælg kolonnen Dato og tid, gå til fanen Transform i Power Query, konverter det til en dato, luk og indlæs, bygg pivottabellen ud fra det. Næste gang du får data, skal du indsætte i den originale tabel, gå til forespørgselspanelet, klikke på Opdater, så er du klar.
Hej, for at downloade projektmappen fra dagens video skal du besøge webadressen i YouTube-beskrivelsen.
Jeg vil takke Ian for at dukke op på mit seminar i Nashville, jeg vil gerne takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download Excel-fil
For at downloade excel-filen: date-time-to-date.xlsx
For yderligere at forbedre Power Query-processen:
- Sørg for, at download-projektmappen altid er gemt i den samme mappe med det samme navn.
- Start fra en tom projektmappe. Gem som med et navn som DownloadedFileTransformed.xlsx
- Fra den tomme projektmappe, Data, Fra fil, Fra projektmappe. Peg på filen fra trin 1.
- Vælg Rens data. Omdan dato / tid til en dato. Luk og indlæs.
- Højreklik på forespørgselspanelet, og vælg Egenskaber. Vælg Opdater data, når filen åbnes.
- Gå på arbejde hver dag. Få din kaffe. Sørg for, at en ny fil til nr. 1 er i mappen.
- Åbn din DownloadFileTransformed.xlsx-projektmappe. Det vil have de nye data, og datoerne vil være korrekte.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Validering er din ven."
Duane Aubin
