Excel: Formatação Condicional com datas

Muitas pessoas perguntam nos meus cursos de Excel, como criar uma planilha com formatação condicional com datas, ou seja, quando uma data está se aproximando, o Excel deverá formatar a planilha com uma cor diferente, que servirá como um alerta para um pagamento, compromisso ou até receber bonificações ou comissões.

Como funciona?

Você faz uma planilha com os gastos do mês, por exemplo, aqui eu fiz uma beeem básica...

Selecione o intervalo das datas...Posicione o cursor na célula B2, clique e arraste até B11.

Depois disso, na faixa de Opções do Excel, clique em Formatação Condicional e vamos criar uma Nova Regra

Depois clique em "Usar uma fórmula para determinar quais células devem ser formatadas"

Na caixa "Edite valores em que esta fórmula é verdadeira" e digite a fórmula que compara a data da planilha com a data de hoje.

Essa fórmula diz o seguinte para o Excel:

=SE(B2>Hoje();verdadeiro)

Se a célula B2 tiver uma data maior que a data de hoje....

Clique no botão Formatar

e

vamos para a Formatação Condicional....

Na Guia Preenchimento, escolha uma cor para o fundo da célula, 

Aqui eu escolhi um verde para dizer que a data de pagamento ainda não chegou...

Clicando em OK, vemos que as datas que ainda não chegaram, ficaram formatadas em verde ..

Vamos novamente para a Formatação Condicional e depois clique em Gerenciar Regras 

Adicione uma Nova Regra e 

depois clique em "Usar uma fórmula para determinar quais células devem ser formatadas"

Na caixa "Edite valores em que esta fórmula é verdadeira" e digite a fórmula que compara a data da planilha com a data de hoje.

Essa fórmula diz o seguinte para o Excel:

=SE(B2=Hoje();verdadeiro)

Se a célula B2 tiver uma data igual a data de hoje....

Clique no botão Formatar

e

vamos novamente para a Formatação Condicional....

Na Guia Preenchimento, escolha uma cor para o fundo da célula, 

Dessa vez eu escolhi a cor laranja para dizer que a data de pagamento é hoje 

Adicione mais uma Nova Regra e 

depois clique em "Usar uma fórmula para determinar quais células devem ser formatadas"

Na caixa "Edite valores em que esta fórmula é verdadeira" e digite a fórmula que compara a data da planilha com a data de hoje.

Essa fórmula diz o seguinte para o Excel:

=SE(B2

Se a célula B2 tiver uma data menor do que a data de hoje....

Clique no botão Formatar

e

vamos novamente para a Formatação Condicional....

Na Guia Preenchimento, escolha uma cor para o fundo da célula 

Agora foi a cor Vermelha que eu escolhi para dizer que a data de pagamento já passou...

Podemos então visualizar as três regras de Formatação... 

Clicando em Ok, o Excel aplica a Formatação Condicional de acordo com a data das células

Se alterarmos qualquer data para a data de HOJE que é dia 04 de Outubro, automaticamente já formata em cor Laranja.

Gostou? Tem sugestões e opinião diferente? Deixe seu comentário.. Eu leio e respondo todos na medida do possível. Se você gosta do Blog Coizaradas, clique em Seguir e receba tudo em primeira mão. Abraços a todos!!

Read More

Controle de Contas no Excel

Essa postagem com o tutorial de controle de contas já postei a algum tempo atrás e quem não pode aproveitar na época e tem interesse em domar suas contas em 2013, pode fazer esse exercício e não mais se perder nas contas no ano novo.

Que tal começar a com um controle de contas e fugir do aperto no final? Todo final de ano é a mesma coisa. Os boletos de pagamentos acumulam na mesma proporção que os cabelos brancos insistem em aparecer...Então vamos criar um controle bem simples sem fórmulas difíceis e bem prático. Mesmo quem não tem muita afinidade com o Excel pode tentar... é bem fácil.

Pra começar, abra o programa e adicione treze planilhas. Uma para cada mês do ano e a última para a totalização dos valores. Para adicionar planilhas clique no botão Inserir Planilha.

Se alguma planilha ficar escondida, posicione o cursor no botão de expansão da barra de rolagem horizontal, quando o cursor se transformar em uma seta dupla, clique a arraste para a direita. Assim a área de visualização das abas de planilhas ficará maior.

Agora vamos renomear as planilhas. Dê dois clicks rápidos na aba da planilha e escreva um nome nela. Sugiro as iniciais de cada mês do ano.

E assim sucessivamente até a última. Essa deverá se chamar Total.
Agora vamos á digitação dos dados da planilha. Serão no total treze planilhas. Para não ter que digitar treze vezes a mesma coisa, ou selecionar, copiar e colar em todas, vamos a um truquezinho do Excel. Clique na aba Jan (a primeira), pressione a tecla Shift e mantenha-a pressionada, clique na aba Total (a última). Pronto! Agora todas estão selecionadas e o que for digitado, formatado, apagado ou movido na primeira planilha, é feito simultaneamente nas outras.

Digite os dados como estão aqui na figura abaixo. Observe as linhas e colunas pra não dar erro depois na fórmulas quando nos referenciarmos à elas.

Essa é a continuação dos dados, observe que começa na coluna E.

Depois da digitação pronta, vamos ás fórmulas...Primeiro vamos ao controle de proventos ou renda do João...
Clique na célula do sub-total e escreva a fórmula da soma: =soma(D3:D9) e depois pressione Enter. Vai aparecer 0 (zero) porque ainda não digitamos os valores de salários e outras fontes de renda.

Agora vamos aos cálculos da renda da Maria:

Clique na célula do sub-total e digite a fórmula: =soma(D12:D17) e depois pressione Enter. Aqui também vai aparecer 0 (zero) porque ainda não digitamos os valores de salários e outras fontes de renda.

Para saber quanto foi a renda familiar, outra fórmula e nem precisa usar a função Soma.

Clique na célula Total e digite a fórmula: =D10+D18. Essa formulazinha soma os valores da totalização da renda do casal.

Para formatar em moeda...

E clique no botão Formato de Número e Contabilização.
Agora vamos usar a função condicional SE para verificar se a renda foi maior que a despesa ou não. Clique na célula ao lado de Crédito e digite a fórmula.
Veja a fórmula na figura acima:
Que diz o seguinte: Se o H35 (o gasto) for menor que D19 (total da ganhos), então faça o D19 menos o H35, ( daí ele mostra o saldo positivo) senão exiba 0 (zero).

Agora a fórmula do Débito.

Veja a fórmula na figura acima que nos diz o seguinte:
Se o H35 (o gasto) for maior que D19 (total da ganhos), então faça o H35 menos o D19, ( daí ele mostra o saldo negativo) senão exiba 0 (zero).

Se for digitado algum valor, já são exibidos os resumos. Veja baixo...

Agora uma maneira do Excel nos alertar caso ocorra um débito nas contas.

Clique na célula ao lado da situação e digite a fórmula:

=se(C22>C23;"Você tem Créditos";"Você está em Débito").

Se o total de receita for menor que o total de despesas, então Você tem Créditos, senão Você está em Débito.

No final da colunas das despesas, também é necessário a totalização dos gastos. Clique na célula H35 e digite: =SOMA(H3:H34). Se você ainda não digitou nehum valor, vai aparecer 0 (zero).

Tá quase, tá quase..

Agora remova a seleção múltipla de planilhas.. clique em qualquer planilha que não seja a Jan e vamos à última planilha, a Total e digite a fórmula em H3:

=soma(Jan:Dez!H3).

Assim o Excel vai procurar todos os valores contidos em H3 de Jan a Dez. O sinal de (!) exclamação serve para separar o intervalo de nome das planilhas do endereço a ser pesquisado.

Depois de tanto suar a camisa, uma boa notícia.

Faça o primeiro cálculo e pressione Enter. Depois clique na célula de novo. Posicione o cursor no canto inferior direito da célula, quando ele se transformar em uma cruzinha, dê dois clicks rápidos. Toda a coluna é calculada automaticamente seguindo as referências relativas como base.

Quando chegar o momento de digitar os valores de Janeiro, vá até as células apropriadas de mercado, combustível, prestações e veja que todos serão totalizados na planilha final. Assim no final do ano você terá um relatório completo de todos os gastos do ano.

Mas cuidado, pode ser assustador. Eu já me apavorei várias vezes.

Quem é seguidor do Blog Coizaradas e quiser essa planilha pronta, faça um comentário nessa postagem e deixe seu e-mail para envio. Se você gostou do post, não gostou, tem dicas, sugestões..comente...

Read More

Controle do contas no Excel

Nessa postagem gostaria de dar umas dicas de como criar uma planilha de controle mensal de contas no Excel. Não exige nenhuma função avançada e é bem simples de criar. Antes de tudo abra o Excel e insira 7 planilhas. Geralmente o Excel inicia com apenas três ou cinco, depende das configurações do programa, então clique no menu Inserir >Planilha e adicione mais planilhas até completar sete. Renomeie as seis primeiras planilhas com as iniciais de um mês do ano e na última escreva Total. Vai ficar assim:

Para dar nome a uma planilha, clique na aba da planilha, depois clique em Formatar > Planilha>Renomear (o atalho é dar dois clicks rápidos na aba da planilha e depois escrever o nome).

Agora que as planilhas já tem nomes, vamos fazer a digitação da tabela que vai servir de base para nossos dados. Em cada mês vai uma planilha de controle igual a essa

Mas não precisa digitar em todas as planilhas a mesma tabela e nem precisa copiar e colar. Olha o truque: Selecione todas as abas das planilhas. Clique na primeira aba que é “JAN”, pressione a tecla “SHIFT” e a mantenha pressionada, agora clique na última aba que é “Total”.

Com todas as abas selecionadas, tudo o que é digitado na primeira, automaticamente é adicionado nas outras.... Depois da tabela pronta você já pode digitar a primeira fórmula que vai realizar o somatório dos valores pagos em cada planilha.

Posicione o cursor na célula B24 como no exemplo acima e digite a fórmula:
=soma(B3:B23)
onde:
= sempre inicia uma fórmula do Excel
Soma é a função (comando) que deverá ser executada pelo programa
Dentro dos parênteses colocamos o intervalo da soma, da célula inicial (B3) até (:) a célula final (B23) .

Obs: dois pontos (:) significa ATÉ e ponto-e-vírgula (;) significa E.
Depois de ter digitado a fórmula, pressione a tecla Enter para que o programa execute a fórmula. Aproveite para selecionar o intervalo dos valores e formate em reais. Clique na primeira célula e com o botão do mouse apertado, arraste até a última célula do intervalo.

Depois que o intervalo está selecionado, clique no botão Estilo de Moeda, mesmo que as células estejam em branco, o formato será aplicado.

Agora você deve remover a seleção das planilhas para iniciar a digitação dos valores correspondentes de cada item da lista, senão os mesmos valores serão inseridos em todas as planilhas. Para remover a seleção das abas clique na aba Fev ou qualquer outra que não seja a aba ativa. Depois dos valores já digitados nas planilhas, é hora de digitar a fórmula final na última planilha (Total) Clique em B3 e digite a fórmula como mostra a figura abaixo:
Nessa fórmula o Excel vai somar todos os conteúdos das células B3 de todas as planilhas.
=soma(Jan:Jun!B3)
onde:
= sempre inicia uma fórmula do Excel
Soma é a função (comando) que deverá ser executada pelo programa
Dentro dos parênteses colocamos o intervalo da soma, da planilha inicial (Jan) até (:) a planilha final (Jun) .
Importante lembrar que o nome da planilha na fórmula deve ser exatamente igual ao nome da planilha da aba, senão o Excel não encontra o destino da soma.

O ponto de exclamação serve pra separar os argumentos da fórmula e deve ser digitado.
Você não precisa repetir a mesma fórmula em todas as linhas. Digite a primeira e pressione a tecla ENTER, depois clique novamente sobre a célula e clique no cantinho da célula onde tem um quadradinho preto. Quando o cursor se transformar numa cruz, clique, segure apertado e puxe para baixo até o final da planilha. Automaticamente ele faz a cópia das fórmulas às linhas subjacentes.... qualquer dúvida ou complementação, faça um comentário.

Espero que dê certo e que seja útil a todos...

Read More