Как мне спроектировать данные временных рядов для удобства использования в сводных таблицах Excel?

Question

У меня есть таблица Excel с ежедневными данными за несколько лет в следующей форме:

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

У меня также есть список в следующей форме, где каждой категории из первой таблицы назначается одно из пяти описаний типов:

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

Я хочу иметь возможность систематизировать и суммировать эти данные с помощью сводной таблицы и строить временные ряды с помощью сводной диаграммы. Однако из-за того, как данные табулируются, я не могу сгруппировать их по категориям, типам или последовательным месяцам (мне интересно составлять графики данных по месяцам / годам, а не группировать их только по месяцам по годам).

Если бы я упорядочил данные, как в следующей таблице, все мои проблемы были бы решены, но я бы дублировал значения нескольких столбцов столько раз, сколько есть категорий (и я очень против дублирования данных):

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

Мой вопрос: как мне спроектировать свою таблицу, чтобы я мог быстро обрабатывать данные с помощью сводных таблиц, не дублируя всю остальную информацию?

Answer 1

Чтобы сводная таблица работала должным образом, вы должны расположить свои данные как базу данных: строка - это запись, а столбец - это поле, содержащее информацию об этой записи.

Итак, во-первых, наличие нескольких столбцов, содержащих несколько значений на определенную дату, кажется неправильным в моем понимании ситуации. Это означает, что множество записей (значений) объединяются в одну запись. По определению, наличие нескольких столбцов для разных категорий не позволяет вам осмысленно использовать эти данные в сводной таблице.

Если вы можете изменять исходные данные на постоянной основе, вот предлагаемый макет:

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

К которому вы можете добавить TYPE, используя формулу VLOOKUP, которая выбирает правильный тип для вашей категории, чтобы избежать дублирования и автоматически отражать любые изменения в вашей таблице TYPE. (Обратите внимание, что в вашей таблице типов подразумеваются перевернутые столбцы: CATEGORY | TYPE для работы VLOOKUP)

 | 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

Такая таблица будет содержать много строк, но подача этого в сводную таблицу позволит вам манипулировать ею всеми способами: по дате, по категории, по типу.

Фактически, ваши исходные данные были бы хорошим РЕЗУЛЬТАТОМ сводной таблицы, суммирующей значения по дате с меткой столбца в категории! И Бог знает, что может быть трудно взять результат сводной таблицы и преобразовать его обратно в данные, которые затем могут быть использованы для создания другой сводной таблицы. Решения, включающие ручной труд, макросы или несколько промежуточных формул.

Надеюсь, это поможет.