Busca de múltiplos resultados

A busca de múltiplos resultados a partir de um valor de referencia, é um problema requisitado entre os aficionados em Excel, visto que possui grande aplicabilidade em diversas situações, tais como, organizar filmes por categoria, buscar produtos de uma linha de negócio, ou até mesmo agrupar cidades de acordo com a Unidade Federativa (UF).

A técnica a ser aplicada nos exigirá uma intimidade com funções combinadas, fórmulas matriciais e um pouco de criatividade. 

Para explicar esta técnica, o Acadêmicos do Excel criou um case, em que a partir de uma UF, agruparemos as cidades associadas a esta.

1. Busca de múltiplos resultados

A seguir o esquema mostra a base de dados e a busca das cidades por UF, com a aplicação da técnica na célula D2.

busca_cidadeApós a construção da fórmula digite, Crtl+Shift+Enter, visto que é uma fórmula matricial, e na sequência arraste para os demais campos, como ilustra o esquema acima.

2. Processo de criação e conceitos

Para compreendermos a fórmula acima, ela será quebrada em partes, e explicada de uma forma sequencial, pois cada função possui um importante papel.

2.1 ÍNDICE


A função ÍNDICE retorna a partir de uma matriz, e a posição da linha e da coluna, o respectivo elemento, sendo assim, a função possui a seguinte estrutura:

=ÍNDICE(matriz; núm_linha; [núm_coluna])

Observe o esquema a seguir, referente a matriz, número da linha e coluna.

cidade_indicePara entendermos um pouco melhor, vamos verificar alguns resultados.

=ÍNDICE($A$2:$B$11;1;2), é referente a São Paulo, ou seja, é o elemento correspondente a linha 1 e coluna 2 da matriz $A$2:$B$11

=ÍNDICE($A$2:$B$11;8;2) é igual a Ouro Preto, ou seja, é o elemento correspondente a linha 8 e coluna 2 da matriz $A$2:$B$11

2.2 SE


A função SE é aplicada para delimitarmos uma condição,  sendo assim, a estrutura da função SE é a seguinte:

=SE(teste_lógico; [valor_se_verdadeiro]; [valor_se_falso])

SE_cidades

O esquema acima, valida se o intervalo é igual a Unidade Federativa (UF) buscada e retorna os valores do Intervalo subtraindo-se 1 (linha do cabeçalho), sendo estes valores correspondentes a matriz, {1;2;3;4;5;6;7;8;9;10}, caso a condição seja VERDADEIRO, serão exibidos os valores associados, ao contrário mostrará FALSO.

Para entendermos um pouco melhor, vamos verificar a lógica expandida dos resultados das Unidades Federativas: SP, MG e RJ.

=SE($A$2:$A$11=”SP”;{1;2;3;4;5;6;7;8;9;10}) = {1;2;FALSO;FALSO;5;FALSO;FALSO;FALSO;9;FALSO}

=SE($A$2:$A$11=”MG”;{1;2;3;4;5;6;7;8;9;10}) = {FALSO;FALSO;3;4;FALSO;FALSO;FALSO;8;FALSO;FALSO}

=SE($A$2:$A$11=”RJ”;{1;2;3;4;5;6;7;8;9;10}) = {FALSO;FALSO;FALSO;FALSO;FALSO;6;7;FALSO;FALSO;10}

Conheça um pouco mais sobre a função SE no post, Aprenda a utilizar a função SE (IF).

Obs.: Perceba que na função SE a célula D$1 recebe somente  o “$” à direita, para fixar a movimentação na vertical (linhas), visto que a fórmula será “arrastada”  tanto na vertical como na horizontal.

2.3 MENOR


A função MENOR, retorna o menor valor “k” de uma matriz (1,  2, 3,…,k), sendo assim, a sua estrutura é a seguinte:

=MENOR(matriz; k)

Repare que o argumento, matriz, serão as matrizes geradas pela condição SE, e o valor k, as posições dos menores valores.

MENOR_cidadeObs.: A função LIN(A1) é utilizada para tornar o processo de busca dos k menores valores dinâmicos, ou seja LIN(A1) =1, LIN(A2)=2, LIN(A3)=3 e assim por diante.

2.4 SEERRO


A função SEERRO é utilizada para tratamento de erro, sendo a sua estrutura a seguinte:

=SEERRO(valor; valor_se_erro)

No nosso case, ela é aplicada para exibir vazio (“”) no caso da fórmula ÍNDICE(…) retornar um erro, como mostra o esquema logo abaixo.

cidade_erro

Obs.: Um erro do tipo #NÚM! surgirá caso “arrastarmos” a fórmula para um número de linhas maior do que o número de cidades disponíveis para uma referida UF, por exemplo, em MG somente temos 3 cidades, caso o número de linhas exceda este valor, retornará um erro.

Para aprender e aprofundar em funções para tratamento de erro leia o post, Como tratar Erros em Fórmulas.

3. Síntese

Com o detalhamento de cada função, e tendo em vista que a função ÍNDICE retorna um elemento de uma dada matriz, tomando-se como argumentos, a posição da linha e da coluna, temos o seguinte processo:

    • O argumento da posição da coluna é 2 (nome da cidade), e o da linha é submetida a uma condição com a função SE;
    • A condição SE gera uma matriz que alimenta a função MENOR, que retorna a posição da menor linha;
    • A função SEERRO engloba a fórmula ÍNDICE (…), para tratar eventuais erros.

Disponibilizo logo abaixo o link do exemplo para download.

downloadEspero que tenham gostado, bons estudos!

Deixe uma resposta

%d blogueiros gostam disto: