Messwerte (US-Format) formatieren und aktualisieren

Foren-FAQ: Messwerte im US-Format nach DE formatieren und aktualisieren.

Kurzform der Anfrage in einem Forum: Zwei unterschiedliche Messwerte (Feuchtigkeit und Temperatur) werden durch Sensoren im 15-Minutentakt erfasst. Die Daten werden fortlaufend in eine Textdatei mit einem Semikolon ; als Trenner geschrieben. Nach einer vollautomatischen oder händischen Aktualisierung sollen die Daten in eine 3-spaltigen Tabelle stets auf dem neuesten Stand dargestellt werden. Die Beispieldatei aus dem Forum können Sie hier als Textdatei herunterladen.

Importieren Sie die Datei über das Menü Power Query (Excel 2010/13) oder Daten | Abrufen und transformieren (Excel 2016/365) in den Abfrage-Editor. Power Query erkennt ohne ihr Zutun dass das Semikolon als Trenner für die Spalten dient. Sie erweitern die Schaltfläche Laden ▼ und wählen dort Laden in… Im folgenden Dialog klicken Sie auf Nur Verbindung erstellen, da sie im Endeffekt ja nur die aufbereiteten Daten darstellen wollen. Nach einem Klick auf Laden ▼ sehen Sie im Tabellenblatt (noch) keine Daten, aber wenn Sie den Abfrage-Editor beispielsweise durch einen Doppelklick auf die Schaltfläche im rechten Seitenfenster öffnen, erkennen Sie die importierten Daten.

Falls Sie sich vorher in einem Editor die Daten in der Textdatei angesehen haben wird Ihnen auffallen, dass in der 1. Spalte Column1 der Dezimalpunkt „unterschlagen“ worden ist. Darum als ersten Schritt im rechten Seitenfenster bei Angewendete Schritte den zweiten angezeigten Schritt Geänderter Typ löschen. Das führt dazu, dass der Datentyp dieser Spalte nicht mehr Dezimalzahl ist sondern Text, dafür aber mit dem Dezimalpunkt.

Um daraus eine Dezimalzahl nach deutscher Lesart (also mit dem standardmäßigen Komma als Dezimaltrenner) zu machen, ändern Sie wiederum den Datentyp:

  • Rechtsklick in die Überschrift Column1
  • Wählen Sie im Kontextmenü Typ ändern | Mit Gebietsschema…
  • Im Dialog wählen Sie bei Datentyp Dezimalzahl und bei Gebietsschema Englisch (USA). Ich klicke dazu auf die Taste F, dadurch wird im Dropdown auch die gewünschte Auswahl sichtbar und kann direkt angeklickt werden.

Die 2. und die 3. Spalte sind als Text formatiert, was auch erst einmal so bleiben kann. Die Spalte Column3 soll aber zu einem echten Datum umgewandelt werden. Was in plain Excel mit der TEIL()-Funktion recht einfach wäre ist hier doch etwas aufwendiger. Wobei es dann auch gar nicht so schlimm ist, denn Power Query kann dank der Sprache M aus dem ersten Teil jedes Zellinhalts durchaus ein Datum erzeugen. Ich gehe dazu so vor:

    • Klick in ein beliebiges Datum der Liste.
    • Unterhalb des Trennstrichs, welcher sich unter der Liste befindet, steht noch mal der Inhalt dieser Zelle als markierbarer Text. Ich klicke dort hinein und zähle die Anzahl der Zeichen bis zu den Sekunden (einschließlich). Das geht recht gut, wenn ich den Cursor jeweils mit den Tasten nach rechts bewege.
    • Da die Zählweise in PQ so gut wie immer 0-basiert ist ziehe ich 1 vom gefundenen Werte ab.
    • Menü Spalte hinzufügen | Benutzerdefinierte Spalte.
    • Als Spaltennamen vergebe ich Datum/Zeit.
    • In das große Textfeld Benutzerdefinierte Spaltenformel gebe ich nach dem = in exakt dieser Schreibweise ein: Start([Column3], 24) und bestätigen damit OK.
    • Weisen Sie der neu erstellten Spalte über das Menüband oder Rechtsklick in die Überschrift den Datentyp Datum/Uhrzeit zu.
    • Löschen Sie die Spalte Column3, die jetzt nicht mehr gebraucht wird.

Hinweis: Ergänzung vom 26.03.2018 23:00
Das war die Langform. Ich habe das einmal so gezeigt, damit Sie sich ein wenig mit der Sparache M vertraut machen. Dieses oder jenes geht nämlich nur über diesen Weg. Speziell das kürzen des Datum-Begriffs geht aber auch etwas komfortabler. Auch hier die Stichpunkte:

  • Markieren Sie die Überschrift Column3.
  • Zählen Sie aus, wieviel Zeichen Sie von links mit 1 beginnend brauchen.
  • Menü Spalte hinzufügen | Extrahieren | Erste Zeichen
  • Tragen Sie im Dialog 24 (Zeichen) ein und OK.
  • Löschen Sie die Spalte Column3.
  • Benennen Sie die eben erstellte Spalte in Datum/Zeit um.
  • Passen Sie den Typ auf Datum/Zeit an.

Die Forderung des Fragestellers war nun so, dass die zu erstellende Tabelle so aussehen soll (wörtliches Zitat):

Spalte 1 = Wert.
Hier kommen die Werte 29,50 30,0 etc.einfach untereinander als Zahl formatiert
Spalte 2 = W_Name (Feuchtigkeit, Temperatur=
Spalte 3 = Datum in dem Format 23.03.2018 14:23

Wenn das so realisiert werden soll, einfach an dieser Stelle ein Klick auf Schließen & laden. Es wird Sie vielleicht verwundern, dass sich eigentlich nichts getan hat; es wurde (immer noch) keine Tabelle erstellt. Das lässt sich aber rasch ändern, indem sie im rechten Seitenfenster ein Rechtsklick auf die Abfrage machen, dort Laden in… wählen und in der oberen Hälfte Tabelle anklicken. Im unteren Bereich des Dialogs wählen Sie Bestehendes Arbeitsblatt und belassen es bei der Zielposition $A$1. Ruckzuck sind die Daten an dieser Position. Was vielleicht noch bleibt: Die Überschriften nach Wunsch anpassen, was natürlich im Abfrage-Editor passieren sollte, damit das auch nach Aktualisierungen so bleibt. 😉 

Zugegeben, ich finde das nicht so wirklich übersichtlich. Falls jedoch später eine Pivottabelle erstellt werden soll, ist das aber im Gegensatz zu einer Kreuztabelle eine gute Wahl. Ich finde für eine übersichtliche Darstellung eine Kreuztabelle einfach schicker. Dazu öffnen Sie wiederum die Abfrage und erstellen davon über Start | Verwalten | Duplizieren ein Duplikat. Ich benenne die Abfrage gleich etwas um, indem ich in die Klammern statt der 2 das Wort Kreuztabelle schreibe.

  • Wechsel in das Menü Transformieren.
  • Markieren Sie die mittlere Spalte Column2 durch einen Klick in die Überschrift.
  • Wählen Sie im Menüband den Punkt Spalte Pivotieren:

Pivotieren der Daten für die Darstellung als Kreuztabelle

Pivotieren der Daten für die Darstellung als Kreuztabelle

  • Belassen Sie es bei der Vorgabe und klicken Sie auf OK.

Sie erkennen, dass die Überschriften ohne ihr Zutun vergeben worden sind. Und sie haben die gewünschte Darstellung als Kreuztabelle. Schließen & laden und in einem neuen Tabellenblatt werden diese Daten in exakt dieser Form in eine Liste geschrieben. Die Zahlen können so bleiben oder auch noch so formatiert werden, dass beispielsweise bei der Temperatur das Grad-Zeichen ° mit angezeigt wird. In der Spalte Feuchtigkeit können Sie über die Benutzerdefinierte Zahlenformatierung noch das Prozent-Zeichen in Anführungsstrichen dahinter schreiben lassen. Wenn sie später mehr vertraut sind mit Power Query, dann werden Sie innerhalb der Abfrage vielleicht eine Division durch 100 anstoßen und diese neuen verminderten Werte dann der erzeugten Tabelle/Liste als % formatiert:

Die Daten als Kreuztabelle und formatiert

Die Daten als Kreuztabelle und formatiert

Eine Aktualisierung kann dann nach Wunsch durch Klick auf die Schaltfläche Aktualisieren / Alle Aktualisieren (innerhalb des Abfrage-Editors) bzw. über einen Rechtsklick und dort ebenfalls Aktualisieren erfolgen. Alternativ ist es durchaus möglich, ein Makro im gewissen Zeit-Rhythmus die Aktualisierung der Abfragen durchführen zu lassen.

▲ nach oben …

Dieser Beitrag wurde unter Datentyp anpassen, Datum & Zeit, Foren-Q&A, Formatierung, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.