Como calcular a mediana ignorando zeros ou erros no Excel?
Em muitas tarefas de análise de dados no Excel, calcular com precisão a mediana é essencial para entender a tendência central do seu conjunto de dados. No entanto, às vezes o seu conjunto de dados contém zeros ou valores de erro (como #DIV/0!, #N/A, etc.), que podem interferir no cálculo direto da mediana. Por exemplo, usar a fórmula padrão =MEDIAN(range) incluirá os zeros no cálculo e retornará um erro se houver células inválidas presentes no intervalo, potencialmente levando a resultados enganosos ou falhas no cálculo, como ilustrado abaixo.
Para resolver isso, várias soluções podem ajudar você a calcular a mediana enquanto exclui zeros ou erros, garantindo que sua análise seja precisa e robusta. Essas soluções são adequadas para vários cenários, como limpar dados de pesquisa, relatórios financeiros ou medidas científicas onde zeros ou erros precisam ser removidos para obter resultados significativos. Abaixo, você encontrará guias práticos passo a passo para cada método disponível no Excel, variando de fórmulas diretas a técnicas avançadas de automaçã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 você não deseja considerar no cálculo da mediana – como valores ausentes representados como 0 – você pode usar uma fórmula de matriz para excluir zeros. Isso é especialmente útil em conjuntos de dados onde os zeros são espaços reservados para dados indisponíveis, em vez de medições reais.
Selecione uma célula onde você deseja exibir a mediana (por exemplo, C2) e insira a seguinte fórmula:
=MEDIAN(IF(A2:A17<>0,A2:A17))Depois de inserir a fórmula, em vez de apenas pressionar Enter, pressione Ctrl + Shift + Enter para transformá-la em uma fórmula de matriz (você verá chaves aparecerem ao redor da fórmula na barra de fórmulas). Isso garante que apenas os valores diferentes de zero em A2:A17 sejam considerados para o cálculo da mediana. Veja a captura de tela:
Dicas:
- Se você estiver usando o Excel 365 ou o Excel 2021 ou superior, pressionar Enter sozinho já é suficiente, graças ao suporte de matrizes dinâmicas.
- Certifique-se de que haja pelo menos um valor numérico diferente de zero no intervalo, caso contrário, a fórmula retornará um erro #NÚM!.
- Essa solução é ideal para limpar respostas de pesquisas, relatórios de despesas ou dados de vendas onde 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 de mediana padrão retorne um erro, interrompendo sua análise de dados. Para calcular a mediana com segurança excluindo esses erros, você pode usar a seguinte fórmula de matriz.
Selecione qualquer célula onde você gostaria de exibir seu resultado e insira a fórmula abaixo:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Depois de inserir a fórmula, pressione Ctrl + Shift + Enter (a menos que você esteja usando o Excel 365/Excel 2021 ou superior, que permite matrizes dinâmicas). Essa fórmula inclui apenas aqueles valores em F2:F17 que são números genuínos – ignorando totalmente quaisquer células de erro.
Dicas e Precauções:
- Se todas as células forem valores de erro, o resultado retornará um erro #NÚM! – certifique-se de que seus dados incluem pelo menos um número válido.
- Você pode combinar critérios de exclusão (por exemplo, excluindo zeros e erros) aninhando condições.
- Essa fórmula é especialmente útil ao trabalhar com dados importados, resultados de pesquisas ou demonstrações financeiras que podem conter cálculos parciais ou falhos.
VBA: Mediana ignorando zeros e erros (UDF)
Para cenários onde você frequentemente precisa calcular a mediana ignorando tanto zeros quanto erros, ou precisa de uma solução que evite digitar manualmente fórmulas de matriz, você pode usar uma função personalizada VBA (Função Definida pelo Usuário, UDF). Essa abordagem oferece flexibilidade extra porque a função personalizada pode encapsular todos os critérios de exclusão e ser usada como qualquer fórmula embutida, sendo adequada para grandes conjuntos de dados ou dados atualizados frequentemente.
Como configurar a UDF:
- Clique na guia Desenvolvedor no Excel. Se ela não estiver disponível, ative-a via Arquivo > Opções > Personalizar Faixa de Opções.
- 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 usar a UDF:
Depois de voltar ao Excel, simplesmente insira a fórmula =MedianIgnoreZeroError(A2:A17) em qualquer célula (substitua A2:A17 pelo seu intervalo de destino). Ao contrário das fórmulas de matriz, basta pressionar Enter – não há necessidade de Ctrl + Shift + Enter.
- Esse método funciona bem para conjuntos de dados muito grandes, evita peculiaridades das fórmulas de matriz e pode ser adaptado para ignorar outros valores indesejados editando ainda mais o código.
- Se o intervalo contiver apenas zeros ou erros, o resultado mostrará #NÚM!
- Se você receber um erro #NOME?, verifique se a macro VBA está instalada corretamente e se as macros estão habilitadas nas configuraçõ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 realizar cálculos como a mediana. Com o Power Query, você pode facilmente filtrar zeros e erros, garantindo que apenas números válidos permaneçam em seu cálculo. Essa abordagem é especialmente benéfica se seus dados de origem forem atualizados regularmente ou importados de sistemas externos.
Passos para usar o Power Query para calcular a mediana ignorando zeros e erros:
- Selecione qualquer célula dentro do intervalo de dados, vá para a guia Dados e clique em De Tabela/Intervalo. Se seus dados ainda não estiverem no formato de tabela, o Excel solicitará que você crie uma tabela – clique em OK.
- A janela do Editor do Power Query será aberta. Clique na seta suspensa da coluna relevante e desmarque 0 para filtrar os valores zero. (Para filtro de erros, clique com o botão direito do mouse no cabeçalho da coluna, escolha Remover Erros.)
- Uma vez filtrado, clique em Página Inicial > Fechar e Carregar para enviar os dados limpos de volta à sua planilha.
- Agora, aplique a fórmula padrão
=MED()na coluna com os valores filtrados apenas, já que os dados agora excluem todos os itens indesejados.
Esse método garante que seus dados originais permaneçam inalterados, oferece forte repetibilidade com novos ou dados atualizados e é particularmente eficaz para tarefas de relatórios recorrentes ou ao trabalhar com grandes conjuntos de dados ou dados externos. Os fluxos de trabalho do Power Query podem ser atualizados com um único clique sempre que seus dados de origem mudarem, minimizando intervenções manuais e risco de erros.
- O Power Query está disponível no Excel 2016 e versões mais recentes (ou como um complemento para o Excel 2010 e 2013).
- Após a transformação, os cálculos podem ser realizados nos dados limpos resultantes, proporcionando maior confiabilidade para análises downstream.
Se ocorrerem resultados inesperados, verifique novamente suas etapas de filtragem no Power Query e confirme se há valores numéricos válidos restantes em seus dados limpos.
Em resumo, quer você prefira usar fórmulas de matriz diretamente, criar uma solução personalizada VBA para automação ou aproveitar o Power Query para automação de fluxos de trabalho maiores, o Excel oferece várias opções práticas para calcular a mediana enquanto ignora zeros ou erros. Escolha o método que melhor se adapta ao tamanho do seu conjunto de dados, frequência de atualização e preferências de fluxo de trabalho para obter saídas confiáveis e precisas.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins