Como procurar um valor e obter vários valores correspondentes no Excel?
Este tutorial aborda a procura de um valor e o retorno de múltiplos valores correspondentes no Excel, conforme ilustrado nas imagens seguintes:
Procurar valor e devolver múltiplos valores correspondentes com fórmula matricial
Procurar valor e devolver múltiplos valores correspondentes com Filtro
Procurar valor e devolver múltiplos valores correspondentes com Função Definida
Procurar valor e devolver múltiplos valores correspondentes com fórmula matricial
Aqui está uma fórmula matricial avançada que ajuda a procurar um valor e devolver todos os valores correspondentes.
1. Introduza o valor que pretende procurar numa célula vazia. Veja a imagem:
2. Na célula adjacente, introduza esta fórmula =SE(ÉERRO(ÍNDICE($A$1:$B$7;MENOR(SE($A$1:$A$7=$D$4;LIN($A$1:$A$7));LIN(1:1));2));«»;)
ÍNDICE($A$1:$B$7;MENOR(SE($A$1:$A$7=$D$4;LIN($A$1:$A$7));LIN(1:1));2))na mesma e prima Shift + Ctrl + Entersimultaneamente; depois, arraste a alça de Preenchimento Automáticopara preencher as células abaixo até surgir a primeira célula em branco. Veja a imagem:
Nota: Na fórmula acima, $A$1:$B$7 indica o intervalo de dados, $A$1:$A$7 corresponde à coluna em que procura o valor específico, $D$4 refere-se à célula onde inseriu o valor a localizar no passo 1 e o número 2 indica que os valores correspondentes se encontram na segunda coluna.
Procurar valor e devolver múltiplos valores correspondentes com Filtro
No Excel, pode também utilizar a funcionalidade Filtro para resolver este problema.
1. Selecione o intervalo da coluna onde pretende procurar o valor e clique em Dados > Filtro. Veja a imagem:
2. Depois, clique na seta da primeira célula do seu intervalo selecionado e escolha apenas o valor que pretende procurar na lista suspensa. Veja a imagem:
3. Clique em OK e verá que apenas o valor procurado e os respetivos valores correspondentes foram filtrados.
Procurar valor e devolver múltiplos valores correspondentes com Função Definida
Se estiver interessado em Funções Definidas, pode também resolver este problema com uma Função Definida.
1. Prima Alt + F11 para abrir a janela do Microsoft Visual Basic for Applications.
2. Clique em Módulo > Inserir para inserir uma janela de Módulo e copie o seguinte código VBA para essa janela.
VBA: Procure um valor e obtenha vários valores correspondentes.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. Feche esta janela e introduza esta fórmula numa célula:=MyVlookup(A10;$A$2:$B$7;2) (A10 indica o valor a procurar, $A$2:$B$7 representa o intervalo de dados e 2 corresponde ao número da coluna de índice). Prima Shift + Ctrl + Enter simultaneamente. Em seguida, arraste a alça de preenchimento para baixo nas células, coloque o cursor na Barra de Fórmulas e prima novamente Shift + Ctrl + Enter.
Dica: Se pretender devolver os valores em células horizontais, introduza esta fórmula:=MyVlookup(A10, $A$2:$B$7, 2, «h»).
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