Como calcular rapidamente o percentil ou quartil, ignorando os zeros, no Excel?
Ao aplicar as funções PERCENTIL ou QUARTIL no Excel, os utilizadores frequentemente deparam-se com situações em que os seus Intervalo de Dados contêm Valores Zero. Por predefinição, estas funções incluem zeros nos cálculos, o que pode afetar significativamente os resultados ao reduzir os valores do percentil ou quartil, especialmente se o zero não representar dados significativos no contexto. Para uma análise estatística mais precisa, poderá pretender ignorar completamente os Valores Zero ao calcular o percentil ou quartil. Este tutorial mostrar-lhe-á várias técnicas práticas para resolver eficientemente este problema no Excel, incluindo abordagens com fórmulas nativas, soluções VBA e discussões sobre cenários apropriados para o ajudar a escolher o melhor método para as suas necessidades.
PERCENTIL ou QUARTIL ignorando zeros
PERCENTIL ignorando zeros (Fórmula de Matriz)
Para calcular o percentil ignorando os zeros, utilize uma fórmula de matriz que considere apenas os valores superiores a zero.
Selecione uma célula vazia onde deseje apresentar o resultado e introduza a seguinte fórmula:
=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)Após digitar a fórmula, prima Ctrl + Shift + Enter (e não apenas Enter), pois trata-se de uma fórmula de matriz. O Excel envolve automaticamente a fórmula com chavetas { }, indicando que foi introduzida corretamente. Nesta fórmula:
- A1:A13 é o seu intervalo de dados — ajuste-o conforme necessário para a sua própria folha.
- 0,3 especifica o 30 ºpercentil. Pode alterar este valor para qualquer percentil que deseje calcular (por exemplo, 0,75 para o 75)º percentil).
Este método é particularmente útil quando pretende evitar que zeros — como medições em falta ou nulas — afetem os resultados estatísticos.
Tenha em atenção que premir apenas Enter não funcionará corretamente; tem de utilizar Ctrl + Shift + Enter. Além disso, fórmulas com SE(...) dentro de funções de agregação podem ser menos eficientes em conjuntos de dados grandes.
QUARTIL ignorando zeros (Fórmula de Matriz)
Esta abordagem é semelhante para os quartis. Selecione uma célula para o resultado e introduza:
=QUARTILE(IF(A1:A18>0,A1:A18),1)Após introduzir a fórmula, prima Ctrl + Shift + Enter para confirmar como fórmula de matriz.
- A1:A18 é o intervalo de dados da amostra (altere conforme necessário).
- 1significa que pretende o primeiro quartil (25)º percentil). Pode utilizar 2 para a mediana ou 3 para o terceiro quartil (75 º percentil).
Certifique-se de que o seu intervalo de dados não contém texto nem células com erros, pois a fórmula só funciona com valores numéricos. Esta solução é ideal para conjuntos de dados de dimensão moderada que exijam um cálculo rápido, sem recorrer a VBA ou extras.
Macro VBA para Filtrar e Calcular Percentil/Quartil Excluindo Zeros
Pode ainda utilizar o VBA (Visual Basic for Applications) para automatizar a filtragem de valores zero e, em seguida, calcular um percentil ou quartil com base nos dados restantes. Esta abordagem revela-se especialmente útil ao trabalhar com grandes conjuntos de dados ou sempre que pretenda repetir frequentemente o procedimento sem ter de introduzir fórmulas manualmente.
Cenários aplicáveis: Ideal para utilizadores avançados, tarefas repetitivas ou intervalos complexos. Ao personalizar o código, pode processar qualquer percentil, quartil ou área de dados.
1. Aceda ao separador Ferramentas de Programador no Excel. Se não estiver visível, clique com o botão direito na faixa de opções, escolha Personalizar a Faixa de Opções e ative a opção Programador. Em seguida, clique em Ferramentas de Programador > Visual Basic.
2. Na janela Microsoft Visual Basic para Aplicações, clique em Inserir > Módulo.
3. Copie e cole o seguinte código VBA no módulo:
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Clique no botão 
ou prima F5na janela do VBA para executar a macro. Ser-lhe-á solicitado que selecione o seu Intervalo de Dados (apenas números), especifique o percentil pretendido (por exemplo, 0,3 para o 30)º percentil) e o índice do quartil (como 1 para o primeiro quartil). A macro filtrará automaticamente os valores zero e apresentará os resultados numa caixa de mensagem.
Vantagens: Processa rapidamente grandes volumes ou dados irregulares, elimina totalmente os valores zero e evita a introdução manual de fórmulas. Permite reutilização e personalização.
Desvantagens: Requer ativação de macros e alguma familiaridade com VBA. Não é adequado para fórmulas de folha de cálculo, salvo se convertido numa função definida pelo utilizador (UDF).
Problemas comuns e resolução de problemas: Se selecionar células não numéricas ou que contenham erros, a macro poderá ignorá-las ou gerar um erro. Certifique-se de que o Intervalo de Dados inclui apenas números com valores zero ou positivos. Caso não sejam encontrados dados diferentes de zero, receberá uma notificação adequada.
Dicas: Pode personalizar ainda mais o código VBA para copiar o resultado diretamente para uma célula específica da folha de cálculo, ajustar as funções de cálculo ou automatizar múltiplas gamas. Guarde sempre a sua pasta de trabalho antes de executar ou editar macros, evitando assim perdas acidentais de dados.
Se precisar de alargar esta solução a cálculos de percentil ou quartil em múltiplas colunas, considere adaptar a macro para percorrer colunas ou intervalos através de um ciclo.
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