Функция СУММЕСЛИ. Первый из трех китов Excel, можно сказать. Начинаю обзор главных инструментов любимой нами программы от Microsoft. В начале хочу рассказать о моей часто используемой функции («формуле»): это функция СУММЕСЛИ. Если Вы не представляете, что это такое, и как это использовать — я вам завидую! Для меня она была настоящим открытием в свое время.
Вам приходилось суммировать данные по сотрудникам или клиентам из большой таблицы, выбирать сколько выручки было по той или иной номенклатуре? Вы фильтровали по фамилиям/позициям, а затем вносили числа руками в отдельные ячейки? Может быть считали на калькуляторе? А если строк больше тысячи? Как посчитать быстро результат для каждой из строк?
Вот тут и пригодится СУММЕСЛИ!
Задача1. Существует статистика по товарам, городам и по тому, какие показатели были достигнуты по этим позициям. Необходимо рассчитать «на какую сумму продано номенклатуры Товар1?»
Содержание
Как работает Функция СУММЕСЛИ в Excel? Подсчет суммы по значению
Прежде чем приступить к решению 1ой задачи, разберем из чего состоит функция СУММЕСЛИ:
- Диапазон. Диапазон, в котором содержатся условия поиска. Заполнять обязательно. Для 1ой задачи столбец Товар.
- Критерий. Можно заполнить числом (85), выражением («>85»), ссылкой на ячейку (B1), функцией (СЕГОДНЯ()). Определяет условие, по которому суммируются (!). Все текстовые условия заключаются в кавычки («) «>85». Заполнять обязательно. Для 1ой задачи столбец =Товар1
- Диапазон_суммирования. Ячейки, для суммирования, если они отличаются от ячеек в Диапазоне. Для 1ой задачи столбец Выручка.
Итак запишем формулу, предварительно занеся аргумент условия в ячейку F3
Не забудьте проверить ;) Посчиталось? Верно? Отлично!
Как работает функция СУММЕСЛИМН в Excel? Подсчет суммы по значениям
Задача2. Необходимо рассчитать: «на какую сумму продано номенклатуры Товар3 в городе Казань?»
Т.е. отбор необходимо выполнить по двум параметрам. Для этого используется функция СУММЕСЛИ для нескольких условий — СУММЕСЛИМН, где немного изменяется порядок записи и количество аргументов — сперва пишем из какого столбца суммируем (в примере C:C), а затем уже условия.
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
Если вы хотите, чтобы функция выглядела как на следующей картинке, т.е. имела красивые диапазоны, что позволит удобно использовать их по всей книге и не использовать $ для закрепления ссылок, читайте статью Как сделать красивые диапазоны.
Как работает функция СЧЕТЕСЛИ в Excel? Подсчет количества значений
А также для подсчета количества повторений той или иной позиции используйте функции СЧЁТЕСЛИ (СЧЁТЕСЛИМН)
Задача3. Необходимо рассчитать: «сколько раз повторялось значение=»Самара» в столбце Город?»
Решается она еще проще, чем предыдущие две.
Дерзайте! Желаю удачи!
Скачать пример.
Вторая сильнейшая функция в Excel — это ВПР.
А так же не забывайте про сводные таблицы.