Bazaprogram.ru

Новости из мира ПК
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Sumifs excel на русском

Sumifs excel на русском

На своём сайте я много внимания уделял Excel функциям извлечения данных ( ИНДЕКС , ВПР , ПОИСКПОЗ , ГПР ). Они, безусловно, очень важны, но есть ещё один краеугольный класс формул, без которых просто никуда. Это, конечно же, формулы подсчёта и суммирования.

Эта статья будет посвящена формулам СУММЕСЛИМН (SUMIFS), СЧЁТЕСЛИМН (COUNTIFS) и СРЗНАЧЕСЛИМН (AVERAGEIFS). Если вы раньше использовали формулы СУММЕСЛИ (SUMIF), СЧЁТЕСЛИ (COUNTIF) или СРЗНАЧЕСЛИ (AVERAGEIF), то, ознакомившись со статьёй, можете благополучно забыть об их существовании, так как функционально *ЕСЛИМН формулы кроют *ЕСЛИ формулы, как бык овцу.

Идея формулы

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

Мы располагаем таблицей, в которой необходимо просуммировать значения из столбца Количество , но с учётом двух критериев или фильтров, если угодно. Первый критерий — столбец Магазин должен содержать значение Центр , второй критерий — столбец Товар должен содержать значение Компьютер . Оба критерия действуют одновременно или, как говорят, по » И » (а не по » ИЛИ «). То есть нас интересует, сколько компьютеров продал центральный магазин.

Для этого в ячейку G7 мы помещаем формулу СУММЕСЛИМН , в которую передаём 5 параметров:

D3:D15 — диапазон, содержащий числа, которые мы собираемся суммировать

B3:B15 — диапазон, содержащий значения для сравнения с критерием 1

G3 — ячейка, содержащая, собственно, критерий 1 — значение, которое нас интересует — » Центр «

C3:C15 — диапазон, содержащий значения для сравнения с критерием 2

G5 — ячейка, содержащая критерий 2 — » Компьютер «

Формула вернёт нам значение 14, так как только 2 строки таблицы удовлетворяют обоим нашим критериям, и обе они содержат число 7. А теперь давайте дадим более формальное описание данной функции.

Синтаксис

Вот синтаксис формулы суммирования:

