Sumproduct не работает, когда диапазон только 1 строка и часть расчета пуста

Question

У меня есть следующая формула, которая работает, когда диапазоны имеют более 1 строки, но генерирует ошибку #VALUE, если есть только 1 строка и один из факторов является пустым.

=SUMPRODUCT(cost, units)

Cost    Units
   1         1
   2         1

Результат 3.

Cost    Units
   1         
   2         

Результат 0.

Cost    Units
   1         1

Результат 1.

Cost    Units
   1         

Результат #VALUE .

Я могу предотвратить ошибку, обернув формулу в IFERROR:

=IFERROR((SUMPRODUCT(cost, units)), 0)

Почему SUMPRODUCT не работает только с одной строкой? Есть ли лучший способ справиться с этим, чем обертка IFERROR?

Answer 1

Пытаться

=SUMPRODUCT(costs*units)

Интересно видеть в инструменте оценки формул, что функция проходит этот этап:

а затем представляет # Значение! ошибка. Но если ячейка содержит фактическую формулу

=SUMPRODUCT(1,0)

тогда результат равен 0. Это похоже на ошибку.

Другой возможный синтаксис, который не представляет ошибку

=SUMPRODUCT(--(costs),--(units))

Редактировать: После некоторого дополнительного исследования и вклада от других MVP Excel, вот объяснение поведения, описанного выше: в Sumproduct Excel преобразует пустые ячейки в нулевые значения, НО только если это массив, то есть диапазон, состоящий из более чем одной ячейки , Отдельный диапазон ячеек не вызывает приведения, и, следовательно, пустая ячейка не приводится к 0.

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

Это сбивает с толку и немного раздражает, но если вход представляет собой одну пустую ячейку, которая не связана с операторами или двойным унарным, то единственная пустая ячейка берется по номиналу и вызовет значение #Value! ошибка, так как она не числовая.

Answer 2

Со страницы документации MS

замечания

• Аргументы массива должны иметь одинаковые размеры. Если они этого не делают, SUMPRODUCT возвращает # ЗНАЧЕНИЕ! значение ошибки.
• SUMPRODUCT рассматривает записи массива, которые не являются числовыми, как если бы они были нулями.