Как найти значение с двумя или несколькими критериями в Excel?

Excel works!

Как сделать несколько условий в формуле ЕСЛИ?

Распространенный вопрос по Excel «Как записывать несколько условий в одной формуле?». Особенно часто применяется два и более условий при использовании функции ЕСЛИ. Сделать несколько условий в формуле ЕСЛИ довольно просто, главное знать основные принципы. Их и обсудим ниже.

На мой взгляд, стоит рассмотреть пример решения системы условий. Такие задачи часто ставятся в университетах на занятиях по Excel.

Следующая формула довольно громоздка:

Вот пример того, как перенести его в Excel.

Как записать несколько условий в формуле ЕСЛИ?

Для того чтобы создать эту формулу, необходимо как минимум три части:

Более подробную информацию о данном компоненте можно найти здесь.

Но как объединить несколько этих функций в одну, да еще и по условию? Чтобы выяснить это, давайте рассмотрим функцию IF.

Он состоит из следующих элементов

=ЕСЛИ(Условие;если условие = ДА (ИСТИНА);если условие = НЕТ (ЛОЖЬ))

То есть если мы напишем простую формулу, что мы получим в ячейке B2?

Верно — отобразится 100. Если же в А1 будет стоять любое другое значение кроме 1, то в B2 отобразится бы 0.

Наша система условий вернулась. Теперь мы должны научиться писать два условия перед первой точкой с запятой. A1 = 1 и B1 = 0 (знак *), поэтому 100 будет определено тогда и только тогда, когда A1 = 1 и B1 = 0 (знак *).

Особенно обратите внимание на * в скобках

Когда A1=1, а B1=0, то оператор AND* означает, что оба условия должны быть выполнены одновременно.

Если между скобками поставить + (или), то будет достаточно одного из условий. Например, только значение 100 будет отображено, если A1=1.

Мы готовы к написанию формулы, будем это делать по частям

Напишите первое условие

Если условие выполняется, то выполняется первая формула с синусом
Если нет, второе условие

Во всех же остальных случаях будет выполнятся формула =EXP(1/B1)
Итого получается:

Запись нескольких формул в одной

В ячейке B1 есть текст, поэтому формула не сработает. Именно по этой причине я часто использую =ESLIO ERROR().

Представим, что вся наша формула из предыдущего пункта — это один условный аргумент А

Или же, в нашем примере

Образец шаблона можно скачать здесь

Как красиво записать несколько формул

Иногда бывает более 20 Если, тут легко и запутаться… Поэтому существует удобный способ, как записать много формул в одну и удобно отобразить ее в несколько строк. И по прошествии времени разбираться в этой формуле будет легче. С описанием этой отличной возможности мы написали целую статью. Подробно можно прочитать здесь

Функция ЕСЛИ в Excel с несколькими условиями. Примеры использования вложенных функций ЕСЛИ

Мы уже рассмотрели пример функции IF, но часто возникает необходимость использовать несколько условий IF, т.е. поместить несколько IF в одну формулу. При этом у многих возникают вопросы о том, как его реализовать. Эта задача возникает очень часто, и ее давно хотелось переписать. На самом деле в этом нет ничего сложного, но в данном примере мы рассмотрим пример функции IF с несколькими условиями, чтобы иметь четкое представление о том, как ее можно использовать в других примерах.

Пример задачи с использование нескольких функцией ЕСЛИ: У нас есть отчет по продажам продавцов. План продаж считается по количеству проданных единиц товара и составляет от 18 до 20шт. Каждый сотрудник получает оплату 2000 рублей. За каждую дополнительно проданную единицу товара сверх плана сотрудник получает 25 рублей, а за невыполнение плана — штрафуется по 50 рублей за единицу (например, продал 16 шт, недобрав 2 шт до минимального плана 18 шт, следовательно он получает 2000 рублей минус 2*50 рублей то есть 1900 рублей). Нам необходимо рассчитать оплату для каждого сотрудника.

Для вашего удобства мы поместим все условия в отдельную таблицу, чтобы вы могли быстро изменить их в случае необходимости, например, при изменении платежа или плана.

Нам требуется заполнить столбец «Оплата». У нас несколько условий, поэтому одним если обойтись не получится. Вот как мы будет рассматривать эти условия

• Если было более 20 продаж, платеж считается переплатой, иначе проверяется
• Если было менее 18 продаж, платеж считается недоплатой, иначе было 18-20 продаж и сумма платежа составляет 2000 руб.

Для облегчения понимания лучше всего использовать диалоговые окна в самом начале. Позже вы сможете писать функции непосредственно в строке формул. Для начала мы рассчитаем оплату для первого сотрудника Алексея, но сразу после этого подумаем и напишем формулу так, чтобы можно было применить ее и к другим сотрудникам, растянув ее вниз.

Итак, вставьте курсор в ячейку C3 и нажмите на значок выбора функций, перейдите в категорию «Логические» и выберите функцию ЕСЛИ и нажмите «ОК» (см. на рисунок)

Откроется диалоговое окно Функция If.

Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2

Чтобы применить эту формулу к другим сотрудникам в будущем, перетащив ее вниз, нам нужно установить ссылку в ячейке G2 абсолютной (т.е. она не будет меняться при перетаскивании формулы). Для этого перед буквой и числом ставится знак доллара или только перед числом, поскольку мы не растягиваем формулу влево или вправо. Самый удобный способ заблокировать ячейку — выделить ее в формуле и нажать F4

Результат должен быть B3>G$2 или B3>G$2.

Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.

Перейдем к второму аргументу функции.

Значение_если_истина. Переведите ставку в это поле. Здесь мы должны рассчитать зарплату сотрудника, если он соответствует требованиям плана. Мы рассматриваем ситуацию, когда план перевыполнен, поэтому нам нужно добавить сверхурочные к 2000 (F4). Вычтите из общего плана продаж количество продаж, превышающих норму (B3-G2), и умножьте его на 25 рублей, которые выплачиваются за перевыполнение (F5). Расчет выглядит так: 2000 + (35-20) х 25 — это F4 + (B3-G2) х F5.

Также не забудьте установить, чтобы данные таблицы условий перемещались при растягивании формулы. Для этого выберите нужные значения и нажмите F4.

Используя эту формулу: $F$4+(B3-$G$2)*$F$5

Перейдите к следующему полю.

Значение_если_ложь.Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.

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

Снова появится новое диалоговое окно с функцией IF. Не стоит паниковать, предыдущая информация не удаляется, она просто сворачивается. Его можно увидеть на панели форматирования. Это делается для того, чтобы облегчить процесс.

Помните, что мы проверяем условие невыполнения плана сотрудником, поэтому нам необходимо заполнить поле log_expression. То есть мы пишем (выбираем) B3

Затем поле value_if_truth. То есть, если B3

Это дает следующую формулу: $F$4-($F$2-B3)*$F$6.

Штраф. Когда план перевыполнен, мы засчитываем оплату; когда нет, мы проверяем рабочего на невыполнение плана. Оплата засчитывается, если он не выполнил план. Следовательно, если он не перевыполнил и не недовыполнил план, значит, он сделал именно то, что было задумано.

Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку — $F$4

Вот как будет выглядеть вложенное IF

Нажимаем «Ок» — формула готова. Вот так выглядит полная формула:

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

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

Примеры использования функции проверки условия (ЕСЛИ) в Excel

I F — это одна из логических функций в Microsoft Excel, которая чаще всего используется в работе. С помощью этого оператора вы можете выполнять различные задачи, когда вам нужно сравнить некоторые данные и вывести результаты. Используя функцию ЕСЛИ в Excel, вы можете применять в своей работе алгоритмы ветвления, строить дерево решений и т.д.

Видео по использованию функции ЕСЛИ в Excel

Примеры использования оператора ЕСЛИ

Функция IF выглядит следующим образом:

= IF (выражение; истина; ложь)

Теперь немного подробнее:

1. Выражение — это логическое выражение, которое может быть числом или фразой. Например «10» — это логическое выражение, «без НДС» — тоже логическое выражение. Это единственный параметр, который заполняется в обязательном порядке.
2. Истина — значение, которое будет выдано в качестве результата, если выражение окажется истинным.
3. Ложь — значение, которое будет выдано, если выражение будет ложным.

Простейший пример реализации функции ЕСЛИ в Excel — проверяем равенство значений двух ячеек

Например, вы можете ввести число 8 в C1, а в D1 записать следующее: =If(C1<10; 1; 2). То есть, программа сравнит значение C1 с числом 10, и если оно меньше 10 (C1<10), то запишет 1 в D1. В противном случае программа отобразит цифру 2.

Следующий пример: есть список студентов, а также их оценки, которые они получили по экзамену: 5, 4, 3 и 2. Условие задачи заключается в том, что нужно для каждого студента прописать текстовый комментарий «сдал» или «не сдал». То есть если студент получил оценку 3 и выше, то он сдал экзамен, в противном случае — не сдал. Для решения этой задачи можно написать так: =ЕСЛИ(C1<3; «не сдал»; «сдал»). Excel будет сравнивать оценку каждого студента, и когда она будет меньше 3 (т.е. двойка), то в соответствующем поле будет написан комментарий «не сдал». Если же оценка будет 3 или выше, то в ячейке будет отмечено, что этот студент сдал экзамен. По такому несложному принципу работает логическая функция ЕСЛИ в Экселе. Стоит отметить, что текстовые комментарии обязательно нужно брать в кавычки.

При использовании функции IF в Excel можно применять следующие операторы сравнения:

• Больше (>);
• Меньше (<);
• Больше или равно (>=);
• Меньше или равно (<=);
• Равно (=);
• Не равно (<>).

Примеры функций И, ИЛИ

Также при использовании в Экселе функции ЕСЛИ есть возможность соединить ее с простыми операторами сравнения (вроде И, ИЛИ). Например, можно написать такое условие: =ЕСЛИ(И(A1<=5; A1>3); «проходит», «не проходит»). Получается следующее условие: если оценка студента меньше или равна 5 и больше 3, то будет показан комментарий «проходит», в противном случае — «не проходит» (проходят только те студенты, у которых в оценках стоят четверки и пятерки).

Более сложный пример использования функции ЕСЛИ — с использованием условий И либо ИЛИ

Следующее условие с примером оператора ИЛИ: =ЕСЛИ(ИЛИ(A1=5; A1=10); 100; 0). Получается, что если число в ячейке А1 равняется 5 или 10, то программа выдаст 100, в противном случае — 0.

Более того, эти простые операторы можно использовать и для решения более сложных задач. Предположим, например, что нужно найти в базе данных должников, задолжавших более десяти тысяч рублей и не плативших более шести месяцев. Если программа найдет такого человека, то пусть она выдаст критерий «проблемный заемщик».

Допустим, в ячейке A1 находится информация о сроке задолженности (в месяцах), а в поле B1 — сумма задолженности. Тогда формула будет выглядеть следующим образом: =ЕСЛИ(И(A1>=6; B1>10000); «проблемный заемщик»; «»). Если будет найден человек, который подходит под указанное условие, то программа напишет напротив его фамилии комментарий «проблемный заемщик», в противном случае ячейка останется пустой.

Если один из параметров является критическим, то формулу можно написать следующим образом: =If(OR(A1>=6; B1>10000); «критическая ситуация»; «»). Если программа найдет совпадение хотя бы по одному параметру (либо срок, либо сумма долга), то пользователь увидит сообщение о том, что ситуация критическая. В предыдущей формуле сообщение «Проблемный заемщик» выдавалось только при выполнении обоих условий.

Другие примеры использования оператора ЕСЛИ

Функция IF может использоваться для обхода встроенных ошибок деления на ноль и в некоторых других случаях

Иногда Excel выдает ошибку типа «DEL/0», т.е. деление на 0. Чаще всего это происходит, когда формулы A/B копируются в ячейки, а значение B устанавливается равным нулю. Если использовать оператор IF, то можно избежать этой проблемы. Для этого напишите его так: = ЕСЛИ(B1=0; 0; A1/B1). Excel автоматически выведет ноль, если ячейка B1 содержит ноль, в противном случае он разделит A1 на B1 и выведет результат.

Кроме того, расчет скидки на основе общей суммы покупки — еще одна ситуация, которая довольно часто встречается на практике. Вам понадобится матрица следующего вида:

• до 1000 — 0%;
• от 1001 до 3000 — 3%;
• от 3001 до 5000 — 5%;
• свыше 5001 — 7%.

Например, в Excel есть условная база данных клиентов и информация о том, сколько они потратили на покупки. Задача состоит в том, чтобы рассчитать для них скидку. Для этого его можно записать следующим образом: = IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;…). Суть понятна: проверяется общая сумма покупок и если она больше 5001 рубля, то умножается на 93% от цены товара (ячейка B1*0,93); если больше 3001 рубля, то умножается на 95% от цены товара и так далее. Эту формулу легко применить на практике: уровень объемов продаж и скидок определяется по усмотрению компании.

Таким образом, применять функцию ЕСЛИ можно практически в любой ситуации, функциональность Microsoft Excel это позволяет. Главное — правильно составить формулу, чтобы результат не оказался ошибочным.