Přejít k hlavnímu obsahu

Power Query: Příkaz If – vnořené podmínky ifs & vícenásobné

V Excelu Power QueryIF je jednou z nejoblíbenějších funkcí pro kontrolu podmínky a vrácení konkrétní hodnoty v závislosti na tom, zda je výsledek TRUE nebo FALSE. Mezi tímto příkazem if a funkcí IF Excelu jsou určité rozdíly. V tomto tutoriálu vám představím syntaxi tohoto příkazu if a několik jednoduchých a složitých příkladů.

Základní syntaxe příkazu if of Power Query

Power Query if pomocí podmíněného sloupce

Power Query if zapsáním M kódu


Základní syntaxe příkazu if of Power Query

In Power Query, syntaxe je:

= if logical_test then value_if_true else value_if_false
  • logický_test: Stav, který chcete testovat.
  • value_if_true: Hodnota, která se má vrátit, pokud je výsledek TRUE.
  • hodnota_pokud_nepravda: Hodnota, která se má vrátit, pokud je výsledek FALSE.
Poznámka: Power Query if, then a else musí být malá.

V Excelu Power Query, existují dva způsoby, jak vytvořit tento typ podmíněné logiky:

  • Použití funkce podmíněného sloupce pro některé základní scénáře;
  • Psaní M kódu pro pokročilejší scénáře.

V následující části budu hovořit o několika příkladech použití tohoto příkazu if.


Power Query if pomocí podmíněného sloupce

 Příklad 1: Základní příkaz if

Zde představím, jak použít tento příkaz if v Power Query. Mám například následující zprávu o produktu, pokud je stav produktu Starý, zobrazuje 50% slevu; pokud je stav produktu Nový, zobrazí se 20% sleva, jak je uvedeno níže.

1. Vyberte tabulku dat z listu a poté v Excelu 2019 a Excelu 365 klikněte Data > Z tabulky/rozsahu, viz screenshot:

Poznámka: V Excelu 2016 a Excelu 2021 klikněte Data > Z tabulky, viz screenshot:

2. Pak, v otevřeném Power Query editor okno, klepněte na tlačítko Přidat sloupec > Podmíněný sloupec, viz screenshot:

3. Ve vyskočené Přidat podmíněný sloupec v dialogovém okně proveďte následující operace:

  • Název nového sloupce: Zadejte název nového sloupce;
  • Poté zadejte kritéria, která potřebujete. Například upřesním Pokud se stav rovná Starý, pak 50 % jinak 20 %;
Tip:
  • Název sloupce: Sloupec pro vyhodnocení vaší podmínky if. Zde vyberu Stav.
  • Operátor: Podmíněná logika k použití. Možnosti se budou lišit v závislosti na typu dat vybraného názvu sloupce.
    • Text: začíná na, nezačíná na, rovná se, obsahuje atd.
    • Čísla: rovná se, nerovná se, je větší nebo rovno atd.
    • Datum: je před, je po, rovná se, nerovná se atd.
  • Hodnota: Konkrétní hodnota, se kterou se má porovnat vaše hodnocení. Spolu s názvem sloupce a operátorem tvoří podmínku.
  • Výstup: Hodnota, která se má vrátit, pokud je podmínka splněna.
  • Jiný: Další hodnota, která se má vrátit, když je podmínka nepravdivá.

4. Potom klepněte na tlačítko OK tlačítko pro návrat do Power Query editor okno. Nyní nový Sleva sloupec je přidán, viz snímek obrazovky:

5. Pokud chcete formátovat čísla na procenta, stačí kliknout ABC123 ikona z Sleva záhlaví sloupce a vyberte Procento jak potřebujete, viz screenshot:

6. Nakonec prosím klikněte Domů > Zavřít a načíst > Zavřít a načíst k načtení těchto dat do nového listu.


 Příklad 2: Složitý příkaz if

Pomocí této možnosti podmíněného sloupce můžete také vložit dvě nebo více podmínek do Přidat podmíněný sloupec dialog. Postupujte prosím takto:

1. Vyberte datovou tabulku a přejděte na Power Query editor kliknutím na Data > Z tabulky/rozsahu. V novém okně klikněte na Přidat sloupec > Podmíněný sloupec.

