Ian i Nashville får data varje dag från en systemnedladdning. Datumkolumnen innehåller Datum + tid. Detta gör att pivottabellen har flera rader per dag istället för en sammanfattning av en cell per dag.
Den här artikeln visar tre sätt att lösa problemet.
Bilden nedan visar kolumnen Datum med datum plus tid.
En lösning är att fixa det i pivottabellen.
- Dra datumfältet till området Rader. Det dyker upp med Date + Time.
- Välj en cell som innehåller ett datum och en tid.
- Välj Gruppfält på fliken Analysera pivottabellverktyg.
- Välj Dagar i dialogrutan Gruppering. Avmarkera månader. När du klickar på OK visas pivottabellen en rad per dag.
En annan tråkig lösning är att lägga till en ny kolumn efter nedladdningen. Såsom visas i denna figur, är formeln i kolumn J =INT(D2).
Ett datum + tid är ett heltalsdag följt av en decimaltid. Genom att använda INT-funktionen för att få heltalet av datum + tid isolerar du datumet.
Detta kommer att fungera bra, men nu måste du lägga till den nya kolumnen varje gång du laddar ner data.
Min föredragna lösning är att använda Power Query. Power Query fungerar bara i Windows-versioner av Excel. Du kan inte använda Power Query på Android, IOS eller Mac.
Power Query är inbyggt i Excel 2016. För Excel 2010 och 2013 kan du ladda ner Power Query från Microsoft.
Konvertera först nedladdade data till en tabell med Ctrl + T.Välj sedan tabell / intervall på fliken Data.
Välj kolumnen Datum i Power Query-rutnätet. Gå till fliken Transform. Öppna rullgardinsmenyn Datumtyp och välj Datum. Om du får ett meddelande om Ersätt steg eller Nytt steg väljer du Ersätt steg.
Välj Stäng och ladda in på fliken Start i Power Query.
Ditt resultat kommer att visas på ett nytt ark. Skapa pivottabellen från det arket.
Följ följande steg nästa gång du får nya data:
- Klistra in de nya uppgifterna över den ursprungliga datamängden.
- Gå till arket med resultatet av Power Query. Bredda frågepanelen så att du kan se Uppdatera. Klicka på Uppdatera.
- Uppdatera pivottabellen.
Kolla på video
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2203: Avkorta datum och tid till bara ett datum.
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga från Ian i mitt Nashville Power Excel-seminarium. Titta på detta: Ian hämtar från sitt system varje dag, och det har - i kolumnen Datum - det har datum och tid, och det är att skruva upp saker eftersom när Ian skapar en pivottabell och lägger datum tillsammans på vänster sida, istället för att bara få en rad per datum, får han en rad för varje datum och tid.
Nu skulle det vara möjligt att komma hit och säga, Gruppfält, och gruppera detta till Dag, klicka på OK. Och du vet, det är några extra klick som jag inte tror att vi måste göra. Och så, för denna Podcast, de närmaste Podcasts, kommer vi att ta en titt på Power Query. Nu är det inbyggt i Excel 2016 här, under Get & Transform Data. Om du kör 2010 eller 2013 i Windows, inte på en Mac, kan du ladda ner Power Query gratis från Excel. Och Power Query vill arbeta på en tabell eller ett intervall.
Så jag ska välja en datacell, tryck Ctrl + T för tabell, perfekt. Och sedan, på min Power Query-flik, Data, från tabell / intervall, ska jag välja kolumnen Datum och omvandla och säga att jag inte vill att detta ska vara ett datum och en tid, utan bara ett datum. Jag väljer Byt ut nuvarande och sedan Hem, Stäng och ladda, så får vi ett helt nytt arkformat som konverterar data. Nu, härifrån, kan vi naturligtvis sammanfatta med en pivot och det blir perfekt.
Fördelen, tror jag, med den här metoden, Power Query, är när Ian får mer data … Så här har jag nya data, jag ska ta den nya informationen, kopiera data och bara komma hit till mina gamla data och klistra in precis nedanför. Lägg märke till att slutet av tabellmarkören just nu finns i rad 474. När jag klistrar in flyttas slutet av tabellmarkören ner till botten av de nya uppgifterna. OK? Och denna fråga skrivs utifrån tabellen. OK? Så när bordet växer.
Nu är ett besvär här, om du använder Power Query för första gången, börjar Power Query så brett - bara halvt brett - och du kan inte se ikonen Uppdatera. Så du måste dra ut den här så här och få den fantastiska uppdateringsikonen och klicka bara på Uppdatera. Just nu har vi 473 rader laddade och nu 563 rader laddade. Skapa ditt pivottabell så blir livet fantastiskt.
Nu, hej, titta, jag vet att det hade varit möjligt att lägga till en ny kolumn här, = INT (D2) - - av datumet - kopiera det nedåt, kopiera och klistra in specialvärden. Men då är du på kroken för att göra det varje dag. OK? Så även om det finns åtminstone tre olika sätt som vi pratade om i den här videon för att lösa det, för mig är Power Query och bara möjligheten att klicka på Uppdatera vägen att gå.
Power Query, jag pratar om det i min nya bok, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Avslutning av dagens avsnitt: Ian i Nashville hämtar data från ett system varje dag, Datumkolumnen har irriterande både Datum plus tid - det skruvar upp pivottabellerna, så han måste … Tja, tre möjliga saker: En, använd INT-funktionen, kopiera, klistra in värden; för det andra, lämna det bara som datum och tid och gruppera sedan pivottabellen efter datum; eller, en bättre lösning, Power Query, gör den nedladdade datauppsättningen till en tabell med Ctrl + T, och välj sedan Från tabell, välj kolumnen Datum och tid, gå till fliken Transform i Power Query, konvertera det till ett datum, stäng & ladda, bygg pivottabellen från det. Nästa gång du får data klistrar du in i originaltabellen, går till fråge-panelen, klickar på Uppdatera så är du klar.
Hej, för att ladda ner arbetsboken från dagens video, besök webbadressen i YouTube-beskrivningen.
Jag vill tacka Ian för att ha dykt upp i mitt seminarium i Nashville, jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Ladda ner Excel-fil
För att ladda ner excel-filen: date-time-to-date.xlsx
För att ytterligare förbättra Power Query-processen:
- Se till att nedladdningsarbetsboken alltid sparas i samma mapp med samma namn.
- Börja från en tom arbetsbok. Spara som med ett namn som DownloadedFileTransformed.xlsx
- Från den tomma arbetsboken, Data, Från fil, Från arbetsbok. Peka på filen från steg 1.
- Välj Rengör data. Förvandla datum / tid till ett datum. Stäng och ladda.
- Högerklicka på frågepanelen och välj Egenskaper. Välj Uppdatera data när filen öppnas.
- Gå till jobbet varje dag. Skaffa ditt kaffe. Se till att en ny fil för # 1 finns i mappen.
- Öppna din DownloadFileTransformed.xlsx-arbetsbok. Den kommer att ha de nya uppgifterna och datumen kommer att vara korrekta.
Excel-tanke på dagen
Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på:
"Validering är din vän."
Duane Aubin
