Power Query: оператор If - вложени ifs и множество условия

В Excel Power Query, операторът IF е една от най-популярните функции за проверка на условие и връщане на конкретна стойност в зависимост от това дали резултатът е TRUE или FALSE. Има някои разлики между този оператор if и функцията IF на Excel. В този урок ще ви представя синтаксиса на този оператор if и някои прости и сложни примери.

Основен синтаксис на оператор if Power Query

Power Query оператор if, използващ условна колона

• Пример 1: Основен оператор if
• Пример 2: Сложен оператор if

Power Query оператор if, като напишете кода M

• Пример 1: Основен оператор if
• Пример 2: Сложен оператор if
  • Вложени изрази if
  • Оператор If с логика ИЛИ
  • Оператор If с логика И
  • Оператор If с логики ИЛИ и И

---

Основен синтаксис на оператор if Power Query

In Power Query, синтаксисът е:

• логически_тест: Състоянието, което искате да тествате.
• стойност_ако_истина: Стойността, която да се върне, ако резултатът е TRUE.
• стойност_ако_грешно: Стойността, която да се върне, ако резултатът е FALSE.

В Excel Power Query, има два начина за създаване на този тип условна логика:

• Използване на функцията за условна колона за някои основни сценарии;
• Писане на M код за по-напреднали сценарии.

В следващия раздел ще говоря за някои примери за използване на този оператор if.

---

Power Query оператор if, използващ условна колона

 Пример 1: Основен оператор if

Тук ще ви представя как да използвате този оператор if в Power Query. Например, имам следния продуктов отчет, ако статусът на продукта е Стар, показващ 50% отстъпка; ако статусът на продукта е Нов, показва 20% отстъпка, както са показани екранните снимки по-долу.

1. Изберете таблицата с данни от работния лист, след което в Excel 2019 и Excel 365 щракнете Дата > От таблица/обхват, вижте екранна снимка:

Забележка: В Excel 2016 и Excel 2021 щракнете Дата > От таблица, вижте екранна снимка:

2. След това, в отвореното Power Query Редактор прозорец, кликнете Добавяне на колона > Условна колона, вижте екранна снимка:

3. В изскочилото Добавяне на условна колона диалогов прозорец, моля, направете следните операции:

• Ново име на колона: Въведете име за новата колона;
• След това посочете нужните критерии. Например ще уточня Ако Статусът е равен на Стар, тогава 50%, иначе 20%;

4, След това кликнете върху OK бутон, за да се върнете към Power Query Редактор прозорец. Сега, нов Отстъпка колона е добавена, вижте екранна снимка:

5. Ако искате да форматирате числата в проценти, просто щракнете ABC123 икона от Отстъпка заглавка на колона и изберете Процент колкото ви трябва, вижте екранна снимка:

6. Накрая, моля, щракнете Начало > Затваряне и зареждане > Затваряне и зареждане за да заредите тези данни в нов работен лист.

---

 Пример 2: Сложен оператор if

С тази опция за условна колона можете също да вмъкнете две или повече условия в Добавяне на условна колона диалогов прозорец. Моля, направете следното:

1. Изберете таблицата с данни и отидете на Power Query Редактор прозорец чрез щракване Дата > От таблица/обхват. В новия прозорец щракнете Добавяне на колона > Условна колона.

2. В изскочилото Добавяне на условна колона диалогов прозорец, моля, извършете следните операции:

• Въведете име за новата колона в Ново име на колона текстово поле;
• Посочете първите критерии в първото поле за критерии и след това щракнете Добавяне на клауза бутон, за да добавите други полета за критерии, както ви е необходимо.

3. След като завършите критериите, щракнете OK бутон, за да се върнете към Power Query Редактор прозорец. Сега ще получите нова колона със съответния резултат, от който се нуждаете. Вижте екранна снимка:

4. Най-накрая, моля, щракнете Начало > Затваряне и зареждане > Затваряне и зареждане за да заредите тези данни в нов работен лист.

---

Power Query оператор if, като напишете кода M

Обикновено условната колона е полезна за някои основни сценарии. Понякога може да се наложи да използвате множество условия с И или ИЛИ логика. В този случай трябва да напишете M код в персонализирана колона за по-сложни сценарии.

 Пример 1: Основен оператор if

Вземете първите данни като пример, ако статусът на продукта е Стар, показващ 50% отстъпка; ако статусът на продукта е Нов, показва 20% отстъпка. За да напишете M кода, моля, направете следното:

1. Изберете таблицата и щракнете Дата > От таблица/обхват за да отидете на Power Query Редактор прозорец.

2. В отворения прозорец щракнете Добавяне на колона > Персонализирана колона, вижте екранна снимка:

3. В изскочилото Персонализирана колона диалогов прозорец, моля, извършете следните операции:

• Въведете име за новата колона в Ново име на колона текстово поле;
• След това въведете тази формула: if [Status] = "Old " then "50% " else "20% " в Персонализирана колона формула кутия.

4, След това кликнете върху OK за да затворите този диалогов прозорец. Сега ще получите следния резултат, както ви е необходим:

5. Най-накрая щракнете Начало > Затваряне и зареждане > Затваряне и зареждане за да заредите тези данни в нов работен лист.

---

 Пример 2: Сложен оператор if

Вложени изрази if

Обикновено, за да тествате подусловията, можете да вложите няколко израза if. Например имам таблицата с данни по-долу. Ако продуктът е “Рокля”, дайте 50% отстъпка от оригиналната цена; ако продуктът е “Пуловер” или “Худи”, дайте 20% отстъпка от оригиналната цена; и други продукти запазват оригиналната цена.

1. Изберете таблицата с данни и щракнете Дата > От таблица/обхват за да отидете на Power Query Редактор прозорец.

2. В отворения прозорец щракнете Добавяне на колона > Персонализирана колона. В отвореното Персонализирана колона диалогов прозорец, моля, извършете следните операции:

• Въведете име за новата колона в Ново име на колона текстово поле;
• След това въведете формулата по-долу в Персонализирана колона формула кутия.
• = ако [Продукт] = "Рокля", тогава [Цена] * 0.5 друго
  ако [Продукт] = "Пуловер", тогава [Цена] * 0.8 друго
  ако [Продукт] = "Худи", тогава [Цена] * 0.8
  иначе [Цена]

3. И след това щракнете OK бутон, за да се върнете към Power Query Редактор прозорец и ще получите нова колона с данните, от които се нуждаете, вижте екранна снимка:

4. Най-накрая щракнете Начало > Затваряне и зареждане > Затваряне и зареждане за да заредите тези данни в нов работен лист.

---

Оператор If с логика ИЛИ

Логиката ИЛИ извършва множество логически тестове и истинският резултат ще се върне, ако някой от логическите тестове е верен. Синтаксисът е:

Да предположим, че имам таблицата по-долу, сега искам нов дисплей в колона като: ако продуктът е „Рокля“ или „Тениска“, тогава марката е „AAA“, марката на другите продукти е „BBB“.

1. Изберете таблицата с данни и щракнете Дата > От таблица/обхват за да отидете на Power Query Редактор прозорец.

2. В отворения прозорец щракнете Добавяне на колона > Персонализирана колона, в открития Персонализирана колона диалогов прозорец, моля, извършете следните операции:

• Въведете име за новата колона в Ново име на колона текстово поле;
• След това въведете формулата по-долу в Персонализирана формула на колона кутия.
• = ако [Продукт] = "Рокля" или [Продукт] = "Тениска", тогава "ААА"
  иначе "BBB"

3. И след това щракнете OK бутон, за да се върнете към Power Query Редактор прозорец и ще получите нова колона с данните, от които се нуждаете, вижте екранна снимка:

4, Накрая кликнете върху Начало > Затваряне и зареждане > Затваряне и зареждане за да заредите тези данни в нов работен лист.

---

Оператор If с логика И

Логиката AND извършва множество логически тестове в рамките на един оператор if. Всички тестове трябва да са верни, за да бъде върнат истинският резултат. Ако някой от тестовете е грешен, се връща грешният резултат. Синтаксисът е:

Вземете горните данни например, искам нова колона да се показва като: ако продуктът е „Рокля“ и поръчате повече от 300, тогава дайте 50% отстъпка за първоначалната цена; в противен случай запазете първоначалната цена.

1. Изберете таблицата с данни и щракнете Дата > От таблица/обхват за да отидете на Power Query Редактор прозорец.

2. В отворения прозорец щракнете Добавяне на колона > Персонализирана колона. В отвореното Персонализирана колона диалогов прозорец, моля, извършете следните операции:

• Въведете име за новата колона в Ново име на колона текстово поле;
• След това въведете формулата по-долу в Персонализирана формула на колона кутия.
• = ако [Продукт] = "Рокля" и [Поръчка] > 300, тогава [Цена]*0.5
  иначе [Цена]

3, След това кликнете върху OK бутон, за да се върнете към Power Query Редактор прозорец и ще получите нова колона с данните, от които се нуждаете, вижте екранна снимка:

4. Накрая трябва да заредите тези данни в нов работен лист, като щракнете Начало > Затваряне и зареждане > Затваряне и зареждане.

---

Оператор If с логики ИЛИ и И

Добре, предишните примери са лесни за разбиране. Сега нека го направим по-трудно. Можете да комбинирате И и ИЛИ, за да образувате всяко условие, което можете да си представите. В този тип можете да използвате скоби във формулата, за да дефинирате сложни правила.

Вземете и горните данни като пример, приемете, че искам нова колона да се показва като: ако продуктът е „Рокля“ и поръчката му е по-голяма от 300, или продуктът е „Панталони“ и поръчката му е по-голяма от 300, тогава покажете “A+”, в противен случай, показва се “Друго”.

1. Изберете таблицата с данни и щракнете Дата > От таблица/обхват за да отидете на Power Query Редактор прозорец.

2. В отворения прозорец щракнете Добавяне на колона > Персонализирана колона. В отвореното Персонализирана колона диалогов прозорец, моля, извършете следните операции:

• Въведете име за новата колона в Ново име на колона текстово поле;
• След това въведете формулата по-долу в Персонализирана формула на колона кутия.
• =ако ([Продукт] = "Рокля" и [Поръчка] > 300 ) или
  ([Продукт] = "Панталони" и [Поръчка] > 300 )
  след това "A+"
  else "Друго"

3, След това кликнете върху OK бутон, за да се върнете към Power Query Редактор прозорец и ще получите нова колона с данните, от които се нуждаете, вижте екранна снимка:

4. Накрая трябва да заредите тези данни в нов работен лист, като щракнете Начало > Затваряне и зареждане > Затваряне и зареждане.