Green-sell.info

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

Конструкция если в excel

Функция ЕСЛИ в Excel

10.10.2013 Григорий Цапко Функции

Без преувеличения можно сказать, что функция ЕСЛИ в excel является одной из наиболее часто встречаемых и часто используемых. Работа экономиста в excel немыслима без знания этой функции.

Функция ЕСЛИ относиться к логическим функциям MS Excel, и позволяет создавать весьма затейливые конструкции, основываясь на проверке условия и выборе из двух и более альтернатив.

С использованием функции ЕСЛИ в excel можно строить ветвящиеся алгоритмы, строить дерево решений и другие системы и формулы используя вложение одной функции ЕСЛИ в другую и т.д. В Excel 2010 можно использовать до 64 вложений. Это позволяет построить поистине грандиозный алгоритм вычислений.

В общем виде функция ЕСЛИ в excel имеет следующий синтаксис:

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

Рассмотрим синтаксис данной функции на примере алгоритма вычисления суммы с НДС или без НДС.

Лог_выражение – это может быть любое значение или выражение (формула) которое принимает значение ИСТИНА или ЛОЖЬ. Так, например, С5=“с НДС” – это логическое выражение. Если в ячейке С5 находиться текст «с НДС», то логическое выражение принимает значение ИСТИНА, в противном случае – ЛОЖЬ, например, если там содержится текст «без НДС».

Значение_если_истина — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Так, например, если этот аргумент равен формуле С12*1,18, то при значении логического выражения равному ИСТИНА, вычисления ведется по этой формуле.

Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль).

Значение_если_ложь — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ. Так, например, если этот аргумент равен формуле С12*1 или просто С12, то при значении логического выражения равному ЛОЖЬ, вычисления ведется по этой формуле, т.е., как в нашем примере, берется просто значение суммы из ячейки С12

Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней — закрывающая скобка), то возвращается значение 0 (ноль).

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

Пример 1. Использование текстовых значений.

Сразу хочу обратить ваше внимание, что при использовании текстовых выражений в формулах, необходимо заключать их в кавычки “текст” для корректной работы формул.

Например, нам надо добавить какой либо текстовый признак или комментарий, в зависимости от значения той или иной ячейки.

Создаем формулу с использованием функции ЕСЛИ:

=ЕСЛИ(Р20>1000;“превышение лимита”;“в рамках лимита”)

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

Пример 2. Скрытие значений. Скрытие нулевых значений.

Практически повсеместно встречающаяся ситуация – это возникновение ошибки #ДЕЛ/0! при подготовке исходных форматов. Мы готовим исходный формат, в котором в одном из столбцов вычисляем темп роста одного показателя к другому. Как известно, для этого используется формула А/В*100%, но поскольку у нас еще нет данных, то возникает ошибка при делении значений из столбца А на нулевые значения из столбца В.

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

=ЕСЛИ(В10=0;0;А10/В10) или =ЕСЛИ(В10=0;””;А10/В10)

В первом случае будет вместо ошибки #ДЕЛ/0! будет возвращено значение 0, а во втором случае будет просто пустая ячейка. Двойные кавычки возвращают пустое значение в ячейку.

Другой пример, проверка сходимости баланса. В этом случае, нам надо сравнить сумму актива и сумму пассива баланса, и в случае если они равны, т.е. например, С85-С160=0, нам надо скрыть нулевое значение, а в случае если есть расхождение – отобразить это значение.

Пример 3. Многоуровневые, вложенные вычисления.

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

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

Типичный пример, расчет скидки в зависимости от нескольких уровней объема продаж. Или заработка, в зависимости от нескольких уровней выработки.

Предположим у нас следующая матрица скидок:

До 100 000 рублей – 0%

От 100 001 до 300 000 рублей – 3%

От 300 001 до 500 000 рублей – 5%

Свыше 500 001 – 7%

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

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

Предположим, в ячейке D10 у нас находиться объем продаж.

Эта формула в зависимости от уровня объема продаж устанавливает тот или иной уровень скидок. Количество уровней вложения и соответственно уровней скидок может быть гораздо больше.

Операторы сравнения.

В логических выражениях для сравнения значений и формул можно использовать следующие операторы сравнения:

= Больше или равно