= СУММЕСЛИМН ( sum_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

sum_range — первый параметр — всегда диапазон суммирования.

criteria_range_1 — диапазон для тестирования на соответствие критерию 1

criteria_1 — значение критерия 1. Значение может быть в виде:

  • ссылки на ячейку, например E3
  • выражения в виде текстовой строки, например » >10 «
  • значения в виде числа или текста, например 45 или » Москва «

criteria_range_n и criteria_n — таких критериев, которые описываются всегда двумя параметрами, может быть до 127 штук. Принцип их организации остаётся неизменным.

Некоторые важные замечания

Количество параметров будет всегда нечётным, так как есть диапазон суммирования, а критерии идут парами. Это будет выглядеть как 3, 5, 7, 9 и т.д. параметров. Что будет соответствовать 1, 2, 3, 4 и т.д. критериев отбора строк для суммирования.

Диапазоны суммирования и диапазоны критериев должны всегда иметь одинаковый размер и одинаковую ориентацию (вертикальную или горизонтальную)

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

СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН

Не откладывая в долгий ящик, сразу посмотрим на синтаксис формул СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН . У СРЗНАЧЕСЛИМН отличие только в том, что она не суммирует числа, а вычисляет по ним среднюю величину.

= СРЗНАЧЕСЛИМН ( avg_range; criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

А СЧЁТЕСЛИМН считает строки, а не числа, поэтому она не имеет аналога параметров sum_range или avg_range. Таким образом её параметры описывают только критерии и количество параметров всегда должно быть чётным, в отличие от её коллег.

= СЧЁТЕСЛИМН ( criteria_range_1; criteria_1 [; criteria_range_2; criteria_2 [ . ] ] )

Файл примера

Скачать

Критерии

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

СУММЕСЛИМН и умные таблицы

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

L4 и L5 содержат значения критериев.

Критерий для периода дат

А вот как задать период дат (с. по . ) при суммировании:

Обратите внимание на очень важную конструкцию:

Это не что иное, как выражение в виде текстовой строки. Причём это не статическое выражение, которое обычно приводят в справке по СУММЕСЛИМН , типа » >= 200000 «. Это выражение динамическое, то есть — гораздо более ценное и интересное. Всё что вам нужно — это вставить между знаком операции » >= » и ссылкой на ячейку с параметром — знак операции сложения строк » & «.

Динамическая операция сравнения

А почему бы не дать на откуп пользователю право определять операцию для критерия? В предыдущем примере пользователь мог выбирать порог N в критерии » >=N «. А в этом примере пользователь определяет и порог и саму операцию! А почему нет?

В L12 у нас находится выпадающий список, ссылающийся на 4 возможные операции: >, >=, ? » — заменяет любой символ, » * » — заменяет любое количество символов (в том числе и его отсутствие). Например критерий » *т* » сработает и на слово «Центр», и на слово «Восток». А, если бы существовала такая альтернатива, то сработал бы и на слово «опт» (это, как раз случай, когда вторая звёздочка в «*т*» заменила ноль символов справа от «т»).

Ответы на сложные вопросы

При помщи *ЕСЛИМН формул можно отвечать на довольно заковыристые вопросы. Например, какая доля холодильников в Центре была продана по ценам, превышающим средние цены холодильников по всем магазинам? А вот формула, которое это расчитывает:

Как видите мы использовали 2 формулы СУММЕСЛИМН и 1 СРЗНАЧЕСЛИМН . СРЗНАЧЕСЛИМН вернула нам средние цены по холодильникам. Первая СУММЕСЛИМН вернула количество проданных телевизоров с ценами выше средних, а вторая формула вернула общее количество холодильников, проданных в Центре. Найдя частное между результатами двух СУММЕСЛИМН , мы получили долю от единицы и просто выразили её в процентах.

Набор магазинов

А что делать, если нам нужны продажи двух магазинов? В рамках одной формулы это решить в общем случае нельзя (метод через символы подстановки не универсален, так как магазины могут иметь полностью уникальные имена). Ответ очевиден — надо использовать 2 формулы и просто сложить их результаты. Хотя понятно, что такой метод подойдёт далеко не во всех ситуациях.

Читать еще:  Учебник powerpivot для excel

Но есть и ограничения.

Надеюсь вы убедились, что *ЕСЛИМН достаточно гибки, но есть один узкий момент, который надо хорошо понимать. Например, я хочу знать, сколько раз я торговал в Центре, предоставляя покупателям скидку более или равную 5%? Как я могу это узнать? Только вычисляя по каждой строке отношение предоставленной скидки к базовой цене. Понимаете — по каждой строке надо делать вычисление и сравнивать с 5%! Вот такое формулы *ЕСЛИМН сами, без вашей помощи сделать не смогут, так как формула один раз вычисляет критерий, а потом сравнивает его со всеми строками критериального диапазона, а нам надо это делать динамически. Но никто нам не мешает организовать дополнительный столбец, который будет предварительно считать отношение скидки к базовой цене, а после этого можно уже остальную работу поручить формуле СЧЁТЕСЛИМН (см. пример 7).

Пустые ячейки

Если вы хотите, чтобы формула *ЕСЛИМН отреагировала на пустые ячейки, то следует использовать критерии «» (пустая строка) или «=». Например, посмотрите лист Blank нашего учебного файла:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «=» )

однако, если пустота ячейки является следствием работы формулы (например, формулы ЕСЛИ ), то такая ячейка отреагирует только на критерий «», то есть:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «» )

Не пустые ячейки

А вот, если критерием является то, что ячейка хоть что-то содержит, то можно использовать такую форму:

= СУММЕСЛИМН ( E3:E10 ; B3:B10 ; «<>» )

Ну что ж, я надеюсь, что вы почувствовали всю силу, сосредоточенную в формулах этого семейства. Удачи!

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

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)

Это видео — часть учебного курса Сложение чисел в Excel.

При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».

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

Синтаксис

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

Аргументы функции СУММЕСЛИ описаны ниже.

Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).

Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Например, условие может быть представлено в таком виде: 32, «>32», B5, «32», «яблоки» или СЕГОДНЯ().

Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки ( «). Если условием является число, использовать кавычки не требуется.

Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).

В аргументе условие можно использовать подстановочные знаки: вопросительный знак ( ?) и звездочку ( *). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (

Примечания

Функция СУММЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов или применяется к строке #ЗНАЧ!.

Аргумент диапазон_суммирования может не совпадать по размерам с аргументом диапазон. При определении фактических ячеек, подлежащих суммированию, в качестве начальной используется верхняя левая ячейка аргумента диапазон_суммирования, а затем суммируются ячейки части диапазона, соответствующей по размерам аргументу диапазон. Пример:

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

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

Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel, с 2016 по 2003 год. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.

Как пользоваться СУММЕСЛИ в Excel – синтаксис

Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.

Синтаксис функции выглядит следующим образом:

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

Диапазон – это область, которую мы исследуем на соответствие определённому значению.

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

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

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

Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.

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

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

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

Сумма если больше чем, меньше, или равно

Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.

D2:D21 – это координаты, в которых мы ищем значение.

I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.

Читать еще:  Функция search в excel

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

Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, , = и числа.

Можно указать его прямо в формуле, как это сделано в I13

=СУММЕСЛИ(D2:D21;»

В I3 запишем выражение:

F2:F21 – это область, в которой мы отбираем подходящие значения.

I2 – здесь записано, что именно отбираем.

E2:E21 – складываем числа, соответствующие найденным совпадениям.

Конечно, можно указать параметр отбора прямо в выражении:

Но мы уже договорились, что так делать не совсем рационально.

Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.

Подстановочные знаки для частичного совпадения.

При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.

Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.

Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.

Символ “?” позволяет заменить собой один любой символ.

Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).

Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –

=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*

=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)

где * вставлены прямо в выражение и «склеены» с нужным текстом.

  • “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
  • “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
  • “. ” — найдем слово из любых 3 букв

Этому требованию соответствует “Нет”.

  • “. *” — текст из любых 7 и более букв.

Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.

  • “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
  • “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.

Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (

), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:

Точная дата либо диапазон дат.

Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.

Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!

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

Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.

Рассчитаем за вчерашний день.

=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)

СЕГОДНЯ()-1 как раз и будет «вчера».

Складываем за даты, которые предшествовали 1 февраля.

А если нас интересует временной интервал «от-до»?

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

=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»

Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).

Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.

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

Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.

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

=СУММЕСЛИ(F2:F21;«»;E2:E21)

Сумма по нескольким условиям.

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

Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и найдем, сколько всего сделано заказов черного и молочного шоколада.

1. СУММЕСЛИ + СУММЕСЛИ

Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).

Это самое простое решение, но не самое универсальное и далеко не единственное.

2. СУММ и СУММЕСЛИ с аргументами массива.

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

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

Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:

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

А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.

=СУММ(СУММЕСЛИ($C$2:$C$21; ;$E$2:$E$21))

Важно, что результаты вычислений в первом и втором случае совпадают.

3. СУММПРОИЗВ и СУММЕСЛИ.

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

Вот как это будет выглядеть:

в H3 и H4 мы запишем критерии отбора.

Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:

=СУММПРОИЗВ(СУММЕСЛИ(C2:C21; ;E2:E21))

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

Читать еще:  Как включить vba в excel 2020

Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.

Почему СУММЕСЛИ у меня не работает?

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

1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.

Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив <1,2,3>, Excel выдаст сообщение об ошибке.

Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)

Неверно : =СУММЕСЛИ(<1,2,3>, «цвет», C1:C3)

2. Ошибка при суммировании значений из других листов или рабочих книг

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

Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:

Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.

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

Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel 2010 и Excel 2016 сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.

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

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

По названию команды можно понять, что она не просто считает сумму, но еще и подчиняется каким-либо логическим условиям.

СУММЕСЛИ и ее синтаксис

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

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

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

Как работает функция СУММЕСЛИ в Excel?

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

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

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

  1. Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
  2. Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
  3. Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

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

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

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

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

Синтаксис с использованием функции по нескольким критериям

Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.

  1. Диапазон суммирования. Если в СУММЕСЛИ он был в конце, то здесь он стоит на первом месте. Он также означает ячейки, которые необходимо просуммировать.
  2. Диапазон условия 1 – ячейки, которые нужно оценить на основании первого критерия.
  3. Условие 1 – определяет ячейки, которые функция выделит из первого диапазона условия.
  4. Диапазон условия 2 – ячейки, которые следует оценить на основании второго критерия.
  5. Условие 2 – определяет ячейки, которые функция выделит из второго диапазона условия.

И так далее. В зависимости от количества критериев, число аргументов может увеличиваться в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9.

Пример использования

Предположим, нам нужно подсчитать сумму заработных плат за январь всех продавцов-женщин. У нас есть два условия. Сотрудник должен быть:

Значит, будем применять команду СУММЕСЛИМН.

  • диапазон суммирования – ячейки с зарплатой;
  • диапазон условия 1 – ячейки с указанием должности сотрудника;
  • условия 1 – продавец;
  • диапазон условия 2 – ячейки с указанием пола сотрудника;
  • условие 2 – женский (ж).

Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.

СУММЕСЛИ в Excel с динамическим условием

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

У нас появилась дополнительная строчка. Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки.

Копируем данные сотрудника и вставляем их в общий перечень. Суммы в итоговых ячейках изменились. Функции среагировали на появление в диапазоне еще одного продавца-женщины.

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

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