pasta1.xls

Como não usar um gráfico - parte 1

2010-03-16T19:06:00.000-03:00

Na última semana um estudo da Fundação SEADE que comparou as mortes por homicídio versus acidentes de trânsito no estado de São Paulo fez um certo alarde na mídia. Talvez sensibilizada pela recente onda de acidentes bizarros cometidos por motoristas embriagados a TV Globo adotou a linha editorial de "mortes no trânsito superam homicídios", com frases como "o que mais puxou pra cima a violência no trânsito foram as batidas com motos". Tudo ia bem até aparecer o gráfico abaixo na reportagem:

Não precisa fazer força para perceber que não foi a violência no trânsito que atingiu níveis alarmantes e sim os homicídios que vem caindo drasticamente nos últimos anos, esse deveria ser o enfoque da reportagem. Como a intenção da TV Globo era demonizar os acidentes de trânsito usar esse gráfico foi um tiro-no-pé!

O uso de gráficos deve ser limitado a reforçar a sua mensagem e não para preencher espaço no seu relatório, pois o uso decorativo só aborrece os seus espectadores, enquanto o gráfico objetivo potencializa a sua informação.

Vamos trazer esse exemplo da TV para o mundo corporativo, imagine que esse informe para os acionista fosse "conquistamos a liderança do mercado" só que o gráfico está mostrando é que a concorrência é que perdeu participação não nossa empresa que melhorou e está crescendo. Entendeu a diferença?

Veja também: Gráfico De Barras Com Imagem e Gráfico Cotação do Dólar Com Imagem

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

Excel como ferramenta de desenho

2010-03-09T21:41:00.002-03:00

Que o Excel pode ser usado ad hoc das mais várias ferramentas e sistemas a gente já sabe - agora do Corel Draw também. Mas para desenhar através de um gráfico de linhas como nesse vídeo abaixo tem que ser meio lélé, não?

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

Dica para otimizar velocidade de cálculos no Excel 2007

2010-03-06T12:45:00.003-03:00

Se você usa o Excel 2007 em uma máquina com processador de dois (ou mais) núcleos você pode aumentar a velocidade de cálculo habilitando a opção "multithread": Clique no botão OFFICE/OPÇÕES DO EXCEL/AVANÇADO/PERMITIR CÁLCULOS MULTITHREAD

Com isso o Excel passa a fazer operações simultâneas de forma independente usando os núcleos separadamente. Isso significa uma sensível melhora de performance em planilhas parrudas, mas imperceptível no arroz-com-feijão do dia-a-dia.

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

Como criar listas personalizadas em planilhas no Excel

2010-03-02T19:42:00.005-03:00

O recurso de "listas personalizadas" do Excel faz muito mais do que listas ordinárias (1, 2, 3, ...) e completar meses (jan, fev, mar, ...), você pode instruir o programa para preencher aquela sequência que só você precisa, aquela que todo dia você copia e cola de outro arquivo para não ter que digitar novamente.

Vou continuar usando como exemplo o acompanhamento da temporada de Fórmula 1 que eu faço no blog F1Review. Eu quero ter todos os tempos arquivados e vou criar planilhas para documentá-los. Em cada etapa tem 3 treinos livres, 1 treino classificatório e o Grande Prêmio. Até o momento são 22 pilotos confirmados:

Digitar isso após cada evento não é nada estimulante. Abrir o arquivo anterior, copiar e colar a coluna também não é muito prático. Pegar o arquivo anterior e "salvar como" pode dar até mais trabalho se tiver que ficar deletando os dados que não são pertinentes na tarefa atual. A solução ideal é criação da lista personalizada.

Digitei a sequência uma única vez, selecionei todos os itens e cliquei no menu FERRAMENTAS/OPÇÕES/LISTAS PERSONALIZADAS, ali aparecem as listas pré definidas do Excel e mais abaixo tem a opção de inserir a nova, clicando em copiar* a nova lista já está incorporada nos recursos do programa:

*Obs: este post está sendo feito em um netbook com Linux portanto as imagens capturadas são do BrOffice Calc e as nomenclaturas são um pouco diferentes do MS Excel, mas as funções são as mesmas.

