Agora vamos falar sobre visualização e percepção confortável da informação, que são extremamente importantes na análise de dados. É muito conveniente quando você pode comparar vários dados ao mesmo tempo em um diagrama, acompanhar a dinâmica desses indicadores, enquanto tudo deve estar claro e não se fundir em uma “pasta”. Para fazer isso, você pode criar gráficos compostos de diferentes tipos no Excel. O que é um diagrama misto e como criá-lo, consideraremos mais adiante.

Então criamos um gráfico. Agora precisamos colocar uma segunda escala no diagrama. Por exemplo, o percentual dos custos logísticos em relação ao custo das vendas ou que participação esse indicador ocupa no total geral. Aqueles. Este é o caso quando é necessário combinar vários tipos em um gráfico, por exemplo, Histograma e Gráfico de Linhas.

Primeiro, todos os diagramas são do mesmo tipo. Agora selecionamos uma série e alteramos o tipo de gráfico dela. Clicando em uma linha clique com o botão direito mouse, selecione “Alterar tipo de gráfico de série” e selecione o tipo “Gráfico” para a série com porcentagens.

Se o gráfico ficar invisível no diagrama, é necessário adicionar um eixo auxiliar - clique com o botão direito na linha ou no nome da legenda, na janela que aparece selecione - Formatar série de dados.

Na janela que se abre, procure Parâmetros de linha e altere a caixa de seleção para Ao longo do eixo auxiliar.

Este diagrama demonstra muito bem os rácios dos indicadores (ver Figura 1).

Se os indicadores de receita forem muito altos (quanto foi gasto por 1 rublo de renda), então os indicadores do 12º mês podem ser considerados ideais.

Sombreando uma área de gráfico no Excel

Esse é um truque interessante do Excel, não me lembro onde aprendi. Acontece muito claramente se você sombrear parte do diagrama para visualização. Por exemplo, separe o início de um novo período, no exemplo o início de um novo ano (mês 1)

É necessário adicionar outro histograma desde o início do ano com dados iguais a 1% (indicador completo da coluna). Fazemos uma coluna ou linha separada com dados de 1%, começando no ano novo.

Clique em Selecionar fonte - Adicionar - Adicionar dados de 1%, ou seja, adicione outra linha de dados. Repetimos todos os passos para obter outro histograma ao longo do eixo auxiliar.

Para esse histograma, clique com o botão direito em Opções de linha - Espaço lateral no valor 0 para que as colunas se estendam por toda a área.

Há algum tempo publicamos a primeira parte do nosso tutorial sobre criação de gráficos em Excel para iniciantes, no qual foi ministrado instruções detalhadas, como construir um gráfico no Excel. E a primeira pergunta feita nos comentários foi: “ Como mostrar dados localizados em diferentes planilhas em um gráfico?“. Quero agradecer ao leitor que fez esta ótima pergunta!

Na verdade, os dados de origem que precisam ser mostrados no diagrama nem sempre estão localizados na mesma planilha. Felizmente, Excel permite exibir dados localizados em duas ou mais planilhas em um gráfico. A seguir faremos isso passo a passo.

Como criar um gráfico a partir de várias planilhas do Excel

Suponha que você tenha várias planilhas do Excel contendo dados de renda de vários anos e queira criar um gráfico usando esses dados para mostrar a tendência geral.

1. Crie um gráfico usando os dados da primeira planilha

Abra a primeira planilha do Excel, selecione os dados que deseja exibir no gráfico, abra a aba Inserir(Inserir) e na seção Diagramas(Gráficos) selecione tipo desejado diagramas. Para nosso exemplo escolheremos Histograma volumétrico empilhado(Coluna de pilha).

2. Adicione uma segunda linha de dados de outra planilha

