Üblicherweise werden ja ganze Tabellen in Power Query importiert. Handelt es sich dabei um eine normale Excel-Liste in der aktuell geöffneten Datei, wird diese vorher automatisch in eine formatierte Tabelle umgewandelt.
Was macht man aber, wenn man nur eine einzige Zelle aus der aktuellen Arbeitsmappe in den Editor laden möchte? Und warum sollte man so etwas überhaupt tun wollen? Die Antwort ist einfach: Weil man den Inhalt der Zelle als einen einzelnen Parameter an eine Abfrage übergeben möchte.
Und so geht’s:
Beispieldatei herunterladen (ZIP-Archiv)
Ausgangslage
Für mein heutiges Beispiel nehmen wir an, dass jeden Monat eine Datei mit den aktuellen Umsatzdaten mit Hilfe von Power Query in Excel importiert werden soll. Der Aufbau der Quelldatei bleibt dabei gleich, lediglich der Dateiname ändert sich von Monat zu Monat.
Die Datei wird beim ersten Mal wie folgt importiert:
Menü „Daten | Aus Datei | Aus Arbeitsmappe“
Daten aus Excel-Arbeitsmappe importieren
Dann klicke ich mich durch meine Verzeichnisse bis zur gewünschten Datei durch. Da die Umsätze in einer formatierten („intelligenten“) Tabelle stehen, wähle ich im Navigatorfenster gleich das Tabellenobjekt aus. Der Einfachheit halber gehe ich in meinem Beispiel davon aus, dass an der Datei nichts mehr verändert werden muss und ich sie gleich über „Laden | Laden in…“ in ein Tabellenblatt einladen kann:
Tabellenobjekt auswählen und laden
Abfrage in Tabelle laden
Im nächsten Monat muss vor dem Import der Dateiname in der Abfrage geändert werden, dazu gibt es verschiedene Möglichkeiten. Eine Variante wäre, über das Menü „Daten | Daten abrufen | Datenquelleneinstellungen“ und dort über die Schaltfläche „Datenquelle ändern“ die neue Datei auszuwählen:
Datenquelleneinstellungen anpassen
Das ist schon mal relativ einfach, aber wir wollen es noch ein wenig bequemer haben. Schließlich soll der Kollege, der mich vielleicht im Urlaub vertreten muss, sich nicht mit den Details im Daten-Menü auseinandersetzen müssen. Da muss eine einfachere Lösung her.
Eine Parameterzelle festlegen
Die Idee ist es daher, den Pfad und Dateinamen stattdessen gut sichtbar in eine definierte Zelle im Arbeitsblatt zu hinterlegen, damit man ihn dort im nächsten Monat ganz bequem anpassen kann. Das kann direkt oberhalb von meiner Datentabelle sein, aber es wäre auch auf einem anderen Arbeitsblatt möglich. Ich wähle dafür die Zelle B1 und damit der Anwender auch weiß, wo von ihm die Eingabe erwartet wird, färbe ich die Zelle gleich noch ein und schreibe noch einen kleinen Hinweistext dazu.
Jetzt kommt ein entscheidender Schritt:
Die Eingabezelle muss mit einem Namen versehen werden, damit ich sie später als einzelne Zelle in Power Query einladen kann. Am schnellsten geht das, wenn man den gewünschten Namen links oben in das Namensfeld eintippt. Ich verwende hier den Namen „parDatenquelle“:
Namen für Zelle vergeben
Als nächstes wird diese einzelne Zelle nach Power Query geladen:
Menü „Daten | Aus Tabelle/Bereich“
Zelle aus Tabelle/Bereich laden
Hätten wir zuvor keinen Bereichsnamen für die Zelle festgelegt, würde Power Query jetzt die Zelle in eine formatierte Tabelle umwandeln und dann erst laden, was in unserem Fall jedoch nicht gewünscht ist. So wird aber der Name erkannt und die Zelle sofort als neue Abfrage im Power Query Editor angezeigt:
Die Parameterzelle in Power Query geladen
Bevor wir sie aber in unserer anderen Abfrage verwenden können, müssen noch ein paar Anpassungen vorgenommen werden. Zuerst werden alle angewendeten Schritte mit Ausnahme von „Quelle“ gelöscht. Danach machen wir einen Rechtsklick in das Feld mit dem angezeigten Pfad- und Dateinamen und wählen den Menüpunkt „Drilldown ausführen“:
Drilldown ausführen
Danach sieht die Abfrage so aus:
Abfrage nach dem Drilldown
Jetzt wird es ein klein wenig kniffelig. Der angewendete Schritt „Navigation“ muss ausgewählt sein. Dann klickt man oben in die Bearbeitungsleiste und markiert den Teil, der hinter „Quelle“ kommt, also „{0}[Column]“ und kopieren die Markierung mit Strg+C in die Zwischenablage:
Aus Bearbeitungsleiste kopieren
Als nächstes löschen wir den Schritt „Navigation“ und fügen im verbleibenden Schritt „Quelle“ ebenfalls in der Bearbeitungszeile ganz am Ende den kopierten Wert aus der Zwischenablage mit Strg+V wieder ein. Danach sollte die Abfrage wie folgt aussehen:
= Excel.CurrentWorkbook(){[Name="parDatenquelle"]}[Content]{0}[Column1]
In den Quellen-Schritt einfügen
Nun markieren wir die komplette Befehlszeile (allerdings ohne das Gleichheitszeichen am Anfang) und kopieren sie wieder mit Strg+C in die Zwischenablage.
Komplette Befehlszeile kopieren
Keine Angst, wir sind gleich fertig, nur noch ein letzter Schritt. Wir öffnen jetzt die Abfrage tblFakturen und markieren dort den ersten Schritt „Quelle“. Oben in der Bearbeitungsleiste steht im Moment noch der Pfad und Dateiname unserer vorhin importierten Datei als statischer Text in doppelten Anführungszeichen:
= Excel.Workbook(File.Contents("D:\Temp\Sonstiges\Umsätze\Januar-2023.xlsx"), null, true)
Quelle in tblFakturen anpassen
Und genau diesen statischen Text löschen wir heraus. Entferne auch die beiden Anführungszeichen am Anfang und Ende und achte unbedingt darauf, dass ansonsten alles unverändert bleibt.
Dann fügen wir zwischen den beiden verbliebenen runden Klammern mit Strg+V unseren vorhin kopierten Inhalt aus der Zwischenablage dort ein, so dass am Ende der ganze Befehl wie folgt aussieht:
= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="parDatenquelle"]}[Content]{0}[Column1]), null, true)
Der geänderte Quellen-Schritt
Geschafft! Wenn du beim Löschen und Einfügen keinen Fehler gemacht hast, sollte jetzt auch jetzt noch die Umsatztabelle angezeigt werden, wenn den letzten Schritt in der Abfrage ausgewählt hast:
Die Abfrage funktioniert noch
Falls nicht, schau dir die oben beschriebenen Schritte nochmal genau an.
Jetzt können wir auch die Parameterabfrage „parDatenquelle“ löschen, denn wir haben ja alles Notwendige direkt in der tblFakturen-Abfrage eingefügt. Den Power Query-Editor verlassen wir über „Start | Schließen & Laden“
In unserer geladenen Tabelle sieht bisher noch alles wie zuvor aus. Jetzt ändern wir aber unsere Parameterzelle B1 und tragen den neuen Dateinamen für den nächsten Monat ein. Und nach einem Rechtsklick in die Datentabelle und „Aktualisieren“ sollten nach wenigen Sekunden die Februarzahlen erscheinen:
Anpassen der Datenquelle und aktualisieren
Ich gebe zu, das war etwas kniffelig in der Umsetzung, aber als Lohn dafür gibt es einen größeren Bedienerkomfort.
Natürlich lassen sich solche Parameterzellen auch für andere Dinge nutzen. Man könnte beispielsweise Jahreszahlen übergeben, um eine Abfrage damit zu filtern. Oder Wechselkurse für eine dynamische Umrechnung von Fremdwährungen. Oder Prozentangaben für eine Provisionsberechnung. Oder, oder, oder….
Welche Anwendungsmöglichkeiten fallen dir noch ein? Lass es uns in den Kommentaren wissen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Super erklärt, nutze diese Vorgehensweise auch um über z.B. die Kundennummer nur einzelne Datensätze zu selektieren.
Dankeschön, freut mich zu hören!
Schöne Grüße,
Martin
Hallo Martin,
vermutlich beherrscht Du die Kunst des Gedankenlesens, den just diese Lösung habe ich für mein Problem gesucht.
Vielen Dank!
Gruß Joachim
Hallo Joachim,
das freut mich zu hören, vielleicht sollte ich mich ja mal in dieser Richtung betätigen 😉
Schöne Grüße,
Martin
Eine tolle Lösung.
Ich habe sie nachgebaut – es funzt wie immer bei deinen Beispieldaten, lieber Martin.
Ganz herzlichen Dank.
Anwendung über Personal-Nr, um die geleisteten Arbeitsstunden des letzten Monats einzuladen.
Pers-Nr hat viele Anwendungen – Beurteilungsgespräch-Ergebnis usw.
Vielen Dank für deinen Service!!??
Hallo Gerhard,
ja, es gibt wirklich viele Anwendungsmöglichkeiten für solche Parameterzellen. Freut mich zu hören, dass du ein praktisches Einsatzgebiet dafür hast.
Schöne Grüße,
Martin
Hallo Martin,
bei der Suche nach einer passenden Formel bin ich auf deine Seite gestoßen und muss mal zuerst einen Lob aussprechen, so wie du es erklärst und mit Beispielbildern das Ganze auch visuell wiedergibst, scheint alles logisch und nachvollziehbar zu sein.
Allerdings gibt es im Arbeitsalltag auch andere Fälle, deshalb habe ich eine etwas spezielle Aufgabe erhalten, die mir etwas Kopfschmerzen bereitet und ich habe gehofft, dass du mir hierbei helfen kannst.
Und zwar habe ich die Ehre bei uns im Unternehmen einen Betriebsvergleich neu aufzuziehen, der möglichst automatisch gestaltet werden soll. Bedeutet das ich eine Tabelle zum Schluss erhalten soll, wo verschiedene Parameter aus verschiedensten Quellen mit einfließen sollen, aber viele davon sind auf eine ganz einfache Art und Weise durch Verknüpfungen zur anderen Tabellen via Formeln zu meistern, bis auf die eine Sache. Ich habe aus unserem Warenwirtschaftssystem eine Auswertung (dynamisch/wird regelmäßig aktualisiert) erhalten, die automatisch durch Erfassung neuer Artikel an der Verpackungsanlage mir ein Datum rausspuckt. Das bedeutet, dass an einem Datum auch mehrere Artikel laufen könnten und somit das Datum mehrmals vorkommt. Doch ich benötige zum Schluss nur die Eindeutigen Werte unter zwei zusätzlichen Bedingungen dazu wie Betriebsstätte Nr. 1 / Produktionsmonat 1 / = Summe der Arbeitstage??? Habe es mit „Excel 2019“ Formeln versucht Zählenwenns – Häufigkeit allerdings zieht er mir hier nur die häufig vorkommende Werte nur einmal ab, also demensprechend komme ich auf das falsche Ergebnis. Zum Schluss habe ich es dann mit Power Query probiert, wo ich zwar auf die richtige Zahl komme, aber nicht genau weiß, wie ich das aufbauen soll, sowie das Ergebnis zum Schluss nur in Zelle pro Betriebsstätte anzeigen zu lassen.
Hast du evtl. eine andere Idee oder lande ich am Ende eher bei Power Query, um alle Daten aus verschiedenen Quellen überhaupt zu überblicken, sonst habe ich lauter Verknüpfungen zum Schluss.
Danke dir im Voraus und schönen Gruß aus Bayern
Alexander K.
Hallo,
es kein sein, dass ich das Problem nicht richtig verstanden habe, aber ich würde die Daten aus dem Warenwirtschaftssystem mittels PQ importieren und die Auswertungen über Pivot-Tabellen vornehmen.
Gruß Joachim
Hallo Alexander,
wenn es um das Zusammenführen oder Abgreifen von Daten aus unterschiedlichen Arbeitsmappen geht, würde ich grundsätzlich immer Power Query empfehlen. Die Verknüpfung über Formeln ist einfach extrem fehleranfällig (verlinkte Datei wird umbenannt, verschoben, gelöscht…)
In Power Query kannst du dann alle geladenen Daten soweit bereinigen/aufbereiten/verdichten und dann beispielsweise in ein separates Tabellenblatt laden, um dann mit deinen Formeln darauf zuzugreifen. Und dann sollte es auch einfacher werden, mit ZÄHLENWENNS etc auf die korrekten Werte zu kommen.
Oder eben – wie Joachim schreibt – gleich über Pivot-Tabellen auswerten.
Eine ganz konkrete Lösung für deine Aufgabenstellung kann ich hier allerdings nicht bieten, denn ich kenne den Aufbau der Tabellen und die Struktur der Daten nicht.
Schöne Grüße,
Martin
Hallo Martin,
ich stimme Dir vollkommen zu. Ich hatte die Fragestellung so verstanden, dass alles funktioniert, bis auf den Import der Daten aus dem Waren Wirtschaftssystem. Die würde ich mit PQ aufarbeiten.
Wenn es irgendwelche eindeutige Schlüssel gibt, könnte man ja dann über einen eindeutigen Schlüssel per Datenmodell 1:n Beziehungen herstellen um per Pivot die Auswertungen durchzuführen.
Ich muss allerdings gestehen, dass ich 62 Jahre alt bin, bis vor 2,5 Jahren nur rudimentäre Kenntnisse in Excel hatte und eine gewisse Seite http://www.tabellenexperte.de sowie ein E-Book „Excel Pivot-Tabellen für Dummys“ hauptsächlich dazu beigetragen haben, dass ich jetzt Auswertungen vornehmen kann, von denen ich früher nicht mal geträumt hätte.
Ich hoffe, diese Werbung ist hier erlaubt…
Hallo Joachim,
vielen Dank für das schöne Feedback zum meinem Blog und zum Pivot-Buch, über diese Werbung freue ich mich natürlich sehr!
Und ich gebe dir vollkommen recht:
Auch ich würde versuchen, die Daten per PQ einzulesen, aufzubereiten und nach Möglichkeit über Pivot/Power Pivot auszuwerten.
Schöne Grüße,
Martin
Hallo Joachim und Martin,
vielen Dank für euer Feedback zu meiner Fragestellung, muss selber zugeben, dass es nicht so einfach ist dies zu verstehen, was ich geschrieben habe. Es ist für mich ebenfalls schwer alles so wiederzugeben, denn mir fehlt selbst noch die Gesamtübersicht von all den Daten aus unterschiedlichsten Schnittstellen (Tabellen), die ich auswerten soll. Selbst mit PQ habe ich das Problem, dass ich zwar zu dem Ergebnis komme, aber durch das Vorfiltern verschwinden mir dann die anderen Daten und ich bekomme dadurch eine große Differenz, da dies ja alles zusammenhängt. Deshalb habe ich mich für die einfachere Variante entschieden, und zwar via Verknüpfungen (vorerst), bis ich mir evtl. durch den Rat vom Joachim ein gewisses Wissen in Excel angeeignet habe. Aber ich wäre trotzdem dankbar, wenn mir einer einen Tipp geben kann bzgl. unten beschriebener Aufgabe. Wie bekomme ich Eindeutige Tage als Zahl raus, die leider manchmal doppelt oder mehrfach vorkommen anhand einiger Kriterien, wie Kriterium 1 = Spalte C (Betrieb 1,6,8 ..) Kriterium 2 = Spalte D „>=01.01.2023“ und Kriterium 3 Spalte D „=01.01.2023“;$D:$D;“<=31.01.2023")-HÄUFIGKEIT($D:$D;$D:$D)
Danke schon mal im Voraus
Schöne Grüße, Alexander
Hallo Alexander,
ich bin leider immer noch etwas verwirrt hinsichtlich deiner Beschreibung:
Du möchtest wissen, an wie vielen *unterschiedlichen* Tagen in einem Monat pro Betriebsstätte ein Datensatz existiert? Also z.B.
Betriebsstätte 1: 5 verschiedene Tage im Januar
Betriebsstätte 6: 2 verschiedene Tage im Januar
usw.
Tut mir leid, da wüsste ich jetzt auf Anhieb auch keine Formellösung.
Schöne Grüße,
Martin
Hallo!
Vielen Dank für den sehr hilfreichen Blog-Beitrag! Ich suche noch nach einer Lösung, wie für jede der eingelesenen Daten ein eigenes Tabellenblatt angelegt werden kann, idealerweise gleich mit dem Namen der jeweiligen Datei. Ich bekomms nur so hin, dass die Tabelle immer wieder überschrieben wird. Schon im Voraus vielen Dank für einen Tipp!
Liebe Grüße aus Österreich
Steffi
Hallo Steffi,
das ist so ohne weiteres nicht möglich. Denn die einmal erstellte Abfrage führt immer zu genau der gleichen Ausgabetabelle, selbst wenn sich die Quelldatei ändert. Wenn du also mehrere Quelldateien einlesen und in separate Blätter ausgeben möchtest, musst du entsprechend viele Abfragen erstellen. Falls die Dateien den identischen Aufbau haben, kannst du natürlich eine vorhandene Abfrage kopieren und musst dann nur die Datenquelle anpassen.
Schöne Grüße,
Martin
Vielen Dank für die rasche Antwort!
Liebe Grüße
Steffi