Como usar subtotal com averageif no excel

Você pode usar a seguinte fórmula para combinar as funções SUBTOTAL e MÉDIA no Excel:

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

Esta fórmula específica permite calcular a média dos valores no intervalo C2:C11 onde o valor correspondente no intervalo B2:B11 é igual a “Manter” mesmo depois que esse intervalo de células tiver sido filtrado de uma forma ou de outra. outro.

O exemplo a seguir mostra como usar esta fórmula na prática.

Exemplo: Como usar SUBTOTAL com AVERAGEIF no Excel

Suponha que temos o seguinte conjunto de dados contendo informações sobre vários jogadores de basquete:

A seguir, vamos filtrar os dados para mostrar apenas as linhas onde os jogadores da Conferência Oeste estão localizados.

Para fazer isso, destaque o intervalo de células A1:C11 . Em seguida, clique na guia Dados na faixa superior e clique no botão Filtro .

Em seguida, clique na seta suspensa ao lado de Conferência e certifique-se de que apenas a caixa ao lado de Oeste esteja marcada e clique em OK :

Os dados serão filtrados automaticamente para mostrar apenas as linhas onde a coluna Conferência for igual a West :

Se tentarmos usar a função AVERAGEIF() para calcular a média dos valores da coluna Pontos onde o valor da coluna Posição é igual a “Guarda”, ela na verdade retornará a média dos pontos para linhas iguais a “Guarda”. no conjunto de dados original:

Em vez disso, precisamos usar a seguinte fórmula:

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

A captura de tela a seguir mostra como usar esta fórmula na prática:

Esta fórmula retorna a média correta de 21,25 .

Podemos confirmar que isso está correto calculando manualmente a média dos valores dos pontos para as linhas onde a coluna Posição é igual a “Guarda”:

Média de pontos de guarda: (12 + 28 + 30 + 15) / 4 = 21,25 .

Recursos adicionais

Os tutoriais a seguir explicam como realizar outras operações comuns no Excel:

Como excluir linhas filtradas no Excel
Como contar linhas filtradas no Excel
Como adicionar linhas filtradas no Excel