Funções de Pesquisa no Excel com referências de células?

Normalmente quando efetuamos pesquisas no Excel contamos com um identificador único, um valor, para localizar e de seguida devolver um único resultado correspondente. Mas em cenários mais complexos podemos ter vários valores a localizar e vários valores a devolver. Quando os valores a devolver representam um intervalo de colunas ou linhas, o processo pode ser ainda mais complexo, pois temos de selecionar a “tabela” ou intervalo que contem essas colunas.

Se pretendemos que esse intervalo seja dinâmico temos também de “pesquisar” as colunas (ou linhas) para obter o resultado.

Este tipo de pesquisa baseia-se numa localização por referência de célula, onde o valor que procuramos é efetivamente um valor (texto ou outro valor qualquer) mas o resultado devolvido é uma referência de célula, que permite obter a localização do item.

A vantagem neste processo é que não temos de pesquisar manualmente as colunas, e a lógica é simples, pois na verdade estamos a usar um método de referência de células, com um intervalo, por exemplo:

• Intervalo para selecionar colunas -> C2:N2

Este intervalo pode ser substituído por uma função, colocada em cada “extremo” do intervalo, que realiza a pesquisa por nós?

Para realizarmos este tipo de pesquisa podemos usar várias funções de pesquisa, onde habitualmente as funções PROCV [VLOOKUP], PROCX [XLOOKUP], CORRESP [MATCH], CORRESPX [XMATCH] ou ÍNDICE [INDEX] são as mais habituais.

Ficando algo deste género:

• Intervalo para selecionar colunas, por localização da referencia de células -> ÍNDICE:ÍNDICE

No exemplo vou mostrar-te o cenário com a função XLOOKUP, mas também com a função ÍNDICE para perceberes que o que interessa é a lógica e não a técnica!

No cenário temos uma tabela de dupla entrada, com Marcas e Meses, típica para o uso da função ÍNDICE, pois temos uma pesquisa bidimensional.

E pretendemos pesquisar uma marca, e restringir as colunas da pesquisa a um intervalo entre o mês inicial e o mês final. E em cada cenário em que mudamos a Marca, o Mês de Inicio ou Fim, não temos de pesquisar manualmente as colunas (relativas ao mês).

O objetivo é obter o valor total, uma vez que o resultado pode devolver vários valores, e deve ser agregado de alguma forma, neste caso vamos optar pela soma de unidades vendidas.

Aplicar a lógica

Se pensarmos na função PROCX [XLOOKUP]normal obtemos a seguinte formula, no caso de pretendermos pesquisar uma marca, para um mês em específico.

Ou mesmo usando a função PROCV [VLOOKUP] com ajuda da função CORRESP[MATCH]

Mas o pretendido é ter um mês de início e um mês de fim para obter uma tabela ou matriz reduzida em termos de análise. Assim o mês inicial tem de ser dinâmico e pesquisável assim como o mês final.

Para ambos os casos vamos usar a função PROCX [XLOOKUP].

1. Primeira expressão PROCX: Permite localizar a marca pretendida

=PROCX(Q3;B3:B54;”matriz a devolver”)

• Segunda expressão PROCX: Permite localizar o mês inicial.

=PROCX(Q3;B3:B54;PROCX(Q4;C2:N2;C3:N54)

• Terceira expressão PROCX: Permite localizar o mês final.

=PROCX(Q3;B3:B54;PROCX(Q4;C2:N2;C3:N54):PROCX(Q5;C2:N2;C3:N54))

Nota: o que permite usar as funções como referências de células é o “:”

Como podemos verificar a função devolve um Array Dinâmico de valores, correspondente a 3 meses. Para obtermos o valor total ou outro indicador, basta agregar. No cenário vamos utilizar a SOMA [SUM].

=SOMA(PROCX(Q3;B3:B54;PROCX(Q4;C2:N2;C3:N54):PROCX(Q5;C2:N2;C3:N54)))

Em alternativa podemos usar outras funções, como por exemplo a função ÍNDICE, contudo com esta expressão temos de recorrer a mais funções como a função CORRESP [MATCH] OU CORRESPX [XMATC] e a expressão em si também é maior. Realizando a lógica por partes…

1. =ÍNDICE(C3:N54;num_linha;num_coluna) -> para indicar a matriz
2. =ÍNDICE(C3:N54;CORRESPX(Q3;B3:B54;0);num_coluna) -> para localizar a linha, que representa a marca.
3. =ÍNDICE(C3:N54;CORRESPX(Q3;B3:B54;0);CORRESPX(Q4;C2:N2;0)) -> para obter agora o mês inicial.

A Expressão é repetida para a segunda parte da expressão (ÍNDICE:ÍNDICE) mas para detetar o mês final.

• =ÍNDICE(C3:N54;CORRESPX(Q3;B3:B54;0);CORRESPX(Q4;C2:N2;0)):ÍNDICE(C3:N54;CORRESPX(Q3;B3:B54;0);CORRESPX(Q5;C2:N2;0))

A expressão final será então a seguinte:

=SOMA(ÍNDICE(C3:N54;CORRESPX(Q3;B3:B54;0);CORRESPX(Q4;C2:N2;0)):ÍNDICE(C3:N54;CORRESPX(Q3;B3:B54;0);CORRESPX(Q5;C2:N2;0)))

Ficheiros do Artigo