Вместо спойлера: публикую материал с небольшими правками со своего заброшенного блога. В случае положительной реакции читателей - продолжу публикации на данную тему.
Продолжаем нашу работу по изучению основ Excel.
Сегодня поговорим о выборочном суммировании массивов данных. Для этого будем использовать следующие встроенные функции:
1. =СУММЕСЛИ()
2. =СУММЕСЛИМН()
Для начала создадим следующую таблицу:
Допустим, у нас есть отчет со склада овощного магазина. Перед нами стоят следующие расчетные задачи:
1. Посчитать остатки на складе только одного наименование товара
2. Посчитать остатки по товару, с отпускной ценой, отвечающей определенным требованиям.
Начнем с задачи №1.
Воспользуемся функцией =СУММЕСЛИ(). Ее синтаксис следующий:
=СУММЕСЛИ(Диапазон;Критерий;[Диапазон суммирования])
Где:
Диапазон - диапазон ячеек, который необходимо анализировать.Это может быть диапазон, содержащий как текстовые, так и цифровые значения;
Критерий - критерий отбора. Например "Груши", или, если мы говорим о цифровых значениях и хотим сложить значения больше 50, то это в диапазон следует записать ">50";
Диапазон суммирования - не обязательный параметр. Применяется в том случае, если в поле "Диапазон" стоит диапазон значений, которые нельзя сложить - например, текстовые.
Для реализации первой задачи, нам необходимо записать следующую конструкцию в нужную ячейку:
=СУММЕСЛИ(A2:A9;"Виноград белый";B2:B9)
Возвращаемое значение - 42. Если бы у нас было несколько позиций "Виноград белый", был бы возвращен результат суммы всех таких позиций.
Задача 2.
Попытаемся сложить значения по нескольким критериям.
Для этого в Excel предусмотрена функция =СУММЕСЛИМН(). Синтаксис:=СУММЕСЛИМН(Диапазон суммирования;диапазон условия_1;условие_1;...)
Где:
Диапазон суммирования - аналогичен подобному в функции СУММЕСЛИ()
диапазон условия_1... - Диапазон ячеек, из которого необходимо выбрать нужные ячейки
условие_1.... - условие, на основе которого делается выбор в "диапазон условия_1
Как не сложно догадаться, диапазонов и условий может быть несколько.
Теперь приступим к решению задачи.
Допустим, мы хотим посчитать оставшийся на складе вес яблок, отпускная цена которых выше 100 рублей.
Чтобы решить эту задачу, функцию СУММЕСЛИ() необходимо записать в следующем виде:
=СУММЕСЛИМН(B2:B9;A2:A9;"яблоки*";C2:C9;">90").
Т.е. мы оставляем прежний диапазон суммирования "B2:B9", далее задаем диапазон для первого условия - "А2:А9" и ищем в нем условие "яблоки*". На втором шаге мы выбираем диапазон с отпускными ценами больше >90 рублей в диапазоне "C2:C9". Результат - 35 (15+20).
Прошу Вас обратить внимание на использование знака "*" в условии поиска в текстовом диапазоне. Т.е. мы говорим Excel искать строки, содержащие подстроку "яблоки" после которого могут следовать любые значения. Если бы перед нами стояла задача, скажем, сложить остатки всех зеленых фруктов, то условие выглядело бы, примерно, так "*зеленые".