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:
- 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.
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 %;
- 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
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.
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:
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.
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:
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.
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.
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
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...
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!