Office: Erstellen einer Parameterabfrage

Erstellen einer Parameterabfrage
Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Mehr... Weniger Wenn Sie Daten in Excel Abfragen, möchten Sie möglicherweise einen Eingabewert-einen Parameter-verwenden, um etwas zur Abfrage anzugeben. Erstellen Sie dazu eine Parameterabfrage. Wie Sie Parameterabfragen erstellen und wie Sie sich Verhalten, hängt davon ab, ob Sie Microsoft Query oder Power Query verwenden.

Tipp: Power Query-Parameter unterscheiden sich stark von Parametern, die in SQL-basierten Abfragen verwendet werden. Darüber hinaus können Sie eine Abfrage anstelle eines tatsächlichen Parameters verwenden, wenn Sie lediglich Daten filtern möchten. Lesen Sie die Beispiel Abschnitte Power Query, bevor Sie Parameter in Power Query erstellen.

	Microsoft Query	Power Query
Auswirkungen von Parametern auf Abfragen	Parameter werden in der WHERE-Klausel der Abfrage verwendet – sie funktionieren immer als Filter für abgerufene Daten.	Parameter können in jedem Abfrage Schritt verwendet werden. Neben der Funktion als Datenfilter können Parameter verwendet werden, um Elemente wie einen Dateipfad oder einen Servernamen anzugeben.
Parameter Eingabeoptionen	Parameter können den Benutzer auffordern, einen Eingabewert einzugeben, wenn die Abfrage ausgeführt oder aktualisiert wird, eine Konstante als Eingabewert zu verwenden oder den Inhalt einer angegebenen Zelle als Eingabewert zu verwenden.	Parameter geben keine Eingabeaufforderung ein. Stattdessen können Sie deren Wert mit dem Power Query-Editor ändern. Oder anstelle eines Bona fide-Parameters können Sie eine Abfrage verwenden, die sich auf einen externen Speicherort mit einem Wert bezieht, den Sie problemlos bearbeiten können.
Parameter Bereich	Ein Parameter ist Teil der von ihm veränderten Abfrage und kann nicht in anderen Abfragen wieder verwendet werden.	Parameter sind unabhängig von Abfragen – nach der Erstellung können Sie Abfragen nach Bedarf einen Parameter hinzufügen.

Erstellen einer Parameterabfrage in Microsoft Query

1. Klicken Sie auf Daten > & Daten transformieren > Daten > aus anderen Quellen > aus Microsoft QueryAbrufen .
   
   
2. Führen Sie die Schritte des Abfrage-Assistenten aus. Wählen Sie im Abfrage-Assistenten – Ende des Bildschirms die Option Daten anzeigen oder Abfrage bearbeiten in Microsoft Query aus, und klicken Sie dann auf Fertig stellen. Das Microsoft Query-Fenster wird geöffnet und zeigt Ihre Abfrage an.
   
   
3. Klicken Sie auf> SQLanzeigen . Suchen Sie im daraufhin angezeigten SQL-Dialogfeld die WHERE-Klausel – eine Zeile, beginnend mit dem Wort WHERE, in der Regel am Ende des SQL-Codes. Wenn keine WHERE-Klausel vorhanden ist, fügen Sie eine hinzu, indem Sie am Ende der Abfrage Where in einer neuen Zeile eingeben.
   
   
4. Geben Sie nach WHERE den Feldnamen, einen Vergleichsoperator (=, <, >, wie usw.) und eine der folgenden Optionen ein:
   
   • Geben Sie bei einer Eingabeaufforderung für generischen Parameter ein Fragezeichen (?) ein. In der Eingabeaufforderung, die angezeigt wird, wenn die Abfrage ausgeführt wird, wird kein hilfreicher Ausdruck angezeigt.
     
     
     
     
     
     
   • Geben Sie einen in eckigen Klammern eingeschlossenen Ausdruck für eine Parametereingabeaufforderung ein, mit der Personen gültige Eingaben bereitstellen können. Der Ausdruck wird in der Eingabeaufforderung des Parameters angezeigt, wenn die Abfrage ausgeführt wird.
     
     
     
     
     
     
5. Nachdem Sie das Hinzufügen von Bedingungen mit Parametern zur WHERE-Klausel abgeschlossen haben, klicken Sie auf OK , um die Abfrage auszuführen. Excel fordert Sie auf, einen Wert für jeden Parameter anzugeben, dann zeigt Microsoft Query die Ergebnisse an.
   
   
6. Wenn Sie bereit zum Laden der Daten sind, schließen Sie das Microsoft Query-Fenster, um die Ergebnisse an Excel zurückzugeben. Das Dialogfelddaten importieren wird geöffnet.
   
   
   
   
   
   
7. Klicken Sie zum Überprüfen der Parameter auf Eigenschaften. Klicken Sie dann im Dialogfeld Verbindungseigenschaften auf der Registerkarte Definition auf Parameter.
   
   
   
   
   
   
8. Im Dialogfeld "Parameter" werden die in der Abfrage verwendeten Parameter angezeigt. Wählen Sie einen Parameter unter Parameter Name aus, um zu überprüfen oder zu ändern, wie der Parameterwert abgerufen wird. Sie können die Parametereingabeaufforderung ändern, einen bestimmten Wert eingeben oder einen Zellbezug angeben.
   
   
   
   
   
   
9. Klicken Sie auf OK , um Ihre Änderungen zu speichern und das Dialogfeld Parameter zu schließen, und klicken Sie dann im Dialogfelddaten importieren auf OK , um die Abfrageergebnisse in Excel anzuzeigen.
   
   

Ihre Arbeitsmappe verfügt nun über eine Parameterabfrage. Wenn Sie die Abfrage ausführen oder Ihre Datenverbindung aktualisieren, überprüft Excel den Parameter, um die WHERE-Klausel der Abfrage abzuschließen. Wenn der Parameter einen Wert anfordert, zeigt Excel das Dialogfeld Parameterwert eingeben an, um die Eingabe zu erfassen – Sie können einen Wert eingeben oder auf eine Zelle klicken, die den Wert enthält. Sie können auch angeben, dass der angegebene Wert oder Verweis immer verwendet werden soll, und wenn Sie einen Zellbezug verwenden, können Sie angeben, dass Excel die Datenverbindung automatisch aktualisieren soll (d. h., die Abfrage erneut auszuführen), wenn sich der Wert der angegebenen Zelle ändert.

Power Query-Beispiel 1: Verwenden eines Parameters zum Filtern von Daten
Hinweis: In diesem Thema wird davon ausgegangen, dass Sie wissen, wie Sie mithilfe von Power Query eine Verbindung mit einer Access-Datenbank erstellen. Weitere Informationen finden Sie unter Herstellen einer Verbindung mit einer Access-Datenbank.

Sie können Parameter in mehr Power Query-Szenarien verwenden als nur Daten filtern – jeder Schritt einer Power Query-Abfrage kann Parameter aufweisen. So können Sie beispielsweise einen Parameter verwenden, um Teile der Verbindungszeichenfolge im Quellschritt anzugeben, beispielsweise einen Dateinamen.

Power Query-Parameter haben Namen. Wenn Sie einen Parameter verwenden möchten, verweisen Sie in der Formel für einen Schritt auf den Namen. Angenommen, Sie möchten die Daten zu Webseiten überprüfen, die Sie verwalten, und die Daten nach Veröffentlichungsdatum filtern möchten. Sie können zwar immer nur die integrierten Filter in der AbfrageVorschau verwenden, doch mithilfe eines Parameters zum Bereitstellen eines Datums für die Filterung können Sie Zeit sparen und mehr Flexibilität erhalten. Gehen wir durch dieses Beispiel.

In einer leeren Arbeitsmappe erstellen wir eine Verbindung mit der Access-Datenbank, die die gewünschten Webdatenverkehr-Datensätze enthält, einschließlich der Felder, die angeben, wann jede Seite ursprünglich veröffentlicht wurde. In Power Query geladen, sieht es wie folgt aus:





Da wir nach Datum filtern möchten, ändern wir den Datentyp der Spalte, die Sie verwenden, FirstPublishDate. Da es sich um Datums-und Uhrzeitdaten in der Quelle handelt, ist es uns aber egal, welche Zeit der Veröffentlichung aufgetreten ist, und dass es möglicherweise langweilig wird – wir werden es also in den Datentyp Date ändern.





Als Nächstes erstellen wir einen Parameter zum Einschränken der Ergebnisse bis zum Datum, an dem die Seite ursprünglich veröffentlicht wurde. Klicken Sie auf Start > Parameter > Parameter Verwalten , um das Dialogfeld Parameter zu öffnen.





Klicken Sie auf neu, und im Formular wird ein neuer Parameter mit dem Namen parameter1 ohne weitere Informationen angezeigt.

Wir ändern einige Parametereigenschaften:

• Ändern des namens in FirstPubD
  
  
• Ändern Sie die Beschreibung auf das Datum, an dem die Seite erstmals veröffentlicht wurde.
  
  
• Typ in Datum ändern, damit der Parameter nur Datumswerte akzeptiert
  
  
• Stellen Sie den aktuellen Wert ein, damit der Parameter nicht alle Zeilen filtert, wenn wir keine eingabebereit gestellt haben, sondern 1/1/2010 verwenden.
  
  

Tipp: Der Name und die Beschreibung sollten genügend Kontext bereitstellen, damit die Benutzer verstehen, wie und warum der Parameter verwendet werden kann. Auch wenn Sie die einzige Person sind, die den Parameter verwenden wird, benötigen Sie möglicherweise eine Erinnerung von Zeit zu Zeit.

Klicken Sie auf OK , um den Parameter zu erstellen und im Power Query-Editor anzuzeigen.





Nun wird unser Parameter im Abfrage-Panel aufgelistet – wir können ihn dort auswählen, um ihn im Hauptfenster anzuzeigen, oder wir können mit der rechten Maustaste darauf klicken, um weitere Optionen anzuzeigen. Wenn ein Parameter ausgewählt ist, können wir den aktuellen Wert im Haupt Feld bearbeiten oder auf Parameter verwalten klicken, um die anderen Einstellungen zu ändern.

Nun können wir diesen Parameter in der ursprünglichen Abfrage verwenden. Wir klicken auf die ursprüngliche Abfrage im Abfragefenster, um Sie anzuzeigen. Wir möchten unseren Parameter verwenden, um die Ergebnisse basierend auf dem Datum der ersten Veröffentlichung zu filtern, daher wählen Sie als nächstes die Spalte FirstPublishDate aus, klicken auf den Filter/Sortierpfeil am rechten Rand der Spaltenüberschrift, zeigen auf Datumsfilterund klicken dann auf nach....





Im Dialogfeld Zeilen filtern wählen wir Parameter aus der Liste der Auswahlmöglichkeiten im Filter aus.





Geben Sie einen Wert ein, oder wählen Sie ihn durch eine Liste der verfügbaren Parameter aus. Es gibt nur einen, den wir gerade erstellt haben, FirstPubD.





Klicken Sie auf OK. Der Power Query-Editor lädt die Abfrage unter Verwendung des neuen Parameters als Filter.





Um den Parameter zu testen, ändern wir seinen Wert in 1/1/2018.





Wir aktualisieren die Abfrage, die nun nur Zeilen mit einer FirstPublishDate nach 1/1/2018 anzeigt.





Jetzt haben wir eine Abfrage, die nach Datum mithilfe eines Parameters filtert. Um die Ergebnisse nach FirstPublishDate zu filtern, müssen wir das Feld nicht mehr finden, klicken Sie auf den Pfeil Filtern/sortieren, und wählen Sie dann die Option nach... Filtern Sie den Typ, und geben Sie einen Datumswert ein – wir können einfach den Wert von FirstPubD ändern und unsere Abfrage aktualisieren. Darüber hinaus können wir den neuen Parameter wieder verwenden, beispielsweise wenn wir beschließen, eine andere Gruppe von Feldern aus der ursprünglichen Datenquelle in ein neues Arbeitsblatt zu ziehen, aber trotzdem FirstPubDate einbeziehen und zum Filtern der Ergebnisse verwenden möchten.

Power Query-Beispiel 2: Verwenden eines Zellenwerts auf einem Arbeitsblatt zum Filtern von Daten
Parameter sind eindeutig sehr nützlich, doch müssen wir den Wert des Parameters auch mit dem Power Query-Editor ändern. Wir möchten den Filterwert ändern können, ohne den Power Query-Editor öffnen zu müssen. Dazu erstellen wir eine Tabelle auf dem Arbeitsblatt, in der die Abfrage geladen wird, und eine neue Power Query-Verbindung mit der Tabelle, und verwenden Sie dann die neue Abfrage, um unsere Hauptabfrage zu filtern.

Auf dem Arbeitsblatt, in dem unsere Abfrage geladen wird, werden einige Zeilen oberhalb der importierten Daten eingefügt. Anschließend erstellen wir eine Excel-Tabelle mit einer Zeile, die unseren Parameterwert enthalten soll.





Um die neue Tabelle zum Filtern von Abfragen zu verwenden, müssen Sie in Power Query eine Verbindung herstellen. Wir erstellen eine Verbindung mit der Tabelle, indem Sie Sie auswählen und dann auf der Registerkarte Daten auf aus Tabelle/Bereich klicken. Die neue Verbindung wird geöffnet, und die neue Tabelle wird im Power Query-Editor angezeigt.





Da die Daten als Datum/Uhrzeit-Datentyp geladen werden, müssen wir Sie in den Date-Datentyp ändern, damit Sie unserem Parameter entspricht, und klicken dann auf Start > transformieren > Datentyp > Datum.





Wir benennen unsere Abfrage auch in eine aussagekräftigere als Tab. Um das zu verdeutlichen, wofür Sie sich entscheiden, nennen wir es FirstPubDate.





Da wir einen Wert übergeben möchten, nicht die Tabelle selbst, müssen wir einen Drilldown zum Date-Wert durchführen. Klicken Sie dazu mit der rechten Maustaste auf den Wert in den Vorschaudaten, und klicken Sie dann auf Drilldown.





In der Vorschau wird nun der Wert anstelle der Tabelle angezeigt.





Es ist nicht erforderlich, dass die Daten der neuen Abfrage überall geladen werden – die Daten sind bereits auf dem Arbeitsblatt, wo wir Sie haben möchten. Wir benötigen nur die Verbindung, damit Power Query den Parameterwert abrufen kann. So klicken wir auf Datei > Schließen & Load to... , um das Dialogfelddaten importieren zu öffnen, und wählen dann nur Verbindung erstellenaus.





Jetzt haben wir eine Abfrage mit dem Namen "FirstPubDate", die einen einzelnen Datumswert aus einer Tabelle auf dem Arbeitsblatt direkt über dem Punkt abruft, an dem die Hauptabfrage geladen wird. Nun müssen wir diese Abfrage nur als Parameter zum Filtern unserer Hauptabfrage verwenden. Daher öffnen wir die Hauptabfrage und bearbeiten den Schritt, der Zeilen mithilfe der FirstPublishDate-Spalte filtert. Wir erweitern die Bearbeitungsleiste und wählen den zuvor erstellten Parameter (FirstPubD) aus. Dann geben wir eine "a" nach FirstPubD ein, da der Name der neuen Abfrage mit den gleichen Buchstaben wie der Parameter beginnt, wird Sie von Power Query als Option zum auswählen angezeigt.





Wir wählen Sie aus, und klicken Sie dann auf eine Stelle außerhalb der Bearbeitungsleiste, um den Schritt anzuwenden.





Alles sieht richtig aus, daher beenden wir den Power Query-Editor und speichern unsere Änderungen. Um den Parameter zu testen, ändern wir auf dem Berichts Arbeitsblatt den Wert der Zelle in der Tabelle oben auf 5/4/2019, und aktualisieren dann die Verbindung, um die gefilterten Daten anzuzeigen.





Unser neuer Filter funktioniert! Daher speichern und schließen wir die Arbeitsmappe. Jetzt kann jeder, der die Arbeitsmappe verwendet, ein Datum der ersten Publikation angeben, das als Abfragefilter verwendet werden soll – genau dort auf dem Arbeitsblatt, in dem die Abfrage geladen wird.

Erstellen eines Parameters in Power Query

1. Klicken Sie auf Daten > & Daten transformieren > Daten > Starten des Power Query-Editorsabrufen.
   
   
2. Klicken Sie im Power Query-Editor auf Start > Parameter > Parameter verwalten.
   
   
3. Klicken Sie im Dialogfeld Parameter auf neu.
   
   
4. Setzen Sie die folgenden nach Bedarf:
   
   • Name : Dies sollte die Funktion des Parameters wiedergeben, aber möglichst kurz halten.
     
     
   • Beschreibung – Dies kann alle Details enthalten, die es den Benutzern ermöglichen, den Parameter richtig zu verwenden.
     
     
   • Erforderlich – wählen Sie diese Option aus, damit dieser Parameter einen Wert erfordert.
     
     
   • Type – gibt den Datentyp an, der für den Parameter erforderlich ist.
     
     
   • Vorgeschlagene Werte : Fügen Sie bei Bedarf eine Liste mit Werten hinzu, oder geben Sie eine Abfrage an, um Vorschläge für die eingabebereit zustellen.
     
     
   • Standardwert – dieser Wert wird nur angezeigt, wenn vorgeschlagene Werte auf eine Liste von Werten festgelegt sind, und gibt an, welches Listenelement der Standardwert ist.
     
     
   • Aktueller Wert – je nachdem, wo Sie den Parameter verwenden, gibt die Abfrage möglicherweise keine Ergebnisse zurück, wenn dies leer ist. Wenn erforderlich ausgewählt ist, darf der aktuelle Wert nicht leer sein.
     
     
5. Klicken Sie auf OK , um den Parameter zu erstellen.
   
   

Verwenden eines Parameters zum Filtern von Daten in Power Query

1. Öffnen Sie eine Abfrage im Power Query-Editor.
   
   
2. Klicken Sie auf den Pfeil am rechten Rand der Kopfzeile einer Spalte, die Sie zum Filtern Ihrer Daten verwenden möchten, und wählen Sie dann einen Filter aus dem angezeigten Menü aus.
   
   
3. Klicken Sie im Dialogfeld Zeilen filtern auf die Schaltfläche rechts neben der Filterbedingung, und führen Sie dann eine der folgenden Aktionen aus:
   
   • Wenn Sie einen vorhandenen Parameter verwenden möchten, klicken Sie auf Parameter, und wählen Sie dann in der Liste, die auf der rechten Seite angezeigt wird, den gewünschten Parameter aus.
     
     
   • Wenn Sie einen neuen Parameter verwenden möchten, klicken Sie auf neuer Parameter..., und erstellen Sie dann einen Parameter.
     
     

Verwenden eines Zellenwerts zum Filtern von Daten in Power Query

1. Erstellen Sie auf dem Arbeitsblatt, in dem die Abfrage, die Sie filtern möchten, eine Tabelle mit zwei Zellen: eine Kopfzeile und einen Wert.
   
   
2. Klicken Sie auf den Wert, und klicken Sie dann auf Daten > & Daten > aus Tabelle/Bereichtransformieren .
   
   
3. Nehmen Sie im Power Query-Editor alle Anpassungen an der Tabellen Verbindung vor (beispielsweise das Ändern des Datentyps oder des Namens), und klicken Sie dann auf Start > Schließen > & laden schließen, > & Laden schließen.
   
   
4. Klicken Sie im Dialogfelddaten importieren auf nur Verbindung erstellen, wählen Sie optional zum Datenmodell hinzufügenaus, und klicken Sie dann auf OK.
   
   
5. Öffnen Sie die Abfrage, die Sie filtern möchten, im Power Query-Editor.
   
   
6. Klicken Sie auf den Pfeil am rechten Rand der Kopfzeile der Spalte, die Sie zum Filtern der Daten verwenden möchten, und wählen Sie dann einen Filter aus dem angezeigten Menü aus.
   
   
7. Führen Sie eine der folgenden Aktionen aus:
   
   • Wählen Sie einen Wert aus der Dropdownliste der Werte aus (diese stammen aus den abgefragten Daten).
     
     
   • Wählen Sie einen Wert mithilfe der Schaltfläche am rechten Rand der Filterbedingung aus.
     
     
8. Klicken Sie auf den Pfeil am rechten Rand der Bearbeitungsleiste, um die gesamte Abfrage anzuzeigen.
   
   
9. Die Filterbedingung folgt dem jeweiligenWort:
   
   • Der Name der Spalte, die gefiltert wird, wird in eckigen Klammern angezeigt.
     
     
   • Der Vergleichsoperator folgt unmittelbar auf den Spaltennamen.
     
     
   • Der Filterwert folgt unmittelbar dem Vergleichsoperator und endet bei der schließenden Klammer. Wählen Sie diesen gesamten Wert aus.
     
     
10. Beginnen Sie mit der Eingabe des Namens der soeben erstellten Tabellen Verbindung, und wählen Sie Sie in der angezeigten Liste aus.
    
    
11. Klicken Sie auf Start > Schließen > & Laden schließen.
    
    Ihre Abfrage verwendet jetzt den Wert in der von Ihnen erstellten Tabelle, um die Abfrageergebnisse zu filtern. Wenn Sie einen neuen Wert verwenden möchten, bearbeiten Sie den Zellinhalt, und aktualisieren Sie dann die Abfrage.
    
    

Siehe auch
Erstellen von Dropdownlisten

:)