Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.
Предыдущая глава Содержание Следующая глава
По мере усложнения ваших решений в Power Query вы столкнетесь со сценарием, в котором вам нужно выполнить в столбце некую логику. И хотя в Power Query есть инструмент для этого, он отличается от того что ожидает встретить профессионал Excel.
Допустим вы импортируете расписание из текстового файла:
Рис. 18.1. Текстовый файл содержит проблемы
Скачать заметку в формате Word или pdf, примеры в формате архива
Имя сотрудника не включено в строки. Как его извлечь из шапки? Для решения этой задачи будет применена условная логика. Создайте новую книгу Excel. Пройдите по меню Данные –> Получить данные –> Из файла –> Из текстового/CSV-файла. Выберите файл 2015-03-14.txt. Кликните Импортировать. В окне предварительного просмотра кликните Преобразовать данные. В редакторе Power Query –> Главная –> Удалить строки –> Удаление верхних строк –> 4. Кликните Использовать первую строку в качестве заголовков.
Рис. 18.2. Имя менеджера попала в столбце Out; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
У вас может возникнуть соблазн перенести имя Джона Томпсона в строки. Но есть и другие менеджеры, и вы понятия не имеете, сколько их. Решение может заключаться в том, чтобы добавить столбец с формулой, проверяющей, являются ли данные в столбце Out временем, и извлекающей данные, если тест не выполняется.
Поэкспериментируйте. Щелкните правой кнопкой мыши столбец Out –> Тип изменения –> Время. Как и следовало ожидать, все строки конвертируются красиво, но имя сотрудника возвращает ошибку:
Рис. 18.3. У Джона Томпсона нет времени))
Это ожидаемо, но можно ли это как-то использовать? Вы можете применить функцию Time.From(), чтобы преобразовать данные в допустимое время. И основываясь на знаниях Excel, вы бы ожидали, что это сработает:
(1) =IFERROR(Time.From([Out]),null)
К сожалению, эта формула вернет ошибку, так как Power Query не распознает функцию IFERROR (ЕСЛИОШИБКА). Power Query имеет собственную функцию для такой проверки, хотя и с совершенно иным синтаксисом:
=try <operation> otherwise <alternate result>
Оператор try пытается выполнить операцию. Если это удастся, то возвратит результат операции. Если, результатом является ошибка, то try вернет иное значение (или иную логику), указанное в части otherwise.
Это означает, что формула (1) может быть записана в Power Query следующим образом:
(2) =try Time.From([Out]) otherwise null
Такая формула вернет значение null для любой строки, содержащей имя сотрудника в столбце Out, и время для любой строки, в которой есть допустимое время.
В редакторе Power Query удалите шаг Измененный тип 1. Перейдите на вкладку Добавление столбца, кликните Настраиваемый столбец. Введите формулу (2). Нажмите Ok.
Рис. 18.4. Новый столбец возвращает время и null вместо ошибки
Теперь можно добавить еще один столбец с простой логикой: если Пользовательская содержит null, верни значение из столбца Out, если это не так, верни null. Power Query использует для этого следующий синтаксис:
=if <logical test> then <result> else <alternate result>
Добавление столбца –> Настраиваемый столбец –> Присвойте ему имя Employee. Введите формулу:
=if [Custom]=null then [Out] else null
Рис. 18.5. Наконец, у Джона Томпсона есть своя собственная колонка
Любопытно, если нажать шестеренку рядом со строкой Добавлен пользовательский столбец, появится окно, подсказывающее, как работает условный оператор:
Рис. 18.6. Добавление условного столбца
Сейчас вы можете заполнить имя сотрудника в пустые строки. Щелкните правой кнопкой мыши столбец Employee (сотрудник) –> Заполнить –> Вниз.
Поскольку Power Query обрабатывает шаги последовательно, вам не нужно хранить промежуточные вычисления. Вы можете удалить столбец Пользовательская и очистить остальные данные. Щелкните правой кнопкой мыши столбец Пользовательская –> Удалить. Щелкните правой кнопкой мыши столбец Work Date –> Тип изменения –> Используя локаль –> Дата –> Языковый стандарт –> Английский (США). Перейдите на вкладку Главная. Выберите столбец Work Date –> Удалить строки –> Удалить ошибки. Щелкните правой кнопкой мыши столбец Out –> Тип изменения –> Используя локаль –> Время –> Языковый стандарт –> Английский (США). Выберите столбцы с Reg Hrs по Expense –> Тип изменения –> Используя локаль –> Десятичное число –> Языковый стандарт –> Английский (США). Переименовать запрос в Timesheet. Запрос готов к загрузке:
Рис. 18.7. Табель учета рабочего времени сотрудников
In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.
Power Query doesn’t have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it’s called try otherwise
In this post I’ll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can’t locate a data source.
Watch the Video
Download Sample Excel Workbook
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
First up, let’s load data from this table.
I’ve already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.
But when using Power Query this isn’t always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?
Let’s load the data into Power Query and call it Errors from Sheet
Straight away you can see the errors in the column.
Now of course you could use Remove Errors but that would remove the rows with the errors and that’s not what I want.
Or I could use Replace Errors, but this doesn’t give me any idea what the cause of the error is.
I want to see what caused the error and to do this I’ll add a Custom Column and use try [End]
This creates a new column with a Record in each row
In this record are two fields. HasError states whether or not there’s an error in the [End] column
If there is an Error then the 2nd field is another record containing information about that error
If there isn’t an error, then the 2nd field is the value from the [End] column
If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value
Checking what’s in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query
There’s the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.
If I expand this Error column I can see all of these fields.
I’ve ended up with a lot of extra columns here and it’s a bit messy so let’s tidy it up. In fact I’ll duplicate the query and show you another way to get the same information in a neater way
The new query is called Errors from Sheet (Compact) and I’ve deleted all steps except the first two.
What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.
If there isn’t an error I want the value from the [End] column.
I can do all of this in a new column using an if then else
Add a new Custom Column called Error or Value and enter this code
What this is saying is:
- If the boolean value [HasError] in the [Try_End] column is true then
- return the [Message] in the [Error] record of the [Try_End] column
- else return the [Value] from the [Try_End] column
With that written I can remove both the End and Try_End columns so the final table looks like this
Checking for Errors and Replacing Them With Default Values
In this scenario I don’t care what the error is or what caused it, I just want to make sure my calculations don’t fail.
I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step
I add a new Custom column called Result and what I’ll do here is divide [Start] by [End]
this gives me an error as I know it will in rows 1 and 3
so to avoid this, edit the step and use try .. otherwise
now the errors are replaced with 0.
Errors Loading Data from A Data Source
I’ll create a new query and load from an Excel workbook
Navigating to the file I want I load it
and loading this table
I’m not going to do any transformations because I just want to show you how to deal with errors finding this source file.
I’ll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I’ll get an error. Here I change the drive letter to X.
I don’t have an X: drive so I know this will cause the workbook loading to fail.
So that’s what happens when the file can’t be found so let’s say I have a backup or alternate file that I want to load if my main file can’t be found.
Open the Advanced Editor again and then use try otherwise to specify the backup file’s location
close the editor and now my backup file is loaded.
В Power Query существует функция для проверки условия – if. Чтобы записать определенное условие в Power Query, используется структура с оператором if-then-else.
Создать условие можно двумя способами:
- Через создание условного столбца,
- Написать на Power Query через редактор кода.
Рассмотрим все способы работы с конструкцией if-then-else.
Необходимо проставить статусы для заказов. Статус «выполнен», если % выполнения – 100%. Если меньше 100%, то статус «в работе».
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы/диапазона. Для новых версий Excel: вкладка Данные → Из таблицы/диапазона:
Функцию if очень легко создать с помощью условного столбца. На вкладке Добавление столбца выбираем Условный столбец:
В диалоговом окне заполняем все необходимые поля:
- В поле «Имя нового столбца» вводим имя будущего столбца «Статус».
- В поле «Имя столбца» указываем столбец для оценки условия. Выбираем столбец «% выполнения заказа».
- В поле «Оператор» представлен список операторов. Для нашего примера выбираем оператор «равно».
Для разных типов данных будет предложен разный список операторов сравнения:
- Текст: начинается с, не начинается с, равняется, содержит и т. д.
- Номера: равно, не равно, больше или равно и т. д.
- Время: до, после, равно, не равно и т. д.
Все множество значений будет проверено по первому условию. Если будут найдены результаты, удовлетворяющие первому условию, им будет присвоено значение по результатам выполнения первого условия.
- В поле «Значение» вводим конкретное значение для сравнения.
- В полях «Значение», «Имя столбца» и «Оператор» составляем наше условие.
- В поле «Вывод» указываем значение, которое будет возвращено, если условие выполнено.
- В поле «В противном случае» указываем другое значение, которое нужно вернуть, если условие не выполняется.
В результате получаем новый столбец со статусами:
Осталось выгрузить получившийся отчет в Excel с помощью кнопки «Закрыть и загрузить» на вкладке Главная:
С помощью опции «Условный столбец» вы можете вставить дополнительные условия.
Выбранный столбец по условию
В таблице заказов необходимо перевести все суммы в рубли. Имеются данные по суммам заказов, в какой валюте заказ выполнен и конкретные курсы валют на дату заказа.
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы/диапазона. После загрузки данных в Power Query создаем условный столбец. На вкладке Добавление столбца выбираем Условный столбец:
В диалоговом окне заполняем все необходимые поля:
- В поле «Имя нового столбца» вводим имя будущего столбца «Курс валюты».
- В поле «Имя столбца» указывается столбец для оценки условия. Из выпадающего списка столбцов выбираем столбец «Валюта».
- В поле «Оператор» представлен список операторов. Выбираем для нашего примера оператор «содержит».
- В поле «Значение» вводим конкретное значение для сравнения USD.
Помните, что Power Query чувствителен к регистру: строчные и прописные буквы для него разные символы!
В поле «Вывод» выбираем из выпадающего списка команду «Выберите столбец»:
Далее из выпадающего списка имен столбцов выбираем столбец «USD»:
Теперь пропишем еще два условия. Нажимаем на кнопку «Добавить предложение». После этого появляется новая строчка «Иначе если», которую заполняем по аналогии с первым условием.
В поле «Имя столбца» выбираем столбец «Валюта». В поле «Оператор» выбираем оператор «содержит». В поле «Вывод» выбираем из выпадающего списка команду «Выберите столбец», далее указываем столбец EUR:
Добавляем аналогичное условие для валюты CNY. Осталось заполнить последнее поле в случае невыполнения ни одного из 3-х наших условий. В поле «В противном случае» указываем значение 1, которое нужно вернуть, если условия не выполняются. Это значение будет присвоено, если заказ был сделан в рублях (RUB):
Рассмотрим, как работает условный столбец:
- Все множество значений будет проверено по первому условию. Если будут найдены результаты, удовлетворяющие первому условию, то для них будет присвоено значение по результатам выполнения первого условия.
- Для остальных значений из множества будет проверено следующее условие. И так далее до тех пор, пока не будут проверены все условия.
- Если для элементов множества не будет выполнено ни одно условие, то в условный столбец попадет значение из поля «В противном случае».
Получаем новый столбец с нужными курсами валют:
Для решения нашей задачи создаем настраиваемый столбец, в котором перемножаем столбец с суммами заказов и столбец с нужным курсом:
Присваиваем имя новому столбцу «Сумма заказа в рублях», прописываем формулу и нажимаем Ок:
Осталось выгрузить получившийся отчет в Excel с помощью кнопки Закрыть и загрузить на вкладке Главная:
Базовые условия можно проверить с помощью условного столбца, но задать более сложные условия с вычислениями можно только на языке М через написание кода. Рассмотрим на примере ниже.
Настраиваемый столбец c конструкцией if-then-else
Необходимо присвоить скидку в 10% всем заказчикам со способом оплаты «аванс» и рассчитать цену со скидкой.
Выбираем: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона. Для решения нашей задачи создаем настраиваемый столбец, в котором прописываем конструкцию if…then…else:
Присваиваем имя новому столбцу «Цена со скидкой» и прописываем формулу: если способ оплаты «аванс», то высчитываем цену со скидкой (Цена – Цена*10%). Если другие способы оплаты, то выводим обычную цену.
Обратите внимание, что в качестве разделителя десятичной части используется точка вместо запятой:
В результате работы нашей формулы получаем новый столбец уже с корректной финальной ценой:
Выгружаем получившийся отчет в Excel с помощью кнопки Закрыть и загрузить на вкладке Главная.
Условие с обработкой пустой ячейки null
Функция if имеет особенности в работе с пустыми ячейками (null). При попытке выполнить операции сравнения с null появится ошибка. Значения null можно проверить на равенство, но null равен только null. Если вы хотите сравнить null с любым другим значением при помощи относительного оператора (например, <, >, <=, >=), тогда результат сравнения будет не логическим значением типа true или false, а именно null. В этом случае выражение if…then…else покажет ошибку. Как избавиться от этой ошибки, если ваши данные содержат null, и замена его на другое значение не подходит?
Нужно увеличить стоимость заказа на стоимость доставки (200 руб.) во всех случаях, кроме самовывоза.
Загружаем данные в Power Query с помощью команд Данные → Получить данные → Из таблицы/диапазона. Создаем настраиваемый столбец, выбрав Добавление столбца – Настраиваемый столбец. В диалоговом окне присваиваем имя будущего столбца «Полная стоимость заказа» и прописываем формулу. В столбце «Доставка» имеются пустые ячейки, которые в Power Query считываются как null:
Сначала проверяем значения на равенство с null. Если ячейка в столбце «Доставка» пустая, то выводим значение из столбца «Стоимость заказа». На языке М эта запись будет выглядеть так:
if [Доставка] = null then [Стоимость заказа] elseТеперь записываем следующее условие:
Выражение if…then…else выполняет последовательное вычисление условий. Если первым условием будет идти относительное сравнение, ошибка снова появится и останется до конца расчета выражения. Именно поэтому сначала проверяем значение на наличие null.
В итоге получаем готовый расчет, который выполнен с помощью всего одного шага:
Заключение
Мы разобрали, как писать простые условия с помощью условного столбца и рассмотрели более сложные варианты написания конструкции if…then…else.
Выделим следующие особенности работы с функцией if:
- Условие «if» в Power Query пишется в нижнем регистре, формулы Power Query чувствительны к регистру.
- Вместо запятых, разделяющих аргументы значение_если_истина и значение_если_ложь, используем then и else (в нижнем регистре).
- При попытке выполнить операции сравнения с null появится ошибка. Сначала проверяем на равенство null, а затем записываем последующие условия.
- Текстовый тип данных может сравниваться только с текстом, а числа сравниваются с числами.
- Для чисел можно использовать следующие логические операторы:
- = равно
- <> не равно
- > больше, чем
- >= больше или равно
- < меньше, чем
- <= меньше или равно
6. Для текста и операций сравнения используем разные функции:
- Text.Contains — содержит
- not Text.Contains — не содержит
- Text.StartsWith — начинается с
- not Text.StartsWith — не начинается с
- Text.EndsWith — заканчивается на
- not Text.EndsWith — не заканчивается на
|
Александр L  Пользователь Сообщений: 414 Александр |
Коллеги Всем привет , подскажите пжл возможно ли с помощь Try в Power Query при добавлении доп столбца формулой прописать аналог EСЛИОШИБКА ? |
|
PooHkrd  Пользователь Сообщений: 6602 Excel x64 О365 / 2016 / Online / Power BI |
#2 14.02.2019 13:37:57 try чего-то там otherwise что-то вместо ошибки
Изменено: PooHkrd — 14.02.2019 13:44:59 Вот горшок пустой, он предмет простой… |
||
|
Александр L Пользователь Сообщений: 414 Александр |
#3 14.02.2019 13:39:34
Так у меня вроде так и прописано когда создаю доп столбец но не работает Изменено: Александр L — 14.02.2019 13:40:20 |
||
|
Максим Зеленский  Пользователь Сообщений: 4655 Microsoft MVP 2018-2022 |
#4 14.02.2019 13:42:15 Проще всего так:
Еще можно — создать столбец =[#»Количество (в базовых единицах), короба»]*[Вложения] и использовать в формуле его, чтобы не считать два раза, а потом удалить. F1 творит чудеса |
||
|
Максим Зеленский Пользователь Сообщений: 4655 Microsoft MVP 2018-2022 |
#5 14.02.2019 13:44:00
Потому что деление на 0 это не совсем ошибка, которая стопорит запрос: F1 творит чудеса |
||
|
Александр L Пользователь Сообщений: 414 Александр |
А вы вот как обошли я тоже пробовал через If но применял три условия и вот не получалось(((. Спасибо сейчас попробую на массиве этот метод. |
|
А ещё есть null, деление на который даёт null, а не ошибку и не упомянутое выше |
|
|
Александр L Пользователь Сообщений: 414 Александр |
#8 14.02.2019 13:48:21 да с null я всегда пресекаю на начальном этапе))))) |
В Excel Power Query оператор 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 в Power Query
В Power Query синтаксис такой:
= если логическая_проверка, то значение_если_истина, иначе значение_если_ложь
- логический_тест: условие, которое вы хотите проверить.
- значение_если_истина: возвращаемое значение, если результат TRUE.
- значение_если_ложь: возвращаемое значение, если результат FALSE.
Внимание: оператор Power Query if чувствителен к регистру, если, то и еще должны быть строчными.
В 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 for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