A partir de agora, a cada nova planilha criada para registrar os tempos da Fórmula 1 2010 eu só preciso digitar J. Button e arrastar o canto da célula para criar a minha lista de pilotos - conforme o cursor se move aparece a legenda com o dado a ser preenchido até aquele ponto:

A minha confecção faz as roupas sob encomenda, então a cada novo pedido eu tenho uma sequência de tarefas que ordenadas no documento vira um checklist no final para conferir se o pedido foi atendido corretamente, eu tenho uma lista personalizada com esse checklist.

O limite deste recurso é a sua imaginação, pois pode ser adaptado às mais diversas necessidades: lista de presença de alunos, compra de supermercado, itens de estoque, escalação de time de futebol, relação de filiais, staff de colaboradores... Enfim, precisa digitar sempre uma mesma sequência? Crie uma lista personalizada.

PS: no Excel 2007 o recurso é acessado através de OFFICE/OPÇÕES DO EXCEL/LISTAS PERSONALIZADAS.

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

Arquivo, novo, nova pasta do Excel, pasta1.xls

2010-02-22T19:28:00.002-03:00

Caros amigos, nobres colegas, muito obrigado pela paciência e confiança, porque mesmo o blog estando abandonado por todo 2009 teve uma multidão de pessoas inscritas para receber as dicas de Excel.

E isso me motivou a retomar o blog, mas peço que você não coloque muita expectativa pois este meu hobby tem espaço bem limitado no final da minha lista de tarefas e obrigações. A minha empresa demanda praticamente todo o meu tempo livre, o que sobra eu tenho que alimentar o blog que gera negócios para minha empresa e os meus finais de semana que já não eram livres ficarão completamente comprometidos a partir do dia 12 de março.

No meu planejamento de março eu reservei as noites de 3ª feira para produzir conteúdo para o blog Pasta1.xls, espero ter disciplina e conseguir.

Abs!

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

Banco de horas - reloaded

2010-02-10T20:58:00.005-02:00

Muito tempo já passou desde que eu criei uma planilha de banco de horas aqui no blog e eu não tenho mais o arquivo original nas tenho uma cópia no Google docs para compartilhar que pode te servir como modelo, mas deve ser baixada e importada para o Excel pois a função DIATRABALHOTOTAL só funciona no Excel e tem que ser habilitada nos suplemento "ferramentas de análise".

O link é: https://spreadsheets.google.com/ccc?key=0AlwnSbH2pOGGdGpwNm1jNkFndVpCd090TFQ3UHFWMVE&hl=pt_BR

Para personalizar a planilha e entender como funciona para adequá-la às suas necessidades é importante rever os passos da sua criação (clique no tópico para abrir o post):

Clique aqui e assine as atualizações do Pasta1.xls por e-mail e receba as dicas na caixa de entrada.

Como calcular os juros cobrados em prestações fixas

2009-05-28T23:10:00.001-03:00

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

2009-05-22T09:26:00.001-03:00

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

2009-05-15T16:02:00.001-03:00

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

2009-05-08T20:51:00.001-03:00

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

2009-05-04T11:02:00.001-03:00

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:

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

2009-04-27T08:57:00.006-03:00

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

2009-04-19T17:06:00.004-03:00

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…

2009-04-12T23:59:00.001-03:00

…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

2009-04-03T13:16:00.001-03:00

À 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

2009-03-27T03:08:00.001-03:00

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.

Dica final para completar e otimizar a planilha banco de horas

2009-03-22T22:18:00.002-03:00

Leia antes: Formatando a grade horária, Tabela Mensal de horários e Finalizando a planilha de horas.

Acabou passando batido na série Planilha Controle de Horas a exclusão de feriados e folgas dos dias úteis, opção que aparece no assistente da função DIATRABALHOTOTAL:

A função DIATRABALHOTOTAL é usada para contar os dias úteis entre duas datas e desconta apenas os finais de semana do período indicado, use o campo FERIADOS no assistente para indicar uma área da planilha onde estão digitados as datas dos feriados nacionais e da sua região. Você pode criar uma plan só para isso ou fazer uma relação em um dos cantos da planilha mesmo. Eu costumo incluir também as folgas pré programadas, os dias-ponte e escalas, e nomeio esse intervalo como FOLGAS, assim é só digitar o nome no campo Feriados do assistente, a fórmula que calcula os dias úteis ficará assim:

