Como utilizar uma fórmula de pesquisa bidirecional no Excel?
A pesquisa bidirecional permite-lhe obter o valor situado na interseção de uma linha e de uma coluna específicas numa tabela. Esta técnica revela-se especialmente útil quando os seus dados contêm rótulos distintos nas linhas e cabeçalhos nas colunas, e precisa de localizar rapidamente um valor com base nesses critérios. Por exemplo, imagine que gere um relatório de vendas, uma folha de presenças ou uma tabela orçamental e pretende identificar de imediato o valor correspondente a uma determinada data e ao identificador de um colaborador. Com a funcionalidade de pesquisa bidirecional do Excel, consegue extrair essa informação de forma rápida e eficaz. A imagem seguinte ilustra um cenário típico: é devolvido o valor localizado na interseção da linha «AA-3» e da coluna «5-Jan».
Pesquisa bidirecional com fórmulas
Macro VBA para pesquisa bidirecional
Pesquisa bidirecional com fórmulas
Realizar uma pesquisa bidirecional no Excel é uma forma simples e eficaz de obter o valor situado na interseção de cabeçalhos de linha e coluna específicos, especialmente em tabelas bem estruturadas. Essa abordagem aplica-se a diversos cenários, como comparar registos de colaboradores por data, extrair valores orçamentais com base em região e mês ou localizar classificações de testes para um determinado aluno e disciplina.
Embora as fórmulas sejam flexíveis e convenientes, a sua principal limitação reside na exigência de que a estrutura da tabela permaneça fixa. Para necessidades mais dinâmicas ou automatizadas, outras soluções poderão ser preferíveis — métodos adicionais são descritos abaixo.
Para executar uma pesquisa bidirecional utilizando fórmulas, siga estes passos:
1. Liste os cabeçalhos das colunas e os rótulos das linhas que pretende pesquisar. Manter cabeçalhos precisos e consistentes ajuda a evitar erros de pesquisa causados por espaços extra ou formatações inconsistentes. Eis um exemplo de uma tabela bem rotulada:
2. Na célula onde pretende apresentar o resultado, introduza uma das seguintes fórmulas, de acordo com o layout da sua tabela:
Fórmula 1: Combinação ÍNDICE e COINCIDIR
=INDEX(A1:I8,MATCH(L1,A1:A8,0),MATCH(L2,A1:I1,0))Esta fórmula identifica os índices da linha e da coluna ao corresponder os cabeçalhos especificados e, em seguida, devolve o valor na respetiva interseção.
Fórmula 2: SOMARPRODUTO para tabelas numéricas
=SUMPRODUCT((A1:A8=L1)*(A1:I1=L2),A1:I8)A função SOMARPRODUTO oferece os melhores resultados quando os seus dados incluem apenas valores numéricos e pode não devolver o resultado esperado com valores de texto.
Fórmula 3: PROCV com COINCIDIR
=VLOOKUP(L1,$A$1:$I$8,MATCH(L2,B1:I1,0)+1,FALSE)Este método procura primeiro a linha e, em seguida, utiliza COINCIDIR para determinar o deslocamento da coluna.
Dicas:
(1)Explicação dos parâmetros:
A1:A8é o intervalo dos rótulos das linhas,L1é o rótulo da linha específica que pretende encontrar;A1:I1é o intervalo dos cabeçalhos das colunas,L2é o cabeçalho da coluna pretendida;A1:I8é o intervalo total da tabela; ajuste estas referências conforme necessário para as adaptar aos seus dados.
(2) Se os seus Intervalos de valor de pesquisa forem texto e utilizar SUMPRODUCT, este devolverá 0. Nestes casos, recomenda-se utilizar a combinação INDEX/COINCIDIR.
Ao introduzir fórmulas, certifique-se de que os valores dos cabeçalhos em L1 (para linhas) e L2 (para colunas) correspondem exatamente aos da sua tabela, incluindo sensibilidade a maiúsculas e minúsculas, se necessário.
3. Prima a tecla Enter para confirmar a fórmula. A célula selecionada irá agora apresentar o valor na interseção do rótulo da linha e do cabeçalho da coluna especificados.
Precauções e resolução de problemas:
- Se a fórmula devolver um erro como #N/D, verifique se os seus cabeçalhos não contêm espaços extra ou discrepâncias na capitalização.
- Copiar fórmulas entre células pode exigir a conversão de referências relativas em absolutas; utilize os símbolos $ sempre que necessário.
- Se a sua tabela for grande ou tiver um tamanho variável, considere utilizar intervalos nomeados dinâmicos ou soluções alternativas, como o VBA abaixo, para garantir uma melhor escalabilidade.
Macro VBA para pesquisa bidirecional
Em situações onde a pesquisa bidirecional baseada em fórmulas se torna restritiva — como quando são necessárias pesquisas insensíveis a maiúsculas/minúsculas, suporte a tamanhos de intervalo dinâmicos ou automatização de pesquisas repetidas — uma macro VBA personalizada pode ser uma solução prática. O VBA é especialmente útil para utilizadores que frequentemente trabalham com estruturas de tabela mutáveis ou que necessitam de integração de pesquisas em fluxos de trabalho automatizados.
Eis como configurar e utilizar uma macro VBA para pesquisa bidirecional no Excel:
1. Aceda a Ferramentas de Programador > Visual Basic, o que abre o editor do Microsoft Visual Basic para Aplicações. Clique em Inserir > Módulo para adicionar um novo módulo e cole o seguinte código no módulo:
Sub TwoWayLookupMacro()
Dim tblRange As Range
Dim rowLabel As String
Dim colLabel As String
Dim rowIdx As Variant
Dim colIdx As Variant
Dim result As Variant
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set tblRange = Application.InputBox("Select the table range for lookup", xTitleId, Type:=8)
rowLabel = Application.InputBox("Enter the row label to find", xTitleId, Type:=2)
colLabel = Application.InputBox("Enter the column header to find", xTitleId, Type:=2)
On Error GoTo 0
rowIdx = Application.Match(LCase(rowLabel), Application.Index(tblRange, 0, 1), 0)
colIdx = Application.Match(LCase(colLabel), Application.Index(tblRange, 1, 0), 0)
If IsError(rowIdx) Or IsError(colIdx) Then
MsgBox "Row or column label not found. Please check your input.", vbExclamation, xTitleId
Exit Sub
End If
result = tblRange.Cells(rowIdx, colIdx).Value
MsgBox "The value at the intersection is: " & result, vbInformation, xTitleId
End Sub2. Para executar a macro, clique no botão 
ou prima F5. Ser-lhe-á solicitado que selecione o intervalo da sua tabela e introduza os rótulos da linha e da coluna. A macro devolverá o valor na respetiva interseção numa caixa de diálogo.
Dicas práticas:
- Certifique-se de que os cabeçalhos da sua tabela estejam localizados na primeira linha e na primeira coluna do intervalo selecionado, para garantir uma correspondência precisa.
- Esta macro utiliza correspondência insensível a maiúsculas e minúsculas ao converter a entrada para letras minúsculas, evitando assim erros comuns de capitalização.
- Se o layout da sua tabela for diferente, poderá precisar de ajustar a macro para garantir uma indexação correta.
- Para casos de utilização mais complexos, o código VBA pode ser expandido para tratar pesquisas em lote ou escrever diretamente os resultados nas células do Excel.
Se encontrar problemas, como não localizar cabeçalhos, verifique se os rótulos e o intervalo de dados não contêm espaços iniciais, finais, espaços a seguir ou caracteres ocultos.
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