Pesquisando numa tabela com as funções Match (Corresp) e Offset (Desloc)

As funções Match e Offset, Corresp e Desloc respectivamente em Português, são muito úteis para a pesquisa de dados numa tabela de dados, especialmente quando estamos lidando com grandes volumes de dados.

Para a elaboração dos Dashboards são ainda mais úteis, visto que permitem extrair fatias de dados de uma tabela com base em critérios específicos. Uma vez fatiada a tabela, basta posicionar os dados nos locais desejados.

Apenas para efeitos didáticos, vamos imaginar uma tabela contendo a relação de alunos e as várias disciplinas que estão cursando, com suas respectivas médias para cada matéria.

Para a tabela acima, as informações a serem retornadas serão: 1) Média do aluno numa respectiva matéria; 2) Média geral do aluno; 3) Média geral da matéria. Os critérios utilizados serão: 1) Nome do aluno; 2) Disciplina.

Numa segunda tabela, na área destacada em cinza (Aluno e Matéria), criei duas listas de valores, com base na coluna A (relação de alunos) e na linha 1 (relação de matérias) apenas para facilitar a seleção de valores como critérios de pesquisa.

Na coluna “Nota” incluí a seguinte fórmula:

=DESLOC(A1;CORRESP(A12;A2:A7;0);CORRESP(B12;B1:G1;0))

Explicando:

• CORRESP(A12;A2:A7;0) retorna o número da linha correspondente na coluna Aluno ao valor inserido na célula A12, ou seja, linha 5
• CORRESP(B12;B1:G1;0) retorna o número da coluna correspondente na linha 1 ao valor inserido na célula B12, ou seja, coluna G
• A função Desloc retorna o valor inserido na célula identificada pelas duas fórmulas Corresp, ou seja, o valor inserido na posição G5. Temos então a média do aluno.

 Na coluna “Média geral do aluno” incluí a seguinte fórmula:

=MÉDIA(DESLOC(A1;CORRESP(A12;A2:A7;0);1;1;6))

Explicando:

• A1 fixa o início da pesquisa na célula A1
• CORRESP(A12;A2:A7;0) retorna o número da linha correspondente na coluna Aluno ao valor inserido na célula A12, ou seja, linha 5
• 1 fixa que o início da pesquisa ocorrerá na primeira coluna à direita de A1
• 1 fixa que a pesquisa será realizada apenas em uma linha, no caso a linha 5
• 6 fixa em seis colunas a quantidade de colunas a ser pesquisada
• A fórmula média calcula a média de valores do intervalo A5:G5

 Na coluna “Média geral da matéria” incluí a seguinte fórmula:

=MÉDIA(DESLOC(A1;1;CORRESP(B12;B1:G1;0);6;1))

Explicando:

• A1 fixa o início da pesquisa na célula A1
• 1 fixa que o início da pesquisa ocorrerá na primeira linha abaixo de A1
• CORRESP(B12;B1:G1;0) retorna o número da coluna correspondente na linha A1 ao valor inserido na célula B12, ou seja, coluna G
• 6 fixa em seis linhas a quantidade de linhas a ser pesquisada
• 1 fixa em uma coluna a quantidade de colunas a ser pesquisada
• A fórmula média calcula a média de valores do intervalo G2:G7

Fica claro que as duas fórmulas são complementares e que juntas podem facilitar tremendamente a montagem dos Dashboards, inclusive aqueles que requerem atualização dinâmica por meio de critérios de pesquisa.