Planilhas Excelentes

Gráfico Condicional 2 - Muda de cor conforme valor

2012-10-25T18:49:00.001-02:00

Este gráfico é muto simples, ele muda de cor de acordo com o valor.

Para isso utilizaremos apenas um gráfico de barras simples, utilizando um recurso próprio de sobreposição, criando assim a ilusão de mudança de cor.

O passo-a-passo é muito simples, sendo que muitos deles já foram comentados nos posts anteriores. A novidade fica na opção Sobreposição de Séries, que iremos configurar para 100% (Sobreposto).

Ferramentas de Gráfico > Formatar > Selecionar qualquer série (barras) > Opções de Série > Deslizar a seta até o valor ficar em 100% (Sobreposto).

Para deixar o gráfico um pouco melhor você pode também ajustar a opção abaixo (Largura do Espaçamento) para 100% por exemplo.

Outra função nova que utilizamos neste gráfico é a função E(), que somente retorna VERDADEIRO se todas as condições também serem. No nosso exemplo, somente será VERDADEIRO se a quantidade de venda for maior ou igual a 50 e menor que 70, ou seja, apenas se estas duas condições forem verdadeiras.

Assista a criação deste gráfico no YouTube: http://youtu.be/LghzfXfBwxk

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloque as suas dúvidas, e deixe o seu comentário.

Não foi utilizada macro neste gráfico.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Gráfico Interativo 4 - Caixa de Seleção

2012-10-19T13:04:00.000-03:00

Utilizando as caixas de seleção, poderemos visualizar todas as informações das filiais (do nosso exemplo) e compará-las livremente, diferente do post anterior onde só era possível comparar de 2 em 2.

A diferença entre as caixas de seleção e as caixas de combinação é que as caixas de seleção retornam VERDADEIRO ou FALSO em vez de um número relacionado a opção selecionada.

Assista a criação deste gráfico no YouTube: http://youtu.be/26IGeumqaRk

Com relação ao Botão de Opção, não farei um vídeo de exemplo pois é idêntico às caixas de combinação, porém num formato diferente.

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloque as suas dúvidas, e deixe o seu comentário.

Não foi utilizada macro neste gráfico.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Gráfico Interativo 3 - 2 Caixas de Combinação

2012-10-17T20:18:00.002-03:00

Neste gráfico temos duas caixas de combinação, ideal para gráficos comparativos.

Não existe nenhuma função diferente neste post. É mais um complemento ao post anterior.

Assista a criação deste gráfico no YouTube: http://youtu.be/B55YIJTjPco

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloque as suas dúvidas, e deixe o seu comentário.

Não foi utilizada macro neste gráfico.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Gráfico Interativo 2 - Caixa de Combinação

2012-10-17T00:30:00.001-03:00

Vamos demonstrar de maneira bem simples como aplicar a interatividade no gráfico usando a caixa de combinação.

Neste gráfico simulei três filiais e suas vendas. Usei a função ALEATÓRIOENTRE(inferior,superior) que gera um número aleatório entre dois números, onde inferior é o número mais baixo e o superior indica o número máximo. Depois criei uma coluna e uma linha de totais. Nesta matriz, repare que coloquei também números guias em vez de meses, isso para facilitar um outro comando, o PROCV().

O PROCV() é uma função excelente e que utilizo muito nas minhas automações. Funciona assim:

PROCV(valor_procurado, matriz_tabela, núm_índice_coluna, [procurar_intervalo])

Valor_procurado - No nosso caso queremos saber a filial ou o total de vendas. Como estamos utilizando a caixa de combinação que retorna um número referente a lista designada, incluímos uma coluna com os mesmos valores para servir de referência. Reumindo, Valor_procurado é o que nós estamos procurando.

matriz_tabela - No nosso caso, até para facilitar, vamos abranger toda a matriz, mesmo que neste primeiro momento não utilizaremos os dados das colunas adiante. A matriz_tabela é todo o conjunto de linhas e colunas onde estão os dados que queremos relacionar.

núm_índice_coluna - É o número da coluna com os dados que queremos relacionar. Lembra da primeira linha com os números guias? Então, neste caso vamos apontar este item para aquelas células.

procurar_intervalo - É um item bastante importante, pois se a nossa matriz não estiver classificada em ordem alfabética e se este item estiver em 1, podem ocorrer alguns problemas. O ideal é sempre manter esta opção em 0 (zero).

Observe também que eu utilizo alguns caracteres especiais, como o $ (cifrão) no meio de algumas funções. O cifrão serve para fixar uma determinada célula, linha ou coluna. Por exemplo se você ver $A$7, significa que a célula A7 está fixada. No nosso exemplo, eu fixo a célula A7 para a função PROCV() buscar como referência em todas as outras células adjacentes, para não precisar digitar a função novamente em cada célula. Se você quiser fixar uma coluna, inclua o cifrão antes da coluna, assim: $A7. No caso da linha fica assim: A$7.

Dica: Apertando a tecla F4 a fixação é automática. Aperte a tecla F4 mais de uma vez para alternar entre a fixação de célula, linha e coluna.

Depois é só construir o gráfico de acordo com a sua preferência, no exemplo eu só incluí no título o total de venda da respectiva filial e sua porcentagem em relação ao total, utilizando para isso a função CONCATENAR(), que simplesmente une textos, e também a função TEXTO(), que formata o texto de acordo com a sua preferência, no nosso exemplo em porcentagem.

Assista a criação deste gráfico no YouTube: http://youtu.be/ZR1MI_yVPJI

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloque as suas dúvidas, e deixe o seu comentário.

Não foi utilizada macro neste gráfico.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Gráfico interativo - Como inserir controles

2012-10-13T18:36:00.001-03:00

Neste post eu vou mostrar como inserir controles para os nossos gráficos interativos.

Para isso você precisa habilitar a aba Desenvolvedor.

Arquivo > Opções > Personalizar Faixa de Opções > Clicar em Desenvolvedor

Os três controles básicos são a caixa de combinação, caixa de seleção e botão de opção.

A caixa de combinação permite escolher um item (e apenas um) em uma lista.
Clicando com o botão direito do mouse e em Formatar controle > controle, podemos definir onde buscamos a lista de opções em Intervalo de entrada: e a célula de destino onde aparecerá a opção selecionada, neste caso o número referente a opção selecionada.

A caixa de seleção permite escolher mais de uma opção. Neste caso é interessante selecionarmos uma célula para cada opção criada, pois o resultado será VERDADEIRO ou FALSO no Vínculo da célula para cada item.

O botão de opção permite escolher apenas um item de vários disponíveis. Neste caso também aparecerá um número referente a opção selecionada, e também basta apenas uma célula para o Vínculo da célula (percebam no vídeo que eu apenas seleciono o Vínculo da célula na primeira opção, os outros botões assumem automaticamente a célula selecionada).

Com estes três controles dá para fazer bastante coisa nos gráficos interativos, como mostrado no post anterior.

Assista a criação deste gráfico no YouTube: http://youtu.be/Mt5lIrnQ8vk

Demonstração de um gráfico condicional

2012-10-12T16:15:00.003-03:00

Uma pequena demonstração de como os gráficos simplificam a visualização de dados.

Neste caso uma grande quantidade de dados foram sintetizados em apenas 3 (três) gráficos.

Assista o funcionamento deste gráfico no YouTube: http://youtu.be/GS-qyv-QXE0

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloque as suas dúvidas, e deixe o seu comentário.

Não foi utilizada macros neste gráfico.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Planilha com coordenadas geográficas das cidades brasileiras

2012-10-11T11:46:00.000-03:00

Você precisa apresentar um gráfico onde é necessário "plotar" as cidades brasileiras e para isso você precisa das coordenadas geográficas das mesmas, certo?

Ou então você quer automatizar algum processo (via Google Earth ou Maps) e é difícil encontrar estes dados na internet?

Clique na nossa Loja e saiba como é possível adquirir uma planilha com as coordenadas (latitude, longitude e altitude) dos 5.565 municípios brasileiros e também de outros sub-distritos e localidades totalizando 21.886 locais geográficos.

Gráfico com os 21.886 locais geográficos contidos na planilha

A planilha vem com a Unidade Federativa (Estado), Município, Sub-Distrito (quando houver), o Tipo (urbano ou rural), a Altitude (em metros e em relação ao nível do mar) e a Latitude e Longitude (em graus decimais).

Formatamos a planilha para o sistema de coordenadas de sua preferência/necessidade.

Gráfico estilo velocímetro 5 - Valor condicional no gráfico

2012-10-09T17:08:00.001-03:00

Neste post, vou ensinar como deixar o valor impresso condicionado ao valor, ou seja, quando o valor do ponteiro mudar, o valor do texto no gráfico muda também.

Os gráficos são quase idênticos ao do post anterior, a diferença é que o gráfico 2 (vermelho) também terá o Título habilitado e vamos direcionar os títulos para células distintas.

Vale a observação que quando manipulamos textos, o NÃO.DISP() não funciona como queremos, pois ele imprime o valor #N/D e isso nós não queremos. No lugar colocaremos duas aspas duplas (""), que dará o mesmo efeito.

Então desta vez coloquei o valor condicional do título do gráfico 1 (verde) na célula N5, e o título do gráfico 2 (vermelho) na célula O5 utilizando a função SE(). Se a condição é verdadeira imprimir o valor, senão imprimir nada (aspas duplas "").

Assista a criação deste gráfico no YouTube: http://youtu.be/poGZT-vEW-U

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloquem as suas dúvidas, e deixe os seus comentários.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Gráfico estilo velocímetro 4 - Valor no gráfico

2012-10-07T14:39:00.004-03:00

Neste post eu vou mostrar uma mudança bem simples, mas que faz uma enorme diferença.

Para facilitar a leitura do gráfico, principalmente para valores quebrados, vamos mostrar o valor diretamente no gráfico.

O gráfico, a princípio, é exatamente igual ao do post anterior. A grande sacada vem do fato que vamos "linkar" o título do primeiro gráfico (verde) com o valor a ser impresso. É isso mesmo! Essa possibilidade de direcionar para uma célula específica o que vai ser escrito no título do gráfico deixa o gráfico mais dinâmico. E nós vamos utilizar essa possibilidade no nosso gráfico, para isso após toda a criação do primeiro gráfico (verde), clique em Ferramentas de Gráfico > Layout > Título do Gráfico > Título Sobreposto centralizado. Após, podemos mudar a cor da fonte para não contrastar com o fundo preto, e na barra de fórmulas é só digitar = (igual) e a célula com o valor, no nosso exemplo L1. A medida que o valor vai mudando, o valor do Título também muda. Depois é só criar o segundo gráfico (vermelho) e terminar. Note que o título continua imprimindo o valor mesmo nas condições do segundo gráfico (vermelho).

Assista a criação deste gráfico no YouTube: http://youtu.be/Jvk4BU94vD8

Continue acompanhando os nossos posts e sugira planilhas, gráficos, coloquem as suas dúvidas e deixe os seus comentários.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Gráfico condicional - Gráfico estilo velocímetro 3

2012-10-06T13:55:00.001-03:00

Neste post irei explicar como fazer um gráfico que muda de cor dependendo do valor.

Novamente fiz uma busca na internet e achei alguns gráficos muito óbvios, como por exemplo gráficos de barras, etc.

Para prosseguir com a série Velocímetro, eu decidi utilizar mais uma vez este tipo de gráfico, vamos lá.

Para começar precisamos definir a condição para a mudança de cor. No meu exemplo eu defini que o ponteiro muda para vermelho quando estiver acima de 99. Vamos trabalhar novamente com sobreposição de gráficos. O segredo é que um gráfico fica visível até um determinado valor, enquanto o outro fica invisível, através da função NÃO.DISP(). E para este gráfico vamos utilizar a função SE().

A função SE() funciona assim:
SE(teste_lógico; [valor_se_verdadeiro]; [valor_se_falso])
teste_lógico: É o teste da condição, para sabermos se é verdadeira ou falsa.
[valor_se_verdadeiro]: É o que deve ser feito se a condição for verdadeira.
[valor_se_falso]: É o que deve ser feito se a condição for falsa.

Para o primeiro gráfico (verde), que fica visível do 20 até o 99, utilizamos a função SE() da seguinte forma: se valor < 100, então o valor da célula é 0 (zero), senão o valor é #N/D para as duas células que representam o primeiro ponto do gráfico (N1:N2) e a mesma condição para as células que representam o segundo ponto do gráfico (O1:O2), com os valores de seno e cosseno do valor. Neste gráfico devemos inserir a imagem do velocímetro, pois ele será a base principal (layer). Confira no post anterior como calcular estes valores e como formatar o gráfico utilizando uma imagem de fundo.

Mas o que significa #N/D? Significa Valor não disponível. Usamos ele através da função NÃO.DISP(). Para um gráfico, quando ele "lê" este parâmetro, ele não imprime nada, por esta razão, qualquer valor acima de 99 o primeiro gráfico não mostra o ponteiro.

Para o segundo gráfico (vermelho), que fica visível do 100 até o 200, utilizamos a função SE() da seguinte forma: se valor >= 100, então o valor da célula é 0 (zero), senão o valor é #N/D para as duas células que representam o primeiro ponto do gráfico (N3:N4) e a mesma condição para as células que representam o segundo ponto do gráfico (O3:O4), com os valores de seno e cosseno do valor. Devemos formatar este gráfico para ele ficar com o fundo invisível, pois ele será sobreposto a base principal (layer). Confira no primeiro post como deixar o gráfico com o fundo invisível.

É isso aí, mais um gráfico bem bacana para fazer.

Aqui vai um desafio, que tal fazer um gráfico que muda de cor três vezes? Essa é fácil hein?

Qualquer dúvida, crítica ou sugestão, deixe o seu comentário e não perca os próximos posts.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Assista a criação deste gráfico no YouTube: http://youtu.be/fZ3vzG4eYaQ

Como trabalhar com imagens nos gráficos - Gráfico estilo velocímetro 2

2012-10-05T10:06:00.003-03:00

Após eu publicar o post anterior, eu pesquisei na Internet sobre sites que também explicavam como fazer um gráfico no estilo velocímetro e achei muita coisa bacana, mas ao mesmo tempo complicada e nada prática.

Neste post vou explicar como podemos trabalhar com imagens em nossos gráficos.

Eu iria demonstrar em um outro tipo de gráfico, mas aproveitando o gancho decidi utilizar o famoso velocímetro para a explicação.

Então vamos lá.

Neste gráfico não vamos usar sobreposição, o que vai tornar o trabalha ainda mais fácil!
A figura que eu arrumei traz um velocímetro de escala que vai do 20 ao 200 e uma amplitude total de 270º (no post anterior era apenas 180º), o que complica um pouco mais para gente.

Bem, se considerarmos o ponto 50 e 170 do velocímetro e definirmos como 0º e 180º, fica fácil, teremos 170 - 50 = 120, exatamente como no post anterior. A cada número do velocímetro, teremos 1,5º.
Então, para começar eu coloquei os valores mínimo e máximo na coluna L, e o valor na célula L4.
Na célula N1 o valor do ângulo. Na N2 precisei adaptar o Valor para o cálculo do ângulo da seta. Ora, se considerarmos como 0 o 50 no velocímetro (0º), então é só subtrair do Valor 50. Ex. O valor em radianos do Valor 110 é ((110-50)*1,5º-270)*PI()/180 = 360*PI()/180 = 2*PI()
Agora é só construir o gráfico. Inserir > Gráficos > Dispersão > Dispersão com linhas retas e Marcadores.
Pronto. Agora é só formatar o gráfico de forma transparente:
Em Ferramentas de Gráfico, clique em Formatar
Em Seleção Atual, na caixa de combinação, selecione Área do Gráfico e clique em Formatar Seleção. No Preenchimento, clique em Sem preenchimento. Faça o mesmo processo em Área de Plotagem.
Apague as Linhas de Grade.
IMPORTANTE
Em Ferramentas de Gráfico > Formatar > EixoHorizontal (Valor) > Máximo > Mudar para o valor 1,0 > Mínimo para -1,0 e em Tipo de marca de escala principal e Rótulos do eixo, definir como Nenhum. Antes de fechar a janela, clique em Cor da Linha e defina como Sem linha. Repita o processo para o EixoVertical (Valor)
Isso garantirá que aparecerá apenas o ponteiro e que o tamanho da seta permanecerá sempre proporcional, independente do valor.

Para inserir a Figura

Vamos deixar o gráfico proporcional a imagem.
Ferramentas de Gráfico > Formatar > Seleção Atual > Área do Gráfico > Formatar Seleção > Tamanho > Tamanho e rotação.
Podemos definir a Altura e Largura para 10 cm, por exemplo.
E finalmente, é só inserir a imagem
Ferramentas de Gráfico > Formatar > Seleção Atual > Área de Plotagem > Formatar Seleção > Preenchimento > Preenchimento com imagem ou textura.

É isso aí, a princípio o gráfico ficou ainda mais simples de fazer. Você pode incrementar o gráfico com legendas, incluir o valor no ponteiro, mudar a figura de fundo, etc. Fica a critério da sua imaginação. Qualquer dúvida, crítica ou sugestão, deixe o seu comentário e não perca os próximos posts.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Veja o filme da criação deste gráfico em: http://youtu.be/LM6bGMu0DmE

Gráfico estilo velocímetro simples (Excel)

2012-10-04T15:48:00.002-03:00

Gráfico de Velocímetro

Para fazer este gráfico de forma simples e sem o uso de VBA, vamos utilizar dois gráficos sobrepostos.
O primeiro gráfico é do tipo Rosca, e o segundo do tipo X Y (Dispersão).
Para o primeiro gráfico, precisamos definir os valores mínimo, máximo e o valor das escalas. Neste exemplo, o velocímetro vai de 0 a 120, tendo como escala 60 (verde), 100 (amarelo) e 120 (vermelho).
Eu defini as células L1 até a L4 com estes valores (constantes) e a célula L6 com o valor a ser lido (variável).

Gráfico de Velocímetro

Para a construção do primeiro gráfico, precisamos definir as porções das escalas. Para a primeira porção (verde) que vai de 0 a 60, o valor é 60. Para a segunda porção (amarelo) que vai de 60 a 100, o valor é 40 (100 - 60). Para a terceira porção (vermelho) que vai de 100 a 120, o valor é 20 (120 - 100). Para a quarta porção (transparente) que vai de 120 a 240, o valor é de 120. Mas porque uma quarta porção? Neste exemplo, o velocímetro é de 180º, e por este motivo nós só queremos que a metade deste gráfico, a outra parte existe, porém nós ocultamos. Para estes valores eu coloquei as fórmulas na coluna P:

P1 = L3 (Escala 1)
P2 = L4 - L3 (Escala 2 - Escala 1)
P3 = L2 - L4 (Valor máximo - Escala 2)
P4 = L2

Selecione as células P1 até o P4, clique em Inserir > Outros Gráficos > Rosca

Em Ferramentas de Gráfico, clique em Formatar

Em Seleção Atual, na caixa de combinação, selecione Série1 e clique em Formatar Seleção.

Em Opções de Série, na opção Ângulo da primeira fatia, digite 270. Fará com que o nosso gráfico fique na posição desejada. Clique em OK.

No gráfico, clique na quarta porção, ou seja, na parte de baixo do gráfico, aquela que nós não queremos que apareça, e clique em Preenchimento da Forma. Após é só clicar em Sem preenchimento.

Pronto, o primeiro gráfico está feito. Para ficar igual ao meu, é só retirar as legendas e mudar as cores para verde, amarelo e vermelho para as respectivas porções. Você pode diminuir a expessura em Opções de Série, Tamanho do Orifício da Rosca. Neste exemplo eu deixei em 70%

Gráfico de Velocímetro

Para a construção do segundo gráfico, precisamos definir para onde o ponteiro deve apontar (Valor). Primeiro, devemos definir o ângulo do ponteiro. Eu defini a célula N1 com o valor do ângulo para cada valor inteiro, ou seja 180º / Valor máximo = 1,5º. Por exemplo, se o valor lido for de 100, o valor do ângulo será 150º. Para o valor de N2, eu escrevi a seguinte fórmula: (Valor*N1+270)*PI()/180. Para calcular o ângulo e transformar em radianos. Você pode fazer também a seguinte fórmula =RADIANOS(L6*N1+270), dá na mesma.

Para os valores do gráfico, eu coloquei nas células Q1 e Q2 o valor 0 (zero), que será a parte do ponteiro que ficará fixa. Em R1, coloquei a fórmula =sen(N2) e em R2 =cos(N2), que são os valores onde o ponteiro deve apontar.

Selecione as células Q1:R2, clique em Inserir > Dispersão > Dispersão com linhas retas e Marcadores.

Pronto. Agora é só formatar o gráfico de forma transparente:

Em Ferramentas de Gráfico, clique em Formatar

Em Seleção Atual, na caixa de combinação, selecione Área do Gráfico e clique em Formatar Seleção. No Preenchimento, clique em Sem preenchimento. Faça o mesmo processo em Área de Plotagem.

Apague as Linhas de Grade.

IMPORTANTE

Em Ferramentas de Gráfico > Formatar > EixoVertical (Valor) > Máximo > Mudar para o valor 1,0 e em Tipo de marca de escala principal e Rótulos do eixo, definir como Nenhum. Antes de fechar a janela, clique em Cor da Linha e defina como Sem linha.

Em Ferramentas de Gráfico > Formatar > EixoHorizontal (Valor) > Máximo > Mudar para o valor 1,0 > Mínimo para -1,0 e em Tipo de marca de escala principal e Rótulos do eixo, definir como Nenhum. Antes de fechar a janela, clique em Cor da Linha e defina como Sem linha.

Isso garantirá queaparecerá apenas o ponteiro e que o tamanho da seta permanecerá sempre proporcional, independente do valor.

Gráfico de Velocímetro

Agora é só sobrepor o grafico 2 no gráfico 1. Uma dica é colocar o valor em 60 (90º) e colocar bem no centro da escala, e definir as extremidades para o lado interno da rosca.

Gráfico de Velocímetro

Bem, é isso aí. Se você utilizou as minhas dicas o gráfico deve ter ficado parecido. Se algo não funcionou ou se você estiver utilizando uma versão diferente, por favor deixe um comentário.

Para este exemplo, eu utilizei o Microsoft Excel 2010.

Veja o filme da criação deste gráfico em: http://youtu.be/4Dk-zPUpjqs