Сумма элементов между двумя значениями

Была поставлена задача посчитать сумму товаров за которые было заплачено от … до … рублей. То есть необходимо посчитать какова сумма элементов между двумя значениями.

Решить подобную задачу очень просто. Для этого необходимо использовать всего одну функцию «СУММЕСЛИМН». Разберемся, как это сделать.

Исходные значения

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

В которой перечислены товары, приобретенное количество товаров, цена за единицу товара и сумма которую заплатили за весь товар. Посчитаем, какую сумму потратили на оплату товаров, заплатив от 100 до 300 рублей.

Формула

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

Немного отвлеклись, введем на рабочем листе в ячейке G4 такую формулу и разберем ее по параметрам:

=СУММЕСЛИМН(E3:E8;E3:E8;»>=100″;E3:E8;»<=300″)

Первый параметр, определяет диапазон суммирования, в нашем случае это ячейки от Е3 до Е8. Второй параметр считаем что состоит из двух элементов. Первый это диапазон условия, те значения, для которых будет проверяться следующее за этим параметром условие. Так как мы суммируем суммы товаров, да звучит как масло масленое, но всё же. Суммируем суммы товаров и условие проверяем так же на суммы товаров то диапазон условия такой же, как диапазон суммирования, то есть Е3:Е8.

Еще немного про диапазон суммирования и диапазон условия

Отвлечемся еще раз, что бы понять разницу между диапазоном суммирования и диапазоном условия. Пускай данные будут те же, но суммировать мы будем только товары от «А» до «Г», и представим на минутку что таких товаров в таблице не два, а больше. В этом случае наша формула выглядела бы как:

=СУММЕСЛИМН(E3:E8;B3:B8;»>=А»;B3:B8;»<=В»)

То есть диапазон суммирования тот же, а вот диапазон условия другой. В этом примере диапазон условия указывает на названия товаров, расположенные в ячейках В3:В8. Вообще если обратиться к справке по функции то в «Замечаниях» написано «Каждая ячейка в аргументе диапазон_суммирования суммируется только в том случае, если все указанные условия, соответствующие этой ячейке выполнены. Например, формула содержит два аргумента диапазон_условия. Если первая ячейка диапазона_условия1 соответствует условию1, а первая ячейка диапазона_условия2 –условию2, первая ячейка параметра диапазона_суммирования добавляется к сумме».

Если эту длинную, умную мысль выразить по-другому, то происходит вот как…берется первое значение диапазона суммирования, для него проверяется выполняются ли все условия перечисленные в функции и если они выполняются то ячейка суммируется. Получается, что для диапазона суммирования примера и функции проверка условий выглядела бы как:

А – да, Б – да, В – да, Г – нет, Д – нет, Е – нет

И соответственно суммировались бы значения расположенные в тех же строках что и выполняемые условия.

Вернемся к формуле

Второй параметр условия определяет само проверяемое условие. По условиям нашей задачи, первое условие будет выглядеть как «>=100». Аналогичным образом второе условие, определяющее верхнюю границу значений. Диапазон условия всё тот же Е3:Е8, а условие «<=300».

Вот собственно и всё, задали диапазон суммирования, определили диапазоны условий и сами условия, задающие нижнюю и верхнюю границы значений. Таким образом, результат будет выглядеть как:

В этом примере условия точно определенны. Однако что делать если Вы хотите изменять диапазон, задавая границы условий в отдельных ячейках. В таком случае формула стала бы:

=СУММЕСЛИМН(E3:E8;E3:E8;»>=»&G5;E3:E8;»<=»&G6)

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