Использование операторов сравнения позволяет более гибко использовать функцию ЕСЛИ в excel для решения прикладных экономических задач.

В примере №3 мы как раз использовали оператор >= больше или равно для задания диапазона ссылок.

В дополнение к функции ЕСЛИ в excel можно использовать другие логические функции, еще более расширяя возможности построения сложных алгоритмов и повышая гибкость вычислений.

Функции И, ИЛИ, НЕ

Функция И в excel имеет следующий синтаксис:

Функция возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.

Функция ИЛИ в excel имеет аналогичный синтаксис:

Однако возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА. Если все аргументы имеют значение ЛОЖЬ, то возвращается значение ЛОЖЬ.

Функция НЕ в excel имеет следующий синтаксис:

Функция НЕ используется в тех случаях, когда необходимо убедиться, что значение не равно некой конкретной величине. Она возвращает значение ЛОЖЬ, если значение равно ИСТИНА и наоборот.

Пример 4. Использование нескольких условий.

Предположим, нам надо сделать отбор наших дебиторов, которые задолжали нам за 3 месяца сумму большую 100 000 рублей. Допустим, в этом случае формула должна выводить критерий «злостный задолжник».

В ячейке В10 у нас будет срок задолженности в месяцах, а в ячейке С10 сумма задолженности.

Читать еще:  Создание и форматирование таблиц в excel

Тогда формула будет иметь следующий вид:

Если мы считаем критичным либо задолженность старше 3 месяцев, либо свыше 100 000 рублей, то формулу можно записать так:

В общем, как вы сами понимаете, возможности применения функции ЕСЛИ в excel безграничны.

Самое главное – это продумать алгоритм вычислений, а уж инструментария в MS Excel для его реализации вполне достаточно.

Microsoft Excel содержит дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа вхождений текстовой строки или числа в диапазоне ячеек можно использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, можно использовать функции СУММАЕСЛИ и СУММЕСЛИМН.

Об этих функциях мы поговорим в следующий раз.

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

Однако это был демонстрационный пример. Чаще формулу Эксель ЕСЛИ используют для более сложных проверок. Допустим, есть средненедельные продажи товаров и их остатки на текущий момент. Закупщику нужно сделать прогноз остатков через 2 недели. Для этого нужно от текущих запасов отнять удвоенные средненедельные продажи.

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.

Функция ЕСЛИ() в EXCEL

Функция ЕСЛИ() относится к наиболее часто используемым функциям.

Синтаксис функции

ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])

Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. =ЕСЛИ(A1>=100;»Бюджет превышен»;»ОК!») Т.е. если в ячейке A1 содержится значение большее или равное 100, то формула вернет ОК!, а если нет, то Бюджет превышен .

В качестве аргументов функции, могут участвовать формулы, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше, то по столбцу С .

Вложенные ЕСЛИ

В EXCEL 2007 в качестве значений аргументов значение_если_истина и значение_если_ложь можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ() . =ЕСЛИ(A1>=100;»Бюджет превышен»;ЕСЛИ(A1>=90;»Крупный проект»;ЕСЛИ(A1>=50;»Средний проект»;»Малый проект «)))

Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы:

Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:

Если требуется вывести разный текст в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0, то можно записать следующую формулу:

или, если требуется вместо текстовых значений вывести формулы, можно использовать ссылки на диапазон ячеек (содержащих формулы)

=ПРОСМОТР(A24;<-1E+307;0;1E-307>;A27:A29) (см. файл примера )

Опущен третий аргумент [значение_если_ложь]

Третий аргумент функции не обязателен, если его опустить, то функция вернет значение ЛОЖЬ (если условие не выполнено). =ЕСЛИ(A1>100;»Бюджет превышен») Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.

Вместо ИСТИНА или ЛОЖЬ в первом аргументе введено число

Т.к. значение ЛОЖЬ эквивалентно 0, то формулы =ЕСЛИ(0;»Бюджет превышен»;»ОК!») или (если в ячейке A1 содержится значение 0) =ЕСЛИ(A1;»Бюджет превышен»;»ОК!») вернут ОК!

Если в ячейке A1 находится любое другое число кроме 0, то формула вернет Бюджет превышен . Такой подход удобен, когда проверяется равенство значения нулю.

Связь функции ЕСЛИ() с другими функциями использующие условия

EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .

Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.

Пусть данные находятся в диапазоне A6:A11 (см. файл примера)

Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;»>10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )

Читать еще:  Вегас про видео уроки для начинающих

Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;»>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))

Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))

Функция IF (ЕСЛИ) в Excel. Как использовать?

Функция IF (ЕСЛИ) в Excel – это отличный инструмент для проверки условий на ИСТИНУ или ЛОЖЬ. Если значения ваших расчетов равны заданным параметрам функции как ИСТИНА, то она возвращает одно значение, если ЛОЖЬ, то другое.

Что возвращает функция

Заданное вами значение при выполнении двух условий ИСТИНА или ЛОЖЬ.

Синтаксис

=IF(logical_test, [value_if_true], [value_if_false]) – английская версия

=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь]) – русская версия

Аргументы функции

  • logical_test (лог_выражение) – это условие, которое вы хотите протестировать. Этот аргумент функции должен быть логичным и определяемым как ЛОЖЬ или ИСТИНА. Аргументом может быть как статичное значение, так и результат функции, вычисления;
  • [value_if_true] ([значение_если_истина]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если значение которое вы тестируете соответствует условию ИСТИНА;
  • [value_if_false] ([значение_если_ложь]) – (не обязательно) – это то значение, которое возвращает функция. Оно будет отображено в случае, если условие, которое вы тестируете соответствует условию ЛОЖЬ.

Дополнительная информация

  • В функции IF (ЕСЛИ) может быть протестировано 64 условий за один раз;
  • Если какой-либо из аргументов функции является массивом – оценивается каждый элемент массива;
  • Если вы не укажете условие аргумента FALSE (ЛОЖЬ) value_if_false (значение_если_ложь) в функции, т.е. после аргумента value_if_true (значение_если_истина) есть только запятая (точка с запятой), функция вернет значение “0”, если результат вычисления функции будет равен FALSE (ЛОЖЬ).
    На примере ниже, формула =IF(A1> 20,”Разрешить”) или =ЕСЛИ(A1>20;”Разрешить”) , где value_if_false (значение_если_ложь) не указано, однако аргумент value_if_true (значение_если_истина) по-прежнему следует через запятую. Функция вернет “0” всякий раз, когда проверяемое условие не будет соответствовать условиям TRUE (ИСТИНА).

|

  • Если вы не укажете условие аргумента TRUE(ИСТИНА) (value_if_true (значение_если_истина)) в функции, т.е. условие указано только для аргумента value_if_false (значение_если_ложь), то формула вернет значение “0”, если результат вычисления функции будет равен TRUE (ИСТИНА);
    На примере ниже формула равна = IF (A1>20;«Отказать») или =ЕСЛИ(A1>20;”Отказать”) , где аргумент value_if_true (значение_если_истина) не указан, формула будет возвращать “0” всякий раз, когда условие соответствует TRUE (ИСТИНА).
  • Функция Если в Excel примеры с несколькими условиями

    Пример 1. Проверяем простое числовое условие с помощью функции IF (ЕСЛИ)

    При использовании функции IF (ЕСЛИ) в Excel, вы можете использовать различные операторы для проверки состояния. Вот список операторов, которые вы можете использовать:

    Ниже приведен простой пример использования функции при расчете оценок студентов. Если сумма баллов больше или равна “35”, то формула возвращает “Сдал”, иначе возвращается “Не сдал”.

    Пример 2. Использование вложенной функции IF (ЕСЛИ) для проверки условия выражения

    Функция может принимать до 64 условий одновременно. Несмотря на то, что создавать длинные вложенные функции нецелесообразно, то в редких случаях вы можете создать формулу, которая множество условий последовательно.

    В приведенном ниже примере мы проверяем два условия.

    • Первое условие проверяет, сумму баллов не меньше ли она чем 35 баллов. Если это ИСТИНА, то функция вернет “Не сдал”;
    • В случае, если первое условие – ЛОЖЬ, и сумма баллов больше 35, то функция проверяет второе условие. В случае если сумма баллов больше или равна 75. Если это правда, то функция возвращает значение “Отлично”, в других случаях функция возвращает “Сдал”.

    Пример 3. Вычисляем сумму комиссии с продаж с помощью функции IF (ЕСЛИ) в Excel

    Функция позволяет выполнять вычисления с числами. Хороший пример использования – расчет комиссии продаж для торгового представителя.

    В приведенном ниже примере, торговый представитель по продажам:

    • не получает комиссионных, если объем продаж меньше 50 тыс;
    • получает комиссию в размере 2%, если продажи между 50-100 тыс
    • получает 4% комиссионных, если объем продаж превышает 100 тыс.

    Рассчитать размер комиссионных для торгового агента можно по следующей формуле:

    В формуле, использованной в примере выше, вычисление суммы комиссионных выполняется в самой функции ЕСЛИ . Если объем продаж находится между 50-100K, то формула возвращает B2 * 2%, что составляет 2% комиссии в зависимости от объема продажи.

    Пример 4. Используем логические операторы (AND/OR) (И/ИЛИ) в функции IF (ЕСЛИ) в Excel

    Вы можете использовать логические операторы (AND/OR) (И/ИЛИ) внутри функции для одновременного тестирования нескольких условий.

    Например, предположим, что вы должны выбрать студентов для стипендий, основываясь на оценках и посещаемости. В приведенном ниже примере учащийся имеет право на участие только в том случае, если он набрал более 80 баллов и имеет посещаемость более 80%.

    Вы можете использовать функцию AND (И) вместе с функцией IF (ЕСЛИ) , чтобы сначала проверить, выполняются ли оба эти условия или нет. Если условия соблюдены, функция возвращает “Имеет право”, в противном случае она возвращает “Не имеет право”.

    Формула для этого расчета:

    =IF(AND(B2>80,C2>80%),”Да”,”Нет”) – английская версия

    =ЕСЛИ(И(B2>80;C2>80%);”Да”;”Нет”) – русская версия

    Пример 5. Преобразуем ошибки в значения “0” с помощью функции IF (ЕСЛИ)

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

    Формула для преобразования ошибок в ячейках следующая:

    =IF(ISERROR(A1),0,A1) – английская версия

    =ЕСЛИ(ЕОШИБКА(A1);0;A1) – русская версия

    Формула возвращает “0”, в случае если в ячейке есть ошибка, иначе она возвращает значение ячейки.

    ПРИМЕЧАНИЕ. Если вы используете Excel 2007 или версии после него, вы также можете использовать функцию IFERROR для этого.

    Точно так же вы можете обрабатывать пустые ячейки. В случае пустых ячеек используйте функцию ISBLANK, на примере ниже:

    =IF(ISBLANK(A1),0,A1) – английская версия

    =ЕСЛИ(ЕПУСТО(A1);0;A1) – русская версия

    Оператор “ЕСЛИ” в программе Microsoft Excel: применение и примеры

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

    В этой статье мы поговорим о том, что же такое оператор «ЕСЛИ», а также рассмотрим область применения и принципы работы с ним.

    Содержание: Функция “ЕСЛИ” в Excel

    Определение функции “ЕСЛИ” и ее назначение

    Оператор «ЕСЛИ» – это инструмент программы Эксель для проверки определенного условия (логического выражения) на выполнение.

    То есть, представим, что у нас есть какое-то условие. Задача “ЕСЛИ” – проверить соблюдение данного условия и вывести в ячейку с функцией значение, основанное на результате проверки.

    1. Если логического выражение (условие) выполняется, то значение соответствует истине.
    2. Если логического выражение (условие) не выполняется, значение является ложным.
    Читать еще:  Формула округления в большую сторону excel

    Сама формула функции в программе представляет собой следующее выражение:

    =ЕСЛИ(условие; [значение если условие выполнено]; [значение если условие не выполнено])

    Использование функции “ЕСЛИ” на примере

    Вероятно, вышеизложенная информация может показаться не такой понятной. Но, на самом деле ничего сложного здесь нет. И для того, чтобы лучше понять назначение функции и ее работу рассмотрим пример ниже.

    У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования.

    Наша задача — в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской»

    Заполнение данных вручную займет немало времени, и есть высокая доля вероятности где-то ошибиться, особенно, если список длинный. Гораздо проще в этом случае будет автоматизировать процесс при помощи оператора «ЕСЛИ».

    Для выполнения этой задачи потребуется составить следующую формулу ниже:

    =ЕСЛИ(B2=”женский”;25%;0)

    • Логическое выражение: B2=”женский”
    • Значение в случае, условие выполняется (истина) – 25%
    • Значение, если условие не выполняется (ложь) – 0.

    Данную формулу пишем в самой верхней ячейке столбца “Скидка” и нажимаем Enter. Не забываем ставить знак равно (=) перед формулой.

    После этого для данной ячейки будет отображен результат согласно нашему логическому условию (на забываем настроить формат ячеек – процентный). Если проверка выявит, что пол «женский» – будет выведено значение 25%. В противном случае значение ячейки будет равно 0. Собственно говоря, что нам и требовалось.

    Теперь осталось только скопировать данное выражение на все строки. Для этого подведите курсор мыши к правому нижнему краю ячейки с формулой. Указатель мышки при этом должен превратиться в крестик. Зажмите левую кнопку мыши и растяните формулу на все строки, которые нужно проверить согласно заданным условиям.

    Вот и все, теперь мы применили условие ко всем строкам и получили результат для каждой из них.

    Применение «ЕСЛИ» с несколькими условиями

    Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму.

    Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта.

    Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%.

    Пропишем формулу для этих условий в нужной нам ячейке.

    =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))

    Щелкаем Enter и получаем результат согласно заданным условиям.

    Далее растягиваем формулу на все оставшиеся строки таблицы.

    Одновременное выполнение двух условий

    Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И».

    Рассмотрим на примере нашей таблицы. Теперь скидка 30% будет проставлена только, если это женская обувь и предназначена для бега. При соблюдении этих условий одновременно значение ячейки будет равно 30%, в противном случае – 0.

    Для этого используем следующую формулу:

    =ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)

    Нажимаем клавишу Enter, чтобы отобразить результат в ячейке.

    Аналогично примерам выше, растягиваем формулу на остальные строки.

    Оператор «ИЛИ»

    В этом случае значение логического выражения считается истинным при выполнении одного из условий. Второе условие при этом может не выполняться.

    Поставим задачу следующим образом. Скидка 35% предоставляется только на мужскую обувь для тенниса. Если это мужская обувь для бега или любая женская обувь, скидка равна 0.

    В данном случае нужна следующая формула:

    =ЕСЛИ(ИЛИ(B2=”женский”; C2=”бег”);0;35%)

    После нажатия Enter мы получим требуемое значение.

    Растягиваем формулу вниз и скидки для всего ассортимента готовы.

    Как задать функции ЕСЛИ через Построитель формул

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

    Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%.

    1. Ставим курсор на нужную ячейку, заходим во вкладку “Формулы”, далее щелкаем “Вставить функцию”.
    2. В открывшемся списке Построителя формул выбираем “ЕСЛИ” и нажимаем “Вставить функцию”.
    3. Открывается окно настройки функции. В поле “логическое выражение” пишем условие, по которому будет проводиться проверка. В нашем случае это “B2=”женский”.
      В поле “Истина” пишем значение, которое должно отобразиться в ячейке, если условие выполняется.
      В поле “Ложь” – значение при невыполнении условия.
    4. После того, как все поля заполнены, жмем “Готово”, чтобы получить результат.

    Заключение

    Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ, которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы.

    СТАТЬИ ПО ТЕМЕБОЛЬШЕ ОТ АВТОРА

    Как сделать зачеркнутый текст в Эксель

    Как перенести таблицу из Word в Excel

    Как сделать разделение ячеек в Excel

    Импорт содержимого CSV-файла в Excel

    Защита ячеек от изменений в Эксель

    Статистические функции в Microsoft Excel

    Математические функции в Microsoft Excel

    Извлечение корня в Excel с помощью формулы и функции

    Регрессионный анализ данных в Excel

    Метод наименьших квадратов в Excel

    Как решить транспортную задачу в Excel

    Что делать, если исчезла строка формул в Эксель

    3 КОММЕНТАРИИ

    Полезная статья, приходится много работать в Excel, с этой функцией будет намного проще. Сохраню в закладки.

    Я ничего не знал о функции IF в Excel, очень интересен ее охват и принципы работы с ней. Мне понравилась статья, как хорошо они затрагивают эти вопросы, о которых почти никто не говорит.

    По моему это очень давно и в БД, и в EXCEL существует. На кириллице операторы только непривычны.

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector