Como calcular os juros cobrados em prestações fixas

Nestes tempos bicudos o que mais tem na televisão são propagandas com as prestações fixas em letras garrafais mas com a taxa de juros  em letras minúsculas, quase invisíveis. Para calcular os juros cobrados nas prestações fixas anunciadas de forma rápida use a função TAXA do Excel.

Como exemplo eu vou usar a tabela de opções de parcelamento do PagSeguro para a blusa com gola-laço que eu vendo lá no blog Moda de Novela, que à vista custa R$ 89,90 e o PagSeguro facilita em até 10 vezes no cartão de crédito:

Vou montar uma tabela para facilitar a visualização do cálculo:

Para achar o índice da taxa de juros eu insiro a função TAXA e referencio os dados no assistente da função:

NPER é o número de períodos do financiamento, a quantidade de prestações. PAGTO é o valor da prestação, com esta é uma função financeira toda saída de caixa tem que ser negativo, por isso este argumento obrigatoriamente deve ser negativo (em vez de multiplicar por –1 eu poderia ter preenchido a tabela com o valor da parcela –10,00). VP ou valor presente é outro termo financeiro, que é o valor do bem à vista. VF (valor futuro) e Tipo são argumentos pouco utilizados em cálculos simples, por isso não entrarei em detalhes, caso você queira se aprofundar no assunto clique no link de ajuda da função no assistente.

Agora ficou fácil calcular os juros quando você ver um comercial das Casas Bahia ou da Texpicks, hehehe…

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Função SE + formatação condicional fazem relatório com sinalização de farol

Tem chefe que adora relatório com “farol” – uma marcação colorida sobre o status do indicador, eu tive um que queria “farolzinho” em tudo. Lembrando disso, eu explico a seguir como criá-los rapidamente usando a função SE e a formatação condicional. Vou usar como exemplo um controle de estoque, mas pode ser qualquer outro tipo de indicador.

Para cada item eu determino o nível de segurança, que é quantidade de unidades em que a operação pode continuar sem problemas e dá tempo do fornecedor fazer a reposição.

Na coluna D ficará a sinalização gráfica, em vez de usar imagens vou usar caracteres comuns transformados em símbolos através da fonte WEBDINGS. Os números serão transformados em setas.

A função SE aninhada vai comparar o estoque atual com o nível de segurança e retornar um valor do farol, os valores são “5”, “6” e “3&4”".

A tradução da fórmula é SE estoque atual for maior que nível de segurança o valor é 5, senão verificar SE o estoque atual é igual ao nível de segurança aí o valor é 3&4, senão o valor é 6.

Ao lado da sinalização gráfica vou colocar uma frase explicativa usando também a função SE aninhada.

A tradução da fórmula é SE o resultado da célula D é igual a 6 o valor é “estoque baixo”, senão verificar SE o resultado da célula D é igual a 5 aí o valor é “,estoque bom” senão o valor é “atenção”.

Repeti a o processo para relacionar uma ação ao indicador também, assim ao preencher o estoque atual o farol é atualizado e também a sua descrição e uma ação a ser tomada.

A formatação condicional vai colorir as setas aqui de uma forma simples, verificando o valor das células.

Obs.: lembrando que o input do estoque atual não precisa necessariamente ser digitado aí, você pode (e deveria) pegá-lo de uma fonte externa, como um banco de dados, uma página web da intranet, planilha de outro setor, etc. porque assim toda vez que abrir a planilha o cálculo é automático. Ainda hoje perde-se muito tempo com planilha-prá-lá planilha-prá-cá em vez de fazer tudo relacionado e trabalhando em conjunto.

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Como calcular distância entre coordenadas geográficas em uma planilha eletrônica

Já vou avisando, este post é muito chato para quem odeia matemática, mas é muito interessante para quem quer extrair o máximo do Excel.

Depois do Google Maps não temos mais que sofrer em termos de geo-localização, distâncias e itinerários. E é dele que vou pegar as coordenadas geográficas de 2 pontos de São Paulo para “brincar” no Excel, no seu trabalho provavelmente elas virão de um banco de dados.

(Com certeza deve ter um jeito mais fácil,) eu achei o endereço, cliquei em LINK, copie o endereço para o bloco de notas e peguei a latitude e a longitude.

A fórmula vai fazer o cálculo da distância em linha reta entre a Praça da Sé e o autódromo de Interlagos, em São Paulo. Aqui as latitudes são SUL e as longitudes são OESTE:

Agora é necessário transformar as coordenadas em números que o Excel entenda. Atenção, todas as equações de conversão começam com sinal negativo, pois as latitudes Sul são negativas e as Norte positivas, e as longitudes Oeste são negativas e as Leste são positivas.

A fórmula da latitude da Praça da Sé é

=-(23+55/60+4/3600)

A fórmula é grau + minutos divididos por 60 + segundos divididos por 3.600.

E assim por diante com as demais coordenadas.

Achou difícil? Olha isso agora. Vou colar a fórmula que calcula a distância em linha reta em uma superfície curva como o globo terrestre.

A fórmula é:

=6371*ACOS(COS(PI()*(90-D3)/180)*COS((90-D2)*PI()/180)+SEN((90-D3)*PI()/180)*SEN((90-D2)*PI()/180)*COS((E2-E3)*PI()/180))

A equação é complicada mesmo, precisaria escrever um livro para explicar aqui, ela multiplica, soma,  divide e subtrai cossenos, PIs e raios, por isso não quebre a cabeça tentando desvendá-la, preocupe-se apenas que as referências das latitudes e longitudes (em vermelho) estejam corretas e obterá o resultado:

OBS: a equação começa com número 6371 que é o raio da Terra em quilômetros, caso você precise trabalhar a unidade milhas o número é 3959 ou precise de qualquer outra unidade de distância é esse número que deverá ser substituído, ok?

Bem, espero que este exemplo complicado mas interessante possa ter ampliado a sua visão de possibilidades de uma planilha eletrônica.

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Planejamento financeiro doméstico

O Jornal Hoje exibiu uma reportagem sobre planejamento financeiro doméstico bem interessante, vale a pena assistir abaixo. Eles disponibilizaram também 2 planilhas para download, uma de consolidação de gastos mensal pessoal e da casa e outra de planejamento financeiro anual, são bem práticas e fáceis de usar, os links estão logo após o vídeo, clique com o botão direito do mouse e escolha a opção “salvar como”.

Reportagem sobre planilhas financeiras no Jornal Hoje (02’59”):

• Planilha consolidação de gastos mensal (clique com o botão direito e escolha “Salvar como”)
• Planilha planejamento financeiro doméstico anual (clique com o botão direito e escolha “Salvar como”)

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Usar a função PROCV é mais fácil do que parece – parte 2

Leia antes: Usar a função PROCV é mais fácil do que parece – parte 1

Só entendendo bem como funciona a função PROCV é que passamos  usa-la eficientemente no nosso dia-dia e ganhando bastante tempo e produtividade nas tarefas. Nesta segunda parte usarei um novo exemplo bem prático: preencher um pedido de vendas do blog Moda de Novela usando a função PROCV.

Eu vou colocar a lista de produtos e formulário de pedido em uma única planilha para simplificar o exemplo, mas o ideal (e boa prática) é cada um em arquivo distinto:

Apenas para constar, eu “enfeitei o pavão”, deixando o formulário com os campos organizados e com a cara que será impresso:

A idéia é que na parte dos produtos vendidos só seja necessário preencher o código do produto e a quantidade vendida, ficando a descrição e o valor unitário a cargo da função PROCV. Para a descrição do produto a referência na tabela de produtos é só para as duas primeiras colunas e na do valor unitário pega as três colunas. OBS: como a minha lista de produtos cresce com a ordem de códigos desordenadas eu uso o valor “0” no argumento Procurar_intervalo para forçar o Excel só achar o valor exato que estou procurando, porque se o código do produto não existir ele vai dar erro e não mostrar produto diferente:

A fórmula da descrição do produto é

=PROCV(F12;$A$4:$B$11;2;0)

que significa ‘procure o valor da célula F12 na primeira coluna do intervalo A4:B11 e quando achar o valor exatamente igual retorne o valor que estiver na 2ª coluna da direita dele na mesma linha’.

A fórmula do valor unitário do produto é

=PROCV(F12;$A$4:$C$11;3;0)

que significa ‘procure o valor da célula F12 na primeira coluna do intervalo A4:C11 e quando achar o valor exatamente igual retorne o valor que estiver na 3ª coluna da direita dele na mesma linha’.

Agora é só copiar as fórmulas para as demais células e já deixar os cálculos de valores feitos, mas para evitar que fique aparecendo mensagens de erro nas células enquanto não forem preenchida eu acrescentarei a função ÉERROS nas fórmulas (veja como no post “a aparência da planilha importa tanto quanto o conteúdo”).

Abaixo está o formulário de pedidos pronto, eu recebo os pedidos do site de compras Toda Oferta do UOL e preencho os dados na minha planilha. As linhas ficam em branco ou zeradas até que o código do produto seja digitado; no 3º item já está preenchido porém nada está sendo calculado porque falta a quantidade, assim que especificada vai para o cálculo total também.

Acredito que agora ficou mais fácil compreender como a função PROCV funciona, com o tempo você achará novas utilidades nas suas planilhas.

