SUMPRODUCT vs SUMIFS

Я пытаюсь сделать руководство для компании в Excel (мы небольшая некоммерческая и очень нуждаемся в каком-то базовом руководстве). Тем не менее, я застрял, пытаясь объяснить различия между SUMPRODUCT и SUMIFS.

Я понимаю, что SUMPRODUCT использовался до Excel 2007 как способ иметь несколько критериев sumif (среди прочего). Теперь, когда SUMIFS доступен, есть ли разница в возможностях обеих формул?

Я немного оглянулся и обнаружил, что SUMIFS имеет тенденцию быть быстрее, но это было... Любые идеи и/или материалы для чтения оценены!

Спасибо

Ответ 1

SUMPRODUCT можно использовать более гибко, чем SUMIFS, поскольку вы можете изменять диапазоны с помощью других функций в SUMPRODUCT, например. если у вас есть диапазон дат в A2:A100, как вы можете суммировать соответствующие суммы в B2:B100 для December дат (в любой год)?

Вы можете использовать эту формулу, чтобы получить ответ

=SUMPRODUCT((MONTH(A2:A100)=12)+0,B2:B100)

Вы не можете сделать это легко с помощью SUMIFS, хотя, если даты всего за один год, вы можете просто использовать начальную и конечную точки диапазона в качестве критерия в SUMIFS, например. только за декабрь 2014 года:

=SUMIFS(B2:B100,A2:A100,">="&DATE(2014,12,1),A2:A100,"<"&DATE(2015,1,1))

SUMPRODUCT также может использоваться для ссылки на данные в закрытых книгах, SUMIFS не может этого сделать - см. здесь

http://support.microsoft.com/kb/260415

... но в целом SUMIFS значительно быстрее, я видел цифру 5x, но я не подтвердил это.

Для нескольких интересных применений SUMPRODUCT см. эту статью в MS Excel MVP Bob Philips

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Ответ 2

SUMPRODUCT работает как формулы массива. Это дает вам большую гибкость, чем SUMIF. Здесь пример, где вы можете иметь OR (+ в summproduct)

=SUMPRODUCT((A1:A10="Marketing")*((B1:B10="North")+(B1:B10="South"))*(C1:C10))

Это суммирует все, что имеет маркетинг в и Север или Юг в B.

Здесь пример, который использует функцию в диапазоне

=SUMPRODUCT((YEAR(A3:A7)=2014)*(B3:B7=1)*(C3:C7))

Это суммирует все, где год даты в столбце A равен 2014, а col B - 1.

Ответ 3

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

например. для входных данных

10    1
20    2
30    3

=SUMPRODUCT(A1:A3, B1:B3) = > 10 * 1 + 20 * 2 + 30 * 3 = 140

Ответ 4

SUMIFS обычно работает в пределах 30% аналогичной нагрузки вычисления формулы SUMPRODUCT.
SUMIFS может использовать полные ссылки на столбцы (например, A: A вместо A2: A999) без ущерба для вычисления задержки, как это делает SUMPRODUCT, только на самом деле вычисляя использованный диапазон.
SUMIFS счастливо пропускает текстовые значения в столбце, которые создавали бы ошибку с SUMPRODUCT в математической операции.
Пока SUMPRODUCT предоставляет некоторые функции, которые SUMIFS пропускает (упомянутая выше функциональность OR является одной из наиболее часто требуемых), новый SUMIFS на сегодняшний день является предпочтительным методом, когда он может быть использован.