Содержание
ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию
Функция СУММЕСЛИ (SUMIF) в Excel похожа на СУММ (SUM), которую мы только что разбирали, поскольку она тоже суммирует значения. Разница лишь в том, что СУММЕСЛИ суммирует только те значения, которые удовлетворяют заданному Вами критерию. Например, простейшая формула с СУММЕСЛИ:
– суммирует все значения ячеек в диапазоне A2:A10, которые больше 10.
Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID (Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:
- Основная таблица (Main table) содержит множество записей для одного ID в случайном порядке.
- Вы не можете добавить столбец с именами продавцов к основной таблице.
Давайте запишем формулу, которая найдет все продажи, сделанные заданным продавцом, а также просуммирует найденные значения.
Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ (SUMIF):
- range (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
- criteria (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
- sum_range (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.
Собрав все воедино, давайте определим третий аргумент для нашей функции СУММЕСЛИ. Как Вы помните, мы хотим суммировать все продажи, совершённые определённым продавцом, чьё имя задано в ячейке F2 (смотрите рисунок, приведённый выше).
- range (диапазон) – так как мы ищем по ID продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table.
-
criteria (критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР для поиска ID, соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:
Конечно, Вы могли бы ввести имя как искомое значение напрямую в функцию ВПР, но лучше использовать абсолютную ссылку на ячейку, поскольку так мы создаём универсальную формулу, которая будет работать для любого значения, введённого в эту ячейку.
- sum_range (диапазон_суммирования) – это самая простая часть. Так как данные о продажах записаны в столбец C, который называется Sales, то мы просто запишем Main_table.
Всё, что Вам осталось сделать, это соединить части в одно целое, и формула СУММЕСЛИ+ВПР будет готова:
Примеры использования
Рассмотрим все возможные ситуации применения одноименной функции.
Общий вид
Разберем простой пример, призванный наглядно показать преимущества использования СУММЕСЛИ.
Есть таблица, в которой указаны порядковый номер, вид товара, наименование, цена за единицу и количество штук на складе.
Допустим, нам нужно выяснить, сколько всего единиц товара находится на складе. Тут все просто – используем СУММ и указываем нужный интервал. Но что делать, если интересует количество единиц одежды? Тут на помощь и приходит СУММЕСЛИ. Функция будет иметь следующий вид:
=СУММЕСЛИ(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
Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ целесообразно использовать в случаях, когда таблица содержит большое количество данных, которые могут быть отфильтрованы по одному или нескольким критериям. При этом в результате применения фильтров будет отображена только часть таблицы, данные в которой соответствуют установленному критерию. Однако операции с использованием обычных функций, таких как СУММ, СРЗНАЧ и др. будут производиться над всей изначальной таблицей (то есть с учетом скрытых строк). Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает только с отфильтрованными данными.
Суммирование только видимых ячеек в фильтре Excel
Пример 1. В таблице содержатся данные о продажах в магазине музыкальных инструментов электрогитар трех марок различных моделей на протяжении трех дней. Рассчитать промежуточные итоги по продажам гитары марки Ibanez.
Вид исходной таблицы данных:
Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу или просто перейдите курсором на любую ячейку таблицы и воспользуйтесь инструментом «ДАННЫЕ»-«Фильтр». Теперь исходная таблица имеет следующий вид:
Нажмем на раскрывающийся список в столбце B («Марка товара») и установим флажок только напротив названия «Ibanez»:
После нажатия на кнопку «ОК» таблица примет следующий вид:
Как видно, некоторые строки теперь являются скрытыми. Если применить обычную функцию СУММ, будет произведен расчет для всех строк исходной таблицы:
Вместо этого в ячейке C24 будем использовать следующую функцию:
Описание аргументов:
- 9 – числовое значение, соответствующее использованию функции СУММ для получения промежуточных итогов;
- C4:C20 – диапазон ячеек, содержащих данные о стоимости гитар (при этом все другие гитары, кроме марки Ibanez, в расчете не учитываются).
Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В результате получим:
Для сравнения приведем результаты, полученные с использованием обычной функцией СУММ:
Несмотря на то, что часть строк скрыта благодаря использованию фильтра, функция СУММ учитывает все строки в расчете.
С этим читают
- Примеры использования функций правсимв и левсимв в excel
- Как создать таблицу в excel: пошаговая инструкция
- 10 популярных функций даты и времени в microsoft excel
- Скрытые ячейки в таблице excel: как показать
- Как сделать таблицу в excel: пошаговая инструкция для начинающих как сделать правильную таблицу (85 фото)
- Как начать работать в excel с нуля без прохождения курсов
- Как в excel посчитать количество дней между двумя датами
- Как изменить названия столбцов c цифр на буквы в excel
- Как защитить ячейку от изменений в excel
- Как легко поставить пароль на файл excel для защиты вашего документа?