Obtenha um exemplo de dados da tabela dinâmica do Excel

Deixe um comentário 6,950

Anteriormente eu já falei sobre como quando você se refere a uma célula tabela dinâmica em vez de um link normal, a função GET.PIVORTABLE.DATA é retornada (consulte). Se você estiver interessado Como Para superar esse inconveniente, recomendo que você consulte a nota mencionada. Se você estiver interessado, Por que isso acontece, e também o que aspectos positivos função tem GET.DATA.PIVOT.TABLE, então sugiro um fragmento do livro de Jelen, Alexander. (Capítulo 15). A técnica em consideração nos permitirá lidar com muitos problemas que causam dores de cabeça aos usuários de tabelas dinâmicas, em particular:

Quando você atualiza uma tabela dinâmica, a formatação aplicada anteriormente desaparece. Os formatos numéricos são perdidos. Os resultados do ajuste das larguras das colunas desaparecem.
Não existe maneira simples criando uma tabela dinâmica assimétrica. A única opção é usar conjuntos nomeados, mas esse método só está disponível para aqueles que usam tabelas dinâmicas de modelo de dados em vez de tabelas dinâmicas regulares.
O Excel não consegue lembrar os modelos. Se precisar criar tabelas dinâmicas repetidamente, você precisará refazer o agrupamento, aplicar campos e membros calculados e executar diversas outras tarefas semelhantes.

Na verdade, tudo o que é descrito aqui não é novidade. Além disso, técnicas semelhantes têm sido utilizadas desde o Excel 2002. No entanto, a minha comunicação com os utilizadores mostra que menos de 1% estão familiarizados com elas. A única dúvida que os usuários têm é como desabilitar a estranha função GET.PIRTTABLE.DATA. É uma pena…

Baixe a nota em ou formato, exemplos em formato

Bem, vamos começar em ordem.

Como abandonar a função problemática GET.PIVOT.TABLE.DATA

A função GET.PIVO.TABLE.DATA sempre foi uma dor de cabeça para muitos usuários. De repente, sem qualquer aviso, o comportamento das Tabelas Dinâmicas mudou no Excel 2002. Depois que você começa a criar fórmulas fora da Tabela Dinâmica que fazem referência a seus dados, esse recurso surge do nada.

Suponha que na tabela dinâmica mostrada na Fig. 1, você precisa comparar os dados de 2015 e 2014.

Arroz. 1. Tabela Dinâmica Original

Adicione o título “% Crescimento” à célula D3.
Copie o formato da célula C3 para a célula D3.
Na célula D4, insira um sinal de igual.
Clique na célula C4.
Digite uma / (barra) para representar a operação de divisão.
Clique na célula B4.
Digite -1 e pressione a combinação de teclas ficar na mesma cela. Formate o resultado como uma porcentagem. Você verá que a região Oeste teve uma queda de 43,8% na receita (Figura 2). Resultados não muito bons.
Depois de terminar de inserir sua primeira fórmula, selecione a célula D4.
Clique duas vezes no pequeno quadrado localizado no canto inferior direito da célula. Esta caixa representa uma alça de preenchimento que permite copiar a fórmula para preencher toda a coluna do relatório.

Após concluir a cópia da fórmula, olhando para a tela, você perceberá que algo está errado - cada região apresentou queda de 43,8% ao longo do ano (Fig. 3).

Arroz. 3. Ao terminar de copiar a fórmula para todas as células da coluna, você verá que cada região apresentou uma queda de 43,8%

É improvável que isso aconteça em Vida real. Qualquer usuário dirá que após realizar as etapas listadas acima, o Excel criará a fórmula =C4/B4–1. Volte para a célula D4 e preste atenção na barra de fórmulas (Figura 4). Apenas algum tipo de diabrura! A fórmula simples =C4/B4–1 não existe mais. Em vez disso, o programa substitui uma construção complexa pela função GET.PIVOT.TABLE.DATA. Por que esta fórmula dá resultados corretos na célula D4, mas quando copiada para as células abaixo ela se recusa a funcionar?

A primeira reação de qualquer usuário ao ocorrido será a seguinte: “O que é essa estranha construção GET.PIVOT.TABLE.DATA que estragou meu relatório?” A maioria dos usuários desejará se livrar desse recurso imediatamente. Alguns farão a pergunta: “Por que a Microsoft nos deu esse recurso?”

Não havia nada assim em Tempos do Excel 2000. Depois que comecei a encontrar regularmente a função GET.PIVOT.TABLE.DATA, simplesmente a odiei. Quando, num dos seminários, alguém me perguntou como isso poderia ser usado em benefício da causa, fiquei pasmo. Nunca me fiz essa pergunta! Na minha opinião, e na opinião da maioria dos usuários do Excel, a função GET.DATA.PICTTABLE foi um produto do mal, não tendo nada a ver com as forças do bem. Felizmente, existem duas maneiras de desativar esse recurso.

Bloqueio da função GET.PIVOT.TABLE.DATA inserindo uma fórmula. Existe uma maneira simples de evitar que a função GET.PIVOTABLE.DATA apareça. Para fazer isso, você precisa criar uma fórmula sem usar o mouse ou as teclas do cursor. Basta seguir estas etapas.

Vá para a célula D4 e digite = (sinal de igual).
Digite C4.
Insira um / (barra indicando operação de divisão).
Digite B4.
Insira -1.
Clique Digitar.

