Neste artigo, aprenderemos a calcular a média para números que atendem aos critérios especificados no Excel.
Cenário:
Ao trabalhar com dados, precisamos obter a média dos números com valores de critérios. Por que os valores dos critérios criam uma diferença no resultado, a função do Excel deve considerá-lo zero e pegar o resultado. Um valor zero pode fazer diferença na média dos números. A média dos números é a soma dos números exigidos dividida pela contagem dos números. Onde o zero não contribui para a soma, mas aumenta a contagem. Entenda como resolver o problema.
Como resolver o problema?
Aqui, precisamos encontrar a média dos números com critérios. Criteria está ignorando os valores dos critérios. Se você tiver apenas números positivos ou negativos, você pode usar a função AVERAGEIF
Sintaxe da fórmula (números positivos):
| = AVERAGEIF (valores, "> = 0") |
A fórmula acima inclui zeros, mas ignora os valores dos critérios. Se você deseja ignorar zeros e valores de critérios, basta usar os critérios como "> 0".
Da mesma forma, você pode obter a média dos valores negativos, incluindo zeros, usando a fórmula:
Sintaxe da fórmula (números negativos):
| = AVERAGEIF (valores, "<= 0") |
A fórmula acima inclui zeros, mas ignora os valores dos critérios. Se você deseja ignorar zeros e valores de critérios, basta usar os critérios como "> 0"
Mas o problema de considerar números ignorando critérios não está resolvido. Você pode pensar que, tirando a média dos dois resultados acima (média de números positivos e média de números negativos), obterá o valor necessário. Não funciona bem até que a contagem de números positivos seja igual à contagem de números negativos. Para este problema, usaremos a função AGGREGATE. A função AGGREGATE é o pacote de funções matemáticas. Ajuda geralmente a resolver esses tipos de problemas. Para descompactar este pacote para a média dos valores ignorando critérios. Use a fórmula abaixo.
Sintaxe da fórmula (para todos os números):
| = AGREGADO (1, 6, valores) |
1: Argumento para números médios
6: ignorando valores de critérios
Exemplo :
Tudo isso pode ser confuso de entender. Vamos dar um exemplo e entender como usar essas fórmulas em um exemplo. Aqui temos uma lista de valores para os quais a média é necessária.
Em primeiro lugar, calculamos a média dos números positivos. Para isso, usaremos o seguinte para os dados.
Use a fórmula:
| = MÉDIA SE (B3: B15, "> = 0") |
Explicação
- Critérios são números maiores ou iguais a zero.
- A função AVERAGEIF considera apenas os números que satisfazem os critérios e retorna a média dos números
Aqui, a fórmula retorna 58,6 considerando apenas os valores positivos, onde a contagem de valores positivos é 7. Da mesma forma, você pode obter a média dos números negativos.
Use a fórmula:
| = MÉDIA SE (B3: B15, "<= 0") |
Explicação
- Critérios são números menores ou iguais a zero.
- A função AVERAGEIF considera apenas os números que satisfazem os critérios e retorna a média dos números.
Para números negativos, a média é -21,0 incluindo zeros ignorando os valores dos critérios. A contagem para os números considerados é 4.
Aqui, neste exemplo, a contagem de números negativos não é igual à contagem de números positivos. Portanto, a média dos 2 resultados acima não produzirá a média de todos os números. Use a fórmula abaixo para esses casos.
Use a fórmula:
| = AGREGADO (1, 6, B3: B15) |
Explicação:
- O argumento 1 é fornecido para usar a função Média.
- O argumento 6 é fornecido para ignorar os valores dos critérios.
- A função AGGREGATE retorna a média de todos os números na matriz B3: B15, ignorando os valores dos critérios.
A média de todos os números que ignoram todos os valores dos critérios chega a ser 36,2. Aqui, a contagem de números é 9. A função AGREGAR é a melhor maneira de resolver esses problemas, pois essas funções são funções inerentes ao Excel.
Se você está tentando obter a média dos números usando a função básica MÉDIA, você precisa usar a fórmula abaixo para obter a média dos números ignorando os critérios.
Use a fórmula:
| { = MÉDIA (SE (IScritérios (B3: B15), "", B3: B15)) } |
Observação : não use as chaves manualmente. Apenas use Ctrl + Shift + Enter no lugar de Digitar para obter o resultado.
Explicação:
- A função IF verifica a condição com a função IScriteria na matriz B3: B15.
- A função IScriteria retorna True para o valor onde o valor é um critério e False onde não é.
- A função IF retorna um valor em branco se o valor for um critério e retorna o valor correspondente se o valor não for um critério.
- A função AVERAGE retorna a média dos valores retornados.
- Os colchetes fazem com que essas funções lógicas funcionem para uma matriz.
Como você pode ver, a fórmula funciona bem e retorna o mesmo resultado. Mas use a função AGREGAR, é fácil de usar para várias funções matemáticas com diferentes critérios embutidos.
Se você não tiver nenhum valor zero na matriz. Você pode usar o retorno zero de todos os valores dos critérios e usar a fórmula para obter a média dos números ignorando os zeros aqui. Mas essa fórmula não funciona com valores de critérios.
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas:
- A função AGREGADO é a melhor função a ser usada para esses problemas.
- A função retorna #VALUE! critérios se nenhum valor corresponder aos critérios fornecidos.
- Diferentes critérios dentro da fórmula são aceitos, como igual a (=), menor que (<), menor ou igual a (=), maior que (>) e diferente de ().
Desta forma, aprendemos a calcular a média para números que atendem aos critérios especificados no Excel. Você pode usar essas funções no Excel 2016, 2013 e 2010. Encontre mais artigos sobre Formulação matemática com diferentes critérios. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Como destacar células acima e abaixo do valor médio: realce os valores que estão acima ou abaixo do valor médio usando a formatação condicional do Excel.
Ignore zero na média dos números: calcular a média dos números na matriz ignorando zeros usando a função AVERAGEIF no Excel.
Calcular a média ponderada: encontre a média dos valores com pesos diferentes usando a função SUMPRODUCT do Excel.
Diferença média entre listas: calcule a diferença na média de duas listas diferentes. Aprenda mais sobre como calcular a média usando a fórmula matemática básica da média.
Números médios se não estiverem em branco no Excel: extrai a média dos valores se a célula não estiver em branco no excel.
MÉDIA das 3 melhores pontuações em uma lista no Excel: Encontre a média dos números com critérios como os 3 números mais altos da lista no Excel
Artigos populares:
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
