Community

Daten mal anders darstellen mit Excel


#1

Hallo zusammen,

ich habe nach einer Lösung für mich gesucht, wie ich die Daten also Stromverbrauch Temperatur etc. besser Auswerten und Grafisch darstellen kann. Ich weiß das es hier im Forum schon Lösungen gibt. Hier auf jeden Fall mal ein anderer Ansatz.

Da ich mich schon immer viel mit Excel beschäftigt habe, war es für mich klar, dass es eine Lösung in Excel sein wird. Mein erster Versuch war es, über VBA die CSV Datei einzulesen. Das war mir aber zu umständlich. Warum also nicht auf die Hausinternen mittle von Excel zurückgreifen. Das Programm dafür heißt Power query. Ab Version 2013 ist es Standardmäßig mit dabei. Bei Excel 2010 kann man es optional hinzufügen.

Der große Vorteil für mich war, dass man in Excel alles „relativ frei“ so einstellen kann, wie man es möchte. Es ist vieles möglich.

Eigentlich ist es selbsterklärend, aber wie es immer so ist muss man doch ein paar Dinge beachten. Ich habe mal eine Anleitung erstellt:

Als erstes muss man eine Abfrage erstellen. Am besten wählt ihr aus Ordner aus, damit auch alle Daten eingelesen werden. Man kann natürlich auch nur einzelne Ordner bzw Geräte auswählen. In meinem Beispiel lade ich mal alle Geräte aus, also den ganzen Orden auf dem FTP Server. Excel wird diese Datenbank, dann später mit einem Klick immer wieder aktualisieren.

Im nächsten Schritt wählt ihr unten Kombinieren und laden aus. Hier lädt er alle Unterordner.

Jetzt wählt ihr bei Dateiursprung 65001 UTF8 aus und bei Trennzeichen Komma, damit er später alles richtig trennt. Nach dem ihr okay gedrückt habt, dauert es etwas, je nachdem wie viel Daten ihr so gesammelt habt :wink:

Keine Angst die Tabelle bleibt nicht so. Jetzt kommt das wichtigste. Das nachbearbeiten. Dafür klickt ihr rechts mit der rechten Maustaste auf eure so erbend erstellen Abfrage und klickt auf bearbeiten.

Jetzt öffnet sich der Editor von Power query. Hier kann man seine Daten nochmal nach editieren. Wichtig ist das man als erstes die Zeile mit den Werten markiert und es dann als Text formatiert. Beständigen mit Aktuelle ersetzen.

Danach auf Werte ersetzen und dort den „.“ Ersetzen durch „,“. Im nächsten Schritt müsst ihr den Dateityp auf Dezimalzahl stellen.

Diese Schritte sind wichtig, damit Excel es am Ende vernünftig angezeigt und man mit den Zahlen rechnen kann.

Damit es später in der Grafik einfach wird und ihr auch die einzelnen Geräte wiedererkennt, Teile ich die Zeilen noch etwas auf.

Jetzt benenne ich die Zeilen erstmal Sinnvoll um damit man diese später auch wiederfinden kann.

Jetzt stört mich noch beim Tag das“.csv“ . Diese werde ich durch ersten durch wieder los. Beim Ersetzen durch lässt ihr einfach das Feld frei. Jetzt stellt ihr das Feld Tag noch auf dem Dateityp Datum um.

Dadurch das bei mir der summierte Verbrauch nicht immer richtig anzeigt wird, filtere ich die hohen Werte raus. Also kleiner 1000 bei mir.

Jetzt seid ihr in von Power query erstmal fertig. Drückt also oben links auf schließen und laden. Jetzt wird die Tabelle aktualisiert und richtig angezeigt. Dies kann je nach Datensätze etwas dauern. Bei mir sind es 200 000 Stück. Um die Datenmenge klein zu halten kann man in von Power query auch vorab zum Beispiel nur den Summierten verbrauch anzeigen lassen. Excel wird die Daten dann immer in dem Format einlesen und abspeichern. Wenn jetzt neue Dateien dazu kommen, kann man mit einem rechten klick auf die Tabelle dieses Aktualisieren.

Damit die daten auch schöner Dargestellt werden können und es auch eine Grafik dazu gibt erstelle ich eine PivotChart Tabelle. Dafür oben auf den Reiter einfügen gehen.

Beim Bereich Markiere ich meine Datensätze und drücke anschließen auf OK.
Jetzt öffnet sich eine neue Tabelle mit der Leeren Pivot Tabelle.

Anschließend müsst ihr eure Daten rechts in die entsprechenden Felder ziehen.

Dadurch das bei mir alle Werte eingelesen werden sieht es erstmal etwas chaotisch aus. Beim summierten Verbrauch zum Beispiel ist es eher etwas umständlich, dass zu viele Werte am Tag angezeigt werden. Deswegen werden ich es etwas zusammenfassen.

Deswegen müssen jetzt noch ein paar Filter Angewendet werden. In meinen Fall möchte ich nur den Summierten Verbrauch anzeigen lassen. Man kann sich natürlich alles anzeigen lassen was man möchte, dass ist einer der Vorteile an Excel. Weil ich es ja Übersichtlich haben möchte gehe ich jetzt unten rechts in dem Feld Werte und klicke auf die Summe von …

Und dann auf Feldeigenschaften. Dort klicke ich dann auf Max. weil ich ja von einem Tag nur den Höchsten Wert angezeigt bekommen haben möchte.

Damit die Werte jetzt auch noch im richtigen Format stehen, kann man einmal die Tabelle markieren und rechte Maustaste Zellen formatieren und dann im Feld Benutzerdefiniert KW eintragen.

Jetzt soll ja noch die Grafik schön werden. Dafür einmal ein Rechtsklick auf die Grafik und dann den Diagrammtyp auf Linie ändern.

Dadurch das nicht immer an jeden Tag alle Geräte an waren gibt es unschöne Lücken. Um diese zu beseitigen muss man einen Rechtsklick auf ein Line machen und dann auf Daten auswählen.

Danach unten links auf Ausgeblendete und leere Zellen klicken.
Hier dann Datenpunkte mit einer Line verbinden auswählen.

Jetzt werden die Lienen schön Dargestellt.

Damit die Daten auch immer Aktuell sind muss man beide Tabellen aktualisieren mittels Rechtsklick.

Mit der App von Excel kann wird auch die Grafik angezeigt und man kann Filter setzen wie man möchte. Leider kann man die Daten nicht aktualisieren. Dies geht nur am PC.

Jetzt ist jeder selbst gefragt wie er es gestaltet haben möchte.

Vielleicht habt ihr ja noch ein Paar Ideen wie man es noch erweitern kann. Als nächstes werde ich mal meinen Tabellen mit ein paar Formeln versehen, für zum Beispiel Verbrauch von XXX oder Durchschnittstemperatur etc. in einem bestimmen Zeitraum.

Ich freue mich auf eure Vorschläge und mögliche Verbesserungen.


#2

Versuch das selbe mal mit PowerBI von MS, die Anleitung wirst wahrscheinlich 1:1 hernehmen können, weil auch in PowerBI die Datentransformation ähnlich funktioniert. Du hast halt in PowerBI mehr möglichkeiten mit Datenschnitten, Diagrammtypen, usw…

Hab leider noch keine Zeit eine Anleitung zu schreiben, mein 50-Stunden-Job + Studium füllt mich derzeit zu sehr aus :wink:


#3

Mit Power BI und Power Query läßt sich auch eine sehr schöne Weblösung bauen, die sich mehrfach täglich automatisch aktualisiert über kostenlose Services von Microsoft. Auch mit Power BI auf dem Tablet oder dem Smartphone lassen sich damit Verbräuche, Soll- und Ist-Temperaturen, Lichtschaltungen, Meldungen von Sensoren und vieles mehr sehr gut darstellen ohne dass größere Programmierkenntnisse notwendig sind.

Der Nachteil ist, dass der Abruf recht aufwändig ist, da immer alle Daten eingelesen werden. Mein Tipp: Reduziert für Power-Query den Abruf zB auf die letzten 30 Tage, bevor die Log-Dateien in das Datenmodell eingelesen werden.

Natürlich ist es mit einer Datenbank und ggf einer eigenen API noch viel viel hünscher zB mit AM Charts, aber für den Nicht-Programmierer kann ich Power Query nur empfehlen. Angenehm ist, dass du gar. icht viele Charts anlegen muss, sondern mit Slicern und Filtern jeweils die Kategorie und die Räume auswählst und dann dazu einen Chart und/oder Tabelle angezeigt bekommst. Ein kleines Beispiel füge ich gerne mal bei.


#4

Das hört sich auch nicht schlecht an. Benutzt du dafür dann die Power BI Desktop ?
Wie bekomme ich dann die Verbindung mit einer app hin? Was ich so gelesen habe, das man sich da registrieren muss mit einem Geschäftskonto anmelden , oder wie meinst du das ?


#5

Ich benutze die Cloudapp in der kostenlosen Version. Auf die Daten ruft sie über einen Webservice meines NAS zu. So kann ich jederzeit mobil auf die aktuellen Daten zugreifen.


#6

mehr auf
https://powerbi.microsoft.com/de-de/desktop/


#7

ah okay. Ich realisiere mein NAS über die Fritzbox. Geht es damit auch ?
@Kobold Ich habe es schon installiert und auch schon mit Daten gefüttert :slight_smile: . Ich weiß leider nur nicht wie ich Daten aufs Handy bekomme.


#8
  1. Lade dein Power BI hoch in der Desktopversion.
  2. Unter /Einstellungen/Datasets/Geplante Aktualisierung hinterlegst du deine Gatewaydaten (diese verweisen auf die http:// Dateifreigabe deines NAS) und hinterlegst die Uhrzeiten für die Aktualisierung.
  3. Schritt 1: Du erstellst in der Cloudapp eine Freigabe unter dem Punkte /Datei/Im Web veröffentlichen.
  4. Jetzt rufst du den Link mit dem Tablet oder dem Smartphone auf oder alternativ mit der PowerBI App für iOS oder Android.

.54


#9

Leider sieht es bei mir komplett anders aus .


Welche Cloudapp meinst du ?
Wenn ich da irgendwas veröffentlichen möchte muss ich mich vorher anmelden/registrieren. Dafür brauche ich dann eine geschäftliche Emailadresse. Oder mache ich da irgendwas falsch ?