É um prazer estar com todos vocês. Esta é nossa primeira publicação juntos e espero que gostem. A proposta aqui e trazer novas visões para o uso do Excel.

Para ver mais posts sobre Excel e aprender centenas de tutoriais, dicas, planilhas para download, etc. confira o site do pessoal do Aprender Excel. Ahhh, e curta aqui a página deles no Facebook pra ficar por dentro das últimas novidades.

Normalmente planilhas são projetadas para atender necessidades especificas, um exemplo disso são as planilhas de controle financeiro pessoal, inclusive para quem quiser dar uma "fuçada" tem uma muito boa aqui na seção de Excel, porém ao tirar uma amostragem de uso desta planilha por pessoas diferentes, percebi que tinha muita coisa ou a falta de certas coisas que os usuários necessitavam em seu uso pessoal. A planilha continua sendo ótima, mas como sua lógica funciona para um todo, naturalmente não atende demandas especificas. Então o que fazer?

Partindo desta avaliação resolvemos adotar uma abordagem mais ampla, onde vocês amigos vão ditar o caminho do conhecimento. Para esse primeiro encontro eu trouxe uma planilha muito simples para controle do tão famigerado cartão de crédito, vilão nacional e autor da maioria das contas no vermelho.

Este será portanto nosso primeiro trabalho: Uma planilha simples com três abas, uma para inserção de dados de compras no cartão (Lembrando que no caso de crédito normalmente pagaremos no mês seguinte de acordo com a data de compra), uma para acompanhamento do gráfico (trazendo 2 exemplos: um de consumo por bandeira do cartão e o outro de consumo mensal de cada bandeira) e a aba de dados onde iremos inserir quais as bandeiras dos cartões que trabalhamos.

Esta aba de Dados, sem dúvida será nosso pontapé inicial. Ao abrir você notara inicialmente apenas uma coluna com o título "Cartões" e alguns exemplos de bandeiras. É ai que começa nosso desafio. Eu hoje consigo usar apenas um cartão de bandeira visa, porém minha mulher usa 2, um Master e um Visa. Para mim a planilha bastaria com uma bandeira apenas ou até mesmo nem precisaria desta especificação, já para minha esposa seriam necessários 2 espaços.
Aba "Dados" da tabela contendo os nomes de bandeiras conhecidas de cartão de crédito.

Esse é nosso primeiro impasse. Temos uma lista que é feita através da validação de dados, cuja referência é a Coluna A da aba Dados. Porém esta lista pode variar e se deixarmos muitos espaços em branco na hora de nomear o intervalo Cartões, ficaremos com espaços em branco na lista também.

Ao usar essa validação na aba Cartões no item Bandeira, quando clicarmos aparecerá em branco e teremos que rolar a barra para cima para depois escolher o valor que queremos (exemplo na planilha Aba Cartões Coluna a Item Bandeira).
Aba "Cartões". Tabela de valores utilizados nos cartões.

Visto este problema como proceder? Bem, para nossa apreciação vamos trabalhar com uma lista de validação dinâmica. Iniciaremos por determinar qual espaço vamos contabilizar, na aba Dados. Nossa lista começa no campo A2 e vai até o A10 neste exemplo, porém podemos necessitar de novos nomes ou de tirar nomes que não vamos utilizar. Ao fazermos isso, nossa lista da Aba Cartões não fica com espaço em branco. Isso se dá utilizando uma fórmula na validação de dados ao invés de um intervalo nomeado.

Se você for na aba Fórmulas (Excel 2007), Gerenciador de nomes, e escolher cartões você verá no item "Refere-se a" esta formula:

DESLOC(Dados!$A$2;;;CONT.VALORES(Dados!$A:$A)-1).

Esta é a mágica! Ao dizermos na tabela Cartões que o campo Bandeira será validado por uma lista (Excel 2007: Dados/ Validação de Dados/ Lista/) e colocarmos o nome Cartões, você estará dizendo ao Excel que pegue a referência do nome que você definiu previamente.

Como isso funciona? A fórmula DESLOC em Excel é uma formula de referência, segundo definição do endereço: Office.microsoft.com

Descrição:

Retorna uma referência para um intervalo, que é um número especificado de linhas e colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e de colunas a serem retornadas.

Sintaxe:

DESLOC(ref, lins, cols, [altura], [largura])

Sendo assim, se avaliarmos como nossa fórmula repassa essa referência ao Excel veremos:

DESLOC(Dados!$A$2;;;CONT.VALORES(Dados!$A:$A)-1).

De acordo com a síntese ref= Aba Dados célula A2, veja que não deslocamos nem linhas nem colunas. Depois vem a outra mágica. Usamos a fórmula CONT.VALORES pegando toda a coluna A e subtraindo -1 que é nosso título da coluna. A fórmula CONT.VALORES, conta dentro da coluna quantas linhas tem valores escritos, ou seja não estão em branco e retorna ao Desloc 10-1 = 9. O DESLOC, que por sua vez, armazena no nome a referência da célula A2 até 9 de altura (célula A10).

Se apagarmos um item da lista ou acrescentarmos 1, o valor de CONT.VALORES será alterado. De modo que a referência "cartões" usada na validação de dados através de lista, passa a ser dinâmica mostrando apenas os valores inseridos na coluna.

Gráfico dos dados de gastos com o cartão de crédito.

Isso é só o começo desta ferramenta magnífica. Espero que tenhamos muitas experiências juntos e que possamos construir uma base de conhecimento para pesquisas posteriores. Não esqueçam de deixar seus comentários, sugestões e pedidos do que vocês querem ver nos nossos próximos encontros. Abraços e até a próxima!

Veja também o segundo artigo: Planilha de controle de gastos no cartão de crédito e a terceira parte desta planilha que mostra como economizar o cartão de crédito usando excel.

E para finalizar, não esqueça de curtir a página do Aprender Excel no Facebook e dar uma visitadinha no site deles para aprender novas funções e usabilidades do editor de planilhas da Microsoft.