Clique no diagrama que você acabou de criar para que um grupo de guias apareça na faixa do menu Trabalhando com gráficos(Ferramentas de gráfico), abra a guia Construtor(Desenho) e pressione o botão Selecione os dados(Selecione Dados). Ou clique no ícone Filtros de gráfico(Filtros de gráfico) à direita do gráfico e na parte inferior do menu que aparece, clique no link Selecione os dados(Selecione Dados).

Na caixa de diálogo Selecionando uma fonte de dados(Selecionar fonte de dados) clique no botão Adicionar(Adicionar).

Agora vamos adicionar uma segunda linha de dados de outra planilha. Este ponto é muito importante, por isso siga atentamente as instruções. Depois de pressionar o botão Adicionar A caixa de diálogo (Adicionar) será aberta Mudança de linha(Editar série), aqui você precisa clicar no ícone de seleção de intervalo próximo ao campo Valores(Valores da série).

Caixa de diálogo Mudança de linha(Editar série) entrará em colapso. Clique na guia da planilha que contém o próximo dado que você deseja mostrar no gráfico do Excel. Ao mudar para outra planilha, a caixa de diálogo Mudança de linha(Editar série) permanecerá na tela.

Na segunda planilha, selecione a coluna ou linha de dados que deseja adicionar ao gráfico do Excel e clique novamente no ícone de seleção de intervalo para abrir a caixa de diálogo. Mudança de linha(Editar série) voltou ao tamanho original.

Agora clique no ícone de seleção de intervalo próximo ao campo Nome da série(Nome da série) e selecione a célula que contém o texto que deseja usar como nome da série de dados. Clique no ícone de seleção de intervalo novamente para retornar à caixa de diálogo original Mudança de linha(Editar série).

Como você pode ver na imagem acima, associamos o nome da linha à célula B1, que contém o cabeçalho da coluna. Em vez de fazer referência ao cabeçalho da coluna, você pode inserir o nome como uma sequência de texto entre aspas, por exemplo:

="Segunda linha de dados"

Os nomes das séries de dados aparecerão na legenda do gráfico, por isso é melhor criar nomes significativos e significativos. Nesta fase o resultado deve ser algo assim:

3. Adicione ainda mais séries de dados (opcional)

Se o gráfico precisar mostrar dados de mais de duas planilhas, repita a etapa anterior para cada série de dados que deseja adicionar ao gráfico. Quando terminar, clique em OK na caixa de diálogo Selecionando uma fonte de dados(Selecione fonte de dados).

4. Personalize e melhore o gráfico (opcional)

Quando você cria gráficos no Excel 2013 e 2016, itens como título e legenda do gráfico normalmente são adicionados automaticamente. Nosso gráfico, que foi compilado a partir do conteúdo de várias planilhas, não adicionou título e legenda automaticamente, mas iremos corrigir isso rapidamente.

Selecione o gráfico, clique no ícone Elementos do gráfico(Elementos do gráfico) na forma de uma cruz verde próximo ao canto superior direito do gráfico e verifique os parâmetros necessários:

Criando um gráfico a partir de uma tabela de resumo

A solução mostrada acima só é útil se os dados que você deseja exibir no gráfico estiverem organizados na mesma ordem em todas as planilhas, ou seja, na primeira linha - Laranjas, no segundo - Maçãs etc. Caso contrário, os gráficos ficarão ilegíveis.

Neste exemplo, o layout dos dados é o mesmo nas três planilhas. Se você precisar construir um gráfico a partir de tabelas muito maiores e não tiver certeza de que a estrutura de dados nessas tabelas é a mesma, seria mais sensato criar primeiro uma tabela de resumo e, em seguida, criar um gráfico a partir da tabela de resumo resultante. . Para preencher a tabela final com os dados necessários, você pode usar a função PROCV(PROCV) .

Por exemplo, se as planilhas discutidas neste exemplo contiverem dados em ordens diferentes, podemos fazer uma tabela de resumo a partir deles usando esta fórmula:

PROCV(A3,"2014"!$A$2:$B$5,2,FALSO)
=PROCV(A3,"2014"!$A$2:$B$5,2,FALSO)

E obtenha este resultado:

Configurando um gráfico no Excel criado a partir de diversas planilhas

Pode acontecer que depois de concluir a criação de um diagrama a partir de duas ou mais planilhas, fique claro que ele deve ser construído de forma diferente. E como criar tal gráfico no Excel não é um processo tão rápido quanto criar um gráfico a partir de uma única planilha, provavelmente será mais fácil refazer o gráfico criado do que criar um novo do zero.

Em geral, as opções de um gráfico do Excel criado a partir de várias planilhas não são diferentes das opções de um gráfico do Excel normal. Você pode usar um grupo de guias Trabalhando com gráficos(Ferramentas de gráficos) ou menu de contexto ou os ícones de configurações no canto superior direito do gráfico para personalizar elementos básicos, como título do gráfico, títulos dos eixos, legenda, estilo do gráfico e muito mais. Instruções passo a passo Como configurar esses parâmetros pode ser encontrado no artigo sobre configuração de gráficos no Excel.

Se precisar alterar a série de dados mostrada no diagrama, você pode fazer isso de três maneiras:

Alterando uma série de dados usando a caixa de diálogo Selecionar fonte de dados

Abrir caixa de diálogo Selecionando uma fonte de dados(Selecionar fonte de dados), para isso na aba Construtor(Design) clique no botão Selecione os dados(Selecione os dados).

Para alterar uma série de dados, clique nela e, a seguir, clique no botão Mudar(Editar) e edite os parâmetros Nome da série(Nome da série) ou Significado(Valores da série) como nós. Para alterar a ordem das séries de dados em um gráfico, selecione a série de dados e mova-a para cima ou para baixo usando as setas apropriadas.

Para ocultar uma linha de dados, basta desmarcar a caixa na lista Elementos de legenda(Entradas de legenda) no lado esquerdo da caixa de diálogo. Para remover completamente uma série de dados do gráfico, selecione-a e clique no botão Excluir(Remover).

Oculte ou mostre séries de dados usando o ícone “Filtros de gráfico”

Outra forma de controlar a série de dados que aparece em um gráfico do Excel é usar o ícone Filtros de gráfico(Filtros de gráfico). Se você clicar no diagrama, este ícone aparecerá imediatamente à direita.

Para ocultar dados, clique no ícone Filtros de gráfico(Filtros de gráfico) e desmarque a série ou categoria de dados correspondente.
Para alterar uma série de dados, clique no botão Alterar linha(Editar série) à direita do nome da série. A caixa de diálogo familiar aparecerá Selecionando uma fonte de dados(Selecionar fonte de dados), onde você pode fazer as configurações necessárias. Para botão Alterar linha(Editar série) apareceu, basta passar o mouse sobre o nome da série. Nesse caso, a linha de dados sobre a qual o mouse está passando é destacada em cores no diagrama para facilitar a compreensão de qual elemento será alterado.

Alterando uma série de dados usando uma fórmula

Como você provavelmente sabe, cada série de dados no Excel é definida por uma fórmula. Por exemplo, se selecionarmos uma das séries de dados no gráfico que acabamos de criar, a fórmula para a série de dados ficará assim:

LINHA("2013"!$B$1;"2013"!$A$2:$A$5;"2013"!$B$2:$B$5;1)
=SÉRIE("2013"!$B$1,"2013"!$A$2:$A$5,"2013"!$B$2:$B$5,1)

Cada fórmula de série de dados consiste em vários elementos básicos:

LINHA([ nome_linha];[nome_da_categoria];intervalo_dados;número_linha)

Ou seja, nossa fórmula pode ser decifrada da seguinte forma:

O nome da linha ('2013'!$B$1) é retirado da célula B1 em uma folha 2013 .
Os nomes das categorias ('2013'!$A$2:$A$5) são retirados das células A2:A5 em uma folha 2013 .
Dados ('2013'!$B$2:$B$5) retirados das células B2: B5 em uma folha 2013 .
O número de série (1) indica que esta série ocupa o primeiro lugar no gráfico.

Para alterar uma série de dados específica, selecione-a no gráfico e faça as alterações necessárias na barra de fórmulas. Claro, você precisa ter muito cuidado ao alterar a fórmula de uma série de dados, pois é fácil cometer um erro, principalmente se durante a edição os dados de origem estiverem contidos em planilhas diferentes e não diante de seus olhos. No entanto, se você se sentir mais confortável trabalhando com fórmulas do que com uma interface normal, esse método de fazer pequenas correções pode ser adequado.

Isso é tudo por hoje! Obrigado pela sua atenção!

Gráficos e tabelas (5)
Trabalhando com projeto VB (12)
Formatação Condicional (5)
Listas e intervalos (5)
Macros (procedimentos VBA) (64)
Diversos (41)
Bugs e falhas do Excel (4)

Dois em um – como fazer?

Provavelmente muitos de vocês fazem todos os tipos de relatórios no Excel e constroem gráficos e tabelas com base neles. Ou eles querem aprender como fazer isso. E, claro, todo mundo quer que este gráfico seja bonito e impressionante, mas ao mesmo tempo reflita toda a imagem. Talvez você já tenha visto gráficos nos quais os dados são exibidos tanto na forma de uma curva quanto na forma de colunas ou gráfico de pizza ao mesmo tempo. Esses diagramas também são chamados de mistos. Hoje vou contar brevemente como combinar vários tipos diferentes em um diagrama e obter algo assim:

Para construir tal diagrama, precisamos de uma tabela com dados de origem e pelo menos duas linhas de dados. Para treinamento, você pode baixar um arquivo de exemplo no final do artigo - todos os dados iniciais estão lá. Primeiro precisamos determinar o tipo do nosso diagrama.

Importante: se você planeja usar o tipo em seu diagrama Histograma (colunas), então, ao criar um gráfico, você deverá selecionar esse tipo de gráfico específico. E depois adicione outros tipos de diagramas, porque... tenta atribuir um tipo Histograma (colunas) depois de todos os outros resultará em todas as linhas se tornando como Histograma (colunas). Além disso, sem quaisquer avisos.
Você também deve se lembrar de que não pode combinar tipos de diagramas tridimensionais e planos. Mas se você tentar combinar tipos planos e volumétricos, o próprio Excel irá avisá-lo sobre isso, então não é tão assustador.

Após selecionar o tipo de gráfico inicial, clique uma vez em uma das séries de dados do gráfico (todas as colunas desta linha serão destacadas). Botão direito do mouse:

Selecione o tipo e veja como o diagrama mudou.
Pequena nuance: se seus dados tiverem uma grande diferença entre si em uma tabela (por exemplo, valores de vendas em rublos e quantidade de mercadorias vendidas) e você deseja comparar esses dados combinando-os em um diagrama - inicialmente você não terá sucesso, porque a diferença nos dados numéricos entre as séries diferirá dezenas ou até centenas de vezes. O que tornará os dados quantitativos praticamente invisíveis. Para evitar isso, você pode aproveitar uma oportunidade interessante - plotar esses dados ao longo de um eixo diferente. Botão direito do mouse na linha - grupo Opções de série-Eixo Secundário:

Além disso, se você clicar com o botão direito em uma linha de dados e selecionar já Formatar série de dados, então você pode percorrer outros parâmetros e alterar a cor de preenchimento de uma linha separada, borda, adicionar sombras, etc. Isso dá bastante espaço para a criatividade e não há mais nada a dizer - você tem que tentar e criar :-)

Baixe exemplo:

(47,0 KiB, 14.126 downloads)

O artigo ajudou? Compartilhe o link com seus amigos! Tutoriais em vídeo

("Barra inferior":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"esquerda","texteffectslidedistance" :30,"textefeitodelay":500,"textefeitoseparado":false,"textefeito1":"slide","textefeitoslidedirection1":"direita","textefeitoslidedistance1":120,"textefeitoeasing1":"easeOutCubic","textefeitoduração1":600 ,"textefeitodelay1":1000,"textefeito2":"slide","textefeitoslidedirection2":"direita","textefeitoslidedistance2":120,"textefeitodelay2":"easeOutCubic","textefeitoduration2":600,"textefeitodelay2":1500," textcss":"display:bloco; alinhamento de texto:esquerda;","textbgcss":"display:absoluto;cor de fundo:#333333; ","titlecss":"display:bloco; posição:relativo; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:bloco; posição:relativo; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; cor:#fff; margin-top:8px;","buttoncss":"display:block; posição:relativo; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Um histograma no Excel é uma forma de criar um gráfico visual que mostra alterações em vários tipos de dados durante um período de tempo.

Um histograma é útil para ilustrar e comparar diferentes parâmetros. Vejamos os tipos mais populares de histogramas e aprendamos como construí-los.

Como construir um histograma atualizado?

Temos dados sobre vendas de diferentes tipos de laticínios para cada mês de 2015.

Vamos construir um histograma atualizado que responderá às alterações feitas na tabela. Selecione todo o array incluindo o cabeçalho e clique na guia INSERT. Vamos encontrar DIAGRAMAS - HISTOGRAMA e selecionar o primeiro tipo. É chamado de HISTOGRAMA COM GRUPO.

Recebemos um histograma cujo tamanho do campo pode ser alterado. Este diagrama mostra claramente, por exemplo, que as maiores vendas foram de leite em Novembro, e as menores de natas em Junho.

Se fizermos alterações na tabela, aparência o histograma também mudará. Por exemplo, em vez de 1.400 em janeiro para o kefir, vamos colocar 4.000. Vemos como a coluna verde voou para cima.

Histograma empilhado

Agora vamos ver como construir um histograma empilhado no Excel. Outro tipo de histograma que permite exibir dados em porcentagem. É construído exatamente da mesma maneira, mas um tipo diferente é escolhido.

Obtemos um diagrama onde você pode ver que, por exemplo, em janeiro foi vendido mais leite do que kefir ou creme. E em agosto, em comparação com outros laticínios, foi vendido pouco leite. Etc.

Histogramas no Excel podem ser modificados. Então, se clicarmos com o botão direito em espaço vazio gráfico e selecione CHANGE TYPE, podemos modificá-lo um pouco. Vamos mudar o tipo do nosso histograma de empilhado para normalizado. O resultado será o mesmo gráfico, mas o eixo Y mostrará as proporções em termos percentuais.

Da mesma forma, você pode fazer outras alterações no histograma, que foi o que fizemos:

mudou a fonte para Arial e mudou sua cor para roxo;
fiz um sublinhado com linha pontilhada;
moveu a legenda um pouco mais para cima;
adicionou legendas às colunas.

Como combinar um histograma e um gráfico no Excel?

Alguns conjuntos de dados requerem a construção de diagramas mais complexos que combinam vários tipos de dados. Por exemplo, um histograma e um gráfico.

Vejamos um exemplo. Primeiro, vamos adicionar outra linha à tabela com os dados, onde é registrada a receita de cada mês. É indicado em rublos.

Agora vamos modificar o diagrama existente. Clique com o botão direito em um espaço vazio e selecione SELECIONAR DADOS. Um campo aparecerá solicitando que você selecione um intervalo diferente. Selecionamos novamente a tabela inteira, mas desta vez incluindo a linha com receita.

O Excel expandiu automaticamente o intervalo de valores ao longo do eixo Y, de modo que os dados sobre o número de vendas permaneceram na parte inferior, na forma de colunas invisíveis.

Mas tal histograma está incorreto, porque em um diagrama temos números em rublos e equivalentes quantitativos (rublos e litros). Portanto, mudanças precisam ser feitas. Vamos transferir dados de receita para lado direito. Clique com o botão direito nas colunas roxas, selecione FORMATO DE SÉRIE DE DADOS e selecione EIXO AUXILIAR.

Vemos que o cronograma mudou imediatamente. Agora a barra roxa de receita tem sua própria área de valor (à direita).

Mas isso ainda não é muito conveniente, porque as colunas quase se fundem. Portanto, realizaremos mais uma ação adicional: clicar com o botão direito nas colunas roxas e selecionar ALTERAR TIPO DE GRÁFICO PARA SÉRIE. Aparecerá uma janela na qual selecionamos um gráfico, o primeiro tipo.

Obtemos um diagrama muito claro, que é uma combinação de um histograma e um gráfico. Vemos que a receita máxima foi em janeiro e novembro, e a mínima em agosto.

O artigo de hoje será muito curto. Vou lhe dizer como exibir dois diagramas simultaneamente em uma área do gráfico. tipos diferentes informações gráficas, como gráficos de barras e gráficos. Uma tabela simples com números não é muito visual e os gráficos costumam ser mais úteis para análise (pelo menos para mim).

Aqui está meu exemplo de trabalho hoje. Naturalmente, encontrei os dados: não é à toa que, ao se candidatarem a um emprego, exigem assinatura de não divulgação de informações.

Então, existe um protocolo de laminação, onde temos dados sobre a redução, a força média de laminação em cada passe e a temperatura do metal na saída dos rolos após cada passe. Seria conveniente ver os valores de pelo menos dois parâmetros em um gráfico: desta forma vemos a combinação desses parâmetros em cada passagem e ao mesmo tempo o quadro geral da mudança em cada um dos parâmetros ao longo do passa (é imediatamente claro que à medida que a temperatura do metal diminui, sua resistência à deformação aumenta).

A pergunta pode ser compreensível: “por que não construir apenas dois gráficos, por que dois tipos diferentes de diagramas?” Resposta: porque as unidades de medida e, consequentemente, a ordem dos próprios números podem ser diferentes. Por exemplo, a temperatura está na faixa de 700-1000°C e as forças de rolamento estão na faixa de 10-70 MN. Vamos plotá-los em um gráfico, e o gráfico da força de rolamento ficará significativamente abaixo do gráfico da temperatura. É necessário que cada parâmetro tenha seu próprio eixo vertical. É por isso que precisamos de diferentes tipos de gráficos.

Então, vamos lá.

1. Primeiro, construímos um gráfico de barras que conterá informações sobre a distribuição da compressão entre as passagens. Caminho: “inserir/histograma/histograma” (ver figura).

2. Selecione os dados: no eixo horizontal – o número de passadas (coluna B), no eixo vertical – a força de rolamento (coluna C).

3. Agora vamos adicionar uma segunda linha de dados – a temperatura do metal na saída dos rolos (coluna D). Até agora recebemos uma imagem não muito digerível, pois a ordem dos números é diferente.

4. Agora o passo mais importante: você precisa alterar o tipo de “diagrama” de temperatura. Clique com o botão direito em qualquer coluna do gráfico de temperatura, selecione o menu “Alterar tipo de gráfico para série” e selecione o tipo “gráfico de dispersão”.

5. Isto é o que obtivemos:

Vemos que agora temos dois eixos verticais e dois eixos horizontais, cada um para o seu próprio gráfico.

6. Resta trabalhar com os valores máximo e mínimo nos eixos, caso contrário, por enquanto o diagrama e o gráfico vivem por conta própria. Os valores mínimo e máximo no eixo superior devem ser iguais aos do fundo. Clique com o botão direito no eixo, selecione o item de menu “Formatar Eixo” e defina os valores necessários. Você também pode alterar o valor mínimo no eixo vertical (eixo da temperatura): podemos definir “mínimo 700”.

7. Você também pode plotar os valores em gráficos. Novamente, clique com o botão direito no gráfico desejado e selecione “adicionar rótulos de dados”.

Isto é o que terminamos: