Функция СУММЕСЛИ. Первый из трех китов 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 — это ВПР.
А так же не забывайте про сводные таблицы.
Комментарии:
( 6 Comments )
Я пока только осваиваю таинства Excel, поэтому простите за дурацкий вопрос: эта функция в какой версиии появилась? У меня стоит версия 2007, что-то пока не могу найти…
Функция суммесли появилась еще в 2003ем excel. СуммеслиМН уже с 2007 года.
Чтобы проще найти функцию в строке формул или ячейке введите «=» и начните набирать «суммес…» на выбор предложат все имеющиеся функции
Добрый вечер.
Вы пишете, что существуют три кита Excel. Про СУММЕСЛИ и ВПР написали, а какой третий кит?
Третий кит — это сводные таблицы.
Самый пожалуй, мощный инструмент Excel. О них будет как минимум две статьи! Пока готовлюсь морально, чтобы было полезно и интересно :)
Здравствуйте!
Подскажите как из массива с формулами суммесли и суммеслимн исключить пустые строки.
Нужно, чтобы сумма в графе количество не суммировалась, когда в ячейках встречаются пустые строки.
Добрый день.
Думаю тут 2 самых простых варианта.
1. Сделать формулу ЕСЛИ, если равно 0, то проставлять пусто («»). Тогда сумма не будет считаться.
2. Отфильтровать данные без 0, и посчитать сумму при помощи ПРОМЕЖУТОЧНЫЕ.ИТОГИ