Power Query: Оператор If - вкладені ifs і кілька умов

В Excel Power Query, оператор IF є однією з найпопулярніших функцій для перевірки умови та повернення певного значення залежно від того, ІСТИННИЙ чи ХИБНИЙ результат. Існують деякі відмінності між цим оператором 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 із логікою AND
  • Оператор If із логіками АБО та І

Базовий синтаксис оператора if Power Query

In Power Query, синтаксис такий:

• логічний_тест: умова, яку ви хочете перевірити.
• значення_якщо_правда: значення, яке повертається, якщо результат ІСТИННИЙ.
• значення_якщо_хибне: значення, яке повертається, якщо результат ЛОЖНИЙ.

В 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

За допомогою цього параметра Conditional Column ви також можете вставити дві або більше умов у Додати умовний стовпець діалог. Будь ласка, зробіть так:

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. У вікні, що відкрилося, натисніть Додати колонку > Спеціальна колонка. У відкритому Спеціальна колонка діалоговому вікні, виконайте такі дії:

• Введіть назву для нового стовпця в Нова назва стовпця текстове вікно;
• Потім введіть наведену нижче формулу в Спеціальний стовпець формула коробка
• = if [Product] = "Dress" then [Price] * 0.5 else
  if [Product] = "Sweater" then [Ціна] * 0.8 else
  якщо [Товар] = "Толстовка", то [Ціна] * 0.8
  ще [ціна]

3. А потім натисніть OK кнопку, щоб повернутися до Power Query редактор вікно, і ви отримаєте новий стовпець з даними, які вам потрібні, дивіться знімок екрана:

4. Нарешті клацніть Головна > Закрити та завантажити > Закрити та завантажити щоб завантажити ці дані на новий аркуш.

Оператор If із логікою АБО

Логіка АБО виконує кілька логічних перевірок, і справжній результат повернеться, якщо будь-яка з логічних перевірок є правильною. Синтаксис:

Припустімо, що у мене є наведена нижче таблиця, і тепер я хочу новий стовпець відображати так: якщо продукт «Сукня» або «Футболка», то бренд «AAA», бренд інших продуктів «BBB».

1. Виберіть таблицю даних і натисніть дані > З таблиці/діапазону йти до Power Query редактор вікна.

2. У вікні, що відкрилося, натисніть Додати колонку > Спеціальна колонка, у відкритому Спеціальна колонка діалоговому вікні, виконайте такі дії:

• Введіть назву для нового стовпця в Нова назва стовпця текстове вікно;
• Потім введіть наведену нижче формулу в Формула спеціального стовпця коробка
• = якщо [Продукт] = "Сукня" або [Продукт] = "Футболка", то "ААА"
  інакше "BBB"

3. А потім натисніть OK кнопку, щоб повернутися до Power Query редактор вікно, і ви отримаєте новий стовпець з даними, які вам потрібні, дивіться знімок екрана:

4. Нарешті, клацніть Головна > Закрити та завантажити > Закрити та завантажити щоб завантажити ці дані на новий аркуш.

Оператор If із логікою AND

Логіка 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 )
  потім "А+"
  else "Інше"

3. Потім натисніть кнопку OK кнопку, щоб повернутися до Power Query редактор вікно, і ви отримаєте новий стовпець з даними, які вам потрібні, дивіться знімок екрана:

4. Нарешті, ви повинні завантажити ці дані в новий аркуш, клацнувши Головна > Закрити та завантажити > Закрити та завантажити.