Power Query: If-Anweisung – verschachtelte Ifs und mehrere Bedingungen
Im Excel Power QueryDie IF-Anweisung ist eine der beliebtesten Funktionen, um eine Bedingung zu prüfen und einen bestimmten Wert zurückzugeben, je nachdem, ob das Ergebnis TRUE oder FALSE ist. Es gibt einige Unterschiede zwischen dieser if-Anweisung und der IF-Funktion von Excel. In diesem Tutorial werde ich die Syntax dieser if-Anweisung sowie einige einfache und komplexe Beispiele für Sie vorstellen.
Grundlegende if-Anweisungssyntax von Power Query
Power Query if-Anweisung mit einer bedingten Spalte
Power Query if-Anweisung durch Schreiben des M-Codes
Grundlegende if-Anweisungssyntax von Power Query
In Power Query, die Syntax lautet:
- Logik Test: Die Bedingung, die Sie testen möchten.
- value_if_true: Der zurückzugebende Wert, wenn das Ergebnis TRUE ist.
- value_if_false: Der zurückzugebende Wert, wenn das Ergebnis FALSE ist.
Im Excel Power Querygibt es zwei Möglichkeiten, diese Art von bedingter Logik zu erstellen:
- Verwenden der Funktion „Bedingte Spalte“ für einige grundlegende Szenarien;
- Schreiben von M-Code für fortgeschrittenere Szenarien.
Im folgenden Abschnitt werde ich über einige Beispiele für die Verwendung dieser if-Anweisung sprechen.
Power Query if-Anweisung mit einer bedingten Spalte
Beispiel 1: Einfache if-Anweisung
Hier werde ich vorstellen, wie diese if-Anweisung verwendet wird Power Query. Ich habe zum Beispiel einen folgenden Produktbericht, in dem ein Rabatt von 50 % angezeigt wird, wenn der Produktstatus „Alt“ lautet. Wenn der Produktstatus „Neu“ ist, wird ein Rabatt von 20 % angezeigt, wie in den folgenden Screenshots gezeigt.
1. Wählen Sie die Datentabelle aus dem Arbeitsblatt aus und klicken Sie dann in Excel 2019 und Excel 365 auf Datum > Aus Tabelle/Bereich, siehe Screenshot:
Note: Klicken Sie in Excel 2016 und Excel 2021 auf Datum > Aus Tabelle, siehe Screenshot:
2. Dann im geöffneten Power Query Herausgeber Klicken Sie im Fenster In Spalte > Bedingte Spalte, siehe Screenshot:
3. In der herausgesprungen Bedingte Spalte hinzufügen Dialog, führen Sie bitte die folgenden Operationen aus:
- Neuer Spaltenname: Geben Sie einen Namen für die neue Spalte ein;
- Geben Sie dann die gewünschten Kriterien an. Zum Beispiel werde ich angeben Wenn Status gleich Alt ist, dann 50 %, sonst 20 %;
- Spaltenname: Die Spalte, anhand derer Ihre if-Bedingung ausgewertet wird. Hier wähle ich Status aus.
- Operator: Zu verwendende bedingte Logik. Die Optionen unterscheiden sich je nach Datentyp des ausgewählten Spaltennamens.
- Text: beginnt mit, beginnt nicht mit, ist gleich, enthält usw.
- Zahlen: ist gleich, ist nicht gleich, ist größer als oder gleich usw.
- Datum: ist vorher, ist danach, ist gleich, ist nicht gleich usw.
- Wert: Der spezifische Wert, mit dem Ihre Bewertung verglichen werden soll. Er bildet zusammen mit dem Spaltennamen und dem Operator eine Bedingung.
- Output: Der zurückzugebende Wert, wenn die Bedingung erfüllt ist.
- sonst: Ein weiterer Wert, der zurückgegeben wird, wenn die Bedingung falsch ist.
4. Dann klick OK Taste um zurück zu kommen Power Query Herausgeber Fenster. Jetzt ein neues Rabatt Spalte hinzugefügt, siehe Screenshot:
5. Wenn Sie die Zahlen in Prozent formatieren möchten, klicken Sie einfach ABC123 Symbol von der Rabatt Spaltenüberschrift und wählen Sie aus Prozentsatz siehe Screenshot:
6. Abschließend bitte klicken Home > Schließen & Laden > Schließen & Laden um diese Daten in ein neues Arbeitsblatt zu laden.
Beispiel 2: Komplexe if-Anweisung
Mit dieser Option für bedingte Spalten können Sie auch zwei oder mehr Bedingungen in die einfügen Bedingte Spalte hinzufügen Dialog. Bitte gehen Sie so vor:
1. Wählen Sie die Datentabelle aus und gehen Sie zu Power Query Herausgeber Fenster durch Klicken Datum > Aus Tabelle/Bereich. Klicken Sie im neuen Fenster auf In Spalte > Bedingte Spalte.
2. In der herausgesprungen Bedingte Spalte hinzufügen Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
- Geben Sie einen Namen für die neue Spalte in das ein Neuer Spaltenname Textfeld;
- Geben Sie die ersten Kriterien in das erste Kriterienfeld ein und klicken Sie dann auf Klausel hinzufügen Schaltfläche, um nach Bedarf weitere Kriterienfelder hinzuzufügen.
3. Klicken Sie nach Abschluss der Kriterien auf OK Taste um zurück zu kommen Power Query Herausgeber Fenster. Jetzt erhalten Sie eine neue Spalte mit dem entsprechenden Ergebnis, das Sie benötigen. Siehe Screenshot:
4. Zum Schluss bitte klicken Home > Schließen & Laden > Schließen & Laden um diese Daten in ein neues Arbeitsblatt zu laden.
Power Query if-Anweisung durch Schreiben des M-Codes
Normalerweise ist Conditional Column für einige grundlegende Szenarien hilfreich. Manchmal müssen Sie möglicherweise mehrere Bedingungen mit UND- oder ODER-Logik verwenden. In diesem Fall müssen Sie für komplexere Szenarien M-Code in eine benutzerdefinierte Spalte schreiben.
Beispiel 1: Einfache if-Anweisung
Nehmen Sie die ersten Daten als Beispiel, wenn der Produktstatus Alt ist und einen Rabatt von 50 % anzeigt; wenn der Produktstatus Neu ist, wird ein Rabatt von 20 % angezeigt. Gehen Sie zum Schreiben des M-Codes wie folgt vor:
1. Wählen Sie die Tabelle aus und klicken Sie auf Datum > Aus Tabelle/Bereich auf dem Sprung Power Query Herausgeber Fenster.
2. Klicken Sie im geöffneten Fenster auf In Spalte > Benutzerdefinierte Spalte, siehe Screenshot:
3. In der herausgesprungen Benutzerdefinierte Spalte Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
- Geben Sie einen Namen für die neue Spalte in das ein Neuer Spaltenname Textfeld;
- Geben Sie dann diese Formel ein: if [Status] = "Alt" then "50%" else "20% " in die Benutzerdefinierte Spalte Formel Box.
4. Dann klick OK um dieses Dialogfeld zu schließen. Jetzt erhalten Sie nach Bedarf das folgende Ergebnis:
5. Endlich, klick Home > Schließen & Laden > Schließen & Laden um diese Daten in ein neues Arbeitsblatt zu laden.
Beispiel 2: Komplexe if-Anweisung
Normalerweise können Sie zum Testen der Unterbedingungen mehrere if-Anweisungen verschachteln. Zum Beispiel habe ich die folgende Datentabelle. Wenn es sich bei dem Produkt um „Kleid“ handelt, geben Sie 50 % Rabatt auf den ursprünglichen Preis; wenn das Produkt „Sweater“ oder „Hoodie“ ist, gewähren Sie 20 % Rabatt auf den ursprünglichen Preis; und andere Produkte behalten den ursprünglichen Preis.
1. Wählen Sie die Datentabelle aus und klicken Sie auf Datum > Aus Tabelle/Bereich auf dem Sprung Power Query Herausgeber Fenster.
2. Klicken Sie im geöffneten Fenster auf In Spalte > Benutzerdefinierte Spalte. In der geöffneten Benutzerdefinierte Spalte Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
- Geben Sie einen Namen für die neue Spalte in das ein Neuer Spaltenname Textfeld;
- Geben Sie dann die folgende Formel in die ein Benutzerdefinierte Spalte Formel Box.
- = wenn [Produkt] = "Kleid" dann [Preis] * 0.5 sonst
wenn [Produkt] = "Pullover" dann [Preis] * 0.8 sonst
Wenn [Produkt] = "Hoodie", dann [Preis] * 0.8
sonst [Preis]
3. Und dann klicken OK Klicken Sie auf die Schaltfläche, um zum zurückzukehren Power Query Herausgeber Fenster, und Sie erhalten eine neue Spalte mit den benötigten Daten, siehe Screenshot:
4. Endlich, klick Home > Schließen & Laden > Schließen & Laden um diese Daten in ein neues Arbeitsblatt zu laden.
Die ODER-Logik führt mehrere logische Tests durch, und das wahre Ergebnis wird zurückgegeben, wenn einer der logischen Tests wahr ist. Die Syntax lautet:
Angenommen, ich habe die folgende Tabelle, jetzt möchte ich eine neue Spaltenanzeige wie folgt: Wenn das Produkt „Kleid“ oder „T-Shirt“ ist, dann ist die Marke „AAA“, die Marke anderer Produkte ist „BBB“.
1. Wählen Sie die Datentabelle aus und klicken Sie auf Datum > Aus Tabelle/Bereich auf dem Sprung Power Query Herausgeber Fenster.
2. Klicken Sie im geöffneten Fenster auf In Spalte > Benutzerdefinierte Spalteim geöffneten Benutzerdefinierte Spalte Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
- Geben Sie einen Namen für die neue Spalte in das ein Neuer Spaltenname Textfeld;
- Geben Sie dann die folgende Formel in die ein Benutzerdefinierte Spaltenformel Box.
- = wenn [Produkt] = „Kleid“ oder [Produkt] = „T-Shirt“, dann „AAA“
Sonst "BBB"
3. Und dann klicken OK Klicken Sie auf die Schaltfläche, um zum zurückzukehren Power Query Herausgeber Fenster, und Sie erhalten eine neue Spalte mit den benötigten Daten, siehe Screenshot:
4. Abschließend klicken Sie auf Home > Schließen & Laden > Schließen & Laden um diese Daten in ein neues Arbeitsblatt zu laden.
Die AND-Logik führt mehrere logische Tests innerhalb einer einzigen if-Anweisung durch. Alle Tests müssen wahr sein, damit das wahre Ergebnis zurückgegeben wird. Wenn einer der Tests falsch ist, wird das falsche Ergebnis zurückgegeben. Die Syntax lautet:
Nehmen Sie zum Beispiel die obigen Daten, ich möchte, dass eine neue Spalte wie folgt angezeigt wird: Wenn das Produkt „Kleid“ ist und mehr als 300 bestellen, dann geben Sie 50 % Rabatt auf den ursprünglichen Preis; andernfalls behalten Sie den ursprünglichen Preis bei.
1. Wählen Sie die Datentabelle aus und klicken Sie auf Datum > Aus Tabelle/Bereich auf dem Sprung Power Query Herausgeber Fenster.
2. Klicken Sie im geöffneten Fenster auf In Spalte > Benutzerdefinierte Spalte. In der geöffneten Benutzerdefinierte Spalte Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
- Geben Sie einen Namen für die neue Spalte in das ein Neuer Spaltenname Textfeld;
- Geben Sie dann die folgende Formel in die ein Benutzerdefinierte Spaltenformel Box.
- = wenn [Produkt] ="Kleid" und [Bestellung] > 300, dann [Preis]*0.5
sonst [Preis]
3. Dann klick OK Klicken Sie auf die Schaltfläche, um zum zurückzukehren Power Query Herausgeber Fenster, und Sie erhalten eine neue Spalte mit den benötigten Daten, siehe Screenshot:
4. Abschließend sollten Sie diese Daten per Klick in ein neues Arbeitsblatt laden Home > Schließen & Laden > Schließen & Laden.
If-Anweisung mit ODER- und UND-Logik
Okay, die vorherigen Beispiele sind für uns leicht zu verstehen. Jetzt machen wir es schwieriger. Sie können UND und ODER kombinieren, um jede erdenkliche Bedingung zu bilden. Bei diesem Typ können Sie Klammern in der Formel verwenden, um komplexe Regeln zu definieren.
Nehmen Sie auch die obigen Daten als Beispiel, nehmen Sie an, ich möchte, dass eine neue Spalte wie folgt angezeigt wird: Wenn das Produkt „Kleid“ ist und seine Bestellung größer als 300 ist, oder das Produkt „Hose“ ist und seine Bestellung größer als 300 ist, dann zeigen „A+“, andernfalls „Andere“ anzeigen.
1. Wählen Sie die Datentabelle aus und klicken Sie auf Datum > Aus Tabelle/Bereich auf dem Sprung Power Query Herausgeber Fenster.
2. Klicken Sie im geöffneten Fenster auf In Spalte > Benutzerdefinierte Spalte. In der geöffneten Benutzerdefinierte Spalte Im Dialogfeld führen Sie bitte die folgenden Vorgänge aus:
- Geben Sie einen Namen für die neue Spalte in das ein Neuer Spaltenname Textfeld;
- Geben Sie dann die folgende Formel in die ein Benutzerdefinierte Spaltenformel Box.
- =if ([Produkt] = "Kleid" und [Bestellung] > 300 ) oder
([Produkt] = "Hosen" und [Bestellung] > 300 )
dann "A+"
sonst "Andere"
3. Dann klick OK Klicken Sie auf die Schaltfläche, um zum zurückzukehren Power Query Herausgeber Fenster, und Sie erhalten eine neue Spalte mit den benötigten Daten, siehe Screenshot:
4. Abschließend sollten Sie diese Daten per Klick in ein neues Arbeitsblatt laden Home > Schließen & Laden > Schließen & Laden.
Im Formelfeld Benutzerdefinierte Spalte können Sie die folgenden logischen Operatoren verwenden:
- = : Gleich
- <> : Ungleich
- > : Größer als
- >= : Größer als oder gleich
- < : Weniger als
- <= : Kleiner als oder gleich
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
