Imagine que você tenha várias planilhas idênticas em uma pasta de trabalho que contém tabelas idênticas (como registros de frequência de cada mês em uma planilha separada). Agora você deseja criar um painel que mostra a participação total no mês. Agora, ter os dados de cada mês no painel ao mesmo tempo não é uma boa opção. Queremos uma lista suspensa para selecionar o mês. Precisamos de uma fórmula VLOOKUP para olhar na planilha do mês escolhido.
Em palavras simples, precisamos de uma fórmula de pesquisa para consultar as planilhas de variáveis.
Como mostra o gif acima, usaremos as funções PROCV e INDIRETO juntas para pesquisar em várias planilhas, com base em seus nomes.
Fórmula genérica para consultar várias folhas
| =PROCV(lookup_value,INDIRETO(""&sheet_name_reference&"! lookup_table "), col_index, 0) |
Valor_procurado: Este é o valor que você está procurando na pesquisa tabela.
Sheet_name_reference: Esta é a referência da célula que contém o nome da planilha.
Tabela de pesquisa: Esta é a referência de tabela na qual você deseja procurar lookup_value. Pode ser um intervalo nomeado, tabela ou referência absoluta. Deve ser igual em todas as folhas.
Col_Index: Este é o número da coluna na tabela da qual você deseja recuperar o valor. Se você está familiarizado com a função PROCV, sabe o que é.
Então, vamos pegar um exemplo.
Exemplo: Recuperar Presença para o Mês Selecionado
Portanto, temos uma apostila que mantém a frequência dos meus alunos do Excel. Os dados de cada mês são salvos separadamente em planilhas diferentes. O nome da planilha é definido como o nome de meses. Por enquanto, tenho dados de três meses, mas haverá mais, é claro.
Quero criar um relatório que mostre o comparecimento do mês selecionado. O mês pode ser selecionado em uma lista suspensa. A fórmula deve ser capaz de pesquisar a partir dessa planilha automaticamente, mesmo se uma nova planilha for adicionada a ela.
Portanto, preparamos a tabela acima. O Cell G3 criamos uma lista suspensa usando uma lista suspensa.
O valor da pesquisa está em B4. O sheet_name_reference está em G3. A tabela de pesquisa em todas as planilhas é B3: AZ100. Gostaríamos de recuperar o valor de 2 colunas. Então, escrevemos esta fórmula em C4 e a arrastamos para baixo. Da mesma forma, para valores ausentes, alteramos o índice da coluna.
| =PROCV(B4,INDIRETO("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Como funciona?
A fórmula é resolvida de dentro para fora. Primeiro vamos ver como isso é resolvido passo a passo.
Assumindo que G3 contém Jan.
| =PROCV(B4,INDIRETO("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = PROCV (B4, INDIRETO ("Janeiro! $ B $ 3: $ Az $ 100"),2,0) |
| = PROCV (B4,Janeiro! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Portanto, primeiro a declaração "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" é resolvida em uma string "Janeiro! $ B $ 3: $ Az $ 100". Em seguida, a função INDIRETO converte esta string em referência real. E finalmente obtivemos a fórmula VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). E isso finalmente é resolvido para 7. Agora, se você alterar o nome da planilha no G3, o texto de referência do valor será alterado. E é assim que você consulta as planilhas variáveis no Excel.
Espero que tenha sido útil para você. Se você tiver alguma dúvida ou quiser realizar uma pesquisa diferente, me avise na seção de comentários abaixo. Ficarei feliz em ajudá-lo. Até então, mantenha a excelência.
Use PROCV de duas ou mais tabelas de pesquisa | Para pesquisar em várias tabelas, podemos usar uma abordagem IFERROR. A consulta a partir de várias tabelas considera o erro como uma opção para a próxima tabela. Outro método pode ser uma abordagem If.
Como fazer pesquisa sensível a maiúsculas e minúsculas no Excel | a função VLOOKUP do Excel não diferencia maiúsculas de minúsculas e retornará o primeiro valor correspondente da lista. INDEX-MATCH não é exceção, mas pode ser modificado para diferenciar maiúsculas de minúsculas. Vamos ver como …
Pesquisar texto que aparece com frequência com critérios no Excel | A pesquisa aparece com mais frequência em texto em um intervalo em que usamos INDEX-MATCH com a função MODE. Aqui está o método.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar a função VLOOKUP do Excel| Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar o Excel Função CONT.SE| Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.