Как использовать промежуточный итого с суммпроизв в excel

Вы можете использовать следующую формулу для объединения функций ПРОМЕЖУТОЧНЫЙ ИТОГО и СУММПРОИЗВ в Excel:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Эта конкретная формула позволяет суммировать произведение значений диапазона C2:C11 и диапазона D2:D11 даже после того, как этот диапазон ячеек был каким-либо образом отфильтрован.

Следующий пример показывает, как использовать эту формулу на практике.

Пример: Как использовать ПРОМЕЖУТОЧНЫЙ ИТОГ с СУММПРОИЗВ в Excel

Предположим, у нас есть следующий набор данных, содержащий информацию о продажах различных продуктов в двух разных продуктовых магазинах:

Далее давайте отфильтруем данные, чтобы отображались только те строки, значение которых в столбце «Магазин» равно B.

Для этого выделите диапазон ячеек A1:D11 . Затем нажмите вкладку «Данные» на верхней ленте и нажмите кнопку «Фильтр» .

Затем щелкните стрелку раскрывающегося списка рядом с пунктом «Магазин » и убедитесь, что установлен только флажок рядом с пунктом B , затем нажмите «ОК» :

Данные будут автоматически отфильтрованы, чтобы отображать только строки, в которых столбец Store равен B :

Если мы попытаемся использовать функцию СУМПРОДИЗВ() для суммирования произведения значений столбцов «Продажи» и «Цена», она фактически вернет сумму произведения этих двух столбцов в исходном наборе данных:

Вместо этого нам нужно использовать следующую формулу:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

На следующем снимке экрана показано, как использовать эту формулу на практике:

Эта формула возвращает правильную сумму 77,5 .

Мы можем подтвердить, что это правильно, рассчитав вручную сумму произведения значений между столбцами «Продажи» и «Цена»:

Сумма произведения значений продаж на цену: (3*2) + (12*2,5) + (5*3,5) + (8*3) = 77,5 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Как удалить отфильтрованные строки в Excel
Как посчитать отфильтрованные строки в Excel
Как суммировать отфильтрованные строки в Excel