3 месяца подписки в подарок | ✆ 8 (800) 550-07-98 Присоединяйтесь
Журнал

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

  • 5 июня 2019
  • 131
Формула ЕСЛИ в Excel: примеры с несколькими условиями

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

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

«СУММЕСЛИ» («СУММЕСЛИМН» тоже) входит в категорию математических и тригонометрических. Разберем, какая формула соответствует СУММЕСЛИ и СУММЕСЛИМН, и как в Excel считают показатели, когда задали несколько условий.

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

=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

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

=СУММЕСЛИ(B2:B9;"бухгалтер";D2:D9)

Получается, что всего отделу бухгалтерии нужно выплатить 238 117 руб.

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

 =СУММЕСЛИ(диапазон_суммирования;диапазон_условия;условие;…)

Женской части коллектива нужно будет выплатить 174 332 руб. Формула для примера выглядит так:

 =СУММЕСЛИМН(D2:D9;B2:B9;"бухгалтер";C2:C9;"ж")

Обратите внимание, что в ячейке после наименования условия (в нашем случае – «бухгалтер»), которое вы будете искать, не должно быть пробела. В противном случае программа не возьмет ячейку в расчет.

Формула ВПР в Excel: примеры с несколькими условиями

Эта опция программы относится к категории «функции ссылки и поиска». Она нужна, чтобы в таблице Excel или в ее определенном диапазоне можно было найти информацию. Для поиска применяют формулу:

=ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

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

Для нашего примера формула изменится таким образом:

=ВПР(A4:A10;ВПР2!A4:B10;2;ЛОЖЬ), где

  • А4:А10 - искомое значение (информация из первого столбца таблицы);
  • ВПР2!A4:B10 – таблица (отсылка к таблице на другой странице)
  • 2 – номер столбца;
  • ЛОЖЬ – интервальный просмотр (позволяет брать точные результаты, а не приблизительные).
Продвинутая работа с формулами в ваших отчетах Excel: бесплатный вебинар

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

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

Получить доступ бесплатно

Формула «СЧЕТЕСЛИ» в Excel. Пример с несколькими условиями

Эта функция относится к категории статистических, как и «СЧЕТЕСЛИМН». Она позволяет определить, сколько ячеек в таблице подходит под определенное условие. Всего у этой функции две задачи:

  • где нужно искать;
  • что нужно искать.

Посмотрим, как работает «СЧЕТЕСЛИ» на практике. Предположим, что предприятие проводит инвентаризацию. Нужно определить, какого имущества у него более 100 единиц. Этот параметр и будет условием для функции.

=СЧЕТЕСЛИ(диапазон;критерий) – так выглядит формула в общем виде

Для нашего примера формулу пропишем как =СЧЕТЕСЛИ(B2:B8;">100").

Получается, что всего три ячейки отвечают нашему условию. Это стулья и компьютеры.

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

=СЧЕТЕСЛИ(A1:A8;"стол")+СЧЕТЕСЛИ(A1:A8;"тумба")

В таблице слова упоминаются в общей сложности два раза.

Подготовлено по материалам
"Системы Финансовый директор"

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

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

Для подсчета будем использовать следующую формулу: =СЧЕТЕСЛИМН(диапазон_условия;условие;…)

Рассчитаем с помощью функции количество дней, когда работник компании работал сверх нормы. То есть, свыше восьми часов. В качестве анализа возьмем период с 1 по 31 мая 2019 года. Зададим формулу для нашего примера:

=СЧЕТЕСЛИМН(A2:A32;">01.05.2019";A2:A32;"<31.05.2019";B2:B32;">8")

В этой формуле всего три критерия: две даты и количество рабочих часов не более восьми.

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

Резюме

Систематизируем информацию о функции «ЕСЛИ» в таблице.

Название

Характеристика

Классификация

Формула

СУММЕСЛИ

суммирует значения, которые соответствуют одному условию

математические и тригонометрические

=СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

СУММЕСЛИМН

складывает ячейки, соответствующие нескольким условиям

математические и тригонометрические

=СУММЕСЛИ(диапазон_суммирования;диапазон_условия;условие;…)

ВПР

ищет информацию по всей таблице или в заданном диапазоне

функции ссылки и поиска

=ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)

СЧЕТЕСЛИ

ищет ячейки под определенное условие

 

статистическая

=СЧЕТЕСЛИ(диапазон;критерий)

СЧЕТЕСЛИМН

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

статистическая

=СЧЕТЕСЛИМН(диапазон_условия;условие;…)

Подписка на статьи

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

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

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

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войти через соц сети
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

Мы подобрали для вас книги:

Цель-2 | Дело не в везении Учитесь видеть бизнес-процессы. Построение карт потоков создания ценности Постороение бизнес-моделей. Настольная игра стратега и новатора
Скачать бесплатно Скачать бесплатно Скачать бесплатно
Как решать нерешаемые задачи, посмотрев проблему с другой стороны Управление финансовой службой по KPI Как не потерять на налогах: проверенные способы
Скачать бесплатно Скачать бесплатно Скачать бесплатно
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.