Como calcular a mediana no Excel com várias condições?
O cálculo da mediana de um conjunto de dados no Excel é uma operação frequentemente necessária em Análise de Dados e relatórios. Embora seja possível obter rapidamente a mediana de um intervalo simples utilizando funções padrão do Excel, surgem frequentemente situações em que é necessário obter apenas o valor mediano dos dados que cumprem vários critérios específicos — por exemplo, determinar o valor mediano das vendas de um determinado produto numa data específica, dentro de um conjunto alargado de dados. Lidar com este tipo de operações condicionais complexas apenas com funções tradicionais pode ser desafiante. Neste tutorial, apresentamos várias soluções práticas para calcular a mediana com múltiplas condições no Excel, explorando tanto abordagens baseadas em fórmulas como automação com VBA para necessidades avançadas.
- Calcular a mediana se forem cumpridas várias condições
- Código VBA – Calcular a mediana com várias condições
Calcular a mediana se forem cumpridas várias condições
Imagine que tem um intervalo de dados como o apresentado abaixo e que a sua tarefa é determinar o valor mediano que satisfaz dois critérios — por exemplo, encontrar o valor mediano da coluna B em que a coluna A contém «a» e a coluna C corresponde à data «2-Jan». Este cenário é especialmente comum em relatórios de vendas, resultados de testes escolares e outros contextos empresariais ou académicos de análise de dados, onde é necessário filtrar com base em múltiplas categorias.
Para maior clareza, prepare a sua folha de cálculo da seguinte forma: na sua folha do Excel, introduza as suas condições e crie um esquema semelhante ao da imagem abaixo. Neste exemplo, a coluna E apresenta os critérios correspondentes à coluna A, e a linha 1, a partir da coluna F, indica os critérios de data provenientes da coluna C.
Para calcular a mediana que satisfaz múltiplos critérios, utilize uma fórmula matricial que combina as funções MEDIANA e SE para criar uma lista filtrada de valores com base nas suas condições. Eis como proceder:
1.Clique na célula F2, onde pretende que o resultado da mediana apareça, e introduza a seguinte fórmula:
=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))Esta fórmula funciona verificando, em cada linha, se o valor da coluna A corresponde à condição em E2 e se o valor da coluna C corresponde ao cabeçalho em F1; caso ambas as condições sejam satisfeitas, recolhe o valor da coluna B para o cálculo da mediana.
2. Depois de introduzir a fórmula, prima Ctrl + Shift + Enter (e não apenas Enter), pois trata-se de uma fórmula matricial. O Excel colocará automaticamente chavetas à volta da fórmula { } para indicar que se trata de uma fórmula matricial.
3.Arraste a alça de preenchimento a partir do canto inferior direito da célula F2 para copiar a fórmula para outras células relevantes onde necessite de medianas com diferentes condições, conforme ilustrado abaixo:
Explicação dos parâmetros e dicas de utilização: Na fórmula, $A$2:$A$12 é o intervalo que contém a primeira condição (por exemplo, nomes de produtos), $C$2:$C$12 é o intervalo da segunda condição (por exemplo, datas) e $B$2:$B$12 é o intervalo que contém os valores numéricos relativamente aos quais pretende calcular a mediana. Ajuste estes intervalos conforme necessário na sua própria folha de cálculo. Utilize sempre referências absolutas (com o símbolo $) para garantir que os intervalos não se alteram ao copiar a fórmula.
Precauções: Se nenhum valor cumprir ambas as condições, a fórmula devolverá um erro #NÚM!. Para evitar confusão, pode incorporar a fórmula na função SEERRO, de modo a devolver uma célula vazia ou uma mensagem personalizada:
=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")Certifique-se de que os seus dados não contêm células vazias nem valores não numéricos na coluna da mediana, pois isso também poderá afetar os resultados.
Esta abordagem baseada em fórmulas é ideal para condições relativamente simples — normalmente até duas ou três. É rápida de configurar e não exige conhecimentos de programação. Contudo, em cenários de filtragem mais complexos, com condições dinâmicas ou conjuntos de dados maiores, manter ou editar fórmulas matriciais pode tornar-se incómodo.
Código VBA – Calcular a mediana com várias condições
Em cenários que exigem a automatização do cálculo da mediana condicional — como quando há múltiplas condições, grandes volumes de dados ou critérios em constante mudança — uma solução em VBA surge como uma alternativa prática. Com o VBA, é possível criar uma macro reutilizável capaz de calcular a mediana com base em qualquer número de condições. Soluções em VBA revelam-se especialmente úteis para agilizar análises repetitivas ou desenvolver processos personalizados no Excel, ideais para relatórios e painéis interativos.
Siga estes passos para utilizar o VBA no cálculo da mediana condicional:
1. Clique em Ferramentas de Programador > Visual Basic. Uma nova janela do Microsoft Visual Basic for Applications será aberta. Clique em Inserir > Módulo e cole o seguinte código no módulo:
Sub ConditionalMedian()
Dim DataRange As Range
Dim CriteriaRange1 As Range
Dim CriteriaRange2 As Range
Dim OutputRange As Range
Dim Criteria1 As Variant
Dim Criteria2 As Variant
Dim TempArr() As Double
Dim i As Long
Dim j As Long
Dim count As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
count = 0
For i = 1 To DataRange.Rows.count
If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
ReDim Preserve TempArr(count)
TempArr(count) = DataRange.Cells(i, 1).Value
count = count + 1
End If
Next i
If count = 0 Then
OutputRange.Value = "No match"
Else
Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
If count Mod 2 = 1 Then
OutputRange.Value = TempArr(count \ 2)
Else
OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
End If
End If
End Sub
Sub QuickSort(arr() As Double, first As Long, last As Long)
Dim i As Long
Dim j As Long
Dim pivot As Double
Dim temp As Double
i = first
j = last
pivot = arr((first + last) \ 2)
Do While i <= j
Do While arr(i) < pivot
i = i + 1
Loop
Do While arr(j) > pivot
j = j - 1
Loop
If i <= j Then
temp = arr(i)
arr(i) = arr(j)
arr(j) = temp
i = i + 1
j = j - 1
End If
Loop
If first < j Then
QuickSort arr, first, j
End If
If i < last Then
QuickSort arr, i, last
End If
End Sub2. Clique no botão 
(ou prima F5) para executar o código. Ser-lhe-á pedido que selecione cada um dos intervalos necessários e introduza os respetivos critérios. Após concluir estes passos, o resultado — a mediana que cumpre todos os critérios — será apresentado na célula de destino que especificou.
Esta macro permite-lhe selecionar, de forma flexível em cada execução, o intervalo de valores, os intervalos e valores dos critérios, bem como o local onde os resultados deverão ser apresentados. Além disso, pode adaptar facilmente o código para incluir mais condições, sempre que necessário.
Dicas e resolução de problemas: Ao utilizar soluções em VBA, certifique-se de que todos os intervalos selecionados têm o mesmo comprimento e de que os critérios correspondem ao tipo de dados e à formatação corretos (por exemplo, texto versus datas). Se nenhum valor cumprir os critérios, o resultado será «Sem correspondência.». Para maior estabilidade, guarde sempre a sua pasta de trabalho antes de executar a macro e ative as macros sempre que solicitado. Esta solução em VBA é ideal para utilizadores familiarizados com as definições de segurança de macros e para utilização em fluxos de trabalho automatizados no Excel.
Em resumo, a abordagem em VBA automatiza cálculos complexos de mediana que seriam incómodos ou difíceis de executar apenas com fórmulas, revelando-se especialmente adequada para condições variáveis, recalculações frequentes e grandes conjuntos de dados.
Artigos relacionados:
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