Uma Tabela Dinâmica tem vários layouts que fornecem uma estrutura predefinida para o relatório, mas você não pode personalizar esses layouts. Se você precisar de mais flexibilidade para projetar o layout de um relatório de Tabela Dinâmica, poderá converter as células em fórmulas de planilha e alterar o layout dessas células aproveitando ao máximo todos os recursos disponíveis em uma planilha. Você pode converter as células em fórmulas que usam funções cube ou usar a função GETPIVOTDATA. Converter células em fórmulas simplifica muito o processo de criação, atualização e manutenção dessas tabelas dinâmicas personalizadas.
Quando você converte células em fórmulas, essas fórmulas acessam os mesmos dados que a Tabela Dinâmica e podem ser atualizadas para ver resultados atualizados. No entanto, com a possível exceção de filtros de relatório, você não tem mais acesso aos recursos interativos de uma Tabela Dinâmica, como filtragem, classificação ou expansão e níveis em colapso.
Observação: Ao converter uma Tabela Dinâmica de Processamento Analítico Online (OLAP), você pode continuar atualizando os dados para obter valores de medida atualizados, mas não pode atualizar os membros reais exibidos no relatório.
Saiba mais sobre cenários comuns para converter tabelas dinâmicas em fórmulas de planilha
Veja a seguir exemplos típicos do que você pode fazer depois de converter células de Tabela Dinâmica em fórmulas de planilha para personalizar o layout das células convertidas.
Reorganizar e excluir células
Digamos que você tenha um relatório periódico que precisa criar todos os meses para sua equipe. Você só precisa de um subconjunto das informações do relatório e prefere expor os dados de forma personalizada. Basta mover e organizar células em um layout de design desejado, excluir as células que não são necessárias para o relatório mensal da equipe e formatar as células e a planilha para atender à sua preferência.
Inserir linhas e colunas
Digamos que você deseja mostrar informações de vendas dos dois anos anteriores divididas por região e grupo de produtos e que deseja inserir comentários estendidos em linhas adicionais. Basta inserir uma linha e inserir o texto. Além disso, você deseja adicionar uma coluna que mostre vendas por região e grupo de produtos que não esteja na Tabela Dinâmica original. Basta inserir uma coluna, adicionar uma fórmula para obter os resultados desejados e, em seguida, preencher a coluna para baixo para obter os resultados de cada linha.
Usar várias fontes de dados
Digamos que você queira comparar resultados entre um banco de dados de produção e teste para garantir que o banco de dados de teste esteja produzindo resultados esperados. Você pode copiar facilmente fórmulas de célula e alterar o argumento de conexão para apontar para o banco de dados de teste para comparar esses dois resultados.
Usar referências de célula para variar a entrada do usuário
Digamos que você queira que todo o relatório seja alterado com base na entrada do usuário. Você pode alterar argumentos para as fórmulas do Cubo para referências de célula na planilha e, em seguida, inserir valores diferentes nessas células para derivar resultados diferentes.
Create uma linha não-uniforme ou layout de coluna (também chamado de relatório assimétrico)
Digamos que você precisa criar um relatório que contenha uma coluna de 2008 chamada Vendas Reais, uma coluna de 2009 chamada Projected Sales, mas você não deseja nenhuma outra coluna. Você pode criar um relatório que contém apenas essas colunas, ao contrário de uma Tabela Dinâmica, que requer relatórios simétricos.
Create suas próprias fórmulas de Cubo e expressões MDX
Digamos que você queira criar um relatório que mostre as vendas de um determinado produto por três vendedores específicos para o mês de julho. Se você tiver conhecimento sobre expressões MDX e consultas OLAP, você pode inserir as fórmulas do Cubo por conta própria. Embora essas fórmulas possam se tornar bastante elaboradas, você pode simplificar a criação e melhorar a precisão dessas fórmulas usando o Formula AutoComplete. Para obter mais informações, consulte Usar o preenchimento automático de fórmula.
Observação: Você só pode converter uma Tabela Dinâmica de Processamento Analítico Online (OLAP) usando esse procedimento.
-
Para salvar a Tabela Dinâmica para uso futuro, recomendamos fazer uma cópia da pasta de trabalho antes de converter a Tabela Dinâmica clicando em Arquivo > Salvar Como. Para obter mais informações, consulte Salvar um arquivo.
-
Prepare a Tabela Dinâmica para que você possa minimizar o rearranjo das células após a conversão fazendo o seguinte:
-
Altere para um layout que se assemelha mais ao layout desejado.
-
Interaja com o relatório, como filtrar, classificar e redesenhar o relatório, para obter os resultados desejados.
-
-
Clique na Tabela Dinâmica.
-
Na guia Opções , no grupo Ferramentas , clique em ferramentas OLAP e clique em Converter em Fórmulas.
Se não houver filtros de relatório, a operação de conversão será concluída. Se houver um ou mais filtros de relatório, a caixa de diálogo Converter em Fórmulas será exibida.
-
Decida como deseja converter a Tabela Dinâmica:
Converter toda a Tabela Dinâmica
-
Selecione a caixa Converter Filtros de Relatório marcar.
Isso converte todas as células em fórmulas de planilha e exclui toda a Tabela Dinâmica.
Converta apenas a área rótulos de linha de tabela dinâmica, rótulos de coluna e valores, mas mantenha os Filtros de Relatório
-
Verifique se a caixa Converter Filtros de Relatório marcar está limpa. (Este é o padrão.)
Isso converte todas as células de área de rótulo, rótulo de coluna e valores de linha em fórmulas de planilha e mantém a Tabela Dinâmica original, mas com apenas os filtros de relatório para que você possa continuar a filtrar usando os filtros de relatório.
Observação: Se o formato de Tabela Dinâmica for versão 2000-2003 ou anterior, você só poderá converter toda a Tabela Dinâmica.
-
-
Clique em Converter.
A operação de conversão atualiza primeiro a Tabela Dinâmica para garantir que os dados atualizados sejam usados.
Uma mensagem é exibida na barra status enquanto a operação de conversão ocorre. Se a operação demorar muito tempo e você preferir converter em outro momento, pressione ESC para cancelar a operação.
Observações:
-
Não é possível converter células com filtros aplicados a níveis ocultos.
-
Não é possível converter células nas quais os campos têm um cálculo personalizado que foi criado por meio da guia Mostrar Valores Como da caixa de diálogo Configurações de Campo de Valores . (Na guia Opções , no grupo Campo Ativo , clique em Campo Ativo e clique em Configurações do Campo de Valores.)
-
Para células convertidas, a formatação de células é preservada, mas os estilos de Tabela Dinâmica são removidos porque esses estilos podem ser aplicados apenas a tabelas dinâmicas.
-
Você pode usar a função GETPIVOTDATA em uma fórmula para converter células de Tabela Dinâmica em fórmulas de planilha quando quiser trabalhar com fontes de dados não OLAP, quando preferir não atualizar para o novo formato de Tabela Dinâmica 2007 imediatamente ou quando quiser evitar a complexidade de usar as funções Cube.
-
Verifique se o comando Gerar GETPIVOTDATA no grupo tabela dinâmica na guia Opções está ativado.
Observação: O comando Gerar GETPIVOTDATA define ou limpa a opção Usar funções GETPIVOTTABLE para referências de Tabela Dinâmica na categoria Fórmulas da seção Trabalhar com Fórmulas na caixa de diálogo Opções do Excel .
-
Na Tabela Dinâmica, verifique se a célula que você deseja usar em cada fórmula está visível.
-
Em uma célula de planilha fora da Tabela Dinâmica, digite a fórmula desejada até o ponto em que deseja incluir dados do relatório.
-
Clique na célula na Tabela Dinâmica que você deseja usar em sua fórmula na Tabela Dinâmica. Uma função de planilha GETPIVOTDATA é adicionada à fórmula que recupera os dados da Tabela Dinâmica. Essa função continua a recuperar os dados corretos se o layout do relatório for alterado ou se você atualizar os dados.
-
Termine de digitar sua fórmula e pressione ENTER.
Observação: Se você remover qualquer uma das células referenciadas na fórmula GETPIVOTDATA do relatório, a fórmula retornará #REF!.
