Note: The other languages of the website are Google-translated. Back to English

Power Query: Příkaz If – vnořené ifs a více podmínek

V Excel Power Query je příkaz IF 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 PRAVDA nebo NEPRAVDA. 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 Power Query

Příkaz Power Query if pomocí podmíněného sloupce

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


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

V Power Query je syntaxe:

= 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: Příkaz Power Query if rozlišuje velká a malá písmena, příkaz if, then a else musí být malá písmena.

V aplikaci Excel 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.


Příkaz 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 Editor Power Query 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 Editor Power Query 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 Home > 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 Editor Power Query 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 Editor Power Query 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 Home > Zavřít a načíst > Zavřít a načíst k načtení těchto dat do nového listu.


Příkaz 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 Editor Power Query 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 Home > 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 Editor Power Query 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 Editor Power Query okno a získáte nový sloupec s údaji, které potřebujete, viz snímek obrazovky:

4. Konečně klikněte na tlačítko Home > 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 Editor Power Query 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 Editor Power Query okno a získáte nový sloupec s údaji, které potřebujete, viz snímek obrazovky:

4. Nakonec klepněte na tlačítko Home > 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 Editor Power Query 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 Editor Power Query 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 Home > 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 Editor Power Query 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 Editor Power Query 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 Home > 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ší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2021 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
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!
officetab dno
Komentáře (0)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Zatím zde nejsou žádné komentáře
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL