Составление квартальных и ежемесячных данных в Excel
У меня есть один ежемесячный ряд данных в одной электронной таблице и один квартал в другой электронной таблице. Каков "самый умный" способ построить их вместе в одном графике (в 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 - это тот же день, что и соответствующие месячные данные за январь.
3 ответа
Я предполагаю, что временной интервал квартальных данных соответствует месячным данным. Я расширил ежемесячные данные, включив в них целый год, и составил соответствующие квартальные данные и нанес их на график отдельно:
Мне нравятся столбцы для квартальных данных, потому что я могу сделать их достаточно широкими, чтобы охватить трехмесячные месячные данные для каждого квартала.
Чтобы создать график, начните с выбора месячных данных и вставки линейного графика. Excel автоматически сортирует даты, поэтому они отображаются в порядке возрастания слева направо (диаграмма 1 ниже).
Выберите и скопируйте квартальные данные, выберите диаграмму и используйте вкладку "Главная"> "Раскрыть вставку"> "Специальная вставка", чтобы добавить данные в виде новой серии, серии в столбцах, имен серий в первой строке, категорий в первой колонке. Новая серия не видна (пока).
Выберите новую серию (вы ее не видите, но если вы выберете видимую серию и нажмете клавишу со стрелкой вверх, вы ее выберете), и отформатируете ее, чтобы назначить ее для дополнительной оси. Вы по-прежнему не видите его, но в Excel добавлена дополнительная вертикальная ось (диаграмма 2).
Используйте пометку "+", расположенную рядом с диаграммой (Excel 2013+), или кнопки на ленте, чтобы добавить дополнительную горизонтальную ось (диаграмма 3). Теперь мы куда-то добираемся.
Эта ось не сортируется автоматически, так как Excel не распознает 2011Q4 как дату. Поэтому отформатируйте вторичную горизонтальную ось: отметьте Категории в обратном порядке и отметьте Кресты осей в автоматическом режиме (диаграмма 4). Не беспокойтесь о разных масштабах оси Y.
Щелкните правой кнопкой мыши квартальный ряд данных на диаграмме, выберите "Изменить тип диаграммы серии" и выберите тип столбца с кластерами (диаграмма 5). Не беспокойтесь, что столбики - это сталактиты, а не сталагмиты.
Отформатируйте вторичную вертикальную ось (правый край диаграммы) и установите флажок Крестики при автоматическом. Это перемещает квартальную ось и ее метки в нижнюю часть графика (диаграмма 6).
Скройте квартальную ось: отформатируйте ее так, чтобы она не использовала строку, а ее положение метки - "Нет меток" (диаграмма 7).
Удалить вторичную вертикальную ось. Теперь обе серии используют основную ось для масштабирования (диаграмма 8).
Наконец отформатируйте столбики. Я использовал ширину зазора 20%, чтобы каждый из них занимал три месяца линейного графика. Я использовал прозрачность 25%, чтобы цвет заливки полосы не был слишком жирным, и чтобы вы могли видеть линии сетки за ними. Я добавил метки данных на столбцы, используя позицию "Внутренняя база" и "Имя категории" вместо "Значение" (диаграмма 9).
Работа с аналогичной проблемой. То, что вы хотите сделать, это построить точечный график вместо линейного графика. Scatter легко отображает временные ряды разных частот, потому что вы можете указать разные серии осей X для разных временных рядов оси Y.
На вкладке "Вставка" вставьте точечный график (возможно, в случае прямой линии). Щелкните правой кнопкой мыши по графику и выберите желаемый ряд данных, установив временные рамки, связанные с каждым рядом. Затем вы можете щелкнуть правой кнопкой мыши по оси и установить максимальные и минимальные значения в дополнительных единицах. это займет немного проб и ошибок, но должно быть довольно простым.
Удачи, частичная публикация для будущих пользователей с тем же вопросом, поскольку он не интуитивно понятен.
Сначала вы должны решить, что вы хотите построить. Например, вы хотите месячный или квартальный график?
Если вам нужен месячный график, вы должны решить, будут ли квартальные данные сопоставлены с ежемесячными данными, повторяя значение квартала каждый месяц, составляя график только конца периода или чего-то еще.
Если вам нужен квартальный график, вам необходимо решить, будут ли месячные данные сопоставлены с квартальными данными, используя среднеквартальное значение, значение на конец периода или другое преобразование.
Это решение будет зависеть от того, что вам нужно наметить - нет простого ответа.
Что касается того, как связать данные, используйте пару формул для создания идентификатора, по которому вы можете выполнять поиск. Подобно:
=year()*10+month() and pass a date as parameter
=value(replace("2003q2";"q";""))
оба будут давать месяцы в формате 20031, упрощая использование функций поиска.