Эксель как суммировать определенным критериям. Финансы в Excel

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

Синтаксис функции и принцип работы

О возможностях сложения известно каждому пользователю, хоть раз запускавшего Excel. СУММЕСЛИ – логичное продолжение базовой СУММ, отличие которой заключается в суммировании по условию.

Синтаксис СУММЕСЛИ выглядит следующим образом:

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

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

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

Примеры использования

Рассмотрим все возможные ситуации применения одноименной функции.

Общий вид

Разберем простой пример, призванный наглядно показать преимущества использования СУММЕСЛИ.

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

Допустим, нам нужно выяснить, сколько всего единиц товара находится на складе. Тут все просто – используем СУММ и указываем нужный интервал. Но что делать, если интересует количество единиц одежды? Тут на помощь и приходит СУММЕСЛИ. Функция будет иметь следующий вид:

=СУММЕСЛИ(C3:C12;»одежда»;F3:F12) , где:

C3:C12 – тип одежды;
«одежда» – критерий;
F3:F12 – интервал суммирования.

В результате получили 180. Функция проработала все указанные нами условия и выдала корректный результат. Также можно повторить подобные манипуляции для других типов товара, сменив критерий «одежда» на какой-то другой.

С несколькими условиями

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

=СУММЕСЛИМН(F3:F12;C3:C12;»одежда»;E3:E12;50) , где:

F3:F12 – диапазон суммирования;
«одежда» и 50 – критерии;
C3:C12 и E3:E12 – диапазоны типов товара и стоимости единицы соответственно.

Внимание! В функции СУММЕСЛИМН аргумент диапазона суммирования стоит в начале формулы. Будьте внимательны!

С динамическим условием

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

Для вставки новой строки нужно нажать ПКМ на интересующей ячейке и выбрать «Вставить» – «Строку».

Далее просто введите новые данные или скопируйте их с другого места. Итоговое значение соответственно изменится. Аналогичные трансформации происходят и при редактировании или удалении строк.

На этом я заканчиваю. Вы увидели основные примеры использования функции СУММЕСЛИ в Excel. Если есть какие-то рекомендации или вопросы – милости прошу в комментарии.

Функция СУММЕСЛИ. Первый из трех китов Excel. Начинаю обзор главных инструментов любимой нами программы от Microsoft. И конечно в начале хочу рассказать о моей самой частоиспользуемой функции («формуле»). А точнее функция СУММЕСЛИ. Если Вы не представляете, что это такое и как это использовать — я Вам завидую! Для меня это было настоящее открытие.

Вам приходилось суммировать данные по сотрудникам или клиентам из большой таблицы, выбирать сколько выручки было по той или иной номенклатуре? Вы фильтровали по фамилиям/позициям, а затем вносили числа руками в отдельные ячейки? Может быть считали на калькуляторе? А если строк больше тысячи? Как посчитать быстро?

Вот тут пригодится СУММЕСЛИ!

Задача1 . Существует статистика по товарам, городам и какие показатели были достигнуты по этим позициям. Необходимо рассчитать: « на какую сумму продано номенклатуры Товар1?»

Как работает Функция СУММЕСЛИ в Excel? Подсчет суммы по значению

Прежде, чем преступить к решению 1ой задачи, разберем из чего состоит функция СУММЕСЛИ:

Диапазон. Диапазон, в котором содержатся условия поиска. Заполнять обязательно. Для 1ой задачи столбец Товар.
Критерий. Можно заполнить числом (85), выражением («>85»), ссылкой на ячейку (B1), функцией (сегодня()). Определяет условие по которому суммируются (!). Все текстовые условия заключаются в кавычки (« ) «>85». Заполнять обязательно. Для 1ой задачи столбец =Товар1
Диапазон_суммирования. Ячейки, для суммирования, если они отличаются от ячеек в Диапазоне. Для 1ой задачи столбец Выручка.

Итак запишем формулу, предварительно занеся аргумент условия в ячейку F3

Не забудьте проверить;) Посчиталось? Верно? Отлично!

Как работает функция СУММЕСЛИМН в Excel? Подсчет суммы по значениям

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

Сейчас мы с вами рассмотрим еще одну очень часто используемую функцию Excel — СУММЕСЛИ .

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

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

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

В нашем примере нам необходимо заполнить вторую таблицу справа – столбец суммы заказов по городам. Как вы видите из синтаксиса функции СУММЕСЛИ нам потребуется три аргумента.

диапазон – это диапазон сравнения, то есть тот массив, в котором будут сравниваться с критериями. В нашем пример это A5:A504 , чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, поставив знак $. Получаем – $A$5:$A$504

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

диапазон_суммирования – это тот диапазон, который необходимо просуммировать, при совпадении критериев. В нашем примере это диапазон: B5:B504 , который мы также для удобства превратим в абсолютный $B$ 5:$B$504

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

Для решения задачи с данным примером в ячейку F5 впишем следующую формулу:

СУММЕСЛИ($A$5:$A$504 ;E5 ;$B$5:$B$504 )

Логика работы функции СУММЕСЛИ следующая: в диапазоне $A$5:$A$504 ищется критерий E5 (Санкт-Петербург) , если Санкт-Петербург находится, то суммируется кол-во заказов из этой строчки то есть из диапазона $B$5:$B$504

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

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

Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Спасибо за внимание.

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

Формулы основаны на таблице, показанной на рис. 118.1 и настроенной на отслеживание счетов. Столбец F содержит формулу, которая отнимает дату из столбца Е от даты в столбце D. Отрицательное число в столбце F означает, что платеж является просроченным. Лист использует именованные диапазоны, соответствующие меткам в строке 1.

Рис. 118.1. Отрицательное значение в столбце F указывает на просроченную оплату Суммирование только отрицательных значений

Следующая формула возвращает сумму отрицательных значений в столбце F. Другими словами, она возвращает общее количество просроченных дней для всех счетов (для таблицы на рис. 118.1 формула вернет -63):
=СУММЕСЛИ(Разница;" .

Функция СУММЕСЛИ может использовать три аргумента. Поскольку вы опускаете третий аргумент, второй аргумент (") относится к значениям диапазона Разница .

Суммирование значений на основе разных диапазонов

Следующая формула возвращает сумму объемов просроченных счетов (в столбце С): =СУММЕСЛИ(Разница;" . Эта формула использует значения диапазона Разница , чтобы определить, вносят ли вклад в сумму соответствующие значения диапазона Сумма .

Суммирование значений на основе сравнения текста

Следующая формула возвращает общую сумму счетов для офиса Орегон: =СУММЕСЛИ(Офис;"=Орегон";Сумма) .

Использовать знак равенства необязательно. Следующая формула дает такой же результат:

Чтобы суммировать счета для всех офисов, кроме Орегона, задайте такую формулу:
=СУММЕСЛИ(Офис;"Орегон";Сумма) .

Суммирование значений на основе сравнения дат

Следующая формула возвращает общую сумму счетов, срок оплаты которых - 10 мая 2010 года или позже:
=СУММЕСЛИ(Дата;">="&ДАТА(2010:5;10);Сумма) .

Обратите внимание, что второй аргумент для функции СУММЕСЛИ является выражением. Выражение использует функцию ДАТА , которая возвращает дату. Кроме того, оператор сравнения, заключенный в кавычки, объединяется (с помощью оператора & ) с результатом функции ДАТА .

Следующая формула возвращает общую сумму счетов, срок оплаты которых еще не настал (включая сегодняшние счета):
=СУММЕСЛИ(Дата;">="&СЕГОДНЯ();Сумма) .
Острый Стоматит у взрослых - заболевание вирусной этиологии, возникает как у взрослых, так и у детей. В последнее время острый герпетический стоматит рассматривают как проявление первичной герпетической инфекции вирусом простого герпеса в полости рта. Инкубационный период заболевания продолжается 1-4 сут. Для продромального периода характерно увеличение поднижнечелюстных (в тяжелых случаях - шейных) лимфатических узлов. Температура тела повышается до 37-41°С, отмечаются слабость, бледность кожных покровов, головная боль и другие симптомы.