Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Excel works!

menu

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

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

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

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

vAkJzQfNZOo

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

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

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

Несколько Если

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

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

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

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

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

Несколько Если2

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

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

Несколько Если3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Как изменить браузер по умолчанию при открытии гиперссылки в Excel?

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

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

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

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

funkciya-excel-primery-neskolkimi-usloviyami

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

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

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

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

otkryvaem-dialogovoe-okno-funkcii-esli

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

Читайте так же:
Как легко извлечь содержимое комментариев в Excel?

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

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

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

log-vyrazheniya-esli

Обратите внимание, 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

esli-istina-v-funkcii-esli

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

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

Читайте так же:
Как извлечь номер улицы из адреса в Excel?

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

dobavlyaem-vlozhennoe-esli

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

vstavka-vstroennoj-funkcii-esli

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

proveryaem-varazhenie-vo-vlozhennom-esli

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

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

vlozhennaya-funkciya-esli-esli-istina

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

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

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

vlozhennoe-esli-v-excel

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

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

ITGuides.ru

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

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

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

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

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

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

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

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

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

Простейший пример реализации функции ЕСЛИ в 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.

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

Читайте так же:
Как извлечь уникальные значения из нескольких столбцов в Excel?

Допустим, в ячейке 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 это позволяет. Главное — правильно составить формулу, чтобы результат не оказался ошибочным.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector