Preskoči na glavno vsebino

Power Query: Izjava If – ugnezdeni if-ji in več pogojev

V Excelu Power Query, je stavek IF ena najbolj priljubljenih funkcij za preverjanje pogoja in vrnitev določene vrednosti glede na to, ali je rezultat TRUE ali FALSE. Med tem stavkom if in funkcijo IF v Excelu je nekaj razlik. V tej vadnici vam bom predstavil sintakso tega stavka if ter nekaj preprostih in zapletenih primerov.

Osnovna sintaksa stavka if Power Query

Power Query stavek if s pogojnim stolpcem

Power Query stavek if z zapisom kode M


Osnovna sintaksa stavka if Power Query

In Power Query, sintaksa je:

= if logical_test then value_if_true else value_if_false
  • logični_test: Stanje, ki ga želite preizkusiti.
  • vrednost_če_resnična: Vrednost, ki se vrne, če je rezultat TRUE.
  • vrednost_če_napačno: Vrednost, ki se vrne, če je rezultat FALSE.
Opombe: Power Query če je stavek občutljiv na velike in male črke, morajo biti if, then in else napisane z malimi črkami.

V Excelu Power Query, obstajata dva načina za ustvarjanje te vrste pogojne logike:

  • Uporaba funkcije pogojnega stolpca za nekatere osnovne scenarije;
  • Pisanje kode M za naprednejše scenarije.

V naslednjem razdelku bom govoril o nekaterih primerih uporabe tega stavka if.


Power Query stavek if s pogojnim stolpcem

 Primer 1: Osnovni stavek if

Tukaj bom predstavil, kako uporabiti ta stavek if v Power Query. Na primer, imam naslednje poročilo o izdelku, če je status izdelka Star, prikazuje 50 % popust; če je status izdelka Nov, prikazuje 20-odstotni popust, kot je prikazano na spodnjih posnetkih zaslona.

1. Izberite podatkovno tabelo z delovnega lista, nato pa v Excelu 2019 in Excelu 365 kliknite datum > Iz tabele/razpona, glej posnetek zaslona:

Opombe: V programih Excel 2016 in Excel 2021 kliknite datum > Iz tabele, glej posnetek zaslona:

2. Potem pa v odprto Power Query urednik okno, kliknite Dodaj stolpec > Pogojni stolpec, glej posnetek zaslona:

3. V izskočil Dodaj pogojni stolpec pogovorno okno, naredite naslednje:

  • Novo ime stolpca: Vnesite ime za nov stolpec;
  • Nato določite kriterije, ki jih potrebujete. Na primer, določil bom Če je status enak Old, potem 50 % sicer 20 %;
Nasvet:
  • Ime stolpca: Stolpec, s katerim ocenite svoj if-pogoj. Tukaj izberem Status.
  • Operater: Pogojna logika za uporabo. Možnosti se bodo razlikovale glede na vrsto podatkov izbranega imena stolpca.
    • Besedilo: se začne z, se ne začne z, je enako, vsebuje itd.
    • Številke: je enako, ni enako, je večje ali enako itd.
    • Datum: je pred, je za, je enako, ni enako itd.
  • vrednost: Posebna vrednost, s katero lahko primerjate svojo oceno. Skupaj z imenom stolpca in operaterjem tvori pogoj.
  • izhod: Vrednost, ki se vrne, če je pogoj izpolnjen.
  • Else: Druga vrednost za vrnitev, ko je pogoj napačen.

4. Nato kliknite OK gumb za vrnitev na Power Query urednik okno. Zdaj pa novo Popust stolpec je dodan, glejte posnetek zaslona:

5. Če želite številke oblikovati v odstotke, samo kliknite ABC123 ikona iz Popust glavo stolpca in izberite Odstotek po potrebi si oglejte posnetek zaslona:

6. Na koncu kliknite HOME > Zapri in naloži > Zapri in naloži da te podatke naložite na nov delovni list.


 Primer 2: Kompleksna izjava if

S to možnostjo pogojnega stolpca lahko tudi vstavite dva ali več pogojev v Dodaj pogojni stolpec dialog. Prosim naredite takole:

1. Izberite podatkovno tabelo in pojdite na Power Query urednik okno s klikom datum > Iz tabele/razpona. V novem oknu kliknite Dodaj stolpec > Pogojni stolpec.

2. V izskočil Dodaj pogojni stolpec pogovorno okno, naredite naslednje:

  • Vnesite ime za nov stolpec v Novo ime stolpca besedilno polje;
  • Podajte prvi kriterij v prvo polje kriterija in nato kliknite Dodaj klavzulo gumb za dodajanje drugih polj kriterijev, kot jih potrebujete.

3. Ko končate s kriteriji, kliknite OK gumb za vrnitev na Power Query urednik okno. Zdaj boste dobili nov stolpec z ustreznim rezultatom, ki ga potrebujete. Oglejte si posnetek zaslona:

4. Končno kliknite HOME > Zapri in naloži > Zapri in naloži da te podatke naložite na nov delovni list.


Power Query stavek if z zapisom kode M

Običajno je pogojni stolpec koristen za nekatere osnovne scenarije. Včasih boste morda morali uporabiti več pogojev z logiko IN ali ALI. V tem primeru morate za bolj zapletene scenarije napisati kodo M znotraj stolpca po meri.

 Primer 1: Osnovni stavek if

Vzemimo za primer prve podatke, če je status izdelka Star, prikazuje 50% popust; če je status izdelka Nov, izpiše 20% popust. Za pisanje kode M naredite naslednje:

1. Izberite tabelo in kliknite datum > Iz tabele/razpona Pojdite na Power Query urednik okno.

2. V odprtem oknu kliknite Dodaj stolpec > Stolpec po meri, glej posnetek zaslona:

3. V izskočil Stolpec po meri pogovorno okno, naredite naslednje:

  • Vnesite ime za nov stolpec v Novo ime stolpca besedilno polje;
  • Nato vnesite to formulo: if [Status] = "Old " then "50% " else "20% " v Stolpec po meri Formula škatla.

4. Nato kliknite OK da zaprete to pogovorno okno. Zdaj boste dobili naslednji rezultat, kot ga potrebujete:

5. Končno kliknite HOME > Zapri in naloži > Zapri in naloži da te podatke naložite na nov delovni list.


 Primer 2: Kompleksna izjava if

Ugnezdeni stavki if

Običajno lahko za testiranje podpogojev ugnezdite več stavkov if. Na primer, imam spodnjo podatkovno tabelo. Če je izdelek "Obleka", dajte 50% popust na prvotno ceno; če je izdelek "pulover" ali "pulover s kapuco", dajte 20% popust na prvotno ceno; in drugi izdelki ohranijo prvotno ceno.

1. Izberite podatkovno tabelo in kliknite datum > Iz tabele/razpona Pojdite na Power Query urednik okno.

2. V odprtem oknu kliknite Dodaj stolpec > Stolpec po meri. V odprti Stolpec po meri pogovorno okno, naredite naslednje:

  • Vnesite ime za nov stolpec v Novo ime stolpca besedilno polje;
  • Nato vnesite spodnjo formulo v Stolpec po meri Formula škatla.
  • = če [Izdelek] = "Obleka", potem [Cena] * 0.5 sicer
    if [Izdelek] = "Pulover", potem [Cena] * 0.8 else
    če je [Izdelek] = "Pulovec s kapuco", potem [Cena] * 0.8
    drugače [Cena]

3. In nato kliknite OK gumb za vrnitev na Power Query urednik in dobili boste nov stolpec s podatki, ki jih potrebujete, glejte posnetek zaslona:

4. Končno kliknite HOME > Zapri in naloži > Zapri in naloži da te podatke naložite na nov delovni list.


Izjava If z logiko ALI

Logika ALI izvede več logičnih preizkusov in pravi rezultat bo vrnjen, če je kateri od logičnih preizkusov pravilen. Sintaksa je:

= če je logični_test1 ali logični_test2 ali … potem vrednost_če_true sicer vrednost_če_napačna

Recimo, da imam spodnjo tabelo, zdaj pa želim nov prikaz v stolpcu: če je izdelek »obleka« ali »majica«, potem je blagovna znamka »AAA«, blagovna znamka drugih izdelkov je »BBB«.

1. Izberite podatkovno tabelo in kliknite datum > Iz tabele/razpona Pojdite na Power Query urednik okno.

2. V odprtem oknu kliknite Dodaj stolpec > Stolpec po meri, v odprtem Stolpec po meri pogovorno okno, naredite naslednje:

  • Vnesite ime za nov stolpec v Novo ime stolpca besedilno polje;
  • Nato vnesite spodnjo formulo v Formula stolpca po meri škatla.
  • = če [Izdelek] = "Obleka" ali [Izdelek] = "Majica s kratkimi rokavi", potem "AAA"
    drugače "BBB"

3. In nato kliknite OK gumb za vrnitev na Power Query urednik in dobili boste nov stolpec s podatki, ki jih potrebujete, glejte posnetek zaslona:

4. Nazadnje kliknite HOME > Zapri in naloži > Zapri in naloži da te podatke naložite na nov delovni list.


Izjava If z logiko IN

Logika IN izvede več logičnih preizkusov znotraj enega stavka if. Vsi testi morajo biti resnični, da se vrne pravi rezultat. Če je kateri od testov napačen, se vrne napačen rezultat. Sintaksa je:

= če logični_test1 in logični_test2 in … potem vrednost_če_true sicer vrednost_če_napačna

Vzemimo za primer zgornje podatke, želim, da se nov stolpec prikaže kot: če je izdelek »Obleka« in naročilo je večje od 300, dajte 50 % popust na prvotno ceno; sicer ohrani prvotno ceno.

1. Izberite podatkovno tabelo in kliknite datum > Iz tabele/razpona Pojdite na Power Query urednik okno.

2. V odprtem oknu kliknite Dodaj stolpec > Stolpec po meri. V odprti Stolpec po meri pogovorno okno, naredite naslednje:

  • Vnesite ime za nov stolpec v Novo ime stolpca besedilno polje;
  • Nato vnesite spodnjo formulo v Formula stolpca po meri škatla.
  • = če [Product] = "Dress" in [Order] > 300, potem [Price]*0.5
    drugače [Cena]

3. Nato kliknite OK gumb za vrnitev na Power Query urednik in dobili boste nov stolpec s podatki, ki jih potrebujete, glejte posnetek zaslona:

4. Nazadnje morate te podatke naložiti v nov delovni list s klikom HOME > Zapri in naloži > Zapri in naloži.


Izjava If z logiko ALI in IN

V redu, prejšnje primere zlahka razumemo. Zdaj pa naredimo težje. Kombinirate lahko IN in ALI, da oblikujete poljuben pogoj, ki si ga zamislite. Pri tej vrsti lahko uporabite oklepaje v formuli, da definirate kompleksna pravila.

Vzemite zgornje podatke tudi kot primer, predpostavimo, da želim, da se nov stolpec prikaže kot: če je izdelek »Obleka« in je njegovo naročilo večje od 300 ali je izdelek »Hlače« in je njegovo naročilo večje od 300, potem pokaži “A+”, drugače, prikaz “Drugo”.

1. Izberite podatkovno tabelo in kliknite datum > Iz tabele/razpona Pojdite na Power Query urednik okno.

2. V odprtem oknu kliknite Dodaj stolpec > Stolpec po meri. V odprti Stolpec po meri pogovorno okno, naredite naslednje:

  • Vnesite ime za nov stolpec v Novo ime stolpca besedilno polje;
  • Nato vnesite spodnjo formulo v Formula stolpca po meri škatla.
  • =if ([Izdelek] = "Obleka" in [Naročilo] > 300 ) oz
    ([Izdelek] = "Hlače" in [Naročilo] > 300 )
    nato "A+"
    else "Drugo"

3. Nato kliknite OK gumb za vrnitev na Power Query urednik in dobili boste nov stolpec s podatki, ki jih potrebujete, glejte posnetek zaslona:

4. Nazadnje morate te podatke naložiti v nov delovni list s klikom HOME > Zapri in naloži > Zapri in naloži.

Nasvet:
V polju Formula stolpca po meri lahko uporabite naslednje logične operatorje:
  • = : je enako
  • <> : ni enako
  • > : Večje kot
  • >= : Večje ali enako
  • < : manj kot
  • <= : manj kot ali enako

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations