Функция суммесли excel с примерами(sumif)

СУММ (функция СУММ)

Теперь, если вам нужно рассчитать количество синих, черных, желтых и розовых ячеек, как использовать формулу? Вы можете использовать функцию СУММЕСЛИМН + СУММЕСЛИМН + СУММЕСЛИМН + СУММЕСЛИМН, как указано выше.

Однако что, если вы хотите подсчитать общее количество 20 цветов в 40-цветном блоке?

Вместо записи = СУММЕСЛИМН + СУММЕСЛИМН + СУММЕСЛИМН + СУММЕСЛИМН +. . 20 раз вам просто нужно использовать функцию DSUM. Функция DSUM позволяет создавать условия вне формулы.

Давайте рассмотрим пример, чтобы вы лучше поняли эту функцию.

Чтобы вычислить общее количество синих, желтых и черных ячеек, создайте условия вне таблицы данных.

Сначала вам нужно написать заголовок столбца, в данном случае Color. Ниже вы записываете каждое условие, которое будет суммироваться в каждой строке. При написании этого условия под другим условием вы создаете условия Или.

Формула будет такой:

= СУММ (A1: J1001; I1; M21: M24)

Общая формула для функций DSUM:

= DSUM (База данных, Поле, Критерии)

Внутри:

1. База данных: это весь объем данных. В данном случае A1: J1001.
2. Поле: заголовок суммируемого столбца, в данном примере — количество столбца (I1).
3. Критерии — это диапазон данных, которые вы создаете за пределами. Вот диапазон от ячейки M21 до M24.

Пример 2:

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

Когда вы пишете критерии рядом, это означает, что вы содействуете И, запись этого критерия под другим критерием означает содействие Или.

Формула, используемая для расчета общей суммы в соответствии с вышеуказанными условиями, следующая:

= СУММ (A1: J1001; I1; M28: N30)

Функция СУММЕСЛИ при условии соответствия тексту

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

1. В этот раз помимо диапазона суммируемых ячеек определите и те, где присутствуют надписи, попадающие в условие.

Начните запись функции с ее обозначения точно так же, как это уже было показано выше.

В первую очередь введите диапазон надписей, поставьте ; и задайте условие. Тогда это выражение в синтаксическом формате обретет примерно такой вид: A2:A25;«Сентябрь»;.

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

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

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

Использование операторов сравнения.

Рассчитываем стоимость заказов для «Красного» покупателя, в которых было более 100 единиц товара. Как видите, здесь нужно использовать как текстовые, так и числовые критерии.

Критерии можно записать в самой формуле, и это будет выглядеть так:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; «Красный»; RE2: RE21; «> 100”)

Но рациональнее использовать ссылки, как это сделано на рисунке:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; I2; RE2: RE21; I4)

Примечание

Обратите внимание, что логические выражения с операторами сравнения всегда должны заключаться в кавычки («») в формулах («»)

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

Как еще мы можем решить нашу проблему?

Способ 2. Используем функцию СУММПРОИЗВ.

Давайте подробнее рассмотрим, как работает SUMPRODUCT():

= СУММПРОИЗВ (- (B2: B21 = $ I $ 12), — (D2: D21> I13), E2: E21)

Результат вычисления B2: B21 = $ I $ 12 — это массив

{ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ.: ЛОЖЬ

ИСТИНА означает, что код покупателя соответствует условию, например, слово Красный. Этот массив можно увидеть, выбрав в строке формул B2: B21 = $ I $ 12, а затем нажав F9.

А что это за странные минусовые знаки перед этими выражениями? Дело в том, что нам нужны не эти логические выражения, а числа, чтобы затем их можно было умножить и сложить. Если Excel выполняет математическую операцию с логическим выражением, он автоматически преобразует его в число. А знак минус означает умножение на -1. А если умножить на -1 дважды, результат не изменится. Мы помним это из школьной математики

И в результате логический массив превратится в массив чисел {0: 1: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 1: 0: 0: 0: 1: 0: 0: 0}.

Результат вычисления D2: D21> I13 — это массив

{ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ}.

ИСТИНА соответствует пределу «количество больше 100». Здесь мы также используем двойное отрицание для преобразования логических значений в числа.

И, наконец, результатом вычисления B2: B13 является массив {11250: 23210: 12960: 3150: 5280: 9750: 3690: 18300: 5720: 6150: 8400: 2160: 7200: 1890: 17050: 3450: 15840: 2250 : 7200: 8250}, т.е только числа из столбца E.

Результатом умножения этих трех массивов на элемент будет {0: 23210: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 15840: 0: 0: 0 }. Складываем эти продукты и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета — применяем формулу массива. В I14 пишем:

= СУММ ((L2: L21 = I12) * (L2: L21> I13) * (L2: E21))

Не забудьте нажать CTRL + SHIFT + ENTER в конце, чтобы обозначить это выражение как формулу массива. Программа автоматически добавит фигурные скобки в начало и в конец. Опять получаем результат 39050.

Способ 4. Автофильтр.

Другой альтернативой является использование автоматического фильтра. Для этого преобразуйте диапазон данных A1: F21 в интеллектуальную таблицу. Напоминаю, что для этого в меню «Главная» выберите «Форматировать как таблицу». Далее добавляем итоговую строку (вкладка «Дизайн») и устанавливаем необходимые фильтры.

Без формул будет определено общее количество отфильтрованных строк.

Задача1 (1 текстовый критерий и 1 числовой)

Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):

Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)

• B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
• A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
• D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.

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

2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :

• Результатом вычисления A2_A13=D2 является массив Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2_A13=D2 , а затем нажав F9 ;
• Результатом вычисления B2:B13 является массив, т.е. просто значения из столбца B ;
• Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является . При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
• Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив . Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
• Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .

4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).

Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Примеры использования функции СУММЕСЛИ в Microsoft Excel

Функция СУММЕСЛИ популярна, поскольку почти в каждой таблице нужно посчитать сумму чисел в ячейках, игнорируя значения, не попадающие под основное условие. Благодаря этой формуле подсчет не становится чем-то сложным и длительным. Стандартное оформление функции выглядит как =СУММЕСЛИ(Диапазон;Критерий;Диапазон_суммирования), а «Диапазон суммирования» указывается только при условии, когда есть фактические ячейки, сложение которых выполняется при любых обстоятельствах. Если данные в «Диапазоне суммирования» отсутствуют, проверяться будут все ячейки, входящие в «Диапазон».

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

Функция СУММЕСЛИ при условии неравенства

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

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

Выше был разобран только один из примеров со случайно взятыми условиями. Ничего не помешает подставить другие значения, расширить или сузить диапазон — формула все равно нормально посчитает значение, если правила синтаксиса были соблюдены.

Функция СУММЕСЛИ при условии соответствия тексту

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

1. В этот раз помимо диапазона суммируемых ячеек определите и те, где присутствуют надписи, попадающие в условие.

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

Функция СУММЕСЛИМН с несколькими условиями

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

1. Возьмем таблицу, в которой есть три разных значения как по находящимся там данным, так и по их типам. Это месяц, общая прибыль и количество проданных единиц товара. В формуле с несколькими условиями нужно сделать так, чтобы в итоговую сумму попали только те результаты прибыли, которые были получены в сентябре при продажах выше 29 единиц.

Мы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12345 инструкций. Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Примеры использования функции СУММЕСЛИМН

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

Динамический диапазон суммирования по условию

Итак, начнем с первого примера. Допустим, у нас есть таблица, в которой содержится информация о том, как справляются студенты с учебной программой по определенному предмету. Есть набор оценок, успеваемость оценивается по 10-балльной шкале. Стоит задача найти оценку за экзамен тех студентов, фамилия которых начинается с буквы А, а их минимальный балл составляет 5.

Таблица выглядит следующим образом.

1

Чтобы нам рассчитать суммарный балл, основываясь на описанных выше критериях, необходимо применять такую формулу.

2

Давайте более подробно опишем аргументы:

1. C3:C14 – это наш диапазон суммирования. В нашем случае он совпадает с диапазоном условия. Из него будут отбираться баллы, используемые для расчета суммы, но лишь те, которые подпадают под наши критерии.
2. «>5» – наше первое условие.
3. B3:B14 – второй диапазон суммирования, который обрабатывается на предмет соответствия второму критерию. Видим, что здесь нет совпадения с диапазоном суммирования. Из этого делаем вывод, что диапазон суммирования и диапазон условия может быть как идентичным, так и нет. 
4. «A*» – второй диапазон, который задает отбор оценок лишь тех студентов, чья фамилия начинается на А. Звездочка в нашем случае означает любое количество знаков. 

После расчетов мы получаем следующую таблицу.

3

Как видим, формулой было произведено суммирование значений, основываясь на динамическом диапазон и опираясь на заданные пользователем условия.

Использование СУММЕСЛИМН и СУММЕСЛИ в Excel — что нужно запомнить?

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

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

1. Порядок аргументов

Аргументы применяются по-разному. В частности, диапазон_суммирования — это первый параметр в СУММЕСЛИ, но это третий параметр в СУММЕСЛИ.

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

Однако в SUMIFN это очень важно и обязательно, поэтому оно стоит на первом месте. Ребята из Microsoft, наверное, подумали, что после добавления десятого или сотого интервала / пары критериев кто-то может забыть указать интервал для добавления:)

2. Диапазон суммирования и область критериев должны быть одинакового размера

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

Выражение = СУММЕСЛИ (E2: E21; C2: C21; I2; F2: F22; I3) вернет #VALUE! Сообщение об ошибке, потому что второй параметр поиска (F2: F22) не того же размера, что и остаток (E2: E21) и (C2: C21).

Хорошо, хватит стратегии (т.е теории), перейдем к тактике (примерам).

Как работает функция СУММЕСЛИ в Эксель

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

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

Давайте опишем аргументы.

1. В качестве диапазона поиска используется колонка с должностями. Конечно, заголовок колонки не входит в него.
2. Используемое условие – продавец. Не стоит забывать заключать этот аргумент в кавычки.
3. В нашем случае в качестве диапазона суммирования используется заработная плата. Соответственно, значение этого аргумента – F2:F14.

Получившийся результат – 92900. Согласитесь, что это значительно удобнее, чем вручную анализировать список профессий, проверять их на соответствие определенному условию, а потом ещё и суммировать эти значения. Особенно, если таблица большая.

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

Пример использования функции СУММЕСЛИ для сопоставления данных

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

Теперь рассмотрим пример, как функция СУММЕСЛИ оказывается более подходящей для подтягивания данных, чем ВПР. Пусть данные из примера ваше – это продажи некоторых товаров за январь. Мы хотим узнать, как они изменились в феврале. Сравнение удобно произвести в этой же табличке, предварительно добавив еще один столбец справа и заполнив его данными за февраль. Где-то в другом экселевском файле есть статистика за февраль по всему ассортименту, но нам хочется проанализировать именно эти позиции, для чего требуется из большого файла со статистикой продаж всех товаров подтянуть нужные значения в нашу табличку. Для начала давайте попробуем воспользоваться формулой ВПР. В качестве критерия будем использовать код товара. Результат на рисунке.

Отчетливо видно, что одна позиция не подтянулась, и вместо числового значения выдается ошибка #Н/Д. Скорее всего, в феврале этот товар просто не продавался и поэтому он отсутствует в базе данных за февраль. Как следствие ошибка #Н/Д показывается и в сумме. Если позиций не много, то проблема не большая, достаточно вручную удалить ошибку и сумма будет корректно пересчитана. Однако количество строчек может измеряться сотнями, и рассчитывать на ручную корректировку не совсем верное решение. Теперь воспользуемся формулой СУММЕСЛИ вместо ВПР.

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

На сегодня все. Всех благ и до новых встреч на statanaliz.info.