Power Query: If-sætning - indlejrede hvis og flere betingelser
I Excel Power Query, IF-sætningen er en af de mest populære funktioner til at kontrollere en betingelse og returnere en bestemt værdi afhængigt af, om resultatet er SAND eller FALSK. Der er nogle forskelle mellem denne if-sætning og IF-funktionen i Excel. I denne tutorial vil jeg introducere syntaksen for denne if-sætning og nogle enkle og komplekse eksempler til dig.
Grundlæggende hvis sætningssyntaks af Power Query
Power Query if-udsagn ved hjælp af en betinget kolonne
Power Query if-udsagn ved at skrive M-koden
Grundlæggende hvis sætningssyntaks af Power Query
In Power Query, syntaksen er:
- logisk_test: Den tilstand, du vil teste.
- værdi_hvis_sand: Værdien, der skal returneres, hvis resultatet er SAND.
- værdi_hvis_falsk: Værdien, der skal returneres, hvis resultatet er FALSK.
I Excel Power Query, er der to måder at oprette denne type betinget logik på:
- Brug af funktionen Betinget kolonne til nogle grundlæggende scenarier;
- Skriver M-kode til mere avancerede scenarier.
I det følgende afsnit vil jeg tale om nogle eksempler på brug af denne if-sætning.
Power Query if-udsagn ved hjælp af en betinget kolonne
Eksempel 1: Grundlæggende if-sætning
Her vil jeg introducere, hvordan du bruger denne if-sætning i Power Query. For eksempel har jeg en følgende produktrapport, hvis produktstatus er Gammel, der viser 50 % rabat; hvis produktstatus er Ny, viser en 20% rabat som vist nedenfor skærmbilleder.
1. Vælg datatabellen fra regnearket, og klik derefter i Excel 2019 og Excel 365 data > Fra tabel/område, se skærmbillede:
Bemærk: Klik på i Excel 2016 og Excel 2021 data > Fra tabel, se skærmbillede:
2. Derefter i åbnet Power Query editor vindue, skal du klikke på Tilføj kolonne > Betinget kolonne, se skærmbillede:
3. I poppet ud Tilføj betinget kolonne dialog, skal du udføre følgende handlinger:
- Nyt kolonnenavn: Indtast et navn til den nye kolonne;
- Angiv derefter de kriterier, du har brug for. For eksempel vil jeg specificere Hvis status er lig gammel, så 50% ellers 20%;
- Kolonnenavn: Kolonnen til at evaluere din if-betingelse i forhold til. Her vælger jeg Status.
- Operatør: Betinget logik at bruge. Indstillingerne vil variere afhængigt af datatypen for det valgte kolonnenavn.
- tekst: begynder med, begynder ikke med, er lig med, indeholder osv.
- Tal: er lig, er ikke lig, er større end eller lig med osv.
- Dato: er før, er efter, er lig med, er ikke lig osv.
- Værdi: Den specifikke værdi, du skal sammenligne din evaluering med. Det udgør sammen med kolonnenavnet og operatøren en betingelse.
- Produktion: Værdien, der skal returneres, hvis betingelsen er opfyldt.
- Else: Endnu en værdi, der skal returneres, når betingelsen er falsk.
4. Klik derefter på OK knappen for at vende tilbage til Power Query editor vindue. Nu en ny Rabat kolonne tilføjes, se skærmbillede:
5. Hvis du vil formatere tallene til procent, skal du blot klikke ABC123 ikon fra Rabat kolonneoverskrift, og vælg Procent som du har brug for, se skærmbillede:
6. Klik endelig Hjem > Luk og indlæs > Luk og indlæs for at indlæse disse data til et nyt regneark.
Eksempel 2: Kompleks if-sætning
Med denne betingede kolonne-indstilling kan du også indsætte to eller flere betingelser i Tilføj betinget kolonne dialog. Gør venligst som dette:
1. Vælg datatabellen, og gå til Power Query editor vindue ved at klikke data > Fra tabel/område. Klik i det nye vindue Tilføj kolonne > Betinget kolonne.
2. I poppet ud Tilføj betinget kolonne dialogboksen, skal du udføre følgende handlinger:
- Indtast et navn til den nye kolonne i Nyt kolonnenavn tekstboks;
- Angiv de første kriterier i det første kriteriefelt, og klik derefter Tilføj klausul knappen for at tilføje andre kriteriefelter efter behov.
3. Når du er færdig med kriterierne, skal du klikke OK knappen for at vende tilbage til Power Query editor vindue. Nu får du en ny kolonne med det tilsvarende resultat, du har brug for. Se skærmbillede:
4. Klik endelig Hjem > Luk og indlæs > Luk og indlæs for at indlæse disse data til et nyt regneark.
Power Query if-udsagn ved at skrive M-koden
Normalt er Conditional Column nyttig til nogle grundlæggende scenarier. Nogle gange skal du muligvis bruge flere betingelser med OG- eller ELLER-logik. I dette tilfælde skal du skrive M-kode inde i en brugerdefineret kolonne for mere komplekse scenarier.
Eksempel 1: Grundlæggende if-sætning
Tag de første data som eksempel, hvis produktstatus er Gammel, viser en 50% rabat; hvis produktstatus er Ny, viser 20 % rabat. For at skrive M-koden skal du gøre følgende:
1. Vælg tabellen, og klik data > Fra tabel/område at gå til Power Query editor vindue.
2. Klik i det åbne vindue Tilføj kolonne > Brugerdefineret kolonne, se skærmbillede:
3. I poppet ud Brugerdefineret kolonne dialogboksen, skal du udføre følgende handlinger:
- Indtast et navn til den nye kolonne i Nyt kolonnenavn tekstboks;
- Indtast derefter denne formel: if [Status] = "Gamle" derefter "50% " ellers "20% " ind i Brugerdefineret kolonne Formula boks.
4. Klik derefter på OK for at lukke denne dialogboks. Nu får du følgende resultat efter behov:
5. Endelig skal du klikke på Hjem > Luk og indlæs > Luk og indlæs for at indlæse disse data til et nyt regneark.
Eksempel 2: Kompleks if-sætning
Normalt, for at teste underbetingelserne, kan du indlejre flere if-sætninger. For eksempel har jeg nedenstående datatabel. Hvis produktet er "Dress", giv 50% rabat for den oprindelige pris; hvis produktet er "Sweater" eller "Hoodie", giv 20% rabat for den oprindelige pris; og andre produkter holder den oprindelige pris.
1. Vælg datatabellen, og klik data > Fra tabel/område at gå til Power Query editor vindue.
2. Klik i det åbne vindue Tilføj kolonne > Brugerdefineret kolonne. I åbnet Brugerdefineret kolonne dialogboksen, skal du udføre følgende handlinger:
- Indtast et navn til den nye kolonne i Nyt kolonnenavn tekstboks;
- Indtast derefter nedenstående formel i Brugerdefineret kolonne Formula boks.
- = hvis [Produkt] = "Kjole" så [Pris] * 0.5 andet
hvis [Produkt] = "Sweater" så [Pris] * 0.8 andet
hvis [Produkt] = "Hættetrøje" så [Pris] * 0.8
andet [Pris]
3. Klik derefter på OK knappen for at gå tilbage til Power Query editor vindue, og du får en ny kolonne med de data, du har brug for, se skærmbillede:
4. Endelig skal du klikke på Hjem > Luk og indlæs > Luk og indlæs for at indlæse disse data til et nyt regneark.
OR-logikken udfører flere logiske test, og det sande resultat vil returnere, hvis nogen af de logiske test er sande. Syntaksen er:
Antag, at jeg har nedenstående tabel, nu vil jeg have en ny kolonnevisning som: hvis produktet er "Kjole" eller "T-shirt", så er mærket "AAA", mærket for andre produkter er "BBB".
1. Vælg datatabellen, og klik data > Fra tabel/område at gå til Power Query editor vindue.
2. Klik i det åbne vindue Tilføj kolonne > Brugerdefineret kolonne, i det åbnede Brugerdefineret kolonne dialogboksen, skal du udføre følgende handlinger:
- Indtast et navn til den nye kolonne i Nyt kolonnenavn tekstboks;
- Indtast derefter nedenstående formel i Brugerdefineret kolonneformel boks.
- = hvis [Produkt] = "Kjole" eller [Produkt] = "T-shirt" så "AAA"
ellers "BBB"
3. Klik derefter på OK knappen for at gå tilbage til Power Query editor vindue, og du får en ny kolonne med de data, du har brug for, se skærmbillede:
4. Endelig skal du klikke på Hjem > Luk og indlæs > Luk og indlæs for at indlæse disse data til et nyt regneark.
AND-logikken udfører flere logiske test i en enkelt if-sætning. Alle testene skal være sande, for at det sande resultat kan returneres. Hvis nogen af testene er falske, returneres det falske resultat. Syntaksen er:
Tag ovenstående data som et eksempel. Jeg vil have en ny kolonne vist: hvis produktet er "Kjole", og ordren er større end 300, så anvend 50% rabat på den oprindelige pris; ellers behold den oprindelige pris.
1. Vælg datatabellen, og klik data > Fra tabel/område at gå til Power Query editor vindue.
2. Klik i det åbne vindue Tilføj kolonne > Brugerdefineret kolonne. I åbnet Brugerdefineret kolonne dialogboksen, skal du udføre følgende handlinger:
- Indtast et navn til den nye kolonne i Nyt kolonnenavn tekstboks;
- Indtast derefter nedenstående formel i Brugerdefineret kolonneformel boks.
- = hvis [Produkt] ="Kjole" og [Ordre] > 300, så [Pris]*0.5
andet [Pris]
3. Klik derefter på OK knappen for at gå tilbage til Power Query editor vindue, og du får en ny kolonne med de data, du har brug for, se skærmbillede:
4. Til sidst skal du indlæse disse data i et nyt regneark ved at klikke Hjem > Luk og indlæs > Luk og indlæs.
Hvis sætning med OR og AND Logics
Okay, de foregående eksempler er lette for os at forstå. Lad os nu gøre det sværere. Du kan kombinere OG og ELLER for at danne enhver tilstand, du kan forestille dig. I denne type kan du bruge parenteser i formlen til at definere komplekse regler.
Tag også ovenstående data som et eksempel, antag, at jeg vil have en ny kolonne vist som: hvis produktet er "Kjole" og dets rækkefølge er større end 300, eller produktet er "bukser" og dets rækkefølge er større end 300, så vis "A+", ellers viser "Andet".
1. Vælg datatabellen, og klik data > Fra tabel/område at gå til Power Query editor vindue.
2. Klik i det åbne vindue Tilføj kolonne > Brugerdefineret kolonne. I åbnet Brugerdefineret kolonne dialogboksen, skal du udføre følgende handlinger:
- Indtast et navn til den nye kolonne i Nyt kolonnenavn tekstboks;
- Indtast derefter nedenstående formel i Brugerdefineret kolonneformel boks.
- =hvis ([Produkt] = "Kjole" og [Bestil] > 300 ) eller
([Produkt] = "Bukser" og [Bestilling] > 300 )
derefter "A+"
ellers "Andet"
3. Klik derefter på OK knappen for at gå tilbage til Power Query editor vindue, og du får en ny kolonne med de data, du har brug for, se skærmbillede:
4. Til sidst skal du indlæse disse data i et nyt regneark ved at klikke Hjem > Luk og indlæs > Luk og indlæs.
I feltet Brugerdefineret kolonneformel kan du bruge følgende logiske operatorer:
- = : Lige til
- <> : Ikke lig med
- > : Større end
- >= : Større end eller lig med
- < : Mindre end
- <= : Mindre end eller lig med
Bedste kontorproduktivitetsværktøjer
Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid. Klik her for at få den funktion, du har mest brug for...
Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere
- Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
- Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
- Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!