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, basta digitar uma fórmula, por exemplo, =D5/C5-1sem 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.
Crie uma tabela dinâmica com Armazenar em Linhas. Coloque o mês e o tipo 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, você pode alterar 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 compactada 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 real 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 janeiro real 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.
A figura abaixo mostra 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 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 mensal:
- 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.
Você tem que admitir que usar um relatório 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.
