Calcular a Média Ignorando Erros no Excel

Como calcular a média ignorando erros no Excel passo-a-passo com download da planilha de exemplo grátis.

1. Introdução

No Excel, uma coluna que a priori deve possuir números para permitir os mais diversos tipos de cálculos pode apresentar erros derivados de diversas causas. Isto pode ser um problema sério, que inviabiliza, por exemplo, um simples cálculo de média.

Tomemos como base os dados a seguir.

Note que o último campo possui erros típicos retornados por fórmulas (destacados em vermelho). Ao tentarmos calcular a média com todos os dados deste campo temos um resultado de erro.

Temos no Excel uma série de alternativas para contornar este problema. Por exemplo, no Power Query, temos tratamentos específicos para os erros. Lá, uma possibilidade é substituir os erros por null.

Este artigo propõe algumas soluções no contexto de fórmulas de planilhas para que a média seja obtida. Em essência, serão expostas alternativas de ignorar os erros existentes.

P.S: o objetivo é expor o cálculo da média ignorando os erros. No entanto, também ignorando erros e com breves adaptações, podemos calcular a soma, máximo, mínimo, contar valores, contar números etc.

2. Fórmulas para calcular ignorando erros

2.1. Usando a função AGREGAR

Esta função é conhecida por sua flexibilidade em lidar com erros e permitir diversos tipos de cálculos. Ela surgiu no Excel 2010 e assim como a conhecida função SUBTOTAL permite trabalhar com dados e realizar diversos tipos de cálculos, como soma, média, máximo, mínimo etc.

Para realizar o cálculo da média de valores com a função AGREGAR de modo a ignorar os erros, devemos informar 3 argumentos: nº da função escolhida (que será 1 pois desejamos a média), a opção escolhida (usamos 6 para indicar que desejamos ignorar erros) e ao final o próprio intervalo em que há os valores.

A imagem seguinte ilustra a fórmula obtida para o nosso caso.

A fórmula inserida foi =AGREGAR(1;6;D3:D26). Em vez de informar o número 1 no primeiro argumento, caso deseje outro tipo de cálculo, use os números de acordo com a imagem a seguir:

2.2. Usando outras funções

Anteriormente à função AGREGAR era bem mais comum usar aninhamentos, inclusive matriciais, para obter o resultado desejado.

A próxima fórmula proposta usa a função SEERRO para “atualizar” a coluna, trocando cada erro por um texto (““). Ela é matricial (considere finalizar sua fórmula teclando CTRL + SHIFT + ENTER em vez de apenas ENTER), retornando múltiplos valores: os erros se vazios (“”) e os outros valores (que originalmente já não apresentavam erros) se mantém inalterados. Estes valores, ou seja, o resultado disponibilizado por SERRRO, são contemplados para a média normalmente. Por isso, SEERRO está encapsulada dentro de MÉDIA.

A fórmula inserida foi =MÉDIA(SEERRO(D3:D26;"")).

Uma alternativa é usar a função ÉERROS, que basicamente “reinventa a roda” em relação à fórmula anterior. Daí, a fórmula proposta usar a função SE aninhada com ÉERROS, ou seja: SE(ÉERROS é como se fosse a decomposição equivalente de SEERRO.

A imagem seguinte ilustra a fórmula usada.