KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Power Query: Оператор if — вложенные условия и множественные условия

АвторСяоянДата изменения

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

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

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

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

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

В Power Query синтаксис выглядит так:

= if logical_test then value_if_true else value_if_false
  • логическое_условие: Условие, которое необходимо проверить.
  • значение_если_истина: значение, возвращаемое, если результат — ИСТИНА.
  • значение_если_ложь: значение, возвращаемое, если результат — ЛОЖЬ.
Примечание: оператор if в Power Query чувствителен к регистру; ключевые слова if, then и else должны быть строчными.

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

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

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

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

Пример 1: Простой оператор if

Здесь я покажу, как использовать оператор if в Power Query. Например, у меня есть отчёт по товарам: если статус товара — «Old», применяется скидка 50 %; если статус — «New» — скидка 20 %, как показано на скриншотах ниже.

Снимок экрана, показывающий отчет о товарах с добавленными в Excel столбцами «Статус товара» и «Скидка»

1. Выделите таблицу данных на листе, затем в Excel 2019 и Excel 365 выберите Данные > Из таблицы/диапазона. См. скриншот:

Снимок экрана вкладки «Данные» с выделенным пунктом «Из таблицы/диапазона» в Excel 2019 и Excel 365

Примечание: В Excel 2016 и Excel 2021 нажмите Данные > Из таблицы. См. скриншот:

Снимок экрана вкладки «Данные» с выделенным пунктом «Из таблицы» в Excel 2016 и Excel 2021

2. В открывшемся окне Редактор Power Query нажмите Добавить столбец > Условный столбец, см. скриншот:

Снимок экрана редактора Power Query с выделенными пунктами «Добавить столбец» и «Условный столбец»

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

  • Имя нового столбца: Введите имя для нового столбца;
  • Затем укажите необходимые критерии. Например, я укажу: Если Статус равен «Old», то 50 %, иначе — 20 %;
Советы:
  • Имя столбца: Столбец, по которому будет проверяться условие. Здесь я выбираю «Статус».
  • Оператор: Условная логика, которую можно использовать. Доступные варианты зависят от типа данных выбранного столбца.
    • Текст: начинается с, не начинается с, равно, содержит и т. д.
    • Числа: равно, не равно, больше или равно и так далее.
    • Дата: раньше, позже, равно, не равно и т. д.
  • Значение: Конкретное значение, с которым сравнивается результат оценки. Вместе с именем столбца и оператором оно формирует условие.
  • Результат: значение, возвращаемое при выполнении условия.
  • Иначе: другое значение, возвращаемое, когда условие не выполнено.

Снимок экрана диалогового окна «Добавить условный столбец» в Power Query с настраиваемыми условиями

4. Затем нажмите кнопку ОК, чтобы вернуться в окно Редактор Power Query. Теперь добавлен новый столбец Скидка — см. скриншот:

Снимок экрана редактора Power Query с добавленным новым столбцом «Скидка»

5. Чтобы отформатировать числа как проценты, просто нажмите значок ABC123 в заголовке столбца Скидка и выберите формат Процентный. См. скриншот:

Снимок экрана с нажатой кнопкой значка ABC123 для форматирования столбца «Скидка» в проценты

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

Снимок экрана опции «Закрыть и загрузить» в Power Query для загрузки данных на лист

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

С помощью параметра «Условный столбец» вы можете задать два или более условий в диалоговом окне Добавить условный столбец. Выполните следующие действия:

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

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

  • Введите имя для нового столбца в поле Имя нового столбца;
  • Укажите первое условие в первом поле критериев, затем нажмите кнопку Добавить условие, чтобы при необходимости добавить дополнительные поля.

Снимок экрана диалогового окна «Добавить условный столбец» с несколькими заданными условиями

3. После завершения настройки условий нажмите кнопку OK, чтобы вернуться в окно Power Query Editor. Теперь вы увидите новый столбец с нужным результатом. См. снимок экрана:

Снимок экрана редактора Power Query с новым столбцом, отражающим применение нескольких условий

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

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

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

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

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

1. Выделите таблицу и нажмите Data > From Table/Range, чтобы открыть окно Power Query Editor.

2. В открывшемся окне нажмите Add Column > Custom Column. См. снимок экрана:

