Mit wenigen Klicks zum Erfolg
Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Angeregt durch eine Frage in einem Excel-Forum habe ich die dort gestellte Frage in eine kleine Aufgabe für diesen Blog umgewandelt. Im Prinzip geht es darum, aus einer Gruppe von Daten einem bestimmten Begriff zu wählen und alle dazu passenden Einträge der Nachbarspalte darzustellen. Das hört sich jetzt vielleicht nicht wirklich nachvollziehbar an. Darum laden Sie zu Beginn diese Datei mit den Roh-Daten von unserem Server herunter.
Der große Block mit den beiden Spalten Alle Daten sowie Produkt enthält in der Spalte AlleDaten in nicht geordneter Reihenfolge unterschiedliche Produktgruppen (Fleisch, Gemüse, Meeresfrüchte und Obst) sowie in der Spalte Produkt die dazugehörigen Lebensmittel. Im Bereich A22:A23 kann im DropDown eine der 4 Produktgruppen ausgewählt werden. Das Ziel ist es nun, in einem weiteren Bereich die zur ausgewählten Produktgruppe passenden Lebensmittel aufzulisten. Und da das ganze mit Power Query geschehen soll, bedarf es zur Aktualisierung natürlich des entsprechenden Mausklicks. 😉
Die Muster-Datei haben sie geladen. Importieren Sie nun nacheinander die Tabelle mit den Rohdaten sowie jene mit dem Suchbegriff in Power Query. Dazu werden sie erst einmal die in A1 beginnende Tabelle oder eine Zelle darin markieren, in den Excel-Versionen 2010⁄13 das Register Power Query aktivieren und dann Von Tabelle, in neueren Versionen das Register Daten, Gruppe Daten Abrufen und transformieren und anschließend das Symbol Aus Tabelle/Bereich. Erweitern Sie nun das Symbol Schließen & laden durch einen Klick auf den Text unterhalb des Symbols und wählen dort Schließen & laden in… Markieren Sie im Dialog die Auswahl Nur Verbindung erstellen und bestätigen Sie mit OK. Im rechten Seitenfenster wird nun die Abfrage AlleDaten angezeigt.
Markieren Sie nun eine der beiden Zellen A22 oder A23 und importieren Sie auch diese Tabelle in den Power Query Editor. Diese Abfrage werden sie nun jedoch etwas anders speichern: Gehen Sie beispielsweise über Datei | Schließen & laden in…, belassen es bei der Auswahl Tabelle und markieren die Optionsschaltfläche Bestehendes Arbeitsblatt und Klicken Sie im Excel-Arbeitsblatt Tabelle1 in die Zelle C22. Dieser Wert wird automatisch übernommen und nach einem OK fügt Power Query erst einmal eine einspaltige Tabelle an der entsprechende Position im Arbeitsblatt ein.
Sie verfügen nun über 2 Abfragen, die sie auch im rechten Seitenfenster aufgeführt sehen. Öffnen Sie dort die Abfrage Suche beispielsweise durch einen Doppelklick auf diesen Eintrag. Das stellt sich derzeit noch ziemlich „schlank” dar, aber immerhin wird in der einen Zelle schon Suchbegriff (hier: Gemüse) angezeigt:
Direkt nach dem Import sieht das alles noch sehr „schlank” aus…
Power Query soll nun alle zu diesem Begriff passenden Produkte, welche in der Abfrage AlleDaten vorhanden sind filtern und die aktuelle Tabelle um diesen Extrakt ergänzen. Dazu wählen Sie Start | Kombinieren | Abfragen zusammenführen und es öffnet sich ein Dialog:
Der Zusammenführen-Dialog mit dem gelb markierten Feld für die Auswahl der 2. Abfrage
Erweitern Sie das hier gelb markierte Textfeld und wählen Sie dort den Eintrag AlleDaten. Markieren Sie nun im oberen Kasten das Feld mit der Produktgruppe (hier: Gemüse) und im unteren Kasten ein Feld in der Spalte AlleDaten. Die Auswahl Join-Art belassen Sie so wie sie ist. Bevor sie auf OK Klicken stellt sich der Dialog so dar:
Die Daten sind für das Zusammenführen fertig markiert (grün hinterlegt)
… und gleich danach ist in der Abfrage die Spalte AlleDaten dazu gekommen und der Inhalt dieser Zelle ist Table. Die hier passende Übersetzung ist übrigens „Tabelle” und nicht „Tisch”. 😎 Erweitern Sie die Überschrift durch einen Klick auf den Doppelpfeil 
und es öffnet sich wiederum ein Dialog:
Der Erweitern-Dialog; was soll angezeigt werden?
Entfernen Sie das Häkchen bei AlleDaten und bei Ursprünglichen Spaltennamen als Präfix verwenden, danach schließen Sie diesen Dialog. Sie erkennen, dass nun eine zweite Spalte mit der Überschrift Produkt existiert und dass dort alle 4 in der Ursprungstabelle erfassten Gemüsesorten aufgelistet sind. Idealerweise werden sie nun noch die Spalte Produkt auf die übliche Weise aufsteigend sortieren. Jetzt noch ein Klick auf das Symbol Schließen & laden und die in C22 beginnende Tabelle wird nach den Gegebenheiten der eben erstellten Abfrage aktualisiert.
Wenn Sie nun in A23 beispielsweise Fleisch als Suchbegriff auswählen, wird sich erst einmal gar nichts tun. Das ist eine Eigenart von Power Query, die gewollt und aus meiner Sicht auch gut ist. Führen Sie in der Ergebnis-Tabelle einen Rechtsklick durch und wählen im Kontextmenü Aktualisieren und schon passt das Ergebnis. 💡
Was sie vielleicht als kleines Manko ansehen könnten ist, dass in der Ergebnis-Tabelle die Produktgruppe in jeder einzelnen Zeile wiederholt wird. Das lässt sich durch eine Bedingte Formatierung recht gut regulieren. Markieren Sie dazu den Datenbereich der Spalte Gesucht wird … und anschließend im Register Start ein Klick auf Bedingte Formatierung. Wählen Sie im DropDown Neue Regel und anschließend Formel zur Ermittlung der zu formatieren den Zellen verwenden. In das Feld Regelbeschreibung bearbeiten: tragen Sie diese Formel ein: =C23=C22 und anschließend ein Klick auf Formatieren… Wählen Sie hier das Register Zahlen | Benutzerdefiniert und tragen Sie bei Datentyp: (dort wo derzeit Standard steht) nur 3 Semikola ;;; ein:
Dieses Zahlenformat blendet identische Werte ab 2. Zeile aus
Nach einem Klick auf OK und noch einmal OK im übergeordneten Fenster und sie werden erkennen, dass jetzt nur noch die erste Zeile der Produktgruppe in der Tabelle angezeigt wird. Und das wird so auch beibehalten, wenn sie beispielsweise die Meeresfrüchte ausgewählt und aktualisiert haben, obwohl die Zahl dieser Produkte größer ist.
Die von mir erstellte fertige Datei können Sie hier von unserem Server herunterladen. Und noch ein kleiner Hinweis: ich habe mir speziell für das Aktualisieren ein Symbol in die Schnellstartleiste gelegt; ein Klick darauf ist eben doch schneller geschehen als der Rechtsklick und dann zu suchen, wo der Eintrag Aktualisieren wohl steht. Wie sie das Symbol in die Schnellstartleiste einfügen, können Sie gewiss rasch herausfinden, wenn sie die Suchmaschine ihrer Wahl bemühen.
Hinweis: In der Forums-Diskussion stellte sich im Laufe der Zeit heraus, dass der Wunsch des Fragestellers prinzipiell so war, dass die einzelnen Produkte nicht in mehreren Zeilen der Tabelle aufgelistet werden sollten (wie in diesem Beispiel) sondern in einer einzigen Zelle, wo die unterschiedlichen Produkte durch eine Zeilenschaltung (die Sie in Excel durch AltEingabe erreichen) aufgeführt werden sollen.
Auch das ist (natürlich) mit Power Query machbar. Der Aufwand dafür ist aber um einiges größer und deutlich anspruchsvoller als bei der hierüber gezeigten Lösung. Wenn Sie interessiert sind, fragen Sie gerne an; gegen eine kleine Spende (deren Höhe wir Ihnen per Mail mitteilen) sende ich Ihnen den Link und das Passwort für die Lösung dieser Darstellung. Die Beschreibung in dem Beitrag setzt aber voraus, dass sie schon einige Erfahrung mit Power Query haben, denn die absoluten Basics sind dort nicht ausführlich erklärt sondern werden nur im Text erwähnt. ->
Wissensstand: Level 2 ⇒ Solides Basiswissen in PQ, etwas Erfahrung
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (€ 1,00 – € 2,00) Ihrerseits freuen …