Сегодня мы поговорим о функциях, которые могут нам значительно облегчить жизнь, в том случае, если нужно просуммировать значения, но не все, а только удовлетворяющие определенным условиям
Давайте рассмотрим пример:
В приведенной таблице в столбце С указаны фамилии клиентов, а в столбце E указаны суммы.
Разберем, сначала, такую задачу - нам нужно посчитать сумму всех приходов по клиенту Сидоров.
Можно конечно помучаться и сложить все значения, используя обычный калькулятор. Что, конечно, не совсем удобно. Можно, так же, выделить все ячейки напротив фамилии Сидоров, используя клавишу Ctrl. Что тоже займет длительное время.
Мы будем использовать функцию СУММЕСЛИ
Итак. Конструкция этой формулы достаточно проста - в скобках (через ";") нужно указать всего 3 параметра
СУММЕСЛИ(диапазон; условие; [сумма_диапазона])
диапазон - это значения, которые должны соответствовать нашим требованиям (условиям). В нашем случае ячейки из диапазона С4:С17 должны содержать фамилию "Сидоров"
условие - это, собственно, наше требование, в частности "Сидоров"
Сумма_диапазона - это диапазон в котором нужно брать значения для суммирования, если все ок и тест на проверку требованиям пройден. В нашем случае мы берем значения из диапазона Е4:Е17. Следует обратить внимание на то, что это не обязательный параметр. В нашем проимере этот параметр будет обязательным, т.к. проверку на соответствие критерию мы делаем по одним данным (по столбцу с фамилией), а суммируем значения из колонку "Сумма"
Для заданного примера формула будет выглядеть следующим образом:
=СУММЕСЛИ(C4:C17;"Сидоров";E4:E17)
Обратите внимание на написание формулы
СУММЕСЛИ(что проверяем; на что проверяем; что суммируем)
Важный момент! Первый параметр (что проверяем) и третий параметр (что суммируем) являются диапазонами ячеек и ои должны быть равны. Должно быть равное количество аргументов (строк) в каждом диапазоне. Иначе будет ошибка и формула не будет работать.
Второй аргумент (т.е. условие) в нашем примере является текстом, поэтому мы берём его в двойные кавычки. Вместо указания во втором аргументе формулы текста "Сидоров", мы можем сделать ссылку на ячейку, содержащую это условие, например так:
=СУММЕСЛИ(C4:C17;B20;E4:E17)
Вместо "Сидоров" мы вставили ссылку на ячейку, где у нас находится это условие.
Это позволит нам быстро, "на лету" менять условие. Вбиваем в ячейку "Петров" и не требуется перенастраивать формулу. Условие будет автоматически пересчитано.
Давайте разберем следующую задачу: Нам нужно посчитать сумму всех заказов с ценой более 100000.
В этом случае мы можем обойтись только двумя параметрами. Т.к. диапазон, который нужно суммировать и диапазон который нужно проверять будут совпадать
Вот как будет выглядеть формула
=СУММЕСЛИ(E4:E17;">100000")
Первый аргумент - это диапазон (по которому проверяем и который суммируем), второй аргумент это условие. Обратите внимание на то, что вся конструкция в двойных кавычках ">100000". В нашем примере мы считаем все заказы крупнее 100000, а если бы нам нужно было посчитать все заказы равные и более 100000, то следовало бы аргумент написать так: ">=100000"
На всякий случай напомню
"=" - равно
">" - больше
"<" - меньше
">=" - больше либо равно
"<=" - меньше либо равно
"<>" - не равно
СУММЕСЛИМН
Иногда необходимо суммировать диапазон, который удовлетворяет сразу нескольким значением. Для этого используется функция СУММЕСЛИМН
СУММЕСЛИМН(сумма_диапазона; диапазон_критерия1; критерий1; [диапазон_критерия2; …]; [условие2; …])
В отличии от функции СУММЕСЛИ, которую мы разбирали выше, здесь несколько иная последовательность написания аргументов. Первым идет диапазон в котором мы будим выбирать суммируемые значения. Вторым идет диапазон в котором мы будем проверять условие. Далее идет само условие (критерий). Пар диапазон_критерия - критерий может быть несколько. Обратите внимание, что аргументов у этой функции всегда 3 и более и они их всегда нечетное число.
Давайте разберем пример:
Нужно составить формулу для суммирования всех значений столбца Е таблицы, которые более 100000 и менее 300000.
Диапазон суммирования - это все ячейки столбца Е нашей таблицы, а именно Е4:Е17
У нас будет два критерия ">100000" и "<300000"
Диапазон критерия 1 - у нас будет Е4:Е17, и для второго критерия диапазон будет таким-же.
Как будет выглядеть наша формула:
=СУММЕСЛИМН(E4:E17;E4:E17;">100000";E4:E17;"<300000")
В данном случае мы явно прописали наши критерии ">100000" и "<300000", но мы можем вставить ссылки на наши критерии, чтобы не прописывать их каждый раз.
Можно, как и в случае с функцией СУММЕСЛИ, вставить ссылки.
Допустим критерии у нас прописаны в ячейках И21 и С21
В этом случае наша формула примет вид:
=СУММЕСЛИМН(E4:E17;E4:E17;">"&B21;E4:E17;"<"&C21)
Обратите внимание на трансформацию параметра с критерием. Вместо ">100000" у нас появилась следующая конструкция ">"&B21
Очень важный момент
">"- это знак, обозначающий , что нам нужны варианты, которые больше того, что указано в ячейке В21. Знак "больше" взят в кавычки, а ссылку на ячейку нельзя брать в кавычки, иначе она будет преобразована в текст и не будет восприниматься как ссылка на ячейку со значением. Знак "больше" и ссылка на ячейку обязательно "склеиваются" знаком & (амперсанд). Поверьте мне, это очень частая ошибка, когда пользователи забывают проставлять такой знак и формула начинает работать не верно. Запомните, пожалуйста, это правило.
А теперь давайте разберем еще один пример, который встречается достаточно часто в практике.
Нам нужно вычислить сумму заказов сделанных в указанный промежуток дат. Эти даты заданы в ячейках В22 и С22. Т.е. с 1 по 31 октября 2023 года.
Какие параметры у нас будут?
Диапазон суммирования - Е4:Е17
Диапазон критерия 1 - D4:D17
Критерий 1 - ">=01/10/23", а вернее так: ">="&B22
Диапазон критерия 2 - D4:D17, он совпадает с Диапазоном критерия 1
Критерий 2 - "<=31/10/23", а вернее так: "<="&C22
Так будет выглядеть формула в этом случае:
=СУММЕСЛИМН(E4:E17;D4:D17;">"&B22;D4:D17;"<"&C22)
Друзья, я попытался максимально доходчиво объяснить применение функций СУММЕСЛИ и СУММЕСЛИМН. Надеюсь мой урок был полезен. Поставьте лайк и/или напишите отзыв. Спасибо!
Остальные уроки Вы можете посмотреть, перейдя по ссылке: