Pow­er Query (PQ) ist ja die (logis­che) Weit­er­en­twick­lung aber auch Zusam­men­führung ver­schieden­er Funk­tion­al­itäten aus den älteren Ver­sio­nen dieses Kalku­la­tion­spro­gramms. In den Excel-Ver­sio­nen 2010 und 2013 wer­den Sie PQ über die Microsoft-Web­site down­load­en und instal­lieren, mehr dazu find­en Sie hier in diesem Blog. Ab Ver­sion 2016 ist PQ voll inte­gri­ert und Sie find­en die Funk­tion­al­itäten im Menü Dat­en; die „klas­sis­che” Ver­sion bei der Auswahl Externe Dat­en abrufen und die Pow­er Query-Vari­ante im gle­ichen Menü in der Gruppe Abrufen und trans­formieren | Neue Abfrage | Aus anderen Quellen. – Die meis­ten Vorge­hensweisen und Abbil­dun­gen beziehen sich auf Excel 2013, wenn es für das Ver­ständ­nis wichtig ist, wer­den wir jew­eils als Zusatz einen Abschnitt für die 2016^er Ver­sion ein­fü­gen. – Das alles gilt nur für die Win­dows-Ver­sio­nen des Excel, Mac-Jünger sind hier von der Pow­er Query-Welt aus­geschlossen, auch in der 2016^er Ver­sion für den Mac gibt es kein PQ. 🙁 

In diesem Beitrag stellen wir Ihnen zwei Auf­gaben und natür­lich deren Lösun­gen vor. Sie wer­den zuerst die Dat­en aus ein­er Web­site importieren und anschließend eine Auswer­tung mit Piv­ot­Table (im kom­menden Teil auch mit Piv­otChart) vornehmen, was Excel so richtig attrak­tiv macht.  😉 

▲ nach oben …

Voraussetzungen

Prinzip­iell gibt es für ein „vernün­ftiges” Arbeit­en im Bere­ich Web-Abfra­gen mit Pow­er Query nur drei Bedin­gun­gen: Die Adresse der Ziel-Web­seite (also dort, wo auch die zu importieren­den Dat­en ste­hen, die URL) ist immer gle­ich, die Dat­en liegen als HTML-Tabelle vor und der interne Name der zu importieren­den Tabelle (auf der Web-Site) wech­selt auch nicht. Ser­iöse, benutzer­fre­undliche Web­seit­en hal­ten sich auch an diesen Stan­dard, davon kön­nen Sie aus­ge­hen.

Eines allerd­ings soll­ten Sie beacht­en: Nicht alles, was (im Netz) wie eine Tabelle aussieht, ist auch eine (echte) Tabelle. Es gab Zeit­en, wo HTML-Tabellen ver­teufelt wur­den und im (Über-) „Eifer des Gefechts” oder falschem, puris­tis­chem Denken wer­den Tabellen mit anderen Möglichkeit­en (CSS) for­matiert. Das ist aber zwis­chen­zeitlich kaum noch anzutr­e­f­fen, es hat sich durchge­set­zt, dass tabel­lar­ische Dat­en auch in einen Tabelle gehören. Wenn Sie also ein­mal Dat­en, die wie eine Tabelle ausse­hen nicht zum Import ange­boten bekom­men, dann liegt das unter Umstän­den an dieser Art der For­matierung.

▲ nach oben …

Die 10 größten Städte der Bundesländer (Deutschland)

Zum Ein­stieg eine ein­fach erscheinende Auf­gabe: In Excel sollen in ein­er getren­nten Tabelle die 10 größten Städte der Flächen­län­der aus­gegeben wer­den. Die Anord­nung (Sortierung) und eventuelle weit­ere Ein­schränkung (beispiel­sweise mehr als 200.000 Ein­wohn­er) soll möglich sein.

Zugegeben, solch eine Abfrage der Dat­en wird nicht all zu oft durchge­führt wer­den, vielle­icht ein Mal jährlich, wenn die Werte im Netz aktu­al­isiert wor­den sind. Oder sog­ar noch sel­tener. Aber in diesem ersten Teil ste­ht ein­er­seits das Prinzip sowie die Über­sichtlichkeit der Dat­en im Vorder­grund und ander­er­seits das Zusam­men­spiel mit Piv­ot­Table.

Dass die Dat­en aus dem Inter­net kom­men, das wird Ihnen schon klar sein. Sie selb­st wer­den vielle­icht über eine Such­mas­chine Ihrer Wahl erkun­den, welche Seite für Sie am inter­es­san­testen ist. Wir haben für diesen Beitrag eine spon­tane, gefühlsmäßige Entschei­dung getrof­fen, die aber kein­er­lei Wer­tung darstellen soll.

▲ nach oben …

Der Import

Sie haben (natür­lich) Pow­er Query bere­its instal­liert und sehen auch diesen Menüpunkt oder set­zen bere­its die Ver­sion 2016 ein. Zweck­mäßiger­weise (aber nicht notwendi­ger­weise) sehen Sie ein leeres Arbeits­blatt auf dem Schirm. Ein Klick auf den Menüpunkt Pow­er Query und erwartungs­gemäß passt sich das Menüband (Rib­bon-Leiste) den Gegeben­heit­en an:

Das Menü-Band des Pow­er Query

Gle­ich die erste Gruppe ist schon passend, ja sog­ar das erste Sym­bol, denn es sollen ja Dat­en aus dem Web abge­fragt wer­den. Ein Klick darauf und Sie wer­den nach der Web-Adresse, der URL gefragt:

Bitte den Link eingeben …

Falls Sie gehofft haben, dass Sie hier eine Such­möglichkeit wie in einem Inter­net-Brows­er haben, wer­den Sie gewiss ent­täuscht sein. In einem eige­nen Pro­jekt ist es wahrschein­lich, dass Sie in dieser Sit­u­a­tion Ihren Lieblings-Brows­er öff­nen und per Stichwort(en) auf die Suche nach ein­er geeigneten Seite gehen. Wenn Sie fündig gewor­den sind, Sie also auf der Seite die Sie inter­essieren­den Dat­en sehen, dann kopieren Sie den kom­plet­ten Link aus der Adresszeile (Tipp: hinein Klick­en, StrgA, StrgC) und fügen den Inhalt der Zwis­chen­ablage nach einem Wech­sel zurück nach Excel in das Feld des Dialogfen­sters per StrgV ein. In dieser Auf­gabe haben wir Ihnen die Entschei­dung abgenom­men und diese Seite gewählt:

de.wikipedia.org/wiki/Liste_der_Groß-_und_Mittelstädte_in_Deutschland

Hin­weis: Die Umlaute sind kein Schreibfehler, Sie kön­nen den Link per Hand genau so eingeben. Und stören Sie sich bitte nicht daran, dass nach dem Ein­fü­gen aus der Zwis­chen­ablage ver­mut­lich statt der deutschen Son­derze­ichen kryp­tis­che Zeichenkom­bi­na­tio­nen dort ste­hen, das ist dann richtig so.

Nach einem Klick auf OK wer­den Ihnen in einem neuen Fen­ster im linken Bere­ich alle Tabellen angezeigt, welche sich für einen Import eignen. Pro­bieren Sie gerne ein­mal alle Optio­nen, im recht­en Teil des Nav­i­ga­tors sehen Sie eine Vorschau der Dat­en als Hil­fe für Ihre Entschei­dung. Hier wäre die dritte Möglichkeit genau das, was Sie suchen:

Auswahl der zu importieren­den Tabelle

Sie kön­nten nun ein­fach auf Laden Klick­en, dann wird Excel ein neues Arbeits­blatt anle­gen und dort in A1 die Dat­en platzieren. Oder aber Sie Klick­en auf den Drop­Down-Pfeil ▼ direkt daneben und wählen dann im Kon­textmenü den zweit­en Punkt, Laden in… Damit kön­nen Sie erre­ichen, dass die Dat­en dort abgelegt wer­den, wo Sie es wollen:

Den Zielort des Imports selb­st bes­tim­men