Agora você criou um regular Fórmula Excel, que pode ser copiado nas células abaixo da coluna e com o qual você pode obter os resultados corretos (Fig. 5). Como você pode ver, você pode criar fórmulas em áreas fora da Tabela Dinâmica que fazem referência a dados dentro da Tabela Dinâmica. E aqueles que não acreditam que isso seja possível, deixe-os realizar eles próprios os passos descritos.

Arroz. 5. Basta digitar =С4/В4–1 no teclado e a fórmula funcionará como deveria

Alguns usuários se sentirão desconfortáveis pelo fato de a ordem normal de inserção das fórmulas ser interrompida. Além disso, a opção proposta exige mais mão-de-obra. Se você é um desses usuários, o segundo método é para você...

Desativando a função GET.PIVOT.TABLE.DATA. Você pode desativar permanentemente o recurso GET.PIVOTABLE.DATA. Clique na faixa do menu Arquivo → Opções. Na janela que se abre OpçõesExcel vá para depositar Fórmulas e desmarque a caixa ao lado da opção Usar funçãoGetPivotData para links em uma tabela dinâmica. Clique OK.

Opção alternativa. Clique na tabela dinâmica e na guia contextual que aparece Análise clique na lista suspensa ao lado do botão Opções. Desmarque a caixa ao lado do item Criar GetPivotData(Fig. 7). Por padrão, a caixa de seleção está habilitada.

Por que a Microsoft nos ofereceu a função GET.PICTTABLE.DATA. Se esse recurso é tão terrível, por que a Microsoft o habilitou por padrão? Por que eles se preocupam em manter o suporte para esse recurso nas novas versões do Excel? Eles estão cientes do sentimento do usuário? E passamos para o mais interessante...

Usando a função GET.PIVORTABLE.DATA para melhorar tabelas dinâmicas

As tabelas dinâmicas são uma grande invenção da humanidade. Uma tabela dinâmica é criada com apenas alguns cliques, eliminando a necessidade de filtros avançados, BDSUMM e tabelas de dados. As tabelas dinâmicas permitem criar relatórios de uma página com base em grandes quantidades de dados. Essas vantagens ofuscam algumas das desvantagens das tabelas dinâmicas, como a formatação sem brilho e a necessidade de converter tabelas dinâmicas em valores para personalização adicional. Na Fig. A Figura 8 mostra um processo típico de criação de uma tabela dinâmica. Neste caso, tudo começa com os dados iniciais. Criamos uma tabela dinâmica e utilizamos todas as técnicas possíveis para personalizá-la e melhorá-la. Às vezes convertemos a tabela dinâmica em valores e fazemos a formatação final.

A nova técnica para criação de tabelas dinâmicas, proposta por Rob Colley (um desenvolvedor da Microsoft) e discutida a seguir, é uma melhoria no processo descrito acima. Nesse caso, uma tabela dinâmica primitiva é criada primeiro. Esta tabela não precisa ser formatada. Em seguida, ele passa por um processo de uma etapa relativamente trabalhoso para criar um shell bem formatado que abrigará o relatório final. Depois disso, a função GET.PIVOT.TABLE.DATA é usada para preencher rapidamente o relatório localizado no shell com dados. Após receber os novos dados, pode-se colocá-los em uma planilha, atualizar a tabela dinâmica primitiva e imprimir o relatório localizado no shell (Fig. 9). Esta técnica tem uma série de vantagens inegáveis. Por exemplo, você não precisa se preocupar em formatar o relatório imediatamente após criá-lo. O processo de criação de tabelas dinâmicas torna-se quase totalmente automatizado.

As seções a seguir discutem como criar um relatório dinâmico que exibe valores reais dos meses anteriores e metas dos meses futuros.

Criando uma tabela dinâmica primitiva. Os dados iniciais (Fig. 10) são apresentados na forma de transações contendo informações sobre indicadores planejados e reais para cada região em que a empresa possui filiais. Os indicadores planejados são detalhados no nível do mês e os indicadores reais - no nível dos dias individuais. Os indicadores planejados são criados para o ano seguinte e os indicadores reais são criados para os meses anteriores. Como o relatório será atualizado todos os meses, esse processo será bastante simplificado se a fonte de dados da Tabela Dinâmica aumentar de tamanho à medida que novos dados forem adicionados à parte inferior. Nas versões herdadas do Excel, a criação de tal fonte de dados era feita usando um intervalo dinâmico nomeado usando a função OFFSET (para obter mais informações, consulte). Ao trabalhar no Excel 2013, basta selecionar uma das células de dados e pressionar a combinação de teclas Ctrl+T (criar uma Tabela). Isso cria um conjunto de dados nomeado que se expande automaticamente à medida que novas linhas e colunas são adicionadas.

Agora vamos criar uma tabela dinâmica. A função GET.PIVORTABLE.DATA é bastante poderosa, mas só pode retornar os valores que aparecem na tabela dinâmica real. Esta função não consegue examinar o cache para calcular itens que não estão na tabela dinâmica.

Crie uma tabela dinâmica:

Selecione uma equipe Inserir → Tabela dinâmica e depois na caixa de diálogo Criando uma tabela dinâmica clique OK.
Na lista de campos da tabela dinâmica, selecione o campo data. Uma lista de datas aparecerá no lado esquerdo da tabela dinâmica (Fig. 11).
Selecione qualquer célula de data, por exemplo A4. Na guia de contexto Análise localizado em um conjunto de guias contextuais Trabalhando com tabelas dinâmicas, clique no botão Agrupar por campo(Veja detalhes). Na caixa de diálogo Agrupamento selecione a opção Meses(Fig. 12). Clique OK. Os nomes dos meses aparecerão no lado esquerdo da tabela dinâmica (Fig. 13).
Arraste o campo data para a área de colunas da tabela dinâmica.
Arraste o campo Índice para a área de colunas da lista de campos da tabela dinâmica.
Selecione o campo Região, que aparecerá na coluna esquerda da Tabela Dinâmica.
Selecione o campo Renda que aparece na área de valores da tabela dinâmica.

Arroz. 11. Comece agrupando por campo data

Neste estágio, nossa tabela dinâmica parece bastante primitiva (Fig. 14). Eu realmente não gosto das inscrições Nomes de linha E Nomes de colunas. Não é prático exibir totais para Plano de janeiro E Fato de janeiro na coluna D, etc. Mas não se preocupe aparência esta tabela de resumo, porque ninguém mais a verá, exceto você. A partir de agora, criaremos um shell de relatório, cuja fonte de dados será a tabela dinâmica recém-criada.

Criando um shell de relatório. Insira uma folha em branco na pasta de trabalho. Vamos deixar de lado as ferramentas para trabalhar com tabelas dinâmicas por um tempo e passar para as ferramentas usuais do Excel. Nossa tarefa é usar fórmulas e formatações para criar um relatório bonito e que não seja constrangedor de mostrar ao gestor.

Siga estes passos (Fig. 15).

