Ian in Nashville haalt elke dag gegevens uit een systeemdownload. De datumkolom bevat Datum + Tijd. Hierdoor heeft de draaitabel meerdere rijen per dag in plaats van een samenvatting van één cel per dag.
Dit artikel laat drie manieren zien om het probleem op te lossen.
De onderstaande afbeelding toont de kolom Datum met Datum plus tijd.
Een oplossing is om het in de draaitabel te bevestigen.
- Sleep het veld Datum naar het gebied Rijen. Het verschijnt met Datum + Tijd.
- Selecteer een cel die een datum en tijd bevat.
- Kies Groepsveld op het tabblad Hulpmiddelen voor draaitabellen analyseren.
- Selecteer Dagen in het dialoogvenster Groeperen. Schakel Maanden uit. Als u op OK klikt, toont de draaitabel één rij per dag.
Een andere vervelende oplossing is om na het downloaden een nieuwe kolom toe te voegen. Zoals getoond in deze figuur, de formule in kolom J =INT(D2)
.
Een datum + tijd is een geheel getal dag gevolgd door een decimale tijd. Door de INT-functie te gebruiken om het gehele deel van datum + tijd te krijgen, isoleer je de datum.
Dit werkt prima, maar nu moet u elke keer dat u de gegevens downloadt, de nieuwe kolom toevoegen.
Mijn voorkeursoplossing is om Power Query te gebruiken. Power Query werkt alleen in Windows-edities van Excel. U kunt Power Query niet gebruiken op Android, IOS of Mac.
Power Query is ingebouwd in Excel 2016. Voor Excel 2010 en 2013 kunt u Power Query van Microsoft downloaden.
Converteer eerst de gedownloade gegevens naar een tabel met Ctrl + T. Kies vervolgens op het tabblad Gegevens de optie Uit tabel / bereik.
Kies in het Power Query-raster de kolom Datum. Ga naar het tabblad Transformeren. Open de vervolgkeuzelijst Datumtype en kies Datum. Als u een bericht krijgt over Stap vervangen of Nieuwe stap, kiest u Stap vervangen.
Kies op het tabblad Start van Power Query de optie Sluiten en laden.
Uw resultaat verschijnt op een nieuw blad. Maak de draaitabel van dat blad.
Volg deze stappen de volgende keer dat u nieuwe gegevens ontvangt:
- Plak de nieuwe gegevens over de originele gegevensset.
- Ga naar het blad met het resultaat van Power Query. Verbreed het Query-paneel zodat u Vernieuwen kunt zien. Klik op Vernieuwen.
- Vernieuw de draaitabel.
Bekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2203: verkort datum en tijd tot slechts een datum.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag van Ian in mijn Nashville Power Excel-seminar. Kijk hier eens naar: Ian krijgt elke dag een download van zijn systeem, en het heeft - in de Datum-kolom - het heeft Datum en tijd, en dat is een rotzooi, want als Ian een draaitabel maakt en datums toevoegt aan de linkerkant, in plaats van slechts één rij per datum te krijgen, krijgt hij één rij voor elke datum en tijd.
Nu zou het mogelijk zijn om hier te komen en te zeggen, Groepsveld, en dit te groeperen naar Dag, klik op OK. En weet je, dat zijn een paar extra klikken waarvan ik denk dat we ze niet hoeven te doen. En dus, voor deze podcast, de komende paar podcasts, gaan we Power Query bekijken. Nu is het hier in Excel 2016 ingebouwd, onder Gegevens ophalen en transformeren. Als u 2010 of 2013 in Windows hebt uitgevoerd, niet op een Mac, kunt u Power Query gratis downloaden vanuit Excel. En Power Query wil werken aan een tabel of bereik.
Dus ik ga een cel met gegevens kiezen, druk op Ctrl + T voor Tabel, perfect. En dan, op mijn Power Query-tabblad, Gegevens, Uit tabel / bereik, ga ik de kolom Datum en Transformeren kiezen en zeg ik dat ik wil dat dit geen datum en tijd is, maar alleen een datum. Ik kies Huidige vervangen en vervolgens Home, Sluiten en laden, en we krijgen een geheel nieuw bladformulier waarin de gegevens zijn geconverteerd. Nu, vanaf hier, kunnen we natuurlijk samenvatten met een draaipunt en het zal perfect zijn.
Het voordeel, denk ik, van deze methode, Power Query, is wanneer Ian meer gegevens krijgt … Dus hier heb ik nieuwe gegevens, ik ga deze nieuwe gegevens nemen, de gegevens kopiëren en gewoon hier naar mijn oude gegevens komen en plak er net onder. En merk op dat de markering voor het einde van de tabel zich nu in rij 474 bevindt. Als ik plak, wordt de markering voor het einde van de tabel naar de onderkant van de nieuwe gegevens verplaatst. Oké? En deze vraag is geschreven op basis van de tabel. Oké? Dus als de tafel groeit.
Een probleem hierbij is dat als u Power Query voor het eerst gebruikt, Power Query zo breed begint - slechts half breed - en u het pictogram Vernieuwen niet kunt zien. Dus je moet het op deze manier naar hier slepen en dat geweldige vernieuwingspictogram krijgen en gewoon op Vernieuwen klikken. Op dit moment hebben we 473 rijen geladen en nu 563 rijen geladen. Creëer uw draaitabel en het leven is geweldig.
Nu, hé, kijk, ik weet dat het mogelijk zou zijn geweest om hier een nieuwe kolom toe te voegen, = INT (D2) - - van de datum - kopieer dat naar beneden, kopieer en plak speciale waarden. Maar dan zit je eraan vast om dat elke dag te doen. Oké? Dus hoewel er in deze video ten minste drie verschillende manieren zijn om het op te lossen, is voor mij Power Query en, gewoon de mogelijkheid om op Vernieuwen te klikken, de juiste keuze.
Power Query, ik praat erover in mijn nieuwe boek, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Afronding van de aflevering van vandaag: Ian in Nashville haalt elke dag gegevens uit een systeem, de kolom Datum bevat irritant zowel Datum als Tijd - het verknoeit zijn draaitabellen, dus hij moet … Nou, drie mogelijke dingen: Eén, gebruik de INT-functie, kopieer, plak waarden; ten tweede, laat het gewoon staan als Datum en tijd en groepeer de draaitabel op Datum; of, een betere oplossing, Power Query, maakt van de gedownloade gegevensset een tabel met Ctrl + T, en kies vervolgens op het tabblad Gegevens Uit tabel, selecteer de kolom Datum en tijd, ga naar het tabblad Transformeren in Power Query, converteer het naar een datum, sluiten en laden, bouw daaruit de draaitabel. De volgende keer dat u gegevens ophaalt, plakt u in de originele tabel, gaat u naar het Query-paneel, klikt u op Vernieuwen en bent u klaar om te gaan.
Hé, om de werkmap van de video van vandaag te downloaden, gaat u naar de URL in de YouTube-beschrijving.
Ik wil Ian bedanken voor het verschijnen op mijn seminar in Nashville, ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: date-time-to-date.xlsx
Om het Power Query-proces verder te verbeteren:
- Zorg ervoor dat de downloadwerkmap altijd in dezelfde map met dezelfde naam wordt opgeslagen.
- Begin met een lege werkmap. Opslaan als met een naam als DownloadedFileTransformed.xlsx
- Uit de lege werkmap, gegevens, uit bestand, uit werkmap. Wijs naar het bestand uit stap 1.
- Kies Gegevens opschonen. Transformeer de datum / tijd naar een datum. Sluiten en laden.
- Klik met de rechtermuisknop op het Queryvenster en kies Eigenschappen. Kies Gegevens vernieuwen bij het openen van het bestand.
- Ga elke dag naar je werk. Haal je koffie. Zorg ervoor dat er een nieuw bestand voor # 1 in de map staat.
- Open uw DownloadFileTransformed.xlsx-werkmap. Het bevat de nieuwe gegevens en de datums zullen correct zijn.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Validatie is je vriend."
Duane Aubin