13. Сумма по условию
Мы изучили функции СУММ и ЕСЛИ, теперь перейдем к их гибриду – функции
СУММЕСЛИ. Она возвращает сумму только тех значений, которые
удовлетворяют определённому условию.
=СУММЕСЛИ(диапазон_условия; условие; диапазон_суммирования)
Когда в функции ЕСЛИ мы проверяли, чтобы число в ячейке B2 было больше 210, у нас было две вещи:
B2 – ячейка, к которой применяется условие
«больше, чем 210» – само условие
В функцию ЕСЛИ записывали единое условие B2>210
В функции СУММЕСЛИ обычно проверяется не 1 ячейка, а диапазон (иначе смысл суммировать одно число:). По аналогии, если мы хотим проверить, что диапазон B2:B4 больше 210, у нас те же вещи:
B2:B4 – диапазон, к которому применяется условие – другими словами диапазон_условия
«больше, чем 210» – условие
В функции СУММЕСЛИ эти вещи должны записываться уже как отдельные аргументы.
Обратите внимание!
• В условиях с использованием логических операторов (>210, =100, <12 и т.д.), это условие записывается в кавычках (мне немало нервов потрепала эта вещь)
• Диапазон условия должен содержать столько же ячеек, как и диапазон суммирования, иначе формула не будет работать (A1:A3 и B1:B3 – работают, A1:A3 и B1:B4 – нет)
=СУММЕСЛИ(диапазон_условия; условие; диапазон_суммирования)
Когда в функции ЕСЛИ мы проверяли, чтобы число в ячейке B2 было больше 210, у нас было две вещи:
B2 – ячейка, к которой применяется условие
«больше, чем 210» – само условие
В функцию ЕСЛИ записывали единое условие B2>210
В функции СУММЕСЛИ обычно проверяется не 1 ячейка, а диапазон (иначе смысл суммировать одно число:). По аналогии, если мы хотим проверить, что диапазон B2:B4 больше 210, у нас те же вещи:
B2:B4 – диапазон, к которому применяется условие – другими словами диапазон_условия
«больше, чем 210» – условие
В функции СУММЕСЛИ эти вещи должны записываться уже как отдельные аргументы.
Обратите внимание!
• В условиях с использованием логических операторов (>210, =100, <12 и т.д.), это условие записывается в кавычках (мне немало нервов потрепала эта вещь)
• Диапазон условия должен содержать столько же ячеек, как и диапазон суммирования, иначе формула не будет работать (A1:A3 и B1:B3 – работают, A1:A3 и B1:B4 – нет)
Задание 1: Приступим
Для начала просуммируем только те значения, которые больше
определённого числа. Возьмём 100
1. В ячейке B10 введите формулу для нахождения суммы чисел выше
100. Диапазоном условия будет B2:B9, условием –
«>100», а диапазон суммирования будет таким же, как и
диапазон условия. Получим =SUMIF(B2:B9,">100",B2:B9)
2. В ячейке E10 по аналогии найдите сумму чисел меньше
90
3. И в H10 сумму чисел, которые больше или равны 130
Выполните шаги... (0/3)
Задание 2: А теперь к интересному
На самом деле вариант формулы, который мы рассмотрели в предыдущем
задании, используется редко. Чаще всего условием становится
значение из соседнего столбца.
Так, при вводе функции =СУММЕСЛИ(B2:B4;”Яблоки”,С2:С4), эксель будет находить сумму тех ячеек из диапазона С2:С4, которые находятся в той же строке, что и ячейки с текстом «Яблоки» из диапазона B2:B4. Например, если B2 – «Яблоки», B3 – «Груши», а B4 – «Яблоки», то C2 и С4 пойдут в сумму, а С3 – нет.
Так, при вводе функции =СУММЕСЛИ(B2:B4;”Яблоки”,С2:С4), эксель будет находить сумму тех ячеек из диапазона С2:С4, которые находятся в той же строке, что и ячейки с текстом «Яблоки» из диапазона B2:B4. Например, если B2 – «Яблоки», B3 – «Груши», а B4 – «Яблоки», то C2 и С4 пойдут в сумму, а С3 – нет.
1. В ячейке B9 необходимо найти сумму только яблок.
Диапазоном условия здесь будет ячейки с товарами, условием –
«Яблоки», а диапазоном суммирования – ячейки с суммами
2. В прошлом уроке разобрали, что не обязательно прописывать
условие целиком. Попробуйте в ячейке E9 найти сумму яблок,
используя в качестве условия ссылку на ячейку D2
3. В ячейках K2:K4 найдите сумму для каждого фрукта,
используя в условии ссылки на ячейки в столбце J. Не забудьте
закрепить ссылки в формулах!
Выполните шаги... (0/3)
Задание 3: СУММЕСЛИМН – SUMIFS
Функция аналогична предыдущей, но вместо одного условия можно
делать несколько. Всего может быть от 1 до 127 условий.
=СУММЕСЛИ(диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2;…)
=СУММЕСЛИ(диапазон_суммирования; диапазон_условия1; условие1; диапазон_условия2; условие2;…)
1. Попробуем. В ячейке B9 найдите сумму яблок с помощью
функции SUMIFS. В отличие от предыдущей функции аргументы здесь на
других местах, поэтому сначала задается диапазон суммирования – B2:B8, затем диапазон условия A2:A8, а затем условие – например, A7.
Получаем =SUMIFS(B2:B8,A2:A8,A7)
2. Теперь 2 условия. В ячейке F9 нужно найти сумму только
больших яблок. Начало формулы останется таким же, но
добавятся 2 аргумента
– диапазон с размерами и условие «Большие»
3. Теперь в ячейках J2:J7 найдите суммы для каждого типа фруктов
из таблицы D2:F8. Условия даны в столбцах H и I. При копировании
вниз не забывайте о закреплении ячеек в формуле
Выполните шаги... (0/3)
