Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
In einem Excel-Forum (Herber) wurde unter dem Titel ” Format Punkt / Komma” folgende Frage gestellt:
Guten Tag Excel Experten,
ich habe in den Bereich (.range("E2:AS" & LZ) Zahlenwerte aus dem engl. Format,- also Punkt, statt Komma.
Wenn ich diese ersetzen möchte, zB mit
Replace What:=".", Replacement:=",", LookAt:=xlPart
wird immer noch nicht der Zahlenwert entsprechend angezeigt (wohl Apostroph vorhanden).
Gibt es ein Makro, was mir den oben genannten Bereich „in einem Wisch” entsprechend ändert (formatiert)?
Gruß
Fred
Verschiedene Formel-Lösungen und auch eine funktionierende VBA-Lösung wurden vorgestellt. Als bekennender Fan des Power Query-Tools finde ich natürlich, dass gerade so etwas eine „Steilvorlage” für PQ ist. 😎 Die vom Fragesteller eingestellte *.xlsb habe ich zur Vermeidung von Missverständnissen in eine *.xlsx umgewandelt, es war sowieso keine einzige Zeile VBA-Code enthalten. Diese derart konvertierte Datei können Sie hier in unserem Blog herunterladen.
Nach dem öffnen der Datei werden sie die Daten erst einmal in eine „Intelligente” Tabelle umwandeln. Und mit hoher Wahrscheinlichkeit wird solch ein Dialog-Fenster mit einer aus meiner Sicht irritierenden Nachfrage erscheinen:
… und was soll mir das nun sagen?
Einfach mit Ja bestätigen. Ich versichere Ihnen, dass diese Nachfrage nicht wegen der Umwandlung in das Standard-Format auftaucht. Die vorliegenden Daten wurden (auf welchem Wege auch immer) über eine andere Abfrage importiert und Excel „merkt” das nun und fragt vorsichtshalber einmal nach. 😉 Mehr dazu können Sie hier bei Microsoft nachlesen.
Hier hat der Fragesteller die gewünschten Daten aus dem Web importiert und als *.csv-Datei gespeichert. In Excel (Version 2016) hat er nun die Daten über den Weg Externe Daten abrufen | Aus Text importiert. Hinweis: Fred (der Fragesteller) hätte es in jeder Hinsicht einfach haben können. Den alternativen Weg des Imports einer csv-Datei zeige ich Ihnen weiter unten auf.
Wie soeben beschrieben, ein Klick auf die Schaltfläche Ja reicht aus, um die Verknüpfung mit den Daten zu lösen. Wenn Sie doch lieber die Sache „zu Fuß” angehen wollen, dann wählen Sie einfach erst einmal die Schaltfläche Nein. Alternativ schließen sie die Datei ohne zu speichern und importieren Sie die csv noch einmal auf beliebigem Wege. Vermutlich werden sie direkt nach dem Import zwischen Symbolleiste und der Bearbeitungszeitzeile eine Sicherheitswarnung finden, dass Externe Datenverbindungen deaktiviert wurden. Die können Sie einfach mit einem Klick auf das X rechts in der Leiste wegklicken. Menü Daten | Verbindungen und es wird sich solch ein Dialog auftun:
Hier werden alle Datenverbindungen aufgelistet und verwaltet
Der Name der Datei (hier: 132610) ist der Name jenes Files, welches ich aus dem Blog heruntergeladen und anschließend in eine csv konvertiert habe. Die Dateiendung wird (Windows-typisch bei Standard-Einstellungen) nicht mit angezeigt. In diesem Dialog können Sie verschiedene weitere Informationen erkunden und auch die Verbindung kappen (entfernen). Übrigens: Die vor mit weiter unten zum Download angebotene Datei hat einen etwas anderen Namen.
Aus der vorliegenden *.xlsx
O. k., Sie wollen erst einmal die *.xlsx nutzen und den Weg über Power Query beschreiten. Nachdem sie also auf ihre Weise die Datenverbindung entfernt oder den Datenimport auf andere Weise durchgeführt haben, sollte das jetzt mit dem umwandeln der Daten in eine Formatierte Tabelle auch klappen. Das geht entweder über das Menüband oder per StrgT bzw. StrgL. diese Tabelle importieren Sie dann über Daten | Aus Tabelle in den Power Query-Editor. Das stellt sich anschließend so dar:
Direkt nach dem Import in den Power Query-Editor
Das sieht ja bislang ganz vernünftig aus. Im ersten Schritt werden Sie das Datum (Spalte Date) von der Uhrzeit „befreien” wollen. Aber sie werden sich erinnern, dass die Fragestellung dergestalt war, dass auch ja diverse Zahlen mit einem Dezimaltrenner Punkt statt eines hierzulande genutzten Kommas dargestellt worden sind. Und wenn sie bis ganz nach rechts durchblättern werden sie diese Aussage nicht bestätigt finden. Weder ein Komma noch ein Punkt sind zu sehen. Zugegeben, das liegt in diesem Fall am Power Query.
Im rechten Seitenfenster werden Sie unter Angewendete Schritte erkennen, dass direkt unter dem Eintrag Quelle der Schritt Geänderter Typ automatisch eingefügt worden ist. Löschen Sie diesen Schritt beispielsweise durch einen Klick auf das rote 
links. Und siehe da, die Aussage des Fragestellers wird nun ganz klar bestätigt; jeweils ein Punkt statt eines Kommas als Dezimaltrenner. Sie werden auch rasch erkennen, dass der Datentyp als dieser Spalten von der Optik her Text jedoch in der Realität Beliebig ist. Markieren Sie alle Spalten ab GD<-5 bis zur letzten Spalte durch einen Klick in die erste Überschrift, Shift und dann in die letzte Überschrift. Führen Sie nun einen Rechtsklick in eine der markierten Überschriften durch, Typ ändern | Mit Gebietsschema… und im Dialog wählen Sie den Datentyp: Dezimalzahl. Bei Gebietsschema idealerweise ein Klick auf die Taste F und wählen Sie im DropDown Englisch (Welt). Nach einem OK werden Sie erkennen, dass jetzt alle Zahlen mit einem Komma als Dezimaltrenner versehen sind und auch rechtsbündig ausgerichtet sind. So soll es sein! 🙂
Zurück zum Thema „Datum mit Uhrzeit”. Idealerweise werden sie die Spalte durch einen Klick auf die Überschrift Date und den Datentyp direkt von Beliebig in Datum ändern. Damit wird die Abfrage prinzipiell ihren endgültigen Stand erreicht. Klicken Sie auf das Symbol Schließen & laden und die Daten werden in ein neues Blatt geschrieben.
Wenn sich jetzt die Quelldaten (egal ob csv oder Excel) verändern, reicht in der Ergebnis-Tabelle ein Klick auf Aktualisieren und der neueste Stand wird sofort generiert.
Import der heruntergeladenen csv-Datei
Auch wenn Microsoft csv-Dateien stets mit einem dem Excel-Logo zum verwechseln ähnlichen Symbol versieht, so ist ein Doppelklick auf solch eine Datei zum öffnen nicht ratsam. Da geschieht dann so einiges, was Sie vielleicht gar nicht wollen und auch nicht beeinflussen können. Wenn Sie Power Query einsetzen wollen, werden sie idealerweise in einem leeren Arbeitsblatt diesen Weg gehen: In Versionen bis Excel 2016 (einschließlich) Daten | Neue Abfrage | Aus Datei | Aus csv und anschließend die Auswahl der gewünschten Datei. Auch die csv können Sie von unserem Server herunterladen. Ab 2019⁄365 stellt sich das so dar: Daten | Aus Text/csv oder über den Menüpunkt Daten abrufen | Aus Datei | Aus Text/csv und auch hier im File-Dialog die entsprechende Datei markieren und öffnen. In diesem Fall öffnet sich anschließend dieser Dialog:
Das Dialogfenster für den Import von csv-Dateien
Klicken Sie auf die Schaltfläche Daten transformieren und sofort werden die Daten in den Power Query-Editor importiert. Die erste Spalte ist markiert. Und wahrscheinlich wird Ihnen auffallen, dass einerseits die Überschrift eine andere ist (Beteiligung statt vorher Date) und dass das Datum hier im Datentyp: Ganze Zahl ohne Trennung zwischen Tag, Monat und Jahr geschrieben ist. Ich zeige Ihnen hier zwei Wege auf, daraus wieder ein korrektes Datum zu machen:
- Sie ändern den Datentyp im ersten Schritt auf Text, das geht beispielsweise per Rechtsklick oder über das Menüband. Anschließend gleich noch einmal den Datentyp ändern dieses Mal aber auf Datum. Dass Sie jedes Mal die Änderung bestätigen müssen, ist korrekt und auch wichtig. Wenn Sie möchten, können Sie bei der Gelegenheit auch gleich die Überschrift von Beteiligung in Date ändern.
- Wenn Sie ein wenig tiefer in die Materie des Power Query einsteigen wollen, dann bietet sich auch ein Weg an, der auf Dauer gesehen um einiges effektiver ist und gewiss einen Erkenntnisgewinn bringt. Sehen Sie sich einmal diesen Bildschirmausschnitt, insbesondere den gelb markierten Teil an:
Vorsicht, vielleicht vor dem „Experiment” den Zustand per Schließen & laden sichern
Nach dem Komma ist der Datentyp als Int64.Type eingetragen. Ich markiere diesen Text, entferne ihn und trage stattdessen in exakt dieser Schreibweise (Kleinschreibung) type date ein. Ein Klick in die Daten der Abfrage und sie erkennen, dass der Effekt sofort gegeben ist. 😎
Für die Spalten GD<-5 bis zur letzten Spalte gilt im Prinzip das gleiche Vorgehen, wie im ersten Teil schon beschrieben. Sie markieren alle relevanten Spalten, wandeln den Datentyp im ersten Schritt in Text und anschließend per Rechtsklick in eine der markierten Überschriften in den Datentyp Mit Gebietsschema… um. Der endgültige Datentyp ist dann natürlich Dezimalzahl und das Gebietsschema ist dann beispielsweise Englisch (Welt) oder auch Englisch (USA).
Damit es auch diese Konvertierung der Daten erfolgreich abgeschlossen. Schließen & laden oder Schließen & laden in… und die Abfrage wird im Arbeitsblatt gespeichert. Ich ziehe den zweitgenannten Weg vor, weil dann die Daten im ersten leeren Arbeitsblatt gespeichert werden können und nicht automatisch ein neu erstelltes Tabellenblatt geschrieben werden.
Natürlich gilt auch hier die Vereinbarung, dass veränderte Quelldaten automatisch übernommen werden, wenn sie die Funktionalität Aktualisieren nutzen.
Zurück zum Absprung (weiter oben)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50 € freuen …