Veja também:

• A aparência da planilha importa tanto quanto o seu conteúdo
• Não é o ideal, mas já que o relatório vai em Excel…
• Faça uma planilha sem segredos e livre-se dela rapidamente

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Usar a função PROCV é mais fácil do que parece - parte 1

Da interface do Cliente para dentro, uma empresa é composta muitas siglas e códigos. Departamentos, áreas, regiões, produtos e até cada funcionário, é tudo um conjunto de números ou de consoantes. E a gente se acostuma com isso. E decora dezenas, centenas deles, não é? Mas na hora de produzir um relatório não posso plotar que foram vendidas X unidades de produto SPT-MRN na área BSB como consta no arquivo do banco de dados do mês passado, tenho que transmitir a informação clara e obejtiva de quantos e quais produtos foram vendidos e aonde.

A forma mais prática de resolver isso é trabalhar normalmente as planilhas nos códigos e padrões do banco de dados e do "dialeto" interno até o momento da finalização do relatório, onde só se faz a tradução das siglas e códigos na tabela final e/ou gráficos usando a função PROCV.

A função PROCV parece complicada, mas este exemplo bem simples vai te ajudar a entende-lo. Vou usar mais uma vez a Fórmula 1 (porque será?). Esta fração da tabela da numeração dos carros da temporada 2009 nas colunas A e B vai representar o meu banco de dados, o número é o código do produto (área, vendedor, região, what ever...) e o nome do piloto é o nome do produto (etc). Ali nas colunas D e E é a representação dos campos do meu relatório, nas células D serão inseridos os códigos e nas E a função PROCV vai traduzi-los.

Usando o assistente da função PROCV: no Valor_procurado eu indico a célula onde eu preencherei com o número do carro, é o valor que vai ser buscado na tabela de códigos; em Matriz_tabela eu tenho que colocar todo o range da tabela de códigos, neste exemplo de A2 a B11 - com cifrão para poder copiar a fórmula para outras células sem perder a referência absoluta da tabela; no Núm_índice_tabela eu indico em qual coluna a partir do Valor_procurado na tabela de códigos está o campo que eu quero, neste exemplo é 2, pois na 1ª coluna está o número do carro e na 2ª coluna está o nome do piloto. OBS: o item Procurar_intervalo fica em branco quando você estiver efetuando uma pesquisa de valor exato ou colocar "1" nesse campo se a procura for por valor aproximado - nesse caso a tabela de código deve estar ordenada em ordem crescente; funciona assim: por superstição não existem mais carros de Fórmula com nº 13, se a minha tabela estivesse completa e eu fizesse a busca aproximada pelo número 13 o valor retornado seria o do corresponde ao primeiro mais perto do exato, no caso o do número 12. Pode ser que na sua tarefa um resultado "não-exato" seja satisfatório, mas neste caso o carro nº 1 só pode ser do Lewis Hamilton.

Neste exemplo simples eu preenchi manualmente a célula D2 com o nº 3 e a função PROCV retornou na célula E2 o valor Felipe Massa da tabela de códigos, mas em um trabalho mais elaborado o valor da célula D2 estaria vindo de outra planilha, neste exemplo poderia ser da tabela de resultados do GP do Bahrein, no seu caso de uma planilha de vendas. É no uso constante que se vai aprimorando a técnica. 

Apesar de não ter feito neste exemplo eu continuo recomendando que SEMPRE você evite deixar que as células com fórmulas aguardando argumento exibam mensagem de erro, adquira essa boa prática.

Eu sei que ainda restarão muitas dúvidas sobre essa função, por isso estou preparando um outro exemplo mais prático para desmistificar a função PROCV. Até breve.

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Excel até no final de semana

Não tem segredo, para ficar fera em Excel você tem que estudar e praticar o máximo possível, mas não precisar exagerar tanto assim:

Quem inventou essa grelha de churrasco é o tipo de cara que "come Excel com farinha", hahaha...

Via: Objetos de Desejo

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

Não é o ideal, mas já que o relatório vai em Excel…

…use e abuse da barra de ferramentas de desenho (menu Exibir/Barra de ferramentas/Desenho).

Em um mundo perfeito o Excel apenas forneceria tabelas e gráficos para um relatório em outra ferramenta, mas o mais comum é a planilha virar o relatório completo e aí temos que formatar toda a planilha como se fosse um .doc, não é?

É muito trabalho para ficar bem tosco no final. Para dar uma aparência decente e mais fácil de remodelar – se necessário, eu sugiro a utilização dos recursos da ferramentas de desenho, criando elementos separados para cada informação de texto do relatório.

Com as formas básicas dá para criar caixas destacadas, onde se pode inserir algumas informações isoladas.

Use e abuse das caixas de texto, faça cada parágrafo em uma distinta, depois as formate igual e agrupe tudo para formar uma só. Aqui eu mostro o título, bem mais prático que ficar mesclando células.

O mais importante, tanto para as imagens quanto para os gráficos, é ir lá nas propriedades e selecionar “Não mover ou dimensionar com células”, para que não fiquem deformados ao se fazer alterações na planilha.

Fazendo assim você monta o relatório como se fosse Lego em vez de ficar tentando encaixar e formatar texto dentro das células, deformando tabelas e saindo tudo torto na impressão.

Clique aqui, assine as atualizações do Pasta1.xls por e-mail e receba as dicas de planilha eletrônica na sua caixa de entrada.

O gráfico Radar é simplesmente um mapa

À primeira vista o gráfico Radar assusta. Esse modelo é um daqueles que não caminha sozinho e geralmente que o faz tem que sempre explicar em letra de forma que tipo de informação está querendo transmitir, por isso é bom usá-lo somente para uma audiência de conhecimento mais avançado. Ou não! Veja este exemplo em que vou tentar simplificar ao máximo o uso de um gráfico Radar.

Já tem um tempo que as 4ª-feiras acontecem a guerra dos hipermercados, onde as grandes redes diminuem drasticamente os preços e fazem grande alarde na mídia para atrair os consumidores e toda semana lá vou eu atrás da ofertas.

Acontece que cada rede de supermercados consegue regularmente preços bons em certos tipos de produtos e em cada uma eu tenho um perfil de compra ligeiramente distinto. Eu vou criar uma tabela com os principais produtos que eu costumo comprar às 4ª-feiras e dei notas de 0 a 10 conforme o estabelcimento pesquisado:

Atenção: as notas expressam exclusivamente minha opinião pessoal baseada na minha experiência de consumo nessas lojas.

Selecionei toda a tabela, cliquei no botão para criar gráfico e selecionei a opção Radar. Como sempre faço clico em Concluir e realizo os ajustes de personalização direto na área do gráfico.

Agora olhe bem para o gráfico; a teia formada tem a escala de valores que vai do centro para as extremidades, no meio o valor é zero e a última linha externa é 10.

Na 4ª feira de manhã eu analiso minha despensa e dependo da necessidade da casa eu vou para um determinado mercado. O preço de frutas e legumes é o chamariz principal do Carrefour e do Extra, mas se vou comprar cerveja também o mapa me sinaliza que o Carrefour tem tido preços melhores; caso precise de carne as melhores ofertas estão no Extra. E assim por diante, seguindo a sinalização visual que aponta os pontos fortes de cada unidade.

O gráfico Radar é muito útil para fazer múltiplas comparações, do tipo “eu caso ou compro uma bicicleta”, saca? Se estivesse usando apenas a tabela o que teria que fazer para obter uma resposta rápida ao meu questionamento? É o que muita gente faz, fica rastreando valores na tabela, fazendo contas desnecessárias e formatação condicional para depois comparar os resultados. O gráfico Radar mostra as direções com poucos cliques.

Esses atributos que comparei é só um exemplo da infinidade de usos para esse gráfico. Pode-se avaliar perfil dos funcionários, estilos de carros, tendências de mercado, etc. Se você está apaixonado pode fazer um gráfico romântico com “coisas que amo em você” – guarde o modelo para atualizar na separação, substituindo os quesitos originais por “coisas que odeio em você”, hehehe…

Eu considero a comparação de cinco itens a quantidade ideal por causa do formato de pentágono. Menos de quatro fica esquisito e mais que seis fica muito poluído, mas vai de cada necessidade, encontre a sua melhor forma – sem complicar para audiência, claro.

Como somar várias planilhas de forma prática e rápida

Esta dica funciona para consolidar uma série de planilhas semelhantes, que tenham o mesmo layout. A planilha de controle de horas é um bom exemplo para essa aplicação: eu repliquei a planilha várias vezes dentro do mesmo arquivo e fiquei com 12 plans nomeadas conforme o mês de referência, de jan09 a dez09. Na plan1 eu farei a consolidação de alguns dados:

Neste exemplo eu estou acompanhando o total de horas trabalhadas e fazendo a média simples mensal. A referência usada tanto nas funções SOMA e MÉDIA está apontando para as células E40 de todas as planilhas que estiverem neste arquivo entre as plans jan09 a dez09, inclusive. É como se o Excel empilhasse todas as planilhas e vai somar tudo que estiver na mesma célula em todas elas.

Isso pode ser usado para consolidação de qualquer tipo de grupo de planilhas padronizadas e com a maioria das funções de cálculo do Excel. Experimente.