Составление квартальных и ежемесячных данных в Excel

Question

У меня есть один ежемесячный ряд данных в одной электронной таблице и один квартал в другой электронной таблице. Каков "самый умный" способ построить их вместе в одном графике (в Excel)? Это довольно много данных, поэтому я бы не стал возиться с этим вручную.

РЕДАКТИРОВАТЬ: данные представлены следующим образом:

Ежемесячно:

2011-12-01  1246.91
2011-11-01  1251
2011-10-03  1131.21
2011-09-01  1219.12
2011-08-01  1292.59
2011-07-01  1320.64
2011-06-01  1345.2

Ежеквартально:

1947q1  237.2
1947q2  240.4
1947q3  244.5
1947q4  254.3
1948q1  260.3
1948q2  267.3
1948q3  273.8
1948q4  275.1

Можно предположить, что 1947q1 - это тот же день, что и соответствующие месячные данные за январь.

Answer 1

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

Мне нравятся столбцы для квартальных данных, потому что я могу сделать их достаточно широкими, чтобы охватить трехмесячные месячные данные для каждого квартала.

Чтобы создать график, начните с выбора месячных данных и вставки линейного графика. Excel автоматически сортирует даты, поэтому они растут слева направо (диаграмма 1 ниже).

Выберите и скопируйте квартальные данные, выберите диаграмму и используйте вкладку «Главная»> «Раскрыть вставку»> «Специальная вставка», чтобы добавить данные в виде новой серии, серии в столбцах, имен серий в первой строке, категорий в первой колонке. Новая серия не видна (пока).

Выберите новую серию (вы ее не видите, но если вы выберете видимую серию и нажмете клавишу со стрелкой вверх, вы ее выберете), и отформатируете ее, чтобы назначить ее для дополнительной оси. Вы до сих пор не видите его, но в Excel добавлена дополнительная вертикальная ось (диаграмма 2).

Используйте пометку «+», плавающую рядом с диаграммой (Excel 2013+), или кнопки ленты, чтобы добавить дополнительную горизонтальную ось (диаграмма 3). Теперь мы куда-то добираемся.

Эта ось не сортируется автоматически, так как Excel не распознает 2011Q4 как дату. Поэтому отформатируйте вторичную горизонтальную ось: отметьте Категории в обратном порядке и отметьте Кресты осей в автоматическом режиме (диаграмма 4). Не беспокойтесь о разных масштабах оси Y.

Щелкните правой кнопкой мыши квартальный ряд данных на диаграмме, выберите «Изменить тип диаграммы ряда» и выберите тип столбца с кластерами (диаграмма 5). Не беспокойтесь, что столбики - это сталактиты, а не сталагмиты.

Отформатируйте вторичную вертикальную ось (правый край диаграммы) и установите флажок Крестики в автоматическом режиме. Это перемещает квартальную ось и ее метки в нижнюю часть графика (диаграмма 6).

Скройте квартальную ось: отформатируйте ее так, чтобы она не использовала строку, а ее положение метки - «Нет меток» (диаграмма 7).

Удалить вторичную вертикальную ось. Теперь обе серии используют основную ось для масштабирования (диаграмма 8).

Наконец отформатируйте столбики. Я использовал ширину зазора 20%, чтобы каждый из них занимал три месяца линейного графика. Я использовал прозрачность 25%, чтобы цвет заливки полосы не был слишком жирным, и чтобы вы могли видеть линии сетки за ними. Я добавил метки данных на столбцы, используя положение «Внутренняя база» и «Имя категории» вместо «Значение» (диаграмма 9).

Answer 2

Имея дело с подобной проблемой. То, что вы хотите сделать, это построить точечный график вместо линейного графика. Scatter легко отображает временные ряды разных частот, потому что вы можете указать разные серии осей X для разных временных рядов оси Y.

На вкладке «Вставка» вставьте точечный график (возможно, в вашем случае прямолинейный график). Щелкните правой кнопкой мыши по графику и выберите нужные серии данных, установив временные рамки, связанные с каждой серией. Затем вы можете щелкнуть правой кнопкой мыши по оси и установить максимальные и минимальные значения в дополнительных единицах. это займет немного проб и ошибок, но должно быть довольно простым.

Удачи, частичная публикация для будущих пользователей с тем же вопросом, поскольку он не интуитивно понятен.

Answer 3

Сначала вы должны решить, что вы хотите построить. Например, хотите ли вы ежемесячный или квартальный график?

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

Если вам нужен квартальный график, вам необходимо решить, будут ли месячные данные сопоставлены с квартальными данными, используя среднеквартальное значение, значение на конец периода или другое преобразование.

Это решение будет зависеть от того, что вам нужно наметить - нет простого ответа.

Что касается того, как связать данные, используйте пару формул для создания идентификатора, по которому вы можете выполнять поиск. Подобно:

=year()*10+month()  and pass a date as parameter
=value(replace("2003q2";"q";""))

оба будут давать месяцы в формате 20031, упрощая использование функций поиска.