Mittels Power Query (immer) aktuelle Daten aus dem Netz, Auswertung (auch) mit PivotChart
Im ersten Teil dieser kleinen Einführung in Power Query haben Sie mit der 2013er-Version des Excel aus dem Internet (Web) Daten der deutschen Groß- und Mittelstädte importiert. Das Begleit-Video des ersten Teils wurde mit der 2016er-Version aufgezeichnet. Nicht nur aus diesem Grunde folgt hier zum Einstieg eine Kurzbeschreibung dessen, was in der 2016er Version grundlegend anders ist.
Excel 2016, nicht nur andere Begrifflichkeiten
Das Wichtigste für Anwender gleich zu Beginn: Power Query ist ein fester Bestandteil des Excel 2016 und es heißt (zumindest offiziell) nicht mehr Power Query. Eine gesonderte Installation eines Plug-In ist nicht mehr erforderlich. Diese Funktionalität finden Sie im Menü Daten, Gruppe Abrufen und transformieren. Und dieser Gruppen-Name ist auch die offizielle Bezeichnung seitens Microsoft. Na ja, die haben mitunter sehr eigenwillige Namensumbenennungen (Liste wird zu Tabelle, damit man ja nicht erkennen kann, ob das Tabellenblatt oder die Liste gemeint ist) …
Aktivieren Sie also bitte das Menü Daten. Auch wenn es verführerisch ist: Nein, es ist nicht der erste Button Externe Daten abrufen, das ist eine andere „Baustelle”, obwohl die gleiche Funktionalität wie beim gleichnamigen Menüpunkt in Excel 2013, Menü Daten gegeben ist. 😉 Übrigens: Wir bleiben bei der klassischen Bezeichnung Power Query und verwenden nicht den Gruppen-Namen, der uns einfach zu sperrig ist. Der englische Original-Name Get & Transform ist da etwas aussagekräftiger, griffiger und treffender.
Das Menü-Band des Daten-Menüs
Sie wollen eine neue Abfrage erstellen …
Neue Abfrage erstellen
… also Klicken Sie auf diese Schaltfläche:
Die Daten kommen aus dem Web
Und ab hier ist alles praktisch genau so, wie in der 2013er Version. Sie werden nach der URL gefragt, wählen die Tabelle aus, … All das ist hier bereits beschrieben, sogar die Abbildungen stimmen für beide Versionen überein.
So viel erst einmal zum Bereich bzw. Thema Power Query. Wenn Sie den Pivot-Teil überspringen und noch etwas zum Thema Power Query in diesem Beitrag lesen wollen, dann geht es hier direkt dort hin. In kommenden Beiträgen dieser Einstiegs-Serie zeigen wir Ihnen weitere sehr nützliche Handhabungen dieses Abfrage-Tools.
PivotChart aus den erarbeiteten Daten
Im ersten Teil des PQ-Basics (1) haben wir bewusst darauf verzichte, Ihnen eine Mustermappe mit den Ergebnissen zur Verfügung zu stellen. Sie sollten genügend Spielraum für eigene Experimente haben. Nun aber geht es darum, die erarbeiteten Werte in ein Diagramm, ein Chart umzusetzen. Und da halten wir es für hilfreich, wenn Sie die gleichen Zahlen haben wie wir und Ihre Tabellen haben so auch den gleichen Aufbau. Als angenehmen Nebeneffekt können Sie dann auch noch verbuchen, dass Sie Ihr eigenes Werk aus dem ersten Teil mit unserem vergleichen können; einschließlich der Aufgabe, wo die Vorgehensweise nicht beschrieben war. 😉 Sie können und sollten dieses File hier herunterladen.
Wir legen nacheinander mehrere Etappenziele fest (blaue Überschrift), wobei Sie auch einzelne davon gerne auslassen können. Jede Etappe ist in sich abgeschlossen und baut nur auf Grundwissen, nicht jedoch auf der vorherigen auf.
Bevölkerungs-Entwicklung der 5 größten Städte
Gleich zu Beginn stellen wir Ihnen einmal vor, wie das Diagramm nach Abschluss des ersten Schrittes aussehen soll:
So wird das Diagramm aussehen
Sie werden die 5 größten Städte der Bundesrepublik in ihrer Bevölkerungsentwicklung 1970 bis 2015 darstellen. Zugegeben, es ist nur ein grober Überblick, aber ein Anfang. Gehen Sie dazu so vor:
- Öffnen Sie die Datei PowerQuery_Einstieg_01.xlsx und aktivieren Sie erforderlichenfalls das erste Blatt Import-Data.
- Menü Tabellentools | Entwurf, dort in Gruppe Tools den Punkt Mit PivotTable zusammenfassen auswählen.
- Alle Vorgaben ungeändert mit OK übernehmen.
- Falls das Seitenfenster Arbeitsmappenabfragen sichtbar ist, schließen Sie es (Klick auf das Kreuz oben rechts im Seitenfenster).
- Im Seitenfenster PivotTable-Felder …
- Ziehen Sie das Feld Stadt in den Bereich Zeilen.
- Das Feld Rang in Werte
- Die Felder 1970 bis 2015 nacheinander und untereinander in den Bereich Werte ziehen, damit die Reihenfolge auch erhalten bleibt.
- Rechtsklick in C3 (Anzahl von 1970), dort Werte zusammenfassen nach ► und in der Unter-Auswahl Summe auswählen.
- Gehen Sie gleichermaßen in Spalte D:G vor; Spalte H ist ja schon automatisch als Summe berechnet worden; Sie erkennen es an der automatisch erstellten Überschrift.
- Rechtsklick in eine beliebige Stadt, Filter ► | Top 10…
- Die Vorgabe im Dialogfenster scheint vom Prinzip her richtig zu sein, es muss aber noch die Anzahl auf 5 geändert werden, um nur die Top 5 zu erhalten. Dann zum Abschluss OK.
- Aha … 🙄
- In Abwandlung eines Spruchs: Der Mensch denkt, Excel lenkt. 😆 Excel sucht sich natürlich die 5 höchsten Werte aus dem Ranking, und das sind nun einmal die letzen 5 Ränge, die letzten Plätze in dieser Aufstellung. Also werden Sie den Filter dahingehend ändern, dass die untersten 5 Ränge (numerisch gesehen) angezeigt werden.
- Ändern Sie den Namen der Tabelle / des Registers auf Top 5 und verschieben Sie das Register/Tabellenblatt ans Ende nach rechts.
Damit haben Sie erst einmal die Basis geschaffen, um ein oder mehrere Charts (Diagramme) zu erstellen. Weiter geht’s …
- Menü PivotTable-Tools | Analysieren, Gruppe Tools, Klick auf PivotChart.
- Nach dem Klick wird sich solch ein Fenster auftun:
Der Vorschlag ist gut …
- Übernehmen Sie die Vorgabe und unterhalb der Tabelle wird das Diagramm eingefügt.
- Wenn Sie genau hinsehen werden Sie erkennen, dass noch zwei Unterschiede zu der Vorgabe existieren:
- In der Legende ist der oberste Punkt Summe von Rang, der in der Vorgabe nicht existiert. Markieren und Entf, das war’s auch schon.
- Und auch in der Legende als auch den Schaltflächen steht hier Summe von statt Einwohner. Dazu markieren Sie in der Tabelle C3:H3 und ersetzen in allen markierten Feldern (StrgH) Summe von durch Einwohner.
Das war es dann auch schon, das Ziel ist erreicht.
Wenn Sie Lust haben, können Sie ja die Reihenfolge der Darstellung ändern. Derzeit ist das ja nach Alphabet des Städtenamens. Wenn es dann aber unbedingt nach Größe der Stadt sein soll, wird es etwas komplexer…:
- Ziehen Sie das Feld Rang in den Bereich Zeilen, es muss oberhalb Stadt stehen, also an erster Stelle.
- Klicken Sie in C3 auf die Schaltfläche mit dem Filter
und in der Unter-Auswahl Wertefilter ► und dann auf den Punkt Kleiner oder gleich… - Im Dialogfenster noch einmal darauf achten, dass Rang ausgewählt wurde und dann die 5 für die Anzahl der Städte eintragen.
- Vielleicht werden Sie noch die eine oder andere Nachbesserung im Layout vornehmen, aber das Prinzip sollte klar sein.
- Probieren Sie auch gerne einmal eine 3D-Einstellung mit hintereinander gestellten Säulen. das kann recht eindrucksvoll sein.
Eindrucksvolle 3‑D Darstellung
Einwohner-Verteilung der jeweils größten Stadt eines Landes auf der Basis der durchschnittlichen Bevölkerung.
Ausgangsbasis ist das Blatt Import-Data. Da noch keine Werte für den Durchschnitt existieren, muss eine neue Spalte erstellt werden. Um aber für spätere (auch selbst gestellte) Aufgaben immer die gleiche Daten-Basis zu haben, kopieren Sie bitte das aktuelle Tabellenblatt. Im neu erstellten Tabellenblatt Klick in die Daten, StrgA, um die gesamte Tabelle zu markieren. Dann Tabellentools | Entwurf und in der Gruppe Eigenschaften ändern Sie den Namen der Tabelle auf Einwohnerzahlen_2.
Ob Sie nun im Original oder der Tabellenkopie weiter arbeiten, das bleibt Ihnen überlassen. Um den Durchschnitt zu berechnen, schreiben Sie in J1 die Überschrift Durchschnitt und in J2 folgende Formel:
=RUNDEN(MITTELWERT(Einwohnerzahlen_2[@[1970]:[2015]]);0)
Das geht am besten, wenn Sie so oft wie möglich die Bezugs-Zelle mit der Maus anklicken. Dann werden die Überschriften der Spalten von alleine in die Formel übernommen. Der Vorteil einer Intelligenten Tabelle wird wieder einmal deutlich, alle Zeilen der Spalte werden automatisch mit der Formel ausgefüllt und berechnet.
Daraus erstellen Sie auf beliebige Weise eine Pivot Tabelle. Idealerweise benennen Sie das Register gleich um, wir haben den Namen Top 1 mit Land verwendet, da ja von jedem Bundesland die einwohnerstärkste Stadt gewertet werden soll.
Die Füllung der Tabelle, die Auswertung gestaltet sich recht schlank: Nacheinander die Felder Bundesland und Stadt in den Bereich Zeilen ziehen, Durchschnitt in Werte. Dann markieren Sie eine beliebige Stadt, Rechtsklick und Filter | Top 10. Im Dialogfenster machen Sie per Eintrag die obersten 1 daraus, um jeweils die größte Stadt des Bundeslandes zu filtern. Sie können die Ansicht so lassen oder das Berichtslayout auf Tabellenformat setzen und die Teilergebnisse nicht anzeigen lassen. Das sieht vielleicht etwas gefälliger aus, hat aber keinen Einfluss auf das zu erstellende Diagramm.
Der Menüpunkt Analysieren sollte aktiviert sein, dort in der Gruppe Tools ein Mal auf PivotChart Klicken. Als Typ wählen Sie links den Kreis und als Unter-Typ oben den 3D-Kreis, dann OK. Umgehend wird ein durchaus ansehnliches 3D-Diagramm auf der gleichen Seite eingefügt.
Was und und gewiss auch Ihnen nicht so sehr daran gefällt: Die Schaltflächen, der Text der Überschrift und dass dort nur drei Städte in der Legende aufgeführt sind. Das leichteste ist die Überschrift, der Titel: Markieren, Doppelklick in den Text und ändern. Wir haben dort Größte Städte der Bundesländer hinein geschrieben. Und auch die grauen Schaltflächen lassen sich problemlos entfernen: Klick in das Diagramm, Rechtsklick auf eine Schaltfläche und dann Alle Feldschaltflächen im Diagramm ausblenden wählen. Und Tschüss … 😎
Eigentlich würde es reichen, wenn Sie das über der Tabelle liegende PivotChart so weit größer ziehen, bis auch Thüringen Erfurt als letzter Eintrag zu sehen ist. Oder aber Sie wollen Ihrem Spieltrieb 😆 freien Lauf lassen und etwas mehr „Pep” in die Darstellung bringen. Da brauchen Sie mehr Platz. Eine Möglichkeit: Einen Klick auf den Rand des Diagramms, dann auch dort ein Rechtsklick. Im Kontextmenü Diagramm verschieben… und als Ziel ein Neues Blatt wählen. Wenn Ihnen das zu „fummelig” ist ( das können wir verstehen), dann wählen Sie im nun sichtbaren Menü PivotChart-Tools | Entwurf in der Gruppe Ort (ganz rechts) den Punkt Diagramm verschieben. Das Ergebnis sieht doch schon viel eindrucksvoller aus, oder nicht?
3D-Diagramm der größten Städte der Bundesländer
Wenn Sie nun in den PivotChart-Tools (Menü) das Untermenü Entwurf aktivieren, dann können Sie nach Herzenslust in den Diagrammvorlagen stöbern und eine Ansicht auswählen, die Ihnen gefällt. – Das Chart hierüber ist übrigens mit einer Diagrammvorlage erstellt, in der Schriftfarbe und einigen kleineren Einstellungen nachbearbeitet worden.
Zusatzaufgabe (ohne Lösung)
Fast immer sind die größten Städte auch die Landes-Hauptstädte. Aber es gibt Ausreißer. Hessen stimmt natürlich nicht, genau so wenig wie Mecklenburg-Vorpommern, Nordrhein-Westfalen und Sachsen. Hier dürfen Sie erst einmal erkunden, welches die Hauptstädte sind und dann würden wir eine Hilfsspalte mit Wahrheitswerten in der primären Tabelle anlegen. Aber das ist schon fast zu viel verraten … 😎
Es geht auch ohne Hilfsspalte …
Hinweis:
Dieses ist eine Leseprobe ohne den (entscheidenden) Lösungsweg. → BONUS
[Überspringen]
Es geht auch ohne Hilfsspalte. Aber natürlich nicht, was die Hauptstadt-Frage angeht. Da fehlen Excel einfach die Informationen. Aber innerhalb der PivotTable lassen sich die Mittelwerte sehr schön direkt berechnen. Wie so oft im Leben: Es ist eine Frage des „Gewusst wie”. Die Vorgehensweise ist so, wie sie schon mehrfach geübt worden ist:
- Aktivieren Sie das Arbeitsblatt mit den Basis-Daten.
- Erstellen Sie auf beliebigem Wege eine PivotTable, allerdings möglichst auf einem getrennten Blatt (wie es auch vorgeschlagen wird).
- Ziehen Sie zuerst das Feld Bundesland und danach das Feld Stadt in den Bereich Zeilen.
- Ziehen Sie nun auch jedes der Jahre in den Bereich Zeilen, beginnend in 1970 und endend in 2015.
- Hier fehlt etwas (Leseprobe) → BONUS
- Damit werden in Spalte I die prozentualen Anteile berechnet, ohne dass in den Basis-Daten eine Veränderung vorgenommen werden muss.
- Bleibt eigentlich nur noch, die Spalte sinnvoll umzubenennen, damit keine Irritationen entstehen können. Wir haben %-Anteil in Zelle I3 eingetragen.
PQ: Prozent der Gesamtbevölkerung Deutschlands in den 100 größten Städten Stand 2015
Die Frage ist eigentlich ganz simpel: Wie hoch war der Prozentsatz der 100 größten Städte, Stand Anfang des 4. Quartals 2015? Aber dazu fehlt Ihnen natürlich das Zahlenwerk, die aktuelle Aufstellung, wo die Werte erfasst sind. Da bemühen wir ausnahmsweise einmal nicht die EU sondern das statistische Bundesamt. Eine recht lange URL, darum hier der Link. Rufen Sie die Seite auf, machen Sie sich ein Bild, welche Tabelle Sie brauchen und kopieren Sie dann die URL in die Zwischenablage.
Sie werden es ahnen, dass am besten Power Query eingesetzt werden soll. OK, Sie könnten sich diese 81 Millionen-Zahl kopieren, aber wo bleibt da der Lerneffekt? Schauen Sie sich bitte erst einmal die Zahlen in der Website an. Beim ersten Import war ja der falsche Dezimaltrenner für den ersten Fehl-Import verantwortlich. Hier aber ist es ganz klar ein Komma. Also alles gut? Wirklich alles gut? Wenn Sie genau hinsehen, werden Sie erkennen, dass statt des Tausenderpunkts ein Leerzeichen da ist. Und das bedeutet unweigerlich, dass die Felder als Text importiert werden. Dennoch, einen Versuch ist es Wert … Und noch eine Anmerkung: Eigentlich brauchen Sie ja nur eine einzige Zelle, und die könnten Sie mit etwas weniger Aufwand extrahieren oder nachbearbeiten. Aber Sie werden von dem etwas mehr an Aufwand profitieren, denn Sie wissen bestimmt: „Nur Übung macht den Meister”. 💡
Wie gehabt gehen Sie entweder über den Menüpunkt Power Query (Excel 2013) oder Daten (Excel 2016). Für 2013 gilt, dass Sie in der Gruppe Externe Daten abrufen den Punkt Aus dem Web anklicken, in 2016 natürlich Neue Abfrage | Aus anderen Quellen | Aus dem Web. Ab hier geht es fast identisch weiter: Füllen Sie die URL in das Textfeld ein, in 2013 geht es sofort weiter mit dem Verbinden, in 2016 werden Sie wahrscheinlich noch gefragt, wie Sie verbinden wollen. Wir plädieren in dem Fall dringend zu Anonym. Danach wird auch hier verbunden. Sie werden erkennen, dass der erste Eintrag der richtige ist. Und in angemessener Geschwindigkeit werden die Daten aus dem Netz gezogen und als fertige Liste angezeigt.
Jetzt könnten Sie hier in dieser Liste alle Leerzeichen im Datenbereich durch nichts ersetzen. Aber das wäre nun nicht wirklich Power Query-like, denn immer dann, wenn Sie die Daten aktualisieren, hätten Sie die Lücken wieder in den Ergebnis-Listen. Also gehen wir an die Quelle (zumindest in Excel) und löschen da die Leerzeichen in den Zahlen.
Menü TabellenTools | Abfrage, Gruppe Bearbeiten und dort Abfrage bearbeiten. Zweite bis vierte Spalte markieren, Menü Start, Gruppe Transformieren, Auswahl Werte ersetzen. Zu suchender Wert ist natürlich ein Leerzeichen, und Ersetzen durch bleibt naturgemäß leer. Ruck zuck sind die Leerzeichen bei den Zahlen verschwunden. Aber nur bei den ersten beiden der markierten Spalten. In der letzten Spalte sind die Leerstellen immer noch in den Zahlen. Auch wenn Sie es noch einmal versuchen sollten, es bleibt dabei. Und das ärgerliche: Genau aus der letzten Spalte brauchen Sie die Zahl!
Solche Situationen können einen wirklich zur Verzweifelung treiben. Es ist auch wirklich nicht schön, dass innerhalb einer Tabelle verschiedene Methoden der visuellen Trennung in einer Ziffernfolge verwendet werden. – In solchen Situationen sollten Sie immer daran denken, dass manche Programme statt eines (normalen) Leerzeichens das geschützte Leerzeichen verwenden. Markieren Sie also noch einmal die vierte Spalte und geben Sie in das obere Textfeld das geschützte Leerzeichen ein. Das erreichen Sie, indem Sie die Taste Alt drücken und dann auf der Zehner-Tastatur (der Nummernblock rechts) nacheinander 0160 eingeben, dann Alt loslassen. Dann klappt es auch mit dem Ersetzen. 🙂
Die Zahlen sind aber alle noch linksbündig. Und ein Blick in Start, Gruppe Transformieren zeigt auch: Datentyp: Text. Also wieder die drei Spalten markieren und den Datentyp auf Dezimalzahl ändern. Der Erfolg ist sofort sichtbar. Bleibt prinzipiell nur noch, eventuell in den Überschriften die „1 000” zu entfernen. Ein Punkt bleibt eigentlich noch: Die eben gelöschten „ 1 000” bedeuteten ja, dass die Werte mit 1.000 multipliziert werden müssen, um zu den korrekten Ergebnissen zu kommen. Für Einsteiger, aber auch erfahrenere User ist da in Power Query nichts zu machen. Es bedarf professioneller Kenntnisse, um direkt in PQ diese Umrechnung vorzunehmen. Aber: Einmal eingerichtet, funktioniert das so lange, wie die Datenquelle im Web den gleichen Aufbau und die gleiche URL hat. Diese Arbeit bleibt erst einmal den Folgeschritten in der erstellten Tabelle vorbehalten.
Jetzt noch Schließen und laden anklicken und das Ergebnis sieht schon anders aus. Irgendwo sollte natürlich noch der Vermerk stehen, dass es sich um Tausender-Werte handelt. Alternativ könnte zwar ein Einfügen mit Multiplikation die korrekten Werte zeigen, aber die bessere Idee wäre, wiederum in PQ die Überschrift in der ersten Spalte so zu verändern: Bevölkerungsstand in Tsd. oder Bevölkerungsstand ‰ (Das Zeichen erreichen Sie über Alt+0137, Ziffern auf der Zehnertatstaur).
Wie schon erwähnt, Sie brauchen ja nur die eine einzige Zelle als Referenz: D2. Die würden Sie als Bezug verwenden und mit 1.000 multiplizieren. Natürlich hätten Sie auch durch löschen von den ersten 3 Spalten und allen Zeilen ab der zweiten erreichen können, dass nur die Überschrift und der gewünschte Wert ausgegeben werden. Damit wäre dann aber die Chance verwirkt, mit derartigen Daten etwas mehr zu experimentieren.
Die Auswertung an sich geschieht entweder auf die gleiche Art wie oben schon beschrieben, mit PivotTable oder aber durch einen einfachen Filter, welcher ja in der Funktionalität der zu Beginn erzeugten Liste integriert ist. In Stichworten die Vorgehensweise bei der „klassischen” Liste:
- Vergeben Sie für die Zelle mit der Gesamt-Bevölkerung einen Bereichsnamen, beispielsweise EinwohnerGesamt.
- Tragen Sie in J1 die Überschrift Anteil Gesamtbevölkerung ein.
- In J2 kommt dann diese Formel:
=[@2015]/(EinwohnerGesamt*1000) - Optional: Filter in Spalte A setzen, damit nur die ersten 100 Rang-Werte angezeigt werden. Der ist erforderlich, wenn Sie mit der Funktion AGGREGAT() arbeiten.
- In eine beliebige Zelle, beispielsweise M1 schreiben Sie Gesamt-Summe Top 100.
- In der Zelle rechts daneben (N1) berechnen Sie die Einwohner-Summe der 100 größten Städte Deutschlands:
=AGGREGAT(9; 5; H:H)bei Verwendung der AGGREGAT-Funktion oder=SUMMEWENN(A:A; "<=100"; H:H)als klassische Formel.
- Vergeben Sie der Zelle M1 den Bereichsnamen SummeTop100
- Schreiben Sie in K1 die Überschrift Anteil Top 100
- Zelle K2 bekommt diese Formel: =[@2015]/SummeTop100
- Formatieren Sie Spalte J:K als Prozent mit 2 oder drei Nachkommastellen.
Damit ist das Ziel sogar übererfüllt, denn es war ja eigentlich nur die Gesamt-Bevölkerung gefragt. – Auch wenn Power Query nicht immer unbedingt einen direkten Vorteil gebracht hat, in der Einstiegsphase ist solch eine Übung zum Erkennen der Möglichkeiten gut geeignet.
Nachtrag aus Mitte August 2016: Mit einem Update aus den letzten Tagen ist die Möglichkeit gegeben, direkt den prozentualen Anteil zu berechnen. Eine kleine Einschränkung aber ist gegeben: Die Gesamt-Bevölkerung muss in ein Textfeld als Zahl eingegeben/kopiert werden.
| Übersicht der Beiträge Power Query Einstieg | |
| Lerneinheit 1 (1) | Web-Abfragen mit Power Query – Teil 1 |
| Lerneinheit 1 (2) | Web-Abfragen mit Power Query – Teil 2 |
| Lerneinheit 2 | Grundlegende Menü-Elemente Kurzreferenz |
| Lerneinheit 3 | Filtern und teilen |
| Lerneinheit 4 | Text-basierte Files importieren |
Bundesweite ✉ Schulungen ✉ durch unseren Sponsor GMG Computer-Consulting