2. Ve vyskočené Přidat podmíněný sloupec V dialogovém okně proveďte následující operace:

  • Do pole zadejte název nového sloupce Název nového sloupce Textové pole;
  • Zadejte první kritérium do prvního pole kritérií a potom klepněte Přidat doložku tlačítko pro přidání dalších polí kritérií podle potřeby.

3. Po dokončení kritérií klikněte OK tlačítko pro návrat do Power Query editor okno. Nyní získáte nový sloupec s odpovídajícím výsledkem, který potřebujete. Viz snímek obrazovky:

4. Nakonec prosím klikněte Domů > Zavřít a načíst > Zavřít a načíst k načtení těchto dat do nového listu.


Power Query if zapsáním M kódu

Normálně je podmíněný sloupec užitečný pro některé základní scénáře. Někdy může být nutné použít více podmínek s logikou AND nebo OR. V tomto případě musíte napsat M kód do vlastního sloupce pro složitější scénáře.

 Příklad 1: Základní příkaz if

Vezměte první údaje jako příklad, pokud je stav produktu Starý, zobrazuje 50% slevu; pokud je stav produktu Nový, zobrazí se sleva 20 %. Chcete-li napsat kód M, postupujte takto:

1. Vyberte tabulku a klikněte Data > Z tabulky/rozsahu přejděte na Power Query editor okno.

2. V otevřeném okně klikněte na Přidat sloupec > Vlastní sloupec, viz screenshot:

3. Ve vyskočené Vlastní sloupec V dialogovém okně proveďte následující operace:

  • Do pole zadejte název nového sloupce Název nového sloupce Textové pole;
  • Poté zadejte tento vzorec: if [Stav] = "Starý" pak "50 % " jinak "20 % " do Vlastní sloupec vzorec box.

4. Potom klepněte na tlačítko OK zavřete toto dialogové okno. Nyní získáte následující výsledek, jak potřebujete:

5. Konečně klikněte na tlačítko Domů > Zavřít a načíst > Zavřít a načíst k načtení těchto dat do nového listu.


 Příklad 2: Složitý příkaz if

Vnořené příkazy if

Obvykle můžete pro testování dílčích podmínek vnořit více příkazů if. Mám například níže uvedenou tabulku dat. Pokud je produkt „Šaty“, poskytněte 50% slevu z původní ceny; pokud je produkt „Svetr“ nebo „Mikina s kapucí“, poskytněte slevu 20 % z původní ceny; a ostatní produkty si ponechají původní cenu.

1. Vyberte datovou tabulku a klikněte Data > Z tabulky/rozsahu přejděte na Power Query editor okno.

2. V otevřeném okně klikněte na Přidat sloupec > Vlastní sloupec. V otevřeném Vlastní sloupec V dialogovém okně proveďte následující operace:

  • Do pole zadejte název nového sloupce Název nového sloupce Textové pole;
  • Poté zadejte níže uvedený vzorec do Vlastní sloupec vzorec box.
  • = if [Produkt] = "Šaty" pak [Cena] * 0.5 jinak
    if [Product] = "Sweater" then [Cena] * 0.8 else
    pokud [Produkt] = "Mikina s kapucí", pak [Cena] * 0.8
    jinak [cena]

3. A pak klikněte OK tlačítko pro návrat na Power Query editor okno a získáte nový sloupec s údaji, které potřebujete, viz snímek obrazovky:

4. Konečně klikněte na tlačítko Domů > Zavřít a načíst > Zavřít a načíst k načtení těchto dat do nového listu.


Příkaz if s logikou OR

Logika OR provádí více logických testů a skutečný výsledek se vrátí, pokud je některý z logických testů aktivní. Syntaxe je:

= if logical_test1 nebo logical_test2 or … then value_if_true else value_if_false

Předpokládejme, že mám níže uvedenou tabulku, nyní chci nový sloupcový displej jako: pokud je produkt „Šaty“ nebo „Tričko“, pak je značka „AAA“, značka ostatních produktů je „BBB“.

1. Vyberte datovou tabulku a klikněte Data > Z tabulky/rozsahu přejděte na Power Query editor okno.

2. V otevřeném okně klikněte na Přidat sloupec > Vlastní sloupec, v otevřené Vlastní sloupec V dialogovém okně proveďte následující operace:

  • Do pole zadejte název nového sloupce Název nového sloupce Textové pole;
  • Poté zadejte níže uvedený vzorec do Vlastní vzorec sloupce box.
  • = if [Produkt] = "Šaty" nebo [Produkt] = "Tričko" pak "AAA"
    jinak "BBB"

3. A pak klikněte OK tlačítko pro návrat na Power Query editor okno a získáte nový sloupec s údaji, které potřebujete, viz snímek obrazovky:

4. Nakonec klepněte na tlačítko Domů > Zavřít a načíst > Zavřít a načíst k načtení těchto dat do nového listu.


Příkaz if s logikou AND

Logika AND provádí více logických testů v rámci jediného příkazu if. Všechny testy musí být pravdivé, aby byl vrácen skutečný výsledek. Pokud je některý z testů nepravdivý, vrátí se falešný výsledek. Syntaxe je:

= if logical_test1 and logical_test2 and … then value_if_true else value_if_false

Vezměte si například výše uvedená data, chci, aby se nový sloupec zobrazoval takto: pokud je produkt „Šaty“ a objednáte více než 300, dejte 50% slevu z původní ceny; jinak zachovat původní cenu.

1. Vyberte datovou tabulku a klikněte Data > Z tabulky/rozsahu přejděte na Power Query editor okno.

2. V otevřeném okně klikněte na Přidat sloupec > Vlastní sloupec. V otevřeném Vlastní sloupec V dialogovém okně proveďte následující operace:

  • Do pole zadejte název nového sloupce Název nového sloupce Textové pole;
  • Poté zadejte níže uvedený vzorec do Vlastní vzorec sloupce box.
  • = pokud [Produkt] ="Šaty" a [Objednávka] > 300, pak [Cena]*0.5
    jinak [cena]

3. Potom klepněte na tlačítko OK tlačítko pro návrat na Power Query editor okno a získáte nový sloupec s údaji, které potřebujete, viz snímek obrazovky:

4. Nakonec byste měli tato data kliknutím načíst do nového listu Domů > Zavřít a načíst > Zavřít a načíst.


Příkaz If s logikou OR a AND

Dobře, předchozí příklady jsou pro nás snadno srozumitelné. Teď to ztížíme. Můžete kombinovat AND a OR a vytvořit jakoukoli podmínku, kterou si dokážete představit. V tomto typu můžete použít závorky do vzorce k definování složitých pravidel.

Vezměte si také výše uvedená data jako příklad, předpokládejme, že chci, aby se nový sloupec zobrazoval takto: pokud je produkt „Šaty“ a jeho pořadí je vyšší než 300 nebo je produkt „Kalhoty“ a jeho pořadí je vyšší než 300, pak zobrazit „A+“, jinak zobrazí „Jiné“.

1. Vyberte datovou tabulku a klikněte Data > Z tabulky/rozsahu přejděte na Power Query editor okno.

2. V otevřeném okně klikněte na Přidat sloupec > Vlastní sloupec. V otevřeném Vlastní sloupec V dialogovém okně proveďte následující operace:

  • Do pole zadejte název nového sloupce Název nového sloupce Textové pole;
  • Poté zadejte níže uvedený vzorec do Vlastní vzorec sloupce box.
  • =if ([Produkt] = "Šaty" a [Objednávka] > 300 ) nebo
    ([Produkt] = "kalhoty" a [Objednávka] > 300 )
    pak "A+"
    jinak "jiné"

3. Potom klepněte na tlačítko OK tlačítko pro návrat na Power Query editor okno a získáte nový sloupec s údaji, které potřebujete, viz snímek obrazovky:

4. Nakonec byste měli tato data kliknutím načíst do nového listu Domů > Zavřít a načíst > Zavřít a načíst.

Tip:
V poli Vzorec Vlastní sloupec můžete použít následující logické operátory:
  • = : Rovná se
  • <> : Nerovná se
  • > : Větší než
  • >= : Větší než nebo rovno
  • < : Méně než
  • <= : Menší nebo rovno

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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