Pow­er Query ist eine mächtige Erweiterung für Excel ab der Ver­sion 2010. Sie kön­nen dieses Excel Add-In bei Microsoft kosten­los herun­ter­laden. Hier der aktuelle Link (Stand März 2016). Auf jen­er Seite find­en Sie auch Anleitun­gen zur Instal­la­tion. Sie rufen ein­fach unter Win­dows die *.msi (Instal­la­tions­datei) auf,  und anschließend haben Sie in Ihrer Menü-Leiste einen neuen Punk, Pow­er Query.

Speziell für diesen Test haben wir Ihnen eine richtig „fette” Tabelle erstellt. Eine Mil­lio­nen Zeilen mit mehreren leeren Zellen in jed­er der sechs Spal­ten. In diesem Pro­jekt sollen als Beispiel alle Zeilen, wo in Spalte A eine leere Zelle ist, kom­plett gelöscht wer­den. Es kann aber auch jede andere Spalte oder ein beliebiger ander­er Wert, Zahl oder Text gewählt wer­den. Das Prinzip ist iden­tisch. Wichtig ist nur, dass nicht gefiltert wer­den soll son­dern dass wirk­lich eine neue Tabelle erstellt wird, wo tat­säch­lich nur jene Zeilen enthal­ten sind, die (hier) in Spalte A einen beliebi­gen Wert enthal­ten. Und die Orig­i­nal-Dat­en sollen natür­lich erhal­ten bleiben.

Öff­nen Sie nun fol­gende Datei in Ihrem Excel. Zur Über­prü­fung, ob es auch wirk­lich eine Mil­lio­nen Zeilen sind, ein­fach ein­mal StrgEnde. Alles klar, es stimmt. Sich­er wis­sen Sie, dass Sie mit StrgPos1 wieder zu A1 kom­men. Das wäre hier auch mehr als sin­nvoll, um einen später beschriebe­nen Effekt ken­nen zu ler­nen. In jedem Fall ist es wichtig, dass die aktive, die aus­gewählte Zelle also, im Daten­bere­ich der oberen ca. 15.000 Zeilen liegt. Danach ein Klick auf den Menüpunkt Pow­er Query (in der Abbil­dung durch uns rot markiert) und es zeigt sich ein kom­plett neues Menüband:

Menüband für das Pow­er Query-Tool

Zu Beginn geht es darum, die entsprechen­den Dat­en dem Abfrage-Tool zur Ver­fü­gung zu stellen. Gle­ich im Anschluss an die erste Menü-Gruppe (Externe Dat­en abrufen) ist die Gruppe Excel-Dat­en mit der Schalt­fläche Von Tabelle. Ein Klick darauf und da die aktive Zelle (beispiel­sweise A1) ja inner­halb des Daten­bere­ichs ist, sucht sich PQ (Pow­er Query) den passenden Bere­ich, markiert ihn und zeigt ihn in einem solchen Fen­ster an:

Automa­tisch markierten Bere­ich bestäti­gen

Und wie Sie immer vorge­hen soll­ten: Über­prüfen Sie, was Excel vorschlägt. Meis­tens ist es richtig. Meis­tens, aber nicht immer. Die Zeile 17.646 liegt ja „leicht” unter 1 Mil­lio­nen. Natür­lich wer­den Sie die Aktion Abbrechen. Über­prüfen Sie also ein­mal, warum Excel hier einen (oder vielle­icht doch keinen) Fehler macht. OK, im zweit­en Anlauf kön­nen Sie erst A1 markieren, dann StrgShiftEnde und nochmals auf die gle­iche Schalt­fläche. So sind alle Zellen des Daten­bere­ichs markiert. Nun passt es, oder? Nein, es passt immer noch nicht. Excel wider­spricht Ihnen und wählt wieder den gle­ichen Bere­ich wie vorher aus. Das mag etwas nervig sein, hat aber dur­chaus seinen tief­er­en Sinn. Kor­rigieren Sie also den Wert für die let­zte Zeile von Hand auf 1000000 und dann OK? – Nein, immer noch nicht ganz. Es ist näm­lich dur­chaus wahrschein­lich, dass auch bei Ihnen das Häkchen bei Meine Tabelle hat Über­schriften. geset­zt ist. Und es ist ja offen­sichtlich, dass die Tabelle keine Über­schriften hat. Also muss das kor­rigiert wer­den. Aber dann wirk­lich und endgültig OK.  🙂

Alles so wie gewün­scht bzw. erforder­lich? Nun ja, es ist dur­chaus denkbar, dass Excel diese recht große Daten­menge nicht kom­plett im Arbeitsspe­ich­er hal­ten kann. Dann wer­den Sie mit dieser Frage kon­fron­tiert:

Sicher­heitsabfrage, ob so weit­er gemacht wer­den soll

Da an den Orig­i­nal-Dat­en nichts geän­dert wird, dür­fen Sie bedenken­los auf OK Klick­en. Es wird ein neues Fen­ster geöffnet, ein kom­plett anderes Menü ist wieder ein­mal da und die Tabelle sieht auch etwas anders aus. Beson­ders fällt gewiss auf, dass bei den Leer­feldern der Begriff null drin ste­ht. Und es wurde automa­tisch eine Zeile mit Über­schriften einge­fügt. Das ist auch unab­d­ing­bar, denn solch eine Tabelle bzw. Liste braucht in jedem Falle Über­schriften:

Spalte A soll ja der Vor­gabe entsprechend erst ein­mal gefiltert wer­den, darum Klick­en Sie in der Über­schriftzeile im Feld Spalte 1 auf  die Schalt­fläche ▼:

Fil­ter zum aus­blenden von Werten ein­er Spalte

Die eventuell erscheinende Anmerkung direkt über den bei­den unteren But­tons, dass die Liste möglicher­weise unvoll­ständig sei, ist seit­ens des Inhalts ja bekan­nt. Ignori­eren Sie diese ein­fach. Was Sie aber jet­zt irri­tieren kön­nte, das ist der näch­ste Schritt. Schein­bar genau umgekehrt von bish­eri­gen Erfahrun­gen wer­den Sie näm­lich fol­gen­des tun: Sie nehmen das Häkchen bei (NULL) her­aus, selb­st wenn es vielle­icht unl­o­gisch erscheint.

Bedenken Sie jedoch: Hier wird fest­gelegt, was alles angezeigt und auch im Anschluss in ein Tabel­len­blatt über­führt wer­den soll. Darum die Null-Werte ent­fer­nen. Anschließend OK und zum Abschluss ein Klick auf die allererste Menü-Schalt­fläche Schließen und laden. Nach (mehr oder weniger) kurz­er Wartezeit wird sich vielle­icht wieder das Warn-Fen­ster zeigen, auch hier machen Sie ein­fach weit­er. Nochmals einen Moment warten und schließlich zeigt sich ein neues Tabel­len­blatt-Fen­ster; der Reg­is­ter-Name (wahrschein­lich ist es Tabelle1) wird eigen­ständig vergeben. Sie erken­nen eine Liste mit ein­er grü­nen Tabel­lierung (die Ein­fär­bung jed­er 2. Zeile) und rechts einen Regie- und Infor­ma­tions­bere­ich:

Rechts der Regie- und Infor­ma­tions­bere­ich

Das Ergeb­nis der Abfrage sind also 900.058 Zeilen. Sie kön­nen es auch gerne prüfen, ob das stimmt.  😎 Dabei han­delt es sich keineswegs um eine Fil­terung, die Werte ste­hen da tat­säch­lich ohne Lück­en in den einzel­nen Zeilen. Oder anders aus­ge­drückt: In diese neu erstellte Tabelle wur­den nur die gefilterten, die sicht­baren Zeilen aus der Ursprungsta­belle über­nom­men:

Die endgültige Tabelle

Mit diesen Werten, mit dieser Tabelle kön­nen Sie prinzip­iell wie gewohnt arbeit­en. Es ist erst ein­mal eine ganz nor­male Intel­li­gente Tabelle mit all ihren Möglichkeit­en. Zusät­zlich haben Sie noch einige weit­ere Ange­bote, welche sich speziell auf das PQ-Mod­ell beziehen. Es ist möglich, die Tabelle so wie sie ist zu kopieren und beispiel­sweise in einem anderen Arbeits­blatt als Wert einzufü­gen. Dann ist sie gegen verse­hentliche Verän­derung geschützt. Nachteil: Diese kopierte Tabelle ist dann natür­lich auch nicht dynamisch, was ja dur­chaus gewün­scht sein kann. Die ger­ade eben automa­tisch erstellte Tabelle ist jedoch dynamisch und Sie kön­nen alle Vorteile des Pow­er Query nutzen. Die Diskus­sion darüber, welche Form ange­bracht ist, würde allerd­ings hier entsch­ieden zu weit führen, denn das geset­zte Ziel ist erre­icht.

Eine Erk­lärung sind wir Ihnen vielle­icht doch noch schuldig. Warum wer­den zu Beginn nur die ersten 17.646 Zeilen markiert? Nun ja, wenn Sie sich die eine Zeile darunter ein­mal anse­hen so wer­den Sie fest­stellen, dass diese kom­plett leer ist. Und da durch den Klick auf die Schalt­fläche Aus Tabelle eine Intel­li­gente Tabelle erzeugt wer­den soll, wird Excel automa­tisch den gesamten zusam­men­hän­gen­den Bere­ich nutzen. Und durch diese Leerzeile wird genau der an der Stelle unter­brochen, begren­zt. Alter­na­tive: In eine nicht auszuw­er­tende Spalte dieser Zeile ein Leerze­ichen schreiben, dann erken­nt Excel, dass diese Zeile nicht kom­plett leer ist und wird gle­ich den ganzen Bere­ich markieren bzw. vorschla­gen.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 1,00 € freuen … (← Klick mich!)