Você odeia a função GETPIVOTDATA do Excel? Por que isso aparece? Como você pode prevenir isso? Existe um bom uso para GETPIVOTDATA?
A maioria das pessoas encontra GETPIVOTDATA pela primeira vez quando tenta construir uma fórmula fora de uma tabela dinâmica que usa números na tabela dinâmica. Por exemplo, essa porcentagem de variação não será copiada para os outros meses devido ao Excel inserir funções GETPIVOTDATA.
O Excel insere GETPIVOTDATA sempre que você usa o mouse ou as teclas de seta para apontar para uma célula dentro da tabela dinâmica enquanto cria uma fórmula fora da tabela dinâmica.
A propósito, se você não quiser que a função GETPIVOTDATA apareça, simplesmente digite uma fórmula como = D5 / C5-1 sem usar o mouse ou as teclas de seta para apontar para as células. Essa fórmula copia sem problemas.
Aqui está um conjunto de dados que contém um número de plano por mês, por loja. Também há vendas reais por mês por loja para os meses que estão completos. Seu objetivo é construir um relatório que mostre os valores reais para os meses concluídos e planeje os meses futuros.
Construa uma tabela dinâmica com Armazenar em ROWS. Coloque o mês e digite nas COLUNAS. Você obtém o relatório mostrado abaixo, com janeiro real, plano de janeiro e o completamente absurdo janeiro real + plano.
Se você selecionar uma célula de mês e ir para Configurações de campo, poderá alterar os subtotais para Nenhum.
Isso remove o plano real + inútil. Mas você ainda precisa se livrar das colunas de planos de janeiro a abril. Não há uma boa maneira de fazer isso dentro da tabela dinâmica.
Portanto, seu fluxo de trabalho mensal torna-se:
- Adicione os valores reais do novo mês ao conjunto de dados.
- Crie uma nova tabela dinâmica a partir do zero.
- Copie a tabela dinâmica e cole como valores para que não seja mais uma tabela dinâmica.
- Exclua as colunas de que você não precisa.
Existe uma maneira melhor de ir. A figura muito pequena a seguir mostra uma nova planilha do Excel adicionada à pasta de trabalho. Isso tudo é apenas Excel puro, sem tabelas dinâmicas. A única mágica é uma função IF na linha 4 que alterna de Real para Plano com base na data na célula P1.
A primeira célula que precisa ser preenchida é janeiro, reais para Baybrook. Clique na célula e digite um sinal de igual. Usando o mouse, navegue de volta à tabela dinâmica. Encontre a célula para Reais de janeiro para Baybrook. Clique na célula e pressione Enter. Como de costume, o Excel cria uma daquelas funções GETPIVOTDATA irritantes que não podem ser copiadas.
Mas hoje, vamos estudar a sintaxe de GETPIVOTDATA.
O primeiro argumento abaixo é o campo numérico "Vendas". O segundo argumento é a célula onde reside a tabela dinâmica. Os pares restantes de argumentos são nome e valor do campo. Você vê o que a fórmula gerada automaticamente fez? Ele codificou "Baybrook" como o nome da loja. É por isso que você não pode copiar essas fórmulas GETPIVOTDATA geradas automaticamente. Na verdade, eles codificam os nomes em fórmulas. Mesmo que você não possa copiar essas fórmulas, você pode editá-las. Nesse caso, seria melhor editar a fórmula para apontar para a célula $ D6.
Aqui está a fórmula depois de editá-la. Já se foram "Baybrook", "Jan" e "Real". Em vez disso, você está apontando para $ D6, E $ 3, E $ 4.
Copie esta fórmula e escolha Colar Fórmulas Especiais em todas as outras células numéricas.
Agora, aqui está o seu fluxo de trabalho anual:
- Construa uma tabela dinâmica feia que ninguém nunca verá.
- Configure a planilha de relatório.
A cada mês, você deve:
- Cole novos valores reais abaixo dos dados.
- Atualize a tabela dinâmica feia.
-
Altere a célula P1 na folha de relatório para refletir o novo mês. Todos os números são atualizados.
Alterar Célula P1
Você tem que admitir que usar um relatório simples que extrai números de uma tabela dinâmica oferece o melhor dos dois mundos. Você é livre para formatar o relatório de maneiras que não pode formatar uma tabela dinâmica. Linhas em branco estão bem. Você pode ter símbolos de moeda na primeira e na última linha, mas não entre as duas. Você também obtém sublinhados duplos sob os totais gerais.
Obrigado a @iTrainerMX por sugerir esse recurso.
Assistir vídeo
- GetPivotData acontece quando uma fórmula aponta para dentro de uma tabela dinâmica
- Embora a fórmula inicial esteja correta, você não pode copiar a fórmula
- A maioria das pessoas odeia getpivotdata e deseja evitá-lo
- Método 1: construir uma fórmula sem o mouse ou as teclas de seta
- Método 2: desative GetPivotData permanentemente usando a lista suspensa ao lado de opções
- Mas há uma utilidade para GetPivotData
- Seu gerente deseja um relatório com reais para os meses anteriores e orçamento para o futuro
- O fluxo de trabalho normal seria criar uma tabela dinâmica, converter em valores e excluir colunas
- Removendo subtotais para evitar real + plano de janeiro usando configurações de campo
- Em vez disso, crie uma tabela dinâmica com "muitos" dados
- Use uma planilha de relatório bem formatada
=IF((1+MONTH($P1))>COLUMN(A1),"Actual","Plan")- A partir da primeira célula de dados na planilha, construa uma fórmula com o mouse
- Permitir que GetPivotData aconteça
- Examine a sintaxe de GetPivotData (campo a ser retornado, localização do pivô, pares)
- Altere o valor codificado para apontar para uma célula
- Pressionar F4 três vezes bloqueia apenas a coluna
- Pressionar F4 duas vezes bloqueia apenas a linha
- Colar Fórmulas Especiais
- Fluxo de trabalho no próximo mês: Adicionar dados, atualizar a tabela dinâmica, alterar até a data
- Ultra cuidado para ficar atento a novas lojas
Transcrição do vídeo
Aprenda Excel com o podcast, episódio 2013 - GetPivotData não pode ser totalmente mau!
Estarei enviando um podcast para este livro inteiro, clique no “i” no canto superior direito para se inscrever.
Tudo bem, de volta ao episódio 1998, falei brevemente sobre este problema GetPivotData. Se calcularmos uma% de variância e estivermos fora da tabela dinâmica apontando para dentro, e eu usar o mouse ou a tecla de seta, então 2019 / 2018-1. Esta resposta que vamos obter aqui está correta para janeiro, mas quando clicamos duas vezes para copiá-la, a fórmula não copia, obtemos a resposta de janeiro completa. E quando olhamos para ele, estamos obtendo GetPivotData, eu não digitei GetPivotData, apenas apontei para essas células, e isso começou a acontecer no Excel 2002 sem qualquer aviso. E naquele ponto eu disse que a maneira de evitar isso é digitar a fórmula C5 / B5-1, e você obterá uma fórmula que poderá copiar. Ou se você simplesmente odeia GetPivotData, se for “completamente maligno”, vá para a guia Analisar, nãoA propósito, abra o botão Opções. Volte para a tabela dinâmica, vá para a guia Analisar, abra a lista suspensa ao lado de Opções, desmarque esta caixa, é uma configuração global. Depois de desligá-lo, ele ficará desligado para sempre, certo.
Na maioria das vezes, as perguntas que recebo são “Como desativo GetPivotData?” mas, de vez em quando, recebo alguém que adora GetPivotData. E eu estava almoçando com Rob Collie quando ele ainda estava na Microsoft, e ele disse “Bem, nossos clientes internos adoram GetPivotData”. Eu disse o que? Não, todo mundo odeia GetPivotData! ” Rob diz “Você está certo, fora da Microsoft, absolutamente, eles odeiam GetPivotData”. Estou falando dos contadores dentro da Microsoft, e depois conheci um que agora trabalha para a equipe do Excel, o Carlos, e o Carlos era um dos contadores que usa esse método.
Tudo bem, então aqui está o que temos que fazer. Temos o nosso relatório, um conjunto de dados aqui que para cada mês temos o plano de cada loja e, no final, estamos acumulando os reais. Tudo bem, então temos valores reais de janeiro a dezembro, mas só temos valores reais de alguns meses, os meses que se passaram. E o que nosso gerente quer que façamos é criar um relatório com as lojas no lado esquerdo, apenas as lojas do Texas, é claro, para tornar a vida mais difícil. E depois temos os meses, e se tivermos um real para esse mês, mostramos o real, então janeiro real, fevereiro real, março real, abril real. Mas então, nos meses em que não temos dados reais, mudamos e mostramos o orçamento, então faça o orçamento até dezembro, e então um total totalizando tudo, certo. Bem, quando você tenta criar esta tabela dinâmica, sim,não funciona.
Então insira PivotTable, New Worksheet, você coloca Store no lado esquerdo, o lado que é bonito, coloca Meses no topo, coloca Type no topo, coloca Sales aqui, certo. Então, aqui está o que temos para começar a trabalhar, então temos o plano de janeiro real, o plano de janeiro e o plano plus real de janeiro completamente inútil. Ninguém jamais usará isso, mas posso me livrar dessas colunas cinza, isso é bastante fácil, alguns aqui nesta célula, vá para Configurações de campo e altere os subtotais para nenhum. Mas não há absolutamente nenhuma maneira de eu remover o plano de janeiro que não removerá também o plano abril maio junho julho, tudo bem, não há como me livrar disso. Então, neste ponto, todo mês, estou preso selecionando a tabela dinâmica inteira, indo para Copiar e, em seguida, Colar, Colar valores. Não é mais uma tabela dinâmica,e então começo a excluir manualmente as colunas que não aparecem no relatório.
Certo, esse é o método normal, mas os contadores da Microsoft adicionaram uma etapa extra em janeiro, leva 15 minutos, e essa etapa permite que essa tabela dinâmica viva para sempre, certo? Eu a chamo de tabela dinâmica mais feia do mundo, e os contadores da Microsoft aceitam que esta é a tabela dinâmica mais feia do mundo, mas ninguém verá esse relatório, exceto eles. O que eles fazem é vir aqui para uma nova planilha e criar o relatório que seu gerente deseja. Tudo bem, então aqui estão as lojas no lado esquerdo, até agrupei em Houston, Dallas e outros, é um relatório bem formatado. Eu destaquei os totais, você verá que quando colocamos alguns números, há moeda na primeira linha, mas não nas linhas subsequentes, linhas em branco. Ooh, linhas em branco na tabela dinâmica.E um pouquinho de lógica aqui, onde posso colocar a data final na célula P1, e então tenho uma fórmula aqui que analisa que SE o mês da data final for> esta coluna e, em seguida, colocar a palavra Real, caso contrário coloque a palavra Plano, certo. Então, tudo o que tenho a fazer é mudar isso até a data e, em seguida, a palavra Real mudar para plano, tudo bem.
Agora, aqui está o que fazemos, vamos nos permitir ser GetPivotData'd, certo? Não tenho certeza se isso é um verbo, mas vamos permitir que a Microsoft GetPivotData. Então eu começo a construir uma fórmula com um =, pego o mouse e vou procurar o Baybrook real de janeiro! Volto para a tabela dinâmica mais feia do mundo, encontro Baybrook, encontro janeiro, encontro real e clico em Enter e deixo que eles façam isso comigo, certo, vamos lá, agora temos uma fórmula GetPivotData. Lembro-me do dia em que fiz isso, foi tipo, você sabe, depois que Rob me explicou o que eles estavam fazendo, e eu voltei e tentei. Agora, de repente, durante toda a minha vida, me livrei de GetPivotData, nunca realmente abracei GetPivotData. Então o que é, o primeiro item é o que estamos procurando, aí 'sa campo chamado Vendas, é aqui que começa a tabela dinâmica, e pode ser qualquer célula da tabela dinâmica, eles usam o lado superior esquerdo.
Tudo bem, este é um nome de campo “Store”, e então eles codificaram “Baybrook”, este é um nome de campo “Mês”, eles codificaram “Janeiro”, este é um nome de campo “Tipo”, e eles ' ve codificado como “Real”. É por ISSO que você não pode copiá-lo, porque eles codificaram os valores. Mas os contadores da Microsoft, Carlos e seus colegas de trabalho percebem “Uau, espere um segundo, temos a palavra Baybrook aqui, temos janeiro aqui, temos Real aqui. Precisamos apenas alterar esta fórmula para apontar para as células reais no relatório em vez de ser codificado. ” Tudo bem, então eles chamam isso de parametrizar o GetPivotData.
Remova a palavra Baybrook, venha aqui e clique na célula D6. Agora, eu preciso bloquear isso na coluna, certo, então eu pressiono a tecla F4 3 vezes, obtenho um único $ antes do D, certo. Para o mês de janeiro, removo o janeiro codificado, clico na célula E3, pressiono F4 duas vezes para bloqueá-la na linha, E $ 3. Digite Real, remova a palavra Real, clique em E4, novamente F4 duas vezes, certo, e obtenho uma fórmula que agora puxa os dados de volta. Vou copiar isso e, em seguida, Colar especial, escolher Formatos, alt = "" ESF, ver o F sublinhado ali, ESF Enter, e agora que fiz isso, vou repetir apenas com F4, F4 é um refazer e F4. Tudo bem, agora temos um relatório de boa aparência, tem espaços em branco, tem formatação, tem um único sublinhado contábil em cada seção,na parte inferior, tem o duplo sublinhado contábil.
Certo, você nunca obtém essas coisas em uma tabela dinâmica, isso é impossível, mas este relatório é gerado a partir da tabela dinâmica. Então, o que fazemos quando obtemos os reais de maio, volte aqui, cole-os, atualize a tabela dinâmica mais feia do mundo e, em seguida, aqui no relatório, apenas altere a data final de 30/04 para 31/05. E o que isso faz é que faz com que essa fórmula mude da palavra Plano para Real, que vai e puxa os dados reais do relatório, em vez do plano, certo. Agora, aqui está o que- isso é ótimo, certo? Eu posso ver onde eu faria muito isso se eu ainda, você sabe, trabalhasse em contabilidade.
O que você tem que ter muito cuidado é que se eles constroem uma nova loja, você tem que saber como adicionar manualmente, certo, os dados vão aparecer na tabela dinâmica, mas você deve adicioná-los manualmente. Agora, este é um subconjunto de todas as lojas, se estivesse relatando todas as lojas, eu provavelmente teria aqui, fora do intervalo de impressão, algo que extraísse o total geral da tabela dinâmica. E então eu saberia, se esse total não corresponder ao total geral da tabela dinâmica, que algo está errado e tem uma função IF aqui dizendo “Ei, há, você sabe, novos dados que foram adicionados, tenha muito cuidado. ” Eles têm algum tipo de mecanismo para detectar que novos dados estão lá. Mas eu entendo, é um uso legal. Assim, embora na maioria das vezes GetPivotData apenas nos deixe loucos, pode haver uma utilidade para ele. Tudo bem,então essa é a dica nº 21 de 40 no livro, compre o livro agora, faça o pedido online, clique no “i” no canto superior direito.
Recapitulação muito, muito longa hoje, certo: GetPivotData acontece quando uma fórmula aponta para dentro de uma tabela dinâmica, uma fórmula fora da tabela dinâmica aponta para dentro. Embora a fórmula inicial esteja correta, ela não será copiada. A maioria das pessoas odeia GetPivotData e deseja evitá-lo. Então você pode construir uma fórmula sem o mouse ou as teclas de seta, basta digitar a fórmula ou desligar GetPivotData permanentemente, ah, mas há uma utilidade, certo. Portanto, temos que construir um relatório com os valores reais do mês anterior e o orçamento para o futuro. Fluxo de trabalho normal, crie uma tabela dinâmica, converta em valores, apague colunas. Existe uma maneira de remover os subtotais usando Configurações de campo, livrando-se do plano positivo real de janeiro. Em vez disso, vamos apenas criar a tabela dinâmica mais feia do mundo, com muitos dados.
Construa uma planilha de relatório bem formatada, simplesmente antiga, com talvez um pouco de lógica para alterar a palavra Real para Plano. E, a partir da primeira célula do relatório, o primeiro lugar onde os números estarão nesse relatório, digite an =, vá para a tabela dinâmica e permita que GetPivotData aconteça. Examinamos a sintaxe de GetPivotData, portanto, é o campo a retornar, Vendas, onde reside a tabela dinâmica, e depois os pares de critérios, o nome do campo e o valor. Vamos remover o valor codificado e apontar para uma célula, pressionando F4 3 vezes bloqueia apenas a coluna, pressionando F4 2 vezes bloqueia apenas a linha, copie aquela fórmula, Colar Fórmulas Especiais. Eu coloquei uma dica extra lá de que F4 é um refazer, então eu só tive que ir para a caixa de diálogo Colar especial uma vez e, para a próxima Colar fórmulas especiais, usei apenas F4. No próximo mês, adicione os dados,atualize a tabela dinâmica, altere a data final. Certifique-se de que eles não construíram nenhuma loja nova, você sabe, que tenha algum tipo de mecanismo, seja manual ou uma fórmula de verificação, verifique. Obrigado a iTrainerMX no Twitter, que sugeriu GetPivotData, também Carlos e Rob da Microsoft, Rob agora do Power Pivot Pro. Carlos por usar isso e Rob por me dizer que Carlos estava usando, eu conheci Carlos depois, e ele confirmou que sim, ele era um dos contadores que usava isso o tempo todo na Microsoft, certo, pronto.e Rob por me dizer que Carlos estava usando, eu conheci Carlos depois, e ele confirmou que sim, ele era um dos contadores que usava isso o tempo todo na Microsoft, certo, pronto.e Rob por me dizer que Carlos estava usando, eu conheci Carlos depois, e ele confirmou que sim, ele era um dos contadores que usava isso o tempo todo na Microsoft, certo, pronto.
Bem, ei, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2013.xlsx
