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.
В 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. Например, у меня есть таблица данных ниже. Если товар «Платье», сделайте скидку 50% от первоначальной цены; если товар «Свитер» или «Толстовка с капюшоном», дайте скидку 20% от первоначальной цены; и другие товары сохраняют первоначальную цену.
1. Выберите таблицу данных и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Power Query редактор окно.
2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец. В открытом Пользовательский столбец диалоговом окне выполните следующие действия:
- Введите имя нового столбца в Имя нового столбца текстовое окно;
- Затем введите приведенную ниже формулу в поле Пользовательский столбец формула пунктом.
- = если [Товар] = "Платье", то [Цена] * 0.5 иначе
если [Товар] = "Свитер", то [Цена] * 0.8 иначе
если [Товар] = "Толстовка", то [Цена] * 0.8
еще [Цена]
3. А затем нажмите OK кнопку, чтобы вернуться к Power Query редактор окно, и вы получите новый столбец с нужными вам данными, см. скриншот:
4, Наконец, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.
Логика ИЛИ выполняет несколько логических тестов, и истинный результат возвращается, если какой-либо из логических тестов верен. Синтаксис:
Предположим, у меня есть приведенная ниже таблица, теперь я хочу, чтобы новый столбец отображался следующим образом: если продукт «Платье» или «Футболка», то бренд — «ААА», бренд других продуктов — «ВВВ».
1. Выберите таблицу данных и нажмите Данные > Из таблицы/диапазона , чтобы перейти к Power Query редактор окно.
2. В открывшемся окне нажмите Добавить столбец > Пользовательский столбец, в открытом Пользовательский столбец диалоговом окне выполните следующие действия:
- Введите имя нового столбца в Имя нового столбца текстовое окно;
- Затем введите приведенную ниже формулу в поле Формула пользовательского столбца пунктом.
- = если [Продукт] = "Платье" или [Продукт] = "Футболка", то "ААА"
еще "БББ"
3. А затем нажмите OK кнопку, чтобы вернуться к Power Query редактор окно, и вы получите новый столбец с нужными вам данными, см. скриншот:
4, Наконец, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить чтобы загрузить эти данные на новый лист.
Логика И выполняет несколько логических тестов внутри одного оператора if. Все тесты должны быть истинными, чтобы возвращался истинный результат. Если какой-либо из тестов является ложным, возвращается ложный результат. Синтаксис:
Возьмите приведенные выше данные, например, я хочу, чтобы новый столбец отображался как: если продукт «Платье» и заказ больше 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. Наконец, вы должны загрузить эти данные в новый рабочий лист, щелкнув Главная > Закрыть и загрузить > Закрыть и загрузить.
В поле формулы Пользовательский столбец можно использовать следующие логические операторы:
- = : равно
- <> : не равно
- > : больше, чем
- >= : больше или равно
- < : меньше чем
- <= : Меньше или равно
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!