Como calcular a média correta no Excel quando há valores em falta?
Ao trabalhar com dados no Excel, é comum deparar-se com intervalos que contêm valores em falta ou células preenchidas com indicadores de erro, como #N/D ou #DIV/0!. Se utilizar a função padrão do Excel MÉDIA num intervalo deste tipo, a fórmula devolverá um erro em vez de ignorar as células problemáticas. Isto significa que os seus cálculos podem tornar-se inválidos ou inutilizáveis sempre que os dados estiverem incompletos ou contiverem erros, conforme ilustrado na imagem abaixo. Conseguir calcular a média com precisão — ignorando esses valores em falta ou com erros — é crucial, especialmente em relatórios empresariais, investigação académica ou qualquer cenário que envolva conjuntos de dados grandes e complexos. Abaixo encontrará soluções práticas e instruções passo a passo para calcular a média correta nestas situações, garantindo a integridade da sua análise de dados.
Média com valor em falta
Para calcular uma média no Excel ignorando valores em falta ou células com erros, pode optar por várias fórmulas que ignoram inteligentemente células não numéricas e com erros. Isto é essencial em cenários como análise de resultados de inquéritos (em que algumas respostas podem estar incompletas), dados de vendas ao longo do tempo (com meses em branco) ou registo de dados científicos (em que certas medições podem estar em falta ou inválidas). Abaixo estão duas soluções fiáveis com base em fórmulas e os respetivos detalhes.
Opção 1: Função AGREGAR
A função AGREGAR é particularmente adequada para situações em que espera erros ou valores ocultos no seu intervalo de dados, pois permite configurar quais os tipos de dados a ignorar. Isto é especialmente útil ao consolidar dados provenientes de múltiplas fontes ou quando o conjunto de dados é atualizado frequentemente com entradas inconsistentes.
1. Selecione a célula onde pretende exibir o resultado da média e introduza a seguinte fórmula:
=AGGREGATE(1,6,A2:C2)Aqui, 1 especifica a operação MÉDIA e 6 indica ao Excel para ignorar os valores de erro no intervalo limitado A2:C2.
2. Após introduzir a fórmula, prima Shift + Ctrl + Enter (para versões antigas do Excel) ou, simplesmente, prima Enter se estiver a utilizar o Excel 365 ou o Excel 2019, já que estas versões suportam matrizes dinâmicas sem necessidade de combinações adicionais de teclas.
Vantagens: Fácil de utilizar; ignora diretamente os erros e devolve resultados precisos. Funciona perfeitamente em intervalos que incluam valores de erro.
Limitações: Disponível apenas a partir do Excel 2010. Versões anteriores do Excel não suportam a função AGREGAR.
Opção 2: MÉDIA com SE e É.NÚM numa fórmula de matriz
Alternativamente, pode calcular a média apenas dos valores numéricos — ignorando automaticamente erros e células vazias — utilizando uma fórmula de matriz. Esta abordagem é ideal para garantir que apenas números válidos sejam considerados, sendo especialmente útil em relatórios mensais, sistemas de classificação ou folhas financeiras.
1. Na célula onde pretende calcular a média, introduza esta fórmula:
=AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2)))Esta fórmula testa cada célula em A2:C2, mantém apenas os valores numéricos e calcula a respetiva média.
2. Após a introdução, mantenha premidas as teclas Ctrl e Shift e, em seguida, prima Enter para transformá-la numa fórmula de matriz nas versões mais antigas do Excel (confirmado pela presença de chavetas na Barra de Fórmulas). No Excel 365 e Excel 2019, basta premir Enter.
Dica: Se os seus dados abrangerem várias linhas e precisar de resultados para cada uma delas, utilize a alça de preenchimento (o pequeno quadrado no canto inferior direito da célula selecionada) para arrastar a fórmula para baixo. Assim, as referências são ajustadas automaticamente em cada linha.
Vantagens: Funciona em todas as versões do Excel; ignora valores não numéricos e erros; é fiável numa variedade de cenários com erros ou valores em falta.
Limitações: Requer introdução como fórmula de matriz nas versões antigas do Excel, o que pode ser confuso. Um erro de escrita pode originar um erro #VALOR! se for introduzido de forma incorreta.
Independentemente do método escolhido, certifique-se de que o intervalo-alvo inclui apenas os dados pretendidos — colunas ou linhas adicionais não intencionais podem distorcer os resultados. Caso encontre erros persistentes, reveja o método de introdução da fórmula (nomeadamente fórmulas de matriz no Excel mais antigo), confirme se selecionou corretamente o intervalo e assegure-se de que não existem células combinadas ou ocultas que possam interferir no cálculo.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos