Перейти к основному содержанию

Power Query: Оператор If — вложенные if и несколько условий.

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

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

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

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


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

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

= если логическая_проверка, то значение_если_истина, иначе значение_если_ложь
  • логический_тест: условие, которое вы хотите проверить.
  • значение_если_истина: возвращаемое значение, если результат TRUE.
  • значение_если_ложь: возвращаемое значение, если результат FALSE.
Внимание: Power Query Оператор if чувствителен к регистру, if, then и else должны быть в нижнем регистре.

В 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%.;
Советы:
  • Имя столбца: Столбец для оценки вашего условия if. Здесь я выбираю Статус.
  • оператор: Условная логика для использования. Параметры будут различаться в зависимости от типа данных выбранного имени столбца.
    • Текст: начинается с, не начинается с, равняется, содержит и т. д.
    • Номера: равно, не равно, больше или равно и т. д.
    • Время: до, после, равно, не равно и т. д.
  • Значение: Конкретное значение для сравнения вашей оценки. Это вместе с именем столбца и оператором составляет условие.
  • Результат: значение, которое будет возвращено, если условие выполнено.
  • Еще: другое значение, которое нужно вернуть, если условие ложно.

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. В выскочившем Пользовательский столбец диалоговом окне выполните следующие действия:

  • Введите имя нового столбца в Имя нового столбца текстовое окно;
  • Затем введите эту формулу: если [Статус] = "Старый", то "50%", иначе "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 с логикой ИЛИ

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

= если логическая_проверка1 или логическая_проверка2 или …, то значение_если_истина иначе значение_если_ложь

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

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

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

  • Введите имя нового столбца в Имя нового столбца текстовое окно;
  • Затем введите приведенную ниже формулу в поле Формула пользовательского столбца пунктом.
  • = если [Продукт] = "Платье" или [Продукт] = "Футболка", то "ААА"
    еще "БББ"

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

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


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

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

= если логическая_проверка1 и логическая_проверка2 и …, то значение_если_истина иначе значение_если_ложь

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

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

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

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

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

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


Если оператор с логиками ИЛИ и И

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

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

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

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

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

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

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

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

Лучшие инструменты для офисной работы

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

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

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations