Суммирование по условию

Суммирование по условию ExcelСуммирование по условию в Excel.

В прошлой статье я писал о функции суммирования — СУММ.

Она позволяет сложить ряд чисел в диапазоне.

Но иногда бывает необходимо просуммировать не все числа, а только те, которые отвечают какому-либо условию.

Условия могут быть совершенно разные. Например, в ряду чисел (см. рисунок) просуммировать только те, что больше  10.

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

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

Вот как это будет выглядеть: =СУММЕСЛИ(A1:A12;»>10″)

А1:А12 — это, как вы понимаете, диапазон суммирования. А вот то, что указано через точку с запятой в кавычках — условие суммирования, больше 10. Соответственно, во всем диапазоне будут суммироваться только числе больше 10.

Признаться, раньше, когда я еще только осваивал Excel, мне было проще использовать диалоговые окна. То есть, не вносить имя функции в строку формул. а выбрать необходимую в библиотеке. Поскольку суммирование (пусть даже и условное), это математическая функция, то выбираем в библиотеке функций (в закладке меню «Формулы») пункт «Математические».

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

Кликаем на имени СУММЕСЛИ. Откроется диалоговое окно, в котором для каждого аргумента предусмотрено отдельное поле. Обратите внимание — названия двух полей в окне выделены жирным, а название третьего поля написано обычным шрифтом. Это означает, что два первых поля — «Диапазон» и «Критерий» — должны быть заполнены обязательно, а поле «Диапазон_суммирования» — не обязательное для заполнения.

Диапазон суммирования, в нашем случае, совпадает с диапазоном критерия суммирования. То есть, нам необходимо просуммировать те же значения, в отношении которых должно работать правило «>10«. А вот для второй задачи, где нужно сложить значения, соответствующие надписи «Еда», эти диапазоны отличаются. Тут мы и используем третий аргумент.

Открываем таблицу, и вызываем диалоговое окно условного суммирования. В первое поле («Диапазон«) заносим не диапазон суммирования, а диапазон, на основании которого будут отбираться складываемые ячейки. Это B2:B7.

Второе поле для критерия. Вносим сюда в кавычках слово «Еда«. А в третье поле, собственно, вносим через двоеточие адреса верхней и нижней ячеек складываемого массива. Сразу можно проверить, какие числа суммируются (справа от поля в фигурных скобках), и какой получается ответ — 437.

Нажимаем «ОК» и любуемся результатом.

Помимо суммирования, по условию можно подсчитывать количество ячеек, отвечающих какому-либо условию. За это отвечает функция СЧЁТЕСЛИ. Ее синтаксис абсолютно идентичен — указываем диапазон, критерий и, при необходимости, диапазон суммирования.

Третья идентичная функция, которая часть используется — СРЗНАЧЕСЛИ. Как вы понимаете, это расчет среднего значения в ряду чисел, отобранных по какому либо условию.

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

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

Ну, и конечно, расчет среднего значения и подсчет количества ячеек по нескольким условиям тоже возможны. Это, соответственно, функции СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *