GetPivotData no Excel - Dicas do Excel

Você acabou de construir uma tabela dinâmica no Excel. Você clica fora da tabela dinâmica. Você constrói uma fórmula do Excel. Você copia essa fórmula para todas as linhas da tabela dinâmica. O cálculo relata a resposta errada para todas, exceto a primeira linha da tabela dinâmica. Você acabou de ser picado pelo "recurso" Generate GetPivotData. Vamos dar uma olhada no que é isso e como evitá-lo.

Apenas evite o problema GetPivotData - O método rápido

A maneira mais rápida de evitar que o Excel gere GetPivotData é digitar sua fórmula sem usar o mouse ou qualquer tecla de seta. Se você simplesmente digitar =E5/D5, o Excel criará uma fórmula relativa "normal" que pode ser copiada para todas as linhas adjacentes à tabela dinâmica.

Impedir o problema GetPivotData - o método permanente

Há uma configuração oculta para impedir que o Excel gere GetPivotData. Está mais oculto no Excel 2003 do que no Excel 2007.

No Excel 2007, siga estas etapas:

• Crie uma tabela dinâmica no Excel 2007
• Certifique-se de que a célula ativa está dentro da tabela dinâmica
• Observe o lado esquerdo da guia Opções de Ferramentas de Tabela Dinâmica da faixa de opções. Existe um botão Opções. Não clique no botão Opções! No lado direito do botão Opções, há uma seta suspensa. Clique na seta suspensa. Desmarque Gerar GetPivotData.
• Agora você pode inserir fórmulas usando o mouse, as teclas de seta ou digitando.

No Excel 2003 e anteriores, siga estas etapas:

• Escolha ferramentas, personalize
• Existem três guias na caixa de diálogo Personalizar. Escolha a guia Comandos no centro.
• Na caixa de lista à esquerda, escolha o sétimo item, Dados
• Na caixa de lista à direita, role quase até o final. O oitavo ícone do final da lista é Gerar GetPivotData. Arraste este ícone da caixa de listagem e solte-o no meio de qualquer barra de ferramentas existente.
• Clique no botão Fechar para fechar a caixa de diálogo.
• Clique no ícone que você acabou de adicionar à barra de ferramentas do Excel. Isso desativará o recurso. Agora você pode inserir fórmulas usando o mouse sem gerar funções GetPivotData.

Aqui vai uma dica bônus: eu costumava desprezar esse recurso e simplesmente queria que ele fosse desligado o tempo todo. Eu adicionei o ícone à minha barra de ferramentas, desativei o ícone e removi-o da barra de ferramentas. Agora … relaxei um pouco. Posso ver que o ícone ocasionalmente tem alguns bons usos. Portanto, criei uma tabela dinâmica no Excel 2003 e verifiquei se o ponteiro da célula estava na tabela dinâmica. Em seguida, usei a caixa de diálogo de personalização para arrastar o ícone Gerar GetPivotData para minha barra de ferramentas Tabela Dinâmica. Agora - quando estou em uma tabela dinâmica, posso escolher ativar ou desativar o recurso.

Por que a Microsoft fez isso? Existe alguma boa utilidade para GetPivotData?

O que GetPivotData deve fazer? Claramente, a Microsoft gosta da função, pois a impôs a milhões de pessoas desavisadas que usam tabelas dinâmicas.

GetPivotData retornará qualquer célula visível de uma tabela dinâmica. Em vez de apontar para um endereço de célula, porém, você descreve o valor como uma interseção de vários valores de campo.

Conforme a tabela dinâmica muda, GetPivotData continuará a retornar os mesmos dados lógicos da tabela dinâmica, desde que os dados ainda estejam visíveis dentro da tabela dinâmica. Isso pode ser importante se você estiver mudando de mês para mês no campo de página de uma tabela dinâmica e quiser sempre retornar as vendas de um determinado cliente. Conforme a lista de clientes muda a cada mês, a posição do cliente muda. Ao usar =GETPIVOTDATA, você pode garantir que retornou o valor adequado da tabela dinâmica.

=GETPIVOTDATAsempre começa com dois argumentos particulares. Em seguida, tem opcionalmente pares adicionais de argumentos.

Aqui estão os dois argumentos necessários:

1. O nome de um campo de dados. Pode ser "Soma da receita" ou simplesmente "Receita".
2. Qualquer célula localizada "dentro" da tabela dinâmica. Você sabia que sua tabela dinâmica tem um nome? Você provavelmente não sabia disso porque não consegue descobrir o nome na interface do Excel. Você teria que ir para o VBA para aprender o nome da tabela dinâmica. Portanto - era mais fácil para a Microsoft permitir que você identificasse a tabela dinâmica apontando para qualquer célula dentro da tabela dinâmica. Embora você possa escolher qualquer célula, é mais seguro escolher a célula do canto superior esquerdo. Há uma chance de sua tabela dinâmica encolher para uma combinação específica de campos de página. Nesse caso, usar a célula do canto superior esquerdo garantirá que você continue apontando para dentro da tabela dinâmica.

Em seguida, você continua a função com pares adicionais de argumentos. Cada par inclui um nome de campo e um valor.

GetPivotData só pode retornar valores visíveis na tabela dinâmica

Depois de ver algumas funções GetPivotData funcionando, você pode pensar que elas são mais poderosas do que realmente são. Na verdade, a função só funcionará se o valor específico estiver visível na tabela dinâmica. Considere a imagem abaixo.

• Na célula A2, GETPIVOTDATA retorna as vendas de janeiro de 2004 da ABC na Região Central. Isso está pegando o 80003 da célula G19.
• No entanto, a fórmula em A6 falha. Ele está pedindo vendas do ABC em todas as regiões. A tabela dinâmica mostra ABC total para Central, ABC total para Leste, ABC total para Oeste, mas nunca mostra ABC total para todas as regiões, então o resultado é um #REF! erro.
• Se você girar o campo de região até a área da página, a fórmula em A6 começará a funcionar, mas a fórmula em A2 e A4 começará a retornar #REF !. Se você escolher Central no menu suspenso Região, todas as quatro fórmulas funcionarão.
• Desativar os totais gerais na caixa de diálogo Opções da tabela dinâmica IRÁ fazer com que muitas funções GetPivotData comecem a retornar #REF! erros.