Функция И в формуле ЕСЛИ с несколькими условиями в Excel

При проверке значений ячеек по нескольким условиям можно не только циркулировать в формулах функцию ЕСЛИ, но и воспользоваться логической функцией =И(). Это хорошо работающий метод в случаях, когда при определении аргумента, возвращаемого формулой необходимо одновременно проверить 2 или несколько условий.

Как использовать функцию И для формулы ЕСЛИ по нескольким условиям

Ниже на рисунке представлен список товаров, количество штук и предоставленная скидка во время продажи. Код каждого товара состоит из трех частей, разделенных девизами между собой:

1. Первая часть — значит номер отдела.
2. Вторая часть определяет является ли данный товар элементом (ELM), полуфабрикатом (PFB) или финальным продуктом (FIN).
3. Третья часть – это уникальный номер-идентификатор из 4-х символов.

Допустим нам необходимо сделать скидку 10% только на финальные продукты из отдела 202. На все остальные товары нет никаких скидок. Формула:

Функция ЕСЛИ возвращает 10% только когда результат логического выражения является ИСТИНА, в противном же случае возвращено значение 0%. В первом аргументе функции должно быть логическое выражение, которое возвратит значение ИСТИНА, в том случае, когда первая часть кода товара равна значению 202. Одновременно вторая часть кода должна быть равна тексту FIN.

Excel предлагает нам функцию =И(), которая реализует данное решение. Данная функция может содержать до 255 логических аргументов, разделенных точкой с запятой между собой. Логические аргументы – это выражения результат вычисления которых возвращает логические значения ИСТИНА или ЛОЖЬ. В описанном выше примере применяется только 2 логических аргумента в функции =И():

1. Первое логическое выражение: =ЛЕВСИМВ(A2;3)="202" используется в первом логическом аргументе и возвращает результат ИСТИНА, если первые 3 символа исходного кода товара в ячейке A2 равно "202".
2. Второе логическое выражение: =ПСТР(A2;5;3)="FIN" во втором логическом аргументе возвращает значение ИСТИНА если 3 подряд символа, начиная от 5-го слева равно "FIN".

Чтобы функция =И() вернула значение ИСТИНА во всех ее логических аргументах должно возвращается значение ИСТИНА. Достаточно лишь одного аргумента который вернет значение ЛОЖЬ и функция =И() также вернет значение ЛОЖЬ. Легко понять на примере в ниже приведенной таблице, где изображены результаты вычисления возвращаемые функцией =И() с двумя логическими аргументами:

Первый логический аргумент	Второй логический аргумент	Результат функции И
ИСТИНА	ИСТИНА	ИСТИНА
ИСТИНА	ЛОЖЬ	ЛОЖЬ
ЛОЖЬ	ИСТИНА	ЛОЖЬ
ЛОЖЬ	ЛОЖЬ	ЛОЖЬ

В формуле ячейки C2 первый логический аргумент в функции =И() возвращает значение ИСТИНА, так как первые 3 символа кода товара равно значению 202. Выражение во втором логическом аргументе этой же функции возвращает значение ЛОЖЬ, так как средняя часть кода товара равно ELM, а не FIN. С учетом выше приведенной таблицы комбинация значений аргументов ИСТИНА и ЛОЖЬ приводит к результату вычисления функции =И() значение ЛОЖЬ. В последствии целая формула возвращает значение 0%.

Функция =И() в формуле ячейки C4 уже возвращает значение ИСТИНА, так как оба ее логические аргументы возвратили значение ИСТИНА.



Пример как работает функция И

Чтобы наглядно продемонстрировать принцип действия функции =И() и формулы в целом рассмотрим другой пример решения для этой же задачи. Функция =И() в выше описанном примере содержит 2 аргумента, в которых логические выражения возвращают значения ИСТИНА или ЛОЖЬ. Эти же аргументы могут содержать ссылки на ячейки вместо логических выражений. Важно только, чтобы ячейки, на которые будет ссылаться функция содержали логические значения. Для наглядности теперь таблица товаров содержит дополнительные 2 столбца как показано ниже на рисунке. По этим столбцам пользователь может проанализировать по какой причине тот или иной товар получил или не получил скидку:

После модификации таблицы стоило модифицировать формулу упростив ее:

Более того ее можно еще на столько упростить что нам не понадобиться даже функция ЕСЛИ при проверке по нескольким условиям.

Как проверить значение по нескольким условиям без функции ЕСЛИ

Чем можно заменить функцию ЕСЛИ в выше описанных формулах при анализе по нескольким условиям в Excel? Учитывая тот факт, что логические значения ИСТИНА и ЛОЖЬ могут быть выражены числами 1 и 0 соответственно. Поэтому в Excel с логическими значениями можно выполнять математические операции. Например, ИСТИНА+1=2 или ЛОЖЬ-5=-5. Следовательно мы можем еще более упростить формулу причем разными способами. Например, заменив в формуле функцию ЕСЛИ на ВЫБОР или ИНДЕКС:

Для этой таблицы мы можем составить формулу вообще без использования любых функций, опираясь лишь только на математические операции с логическими значениями:

Какой из этого всего можно сделать правильный вывод? Благодаря функции =И() в Excel мы можем проверять таблицы по нескольким условиям без использования логической функции ЕСЛИ, чтобы быстро составлять короткие лаконичные формулы. Для практического примера применим эти знания к первой таблице и усложним задачу дав скидку не 10%, а 19,25%:

Как ведите мы правильно проверили все таблицу по нескольким условиям без функции ЕСЛИ, заменив ее на простые математические операции. Но стоит признать, что в данном случае прочитать такую формулу немного сложнее.