Как сделать несколько условий в excel

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

В этой статье, я хотел бы поговорить, о самой любимой функции любого экономиста — функция ЕСЛИ в Excel. Эта функция является представителем логических функций во встроенном редакторе функций и там же её можно найти или заглянув в раздел “10 самых используемых функций”. Для более опытных пользователей эту формулу можно вызвать начав вычисления в ячейке, при вводе знака “=” и начале ввода названия функции, Excel моментально предложит все допустимые варианты с которых вы сможете выбрать нужную вам функцию.

Синтаксис рассматриваемой функции достаточно прост, но саму ее суть нужно уловить так как это позволит ее еффективно использовать в различных ситуациях:

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

• лог_выражение — это любое выражение, значение, формула или любое условие результат которого будет значение ИСТИНА или ЛОЖЬ, необходимые для дальнейшего выполнения условий;
• значение_если_истина — указываются какие условия будут выполнятся если результат «логического выражения» будут соответствовать значению ИСТИНА;
• значение_если_ложь — критерий который будет выполнятся при условии получения результата ЛОЖЬ если условия не будут выполнены.

Итак, чем же так хороша эта логическая формула, а тем что в неё можно вложить восем аналогичных логических цепочек, то есть несколько формул ЕСЛИ, вот примеры:

1. Простой вариант:

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

2. Сложный вариант:

И хотя в примере я использовал только 4 варианта и 3 вложения, но в сути я показал как она работает. Как видно функция ЕСЛИ в Excel использует логическое построение, сначала идёт значение, которое, должно быть главным, вторым идёт ответ если в ячейке выполняется главное условие и в этом случае предоставляется текстовое значение. Дальше идёт вариант когда условия не выполнены, я для этого использовал новую формулу условий. А уже в конце подвёл итог, если никакие условия не выполнены, то использовать для данных ячеек текстовое обозначение. Также для получения логического значения, функцию ЕСЛИ используют также, как встроенную, в функциях массивов, математических, статистических, текстовых и прочих.

Вы можете вместо расчёта баллов использовать любые условия и вычисления, которые требуют логического сравнения и вычисления. Например, вычисление ставки заработной платы по категориям, при нормировке штатной численности, вычисление групп амортизации основных средств, да и много другого, где можно встретить градиент значений или условий используя несколько ЕСЛИ в формуле Excel.
Программа также предоставляет разные вариации функции ЕСЛИ и походные от неё совмещение функций, это: СУММЕСЛИ, СУММЕСЛИМН, СЧЕТЕСЛИ, но о них поговорим в других статьях. И в общем можно утверждать что логические функции в Excel одни из самых полезных для использования в бухгалтерском и экономическом расчётах. Также вам пригодится статья «3 главные функции Excel для экономистов и бухгалтеров» или «Секреты Excel, 10 лучших и полезных!».

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

Тот, кто живет по средствам, страдает от недостатка воображения
Андре Стендер, писатель

Функция 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 (ИСТИНА).

|

Функция Если в 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, то должны быть знакомы с функцией поиска и подстановки ВПР или VLOOKUP (если еще нет, то сначала почитайте эту статью, чтобы им стать). Для тех, кто понимает, рекламировать ее не нужно 🙂 — без нее не обходится ни один сложный расчет в Excel. Есть, однако, одна проблема: эта функция умеет искать данные только по совпадению одного параметра. А если у нас их несколько?

Предположим, что у нас есть база данных по ценам товаров за разные месяцы:

Нужно найти и вытащить цену заданного товара (Нектарин) в определенном месяце (Январь), т.е. получить на выходе152, но автоматически, т.е. с помощью формулы. ВПР в чистом виде тут не поможет, но есть несколько других способов решить эту задачу.

Способ 1. Дополнительный столбец с ключом поиска

Это самый очевидный и простой (хотя и не самый удобный) способ. Поскольку штатная функция ВПР (VLOOKUP) умеет искать только по одному столбцу, а не по нескольким, то нам нужно из нескольких сделать один!

Добавим рядом с нашей таблицей еще один столбец, где склеим название товара и месяц в единое целое с помощью оператора сцепки (&), чтобы получить уникальный столбец-ключ для поиска:

Теперь можно использовать знакомую функцию ВПР (VLOOKUP) для поиска склеенной пары НектаринЯнварь из ячеек H3 и J3 в созданном ключевом столбце:

Плюсы : Простой способ, знакомая функция, работает с любыми данными.

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

Способ 2. Функция СУММЕСЛИМН

Если нужно найти именно число (в нашем случае цена как раз число), то вместо ВПР можно использовать функцию СУММЕСЛИМН (SUMIFS) , появившуюся начиная с Excel 2007. По идее, эта функция выбирает и суммирует числовые значения по нескольким (до 127!) условиям. Но если в нашем списке нет повторяющихся товаров внутри одного месяца, то она просто выведет значение цены для заданного товара и месяца:

Плюсы : Не нужен дополнительный столбец, решение легко масштабируется на большее количество условий (до 127), быстро считает.

Минусы : Работает только с числовыми данными на выходе, не применима для поиска текста, не работает в старых версиях Excel (2003 и ранее).

Способ 3. Формула массива

О том, как спользовать связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) в качестве более мощной альтернативы ВПР я уже подробно описывал (с видео). В нашем же случае, можно применить их для поиска по нескольким столбцам в виде формулы массива. Для этого:

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

Как это на самом деле работает:

Функция ИНДЕКС выдает из диапазона цен C2:C161 содержимое N-ой ячейки по порядку. При этом порядковый номер нужной ячейки нам находит функция ПОИСКПОЗ. Она ищет связку названия товара и месяца (НектаринЯнварь) по очереди во всех ячейках склеенного из двух столбцов диапазона A2:A161&B2:B161 и выдает порядковый номер ячейки, где нашла точное совпадение. По сути, это первый способ, но ключевой столбец создается виртуально прямо внутри формулы, а не в ячейках листа.

Плюсы : Не нужен отдельный столбец, работает и с числами и с текстом.

Минусы : Ощутимо тормозит на больших таблицах (как и все формулы массива, впрочем), особенно если указывать диапазоны «с запасом» или сразу целые столбцы (т.е. вместо A2:A161 вводить A:A и т.д.) Многим непривычны формулы массива в принципе (тогда вам сюда).

Несколько условий в Excel

Друзья, добрый день!

Как можно выйти из следующей ситуации?
У меня на листе Лист 1 есть информация по месяцам это строка 1 и по утверждениям 1 это строка 2. Мне необходимо посчитать количество утверждений «да», за март. Можно это сделать одной общей формулой, а не прописывать за каждый день отдельно?
Полученный результат необходимо записать на Лист 2 в ячейку B2.
Пример во вложении.

Несколько условий в Excel
Ребята, все привет! Подскажите, пожалуйста, как можно решить задачу с несколькими критериями.

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

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

Несколько условий
Доброго времени суток, уважаемые форумчане! Давно работаю с excel, много чего повидал, но вот.

Решение

Друзья, помогите пожалуйста! Необходимо по трём условиям выбрать и показать значение.
1 условие — Название центра тестирования; 2 условие — Ступень; 3 условие — Пол. Показать нужно — количество человек.
Таблица с данными на листе «Все». Отобразить найденные значения нужно в сводной таблице на листе «Свод».

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

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

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

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

Проверка несколько условий
Здраствуйте. Подскажите пожалуйста как ее сделать,ил что именно я делаю не правильно. При решении.

ЕСЛИ — несколько условий
Есть данная формула: =ЕСЛИ(‘Общий список’!$B$7=Список!D3;МАКС(Список!$A$2:A2)+1;"") Мне нужно.

Использование оператора «ЕСЛИ» в Microsoft Excel

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

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; [функция если истина]; [функция если ложь])» .

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

Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».

1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

Пример функции с несколькими условиями

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

1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

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

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

1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)» . Вставляем ее в ячейку.

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

Пример использования оператора «ИЛИ»

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

Итак, предположим, что премия к 8 марта в 1000 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник — мужчина или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»; C6=»Вспомогательный персонал»); «0»; «1000»)» . Записываем ее в соответствующую ячейку таблицы.

«Протягиваем» результаты вниз.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.