Na célula A1, insira o nome do relatório - Indicadores planejados e reais por região.
Vá para a guia lar, clique no botão Estilos de células selecione o formato Cabeçallho 1.
Na célula A2, insira a fórmula =MÊS(HOJE();0). Esta função retorna o último dia do mês atual. Por exemplo, se você estiver lendo isto em 14 de agosto de 2014, a célula A2 exibirá a data 31 de agosto de 2014.
Selecione a célula A2. Pressione a combinação de teclas Ctrl+1 para exibir a caixa de diálogo Formato de célula. Na aba Número clique no item Todos os formatos. Insira um formato de número personalizado como "A partir do mês "MMMM" indicadores planejados"(Fig. 16). Como resultado, a data calculada aparecerá como texto.
Na célula A5, insira um título Região.
Insira os títulos das regiões nas células restantes da Coluna A. Os títulos das regiões devem corresponder aos nomes das regiões listados na Tabela Dinâmica.
Se necessário, adicione rótulos à coluna dos totais departamentais.
Na parte inferior do relatório, adicione a linha Total para a empresa.
Na célula B4, insira a fórmula =DATA(ANO($A$2),COLUNA(A1),1). Esta fórmula retorna as datas 01/01/2014, 01/02/2104, etc., os primeiros dias de todos os 12 meses do ano atual.
Selecione a célula B4. Pressione a combinação de teclas Ctrl+1 para abrir a janela Formato de célula. Na aba Número No capítulo Todos os formatos insira o formato de número personalizado MMM. Este formato exibe o nome do mês com três letras. Alinhe o texto à borda direita da célula.
Copie o conteúdo da célula B4 para o intervalo C4:M4. Uma linha com os nomes dos meses aparecerá no topo da tabela dinâmica.
Na célula B5, insira a fórmula =SE(MÊS(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Facto, e para o presente e o futuro - Plano.
Adicione um título à célula N5 Resultado final. Para a célula O4 - Resultado final, O5 - Plano, P5 - % desvio.
Insira as fórmulas usuais do Excel usadas para calcular totais departamentais, linhas de total da empresa, colunas de total geral e colunas de variação percentual:
1. na célula B8, insira a fórmula =SUM(B6:B7) e copie-a para outras células da linha;
2. na célula N6, insira a fórmula =SUM(B6:M6) e copie-a para outras células da coluna;
3. na célula P6, insira a fórmula =IFERROR((N6/O10)-1;0) e copie-a para outras células da coluna;
4. na célula B13, insira a fórmula =SUM(B10:B12) e copie-a para outras células da linha;
5. na célula B17, insira a fórmula =SUM(B15:B16) e copie-a para outras células da linha;
6. Na célula B19, insira a fórmula =SOMA(B6:B18)/2 e copie-a para outras células da linha.
Aplique o estilo Título 4 às legendas na coluna A e aos títulos nas linhas 4 e 5.
Para o intervalo de células B6:O19, selecione o formato numérico # ##0.
Para células da coluna P, selecione o formato numérico 0,0%.

Assim, concluímos a criação do shell de relatório mostrado na Fig. 15. Este relatório inclui toda a formatação necessária. A próxima seção demonstra como usar a função GET.PIVORTABLE.DATA para concluir um relatório.

Arroz. 15. Reporte shell antes de adicionar fórmulas GET.PIVOT.TABLE.DATA

Usando a função GET.PIVORTABLE.DATA para preencher o shell do relatório com dados. A partir de agora você poderá experimentar todos os benefícios de usar a função GET.PIVOT.TABLE.DATA. Se você desmarcou uma caixa de seleção que habilitou esse recurso, volte para a configuração apropriada e retorne a caixa de seleção (veja a descrição da Figura 6 ou 7).

Selecione a célula B6 do shell do relatório. Esta célula corresponde à região Nordeste e aos números reais de janeiro.

Digite = (sinal de igual) para começar a inserir a fórmula.
Acesse a planilha da tabela dinâmica e clique na célula que corresponde à região Nordeste e aos indicadores reais de janeiro - C12 (Fig. 17).
Pressione a tecla Digitar para terminar de inserir a fórmula e retornar ao shell do relatório. Como resultado, o Excel adicionará a função GET.PIOTTABLE.DATA à célula B6. A célula exibirá o valor de $ 277.435.

Lembre-se deste número porque você precisará dele ao compará-lo com os resultados da execução de uma fórmula que você editará posteriormente. A fórmula gerada pelo programa é a seguinte: =GET.DATA.PIVOTABLE("Renda" ;'Fig. 11-14′!$A$3; "Região" ; "Nordeste" ; "Data" ;1; "Indicador" ; "Facto"). Se você ignorou a função GET.PIRTTABLE.DATA até agora, é hora de examiná-la mais de perto. Na Fig. 18 esta fórmula é mostrada no modo de edição junto com uma dica.

Argumentos de função:

Campo de dados. Campo da área de valores da tabela dinâmica. Atenção: neste caso o campo é usado Renda, mas não Valor por campo Renda.
Tabela_dinâmica. Com esta opção, a Microsoft pergunta: “Qual tabela dinâmica você deseja usar?” Basta especificar uma das células da tabela dinâmica. Entrada ‘Fig. 11-14’!$A$3 refere-se à primeira célula da tabela dinâmica na qual os dados são inseridos. Como no nosso caso podemos especificar qualquer célula relacionada à tabela dinâmica, deixe o argumento inalterado. O endereço de célula $A$3 é adequado em todos os aspectos.
Campo 1; elemento 1. Na fórmula gerada automaticamente, o nome é selecionado como o nome do campo Região, e como o valor do campo - Nordeste. É aqui que residem os problemas que surgem ao trabalhar com a função GET.PIVOT.TABLE.DATA. Os valores selecionados automaticamente não podem ser copiados porque são codificados. Portanto, se as fórmulas forem copiadas em toda a área do relatório, será necessário alterá-las manualmente. Em vez do valor Nordeste, substitua a referência de célula no formato $A6. Ao especificar um cifrão antes do nome da Coluna A, você especifica que a parte da linha da referência pode ser alterada ao copiar a fórmula nas células da coluna.
Campo 2; elemento 2. Este par de argumentos especifica o campo data com valor 1. Se a tabela dinâmica original foi agrupada por mês, o campo mês mantém o nome do campo original data. O valor numérico do mês é 1, que corresponde a janeiro. Não é aconselhável usar esse valor ao criar fórmulas enormes especificadas em dezenas ou mesmo centenas de células de relatório. É melhor usar uma fórmula que calcule os valores dos campos data, semelhante à fórmula na célula B4. Em vez de 1 neste caso, você pode usar a fórmula MÊS(B$4). Um cifrão antes do 4 indica que a fórmula pode atribuir valores ao campo data com base em outros meses à medida que a fórmula é copiada nas células da linha.
Campo 3; elemento 3. Neste caso, o nome do campo é atribuído automaticamente Índice e valor do campo Facto. Esses valores estão corretos para janeiro, mas para os meses subsequentes o valor do campo deverá ser alterado para Plano. Alterar um valor de campo codificado Facto por link B$5.
Campo 4; elemento 4. Esses argumentos não são usados porque os campos acabaram.

A nova fórmula é mostrada na Fig. 19. Em um minuto, em vez de uma fórmula codificada projetada para funcionar com um único valor, foi criada uma fórmula flexível que pode ser copiada para todas as células do conjunto de dados. Pressione a tecla Digitar e você obterá o mesmo resultado de antes de editar a fórmula. A fórmula editada assume o seguinte formato: =GET.DATA.PIVORTABLE("Receita" ;'Fig. 11-14′!$A$3; "Região" ;$A6; "Data" ;MÊS(B$4); "Indicador " ;B$5)

Arroz. 19. Após a conclusão da edição, a fórmula GET.PIOTTABLE.DATA é adequada para copiar para todas as células do intervalo

Copie a fórmula para todas as células vazias nas colunas B:M onde os resultados são calculados. Agora que o relatório contém valores numéricos reais, você pode fazer ajustes finais nas larguras das colunas.

Na próxima etapa configuraremos a fórmula GET.DATA.PICTTABLE para calcular os indicadores finais planejados. Se você simplesmente copiar a fórmula para a célula O6, verá a mensagem de erro #LINK! A razão pela qual esse erro aparece é que a palavra Resultado final na célula O4 não está o nome do mês. Para garantir o funcionamento correto da função GET.PIVORTABLE.DATA, o valor necessário deve estar na tabela dinâmica. Mas como na tabela dinâmica original o campo Índiceé o segundo campo na área de colunas, a coluna de dados Resultado do plano na verdade ausente. Mova o campo Índice para que seja o primeiro na área da coluna (Fig. 20).

Arroz. 20. Ajuste o layout dos campos na área de colunas para que apareça uma coluna Resultado do plano

Compare com a fig. 14. Lá, na área da COLUNA, foi criado o primeiro campo data, o que fez com que inicialmente as colunas fossem agrupadas por data, e dentro de cada mês por planejado/real. Agora o campo Indicador está localizado primeiro, e no resumo as colunas vêm primeiro Plano, dentro classificado por mês, e então todas as colunas vão Facto.

Voltando à planilha do shell do relatório, vá até a célula O6, digite = (sinal de igual) e consulte a célula N12 da planilha da tabela dinâmica correspondente aos resultados planejados para a região Nordeste. Clique Digitar. A fórmula resultante =GET.DATA.PIVORTABLE("Renda";'Fig. 11-14′!$A$3;"Região";"Nordeste";"Indicador";"Plano"). Edite-o: =GET. SUMMARY.TABLE DATA(“Receita”;’Fig. 11-14′!$A$3;”Região”;$A6;”Indicador”;O$5). Copie esta fórmula para outras células na coluna O (Fig. 21). Observe que mesmo ao mover áreas diferentes do relatório de tabela dinâmica, o shell funciona corretamente. Claro, se você tornar alguns campos de resumo inativos, o shell não será capaz de lidar com isso...

Arroz. 21. Relatório final que pode ser apresentado ao gestor

Agora você tem um wrapper de relatório bem formatado que usa valores de uma tabela dinâmica dinâmica. Embora a criação inicial do relatório tenha demorado algum tempo, atualizá-lo é questão de apenas alguns minutos.

Atualize o relatório. Para atualizar o relatório com dados dos meses futuros, siga estas etapas:

Insira métricas reais abaixo do conjunto de dados original. Como os dados de origem estão em formato de tabela, a formatação da tabela é automaticamente propagada para novas linhas de dados. A definição da tabela dinâmica original também foi ampliada (no arquivo Excel já adicionei os indicadores reais de todo o ano).
Vá para a Tabela Dinâmica. Clique com o botão direito e selecione Atualizar. A aparência da tabela dinâmica mudará, mas tudo bem.
Vá para o shell do relatório. Em princípio, tudo já foi feito para atualizar o relatório, mas não custa nada testar os resultados. Altere a fórmula na célula A2, por exemplo, para esta: =MÊS(HOJE() +31 ;0) e veja o que acontece.

Ao adicionar novos dados reais de vendas a cada mês, você não terá que se preocupar em recriar formatos, fórmulas, etc. O processo descrito de atualização de relatórios é tão simples que você esquecerá para sempre os problemas que surgiram na preparação dos relatórios mensais. O único problema pode surgir se a empresa for reorganizada, podendo surgir novas regiões na tabela dinâmica. Para garantir que suas fórmulas funcionem corretamente, certifique-se de que os totais do seu relatório correspondam aos totais da sua Tabela Dinâmica. Quando uma nova região aparecer, basta adicioná-la à planilha com o shell e “arrastar” as fórmulas correspondentes.

Achei que nunca diria isso: "A função GET.DATA.PICTTABLE é a maior bênção. Como existimos sem ela antes?

No original de Jelen, os dados de origem foram organizados de forma que outras fórmulas funcionassem corretamente apenas em julho de 2015. No arquivo Excel anexado a esta nota, modifiquei os dados de origem, bem como algumas fórmulas para que tudo funcionasse, independentemente de a data em que você fará experiências com o arquivo Excel anexado. Infelizmente, as fórmulas tiveram que ser complicadas.

Para Tabelas Dinâmicas, existe a função GET.PIVORTABLE.DATA, que retorna os dados armazenados no relatório de Tabela Dinâmica.

Para acessar rapidamente uma função, você precisa inserir um sinal de igual em uma célula (=) e destacar a célula desejada na Tabela Dinâmica. O Excel irá gerar a função GET.PIRTTABLE.DATA automaticamente.

Desativando a criação de GetPivotData

Para desabilitar a geração automática da função GET.PIOTTABLE.DATA, selecione qualquer célula da tabela dinâmica, vá até a aba Trabalhando com tabelas dinâmicas -> Opções para o grupo Tabela dinâmica. Clique na seta para baixo ao lado da guia Opções. No menu suspenso, desmarque o item CriarObtenhaPivotData.

Usando referências de células na função GET.PIVOTABLE.DATA

Em vez de especificar os nomes dos itens ou campos na função GET.PIOTTABLE.DATA, você pode consultar as células localizadas na planilha. No exemplo abaixo, a célula E3 contém o nome do produto e a fórmula da célula E4 se refere a ele. Como resultado, o volume total de bolos será devolvido.

Usando referências a um campo de tabela dinâmica

Não há dúvidas sobre como funcionam os links para itens da tabela dinâmica. Surgem problemas se quisermos nos referir a um campo de dados.

No exemplo, a célula E3 contém o nome do campo de dados “Quantidade”, e seria bom fazer referência a esta célula na função, ao invés de ter o nome do campo na fórmula GET.PIVOT.TABLE.DATA.

No entanto, se mudarmos o primeiro argumento campo de dados a uma referência à célula E3, o Excel retornará o erro #REF!

OBTER.PIVORTABELA.DADOS(E3,$A$3)

Simplesmente adicionar uma string vazia (“”) ao início ou final da referência da célula resolverá o problema.

OBTER.PIVORTABELA.DADOS(E3&"";$A$3)

Simplesmente ajustar a fórmula retornará o valor correto.

Usando datas na função GET.PIVOTABLE.DATA

Se você usar datas na função GET.PIVORTABLE.DATA, poderá enfrentar problemas mesmo que a data apareça na tabela dinâmica. Por exemplo, o argumento da fórmula abaixo é a data “21/04/2013”, e a tabela dinâmica contém um campo com as datas de vendas. No entanto, a fórmula na célula E4 retorna um erro.

GET.PICTTABLE.DATA("Volume";$A$3;"Data";"21/04/2013″)

Para evitar erros de data, você pode usar um dos seguintes métodos:

Compare formatos de data em fórmula e tabela dinâmica
Use a função DATEVALUE
Use a função DATA
Consulte a célula com a data correta

Compare formatos de data em uma fórmula e uma tabela dinâmica.

Para obter o resultado correto, ao usar a função GET.PIVORTABLE.DATA, certifique-se de que os formatos de data do argumento da fórmula e da tabela dinâmica sejam iguais.

Na célula E4, a fórmula utilizou o formato de data “DD.MM.AAAA” e retornou as informações corretas.

Usando a função DATEVALUE

Em vez de inserir manualmente a data na fórmula, você pode adicionar a função DATEVALUE para retornar a data.

Na célula E4, a data é inserida usando a função DATEVALUE e o Excel retorna as informações necessárias.

GET.PIVORTABLE.DATA("Volume";$A$3;"Data";DATEVALUE("21/04/2013"))

Usando a função DATA

Em vez de inserir manualmente a data na fórmula, você pode usar a função DATA, que permitirá retornar corretamente as informações necessárias.

GET.PIVORTABLE.DATA("Volume";$A$3;"Data";DATA(2013,4,21))

Referência de célula de data

Em vez de inserir manualmente uma data em uma fórmula, você pode fazer referência a uma célula que contém uma data (em qualquer formato que o Excel aceite como data). No exemplo da célula E4, a fórmula faz referência à célula E3 e o Excel retorna os dados corretos.

GET.PIVORTABLE.DATA("Volume";$A$3;"Data";E3)

Anteriormente, já falei sobre como quando você faz referência a uma célula da tabela dinâmica, em vez de um link normal, a função GET.PIVORTABLE.DATA é retornada (veja). Se você estiver interessado Como Para superar esse inconveniente, recomendo que você consulte a nota mencionada. Se você estiver interessado, Por que isso acontece, e também, quais aspectos positivos a função GET.DATA.PICTTABLE tem, então sugiro um fragmento do livro de Jelen, Alexander. (Capítulo 15). A técnica em consideração nos permitirá lidar com muitos problemas que causam dores de cabeça aos usuários de tabelas dinâmicas, em particular:

Quando você atualiza uma tabela dinâmica, a formatação aplicada anteriormente desaparece. Os formatos numéricos são perdidos. Os resultados do ajuste das larguras das colunas desaparecem.
Não existe uma maneira fácil de criar uma tabela dinâmica assimétrica. A única opção é usar conjuntos nomeados, mas esse método só está disponível para aqueles que usam tabelas dinâmicas de modelo de dados em vez de tabelas dinâmicas regulares.
O Excel não consegue lembrar os modelos. Se precisar criar tabelas dinâmicas repetidamente, você precisará refazer o agrupamento, aplicar campos e membros calculados e executar diversas outras tarefas semelhantes.

Baixe a nota em ou formato, exemplos em formato

Bem, vamos começar em ordem.

Como abandonar a função problemática GET.PIVOT.TABLE.DATA

Suponha que na tabela dinâmica mostrada na Fig. 1, você precisa comparar os dados de 2015 e 2014.

Arroz. 1. Tabela Dinâmica Original

Adicione o título “% Crescimento” à célula D3.
Copie o formato da célula C3 para a célula D3.
Na célula D4, insira um sinal de igual.
Clique na célula C4.
Digite uma / (barra) para representar a operação de divisão.
Clique na célula B4.
Digite -1 e pressione a combinação de teclas ficar na mesma cela. Formate o resultado como uma porcentagem. Você verá que a região Oeste teve uma queda de 43,8% na receita (Figura 2). Resultados não muito bons.
Depois de terminar de inserir sua primeira fórmula, selecione a célula D4.
Clique duas vezes no pequeno quadrado localizado no canto inferior direito da célula. Esta caixa representa uma alça de preenchimento que permite copiar a fórmula para preencher toda a coluna do relatório.

Após concluir a cópia da fórmula, olhando para a tela, você perceberá que algo está errado - cada região apresentou queda de 43,8% ao longo do ano (Fig. 3).

Arroz. 3. Ao terminar de copiar a fórmula para todas as células da coluna, você verá que cada região apresentou uma queda de 43,8%

É improvável que isso aconteça na vida real. Qualquer usuário dirá que após realizar as etapas listadas acima, o Excel criará a fórmula =C4/B4–1. Volte para a célula D4 e preste atenção na barra de fórmulas (Figura 4). Apenas algum tipo de diabrura! A fórmula simples =C4/B4–1 não existe mais. Em vez disso, o programa substitui uma construção complexa pela função GET.PIVOT.TABLE.DATA. Por que esta fórmula dá resultados corretos na célula D4, mas quando copiada para as células abaixo ela se recusa a funcionar?

Não havia nada parecido com isso na época do Excel 2000. Quando comecei a encontrar regularmente a função GET.PIVOTABLE.DATA, simplesmente a odiei. Quando, num dos seminários, alguém me perguntou como isso poderia ser usado em benefício da causa, fiquei pasmo. Nunca me fiz essa pergunta! Na minha opinião, e na opinião da maioria dos usuários do Excel, a função GET.DATA.PICTTABLE foi um produto do mal, não tendo nada a ver com as forças do bem. Felizmente, existem duas maneiras de desativar esse recurso.

Vá para a célula D4 e digite = (sinal de igual).
Digite C4.
Insira um / (barra indicando operação de divisão).
Digite B4.
Insira -1.
Clique Digitar.

Agora você criou uma fórmula normal do Excel que pode copiar nas células abaixo da coluna e obter os resultados corretos (Figura 5). Como você pode ver, você pode criar fórmulas em áreas fora da Tabela Dinâmica que fazem referência a dados dentro da Tabela Dinâmica. E aqueles que não acreditam que isso seja possível, deixe-os realizar eles próprios os passos descritos.

Arroz. 5. Basta digitar =С4/В4–1 no teclado e a fórmula funcionará como deveria

Usando a função GET.PIVORTABLE.DATA para melhorar tabelas dinâmicas

As seções a seguir discutem como criar um relatório dinâmico que exibe valores reais dos meses anteriores e metas dos meses futuros.

Crie uma tabela dinâmica:

Selecione uma equipe Inserir → Tabela dinâmica e depois na caixa de diálogo Criando uma tabela dinâmica clique OK.
Na lista de campos da tabela dinâmica, selecione o campo data. Uma lista de datas aparecerá no lado esquerdo da tabela dinâmica (Fig. 11).
Selecione qualquer célula de data, por exemplo A4. Na guia de contexto Análise localizado em um conjunto de guias contextuais Trabalhando com tabelas dinâmicas, clique no botão Agrupar por campo(Veja detalhes). Na caixa de diálogo Agrupamento selecione a opção Meses(Fig. 12). Clique OK. Os nomes dos meses aparecerão no lado esquerdo da tabela dinâmica (Fig. 13).
Arraste o campo data para a área de colunas da tabela dinâmica.
Arraste o campo Índice para a área de colunas da lista de campos da tabela dinâmica.
Selecione o campo Região, que aparecerá na coluna esquerda da Tabela Dinâmica.
Selecione o campo Renda que aparece na área de valores da tabela dinâmica.

Arroz. 11. Comece agrupando por campo data

Neste estágio, nossa tabela dinâmica parece bastante primitiva (Fig. 14). Eu realmente não gosto das inscrições Nomes de linha E Nomes de colunas. Não é prático exibir totais para Plano de janeiro E Fato de janeiro na coluna D, etc. Mas não se preocupe com a aparência desta tabela dinâmica, porque ninguém mais a verá, exceto você. A partir de agora, criaremos um shell de relatório, cuja fonte de dados será a tabela dinâmica recém-criada.

Siga estes passos (Fig. 15).

Na célula A1, insira o nome do relatório - Indicadores planejados e reais por região.
Vá para a guia lar, clique no botão Estilos de células selecione o formato Cabeçallho 1.
Na célula A2, insira a fórmula =MÊS(HOJE();0). Esta função retorna o último dia do mês atual. Por exemplo, se você estiver lendo isto em 14 de agosto de 2014, a célula A2 exibirá a data 31 de agosto de 2014.
Selecione a célula A2. Pressione a combinação de teclas Ctrl+1 para exibir a caixa de diálogo Formato de célula. Na aba Número clique no item Todos os formatos. Insira um formato de número personalizado como "A partir do mês "MMMM" indicadores planejados"(Fig. 16). Como resultado, a data calculada aparecerá como texto.
Na célula A5, insira um título Região.
Insira os títulos das regiões nas células restantes da Coluna A. Os títulos das regiões devem corresponder aos nomes das regiões listados na Tabela Dinâmica.
Se necessário, adicione rótulos à coluna dos totais departamentais.
Na parte inferior do relatório, adicione a linha Total para a empresa.
Na célula B4, insira a fórmula =DATA(ANO($A$2),COLUNA(A1),1). Esta fórmula retorna as datas 01/01/2014, 01/02/2104, etc., os primeiros dias de todos os 12 meses do ano atual.
Selecione a célula B4. Pressione a combinação de teclas Ctrl+1 para abrir a janela Formato de célula. Na aba Número No capítulo Todos os formatos insira o formato de número personalizado MMM. Este formato exibe o nome do mês com três letras. Alinhe o texto à borda direita da célula.
Copie o conteúdo da célula B4 para o intervalo C4:M4. Uma linha com os nomes dos meses aparecerá no topo da tabela dinâmica.
Na célula B5, insira a fórmula =SE(MÊS(B4)<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово Facto, e para o presente e o futuro - Plano.
Adicione um título à célula N5 Resultado final. Para a célula O4 - Resultado final, O5 - Plano, P5 - % desvio.
Insira as fórmulas usuais do Excel usadas para calcular totais departamentais, linhas de total da empresa, colunas de total geral e colunas de variação percentual:
1. na célula B8, insira a fórmula =SUM(B6:B7) e copie-a para outras células da linha;
2. na célula N6, insira a fórmula =SUM(B6:M6) e copie-a para outras células da coluna;
3. na célula P6, insira a fórmula =IFERROR((N6/O10)-1;0) e copie-a para outras células da coluna;
4. na célula B13, insira a fórmula =SUM(B10:B12) e copie-a para outras células da linha;
5. na célula B17, insira a fórmula =SUM(B15:B16) e copie-a para outras células da linha;
6. Na célula B19, insira a fórmula =SOMA(B6:B18)/2 e copie-a para outras células da linha.
Aplique o estilo Título 4 às legendas na coluna A e aos títulos nas linhas 4 e 5.
Para o intervalo de células B6:O19, selecione o formato numérico # ##0.
Para células da coluna P, selecione o formato numérico 0,0%.

Arroz. 15. Reporte shell antes de adicionar fórmulas GET.PIVOT.TABLE.DATA

Selecione a célula B6 do shell do relatório. Esta célula corresponde à região Nordeste e aos números reais de janeiro.

Digite = (sinal de igual) para começar a inserir a fórmula.
Acesse a planilha da tabela dinâmica e clique na célula que corresponde à região Nordeste e aos indicadores reais de janeiro - C12 (Fig. 17).
Pressione a tecla Digitar para terminar de inserir a fórmula e retornar ao shell do relatório. Como resultado, o Excel adicionará a função GET.PIOTTABLE.DATA à célula B6. A célula exibirá o valor de $ 277.435.

Argumentos de função:

Campo de dados. Campo da área de valores da tabela dinâmica. Atenção: neste caso o campo é usado Renda, mas não Valor por campo Renda.
Tabela_dinâmica. Com esta opção, a Microsoft pergunta: “Qual tabela dinâmica você deseja usar?” Basta especificar uma das células da tabela dinâmica. Entrada ‘Fig. 11-14’!$A$3 refere-se à primeira célula da tabela dinâmica na qual os dados são inseridos. Como no nosso caso podemos especificar qualquer célula relacionada à tabela dinâmica, deixe o argumento inalterado. O endereço de célula $A$3 é adequado em todos os aspectos.
Campo 1; elemento 1. Na fórmula gerada automaticamente, o nome é selecionado como o nome do campo Região, e como o valor do campo - Nordeste. É aqui que residem os problemas que surgem ao trabalhar com a função GET.PIVOT.TABLE.DATA. Os valores selecionados automaticamente não podem ser copiados porque são codificados. Portanto, se as fórmulas forem copiadas em toda a área do relatório, será necessário alterá-las manualmente. Em vez do valor Nordeste, substitua a referência de célula no formato $A6. Ao especificar um cifrão antes do nome da Coluna A, você especifica que a parte da linha da referência pode ser alterada ao copiar a fórmula nas células da coluna.
Campo 2; elemento 2. Este par de argumentos especifica o campo data com valor 1. Se a tabela dinâmica original foi agrupada por mês, o campo mês mantém o nome do campo original data. O valor numérico do mês é 1, que corresponde a janeiro. Não é aconselhável usar esse valor ao criar fórmulas enormes especificadas em dezenas ou mesmo centenas de células de relatório. É melhor usar uma fórmula que calcule os valores dos campos data, semelhante à fórmula na célula B4. Em vez de 1 neste caso, você pode usar a fórmula MÊS(B$4). Um cifrão antes do 4 indica que a fórmula pode atribuir valores ao campo data com base em outros meses à medida que a fórmula é copiada nas células da linha.
Campo 3; elemento 3. Neste caso, o nome do campo é atribuído automaticamente Índice e valor do campo Facto. Esses valores estão corretos para janeiro, mas para os meses subsequentes o valor do campo deverá ser alterado para Plano. Alterar um valor de campo codificado Facto por link B$5.
Campo 4; elemento 4. Esses argumentos não são usados porque os campos acabaram.

Arroz. 19. Após a conclusão da edição, a fórmula GET.PIOTTABLE.DATA é adequada para copiar para todas as células do intervalo

Arroz. 20. Ajuste o layout dos campos na área de colunas para que apareça uma coluna Resultado do plano

Arroz. 21. Relatório final que pode ser apresentado ao gestor

Atualize o relatório. Para atualizar o relatório com dados dos meses futuros, siga estas etapas:

Insira métricas reais abaixo do conjunto de dados original. Como os dados de origem estão em formato de tabela, a formatação da tabela é automaticamente propagada para novas linhas de dados. A definição da tabela dinâmica original também foi ampliada (no arquivo Excel já adicionei os indicadores reais de todo o ano).
Vá para a Tabela Dinâmica. Clique com o botão direito e selecione Atualizar. A aparência da tabela dinâmica mudará, mas tudo bem.
Vá para o shell do relatório. Em princípio, tudo já foi feito para atualizar o relatório, mas não custa nada testar os resultados. Altere a fórmula na célula A2, por exemplo, para esta: =MÊS(HOJE() +31 ;0) e veja o que acontece.

Achei que nunca diria isso: "A função GET.DATA.PICTTABLE é a maior bênção. Como existimos sem ela antes?