Técnica de tabela dinâmica do Excel para calcular a porcentagem de crescimento ano após ano para cada produto, cada cliente ou qualquer coisa.
Assistir vídeo
- Um visualizador baixa dados de um sistema onde cada item é separado por Alt + Enter
- Precisa VLOOKUP cada item na célula
- Use o Power Query para dividir os itens, faça VLOOKUP e junte-os novamente
- É como fazer um ConcatenateX no DAX (que não existe) ou um TEXTJOIN (CHAR (10) no Office 365 Excel
- Construí uma solução usando o Power Query, incluindo a ferramenta Structured Column do Extract As
- Esse recurso funciona apenas em uma lista, não em uma tabela, então usei a função Table.Column para converter a tabela em uma lista.
Transcrição de vídeo
Aprenda Excel, Podcast Episódio 2152: Porcentagem de crescimento anual em uma tabela dinâmica.
Ei. Bem-vindo de volta ao netcast. Sou Bill Jelen. Eu estava em Dallas para o Excelapalooza 6 fazendo 3 dias de seminários lá e esses dois caras da Suécia estavam no meu seminário - Tobias e Robert. Tobias tem seu próprio livro de mesa dinâmica que ele escreveu, e ele estava no meu seminário de mesa dinâmica, e eu estava mostrando uma maneira de fazer um crescimento ano após ano e Tobias tinha um caminho melhor a seguir.
Então, vamos fazer isso. INSERT, PIVOT TABLE, OK. Colocaremos DATAS no lado esquerdo e pressionarei CONTROL + Z para voltar às datas diárias como essa e, em seguida, RECEITA, e escolheremos o primeiro campo DATA. Esse era o comportamento no Excel 2010, Excel 2013. CAMPOS DE GRUPO, digamos que você queira agrupar por MESES e ANOS. Tudo bem. Funciona bem. Pegue o campo ANOS e mova-o para COLUNAS.
Agora, não queremos um GRAND TOTAL aqui, então clicamos com o botão direito e REMOVEMOS GRAND TOTAL, e este é o ponto onde as pessoas gostariam de ver o crescimento, o crescimento percentual de 2016 sobre 2015 - este ano em relação ao anterior ano - e sempre tenho que construir isso fora da tabela dinâmica porque eu uso agrupamento, portanto, é ilegal criar um ITEM CALCULADO. Não pode fazer isso, certo?
Então, estou sempre preso aqui. Temos o problema GetPivotData e todas essas coisas, e Tobias diz, oh, não, você pode fazer isso. Basta preencher o campo REVENUE mais uma vez, certo? Então, agora temos SUM OF REVENUE e SUM OF REVENUE 2. Será assim. Então, temos 2015, 2016. A SOMA DA RECEITA, vamos para esse campo, clique duas vezes, MOSTRAR VALORES COMO, e o cálculo será a% DIFERENÇA DE, e o CAMPO BASE será ANO, e o ITEM BASE vai ser ANTERIOR, certo? Então, para 2016, ele vai calcular 287 ÷ 262 - 1. Ele não saberá o que fazer para 2015 porque não tem dados de 2014. Então, essa coluna vai aparecer em branco. Clique OK. Simples o suficiente. Clique com o botão direito e OCULTAR.
Tudo bem. Agora, a vantagem da versão de Tobias é que à medida que esta tabela dinâmica encolhe ou aumenta - digamos que adicionamos segmentadores e só tivemos alguns meses ou algo parecido - o cálculo aqui vai diminuir ou aumentar com a tabela dinâmica, enquanto o meu, como os cálculos estão fora da tabela dinâmica, simplesmente não vai funcionar tão bem.
Agora, ei, se você pode ler sueco, por suposto confira este livro de Tobias nas tabelas dinâmicas. Ótimo livro, mas, se você preferir livros em inglês, dê uma olhada em meu livro Power Excel With, the 2017 Edition.
Tudo bem. Portanto, a meta é construir um relatório ano a ano em uma tabela dinâmica e mostrar a% de crescimento. Sempre faço esse cálculo fora da tabela dinâmica, mas isso tem problemas, como o problema GetPivotData, e a fórmula precisa ser tratada se a tabela dinâmica encolher ou aumentar. Então, usando este método de Tobias, você pode construir o cálculo na tabela dinâmica, adicionar receita como uma segunda vez, alterar esse cálculo para a% de mudança de, ANOS, item ANTERIOR e então você tem que ocultar a coluna extra. Ótimo, ótimo truque de Tobias.
Bem, ei. Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2152.xlsm
