Neste artigo veja boas práticas e recomendações com tabelas dinâmicas no Excel com download gratuito da planilha exemplo.
1. Introdução
As tabelas dinâmicas estão entre os recursos mais utilizados no Excel, com aplicação poderosa para relatórios, resumos e análises. Elas reservam incrível potencial para elevar o significado dos dados, sendo largamente empregadas e de uso praticamente mandatório em todos os trabalhos envolvendo dados.
Podemos ter aplicações desde triviais até complexas. Independente disso, temos alguns cuidados e boas práticas que podem e devem ser seguidas. Estes pontos estão fundamentados neste artigo com base em uma série de recomendações expostas a seguir.
2. Boas práticas e recomendações
2.1 Alterar o layout de exibição das seções das tabelas dinâmicas
Esta sugestão permite visualizar uma boa quantidade de colunas da fonte de dados nos campos da tabela dinâmica – seção colunas. Para efeito de comparação, tomemos como base a imagem seguinte.
A barra de rolagem destacada indica que há mais colunas da fonte de dados disponíveis para uso, devendo ser usada para visualizar as demais colunas omitidas. Esta disposição apresenta as 4 áreas (Filtros, Colunas, Linhas e Valores) em disposição 2 x 2, estando a seção de Campos na parte superior.
Aqui, a sugestão é alterar para permitir visualizar a maior quantidade de colunas possível, ao colocar a seção Campos ao lado das Áreas. Basta acionar o ícone superior da engrenagem e optar pelo layout destacado.
Como resultado, o layout é alterado permitindo visualizar todas as colunas disponíveis (neste nosso caso), dado o número de colunas na fonte de dados. Este layout não garante que sempre será possível visualizar todas as colunas, mas eleva consideravelmente as possibilidades de visualização sem que seja necessária rolagem adicional.
2.2 Usar o formato de tabela na fonte de dados em vez de intervalo
As tabelas dinâmicas podem ter várias fontes de dados, incluindo consultas e conexões do Power Query e Power Pivot (modelo de dados).
Quando consomem dados diretamente de planilhas, as tabelas dinâmicas podem ter a sua base de dados em intervalo convencional. No entanto, isso pode gerar um grave problema visto que a inclusão de novos registros não será identificada na fonte de dados pelas tabelas dinâmicas nela baseadas. Desta forma, a edição e atualização da tabela dinâmica só ficarão inteiramente abrangentes e corretas ao se atualizar o intervalo da fonte de dados manualmente. Esse retrabalho é muito inconveniente e pode ser facilmente evitado ao usarmos o formato de tabela na fonte de dados.
Conforme mostra a imagem seguinte, ao tentarmos alterar a fonte de dados de uma tabela dinâmica, nota-se 2 linhas ausentes (51 e 52) no intervalo considerado.
Já a próxima imagem mostra a fonte de dados que está considerando uma região devidamente formatada como tabela e nomeada como tabDados.
Agora estamos referenciando todo o intervalo. Isto é satisfatório porque permite a abrangência dinâmica e automática do conjunto de dados sem maiores problemas.
2.3 Desabilite o autoajuste de colunas
Por padrão, sempre que uma tabela dinâmica é atualizada ocorre o autoajuste da largura de suas colunas para comportar seu conteúdo, sejam textos ou números. No entanto, isto pode ser muito inconveniente quando há na mesma planilha alguns elementos, como segmentações de dados, gráficos e imagens, por exemplo. Estes objetos podem ser redimensionados ou movimentados quando a largura das colunas é alterada. É bem verdade que nas propriedades destes objetos podemos inibir o redimensionamento e movimentação, como mostra a imagem seguinte, mas configurar a tabela dinâmica para não executar o autoajuste de colunas pode ser a forma mais rápida de evitar o inconveniente.
Para proceder com a configuração da tabela dinâmica devemos clicar com o botão secundário em qualquer célula da tabela dinâmica e escolher Opções da Tabela Dinâmica.
Em seguida devemos desmarcar a opção Ajustar automaticamente a largura das colunas ao atualizar, disponível na guia Layout e Formato, finalizando em Ok.
2.4 Renomeie as tabelas dinâmicas
Esta é uma excelente prática para a organização da pasta de trabalho, facilitando o trabalho, especialmente quando usamos múltiplas segmentações de dados que podem estar conectadas a uma ou mais tabelas dinâmicas e gráficos dinâmicos. Os nomes escolhidos, que devem sempre ser amigáveis refletindo o teor de cada tabela dinâmica, facilitam as conexões de relatório, além de permitir identificar o vínculo dos gráficos dinâmicos. Teremos um efeito expressivo, especialmente em dashboards.
Para renomear uma tabela dinâmica, selecione pelo menos uma de suas células e com o botão secundário do mouse escolha “Opções da Tabela Dinâmica” para abrir a janela de opções.
Logo no topo poderemos renomear a tabela dinâmica, alterando o seu nome padrão para um nome amigável que será utilizado para a fácil identificação de sua natureza, finalizando em Ok.
2.5 Habilite a atualização da tabela dinâmica ao abrir o arquivo
Existem algumas alternativas para atualizar tabelas dinâmicas, incluindo rotinas em VBA, que podem ser disparadas com a ativação de uma planilha, como também o botão de atualizar (da própria tabela dinêmica) e o botão atualizar tudo, da guia Dados.
Outro método bastante prudente e que evita a exibição de tabelas dinâmicas desatualizadas é a própria abertura da pasta de trabalho. Podemos configurar uma tabela dinâmica para ser atualizada no ato da abertura do arquivo. Esta alternativa garante que a visualização da tabela dinâmica apresente valores corretos, mesmo que anteriormente no fechamento da pasta de trabalha os dados tenham ficado desatualizados.
Para configurar a atualização na abertura da pasta de trabalho, selecione pelo menos uma célula da tabela dinâmica e com o botão secundário do mouse escolha “Opções da Tabela Dinâmica” para abrir a janela de opções.
Na guia Dados, habilite a opção “Atualizar dados ao abrir o arquivo”.
Com isso, no momento da abertura do arquivo os dados serão atualizados e somente após esta etapa será possível que o usuário manipule normalmente a pasta de trabalho.
2.6 Elimine itens excluídos da fonte de dados (dados “fantasmas”) de Tabelas Dinâmicas
Selecione pelo menos uma célula da tabela dinâmica e com o botão secundário do mouse escolha “Opções da Tabela Dinâmica” para abrir a janela de opções.
Na guia Dados, escolha “Nenhum” para o item “Reter itens excluídos da fonte de dados”.
Com isso, dados que em algum momento existiram, mas foram deletados, não mais aparecerão em segmentações de dados e filtros de dimensões na tabela dinâmica.
P.S: esta opção é indisponível caso a tabela dinâmica seja derivada do modelo de dados.
2.7 Jamais posicione tabelas dinâmicas no Excel abaixo de outras
Este descuido é comum e ocorre principalmente em usuários iniciantes. O problema de empilhar tabelas dinâmicas verticalmente é a inibição da atualização da tabela dinâmica superior que, ao ser atualizada, deve exibir mais linhas, mas encontra como obstáculo outra tabela dinâmica. Com isso, uma notificação é exibida e a atualização não ocorre.
Veja a imagem a seguir em que a tabela dinâmica superior apresenta apenas 4 meses, havendo logo abaixo outra tabela dinâmica. Note que há apenas uma linha em branco (linha 8) entre ambas.
Com a inclusão de novos dados certamente haverá registros de meses inéditos, como setembro, outubro e dezembro, a citar. Quando é solicitada a atualização da tabela dinâmica, uma notificação é exibida (geralmente não lida pelo usuário), indicando a causa da não atualização.
Note que a mensagem é clara quando ao final informa para que o usuário libere espaço e tente novamente. Isso basicamente significa que é possível resolver o problema inserindo mais linhas em branco entre as tabelas dinâmicas, como o realizado e evidente na imagem seguinte.
Esta ação é arriscada porque normalmente não temos controle sobre o crescimento das tabelas dinâmicas. Imaginemos um resumo de vendas por clientes. Não é possível saber o número de clientes com vendas ao passar do tempo. Isso requer um espaço incerto e abundante entre tabelas dinâmicas.
O ideal é posicionarmos tabelas dinâmicas sempre lado a lado para diminuir as chances de sobreposição, como ilustra a imagem a seguir.
Download Planilha Tabelas Dinâmicas Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
