Power Query: Условие if - вложенные условия и множественные условия

В Excel Power Query оператор IF является одной из самых популярных функций для проверки условия и возврата определенного значения в зависимости от того, является результат ИСТИНА или ЛОЖЬ. Существуют некоторые различия между этим оператором if и функцией ЕСЛИ (IF) в Excel. В этом руководстве я представлю синтаксис этого оператора if и несколько простых и сложных примеров.

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

Использование условного столбца в операторе if Power Query

• Пример 1: Базовый оператор if
• Пример 2: Сложный оператор if

Оператор if в Power Query через написание кода M

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

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

В Power Query синтаксис следующий:

• logical_test: Условие, которое вы хотите проверить.
• value_if_true: Значение, которое будет возвращено, если результат ИСТИНА.
• value_if_false: Значение, которое будет возвращено, если результат ЛОЖЬ.

В Excel Power Query существует два способа создания такого типа условной логики:

• Использование функции Условный столбец для некоторых базовых сценариев;
• Написание кода M для более продвинутых сценариев.

В следующем разделе я расскажу о нескольких примерах использования этого оператора if.

Оператор if в Power Query с использованием условного столбца

Пример 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. В конце нажмите Главная > Закрыть и загрузить > Закрыть и загрузить, чтобы загрузить эти данные на новый рабочий лист.

Оператор if в Power Query через написание кода M

Обычно Условный столбец полезен для некоторых базовых сценариев. Иногда вам может потребоваться использование нескольких условий с логикой И или ИЛИ. В этом случае вы должны написать код M внутри пользовательского столбца для более сложных сценариев.

Пример 1: Базовый оператор if

Возьмем первый набор данных в качестве примера: если статус продукта «Старый», отображается скидка 50%; если статус продукта «Новый», отображается скидка 20%. Для написания кода M сделайте следующее:

1. Выберите таблицу и нажмите Данные > Из таблицы/Диапазона, чтобы перейти в окно Редактора Power Query.

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

3. В появившемся диалоговом окне Пользовательский столбец выполните следующие действия:

• Введите имя для нового столбца в текстовое поле Имя нового столбца;
• Затем введите эту формулу: if [Status] = "Старый" then "50%" else "20%" в текстовое поле Формула пользовательского столбца.

4. Затем нажмите OK, чтобы закрыть это диалоговое окно. Теперь вы получите следующий результат, который вам нужен:

5. В конце нажмите Главная > Закрыть и загрузить > Закрыть и загрузить, чтобы загрузить эти данные на новый рабочий лист.

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

Вложенные операторы if

Обычно для тестирования подусловий можно вложить несколько операторов if. Например, у меня есть следующая таблица данных. Если продукт — «Платье», дать скидку 50% от исходной цены; если продукт — «Свитер» или «Худи», дать скидку 20% от исходной цены; остальные продукты сохраняют исходную цену.

1. Выберите таблицу данных и нажмите Данные > Из таблицы/Диапазона, чтобы перейти в окно Редактора Power Query.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открывшемся диалоговом окне Пользовательский столбец выполните следующие действия:

• Введите имя для нового столбца в текстовое поле Имя нового столбца;
• Затем введите следующую формулу в текстовое поле Формула пользовательского столбца.
• = if [Product] = "Платье" then [Price] * 0.5 else
  if [Product] = "Свитер" then [Price] * 0.8 else
  if [Product] = "Худи" then [Price] * 0.8
  else [Price]

3. Затем нажмите кнопку OK, чтобы вернуться в окно Редактора Power Query, и вы получите новый столбец с данными, которые вам нужны, см. скриншот:

4. В конце нажмите Главная > Закрыть и загрузить > Закрыть и загрузить, чтобы загрузить эти данные на новый рабочий лист.

Условие if с логикой ИЛИ

Логика ИЛИ выполняет несколько логических тестов, и истинный результат будет возвращен, если любой из логических тестов истинен. Синтаксис следующий:

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

1. Выберите таблицу данных и нажмите Данные > Из таблицы/Диапазона, чтобы перейти в окно Редактора Power Query.

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

• Введите имя для нового столбца в текстовое поле Имя нового столбца;
• Затем введите следующую формулу в текстовое поле Формула пользовательского столбца.
• = if [Product] = "Платье" or [Product] = "Футболка" then "AAA"
  else "BBB"

3. Затем нажмите кнопку OK, чтобы вернуться в окно Редактора Power Query, и вы получите новый столбец с данными, которые вам нужны, см. скриншот:

4. Наконец, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить, чтобы загрузить эти данные на новый рабочий лист.

Условие if с логикой И

Логика И выполняет несколько логических тестов внутри одного оператора if. Все тесты должны быть истинными для возврата истинного результата. Если любой из тестов ложен, возвращается ложный результат. Синтаксис следующий:

Возьмем вышеупомянутые данные в качестве примера. Я хочу, чтобы новый столбец отображал: если продукт — «Платье», и заказ больше 300, тогда применяется скидка 50% от исходной цены; в противном случае сохраняется исходная цена.

1. Выберите таблицу данных и нажмите Данные > Из таблицы/Диапазона, чтобы перейти в окно Редактора Power Query.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открывшемся диалоговом окне Пользовательский столбец выполните следующие действия:

• Введите имя для нового столбца в текстовое поле Имя нового столбца;
• Затем введите следующую формулу в текстовое поле Формула пользовательского столбца.
• = if [Product] ="Платье" and [Order] > 300 then [Price]*0.5
  else [Price]

3. Затем нажмите кнопку OK, чтобы вернуться в окно Редактора Power Query, и вы получите новый столбец с данными, которые вам нужны, см. скриншот:

4. Наконец, загрузите эти данные на новый рабочий лист, нажав Главная > Закрыть и загрузить > Закрыть и загрузить.

Условие if с логикой ИЛИ и И

Хорошо, предыдущие примеры были легкими для понимания. Теперь давайте усложним задачу. Вы можете комбинировать И и ИЛИ для формирования любых условий, которые вы можете себе представить. В этом типе вы можете использовать скобки в формуле для определения сложных правил.

Возьмем вышеупомянутые данные в качестве примера. Предположим, я хочу, чтобы новый столбец отображал: если продукт — «Платье», и его заказ больше 300, или продукт — «Брюки», и его заказ больше 300, тогда показывать «A+», иначе отображать «Другое».

1. Выберите таблицу данных и нажмите Данные > Из таблицы/Диапазона, чтобы перейти в окно Редактора Power Query.

2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открывшемся диалоговом окне Пользовательский столбец выполните следующие действия:

• Введите имя для нового столбца в текстовое поле Имя нового столбца;
• Затем введите следующую формулу в текстовое поле Формула пользовательского столбца.
• =if ([Product] = "Платье" and [Order] > 300 ) or
  ([Product] = "Брюки" and [Order] > 300 )
  then "A+"
  else "Другое"

3. Затем нажмите кнопку OK, чтобы вернуться в окно Редактора Power Query, и вы получите новый столбец с данными, которые вам нужны, см. скриншот:

4. Наконец, загрузите эти данные на новый рабочий лист, нажав Главная > Закрыть и загрузить > Закрыть и загрузить.