Como devo projetar dados de séries temporais para facilidade de uso pelas Tabelas Dinâmicas do Excel?

Question

Eu tenho uma planilha do Excel com vários anos de dados diários da seguinte forma:

Date       | Category1   | Category 2  | ...   | Category30
------------------------------------------------------------
dd/mm/yyyy |  value      |  value      | ...   |  value 
...        |  ...        | ...         | ...   | ...
------------------------------------------------------------

Eu também tenho uma lista no seguinte formulário, onde cada uma das categorias da primeira tabela é atribuída a uma das cinco descrições de tipo:

| Type  | Category   |
----------------------
| Type1 | Category1  |
| Type2 | Category2  | 
| Type2 | Category3  | 
| ...   | ...        | 
| Type5 | Category30 |
----------------------

Eu quero poder organizar e resumir esses dados usando uma Tabela Dinâmica e plotar a série temporal usando um Gráfico Dinâmico. No entanto, devido à maneira como os dados são tabulados, não posso agrupá-los por categoria, tipo ou mês sequencial (estou interessado em mapear os dados por mês / ano em vez de agrupá-los por apenas meses ao longo dos anos).

Se eu pedi os dados como na tabela a seguir, todos os meus problemas seriam resolvidos, mas eu estaria duplicando os valores de várias colunas quantas vezes houvesse categorias (e eu me oponho muito a duplicar dados):

Date        | Year  | Month  | Day  | Type    | Category    | Values
--------------------------------------------------------------------------
date(i)     | yyyy  | mmm    | dd   | Type(j) | Category(k) | value(i,j,k)
--------------------------------------------------------------------------

Minha pergunta é, como devo projetar minha tabela para que eu possa manipular rapidamente os dados com tabelas dinâmicas sem ter que duplicar todas as outras informações?

    

Answer 1

Para uma Tabela Dinâmica funcionar como esperado, você deve organizar seus dados como um banco de dados: uma linha é um registro e uma coluna é um campo que fornece informações sobre esse registro.

Então, primeiro, ter várias colunas contendo vários valores para uma data específica parece errado por entender a situação. Isso se traduz em ter muitos registros (valores) comprimidos em um registro. Por definição, ter várias colunas para diferentes categorias impede que você use esses dados de forma significativa em uma Tabela Dinâmica.

Se você puder modificar seus dados de origem permanentemente, aqui está um layout sugerido:

 | Date       | Category | Value
 | 01/01/2011 | 1        | x
 | 01/01/2011 | 2        | y
 | 01/01/2011 | 25       | z
 | 01/02/2011 | 1        | x

Para o qual você pode anexar o TYPE usando uma fórmula VLOOKUP que busca o tipo adequado para sua categoria para evitar duplicação e refletir automaticamente qualquer alteração na tabela TYPE. (Observe que isso pressupõe colunas invertidas em sua tabela de tipos: CATEGORY | TYPE para VLOOKUP funcionar)

 | Date       | Category | Type                           | Value
 | 01/01/2011 | 1        | VLOOKUP(B2, 'TypeTable', 2, 0) | x
 | 01/01/2011 | 2        | VLOOKUP(C2, 'TypeTable', 2, 0) | y
 | 01/01/2011 | 25       | VLOOKUP(D2, 'TypeTable', 2, 0) | z
 | 01/02/2011 | 1        | VLOOKUP(E2, 'TypeTable', 2, 0) | x

Essa tabela conterá muitas linhas, mas alimentar isso em uma Tabela Dinâmica permitirá que você a manipule de todas as formas: por data, por categoria, por tipo.

Na verdade, seus dados de origem seriam um bom RESULTADO de uma Tabela Dinâmica, resumindo os valores por data com um rótulo de coluna na categoria! E Deus sabe que pode ser difícil obter um resultado de Tabela Dinâmica e convertê-lo de volta para dados que podem ser usados para criar outra Tabela Dinâmica. Soluções que incluem trabalho manual, macros ou um monte de fórmulas intermediárias.

Espero que ajude.