Вы можете использовать следующую формулу для объединения функций ПРОМЕЖУТОЧНЫЙ ИТОГ и СРЗНАЧЕСЛИ в 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.Затем щелкните вкладку « Данные » на верхней ленте и нажмите кнопку « Фильтр ».
Затем щелкните стрелку раскрывающегося списка рядом с Конференция и убедитесь, что установлен только флажок рядом с Западом , затем нажмите OK :
Данные будут автоматически отфильтрованы, чтобы отображались только строки, в которых столбец Conference равен West :
Если мы попытаемся использовать функцию AVERAGEIF() для вычисления среднего значения в столбце Points, где значение в столбце Position равно «Guard», она фактически вернет среднее количество баллов для строк, равных «Guard». ” в исходном наборе данных:
Вместо этого нам нужно использовать следующую формулу:
=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