Cruzamento Relacional de Dados
Uma das tarefas fundamentais no Excel consiste em relacionar tabelas independentes:
- A partir de um Código de Artigo, obter a respetiva Descrição e o Preço Unitário.
- A partir de um NIF, obter o Nome da Entidade e a Morada de Faturação.
- A partir de um Artigo e Canal de Venda, localizar o preço exato numa matriz bidimensional.
🧪 Laboratório Visual Interativo Disponível:
Simule em tempo real o varrimento do radar do $PROCV$, a superação da barreira de pesquisa à esquerda e cruzamentos matriciais na nossa ferramenta dedicada:
👉 Abrir Simulador Visual de Funções de Pesquisa ↗
1. PROCV (VLOOKUP): Pesquisa Vertical
O PROCV pesquisa um valor na primeira coluna à esquerda de uma tabela e devolve a informação correspondente de uma coluna especificada à direita:
=PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; [procurar_intervalo])
Passo 1: Descida Vertical Passo 2: Deslocamento Horizontal
┌─────────────────────────┐ ┌─────────────┬─────────────┬─────────────┐
│ Coluna 1 (Chave) │ │ Col. 1 │ Col. 2 │ Col. 3 │
├─────────────────────────┤ ├─────────────┼─────────────┼─────────────┤
│ REF-101 │ │ REF-101 │ Pastilhas │ 45,50 € │
│ REF-102 │ │ REF-102 │ Bateria │ 120,00 € │
│ REF-104 ◄─── [ENCONTRO]├──────────┼────────────►│ Filtro Ar │ 18,50 € │
│ REF-107 │ └─────────────┴─────────────┴─────────────┘
└─────────────────────────┘ ▲
(Índice Coluna = 2)
Exemplo Prático: Consulta Relacional de Preços
Considere uma tabela de catálogo registada no intervalo de células F1:H4:
| ◢ | F (Índice 1) | G (Índice 2) | H (Índice 3) |
|---|---|---|---|
| 1 | Código Artigo | Descrição | Preço Unitário (€) |
| 2 | PRD-01 |
Teclado Mecânico |
75,00 € |
| 3 | PRD-02 |
Rato Sem Fios |
25,00 € |
| 4 | PRD-03 |
Monitor 27" 4K |
220,00 € |
Na folha de encomendas, ao introduzir o código na célula A2, as colunas B e C preenchem-se automaticamente através do PROCV:
| ◢ | A | B | C |
|---|---|---|---|
| 1 | Código Introduzido | Descrição (PROCV Coluna 2) | Preço (PROCV Coluna 3) |
| 2 | PRD-02 |
=PROCV(A2; $F$2:$H$4; 2; FALSO) ➔ "Rato Sem Fios" |
=PROCV(A2; $F$2:$H$4; 3; FALSO) ➔ 25,00 € |
| 3 | PRD-01 |
=PROCV(A3; $F$2:$H$4; 2; FALSO) ➔ "Teclado Mecânico" |
=PROCV(A3; $F$2:$H$4; 3; FALSO) ➔ 75,00 € |
| 4 | PRD-03 |
=PROCV(A4; $F$2:$H$4; 2; FALSO) ➔ "Monitor 27\" 4K" |
=PROCV(A4; $F$2:$H$4; 3; FALSO) ➔ 220,00 € |
[!CAUTION] O Quarto Argumento (
FALSOou0): Utilize sempreFALSO(ou0) no quarto argumento para garantir correspondência exata. A omissão deste argumento ativa a pesquisa aproximada, originando dados incorretos caso a primeira coluna não esteja perfeitamente ordenada.
2. PROCH (HLOOKUP): Pesquisa Horizontal
Quando a tabela está estruturada horizontalmente, onde as chaves se encontram na primeira linha e os atributos nas linhas inferiores, utiliza-se o PROCH:
=PROCH(valor_procurado; matriz_tabela; núm_índice_linha; [procurar_intervalo])
Exemplo: Tabela Horizontal de Escalões
Considere a matriz A1:E2:
| ◢ | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 (Linha 1) | Qtd: 1 | Qtd: 3 | Qtd: 5 | Qtd: 8 | Qtd: 12 |
| 2 (Linha 2) | 0% |
3% |
5% |
8% |
12% |
Para uma quantidade de 8 unidades indicada em A5:
=PROCH(A5; $A$1:$E$2; 2; VERDADEIRO) // Devolve 8%
3. CORRESP (MATCH): Localizador de Posição Relativa
A função CORRESP não extrai valores de texto ou números: atua estritamente como um localizador de coordenadas, devolvendo a posição ordinal (índice 1, 2, 3…) onde o item se encontra num vetor:
=CORRESP(valor_procurado; vetor_unidimensional; [tipo_correspondência])
tipo_correspondência = 0: Exige correspondência exata.
Vetor de Referências:
Posição 1: [ REF-101 ]
Posição 2: [ REF-102 ]
Posição 3: [ REF-103 ]
Posição 4: [ REF-104 ] ◄─── =CORRESP("REF-104"; N7:N14; 0) devolve o número 4
4. ÍNDICE (INDEX): Extrator por Coordenadas
A função ÍNDICE recebe uma matriz e as respetivas coordenadas de linha e coluna, extraindo o conteúdo dessa célula exata:
=ÍNDICE(matriz; núm_linha; [núm_coluna])
=ÍNDICE(PreçosUnitários; 4) // Extrai o preço da 4.ª linha da lista
5. A Dupla Dinâmica: ÍNDICE + CORRESP
A combinação de ÍNDICE com CORRESP constitui a solução de pesquisa mais robusta e recomendada no Excel profissional:
┌──────────────────────────────────────────────┐
│ CORRESP("REF-104"; VetorChaves; 0) ➔ 4 │ (Descobre a linha)
└──────────────────────┬───────────────────────┘
│ passa a coordenada
▼
┌──────────────────────────────────────────────┐
│ ÍNDICE(VetorDescrições; 4) ➔ "Filtro de Ar" │ (Resgata o conteúdo)
└──────────────────────────────────────────────┘
Vantagens Técnicas Decisivas sobre o PROCV:
- Pesquisa à Esquerda sem Restrições: O
PROCVfalha quando a coluna de retorno está à esquerda da chave de busca. OÍNDICE + CORRESPfunciona em qualquer sentido (esquerda, direita, cima ou baixo). - Imunidade a Inserção de Novas Colunas: Se inserir uma nova coluna no meio da folha de cálculo, a fórmula
=PROCV(..., 3, ...)passa a apontar para o dado errado, enquanto oÍNDICE + CORRESPatualiza as referências automaticamente. - Eficiência de Recálculo: O
ÍNDICE + CORRESPapenas processa as duas colunas envolvidas, em vez de carregar a matriz retangular inteira na memória.
Exemplo de Pesquisa à Esquerda:
Resgatar o Armazém (Coluna A) a partir da Ref. Peça (Coluna B):
=ÍNDICE($A$7:$A$14; CORRESP(B7; $B$7:$B$14; 0))
6. Pesquisa Bidirecional 2D (Matriz Cruzada)
Quando os dados se distribuem numa grelha bidimensional com cabeçalhos em linha e em coluna, aninham-se dois CORRESP:
=ÍNDICE(GrelhaPrecos; CORRESP(Artigo; VetorArtigos; 0); CORRESP(Canal; VetorCanais; 0))
Coluna Canal (CORRESP)
▼
Balcão Online Grossista
Pastilhas [ 45,50 € ] [ 41,00 € ] [ 32,00 € ]
Linha Bateria [ 120,00 €] [ 108,00 €] [ 85,00 € ] ◄─── MIRA TELESCÓPICA
(CORRESP) ▲
INTERSEÇÃO
Síntese Comparativa
| Critério | PROCV (VLOOKUP) |
ÍNDICE + CORRESP |
|---|---|---|
| Direção da Pesquisa | Apenas da esquerda para a direita | Qualquer direção (esquerda/direita) |
| Inserção de Novas Colunas | Quebra a fórmula (índice numérico estático) | Imune (referências dinâmicas) |
| Consumo de Memória | Elevado (matriz retangular completa) | Reduzido (apenas vetores ativos) |
| Pesquisa Bidirecional (2D) | Complexo | Nativo e intuitivo |
Na Aula 08, concluímos o plano de formação com as Funções de Base de Dados no Excel, aprendendo a manipular listas estruturadas com zonas de critérios dinâmicas (BDSOMA, BDMÉDIA, BDCONTAR, BDMÁX, BDMÍN e BDOBTER)!