segunda-feira, 25 de fevereiro de 2013

Dr. Planilha: Como pesquisar em várias planilhas usando a função INDIRETO() do #Excel

Nosso leitor José Francisco deixou uma pergunta há algumas semanas que achei bastante interessante:
"Ola Dr. Planilha boa tarde, estou com uma duvida na planilha, uso a formula abaixo para poder buscar valores em outra planilha.
=SE(ÉERRO(SE(ÉCÉL.VAZIA(G47);"";PROC(G47;[Lista_Produtos.xls]Vendas_2013!Cod_Mat;[Lista_Produtos.xls]Vendas_2013!FEVEREIRO)));""; SE(ÉCÉL.VAZIA(G47);"";PROC(G47;[Lista_Produtos.xls]Vendas_2013!Cod_Mat;[Lista_Produtos.xls]Vendas_2013!FEVEREIRO)))
Pergunta: Como posso substituir a palavra “FEVEREIRO” constante na formula, por uma célula com o texto “FEVEREIRO” em uma nova planilha e a formula continuar funcionando, sendo que a palavra “FEVEREIRO” e referencia a uma coluna renomeada, seria mais ou menos concatenar uma formula com um texto?"
Em resumo, o que o José Francisco precisa é uma função que possibilite a ele informar em uma célula o nome da planilha onde ele quer realizar a pesquisa, ou da qual ele deseja obter algum valor, considerando que em sua pasta do Excel há várias planilhas. Vejamos como isso poderia ser resolvido.

Como exemplo podemos pensar no seguinte problema, temos uma pasta do Excel que contém uma planilha para cada mês do ano, e uma planilha que é usada para realizar consultas. Em nosso exemplo, a planilha de consultas tem apenas uma consulta onde nós informaremos o mês e o dia e o Excel nos retornará o valor deste mês/dia.

Iniciaremos criando a planilha de consultas, conforme figura abaixo:

Observe que adicionei apenas duas planilhas para os meses, Fevereiro e Março, pois não precisaremos em nosso exemplo dos 12 meses do ano.

Em seguida preencheremos algumas células das colunas A e B das planilhas Fevereiro e Março, que serão usadas para a pesquisa na planilha de consulta (Plan1).

As imagens acima mostram preenchidas as linhas apenas até o dia 4, no entanto, as planilhas foram preenchidas até o último dia do mês, ou seja, Fevereiro com 29 dias e Março com 31 dias. Essas duas planilhas do exemplo poderiam representar uma tabela de total de vendas, ou de despesas, contabilizadas em um dia do mês.

Agora vamos à função INDIRETO() propriamente dita. Antes de mais nada, precisamos entender o que ela faz:

Definição da função segundo Ajuda do Excel:
Retorna a referência especificada por uma cadeia de texto. As referências são imediatamente avaliadas para exibir seu conteúdo. Use INDIRETO quando quiser mudar a referência a uma célula em uma fórmula sem mudar a própria fórmula.

Para compreender melhor essa definição, temos que saber o que significa o termo “referência” para o Excel. Uma referência é basicamente um “endereço”, ou seja, quando falamos A1, isto é uma referência. Outros exemplos: $C$10, A1:B2, A1:A1000, ‘Plan2!A1:B1’, etc.

Trocando tudo isso em miúdos, significa que podemos usar a função INDIRETO() para retornar de forma “indireta” qualquer referência de qualquer planilha de uma pasta do Excel. Significa dizer que podemos fazer o seguinte:

1. Preencha a célula A1 com o texto D1
2. Preencha a célula D1 com seu nome
3. Preencha a célula B1 com a fórmula: =INDIRETO(A1)
4. Resultado: na célula B1 será apresentado seu nome

Observe que se nomearmos intervalos, ou uma única célula, poderemos usar esse nome na função INDIRETO(), ao invés de nomes como D1, C1, V100, A1:B1.

Muito bem, considerando que a explicação acima tenha sido suficiente para sua compreensão do funcionamento da função INDIRETO(), vejamos como ficaria a função para nosso exemplo, preenchendo a célula B3 da Plan1:

=PROCV($B$2;INDIRETO(B1&"!A1:B999");2;)
Isolando a função INDIRETO() da fórmula acima:

INDIRETO(B1&"!A1:B999")

Substituindo o conteúdo da função pelos valores das células, ficaria da seguinte forma:

INDIRETO(‘Março’&"!A1:B999")

O que significa dizer que a função INDIRETO() irá retornar o intervalo A1:B999 da planilha “Março”. E por fim, significa também dizer que estamos aplicando a função PROCV nesta mesma referência. Se trocarmos o valor de B1 para Fevereiro, a função PROCV será aplicada na referência ‘Fevereiro’!A1:B999.

Quer ver isso funcionando? Baixe o exemplo abaixo disponível no formato Excel 2003 e Excel 2007/2010.

Excel 2007/2010

Envie suas dúvidas pelos comentários.

Um grande abraço do Dr. Planilha!


7 comentários:

  1. ola dr. planilha boa tarde, primeiramente obrigado pela solução apresentada, pois consegui resover da seguinte maneira,
    a formula ficou assim: =SE(ÉERRO(SE(ÉCÉL.VAZIA(G24);"";PROC(G24;[Lista_Produtos.xls]Vendas_2013!Cod_Mat;INDIRETO($R$4))));""; SE(ÉCÉL.VAZIA(G24);"";PROC(G24;[Lista_Produtos.xls]Vendas_2013!Cod_Mat;INDIRETO($R$4)))), sendo que “$R$4” é uma célula concatenada ([Lista_Produtos.xls]Vendas_2013!MARÇO
    ) com o texto que preciso

    ResponderExcluir
    Respostas
    1. Olá José Francisco!

      Fico feliz que tenha conseguido resolver o problema.

      Abraços do Dr. Planilha!

      Excluir
  2. Boa tarde!

    Preciso utilizar a fórmula indireto porém o nome de algumas de minhas planilhas são "compostos", por exemplo "Gerência de Manutenção", para esses casos a fórmula não funciona, saberia como resolver?

    ResponderExcluir
    Respostas
    1. Muito fácil, amigo, é só usar Underline no nome da planilha e no local da busca.
      Ficaria assim:
      Gerência_de_Manutenção

      Excluir
  3. Existe alguma fórmula que funcionei igual a função indireto, porém sem a necessidade de ter que abrir as planilhas referenciadas?
    Quando utilizamos a função indireto referenciando células de outras planilhas, as mesmas necessitam estar aberta para atualizar o valor na planilha em que foi utilizada a função indireto.
    Outro problema é que quando abre uma planilha que possui a função indireto referenciando célula de outra planilha. Ela indica erro casa a planilha de referencia não seja aberta.
    Existe alguma função que funcione com as planilhas sem a necessidade de abrir a planilha de referência?

    ResponderExcluir
  4. Bom dia, tentei utilizar a formula, mas não consegui aplicá-la corretamente, também não sei se ela é exatamente o que preciso. Tenho uma planilha com várias abas, cada aba possui o nome de um estabelecimento, dentro das planilhas tenho as colunas: codigo, descrição, mes/ano(de jan/2008 a jun/2013 um mês por coluna).
    Porem eu preciso de uma planilha onde eu possa visualizar os dados de todos os estabelecimentos por mês, copiando todas as colunas de um mês/ano "X" de cada estabelecimento.
    ou seja, as colunas seriam código, descrição, estabelecimento1, estabelecimento2, estabelecimento3 e assim por diante, com dados de um mês.

    http://www.mediafire.com/view/g1n7afneqveyz69/exemplo.xlsx

    ResponderExcluir
  5. Eu tambem tive esse problema, pra resolvê-lo usei "_" (underline) entre os espaços como por exemplo: "gerência_de_manutenção".
    Espero ter te ajudado.
    Abç

    ResponderExcluir

Use esse espaço para enviar suas dúvidas ou fazer críticas e sugestões. Todos os comentários serão postados após passarem pela moderação do Dr. Planilha para que seja mantido o alto nível do conteúdo do blog.

Related Posts Plugin for WordPress, Blogger...