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!
Related Posts Plugin for WordPress, Blogger...