Jet­zt Laden und die Dat­en sind ruck zuck im gewün­scht­en Arbeits­blatt an der definierten Posi­tion erfasst. Selb­stver­ständlich als Intel­li­gente Tabelle/Liste und die Spal­tenbre­it­en sind auch nicht Stan­dard son­dern den Inhal­ten entsprechend angepasst:

Die importierten Dat­en im Arbeits­blatt

Rechts neben dem eigentlichen Sheet sehen Sie eine Schalt­fläche, welche auf einen Dop­pelk­lick in die eigentliche Abfrage verzweigt und diese öffnet. Das ist im Moment nicht der entschei­dende Punkt, viel wichtiger ist ein klein­er Hin­weis:

Hin­weis auf 68 Fehler in der Abfrage

680 Zeilen wur­den importiert und dabei wur­den 68 (mögliche) Fehler erkan­nt. Solch ein Hin­weis ist aus unser­er Sicht sehr wertvoll, denn wir sind es ja gewohnt, dass ein Import von Dat­en rei­bungs- und fehler­los von­stat­ten geht.

▲ nach oben …

Wenn Sie mit Forschungs­drang belegt sind, dann machen Sie doch ein­fach ein­mal den fol­gen­den Test: Markieren und kopieren Sie die Tabelle der Web­site und füllen Sie den Inhalt der Zwis­chen­ablage in ein leeres Work­Sheet ein­er beliebi­gen Mappe. Es kommt garantiert kein Fehler. – Und wo ist der Unter­schied? Sehen Sie sich ein­fach ein­mal den Rang 207, also die Stadt Her­zo­gen­rath in bei­den Tabellen an, dann wer­den Sie gewiss erah­nen, warum PQ da „meck­ert”.  💡 

Zurück zur Abfrage-Schalt­fläche. Der Hin­weis auf die 68 Fehler sieht nicht nur aus, wie ein Link, es ist ein­er. Also mutig einen Klick darauf und Sie wer­den sofort erken­nen, dass in der Liste nur die als fehler­haft definierten Zeilen gefiltert wor­den sind:

Die gefilterten, fehler­haften Zeilen

Klar und deut­lich die Kennze­ich­nung Error. Min­destens ein Mal in jed­er der sicht­baren Zeilen. Wenn Sie sich nun eine oder mehrere der Indizes merken und in der Ergeb­nista­belle nach­se­hen, warum dort ein Fehler ist, wer­den Sie auch nicht klüger. Die entsprechen­den Zellen sind ein­fach leer. Was uns beim ersten Mal gewun­dert, eigentlich sog­ar geärg­ert hat, ist im Sinne der „Daten­hy­giene” logisch und gut. – Obwohl jed­er Ver­gle­ich „hinkt”, will ich Ihnen zeigen, was ich meine: Wenn ich in ein­er Stadt nach einem Weg frage und als Antwort ein „Weiß ich nicht” bekomme, dann erwidere ich grund­sät­zlich: „Danke, bess­er so als falsch”. Hier gilt: Bess­er leer, als falsch. Und wenn Sie in den Ursprungs­dat­en nach­se­hen, kom­men Sie rasch hin­ter die Ursache: Über­all sind in den Daten­zeilen Zahlen, in den monierten Zellen sind zwar Zif­fern aber durch die Zusätze ist das Text. Und da es in der EDV eine ganz klare Regel gibt, dass in ein­er Spalte ein­er Liste immer der gle­iche Daten­typ zu sein hat, ist das ein Fehler in der Daten­struk­tur der Tabelle im Netz.

