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

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

Эта конкретная формула позволяет вычислить среднее значение в диапазоне C2:C11, где соответствующее значение в диапазоне B2:B11 равно «Сохранить» даже после того, как этот диапазон ячеек был тем или иным образом отфильтрован. Еще один.

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

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

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

Далее давайте отфильтруем данные, чтобы отображались только те строки, в которых находятся игроки Западной конференции.

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

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

Данные будут автоматически отфильтрованы, чтобы отображать только строки, в которых столбец «Конференция» равен West :

Если мы попытаемся использовать функцию СРЗНАЧЕСЛИ() для расчета среднего значения столбца «Точки», где значение столбца «Позиция» равно «Охрана», она фактически вернет среднее значение точек для строк, равных «Охрана». в исходном наборе данных:

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

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

Эта формула возвращает правильное среднее значение 21,25 .

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

Среднее количество очков защиты: (12 + 28 + 30 + 15) / 4 = 21,25 .

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

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

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