Neste artigo você aprenderá como retornar vários resultados no Excel.
Como se fosse PROCV vários resultados ou índice corresp com vários resultados no Excel.
Neste artigo você aprenderá como realizar uma consulta e retornar vários resultados no Excel de 3 formas diferentes.
Forma 1 – Retornar Vários Resultados com Função Filtro Excel
A forma mais fácil de retornar dados é com o uso da função Filtro.
Porém a função Filtro só está disponível no Microsoft 365, ou seja Excel 365.
Caso não tenha esta função veja a forma 2 ou 3 de retornar vários dados no Excel.
Temos o seguinte caso acima, aonde temos uma lista de abastecimentos e queremos retornar o odômetro à partir da placa no topo.
Para isso podemos utilizar a função Filtro conforme abaixo:
=FILTRO(tAbastecimentos[Odômetro];(tAbastecimentos[Placa]=H7)).
Na função filtro passamos como primeiro parâmetro o que será retornado, e no parâmetro de filtro passamos a placa igual a H7, a placa selecionada no topo.
Então o retorno é automático à partir desta fórmula.
Como pode notar acima nós temos então todos os resultados de hodômetro para aquela placa.
Esta é a forma mais fácil e mais maleável de realizar filtros e retornar vários dados através de fórmulas no Excel.
Mas novamente, caso não tenha a versão 365 do Excel não vai funcionar, então veja mais duas formas.
Forma 2 – Consultar Vários Excel Usando Agregar e Indireto
Uma outra forma de consultar vários resultados no Excel é utilizando a função Agregar e Indireto para isto.
A função Agregar é uma função que permite realizar operações matemáticas como a função Subtotal, mas também tem formas matriciais de consulta.
Veja mais sobre a função Agregar.
Assim, usamos a seguinte fórmula nesta planilha:
=AGREGAR(15;3;1/(tAbastecimentos3[Placa]=$H$7)*LIN(tAbastecimentos3[Placa]);LIN()-LIN($H$9))
Aonde temos então as seguintes parametrizações:
- 15: Na primeira parte temos a passagem do parâmetro Mínimo, ou seja será realizada a operação semelhante a função mínimo do Excel, porém como se fosse a subtotal.
- 3: Este parâmetro é para que a fórmula desconsidere erros, nulos e células vazias.
- 1/(tAbastecimentos3[Placa]=$H$7)*LIN(tAbastecimentos3[Placa]) : Aqui temos a parte que se refere ao cálculo de 1 dividido pelo valor que retorna caso encontre a placa, se encontrar retorna 1, senão 0, temos então nesta parte 1/0 ou 1/1, o primeiro caso retorna erro, o segundo 1. Em seguida multiplicamos por Lin, que é o número da linha pela, retornando então a posição dela na lista.
- LIN()-LIN($H$9) : Número do item a sere retornado, número 1, 2, 3.
Com isso temos então o retorno do número da primeira menor linha com aquela placa, segunda, terceira, etc.
Por fim, como pode notar acima, usamos a função INDIRETO e concatenamos usando “F” & com a fórmula para que retorne então a informação daquela linha
E a função SEERRO é utilizada para retornar vazio caso ocorra um erro na consulta, dado que irá retornar erro pela divisão de 1/0 quando não encontrar.
Copie esta fórmula para quantos retornos desejar.
Forma 3 – Retornar Vários Resultados no Excel com as Funções Agregar e Índice do Excel
Como vimos acima, com a função Agregar e a função Indireto conseguimos retornar as informações à partir de uma célula do intervalo.
Podemos usar uma adaptação desta fórmula para retornar o número da linha e podemos retornar vários dados usando a função Índice do Excel.
Veja a fórmula de exemplo:
=AGREGAR(15;3;1/(tAbastecimentos34[Placa]=$I$7)*LIN(tAbastecimentos34[Placa]);LIN()-LIN($I$9))-LIN($H$9)
No exemplo nós inserimos uma coluna auxiliar, na qual colocamos a fórmula acima.
Ela realiza um cálculo muito semelhante ao anterior, apenas retornando qual o número da linha na matriz de consulta, no caso a tabela de origem dos dados.
Com isso nós iremos utilizar a função Indireto, passando a ela no parâmetro de número da linha da matriz o resultado desta coluna auxiliar.
Fazemos então a
Usamos então a função seguinte:
=ÍNDICE(tAbastecimentos34;$H10;CORRESP(I$9;tAbastecimentos34[#Cabeçalhos];0))
Veja que ela utiliza como parâmetros a tabela de abastecimentos para aonde consultar, e na linha utiliza H10, ou seja, a célula com o número da linha.
E para a coluna é utilizado a função CORRESP, consultando o número da coluna acima do dado, com a informação que desejamos retornar.
E com isso temos então o retorno da informação conforme a linha e a coluna desejada para vários resultados, como um PROCV com vários resultados ou um índice corresp com vários resultados no Excel.
Download
Realize o download da planilha deste artigo no botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
