Comece com vários anos de dados.
Digamos que você tenha registros de detalhes de dois anos. Cada registro tem uma data diária. Ao construir uma tabela dinâmica a partir deste relatório, você terá centenas de linhas de datas diárias na tabela dinâmica. Este não é muito um resumo.
Escolha uma dessas células de data na tabela dinâmica. Na guia Analyze na faixa de opções, escolha Group Field.
Por estar em um campo de data, você obtém esta versão da caixa de diálogo Agrupamento. Nele, desmarque Meses e selecione Anos.
As datas diárias são acumuladas em anos. Mova o campo Data de ROWS para COLUMNS.
O resultado é quase perfeito. Mas em vez de um total geral na coluna D, você provavelmente deseja uma variação percentual.
Para se livrar da coluna Total geral, na guia Projeto escolha Totais gerais, Ligado para colunas apenas. (Eu concordo, esta é uma das formulações mais estranhas no Excel.)
Para construir a coluna de variância, você precisa escrever uma fórmula fora da tabela dinâmica que aponte para dentro da tabela dinâmica. Não toque no mouse ou nas teclas de seta enquanto constrói a fórmula, ou a desagradável função GETPIVOTDATA aparecerá. Em vez disso, simplesmente digite =C5/B5-1e pressione Enter.
Obrigado a Александр Воробьев por sugerir esta dica.
Assistir vídeo
- Comece com vários anos de dados
- Inserir, tabela dinâmica
- Arraste o campo de data para a área da linha
- Excel 2016: Pressione Ctrl + Z para desagrupar datas
- Arraste a receita para a área de valores
- Selecione qualquer data na área da linha
- Usar campo de grupo
- Escolha meses e anos
- Como adicionar subtotais ao campo de anos após agrupar uma tabela dinâmica
- Use a forma tabular em uma tabela dinâmica para dar a cada campo de linha sua própria coluna
- Repita todos os rótulos de linha para preencher os espaços em branco em uma tabela dinâmica
- Arraste anos para a área da coluna
- Clique com o botão direito no cabeçalho da coluna Total geral e remova
- Como evitar a função GetPivotData quando a fórmula aponta para uma tabela dinâmica
- Para construir uma variação, digite a fórmula sem o mouse ou as setas
- Obrigado a Александр Воробьев por sugerir esta dica
Transcrição de vídeo
Aprenda Excel com o podcast MrExcel, episódio 1998. Tabela Dinâmica ano após ano.
Estarei enviando podcast para este livro inteiro, vá, clique no “i” no canto superior direito, para a lista de reprodução MrExcel Excel.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Hoje, o primeiro tópico do livro sobre tabelas dinâmicas e como criar um relatório anual. Então, estamos começando com o conjunto de dados, que abrange dois anos, vejam, vamos de 2018 a 2019. Vou criar uma Tabela Dinâmica, Inserir, Tabela Dinâmica, OK, tudo bem, vamos para um nova planilha.
Na nova planilha, vamos pegar o campo Data e arrastá-lo para a área Linhas. E então, você vê, no Excel 2016 ele agrupa as coisas automaticamente. Agora eu sei que a maioria das pessoas ainda está no Excel 2013, talvez no Excel 2010, então vou pressionar Ctrl + Z para desfazer. Essa é a experiência que você terá em 2010 e 2013, vamos pegar o campo Receita e arrastá-lo para a área Valores. Então, começamos com um relatório feio, haverá centenas e centenas de linhas, porque é todo santo dia que tínhamos vendas. Mas vou para o primeiro campo de data, primeiro campo de data, vá para as Ferramentas de Tabela Dinâmica, Analisar, em 2013 ou 2016 e 2010 era chamado de Opções, e escolha Campo de Grupo. Certifique-se de escolher uma data antes de escolher o campo de grupo e acumular até meses e anos. Ou trimestres e anos,ou Meses e trimestres e anos, clique em OK. E o que temos, é um novo campo virtual chamado Years aqui na área de Rows.
Agora, alguns aborrecimentos. É irritante que eles não nos forneçam os totais do ano, é fácil consertar isso, embora no meu caso eu não precise consertar. Clicamos em Configurações de Campo e Automático, porque não estou planejando deixar os Anos aqui. Obviamente, sempre que crio uma Tabela Dinâmica, vou para Design e digo Mostrar na forma tabular para colocar tudo em sua própria coluna. Em seguida, repita todas as etiquetas de item para preencher os espaços em branco no lado esquerdo. Novamente, isso não seria necessário aqui, porque não estou planejando deixar os Anos onde está, vou pegar os Anos e arrastá-los para as Colunas, e esse é o relatório que eu estava buscando.
Tudo bem, então temos Meses descendo do lado esquerdo, 2018 a 2019. Na coluna D, o que eu realmente quero, não quero um Grande Total, vou clicar com o botão direito e dizer Remover o Grande Total e agora estamos fora de uma tabela dinâmica. Então,% Change, é muito tentador construir esta fórmula usando o mouse ou as teclas de seta, mas você não pode, porque você obterá a função… get Pivot Data, que não pode ser copiada facilmente. Então = C5 / B5-1, vamos formatar isso como uma porcentagem e clicar duas vezes para copiar, certo. Belo relatório mostrando onde estávamos no ano passado, onde estávamos neste ano e a variação percentual de ano para ano.
Bem, esta dica e outras 40, estão todas neste livro. Vá em frente, clique naquele “i” no canto superior direito, ao lado do livro. Tudo bem, então você começa com o conjunto de dados com vários anos, insere, tabela dinâmica, arraste o campo de data para a área da linha. E no Excel 2016 pressione Ctrl + Z para desfazer suas atualizações de agrupamento automático. Por que simplesmente não deixamos isso aqui? Bem, na verdade depende. Se você tivesse dados de, digamos, 2 de janeiro a 30 de dezembro, eles não agrupariam esses dados corretamente. É necessário gastar um ano inteiro para você obter o campo ano. Pode ser que funcione para você, eu sou um pouco maníaco por controle, prefiro apenas ter controle, é por isso que eu desfaço. Há: arraste a receita para a área de valores, selecione qualquer célula, qualquer data na área da linha e, em seguida, Campo de grupo, escolha Meses e Anos,arraste anos sobre a área da coluna, clique com o botão direito no Total geral para remover e então crie uma variação, digite a fórmula, não use o mouse ou as teclas de seta.
Não tenho ideia de como pronunciá-la, mas agradeço a este leitor (Александр Воробьев) por sugerir esta dica e obrigado pela visita. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast1998.xlsx
