Применение СУММЕСЛИМН в Excel

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

Как работает формула СУММЕСЛИМН

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

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

• Диапазон суммирования — это область, внутри которой происходит суммирование значение, если они соответствуют заданным критериям. В ячейках могут содержаться только числа.
• Диапазон критерия — совокупность клеток в таблице, в которых будет выполняться проверка соответствия заданному условию.
• Критерий — собственно само условие, при соответствии которому значение из диапазона суммирования будет добавляться в итоговое значение.

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

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

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

Применение с числовыми значениями

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

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

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

   Вводим диапазон суммирования
3. Далее создаем первый из двух критериев. В выборку должна входить только прибыль от товаров, стоимость которых больше Поэтому выделяем диапазон условия — значения из ячейки C, а также сам критерий — больше 100.
   

   Оформляем первое условие
4. Теперь добавляем второй критерий — объем продаж больше 50 кг. Условие добавляется точно таким же способом, который мы использовали до этого. После этого нажимаем OK.
   

   Оформляем второе условие
5. Программа просуммирует значения из ячеек, которые соответствуют заданным условиям. Мы получим прибыль от товаров, стоимостью выше 100 рублей, и объемом продаж больше 50 кг.
   

   Результат выполнения

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

Применение с числовыми и текстовыми значениями

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

1. Выделяем ячейку для записи результата. Далее вызываем Мастер функций и находим «СУММЕСЛИМН».
   

   Вызываем функцию СУММЕСЛИМН
2. Вводим первый аргумент — диапазон суммирования. Как и в предыдущем примере, суммируются значения из столбца «Прибыль».
   

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

   Задаем первое условие
4. Теперь создаем второй критерий — проверку стоимости. Нас интересуют только товары, цена которых выше 100 рублей. Создаем соответствующее условие, предварительно указав диапазон проверки. Создав второй критерий, нажимаем OK.
   

   Вписываем второй критерий
5. В результате выполнения функции с двумя условиями мы получим прибыль от продажи овощей дороже 100 рублей. Используя маркер автоматического заполнения можно применить формулу к другим названиям категорий. Они будут автоматически вставлены в условие и программа выполнит соответствующий расчет.
   

   Результат выполнения

При оформлении формулы можно создавать критерии не только с числами и текстом, но и с другими типами данных, например, с датами. Максимальное количество условий, которое можно добавить составляет 127.

Заключение

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

Видео по теме