Альтернатива SUMPRODUCT

Question

Я пытаюсь вычислить два столбца из другого листа Excel. Скажем, первый лист - это RAW DATA, первый столбец - Date, второй - Total, третий - Duration, а последний - мой счетчик (для проверки дубликатов). Прежде чем я смогу рассчитать общую продолжительность, которая должна быть помещена в TALLY SHEET, она должна соответствовать следующим критериям: Дата должна быть равна дате в TALLY SHEET, а столбец счетчика должен быть равен 1.

В TALLY SHEET есть следующие столбцы (по порядку): Дата и Общая продолжительность.

В моем столбце Общая продолжительность в TALLY SHEET я использую формулу

=SUMPRODUCT(SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1),
            SUMIFS('RAW DATA'!C:C,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1))

Однако эта формула только суммирует продолжительность и не выполняет SUMPRODUCT() . Если я сделаю это на листе RAW DATA с =SUMPRODUCT(B:B,C:C)/SUM(B:B) это сработает .

Answer 1

Это неправильный способ изменить формулу =SUMPRODUCT(B:B,C:C)/SUM(B:B) чтобы проверить наличие указанных условий.

Ваша формула не будет давать ожидаемый перекрестный продукт, так как вы суммируете внутри SUMPRODUCT() .

SUMPRODUCT(SUMIFS(«Total»),SUMIFS(«Duration»)/SUMIFS(«Total») совпадает с SUMIFS(«Total»)*SUMIFS(«Duration»)/SUMIFS(«Total») который имеет Конечно же, как SUMIFS(«Duration»)

Вам нужно использовать другой стиль формулы, который создает массивы внутри SUMPRODUCT() . Следующий стиль подходит при условии, что в столбцах B и C присутствуют только преобразованные в числа значения. (Пустая строка, возвращаемая формулой, не может быть преобразована в число. Также нет ошибок.)

Введите эту формулу в B2 и ctrl-enter/copy-paste/fill-down в оставшуюся часть столбца:

=SUMPRODUCT('RAW DATA'!$B$2:$B$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),
            'RAW DATA'!$C$2:$C$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)

Обратите внимание , что для этого стиля формулы, не рекомендуются использовать целые столбцы внутри SUMPRODUCT() так как он замедляет Казни формулы много.

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

Объяснение:

('RAW DATA'!$A$2:$A$10=A2) часть проверяет, совпадает ли дата и ('RAW DATA'!$D$2:$D$10=1) часть проверяет, равен ли счетчик 1 .

Когда эти массивы умножаются вместе, TRUE становится 1 а FALSE - 0 . Таким образом, результат равен 1 только если оба истинны.

Умножение на 'RAW DATA'!$B$2:$B$10 (или 'RAW DATA'!$C$2:$C$10) приводит к соответствующему общему (или длительному) значению, если и только если дата совпадает, а счетчик равен 1 .

Функция SUMPRODUCT() пересекает два массива, а затем суммирует их.

Конечно, приведенная выше формула может быть реорганизована / упрощена до следующего, поскольку проверяемые условия одинаковы как для общего, так и для продолжительности:

=SUMPRODUCT('RAW DATA'!$B$2:$B$10*'RAW DATA'!$C$2:$C$10*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)

РЕДАКТИРОВАТЬ:

Если значения в столбцах B и C генерируются формулами и формулы могут возвращать пустые строки, то в этом случае приведенная выше формула приведет к #VALUE! ошибка. Следующее исправит это, обработав пустые строки как нули:

=SUMPRODUCT(("0"&'RAW DATA'!$B$2:$B$10)*("0"&'RAW DATA'!$C$2:$C$10)*('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)

---

Альтернативная формула может быть построена с использованием SUM() и IF() .

Массив введите (Ctrl+Shift+Enter) следующую формулу в B2 и скопируйте-вставьте / заполните в оставшуюся часть столбца (не забудьте удалить { и }):

{=SUM(IF(('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),'RAW DATA'!$B$2:$B$10*'RAW DATA'!$C$2:$C$10,0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)}

Обратите внимание, что SUM можно заменить на SUMPRODUCT и формула будет работать точно так же.

РЕДАКТИРОВАТЬ:

Та же проблема с пустыми строками в столбцах B и C что и выше, относится и к этой формуле. Следующее исправляет это:

=SUM(IF(('RAW DATA'!$A$2:$A$10=A2)*('RAW DATA'!$D$2:$D$10=1),("0"&'RAW DATA'!$B$2:$B$10)*("0"&'RAW DATA'!$C$2:$C$10),0))
/SUMIFS('RAW DATA'!B:B,'RAW DATA'!A:A,A2,'RAW DATA'!D:D,1)