Einen wirk­lich leicht­en Ausweg sehen wir nicht. Das ein­fach­ste: Ein­fach eine andere Quelle für die benötigten Dat­en suchen. Wenn das nicht so leicht oder auch ergeb­nis­los ist: Beim Ersteller der Dat­en anfra­gen, ob es irgend­wann ein­mal eine Liste gibt, wo die (hoch gestell­ten) ^{Anmerkun­gen} in ein­er getren­nten Spalte sein wer­den (was ja auch der Norm entsprechen würde). Von Hand kopieren und ein­fü­gen oder gar selb­st ein­tip­pen, nein danke, das muss nun wirk­lich nicht sein … Oder die Tabelle (wie oben schon ein­mal beschrieben) im Orig­i­nal kopieren und per Hand ein­fü­gen, das wäre halb­herzig aber immer­hin würde es zu ver­ar­beit­baren Lösun­gen führen, wenn Sie die Hin­weis­texte kom­plett (ein­schließlich Leerze­ichen) ent­fer­nen.

Aber es gibt auch noch einen gang­baren Ausweg, der zwar ein­ma­lig etwas Aufwand bedarf, aber im Hin­blick darauf, dass diese Vorgänge aufgeze­ich­net wer­den und bei der näch­sten Aktu­al­isierung der Dat­en automa­tisiert ablaufen, mag das ver­hält­nis­mäßig sein. Bedenken Sie, dass der hier aufgezeigte Weg speziell für diese Basis-Dat­en gilt. Bei anderen Voraus­set­zun­gen sind Sie sel­ber gefragt, eine äquiv­a­lente Lösung zu find­en (oder Sie fra­gen ein­mal bei uns an, wir leit­en das dann nach Ein­hol­ung Ihres Ein­ver­ständ­niss­es zu unserem Spon­sor GMG Com­put­er-Con­sult­ing weit­er. Diese Schritte führen zum Ziel:

• Öff­nen Sie erst ein­mal den Fehler-Bericht, indem Sie auf den Link 68 Fehler Klick­en (siehe oben).
• Prüfen Sie, welch­es die erste und welch­es die let­zte Spalte mit dem Error ist. Prinzip­iell reicht es auch, wenn Sie sich nur jene Spal­ten merken, wo dieser Fehler auftritt. Merken Sie sich in jedem Fall die Über­schriften.

▲ nach oben …

Im Nor­mal­fall wird Ihnen so etwas nicht wider­fahren. Aber hier ist es nun ein­mal so, und Sie müssen gemein­sam mit uns das Beste daraus machen. Also „ran an den Speck!”  😆 

• Schließen Sie diese Ansicht und ver­w­er­fen Sie alles. Das geht am besten, wenn Sie auf die grüne Schalt­fläche Datei (in der Menü-Zeile ganz links) Klick­en und dann die Auswahl Ver­w­er­fen und schließen anklick­en.
• Auch wenn es weh tut: Recht­sklick in die Schalt­fläche der Arbeitsmap­pen­abfrage mit den 680 Zeilen, Löschen. – Der Import muss etwas anders gestal­tet wer­den …
• In dem nun leeren wiederum leeren Blatt: Pow­er Query | Aus dem Web | den Link ein­tra­gen und OK.
• Im Nav­i­ga­tor (links) wiederum die dritte Tabelle auswählen.
• Stop! Nicht Laden son­dern Bear­beit­en auswählen.

Das Fen­ster, welch­es sich nun öffnet, scheint sich nicht von dem zu unter­schei­den, welch­es beim ersten Anlauf sicht­bar war. Und wenn Sie bis zur bekan­nten Zeile 207 herunter scrollen, dann wer­den Sie den „Alten Bekan­nten” wieder sehen:

Auch hier ist der Fehler vorhan­den

Aber es gib den­noch einen Unter­schied: Sie kön­nen jet­zt die Dat­en, die ja noch nicht in die Tabelle geschrieben wor­den sind, im Vor­wege bear­beit­en. Unser Vorschlag:

• Menü Trans­formieren, Gruppe Beliebige Spalte.
• Hier bei Daten­typ: Ganze Zahl ▼ auf den Drop­Down-Pfeil Klick­en.
• Wählen Sie Text aus der Liste aus.

Ja, die Zahlen ste­hen nun alle links und sind mit dem Tausendertren­npunkt verse­hen. Aber: Prüfen Sie nun noch ein­mal die Zeile 207 …  💡 

Jet­zt ist der hoch gestellte Text zu erken­nen

Eine der Möglichkeit­en wäre nun, alle ange­hängten Texte, bei Her­zo­gen­rath wäre das ^(a), durch nichts zu erset­zen. Wenn es aber zig ver­schiedene Anmerkun­gen sind, dann artet das in Arbeit aus. Darum haben wir uns einen anderen Weg über­legt.

Was als Merk­mal immer zutrifft ist, dass in der Spalte alles ab (und ein­schließlich) der öff­nen­den run­den Klam­mer weg muss. In Excel selb­st gibt es da ja die Funk­tion­al­ität Text in Spal­ten, die so etwas ele­gant erledigt. Gle­ich­es ist in Pow­er Query möglich:

• Falls nicht schon geschehen, die zu bear­bei­t­ende Spalte durch Klick in die Über­schrift (hier: 1970) markieren.
• Im Menü Start | Gruppe Trans­formieren find­en Sie an erster Stelle das Sym­bol für Spalte teilen, darauf Klick­en Sie.
• Im sich öff­nen­den Unter­menü wählen Sie Nach Trennze­ichen.
• Im kom­menden Dia­log wählen Sie statt des Kom­mas –Benutzerdefiniert– und tra­gen in das Feld darunter die öff­nende, runde Klam­mer ein. Die soll die „Schere” für das abtren­nen sein.
• Beim Punkt Teilen Klick­en Sie auf Beim äußer­sten linken Trennze­ichen, denn es kön­nten ja mehrere einzelne Anmerkun­gen in der Spalte sein. Und es soll ja immer nach der Zif­fer­n­folge kom­plett abgeschnit­ten wer­den.
• Schließen Sie den Vor­gang mit OK ab.

PQ erzeugt eine neue Spalte und benen­nt bei­de Über­schriften um in 1970.1 und 1970.2 wobei Sie in der neuen Spalte fast nur null sehen. Aber in Zeile 207 wer­den Sie sehen, dass die öff­nende Klam­mer her­aus­geschnit­ten wor­den ist und der Rest des vorheri­gen Inhalts, die restlichen bei­den Zeichen der Anmerkung in der zweit­en Spalte ste­hen. Und was ganz wichtig ist: In der linken der bei­den Spal­ten ste­ht nun nur noch die Zahl, sog­ar ohne die Tausender­punk­te.

So weit, so gut. Bleiben eigentlich für die 1970^er Werte noch drei Dinge zu tun: Die erste Spalte sollte in jedem Fall Zahlen und keinen Text enthal­ten, die Über­schrift soll wieder nur 1970 sein; und natür­lich ist zweite Spalte nun über­flüs­sig. Fan­gen Sie mit der zweit­en Spalte (1970.2) an und markieren diese (immer per Klick in die Über­schrift). Recht­sklick in die Über­schrift und den zweit­en Punkt Ent­fer­nen auswählen. Die erste Spalte auswählen und per Recht­sklick Umbe­nen­nen oder direkt die Über­schrift ändern. Zur Kon­trolle die Spalte erforder­lichen­falls noch ein­mal markieren und in das Menü Trans­formieren wech­seln. Ein Kon­troll­blick in Zeile 207 zeigt, dass auch hier eine ganz nor­male Zahl (rechts­bündig) ste­ht. 

Bleiben noch die Jahre 1980 und 1990, die gle­icher­maßen zu kor­rigieren sind. Eine winzige Erle­ichterung: Sie kön­nen bei­de Spal­ten gemein­sam markieren und dann in Text umwan­deln. Alle weit­eren Schritte müssen Sie aber für jede Spalte einzeln durch­führen. 😥 Der Trost der Ihnen bleibt: Diese Aktion ist ein­ma­lig. Wenn Die die Dat­en noch ein­mal brauchen wird Pow­er Query das bish­er gel­ernte automa­tisch in genau der Rei­hen­folge abar­beit­en.

Eine Empfehlung haben wir noch für Sie: Die zweite Spalte hat ja die viel­sagende Über­schrift Name. Benen­nen Sie die Spalte in Stadt um, es lohnt sich in Sachen Klarheit. – Bleibt zum Schluss noch, die Dat­en in ein Sheet zu brin­gen. Dazu ein­fach auf Schliessen und laden Klick­en, fer­tig.

Nach der „Ochsen­tour” nun noch einige kleinen Bon­bons, die haben Sie sich ver­di­ent. 

• Um die Über­schrift zu ändern, entwed­er einen Dop­pelk­lick dort hinein oder F2.
• Mehrere Spal­ten löschen: Zusam­men­hän­gende Spal­ten mit Shift oder einzelne Spal­ten nacheinan­der mit Strg markieren und dann im Menü Start | Spal­ten ver­wal­ten auf Spal­ten ent­fer­nen Klick­en. Und warum auch immer: Die Recht­sklick-Möglichkeit hat bei unseren Ver­suchen teil­weise zu Fehlergeb­nis­sen geführt.
• Sehr viele Funk­tion­al­itäten kön­nen Sie auch per Recht­sklick schneller erre­ichen als über das (nor­male) Menü, wenn die Spalte markiert ist. Dazu gehört auch die Funk­tion­al­ität Spal­ten teilen.

Ein etwas „saur­er” Bon­bon, vielle­icht auch „bit­tere Pille” ist doch dabei: Wenn Sie diesen Weg gehen, kön­nen Sie nicht wählen, wo die Ergeb­nisse abgelegt wer­den. Es wird in jedem Fall ein neues Tabel­len­blatt erstellt. Aber das sollte nicht das große Prob­lem sein, schließlich lassen sich die Reg­is­ter (Blat­tna­men) prob­lem­los umbe­nen­nen.

▲ nach oben …

Auswertung durch PivotTable

Die so extrahierten Dat­en sind ja kein Selb­stzweck, sie kön­nen und sollen sta­tis­tisch aus­gew­ertet wer­den. Und das geht her­vor­ra­gend mit ein­er Piv­ot Tabelle. Wir nutzen hier mal die deutsche, ver­mehrt aber die englis­che Schreib­weise. Microsoft ver­wen­det auch in den deutschen Doku­men­ta­tio­nen bei­de Schreib­weisen, in Excel sel­ber aber ist der Menüpunkt in der englis­chen Form.

▲ nach oben …

Die 10 größten Städte der Flächenländer

Warum nur Flächen­län­der? Nun ja, es macht wenig Sinn, die Stadt­staat­en mit einzubeziehen. Berlin und Ham­burg haben nur 1 Ein­trag, Bre­men  ist 2 Mal im Rank­ing und das ist weit unter der Bew­er­tungs­gren­ze von 10. – Konkretisierung der Über­schrift und somit der Auf­gabe: In ein­er geson­derten Tabelle sollen pro Bun­des­land (Flächen­land) die (bis zu) 10 ein­wohn­er­stärk­sten Städte dargestellt wer­den. Geord­net nach Bun­des­land und nach Ein­wohn­erzahlen, neuester Stand. Machen Sie mal …  😎 

Formel­spezial­is­ten wer­den da mit ein­er ellen­lan­gen Aneinan­der­rei­hung von Funk­tio­nen gewiss etwas Brauch­bares zusam­men brin­gen. Wir meinen aber, dass es da an Flex­i­bil­ität man­gelt und auch die meis­ten Anwen­der kaum eine Chance haben, solch ein Kon­strukt zu ver­ste­hen und eventuell neunen Gegeben­heit­en bzw. Anforderun­gen anpassen zu kön­nen. Darum der Weg über Piv­ot. Sie wer­den die Flex­i­bil­ität schnell zu schätzen wis­sen.

Anhand der abwech­sel­nden Fär­bung der Zeilen in grün | weiß erken­nen Sie, dass es sich wahrschein­lich um eine Intel­li­gente Tabelle/Liste han­delt, welche seit­ens Pow­er Query erzeugt wor­den ist. Und sofern eine beliebige Zelle inner­halb des Daten­bere­ichs markiert (aktiviert) ist, erken­nen Sie auch in der Menüleiste ganz rechts far­blich gelb her­vorge­hoben die Tabel­len­tools. Wenn Sie dort den Menüpunkt Entwurf wählen, find­en Sie in der Gruppe Tools gle­ich als erste Möglichkeit Mit Piv­ot­Table zusam­men­fassen. Ein Klick darauf und das Dialogfen­ster für die Erstel­lung ein­er Piv­ot Tabelle (PT) wird einge­blendet. Wenn Sie schon etwas mit PT ver­traut sind, tre­f­fen Sie hier Ihre Auswahl. Alter­na­tiv schauen Sie gerne hier im Blog nach, um zum The­ma Piv­ot einige Grund­la­gen zu erfahren.

Nochmals zusam­men­fassend: Für jedes Bun­des­land, ausgenom­men Berlin, Ham­burg und Bre­men sollen die 10 größten Städte mit der Ein­wohn­erzahl und dem Rank­ing inner­halb des Lan­des, Stand 2015 (oder das let­zte aufge­führte Jahr) in ein­er Liste aufge­führt wer­den. Dazu ziehen Sie erst ein­mal das Bun­des­land in den Bere­ich Zeilen. Da hier alle 16 Län­der gelis­tet wer­den, Klick­en Sie in Spalte A bei Zeilenbeschrif­tun­gen auf die Schalt­fläche ▼ und ent­fer­nen Sie das Häkchen bei den drei Stadt­staat­en.

Als näch­stes ziehen Sie das Feld Stadt in den Bere­ich Zeilen. Allerd­ings wer­den nun alle Städte aller verbleiben­den Län­der aufge­lis­tet, was ja nicht im Sinne der Auf­gabe ist. Das ignori­eren Sie aber zuerst ein­mal und ziehen die Ein­wohn­erzahlen des Jahres 2015 in den Bere­ich Werte. Da wird nun zwar die Summe berech­net, aber die Summe von 1 Zahl bleibt so wie sie ist. Es kön­nte auch das Min­i­mum, Max­i­mum oder der Mit­tel­w­ert sein, das Ergeb­nis bliebe gle­ich. Das kann also erst ein­mal so bleiben. Markieren Sie im Daten­bere­ich eine beliebige Stadt, Recht­sklick und im Kon­textmenü Fil­ter | Top 10…:

Der erste Schritt zu Top 10 …

Sofort tut sich dieser Dia­log auf:

Die Vor­gaben stim­men in diesem fall

Bin­go, genau richtig! Also OK und das Ergeb­nis begutacht­en. Vom Prinzip her ist das Ergeb­nis kor­rekt. Einige optis­che Änderun­gen, die das Ganze etwas nutzer­fre­undlich­er machen, kön­nten noch vorgenom­men wer­den. So sollte erst ein­mal in A3 die Über­schrift auf Land | Stadt und in B3 auf Jahr 2015 geän­dert wer­den. Und die Sum­mierung der Werte jew­eils in der Zeile mit den Lan­desna­men irri­tiert mehr, als dass sie hil­ft. Das Bun­des­land an sich hat je mehr Ein­wohn­er als die in den Städten aufge­führten Zahlen. Also wählen Sie im Bere­ich Piv­ot­Table-Tools das Reg­is­ter Entwurf und in der Gruppe Lay­out Klick­en Sie auf das Sym­bol Tei­l­ergeb­nisse und dort wiederum auf den ober­sten Punkt Tei­l­ergeb­nisse nicht anzeigen.

▲ nach oben …

Fast per­fekt.  Jet­zt wäre nur noch eine Sortierung inner­halb des Lan­des nach Ein­wohn­erzahl schick. Zuerst im jew­eili­gen Bun­des­land die großen Städte wie München, Frank­furt, Köln und dann die Städte mit weniger Ein­wohn­ern. Klick­en Sie dazu auf die Ein­wohn­erzahl ein­er beliebi­gen Stadt, dann Recht­sklick und im Kon­textmenü Sortieren  | Nach Größe sortieren (absteigend):

„Absteigend”, weil die hohen Zahlen zuerst ste­hen sollen

Das Ergeb­nis wird Sie gewiss von den Vorzü­gen ein­er Piv­ot­Ta­belle überzeu­gen, wenn Sie es noch nicht schon sind. Es gibt noch zwei, drei Kleinigkeit­en, die das Ganze etwas ansprechen­der machen kön­nten: (In Kurz­form…) Entwurf | Gruppe Lay­out | Leere Zeilen | Leerzeile nach jedem Ele­ment ein­fü­gen. Oder auch Analysieren | Gruppe Anzeigen (ganz rechts) | Schalt­flächen +/-, dann ver­schwinden die Minus-Zeichen vor den Lan­desna­men. Ganz schick, falls nur einzelne Län­der ver­glichen wer­den sollen: Analysieren | Daten­schnitt ein­fü­gen | Bun­des­land. Feine Sache!

Richtig pro­fes­sionell: Mit Daten­schnitt

Dass hier die drei Stadt-Län­der mit aufge­führt sind liegt daran, dass sie ja in den Basis­dat­en enthal­ten sind. Sie wer­den aber als nicht aus­gewählt (weißer Hin­ter­grund) dargestellt, da sie ja in der Tabelle gefiltert wor­den sind. Das Fil­ter-Sym­bol in der Titelzeile des Daten­schnitts wurde aus diesem Grunde auch automa­tisch geset­zt. Wenn die drei Stadt­staat­en im Daten­schnitt nicht mit aufge­führt wer­den sollen, dann müssen Sie (sin­nvoller­weise) die Städte in Pow­er Query löschen. Die Basis­dat­en bleiben dabei in vollem Umfang erhal­ten.

Lust auf mehr?

Wenn Sie Spaß an der Sache haben, dann ver­suchen Sie doch ein­mal, die hier abge­bildete Auswer­tung nachzubauen. Die einzige Hil­fe, die wir Ihnen geben: A4:D23 ist durch Bed­ingte For­matierung einge­färbt wor­den. Und in F4:F7 ist rein­er Text, auch wenn es reizvoll (und möglich) wäre, eine solche Darstel­lung per Zahlen­for­mat zu erre­ichen und diese Werte dann auch noch für die Bed­ingte For­matierung als Eck­w­erte zu ver­wen­den.

Optis­che Auf­bere­itung durch Bed­ingte For­matierung

▲ nach oben …

Für den ersten Teil soll es das gewe­sen sein. Ger­ade in Sachen Pow­er Query wird Ihnen gewiss vieles unbekan­nt gewe­sen sein; aber PQ ist so mächtig, dass es sich lohnt, dort tiefer einzusteigen. Das sehen Sie auch daran, dass in Excel 2016 diese Funk­tion­al­ität bein Daten­im­port an führen­der Stelle ste­ht. Mehr dazu in den fol­gen­den Teilen der Ein­führung. (Die sind derzeit noch in der Entste­hung oder Pla­nung!)

Hin­weis: Am 25. Juli 2016 veröf­fentlichte Microsoft eine neue Ver­sion des Pow­er Query mit ver­schiede­nen teils sehr sin­nvollen Ergänzun­gen. Diese Ver­sion ist hier noch nicht berück­sichtigt, wir wer­den uns aber damit auseinan­der­set­zen und an passender Stelle darüber disku­tieren.

Hin­weis: Derzeit ist ein Begleit-Video in Arbeit. Es umfasst auss­chließlich den Pow­er Query-Teil und ist mit der 2016^er Ver­sion des Excel aufgeze­ich­net wor­den. Als spätester Veröf­fentlichungstag ist der 15. August 2016 ins Auge gefasst. – Im fol­gen­den Teil wer­den wir anfangs näher auf die Andere Vorge­hensweise in Excel 2016 einge­hen und natür­lich wird Pow­er Query dort im Vorder­grund ste­hen, neben ein­er Piv­otChart-Auswer­tung der erar­beit­eten Dat­en.

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 3,00€ bis 5,00€ freuen …

Bun­desweite  ✉ Schu­lun­gen ✉  durch unseren Spon­sor GMG Com­put­er-Con­sult­ing