Como calcular a mediana no Excel, ignorando zeros e erros?
Em muitas tarefas de análise de dados no Excel, calcular a mediana com precisão é essencial para compreender a tendência central do seu conjunto de dados. Contudo, por vezes esse conjunto inclui zeros ou valores de erro (como)#DIV/0!, #N/D, etc.), que podem comprometer um cálculo direto da mediana. Por exemplo, ao usar a fórmula padrão =MEDIANA(intervalo), os zeros são incluídos no cálculo e, se houver células inválidas no intervalo, a fórmula devolve um erro — o que pode levar a resultados enganosos ou falhas no cálculo, conforme ilustrado abaixo.
Para resolver esta questão, existem várias soluções que lhe permitem calcular a mediana excluindo zeros ou erros, garantindo uma análise rigorosa e robusta. Estas soluções adaptam-se a diversos cenários — como a limpeza de dados de inquéritos, relatórios financeiros ou medições científicas — em que é essencial remover zeros ou erros para obter resultados significativos. Abaixo, encontra guias práticos passo a passo para cada método disponível no Excel, desde fórmulas diretas até técnicas avançadas de automatização.
VBA: Mediana ignorando zeros e erros (UDF)
Power Query: Mediana após filtrar zeros/erros
Mediana ignorando zeros
Quando o seu intervalo contém zeros que não pretende incluir no cálculo da mediana — por exemplo, valores em falta representados por 0 — pode usar uma fórmula matricial para os excluir. Esta abordagem é especialmente útil em conjuntos de dados onde os zeros servem como marcadores de informação indisponível e não como medições reais.
Selecione uma célula onde deseje apresentar a mediana (por exemplo, C2) e introduza a seguinte fórmula:
=MEDIAN(IF(A2:A17<>0,A2:A17))Depois de introduzir a fórmula, em vez de premir apenas Enter, prima Ctrl + Shift + Enter para a transformar numa fórmula matricial (verá chavetas a aparecerem à volta da fórmula na Barra de Fórmulas). Desta forma, garante que apenas os valores diferentes de zero em A2:A17 são considerados no cálculo da mediana. Veja a imagem:
Dicas:
- Se estiver a utilizar o Excel 365, o Excel 2021 ou versões posteriores, basta premir Enter, graças ao suporte de matrizes dinâmicas.
- Certifique-se de que existe pelo menos um valor numérico diferente de zero no intervalo; caso contrário, a fórmula devolverá o erro #NÚM!.
- Esta solução é ideal para limpar respostas de inquéritos, relatórios de despesas ou dados de vendas em que os zeros devem ser excluídos da análise.
Mediana ignorando erros
Valores de erro como #N/D, #DIV/0! ou #VALOR! podem fazer com que a função MEDIANA padrão devolva um erro, interrompendo a sua Análise de Dados. Para calcular a mediana de forma segura, excluindo esses erros, utilize a seguinte fórmula matricial.
Selecione qualquer célula onde deseje apresentar o resultado e introduza a fórmula abaixo:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Após introduzir a fórmula, prima Ctrl + Shift + Enter (exceto se estiver a utilizar o Excel 365/Excel 2021 ou versões posteriores, que permitem matrizes dinâmicas). Esta fórmula inclui apenas os valores em F2:F17 que sejam números genuínos — ignorando totalmente quaisquer células com erros.
Dicas e precauções:
- Se todas as células contiverem valores de erro, o resultado devolverá um erro #NÚM! — certifique-se de que os seus dados incluem pelo menos um número válido.
- Pode combinar critérios de exclusão — como excluir simultaneamente zeros e erros — aninhando condições.
- Esta fórmula revela-se especialmente útil ao trabalhar com dados importados, resultados de inquéritos ou demonstrações financeiras que possam incluir cálculos parciais ou falhados.
VBA: Mediana ignorando zeros e erros (UDF)
Em cenários onde necessita frequentemente de calcular a mediana ignorando simultaneamente zeros e erros — ou quando procura uma solução que evite a introdução manual de fórmulas matriciais — pode recorrer a uma função personalizada em VBA (Função Definida pelo Utilizador, UDF). Esta abordagem oferece flexibilidade acrescida, já que a função personalizada pode integrar todos os critérios de exclusão e ser utilizada exatamente como qualquer fórmula incorporada, revelando-se ideal para conjuntos de dados grandes ou sujeitos a atualizações frequentes.
Como configurar a UDF:
- Clique no separador Programador no Excel. Se não estiver disponível, ative-o através de Ficheiro > Opções > Personalizar Friso.
- Clique em Visual Basic para abrir o editor do VBA.
- No editor do VBA, clique em Inserir > Módulo para criar um novo módulo.
- Copie e cole o seguinte código no módulo:
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionComo utilizar a UDF:
Após regressar ao Excel, basta introduzir a fórmula =MedianIgnoreZeroError(A2:A17)em qualquer célula (substitua)A2:A17 pelo seu intervalo pretendido). Ao contrário das fórmulas matriciais, pressione apenas Enter — não é necessário utilizar Ctrl + Shift + Enter.
- Este método revela-se eficaz com conjuntos de dados muito grandes, evita as peculiaridades das fórmulas matriciais e pode ser facilmente adaptado para ignorar outros valores indesejados mediante uma edição adicional do código.
- Se o intervalo contiver apenas zeros ou erros, o resultado apresentará #NÚM!
- Se receber um erro #NOME?, verifique se a macro VBA está corretamente instalada e se as macros estão ativadas nas definições do Excel.
Power Query: Mediana após filtrar zeros/erros
O Power Query é uma ferramenta poderosa no Excel para importar, transformar e analisar dados — especialmente quando o objetivo é limpar e pré-processar grandes conjuntos de dados antes de calcular métricas como a mediana. Com o Power Query, pode filtrar facilmente zeros e erros, garantindo que apenas números válidos permaneçam no seu cálculo. Esta abordagem revela-se particularmente vantajosa se os seus dados de origem forem atualizados regularmente ou importados de sistemas externos.
Passos para utilizar o Power Query no cálculo da mediana ignorando zeros e erros:
- Selecione qualquer célula dentro do seu intervalo de dados e, em seguida, aceda ao separador Dados e clique em A partir de Tabela/Intervalo. Se os seus dados ainda não estiverem em formato de tabela, o Excel pedir-lhe-á para criar uma — basta clicar em OK.
- A janela do Editor do Power Query abrirá. Clique na seta suspensa da coluna relevante e desmarque 0para filtrar os valores zero. (Para filtrar erros, clique com o botão direito no cabeçalho da coluna e escolha)Remover Erros.)
- Após aplicar o filtro, clique em Base > Fechar e Carregar para enviar os dados limpos de volta à sua folha de cálculo.
- Agora, aplique a fórmula padrão
=MEDIANA()à coluna com os valores já filtrados, já que os dados excluem todos os itens indesejados.
Este método garante que os seus dados originais permaneçam inalterados, oferece elevada repetibilidade com dados novos ou atualizados e revela-se particularmente eficaz em tarefas recorrentes de relatórios ou ao trabalhar com conjuntos de dados grandes ou externos. Os fluxos de trabalho do Power Query podem ser atualizados com um único clique sempre que os seus dados de origem mudarem, minimizando a intervenção manual e o risco de erros.
- O Power Query está disponível no Excel 2016 e versões posteriores (ou como suplemento para o Excel 2010 e 2013).
- Após a transformação, os cálculos podem ser realizados nos dados limpos resultantes, garantindo maior fiabilidade às análises subsequentes.
Se surgirem resultados inesperados, reveja os seus passos de filtragem no Power Query e confirme que ainda existem valores numéricos válidos nos seus dados limpos.
Em resumo, quer prefira usar diretamente fórmulas matriciais, desenvolver uma solução personalizada em VBA para automatização ou aproveitar o Power Query para fluxos de trabalho mais amplos, o Excel oferece várias opções práticas para calcular a mediana ignorando zeros ou erros. Escolha o método que melhor se adapta ao tamanho do seu conjunto de dados, à frequência das atualizações e às suas preferências de fluxo de trabalho, garantindo assim resultados fiáveis e precisos.
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