Converter células da Tabela dinâmica em fórmulas da planilha

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 precisar de mais flexibilidade na criação do layout de um relatório de tabela dinâmica, você 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 Cubo ou usar a função INFODADOSTABELADINÂMICA. A conversão de 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 da Tabela Dinâmica e podem ser atualizadas para ver resultados atualizados. No entanto, com a possível exceção dos 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 recolhimento de níveis.

Saiba mais sobre cenários comuns para converter Tabelas Dinâmicas em fórmulas de planilha

Estes são exemplos típicos do que você pode fazer depois de converter células da 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 dispor os dados de forma personalizada. Você pode simplesmente mover e organizar as células no layout de design desejado, excluir as células que não são necessárias para o relatório mensal de equipe e formatar as células e a planilha de acordo com sua preferência.

Inserir linhas e colunas 

Suponha que você queira mostrar informações de vendas dos dois anos anteriores divididas por região e grupo de produtos e que queira 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 as 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 obter os resultados de cada linha.

Usar várias fontes de dados 

Suponha que você queira comparar os resultados entre um banco de dados de produção e um de teste para garantir que o banco de dados de teste esteja produzindo os 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 a fim de 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 os argumentos das fórmulas de Cubo para referências de célula na planilha e, em seguida, inserir valores diferentes nessas células para obter resultados diferentes.

Criar um layout de linha ou coluna não uniforme (também chamado de relatório assimétrico) 

Digamos que você precise criar um relatório que contenha uma coluna de 2008 chamada Vendas Reais e uma coluna de 2009 chamada Vendas projetadas, mas você não queira nenhuma outra coluna. Você pode criar um relatório que contenha apenas essas colunas, ao contrário de uma Tabela Dinâmica, que requer relatórios simétricos.

Criar 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 feitas por três vendedores específicos no mês de julho. Se você tiver conhecimento sobre expressões MDX e consultas OLAP, poderá inserir as fórmulas de cubo por conta própria. Embora essas fórmulas possam se tornar bastante elaboradas, você pode simplificar a criação e melhorar sua precisão usando o Preenchimento Automático de Fórmulas. Para obter mais informações, consulte Usar o preenchimento automático de fórmula.

Converter células em fórmulas que usam funções de cubo

1. Para salvar a Tabela Dinâmica para uso futuro, recomendamos que você faça uma cópia da pasta de trabalho antes de convertê-la clicando em Arquivo>Salvar como. Para obter mais informações, consulte Salvar um arquivo.

2. Prepare a Tabela Dinâmica para que você possa minimizar a reorganização das células após a conversão, fazendo o seguinte:

   • Altere para um layout que mais se assemelhe ao layout desejado.
   • Interaja com o relatório, filtrando, classificando e recriando o relatório, para obter os resultados desejados.

3. Clique na Tabela Dinâmica.

4. Na guia Opções , no grupo Ferramentas , clique em Ferramentas OLAP e 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.

5. Decida como você deseja converter a Tabela Dinâmica:
   Converter toda a Tabela Dinâmica 

   • Marque a caixa de marca Converter Filtros de Relatório.
     Isso converte todas as células em fórmulas de planilha e exclui toda a Tabela Dinâmica.
     Converter apenas a área de rótulos de linha, rótulos de coluna e valores da Tabela Dinâmica, mas manter os Filtros de Relatório 

   • Verifique se a caixa de marca Converter Filtros de Relatório está desmarcada. (Esse é o padrão.)
     Isso converte todas as células de rótulo de linha, rótulo de coluna e área de valores em fórmulas de planilha e mantém a Tabela Dinâmica original, mas apenas com 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 a versão 2000-2003 ou anterior, você só poderá converter a Tabela Dinâmica inteira.

6. Clique em Converter.
   A operação de conversão primeiro atualiza a Tabela Dinâmica para garantir que os dados atualizados sejam usados.
   Uma mensagem é exibida na barra de status enquanto a operação de conversão ocorre. Se a operação demorar muito e você preferir converter em outro momento, pressione ESC para cancelar a operação.

   Observação

   • Você não pode 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 foram criados 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 da célula é preservada, mas os estilos de Tabela Dinâmica são removidos porque esses estilos podem ser aplicados somente a Tabelas Dinâmicas.

Converter células usando a função INFODADOSTABELADINÂMICA

É possível usar a função INFODADOSTABELADINÂMICA em uma fórmula para converter células de Tabela Dinâmica em fórmulas de planilha quando você quiser trabalhar com fontes de dados não-OLAP, quando preferir não atualizar para o novo formato de Tabela Dinâmica versão 2007 imediatamente ou quando quiser evitar a complexidade do uso das funções de Cubo.

1. Verifique se o comando Gerar INFODADOSTABELADINÂMICA no grupo Tabela Dinâmica na guia Opções está ativado.

   Observação

   O comando Gerar INFODADOSTABELADINÂMICA define ou desmarca a opção Usar funções INFOTABELADINÂMICA para referências de Tabela Dinâmica na categoria Fórmulas da seção Trabalhando com Fórmulas na caixa de diálogo Opções do Excel .

2. Na Tabela Dinâmica, verifique se a célula que você deseja usar em cada fórmula está visível.

3. Em uma célula da planilha fora da Tabela Dinâmica, digite a fórmula desejada até o ponto em que deseja incluir os dados do relatório.

4. Clique na célula da Tabela Dinâmica que você deseja usar na fórmula na Tabela Dinâmica. Uma função de planilha INFODADOSTABELADINÂMICA é adicionada à fórmula que recupera os dados da Tabela Dinâmica. Esta função continua a recuperar os dados corretos se o layout do relatório for alterado ou se você atualizar os dados.

5. Conclua a digitação da fórmula e pressione ENTER.

Problema: Não é possível converter células da tabela dinâmica em fórmulas da planilha