Если или в excel много условий

Формула если в excel примеры несколько условий

Если или в excel много условий

Здравствуйте, друзья! Часто ли вам приходится делать выбор? Например, захотели купить новый телефон, а полной суммы денег у вас нет. Взять кредит или копить? Так сегодня разберем как делать выбор в электронных таблицах Excel. Это позволяет сделать условная функция ЕСЛИ().

Условная функция ЕСЛИ()

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

Операции отношения в логических выражениях

Простое условие

Что же делает функция ЕСЛИ()? Посмотрите на схему. Здесь приведен простой пример работы функции при определении знака числа а.

Блок-схема “Простое условие”. Определение отрицательных и неотрицательных чисел

Условие а>=0 определяет два возможных варианта: неотрицательное число (ноль или положительное) и отрицательное. Ниже схемы приведена запись формулы в Excel.

После условия через точку с запятой перечисляются варианты действий. В случае истинности условия, в ячейке отобразится текст “неотрицательное”, иначе – “отрицательное”.

То есть запись, соответствующая ветви схемы «Да», а следом – «Нет».

Текстовые данные в формуле заключаются в кавычки, а формулы и числа записывают без них.

Если результатом должны быть данные, полученные в результате вычислений, то смотрим следующий пример. Выполним увеличение неотрицательного числа на 10, а отрицательное оставим без изменений.

Блок-схема “Простое условие”. Расчет данных

На схеме видно, что при выполнении условия число увеличивается на десять, и в формуле Excel записывается расчетное выражение А1+10 (выделено зеленым цветом). В противном случае число не меняется, и здесь расчетное выражение состоит только из обозначения самого числа А1 (выделено красным цветом).

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

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

Решение:

Решение данной задачи видно на рисунке ниже. Но внесем все-таки ясность в эту иллюстрацию. Основные исходные данные для решения этой задачи находятся в столбцах А и В. В ячейке А5 указано пограничное значение дохода при котором изменяется ставка налогообложения.

Соответствующие ставки указаны в ячейках В5 и В6. Доход фирм указан в диапазоне ячеек В9:В14. Формула расчета налога записывается в ячейку С9: =ЕСЛИ(B9>A$5;B9*B$6;B9*B$5). Эту формулу нужно скопировать в нижние ячейки (выделено желтым цветом).

В расчетной формуле адреса ячеек записаны в виде A$5, B$6, B$5. Знак доллара делает фиксированной часть адреса, перед которой он установлен, при копировании формулы. Здесь установлен запрет на изменение номера строки в адресе ячейки.

Составное условие

Составное условие состоит из простых, связанных логическими операциями И() и ИЛИ().

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

Логическая операция И()

Например: Рассмотрим электронную таблицу “Ведомость сдачи вступительных экзаменов”. Для зачисления абитуриента в ВУЗ, ему необходимо преодолеть проходной балл, и по математике отметка должна быть выше 70 баллов.
Посмотрите внимательно на рисунок ниже.

В этом примере функция ЕСЛИ() использует составное условие, связанное логической операцией И(). Обратите внимание: абитуриент Петров не зачислен, хотя сумма его баллов равна проходному.

Почему так произошло? Посмотрим внимательно на условие в нашей формуле =ЕСЛИ(И(E6>=D2;B6>70);”зачислен”;”не зачислен”).

Логическая операция И() требует выполнения всех условий, но у нас выполняется только одно. Второе условие B6>70 не выполнено, поэтому составное условие принимает значение «ложь».

И на экран выводится сообщение «не зачислен» (вспоминаем схему – ветвь «нет»).

Задание:
В торговой фирме перед Новым Годом устроена праздничная распродажа. Рассчитать сумму продаж с учетом скидки, назначаемой в период распродажи.

1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.

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

3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%).

Решение:

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

  • В ячейке Е7: =B7*C7
  • В ячейке F7: =ЕСЛИ(И(D7>=D$4;D760;E7>D2;);”зачислен”;”не зачислен”). Здесь использована операция ИЛИ(), поэтому достаточно выполнение хотя бы одного условия. Что и произошло, первое условие B7>60 истинно. Оно привело к выводу сообщения о зачислении абитуриента.Задание: В торговой фирме установлены дни распродаж – последние числа месяца. Рассчитать сумму продаж с учетом скидки, назначаемой в дни распродажи.1. Рассчитайте общую стоимость продаж. Итого = Стоимость* Количество.2. Определите скидку (в процентах), используя функцию ЕСЛИ(). Если дата продажи совпадает с датами распродаж, то назначается скидка, иначе скидка равняется нулю. При задании условий используйте логическую функцию ИЛИ().3. Определите сумму продажи с учетом скидки. Сумма продажи с учетом скидки = Итого* (1- Скидка%)Решение:Для проведения расчетов необходимо вписать следующие формулы:
    • В ячейке Е7: =B7*C7
    • В ячейке F7: =ЕСЛИ(ИЛИ(D7=D$4;D7=E$4;D7=F$4);B$4;0)
    • В ячейке G7: =E7*(1-F7)

    и скопировать по соответствующим столбцам до 15 строки включительно.

    Вложенные условия

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

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

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

    1. Определите стаж работы сотрудников. Стаж = 2018- Год приема на работу.

    2.  Используя вложенные функции Если, рассчитайте надбавку для сотрудников. Надбавка (руб.) = Надбавка(%)* Оклад

    Решение:

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

    • В ячейке D9: =2018-B9
    • В ячейке E9: =ЕСЛИ(D9>=B$6;C9*C$6;ЕСЛИ(D9>=B$5;C9*C$5;0))

    и скопировать по соответствующим столбцам до 19 строки включительно.

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

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

    • Формулы эксель с примерами
    • Как работать с таблицей в excel

    PS: Удивительные факты

    Дорогой читатель! Вы посмотрели статью до конца.
    Получили вы ответ на свой вопрос?

    Источник: https://tvojkomp.ru/primery-usloviy-v-excel/

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

    Если или в excel много условий

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

    Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.

    Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

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

    Разберем синтаксис функции:

    Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

    Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

    Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

    Пример:

    Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

    Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.

    Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.

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

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

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

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

    Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

    Примеры несколько условий функции ЕСЛИ в Excel:

    Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

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

    Расширение функционала с помощью операторов «И» и «ИЛИ»

    Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

    Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

    Функции И и ИЛИ могут проверить до 30 условий.

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

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

    Как сравнить данные в двух таблицах

    Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

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

    Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.

    Исходные данные (таблицы, с которыми будем работать):

    Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

    В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

    Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

    Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

    Скачать все примеры функции ЕСЛИ в Excel

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

    Источник: https://exceltable.com/funkcii-excel/funkciya-esli-v-excel

    Формула в Эксель ЕСЛИ, когда несколько условий

    Если или в excel много условий

    Эксель — отличный редактор, позволяющий решать множество задач быстро и просто. Здесь легко посчитать проценты, провести корреляционный анализ, построить графики и т.д.

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

    Рассмотрим на примерах, как работать с формулой ЕСЛИ с несколькими условиями.

    Функция ЕСЛИ

    Написать формулу в Excel можно двумя способами — вручную, введя данные в строку функции или прямо в ячейку, и обратившись к меню. Попробуем разобраться, как пользоваться функцией ЕСЛИ на примере. Алгоритм действий довольно простой — указывается определенное условие и варианты, что следует делать в случае истины или лжи (то есть условие либо выполняется, либо нет):

    • Открываем Эксель, найдя программу в меню «Пуск» или на рабочем столе.
    • Вводим данные, с которыми предстоит работать. Допустим, есть информация о сотрудниках компании — ее и будем использовать в качестве материала.
    • Добавляем столбец «Премия» — в него выводим результаты функции ЕСЛИ. Курсор ставим в ячейку G4.
    • Кликаем по значку функции, расположенному слева от строки ввода оператора, которая находится над рабочим полем. Также формулу в Эксель можно вставить, обратившись к пункту меню «Формулы» и выбрав там «Логические».
    • В «Категории» находим «Логические», а в появившемся списке — функцию ЕСЛИ. Кликаем по «Ок».
    • Появится окно, где необходимо указать аргументы функции, то есть условия.
    • Предположим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения — пишем там адрес ячейки и нужное значение. В нашем случае это выглядит следующим образом: Лог_выражение = D4=«менеджер». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Нажимаем «Ок».
    • В результате видим, что премия первого сотрудника составляет 0%, так как он не является менеджером. Условие выполнено!
    • Таблица содержит множество строк, необходимо заполнить их все. Конечно, не требуется каждый раз вбивать функцию заново, ее надо просто скопировать. Сделать это очень просто — кликаем мышкой по ячейке G4 (там уже получен результат), подводим курсор к левому нижнему углу и смотрим, когда он превратится в плюс.
    • Растягиваем формулу на остальные ячейки, держа левую кнопку мыши нажатой и опускаясь вниз.

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

    Важно: конечно, посмотрев на пример, кажется, что и вручную несложно совершить подобные действия, однако все зависит от объема информации — чем больше данных в таблице, тем сложнее их анализировать визуально. Риск упустить что-то слишком велик, а Excel не только сэкономит время, но и поможет избежать ошибок.

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

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

    • Выделяем мышкой первую ячейку (G4) в столбце с премиями. Кликаем по значку Fx, находящемуся слева от строки ввода формул.
    • Появится окно с уже заполненными аргументами функции.
    • Изменяем логическое выражение, добавив туда еще одно условие и объединив их с помощью оператора И (условия берем в скобки). В нашем случае получится: Лог_выражение = И(D4=«менеджер»;E4=«Южный»). Нажимаем «Ок».
    • Растягиваем формулу на все ячейки, выделив первую и потянув мышкой вниз при нажатой левой клавише.

    Совет: если в таблице много строк, то становится неудобно постоянно перематывать вверх-вниз, чтобы посмотреть шапку. Выход есть — закрепить строку в Excel. Тогда названия столбцов будут всегда показаны на экране.

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

    В качестве примера рассмотрим, как начислить в Экселе премию в размере 40% всем сотрудникам, которые являются бухгалтерами или директорами. То есть произведем выборку по двум условиям:

    • Кликаем мышью по первой ячейке в столбце премий (G4), затем нажимаем на иконку слева от строки ввода формул.
    • Редактируем аргументы функции. Логическое выражение будет представлять собой: ИЛИ(D4=«бухгалтер»;D4=«директор»). В «Значение_если_истина» пишем 40, а в «Значение_если_ложь» — 0. Кликаем «Ок».
    • Копируем формулу, растягивая ее на остальные ячейки. Смотрим результат — премия 40% начислена директору и двум бухгалтерам.

    Функция СУММЕСЛИ

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

    • Добавляем строку с общей зарплатой продавцов и кликаем по ячейке, куда будет выводится результат.
    • Нажимаем на иконку Fx, которая находится слева от строки ввода функций. В открывшемся окне ищем нужную формулу через поиск — вводим в соответствующее окно «суммесли», выбираем оператор в списке, кликаем «Ок».
    • Появится окно, где необходимо заполнить аргументы функции.
    • Вводим аргументы — первое поле «Диапазон» определяет, какие ячейки нужно проверить. В данном случае — должности работников. Кликаем мышкой в поле «Диапазон» и указываем там D4:D18. Можно поступить еще проще — просто выделить нужные ячейки.
    • В поле «Критерий» вводим «продавец». В «Диапазоне_суммирования» пишем ячейки с зарплатой сотрудников (вручную либо выделив их мышкой). Далее — «Ок».
    • Смотрим на результат — общая заработная плата всех продавцов посчитана.

    Совет: сделать диаграмму в Excel просто и быстро — нужно всего лишь найти соответствующую кнопку на вкладке «Вставка» в меню.

    Функция СУММЕСЛИМН

    Данный оператор в Excel предназначен для вычисления суммы с использованием нескольких условий. К примеру, нужно определить заработную плату менеджеров, работающих в Южном филиале:

    • Добавляем строку с общей зарплатой менеджеров Южного филиала. Выделим ячейку, где будет показываться итог вычислений.
    • Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.
    • В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».
    • В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.

    Функция СЧЁТЕСЛИ

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

    • Добавляем строку с количеством продавцов и выделяем ячейку, куда планируется вывести результат.
    • Кликаем по кнопке «Вставить функцию», расположенной во вкладке «Формулы» в меню. В открывшемся окне в поле «Категория» выбираем «Полный алфавитный перечень». В списке формул находим по алфавиту СЧЁТЕСЛИ, нажимаем «Ок».
    • Заполняем аргументы функции — в поле «Диапазон» указываем ячейки с должностями, в «Критерии» пишем «продавец». Далее — «Ок».
    • В результате получаем количество продавцов, работающих в компании. Эксель просто считает ячейки, где написано «продавец».

    Функция СЧЁТЕСЛИМН

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

    • Добавляем строку с количеством продавцов Северного филиала и выделяем ячейку с будущим результатом.
    • Кликаем по кнопке «Вставить функцию» во вкладке «Формулы». Через алфавитный перечень находим нужную функцию и нажимаем «Ок».
    • Вводим аргументы функции: «Диапазон_условия1» — это ячейки с должностями, «Условие1» = «продавец». В «Диапазон_условия2» пишем ячейки с филиалами, «Условие2» = «Северный».
    • В итоге будет определено количество продавцов Северного филиала.

    Подводим итоги

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

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

    Чтобы уточнить синтаксис, лучше воспользоваться справкой, которая в Экселе есть по каждой функции.

    Источник: https://konekto.ru/formula-esli-v-excel.html

    Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах

    Если или в excel много условий

    В этом руководстве объясняется, как использовать функцию СЧЕТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных – числа, даты, текст, символы подстановки. Цель этого поста – продемонстрировать различные подходы и помочь вам выбрать наиболее эффективное решение для каждой конкретной задачи.

    Начиная с версии Excel 2007, Microsoft добавила в Excel «старших сестер» функциям выборочного подсчета СУММЕСЛИСЧЁТЕСЛИ и СРЗНАЧЕСЛИ – функции СУММЕСЛИМНСЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН.

    В английском варианте эти функции выглядят как SUMIFSCOUNTIFS и AVERAGEIFS, т.е. имеют на конце букву -S, обозначающую в английском языке множественное число. В русской версии эту роль играет -МН.

    Их часто путают, поскольку они очень похожи друг на друга и предназначены для подсчета на основе указанных критериев.

    Разница в том, что СЧЕТЕСЛИ предназначен для подсчета ячеек с одним условием в одном диапазоне, тогда как СЧЕТЕСЛИМН может оценивать разные критерии в одном и том же или в разных диапазонах.

    Как работает функция СЧЕТЕСЛИМН?

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

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

    СЧЕТЕСЛИМН(диапазон1;условие1; [диапазон2;условие2]…)

    • диапазон1 (обязательный) – определяет первую область, к которой должно применяться первое условие ( условие1).
    • условие1 (обязательное) – устанавливает требование к отбору в виде числа , ссылки на ячейку , текстовой строки , выражения или другой функции Excel. Определяет, какие ячейки должны учитываться.
    • [диапазон2;условие2]… (необязательные) – это дополнительные области и связанные с ними критерии. Вы можете указать до 127 таких пар.

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

    Что нужно запомнить?

    1. Диапазонов поиска может быть от 1 до 127. Для каждого из них указывается свое условие. Учитываются только те случаи, которые отвечают всем предъявленным требованиям.
    2. Каждый дополнительный диапазон должен иметь одинаковое число строк и столбцов с первым.

      Иначе получите ошибку #ЗНАЧ!

    3. Допускаются как смежные, так и несмежные диапазоны.
    4. Если в аргументе указана ссылка на пустую ячейку , функция обрабатывает его как нулевое значение (0).

    5. В критериях можно использовать символы подстановки – звездочка (*) и знак вопроса (?). Далее мы расскажем об этом подробнее.

    Считаем с учетом всех критериев (логика И)

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

    а. Для каждого диапазона – свой критерий

    Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).

    Задача может быть выполнена таким образом:

    =СЧЁТЕСЛИМН(B2:B11;G1;D2:D11;G2)

    или

    =СЧЁТЕСЛИМН(B2:B11;”>0″;D2:D11;0)

    Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.

    б. Одинаковый критерий для всех диапазонов

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

    Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:

    =СЧЁТЕСЛИМН(B2:B11;0;D2:D11;0)

    Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.

    Использование упрощенного варианта с одним ограничением выбора, например =СЧЁТЕСЛИМН(B2:D11;0), даст другой результат – общее количество ячеек в B2: D11, содержащих ноль (в данном примере это 5).

    Если достаточно выполнения хотя бы одного условия (логика ИЛИ)

    Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем указанным критериям, прост, поскольку функция СЧЕТЕСЛИМН как раз и предназначена для такой работы.

    Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это – 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

    Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН

    Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

    =СЧЁТЕСЛИ(E2:E11;”Отменено”)+СЧЁТЕСЛИ(E2:E11;”Ожидание”)

    В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

    Чтобы получить количество «отмененных» и «отложенных» заказов для клубники, используйте такой вариант:

    =СЧЁТЕСЛИМН(A2:A11;”клубника”;E2:E11;”Отменено”)+СЧЁТЕСЛИМН(A2:A11;”клубника”;E2:E11;”Ожидание”)

    Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива

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

    Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

    СУММ(СЧЁТЕСЛИМН(диапазон;{“условие1″;”условие2″;”условие3”;…}))

    В нашей таблице с примерами для подсчета заказов со статусом «Отменено» или «Ожидание» расчет будет выглядеть следующим образом:

    =СУММ(СЧЁТЕСЛИМН(E2:E11;{“Отменено”;”Ожидание”}))

    Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

    Аналогичным образом вы можете использовать две или более пары диапазон/условие. Чтобы вычислить количество заказов на клубнику, которые отменены или в стадии ожидания, используйте это выражение:

    =СУММ(СЧЁТЕСЛИМН(A2:A11;”Клубника”;E2:E11;{“Отменено”;”Ожидание”}))

    Как сосчитать числа в интервале

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

    1. СЧЕТЕСЛИМН для подсчета ячеек между двумя числами

    Чтобы узнать, сколько было получено заказов количеством товара от 10 до 20, сделаем так:

    =СЧЁТЕСЛИМН(D2:D11;”>10″;D2:D11;”10″)-СЧЁТЕСЛИ(D2:D11;”>20″)

    Это выражение будет возвращать то же количество, как показано на рисунке выше.

    Как использовать ссылки в формулах СЧЕТЕСЛИМН

    При использовании логических операторов, таких как “>”, “=01.02.2020”;D2:D21;”=”&H3;D2:D21;”=”&H3;E2:E21;”>=”&H4)

    и

    =СЧЁТЕСЛИМН(D2:D21;”>=01.02.2020″;E2:E21;”>=05.02.2020″)

    3. Подсчет дат с различными критериями на основе текущей даты

    Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.

    Эта формула с двумя областями и двумя критериями ответит вам, сколько товаров уже куплено, но еще не доставлено.

    =СЧЕТЕСЛИ(D2:D21;””&СЕГОДНЯ())

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

    =СЧЕТЕСЛИ(D2:D21;””&СЕГОДНЯ())

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

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

    Также рекомендуем:

    Как проверить правильность ввода данных в Excel? – Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…

    Источник: https://mister-office.ru/funktsii-excel/function-countifs-examples.html

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

    Если или в excel много условий

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

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

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

    “Как конвертировать Эксель в ПДФ”

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

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

    1. Если логического выражение (условие) выполняется, то значение соответствует истине.
    2. Если логического выражение (условие) не выполняется, значение является ложным.

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

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

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

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

    У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 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 является функция ЕСЛИ, которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы.

    “Возведение числа в квадрат в Эксель”

    Источник: https://MicroExcel.ru/operator-esli/

ЗдоровыйЖелудок
Добавить комментарий