Power Query: If-instructie - geneste ifs en meerdere voorwaarden
in Excel Power Query, is de IF-instructie een van de meest populaire functies om een voorwaarde te controleren en een specifieke waarde te retourneren, afhankelijk van of het resultaat WAAR of ONWAAR is. Er zijn enkele verschillen tussen deze if-instructie en de IF-functie van Excel. In deze tutorial zal ik de syntaxis van deze if-instructie en enkele eenvoudige en complexe voorbeelden voor je introduceren.
Basis if-instructiesyntaxis van Power Query
Power Query if-instructie met behulp van een voorwaardelijke kolom
Power Query if-instructie door de M-code te schrijven
Basis if-instructiesyntaxis van Power Query
In Power Query, de syntaxis is:
- logische test: De aandoening die u wilt testen.
- waarde_indien_waar: De waarde die moet worden geretourneerd als het resultaat WAAR is.
- waarde_als_onwaar: De waarde die moet worden geretourneerd als het resultaat ONWAAR is.
in Excel Power Query, zijn er twee manieren om dit type voorwaardelijke logica te creëren:
- De voorwaardelijke kolomfunctie gebruiken voor enkele basisscenario's;
- M-code schrijven voor meer geavanceerde scenario's.
In het volgende gedeelte zal ik het hebben over enkele voorbeelden van het gebruik van dit if-statement.
Power Query if-instructie met behulp van een voorwaardelijke kolom
Voorbeeld 1: Basic if-statement
Hier zal ik introduceren hoe je deze if-instructie kunt gebruiken Power Query. Ik heb bijvoorbeeld een volgend productrapport, als de productstatus Oud is, met een korting van 50%; als de productstatus Nieuw is, wordt een korting van 20% weergegeven, zoals hieronder weergegeven schermafbeeldingen.
1. Selecteer de gegevenstabel in het werkblad en klik vervolgens in Excel 2019 en Excel 365 op Data > Van tafel/bereik, zie screenshot:
Note: Klik in Excel 2016 en Excel 2021 op Data > Van tafel, zie screenshot:
2. Dan, in het geopende Power Query editor venster klikt Kolom toevoegen > Voorwaardelijke kolom, zie screenshot:
3. In de pop-out Voorwaardelijke kolom toevoegen dialoog, voer de volgende bewerkingen uit:
- Nieuwe kolomnaam: Voer een naam in voor de nieuwe kolom;
- Geef vervolgens de criteria op die u nodig hebt. Ik zal bijvoorbeeld specificeren Als Status gelijk is aan Oud dan 50% anders 20%;
- Kolomnaam: De kolom om uw if-voorwaarde tegen te evalueren. Hier selecteer ik Status.
- Operator: Voorwaardelijke logica om te gebruiken. De opties verschillen afhankelijk van het gegevenstype van de geselecteerde kolomnaam.
- Tekst: begint met, begint niet met, is gelijk aan, bevat, enz.
- Aantallen: is gelijk aan, is niet gelijk aan, is groter dan of gelijk aan, etc.
- Datum: is voor, is na, is gelijk aan, is niet gelijk aan, etc.
- Waarde: De specifieke waarde om uw evaluatie mee te vergelijken. Het vormt samen met de kolomnaam en operator een voorwaarde.
- uitgang: De waarde die moet worden geretourneerd als aan de voorwaarde is voldaan.
- Anders: Een andere waarde die moet worden geretourneerd als de voorwaarde onwaar is.
4. Dan klikken OK knop om terug te gaan naar de Power Query editor venster. Nu, een nieuwe Korting kolom is toegevoegd, zie screenshot:
5. Als u de getallen wilt opmaken in procenten, klikt u gewoon op ABC123 pictogram van de Korting kolomkop en kies Percentage zoals je nodig hebt, zie screenshot:
6. Klik tot slot Home > Sluiten en laden > Sluiten en laden om deze gegevens naar een nieuw werkblad te laden.
Voorbeeld 2: complexe if-opdracht
Met deze optie Voorwaardelijke kolom kunt u ook twee of meer voorwaarden invoegen in de Voorwaardelijke kolom toevoegen dialoog. Doe dit als volgt:
1. Selecteer de gegevenstabel en ga naar de Power Query editor venster door te klikken Data > Van tafel/bereik. Klik in het nieuwe venster op Kolom toevoegen > Voorwaardelijke kolom.
2. In de pop-out Voorwaardelijke kolom toevoegen dialoogvenster, voer dan de volgende bewerkingen uit:
- Voer een naam in voor de nieuwe kolom in het Nieuwe kolomnaam tekstvak;
- Geef de eerste criteria op in het eerste criteriaveld en klik vervolgens op Clausule toevoegen knop om naar behoefte andere criteriavelden toe te voegen.
3. Na het voltooien van de criteria, klikt u op OK knop om terug te gaan naar de Power Query editor venster. Nu krijgt u een nieuwe kolom met het overeenkomstige resultaat dat u nodig hebt. Zie screenshot:
4. Klik ten slotte alstublieft Home > Sluiten en laden > Sluiten en laden om deze gegevens naar een nieuw werkblad te laden.
Power Query if-instructie door de M-code te schrijven
Normaal gesproken is de voorwaardelijke kolom nuttig voor sommige basisscenario's. Soms moet u mogelijk meerdere voorwaarden gebruiken met EN- of OF-logica. In dit geval moet u M-code in een aangepaste kolom schrijven voor complexere scenario's.
Voorbeeld 1: Basic if-statement
Neem de eerste gegevens als voorbeeld, als de productstatus Oud is, wordt een korting van 50% weergegeven; als de productstatus Nieuw is, wordt 20% korting weergegeven. Ga als volgt te werk om de M-code te schrijven:
1. Selecteer de tafel en klik Data > Van tafel/bereik naar de Power Query editor venster.
2. Klik in het geopende venster op Kolom toevoegen > Aangepaste kolom, zie screenshot:
3. In de pop-out Aangepaste kolom dialoogvenster, voer dan de volgende bewerkingen uit:
- Voer een naam in voor de nieuwe kolom in het Nieuwe kolomnaam tekstvak;
- Voer vervolgens deze formule in: als [Status] = "Oud" dan "50%" anders "20%" in de Aangepaste kolom formule doos.
4. Dan klikken OK om dit dialoogvenster te sluiten. Nu krijgt u het volgende resultaat zoals u nodig heeft:
5. Klik ten slotte op Home > Sluiten en laden > Sluiten en laden om deze gegevens naar een nieuw werkblad te laden.
Voorbeeld 2: complexe if-opdracht
Gewoonlijk kunt u meerdere if-statements nesten om de subvoorwaarden te testen. Ik heb bijvoorbeeld de onderstaande gegevenstabel. Als het product "Jurk" is, geef dan 50% korting op de oorspronkelijke prijs; als het product "Sweater" of "Hoodie" is, geef dan 20% korting op de oorspronkelijke prijs; en andere producten behouden de oorspronkelijke prijs.
1. Selecteer de gegevenstabel en klik Data > Van tafel/bereik naar de Power Query editor venster.
2. Klik in het geopende venster op Kolom toevoegen > Aangepaste kolom. In het geopende Aangepaste kolom dialoogvenster, voer dan de volgende bewerkingen uit:
- Voer een naam in voor de nieuwe kolom in het Nieuwe kolomnaam tekstvak;
- Voer vervolgens de onderstaande formule in de Aangepaste kolom formule doos.
- = als [Product] = "Jurk" dan [Prijs] * 0.5 anders
als [Product] = "Trui" dan [Prijs] * 0.8 anders
als [Product] = "Hoodie" dan [Prijs] * 0.8
anders [Prijs]
3. En klik dan OK knop om terug te gaan naar de Power Query editor venster, en je krijgt een nieuwe kolom met de gegevens die je nodig hebt, zie screenshot:
4. Klik ten slotte op Home > Sluiten en laden > Sluiten en laden om deze gegevens naar een nieuw werkblad te laden.
De OR-logica voert meerdere logische tests uit en het ware resultaat wordt geretourneerd als een van de logische tests correct is. De syntaxis is:
Stel dat ik de onderstaande tabel heb, nu wil ik een nieuwe kolomweergave als: als het product "Jurk" of "T-shirt" is, dan is het merk "AAA", het merk van andere producten is "BBB".
1. Selecteer de gegevenstabel en klik Data > Van tafel/bereik naar de Power Query editor venster.
2. Klik in het geopende venster op Kolom toevoegen > Aangepaste kolom, in het geopende Aangepaste kolom dialoogvenster, voer dan de volgende bewerkingen uit:
- Voer een naam in voor de nieuwe kolom in het Nieuwe kolomnaam tekstvak;
- Voer vervolgens de onderstaande formule in de Aangepaste kolomformule doos.
- = als [Product] = "Jurk" of [Product] = "T-shirt" dan "AAA"
anders "BBB"
3. En klik dan OK knop om terug te gaan naar de Power Query editor venster, en je krijgt een nieuwe kolom met de gegevens die je nodig hebt, zie screenshot:
4. Klik ten slotte op Home > Sluiten en laden > Sluiten en laden om deze gegevens naar een nieuw werkblad te laden.
De AND-logica voert meerdere logische tests uit binnen één if-statement. Alle tests moeten waar zijn om het echte resultaat te retourneren. Als een van de tests onwaar is, wordt het resultaat onwaar geretourneerd. De syntaxis is:
Neem bijvoorbeeld de bovenstaande gegevens, ik wil een nieuwe kolom weergeven als: als het product "Kleding" is en bestelt u meer dan 300, geef dan 50% korting op de oorspronkelijke prijs; anders de oorspronkelijke prijs behouden.
1. Selecteer de gegevenstabel en klik Data > Van tafel/bereik naar de Power Query editor venster.
2. Klik in het geopende venster op Kolom toevoegen > Aangepaste kolom. In het geopende Aangepaste kolom dialoogvenster, voer dan de volgende bewerkingen uit:
- Voer een naam in voor de nieuwe kolom in het Nieuwe kolomnaam tekstvak;
- Voer vervolgens de onderstaande formule in de Aangepaste kolomformule doos.
- = als [Product] = "Kleding" en [Bestelling] > 300, dan [Prijs]*0.5
anders [Prijs]
3. Dan klikken OK knop om terug te gaan naar de Power Query editor venster, en je krijgt een nieuwe kolom met de gegevens die je nodig hebt, zie screenshot:
4. Ten slotte moet u deze gegevens in een nieuw werkblad laden door erop te klikken Home > Sluiten en laden > Sluiten en laden.
If-instructie met OR- en AND-logica
Oké, de voorgaande voorbeelden zijn voor ons gemakkelijk te begrijpen. Laten we het nu moeilijker maken. Je kunt EN en OF combineren om elke denkbare voorwaarde te vormen. In dit type kunt u haakjes in de formule gebruiken om complexe regels te definiëren.
Neem ook de bovenstaande gegevens als voorbeeld, stel dat ik een nieuwe kolom wil die wordt weergegeven als: als het product "Jurk" is en de bestelling is groter dan 300, of het product is "Broek" en de bestelling is groter dan 300, toon dan "A+", geef anders "Overig" weer.
1. Selecteer de gegevenstabel en klik Data > Van tafel/bereik naar de Power Query editor venster.
2. Klik in het geopende venster op Kolom toevoegen > Aangepaste kolom. In het geopende Aangepaste kolom dialoogvenster, voer dan de volgende bewerkingen uit:
- Voer een naam in voor de nieuwe kolom in het Nieuwe kolomnaam tekstvak;
- Voer vervolgens de onderstaande formule in de Aangepaste kolomformule doos.
- =if ([Product] = "Jurk" en [Bestelling] > 300 ) of
([Product] = "Broek" en [Bestelling] > 300 )
dan "A+"
anders "anders"
3. Dan klikken OK knop om terug te gaan naar de Power Query editor venster, en je krijgt een nieuwe kolom met de gegevens die je nodig hebt, zie screenshot:
4. Ten slotte moet u deze gegevens in een nieuw werkblad laden door erop te klikken Home > Sluiten en laden > Sluiten en laden.
In het vak Aangepaste kolomformule kunt u de volgende logische operatoren gebruiken:
- = : Is gelijk aan
- <> : Niet gelijk aan
- > : Groter dan
- >= : Groter dan of gelijk aan
- < : Minder dan
- <= : kleiner dan of gelijk aan
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!