Ian in Nashville erhält jeden Tag Daten von einem System-Download. Die Datumsspalte enthält Datum + Uhrzeit. Dadurch verfügt die Pivot-Tabelle über mehrere Zeilen pro Tag anstelle einer Zusammenfassung einer Zelle pro Tag.
Dieser Artikel zeigt drei Möglichkeiten zur Lösung des Problems.
Die folgende Abbildung zeigt die Spalte Datum mit Datum plus Uhrzeit.
Eine Lösung besteht darin, es in der Pivot-Tabelle zu fixieren.
- Ziehen Sie das Feld Datum in den Bereich Zeilen. Es wird mit Datum + Uhrzeit angezeigt.
- Wählen Sie eine Zelle aus, die Datum und Uhrzeit enthält.
- Wählen Sie auf der Registerkarte Analyse der Pivot-Tabellen-Tools die Option Gruppenfeld.
- Wählen Sie im Dialogfeld Gruppierung die Option Tage aus. Monate abwählen. Wenn Sie auf OK klicken, wird in der Pivot-Tabelle eine Zeile pro Tag angezeigt.
Eine weitere mühsame Lösung besteht darin, nach dem Download eine neue Spalte hinzuzufügen. Wie in dieser Figur gezeigt, ist die Formel in Spalte j =INT(D2).
Ein Datum + eine Uhrzeit ist ein ganzzahliger Tag, gefolgt von einer Dezimalzeit. Indem Sie die INT-Funktion verwenden, um den ganzzahligen Teil von Datum + Uhrzeit abzurufen, isolieren Sie das Datum.
Dies funktioniert einwandfrei, aber jetzt müssen Sie die neue Spalte jedes Mal hinzufügen, wenn Sie die Daten herunterladen.
Meine bevorzugte Lösung ist die Verwendung von Power Query. Power Query funktioniert nur in Windows-Editionen von Excel. Sie können Power Query nicht unter Android, IOS oder Mac verwenden.
Power Query ist in Excel 2016 integriert. Für Excel 2010 und 2013 können Sie Power Query von Microsoft herunterladen.
Konvertieren Sie zuerst die heruntergeladenen Daten mit Strg + T in eine Tabelle. Wählen Sie dann auf der Registerkarte Daten die Option Aus Tabelle / Bereich.
Wählen Sie im Power Query-Raster die Spalte Datum. Gehen Sie zur Registerkarte Transformieren. Öffnen Sie die Dropdown-Liste Datumstyp und wählen Sie Datum. Wenn Sie eine Meldung zu Schritt ersetzen oder Neuer Schritt erhalten, wählen Sie Schritt ersetzen.
Wählen Sie auf der Registerkarte Start von Power Query die Option Schließen und Laden.
Ihr Ergebnis wird auf einem neuen Blatt angezeigt. Erstellen Sie die Pivot-Tabelle aus diesem Blatt.
Wenn Sie das nächste Mal neue Daten erhalten, gehen Sie folgendermaßen vor:
- Fügen Sie die neuen Daten über den ursprünglichen Datensatz ein.
- Wechseln Sie zum Blatt mit dem Ergebnis von Power Query. Erweitern Sie das Abfragefenster, damit Sie Aktualisieren sehen können. Klicken Sie auf Aktualisieren.
- Aktualisieren Sie die Pivot-Tabelle.
Schau Video
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2203: Datum und Uhrzeit auf ein Datum kürzen.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage von Ian in meinem Nashville Power Excel-Seminar. Schauen Sie sich das an: Ian bekommt jeden Tag einen Download von seinem System, und es hat - in der Spalte Datum - Datum und Uhrzeit, und das bringt alles durcheinander, denn wenn Ian eine Pivot-Tabelle erstellt und Daten zusammenstellt Auf der linken Seite erhält er nicht nur eine Zeile pro Datum, sondern eine Zeile für jedes Datum und jede Uhrzeit.
Jetzt wäre es möglich, hierher zu kommen und zu sagen: Feld gruppieren und dies zu Tag gruppieren. Klicken Sie auf OK. Und das sind ein paar zusätzliche Klicks, von denen ich glaube, dass wir sie nicht machen müssen. Für diesen Podcast, die nächsten Podcasts, werfen wir einen Blick auf Power Query. Jetzt ist es hier unter Get & Transform Data in Excel 2016 integriert. Wenn Sie 2010 oder 2013 unter Windows und nicht auf einem Mac ausführen, können Sie Power Query kostenlos von Excel herunterladen. Und Power Query möchte an einer Tabelle oder einem Bereich arbeiten.
Also werde ich eine Datenzelle auswählen, Strg + T für Tabelle drücken, perfekt. Und dann werde ich auf meiner Registerkarte "Power Query", "Daten", "Aus Tabelle / Bereich" die Spalte "Datum" und "Transformieren" auswählen und sagen, dass dies kein Datum und keine Uhrzeit sein soll, sondern nur ein Datum. Ich wähle "Aktuell ersetzen" und dann "Startseite", "Schließen & Laden" und wir erhalten ein brandneues Blattformular, in das die Daten konvertiert werden. Von hier aus können wir natürlich mit einem Drehpunkt zusammenfassen und es wird perfekt sein.
Ich denke, der Vorteil dieser Methode, Power Query, ist, wenn Ian mehr Daten erhält … Also hier habe ich neue Daten, ich werde diese neuen Daten nehmen, die Daten kopieren und einfach hierher zu meinen alten Daten kommen und direkt darunter einfügen. Beachten Sie, dass sich das Ende der Tabellenmarkierung derzeit in Zeile 474 befindet. Wenn ich einfüge, wird das Ende der Tabellenmarkierung an den unteren Rand der neuen Daten verschoben. In Ordung? Und diese Abfrage wird basierend auf der Tabelle geschrieben. In Ordung? Also, wenn der Tisch wächst.
Ein Problem hierbei ist, dass Power Query, wenn Sie Power Query zum ersten Mal verwenden, so breit beginnt - nur halb so breit - und Sie das Aktualisierungssymbol nicht sehen können. Sie müssen es also wie folgt hierher ziehen, um das großartige Aktualisierungssymbol zu erhalten, und einfach auf Aktualisieren klicken. Im Moment haben wir 473 Zeilen geladen und jetzt 563 Zeilen geladen. Erstellen Sie Ihren Pivot-Tisch, und das Leben ist großartig.
Nun, hey, schau, ich weiß, es wäre möglich gewesen, hier draußen eine neue Spalte hinzuzufügen, = INT (D2) - - des Datums - kopiere diese, kopiere und füge spezielle Werte ein. Aber dann sind Sie bereit, das jeden Tag zu tun. In Ordung? Während es in diesem Video mindestens drei verschiedene Möglichkeiten gibt, wie wir es lösen können, sind für mich Power Query und die Möglichkeit, auf Aktualisieren zu klicken, der richtige Weg.
Power Query, darüber spreche ich in meinem neuen Buch MrExcel LIVe, Die 54 besten Excel-Tipps aller Zeiten.
Zusammenfassung der heutigen Episode: Ian in Nashville erhält jeden Tag Daten von einem System. Die Datumsspalte enthält ärgerlicherweise sowohl Datum als auch Uhrzeit - sie vermasselt seine Pivot-Tabellen, also muss er … Nun, drei mögliche Dinge: Eins, verwenden die INT-Funktion, Kopieren, Einfügen von Werten; Zweitens lassen Sie es einfach als Datum und Uhrzeit und gruppieren Sie die Pivot-Tabelle nach Datum. oder, eine bessere Lösung, Power Query, macht den heruntergeladenen Datensatz mit Strg + T zu einer Tabelle. Wählen Sie dann auf der Registerkarte Daten die Option Aus Tabelle, wählen Sie die Spalte Datum und Uhrzeit aus, wechseln Sie in Power Query zur Registerkarte Transformieren und konvertieren Sie Erstellen Sie daraus die Pivot-Tabelle, um ein Datum festzulegen, zu schließen und zu laden. Wenn Sie das nächste Mal Daten erhalten, fügen Sie sie in die Originaltabelle ein, wechseln Sie zum Abfragefenster, klicken Sie auf Aktualisieren, und los geht's.
Hey, um die Arbeitsmappe aus dem heutigen Video herunterzuladen, besuchen Sie die URL in der YouTube-Beschreibung.
Ich möchte Ian dafür danken, dass er in meinem Seminar in Nashville aufgetaucht ist. Ich möchte Ihnen dafür danken, dass Sie vorbeigekommen sind. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: date-time-to-date.xlsx
So verbessern Sie den Power Query-Prozess weiter:
- Stellen Sie sicher, dass die Download-Arbeitsmappe immer im selben Ordner mit demselben Namen gespeichert ist.
- Beginnen Sie mit einer leeren Arbeitsmappe. Speichern unter mit einem Namen wie DownloadedFileTransformed.xlsx
- Aus der leeren Arbeitsmappe, Daten, Aus Datei, Aus Arbeitsmappe. Zeigen Sie auf die Datei aus Schritt 1.
- Wählen Sie Daten bereinigen. Verwandeln Sie das Datum / die Uhrzeit in ein Datum. Schließen & Laden.
- Klicken Sie mit der rechten Maustaste auf das Abfragefenster und wählen Sie Eigenschaften. Wählen Sie Beim Öffnen der Datei Daten aktualisieren.
- Gehen Sie jeden Tag zur Arbeit. Hol deinen Kaffee. Stellen Sie sicher, dass sich eine neue Datei für # 1 im Ordner befindet.
- Öffnen Sie Ihre DownloadFileTransformed.xlsx-Arbeitsmappe. Es wird die neuen Daten haben und die Daten werden korrekt sein.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Validierung ist dein Freund."
Duane Aubin
