✅ Preparando dados de origem para tabela dinâmica

Índice

Qual é um bom design para os dados de origem da tabela dinâmica?
Armadilhas comuns a serem evitadas ao preparar os dados de origem
Exemplos de designs de dados de origem inadequados
[ESTUDO DE CASO] Convertendo dados mal formatados em dados de origem prontos para tabela dinâmica

Ter os dados no formato correto é uma etapa crucial na criação de uma Tabela Dinâmica robusta e sem erros. Se não for feito da maneira certa, você pode acabar tendo muitos problemas com sua tabela dinâmica.

Qual é um bom design para os dados de origem da tabela dinâmica?

Vamos dar uma olhada em um exemplo de boa fonte de dados para uma Tabela Dinâmica.

Aqui está o que o torna um bom design de dados de origem:

A primeira linha contém cabeçalhos que descrevem os dados nas colunas.
Cada coluna representa uma categoria de dados exclusiva. Por exemplo, a coluna C tem apenas dados do produto e a coluna D e apenas dados do mês.
Cada linha é um registro que representaria uma instância da transação ou venda.
Os cabeçalhos de dados são exclusivos e não se repetem em nenhum lugar do conjunto de dados. Por exemplo, se você tiver números de vendas para quatro trimestres em um ano, NÃO deve nomear todos eles como Vendas. Em vez disso, dê a esses cabeçalhos de coluna nomes exclusivos, como Vendas Q1, Vendas Q2 e assim por diante …
- Se você não tem títulos exclusivos, ainda pode ir em frente e criar uma Tabela Dinâmica e o Excel automaticamente os tornará exclusivos adicionando um sufixo (como Vendas, Vendas2, Vendas3). No entanto, essa seria uma maneira horrível de preparar e usar uma Tabela Dinâmica.

Armadilhas comuns a serem evitadas ao preparar os dados de origem

Não deve haver colunas em branco nos dados de origem. Este é fácil de detectar. Se você tiver uma coluna em branco nos dados de origem, não será possível criar uma Tabela Dinâmica. Ele mostrará um erro conforme mostrado abaixo.
Não deve haver células / linhas em branco nos dados de origem. Embora você possa criar uma Tabela Dinâmica com êxito, apesar de ter células ou linhas em branco, há muitos efeitos colaterais que podem afetar você no final do dia.
- Por exemplo, digamos que você tenha uma célula em branco na coluna de vendas. Se você criar uma Tabela Dinâmica usando esses dados e colocar o campo de vendas na área das colunas, ela mostrará a CONTAGEM e não a SOMA. Isso ocorre porque o Excel interpreta a coluna inteira como tendo dados de texto (apenas por causa de uma única célula em branco).
Aplique o formato relevante às células nos dados de origem. Por exemplo, se você tiver datas (que são armazenadas como números de série no back-end do Excel), aplique um dos formatos de data aceitáveis. Isso ajudaria a criar a Tabela Dinâmica e usar a Data como um dos critérios para resumir, agrupar e classificar os dados.
- Se você tiver alguns segundos, tente isso. Formate as datas em sua Tabela Dinâmica como números e, em seguida, crie uma Tabela Dinâmica usando esses dados. Agora na Tabela Dinâmica, selecione o campo de data e veja o que acontece. Ele o colocará automaticamente na área de valores. Isso ocorre porque sua Tabela Dinâmica não sabe que essas são datas. Ele os interpreta como números.
Não inclua quaisquer totais de colunas, totais de linhas, médias, etc., como parte dos dados de origem. Assim que tiver a Tabela Dinâmica, você poderá obtê-la facilmente mais tarde.
Sempre crie uma Tabela do Excel e, em seguida, use-a como fonte para uma Tabela Dinâmica. Esta é mais uma boa prática e não uma armadilha. Sua tabela dinâmica funcionaria bem com dados de origem que também não são uma tabela do Excel. A vantagem do Excel Table é que ele pode ajustar os dados de expansão. Se você adicionar mais linhas ao conjunto de dados, não será necessário ajustar os dados de origem repetidamente. Você pode simplesmente atualizar a Tabela Dinâmica e ela será responsável automaticamente pelas novas linhas adicionadas aos dados de origem.

Exemplos de designs de dados de origem inadequados

Vamos dar uma olhada em alguns exemplos ruins de designs de dados de origem.

Projeto de dados de origem ruim - Exemplo 1

Esta é uma maneira comum de manter os dados, pois são fáceis de seguir e compreender. Existem dois problemas com este arranjo de dados:

Você não tem a imagem completa. Por exemplo, você pode ver que as vendas do Centro-Oeste no trimestre 1 são 2924300. Mas é uma venda única ou um número de vendas. Se você tiver cada registro disponível em uma linha separada, poderá fazer uma análise melhor.
Se você for em frente e criar uma Tabela Dinâmica usando isso (o que você pode), obterá campos diferentes para trimestres diferentes. Algo conforme mostrado abaixo:

Projeto de dados de origem ruim - Exemplo 2

Esta representação de dados pode ser bem recebida pela administração e pelo público de apresentações em PowerPoint, mas não é adequada para a criação de uma Tabela Dinâmica.

Novamente, esse é o tipo de resumo que você pode criar facilmente usando uma Tabela Dinâmica. Portanto, mesmo se você eventualmente desejar essa aparência para seus dados, mantenha os dados de origem em um formato pronto para Pivô e crie essa visualização usando a Tabela Dinâmica.

Projeto de dados de origem ruim - Exemplo 3

Novamente, essa é uma saída que pode ser facilmente obtida usando uma Tabela Dinâmica. Mas não pode ser usado para criar uma Tabela Dinâmica.

Existem células em branco no conjunto de dados e os trimestres são distribuídos como cabeçalhos de coluna.

Além disso, a região é especificada na parte superior, embora deva fazer parte de todos os registros.

[ESTUDO DE CASO] Convertendo dados mal formatados em dados de origem prontos para tabela dinâmica

Às vezes, você pode obter um conjunto de dados que não é adequado para ser usado como fonte de dados para a Tabela Dinâmica. Nesse caso, você pode não ter escolha a não ser converter os dados em um formato de dados amigável Pivot.

Aqui está um exemplo de design de dados ruim:

Agora você pode usar funções do Excel ou consulta dinâmica para converter esses dados em um formato que pode ser usado como dados de origem para a tabela dinâmica.

Vamos ver como esses dois métodos funcionam.

Método 1: usando fórmulas do Excel

Vamos ver como usar as Funções do Excel para converter esses dados em um formato pronto para Tabela Dinâmica.

Crie um cabeçalho de coluna exclusivo para todas as categorias no conjunto de dados original. Neste exemplo, seria Região, Trimestre e Vendas.
Na célula abaixo do cabeçalho Region, use a seguinte fórmula: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2) / COUNTA ($ B $ 1: $ E $ 1), 0))
- Arraste a fórmula para baixo e ela repetirá todas as regiões.
Na célula abaixo do cabeçalho Trimestre, use a seguinte fórmula: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1) , 0))
- Arraste a fórmula para baixo e ela repetirá todos os trimestres.
No cabeçalho abaixo de Vendas, use a seguinte fórmula: = ÍNDICE ($ B $ 2: $ E $ 5, CORRESPONDÊNCIA (G2, $ A $ 2: $ A $ 5,0), CORRESPONDÊNCIA (H2, $ B $ 1: $ E $ 1,0 ))
- Arraste-o para baixo para obter todos os valores. Esta fórmula usa os dados da região e do trimestre como os valores de pesquisa e retorna o valor de vendas do conjunto de dados original.

Agora você pode usar esses dados resultantes como dados de origem para a Tabela Dinâmica.

Clique aqui para baixar o arquivo de exemplo.

Método 2: Usando o Power Query

O Power Query tem um recurso que pode converter facilmente esse tipo de dados em formato de dados pronto para Pivot.

Se você estiver usando o Excel 2016, os recursos do Power Query estarão disponíveis na guia Dados no grupo Obter e transformar. Se estiver usando o Excel 2013 ou versões anteriores, você pode usá-lo como um suplemento.

Aqui está um excelente guia sobre como instalar o Power Query, de Jon do Excel Campus.

Novamente, considerando que você tem os dados formatados conforme mostrado abaixo:

Aqui estão as etapas para converter os dados de origem no formato pronto para Tabela Dinâmica:

Converta os dados em uma tabela do Excel. Selecione o conjunto de dados e vá para Inserir -> Tabelas -> Tabela.
Na caixa de diálogo Inserir Tabela, certifique-se de que o intervalo correto esteja selecionado e clique em OK. Isso converterá os dados tabulares em uma tabela do Excel.
No Excel 2016, vá para Dados -> Obter e transformar -> Da tabela.
- Se você estiver usando o suplemento Power Query em uma versão anterior, vá para Power Query -> Dados externos -> Da tabela.
No editor de consultas, selecione as colunas que você deseja remover de dinâmica. Neste caso, são os dos quatro trimestres. Para selecionar todas as colunas, segure a tecla Shift e selecione a primeira coluna e a última coluna.
No Query Editor, vá para Transform -> Any Column -> Unpivot Columns. Isso converterá os dados da coluna em um formato amigável de Tabela Dinâmica.
O Power Query dá nomes genéricos às colunas. Altere esses nomes para aqueles que você deseja. Nesse caso, altere Atributo para Trimestre e Valor para Vendas.
No Editor de Consultas, vá para Arquivo -> Fechar e Carregar. Isso fechará a caixa de diálogo Editor do Power Query e criará uma planilha separada que terá os dados com colunas não dinâmicas.