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

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

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

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

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

Пример 1. В таблице содержатся данные о успеваемости студентов по некому предмету в институте. Найти итоговую оценку для студентов с фамилией, начинающейся на буковку «А» при условии, что малый балл должен быть не наименее 5 (успеваемость студентов оценивается по 10-бальной шкале).

Вид таблицы данных:

Пример 1.

Для расчета суммарного балла согласно установленным аспектам используем последующую формулу:

СУММЕСЛИМН.

  • C3:C14 – спектр ячеек с баллами, из которых будут автоматом выбраны значения для расчета суммы, которые соответствуют установленным аспектам;
  • C3:C14 – 1-ый спектр ячеек, к которому будет использован 1-ый аспект;
  • «>5» – 1-ое условие отбора значений из обозначенного выше спектра;
  • B3:B14 – 2-ой спектр ячеек (с фамилиями студентов), к которому будет использован 2-ой аспект;
  • «А*» – 2-ое условие отбора значений (все фамилии, которые начинаются с буковкы «А»).

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

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

Выборочное суммирование по условию в Excel

Пример 2. В таблице указаны данные о отгрузках 3-х продуктов в разные страны в протяжении 3-х месяцев. Найти суммарный доход от отгрузок продукта №2 в Казахстан за июнь и август.

Вид таблицы данных:

Пример 2.

Для расчета итоговой суммы используем формулу:

Выборочное суммирование по условию.

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

СУММЕСЛИМН для суммирования значений по нескольким условиям в Excel

Пример 3. Используя таблицу из второго примера найти суммарной прибыли от поставок продукта №1 в Китай и Грузию в протяжении всего периода (3-х месяцев).

Для нахождения искомого значения используем формулу массива (для ввода нажимаем CTRL+SHIFT+Enter):

Функция СУММЕСЛИМН возвращает массив значений для критериев «Китай» и «Грузия» соответственно, которые суммируются функцией СУММ.

Примечание: если аспекты переданы в качестве константы массива для 2-ух и наиболее пар диапазон_условияN; условиеN, итог выполнения формулы будет неправильным.

СУММЕСЛИМН для суммирования значений по нескольким условиям.

Индивидуальности использования функции СУММЕСЛИМН в Excel

Функция имеет последующую синтаксическую запись:

=СУММЕСЛИМН( диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

  • диапазон_суммирования – неотклонимый аргумент, принимающий ссылку на ячейки с числами, для которых будет вычислено суммарное значение с учетом установленных критериев;
  • диапазон_условия1 – неотклонимый аргумент, принимающий ссылку на ячейки, в каких содержатся данные для проверки на соответствие данному аспекту;
  • условие1 – неотклонимый аргумент, принимающий ссылку на ячейку с условием поиска, текстовую запись логического выражения (к примеру, «>=100» либо «<>0»), проверяемый текст (к примеру, «Продавец_1», «Товар_1») либо числовое значение (К примеру, 20), определяющие ячейки в спектре условия1, для которых будут отобраны и просуммированы данные из спектра суммирования.
Интересно почитать:  Функция смещение в excel

Следующие аргументы являются необязательными для наполнения, а их смысл соответствует аргументам диапазон_условия1; условие1. Всего быть может задано до 127 критерий отбора данных для суммирования.

Функция СУММЕСЛИМН() Сложение с несколькими аспектами в EXCEL (Часть 2.Условие И)

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

В качестве начальной таблицы возьмем таблицу с 2-мя столбцами (полями): текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

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

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

1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;»> Function_F»>

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

  • B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы либо ссылки, содержащие числа. Пустые значения и текст игнорируются.
  • A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в каких проверяется соответственное условие.
  • D2 и «> » height=»498″ src=»https://excel2.ru/sites/default/files/pic_node/Sum/sum-32.png» width=»544″/>

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

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

  • Результатом вычисления A2_A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует совпадению значения из столбца А аспекту, т.е. слову персики . Массив можно узреть, выделив в Строке формул A2_A13=D2 , а потом нажав F9 ;
  • Результатом вычисления B2:B13 является массив<3:5:11:98:4:8:56:2:4:6:10:11>, т.е. просто значения из столбца B ;
  • Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является <0:0:0:0:4:8:56:0:0:0:0:0>. При умножении числа на значение ЛОЖЬ выходит 0; а на значение ИСТИНА (=1) выходит само число;
  • Разберем 2-ое условие: Результатом вычисления —( B2:B13>=E2) является массив <0:0:1:1:0:1:1:0:0:1:1:1>. Значения в столбце « Количество ящиков на складе », которые удовлетворяют аспекту >=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)

Задача2 (2 числовых аспекта)

Иной задачей быть может нахождение сумм ящиков лишь тех партий продуктов, у каких количество ящиков попадает в определенный интервал, к примеру от 5 до 20 (см. файл примера Лист 2Числа ).

Формулы строятся аналогично задачке 1: =СУММЕСЛИМН(B2:B13;B2:B13;»>=»&D2;B2:B13;» Примечание : для удобства, строчки, участвующие в суммировании, выделены Условным форматированием с правилом =И($B2>=$D$2;$B2

Задача3 (2 аспекта Дата)

Иной задачей быть может нахождение суммарных продаж за период (см. файл примера Лист «2 Даты» ). Используем другую начальную таблицу со столбцами Дата реализации и Размер продаж .

Формулы строятся аналогично задачке 2: = СУММЕСЛИМН(B6:B17;A6:A17;»>=»&D6;A6:A17;» Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время

По мере необходимости даты могут быть введены конкретно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;»>=15.01.2010″;A6:A17;»

Чтоб вывести условия отбора в текстовой строке используейте формулу =»Размер продаж за период с «&ТЕКСТ(D6;»дд.ММ.гг»)&» по «&ТЕКСТ(E6;»дд.ММ.гг»)

В крайней формуле применен Пользовательский формат .

Задача4 (Месяц)

Незначительно модифицируем условие предшествующей задачки: найдем суммарные продаж в месяц(см. файл примера Лист Месяц ).

Формулы строятся аналогично задачке 3, но юзер вводит не 2 даты, а заглавие месяца (предполагается, что в таблице данные в рамках 1 года).

Месяц вводится при помощи Выпадающего перечня , список месяцев формируется с внедрением Динамического спектра (для исключения излишних месяцев).

Другой вариант

Другим вариантом для всех 4-х задач является применение Автофильтра .

Для решения 3-й задачки таблица с настроенным автофильтром смотрится так (см. файл примера Лист 2 Даты ).

За ранее таблицу необходимо конвертировать в формат таблиц MS EXCEL 2007 и включить строчку Итогов.

Примеры функции СУММЕСЛИ и СУММЕСЛИМН в Excel

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

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

Примеры функции СУММЕСЛИ и СУММЕСЛИМН в Excel

СУММЕСЛИ

Аргументы

Разглядим аргументы для СУММЕСЛИ.

=СУММЕСЛИ(спектр; аспект; [диапазон суммирования])

Спектр – тут нужно указать, тот столбец, данные в каком будут сравниваться с данным Аспектом. В примере, это D2:D26.

Аспект – тут необходимо избрать одно значение из ранее обозначенного Спектра. Поначалу будем указывать мальчишек, потом девченок.

Спектр суммирования – это столбец, значения в каком необходимо просуммировать, если они относятся к избранному Аспекту. К примеру, если для избранного Спектра D2:D26 указан Аспект «мальчишка», означает в столбце «Средний бал»: С2:С26, будут суммироваться все значения, которые относятся к мальчишкам.

Пример

Выделяем ячейку G4 и ставим «=». Пишем в ней СУММЕСЛИ и открываем скобку «(». В качестве Спектра, из которого будут выбираться значения, выделяем D2:D26. В качестве Аспекта, необходимо избрать одно из значений в ранее обозначенном Спектре. У нас это «мальчишка», потому выделяем ячейку D3. Спектр суммирования – это столбец, в каком указан «Средний бал». Выделяем С2:С26. Меж аргументами ставьте «;», в конце, закройте скобку «)». Нажмите «Enter».

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

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

Для этого выделяем G4 и в «Строке формул» опосля D2, D26, C2, C26 жмем кнопку «F4». В адресах на ячейки, покажутся знаки «$». Таковым образом, мы делаем ссылки абсолютными. Чтоб растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при всем этом вид на темный плюсик.

Если Для вас любопытно, как создать ссылки в Эксель: абсолютные, относительные, на иной лист либо книжку, прочтите статью по данной для нас теме.

Далее смотрим на «Строчку формул». В качестве аргументов для Спектра и Спектра суммирования берутся правильные значения: D2:D26, C2:C26. В качестве Аспекта укажите адресок подходящей ячейки. Так как, необходимо посчитать средний балл для всех девченок, в столбце D избираем адресок хоть какой ячейки, в какой обозначено «девченка»: D4.

СУММЕСЛИМН

Аргументы

Сейчас разглядим аргументы для функции СУММЕСЛИМН в Excel.

=СУММЕСЛИМН(спектр суммирования; спектр условия1; условие1; спектр условия2; условие2)

Спектр суммирования – это столбец, значения из которого будут выбираться, если они соответствуют одному и второму данному условию. В примере, это С2:С26.

Спектр условия1 – это 1-ый спектр для сопоставления. Необходимо избрать пол ученика, потому напишем D2:D26.

Условие1 – необходимо указать то значение, которое будет выбираться из Спектра условий1. Нас заинтересовывают мальчишки, потому укажем D3.

Спектр условия2 – 2-ой спектр сопоставления. Тут будем выбирать класс, в каком обучается ребенок. Соответственно укажем В2:В26.

Условие2 – конкретное значение, которое нас интересует из Спектра условий2. Поначалу необходимо будет выделить ячейку, в какой указан «8 класс», потом «9 класс».

Пример

Решать будем вторую задачку. Выделяем ячейку Н8, ставим «=», пишем СУММЕСЛИМН и открываем скобку «(». В качестве Спектра суммирования указываем столбец со «Средним балом»: С2:С26. Спектр условия1 – это пол ученика: D2:D26, Условие1 – это мальчишки: ячейка D3. Спектр условия2 – это класс: В2:В26, Условие2 – 8 класс: ячейка В3. Закройте скобку, нажмите «Enter».

В «Строке формул» сходу сделайте абсолютные ссылки на столбцы: Класс, Средний бал, Мал/Дев.

Растягиваем формулу на Н8:Н11. Сейчас укажем правильные ячейки в качестве Условия1 и Условия2.

В Н9 считается средний балл для мальчишек, которые обучаются в 9 классе. Потому, Условие1 для первого спектра «мальчишка»: D3, Условие2 для второго спектра «9»: В6.

В Н10 считаем средний балл девченок, которые обучаются в 8 классе. Условие1 – «девченка», D4, Условие2 – «8», В3.

В Н11 считаем средний балл девченок, которые обучаются в 9 классе. Условие1 – «девченка», D4, Условие2 – «9», В2.

Таковым образом, мы посчитали сумму среднего балла для мальчишек и девченок, которые обучаются в 8 и 9 классах по отдельности.

Давайте сейчас проверим, чтоб итог был правильным. Посчитаем сумму «Среднего бала» для всех значений в начальной таблице, для Задачки 1 и для Задачки 2. Сумма обязана быть схожей.

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

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

Ссылка на основную публикацию
Adblock
detector