gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 72 гостей онлайн
Диапазон таблицы Excel. «СУММПРОИЗВ» в Excel.

«СУММПРОИЗВ» в Excel.

      Рассмотрим примеры функции «СУММПРОИЗВ» в Excel, которая сначала умножает данные столбцов, затем складывает. Этой функцией можно сделать выборочное суммирование в Excel по условию. Все это мы здесь разберем.
      Сначала разберем, как действует функция «СУММПРОИЗВ» в Excel. У нас такая таблица.
Нам нужно перемножить данные столбца А с данными столбца В (1*2, 3*4, 5*6) и, затем, все результаты сложить. В ячейку С 21 устанавливаем функцию.
      На закладке «Формулы» в разделе «Библиотека функций» нажимаем на кнопку «Математические» и выбираем функцию «СУММПРОИЗВ».
      Диалоговое окно заполнили так.Нажимаем «ОК». Получилось так.
В строке формул смотрим на формулу, которая получилась.
Так работает эта функция. Но, можно применить эту функцию, чтобы она складывала выборочные данные.
      Не числовые данные (текст, напирмер) функция обозначает нулем, если не соответствуют условиям, и единицей, если отвечают условиям в формуле. И ячейки с единицами и числами в ячейках напротив них, умножает и, затем, складывает.
      Примеры практического применения функции «СУММПРОИЗВ» в Excel.
      Первый способ.
Таблица такая.
Нам нужно сложить суммы, проданных яблок менеджером Васечкиным. Здесь два условия – «сложить яблоки» и «проданные Васечкиным». В ячейку Е9 устанавливаем функцию.
      На закладке «Формулы» в разделе «Библиотека функций» нажимаем на кнопку «Математические» и выбираем функцию «СУММПРОИЗВ».
      Диалоговое окно заполнили так.Нажимаем «ОК». В ячейке С9 получилась такая формула. =СУММПРОИЗВ(B2:B8=C12;C2:C8=C11;E2:E8)
      Разберем эту формулу и немного подправим.
B2:B8=C12 – это означает, что в диапазоне В2:В8 нужно искать данные, которые написаны в ячейке С12, т.е. в диапазоне «менеджер» искать слово «Васечкин». Подправляем формулу – эту часть формулы заключаем в круглые скобки и ставим знак «*» (умножить).
C2:C8=C11 – это означает, что в диапазоне С2:С8 нужно искать то, что написано в ячейке С11. Эту часть формулы тоже заключаем в круглые скобки.
Получится такая формула.  =СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11);E2:E8)
После правки формулы, нажимаем «Enter».
Посчитались суммы яблок из ячеек С2 и С5. А яблоки зеленые и красные яблоки не посчитались. Это недостаток этой функции, п. ч. нельзя применить символы, которые позволили бы посчитать все яблоки.
      Но, есть функция, в которую можно вставить символы и посчитать все яблоки. Смотрите статью «Суммирование ячеек в Excel по условию».
      Второй способ.
      Функция «СУММПРОИЗВ» в Excel с датами.
Если нужно сложить данные из таблицы по датам, то вставляем в формулу еще один диапазон - диапазон дат и пишем ячейку, в которой написана эта дата. Формула в ячейке Е10 будет такой.
=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8=C13);E2:E8)
      Третий способ.
      Если нужно посчитать данные в определенном периоде, то пишем диапазон и ячейку начала периода и окончания периода. В ячейке Е11 будет такая формула.
=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8>=C13)*(D2:D8<=C14);E2:E8)
Получится так.

СУММПРОИЗВ в Excel.      Обратите внимание
В формуле перед датой начала периода ставим не просто «равно», а «больше» и «равно». Перед датой окончания периода ставим «меньше» и «равно».
      В Excel можно вставить ссылки не только на конкретный диапазон, но и на динамический диапазон. Настроить таблицу так, что она будет считать последние свежие данные, которые добавляются в таблицу. Настроить график так, что в нем будут автоматически добавляться и отражаться последние данные. Как для этого настроить таблицу, смотрите в статье "Функция "СМЕЩ" в Excel".