RSS

Функции Excel. Функция СУММЕСЛИМН(), СЧЁТЕСЛИМН()

В статье «Функция СУММЕСЛИ(), СЧЁТЕСЛИ()» уже затрагивалось  рассмотрение таких функций как СУММЕСЛИ и СЧЁТЕСЛИ. Эти функции присутствуют во всех версиях Excel (c 2003-го точно идут в комплекте). Но у них есть небольшой недостаток, который ограничивает область применения или создает некоторые трудности в плане условий, а именно, у них всего один критерий отбора.

 

Начиная с версии Excel 2007 в состав были добавлены более расширенные версии функций СУММЕСЛИ и СЧЁТЕСЛИ, с практически тем же название но с добавлением в конце «МН» (я так полагаю это «МНОЖЕСТВО») — СУММЕСЛИМН() и СЧЁТЕСЛИМН(). Отличие от старых версий это возможность задавать более одного критерия отбора данных для их суммирования или подсчета кол-ва. Но следует так же обратить внимание, что если Вы разрабатываете формулу для отчета который будет использоваться в Excel ниже 2007-го, то увы, работать они не будут. Ну, да ладно, мы смотрим в будущее 🙂

 
Синтаксис функций:
 
СУММЕСЛИМН(диапазон_чисел, диапазон_условия1, условие1, [диапазон_условия2, условие2], и т.д.)
 
Описание аргументов:

  • диапазон_чисел  — обязательный аргумент. Одна или несколько ячеек для суммирования, включая числа, имена, диапазоны или ссылки на ячейки. Пустые значения и текст игнорируются.
  • диапазон_условия1  — второй обязательный аргумент. Первый диапазон, в котором проверяется соответствующее условие.
  • условие1  — третий обязательный аргумент. Условие в виде числа, выражения, ссылки на ячейку или текста, определяющее, какие ячейки в диапазоне_чисел будут просуммированы. Например, условие можно задать как 12, «>12», С5, «яблоки» или «12»(текст).
  • диапазон_условия2, условие2 и т.д. — необязательные аргументы. Дополнительные диапазоны и условия для них. Допускается использование до 127 пар диапазонов и условий.

 
СЧЁТЕСЛИМН(диапазон_условия1, условие1, [диапазон_условия2, условие2]…)
Набор аргументов аналогичен функции СУММЕСЛИМН за исключением только первого аргумента «диапазон_чисел».
Функция считает кол-во значений соответствующих указанным условиям.
 
Итак, рассмотрим на практическом примере работу функции СУММЕСЛИМН.
 

Задача: Дана таблица из трех столбцов. Первый столбец это наименование товара, второй кол-во товара, третий состояние. Состояние имеет три пункта: 0 – Продано, 1 – На складе, 2 – В пути, т.е. еще не доставлены на склад. Необходимо посчитать общую сумму товаров по каждому виду, которые находятся на складе, количество, которое еще в пути и кол-во проданных позиций. Результаты расчета это собственно фактический остаток.

 

Для реализации этой задачи подготовим три таблицы следующего вида:

ScreenShot_12.07.2015_21:43:27

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

ScreenShot_12.07.2015_21:46:47

Диапазон_суммирования мы указывает «$B:$B» т.е. это столбец «Штук». В данном случае я указал полностью столбец без ограничения по строкам (запись B:B), при этом я закрепил этот диапазон (перед литерой столбца стоят знаки «$»). Это необходимо для того чтобы диапазон не «съехал» в случае «протягивания» или копирования формулы со смещением влево или вправо.

 

Диапазон_условия1 —  указываем «$A:$A. Этот столбец содержит первый признак по которому выбираем «Штуки» для суммирования, собственно Условие1 – «$K3» т.е. ссылаемся на ячейку K3(Вафли). Здесь так же закрепление от смещения по столбцам, но по строкам закрепления нет, т.е. протянув формулу вниз, будет меняться нумерация, соответственно и выбираться другие критерии. В нашем случае – Вафли, Апельсины, Яблоки, Груши.

 

Диапазон_условия2 – указываем «$C:$C» т.е. столбец второго критерия отбора «Состояние». Условие2 – указываем «$F$4» здесь мы полностью привязываемся к коду 1 («На складе») т.к. смещение этого параметра нам не нужно не по горизонтали, не по вертикали, потому что заполняем столбец кол-ва товаров «На складе».

 
В итоге формула получиться вида:
=СУММЕСЛИМН($B:$B;$A:$A;$K3;$C:$C;$F$4)
 
Теперь протяните или скопируйте эту формулу на все строки столбца «На складе» до строки «Груши»(6-я строка).
 
Столбцы M (В пути) и N (Продано) заполняются по аналогии со столбцом «На складе» с той лишь разницей, что второе условие меняем на соответствующий код состояния:
ScreenShot_12.07.2015_21:47:09ScreenShot_12.07.2015_21:47:25

Формулы:
=СУММЕСЛИМН($B:$B;$A:$A;$K3;$C:$C;$F$3)
и
=СУММЕСЛИМН($B:$B;$A:$A;$K3;$C:$C;$F$5)
 
Ну, а фактический остаток, это «На складе» — «Продано»:
ScreenShot_12.07.2015_21:47:51

Вот собственно и все. Функция СЧЁТЕСЛИМН, работает аналогично, за тем исключением, что выводит кол-во значений соответствующих критериям отбора, а не их сумму. Располагается в категории «Статистические».
 
Рассмотренный пример Вы можете скачать здесь.
 
Все. До новых встреч!