=DIATRABALHOTOTAL(B7;B37;FOLGAS)

Tem mais uma função que é muito útil para trabalhar com cálculos de períodos de datas: DATADIF. A função DATADIF calcula a quantidade de dias corridos entre duas datas e o resultado pode ser mostrado na unidade DIA, MÊS ou ANO, a fórmula neste banco de horas é:

=DATADIF(B7;B37;”D”)

O 3º argumento da fórmula é a unidade de cálculo desejada, as mais comuns são “D” para contar dias, “M” para meses e “Y” para anos, sempre entre aspas. O detalhe é que meses e anos só apresentam valores cheios, se eu calcular do dia 1º de janeiro até hoje o resultado será a quantidade de meses até o mês passado, pois este ainda está incompleto e seria uma fração; o resultado de ano seria 0, por que também não foi completado o período cheio.

Pode usar as duas funções juntas?

Sim, dá para combinar uma série de funções em uma única fórmula para economizar trabalho e otimizar a planilha. Neste controle de horas eu posso calcular quantos dias eu não trabalharei, que é a quantidade de dias do mês menos a quantidade de dias úteis (à trabalhar):

=(DATADIF(B7;B37;”D”))-(DIATRABALHOTOTAL(B7;B37;FOLGAS))

Use e abuse da combinação de funções nas suas fórmulas tomando cuidado apenas em encapsular cada função específica em seu próprio par de parênteses dentro da equação.

Veja também:

Como somar várias planilhas 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.

Política de Privacidade do blog Pasta1.xls

2009-03-17T12:32:00.001-03:00

O blog Pasta1.xls não solicita informações de seus leitores e não armazena os dados pessoais inseridos pelos usuários nos comentários quando solicitados pelo nosso provedor, o Blogger. Na inclusão de um comentário no site, mesmo que na opção Anônimo, o usuário está aceitando e concordando com a Política de Privacidade do Google.

O blog Pasta1.xls usa o sistema do Google AdSense de publicidade de terceiros para veicular anúncios durante a sua visita ao nosso website. Essa empresa pode usar informações (que não incluem o seu nome, endereço, endereço de e-mail ou número de telefone) sobre suas visitas a este e a outros websites a fim de exibir anúncios relacionados a produtos e serviços de seu interesse. O Google, como fornecedor de terceiros, utiliza cookies para exibir anúncios no seu site. Com o cookie DART, o Google pode exibir anúncios para seus usuários com base nas visitas feitas aos seus e a outros sites na Internet. Os usuários podem desativar o cookie DART visitando a Política de privacidade da rede de conteúdo e dos anúncios do Google.

Os endereços de e-mail cadastrados para o recebimento das atualizações do blog Pasta1.xls via correio eletrônico são armazenados no banco de dados do Feedburner e são usados exclusivamente para esse fim.

As páginas do blog Pasta1.xls usam scripts de monitoramento de visita que coletam dados estatísticos do visitante, como quantidade de páginas visitadas no blog, localização regional, tipo de navegador, sistema operacional e resolução de tela, mas nunca informações pessoais.

Faça uma planilha sem “segredos” e livre-se rapidamente dela

2009-03-17T10:50:00.001-03:00

Recentemente recebi 2 feedbacks legais sobre este blog que resolvi compartilhar um pensamento importante que tenho sobre o trabalho com planilhas eletrônicas.

Você não precisa ser um expert em planilhas eletrônicas para ter sucesso no mundo corporativo, pelo contrário, quanto mais especialista mais estagnado ficará na carreira. Use bem essa importante ferramenta que é o Excel e progrida profissionalmente.

Usar bem quer dizer tirar proveito das funções que agilizam suas tarefas mas que também possam fazer seu documento circular pela empresa sem que você tenha que acompanhá-lo. Vou dar um exemplo próprio. Certa vez eu desenvolvi um relatório que fazia a consulta em bancos de dados SQL de diversos “produtos” em diversos períodos de tempo que geravam alguns cenários de estudos distintos já com meia dúzia de gráficos para plotar no Power point. Tudo perfeito. Só que eu nunca mais consegui me livrar da tarefa, saí de férias o trabalho ficou paralisado, mudei de departamento carreguei a tarefa comigo, mudei de área de negócio e virei help desk do novo dono da planilha.

A programação VBA no Excel 2003 realiza coisas fantásticas com apenas 1 clique, mas você será o macaquinho amestrado que ficará dando esse clique até o fim dos dias. A aplicação das funções simples no Excel dá pernas para que sua planilha ande sozinha e você possa abraçar novos desafios.

Não tenha medo de compartilhar seu conhecimento, pelo contrário, tente nivelar a performance de todo mundo – nivelar por cima, lógico! Ao entregar uma solução simples e eficiente você ganha moral tanto com seus pares quanto com seu chefe e garante o leitinho das crianças.

Uma boa prática para se livrar (no bom sentido) facilmente da sua planilha:

Use e abuse das informações de propriedades do arquivo (menu Arquivo/Propriedades). Dê um título esclarecedor para o seu arquivo (obs.: título é diferente de nome, o nome do arquivo é aquele .xls que você deu quando criou o arquivo, aqui você pode ser mais abrangente); descreva objetivamente o assunto de que trata o documento; além do nome do autor você pode por o ramal se for documento interno ou e-mail se for documento externo - caso a Política da empresa permita, claro; quem é bem organizado pode catalogar categoria e palavras-chave; os comentários é que são o mais importante! Aqui você pode (e deve!) colocar o “how to” da tarefa, se tem consulta em qual banco de dados, como é a query, porque compara laranja com banana, qual periodicidade, para quem é enviado, o histórico das atualizações e tudo mais que for importante para a execução do trabalho nessa planilha. Além de ajudar na documentação da empresa você está se dando a oportunidade de passar a tarefa adiante sem traumas e não virar escravo da rotina.

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

Finalizando a planilha de controle de horas

2009-03-15T19:11:00.002-03:00

Leia antes: Formatando a grade horária e Tabela Mensal de horários.

É muito simples fazer uma planilha de controle de horas ou banco de horas para você, é só fazer uma grade de horários com uma tabela diária de horário de entrada e saída. Só é preciso ter uma certa cautela no cálculo de horários que não são feitos da mesma forma que outros tipos de números, o ideal é fazer cálculos simples.

Essa planilha é um bom tipo de arquivo que todo mundo precisa, por isso vamos dar um acabamento no modelo que fizemos para ficar um documento apresentável para compartilharmos com nossos pares.

Eu inserí mais uma coluna e algumas linhas para fazer o cabeçalho, no qual vou identificar o mês de referência, a quantidade de dias úteis e para lá moverei a célula da Jornada – que continuará funcionando normalmente nos cálculos das horas extra.

O mês eu referenciei direto do 1º dia da tabela, mas mudei a configuração da célula para tipo personalizado mmmm/aaaa que exibirá o mês, a barra e o ano (março/2009).

Para calcular a quantidade de dias úteis usei a função DIATRABALHOTOTAL (obs.: para ter a função disponível é necessário habilitar o suplemento Ferramentas de Análise lá no menu Ferramentas/Suplementos). No assistente da função DIATRABALHOTOTAL é só preencher a data de início do período ou referenciar a célula em que ela está e preencher a data de fim do período ou referenciar a célula em ela está – e também a relação de feriados se você tiver uma.

No rodapé eu vou fazer uma totalização das horas do mês.

Aqui está um dos “segredos” de se trabalhar com horas no Excel, que é formatação adequada da célula, neste caso eu escolhi a categoria Personalizado e digitei o formato [h]:mm, com isso o Excel passa a somar/subtrair volumes de horas além das 24 horas de um dia.

Na figura acima estão as fórmulas de totalizações. Personalize a planilha com o padrão de cores suas/da empresa/do Cliente e o seu documento está pronto, basta atualizar a data do primeiro dia da tabela que que todas as demais (e o cabeçalho) serão alterados automaticamente.

Veja também:

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.

Planilha controle de horas – a tabela mensal de horários

2009-03-08T23:06:00.003-03:00

Leia antes: Formatando a grade horária.

Com a grade de horários preparada já dá para completar a tabela mensal e fazer o cálculo diário da jornada e, se houver, horas extra.

Esta planilha está sendo feita para jornada em 2 períodos, então a forma correta de somar a carga horário realizada no dia é combinação das funções SOMA e SE:

Preste atenção na função SE aqui da soma das horas trabalhadas pois a usaremos novamente na fórmula da hora extra, mas de uma forma um pouquinho diferente.

O primeiro SE testa se a carga horária do período da manhã (saída menos entrada) foi menor que 0, se SIM soma 0, se NÃO soma a quantidade de horas trabalhadas no período da manhã; o segundo SE faz a mesma coisa no período da tarde, havendo saldo de horas (saída menos entrada) o valor é acrescentado na soma.

OBS: este conjunto SE é adicionado para evitar o aparecimento de sinais de erros nas células enquanto o resultado a jornada não estiver completa, você pode deixar preenchido apenas o horário de entrada que vai ficar um zerinho lá no total, numa soma comum aparecia caracteres de erro. Não se esqueça que beleza é fundamental ;)

Para calcular a hora é necessário saber qual a carga horária diária, aqui eu estabeleci como Jornada e estipulei 8 horas diárias. Para facilitar a digitação das fórmulas eu adotei a boa prática de nomear o intervalo, nesta caso uma única célula (menu INSERIR/NOME/DEFINIR). Mais pra frente, na hora dar a cara do documento, isso vai ser muito útil.

A fórmula da hora extra é simples, a função SE verifica se a quantidade de horas trabalhadas é maior que a jornada regular, se SIM faz a conta horas trabalhadas menos a jornada estipulada, se NÃO o total de hora extra é 0.

Copio as fórmulas de horas trabalhadas e extras até o dia 31. Como a função SE acima estão programadas para preencher automaticamente as células com 0 na ausência de hora trabalhada/extra eu vou igualar as demais células da tabela e preencher toda a grade horária com 0 usando o assistente LOCALIZAR e SUBSTITUIR selecionando todo o período de entrada e saída do mês e substituindo “vazio” por “0”.

O tabelão está pronto, a próxima etapa é transformar essa planilhinha em um belo documento corporativo, até breve.

Veja também:

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.

Planilha controle de horas – formatando a grade horária

2009-03-04T14:48:00.002-03:00

Uma planilha de banco de horas eficiente não deixa de ser uma grande tabela, a formatação correta é que vai permitir o cálculo certo no final.

O cabeçalho da tabela tem a data, entrada e saída para os períodos da manhã e da tarde, o total de horas trabalhadas e o total de horas extra. Digite a 1ª data do mês e escolha a formatação de data. Use fórmula para preencher os demais dias do mês, porque ao usar a planilha como modelo para outros meses é só alterar o primeiro dia que os demais serão atualizados automaticamente. Neste exemplo a fórmula para o próxima dia é =A3+1 e assim até completar o mês.

Assim como no cronograma.xls eu vou usar a formatação condicional para sinalizar o final de semana. Lembrando que o suplemento Ferramentas de Análise deve estar habilitado no menu Ferramentas/Suplementos. No assistente de formatação condicional (menu FORMATAR/FORMATAÇÃO CONDICIONAL) eu escolho “A fórmula é” e digito =OU(DIA.DA.SEMANA($A3)=7;DIA.DA.SEMANA($A3)=1), onde a função OU procura a validação de um dos argumentos da equação que está verificando se a data é um sábado ou um domingo.

As outras células da tabela são formatadas como HORAS e nelas também também são inseridas a mesma formatação condicional “final de semana”.

Na próxima etapa serão inseridos os cálculos. Até breve.

Veja também:

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.

Planilha de controle de horas - introdução

2009-02-27T11:07:00.002-03:00

Uma das maiores ciladas do Excel é o cálculo com valores de horas, porque os seus algorítimos são programados para trabalhar com números “normais” e nem mesmo com a formatação adequada das células as unidades de segundos, minutos, horas e dias são interpretadas corretamente e geralmente são necessárias equações complexas para que se consiga, por exemplo, saber o saldo correto de horas trabalhas em uma semana.

Por outro lado, existem formas simples de se trabalhar com sistema de datas na planilha eletrônica que não doem nada e são muito úteis para o nosso dia-a-dia e é o que eu vou abordar nesta nova série do blog Pasta1.xls: planilha de controle de horas, que você poderá usar para anotar seu banco de horas, por exemplo. Até o próximo post.

Veja também:

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.

A aparência da planilha importa tanto quanto o conteúdo

2009-02-24T07:56:00.000-03:00

Diz o ditado: “não basta a planilha de César ser um bom documento, ela tem que parecer um bom documento”. E você sabe que no ambiente corporativo a imagem conta mais que a competência, geralmente. Isso quer dizer que não adianta você ser fera em planilha eletrônica mas todo mundo achar seu trabalho complicado. Uma planilha simples, clean e com fácil usabilidade será o teu cajado para abrir o mar vermelho da pirâmide corporativa.

O tipo de planilha que mais trafega em escritórios são documentos ou relatórios de gastos/consumo/pedidos/estoque/vendas/compras, ou seja, quanto do quê? E a objetividade e qualidade do seu material são o que fazem você se destacar na organização ou ficar escondido atrás do monitor.

Limpeza é requisito básico

Não tem coisa mais repulsiva em uma planilha eletrônica do que células preenchidas com #DIV/0! ou #REF!, não é? A gente sabe que muitas vezes ainda falta uma referência ou algum parâmetro para completar o cálculo, mas não adianta, a pessoa que está vendo já começa com um pé atrás, achando que está tudo errado. A solução para evitar essa experiência negativa é usar nos cálculos a função ÉERROS, que combinada à função SE ocultará as seguintes mensagens de erro do Excel: #DIV/0!, #N/D, #NOME?, #NULO!, #NUM!, #REF! e #VALOR!.

Exemplo, na célula A3 eu tenho o total da divisão das 2 células de cima =A1/A2, se uma das células estiver vazia o resultado vai ser #DIV/0!, para ocultar esse erro e mostrar apenas 0 a fórmula tem que ser

=SE(ÉERROS(A1/A2);0;A1/A2)

A função SE faz um teste lógico e vai exibir um valor determinado se o resultado for verdadeiro ou o outro valor determinado se o resultado for falso; neste nosso exemplo o teste logico é verificar se divisão A1/A2 dá erro, se der vai preencher a célula com o valor 0, mas se o teste for negativo, não há erro na divisão então ele exibirá o valor da divisão A1/A2.

Não vou mentir, é mais trabalhoso, e dependendo do quantidade de cálculos na planilha é um trabalho miserável, mas o retorno “moral” não tem preço!

O melhor uso de um Painel de Indicadores

2009-02-22T14:56:00.002-03:00

Leia antes: Introdução ao Painel de Indicadores, Prepare a base de dados, Primeiros gráficos, Gráficos com imagens, Visão completa do Período e O painel finalizado

Para encerrar de vez a série Painel de Indicadores F1Review deixo a dica final, que é a melhor forma de uso de um dashboard: a intranet.

O recurso Salvar Como Página da Web é ferramenta ideal nestes tempos de redução de custos ferrenha nas empresas, pois é um argumento para economizar papel e toner na impressão de relatórios, porque você pode publicar o seu Painel de Indicadores na intranet da empresa direto do Excel. Aliás, não precisa ser nem na intranet, você compartilhar direto da sua máquina se ela estiver em rede.

A opção está sob o menu arquivo e também pode ser adicionado um botão na barra de ferramentas. Um detalhe importante para que o arquivo seja visualizado apenas como pagina da web é escolher a opção SELEÇÃO PLANILHA, senão tudo o que tiver nesse arquivo Excel será salvo junto e a visualização vai ter todas as guias mais linhas e colunas.

No lugar que você escolheu para salvar vai ter o arquivo HTML e uma pasta com os arquivos de imagem – os gráficos viram imagens GIF – e o mapa XML da página.

Atenção: se você salvar em uma certa pasta e depois mover para outra na rede, por exemplo, deverá editar os arquivos HTML e XML com as novas referências, o ideal é salvar direto no local definitivo.

Se você está procurando seu ligar ao sol no mundo corporativo o Painel de Indicadores é aquele “algo mais” que o seu chefe espera nos seus relatórios.