Снимок экрана редактора Power Query с выделенными пунктами «Добавить столбец» и «Пользовательский столбец»

3. Во всплывающем диалоговом окне Custom Column выполните следующие действия:

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

Снимок экрана диалогового окна «Пользовательский столбец» в Power Query с базовой формулой IF

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

Снимок экрана редактора Power Query с новым столбцом после применения пользовательской формулы

5. Наконец, нажмите Home > Close & Load > Close & Load, чтобы загрузить эти данные на новый лист.

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

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

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

Снимок экрана набора данных с названиями товаров и ценами, используемого для примеров с вложенными функциями IF

1. Выделите таблицу данных и нажмите Data > From Table/Range, чтобы открыть окно Power Query Editor.

2. В открывшемся окне выберите Add Column > Custom Column. Во вновь открывшемся диалоговом окне Custom Column выполните следующие действия:

  • Введите имя для нового столбца в поле Имя нового столбца;
  • Затем введите приведённую ниже формулу в поле Формула пользовательского столбца формулы.
  • = if [Product] = «Dress» then [Price] * 0,5 else
    if [Product] = «Sweater» then [Price] * 0,8 else
    if [Product] = «Hoodie» then [Price] * 0,8
    else [Price]

Снимок экрана диалогового окна «Пользовательский столбец» с вложенной формулой IF в Power Query

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

Снимок экрана редактора Power Query с новым столбцом, к которому применена логика вложенных функций IF

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

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

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

= if logical_test1 or logical_test2 or … then value_if_true else value_if_false

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

Снимок экрана набора данных, используемого для примеров логики ИЛИ в Power Query

1. Выделите таблицу данных и нажмите Data > From Table/Range, чтобы открыть окно Power Query Editor.

2В открывшемся окне нажмите Add Column>Custom Column. Во вновь открывшемся диалоговом окне Custom Columnвыполните следующие действия:

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

Снимок экрана диалогового окна «Пользовательский столбец» с формулой логики ИЛИ в Power Query

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

Снимок экрана редактора Power Query с новым столбцом, к которому применена логика ИЛИ

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

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

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

= if logical_test1 and logical_test2 and … then value_if_true else value_if_false

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

1. Выделите таблицу с данными и нажмите Data>From Table/Rangeчтобы перейти в Power Query Editorокно.

2. В открывшемся окне нажмите Add Column > Custom Column. Во вновь открывшемся диалоговом окне Custom Column выполните следующие действия:

  • Введите имя для нового столбца в поле Имя нового столбца;
  • Затем введите приведённую ниже формулу в поле Формула пользовательского столбца.
  • = if [Product] =«Dress» and [Order] > 300 then [Price]*0,5
    else [Price]

Снимок экрана диалогового окна «Пользовательский столбец» с формулой логики И в Power Query

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

Снимок экрана редактора Power Query с новым столбцом, к которому применена логика И

4. Наконец, загрузите эти данные на новый лист, выбрав Home > Close & Load > Close & Load.

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

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

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

1. Выделите таблицу с данными и нажмите Data>From Table/Rangeчтобы перейти в Power Query Editorокно.

2В открывшемся окне нажмите Add Column>Custom Column. Во вновь открывшемся Custom Columnдиалоговом окне выполните следующие действия:

  • Введите имя для нового столбца в поле Имя нового столбца;
  • Затем введите приведённую ниже формулу в поле Формула пользовательского столбца.
  • =if ([Product] = «Dress» and [Order] >, 300 ) or
    ([Product] = «Trousers» and [Order] > 300 )
    then «A+»
    else «Other»

Снимок экрана диалогового окна «Пользовательский столбец» с комбинированной логикой И и ИЛИ в Power Query

3. Затем нажмите кнопку ОК, чтобы вернуться в окно Power Query Editor, и вы получите новый столбец с необходимыми данными. См. снимок экрана:

Снимок экрана редактора Power Query с новым столбцом, к которому применена комбинированная логика И и ИЛИ

4. Наконец, загрузите эти данные в Новый лист, нажав Home>Close & Load>Close & Load.

Советы:
В поле формулы пользовательского столбца можно использовать следующие логические операторы:
  • = : Равно
  • : Не равно
  • > : Больше
  • >= : Больше или равно
  • < : Меньше
  • <= : Меньше или равно

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
  • Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек