Изменение типа графика
Открывается диалоговое окно
Change Chart Type
Изменить тип диаграммы.
Выбираем комбинированный тип графика:
Combo
Комбинированная.
Устанавливаем для обоих графиков
Scatter with Straight Lines
Точечная с прямыми отрезками.
Включаем вторую вертикальную ось:
Secondary Axis
Вспомогательная ось.
Комбинированный график
Настраиваем диапазон значений по каждой оси. Устанавливаем заголовки по осям. Получаем более-менее информативную диаграмму — см. рисунок.
Комбинированная диаграмма
Можно видеть, что в целом направление изменения совпадает, хотя иногда котировки акций отклоняются от движения индекса.
Построим диаграмму разброса. Настраиваем заголовки, маркер, линию. Включаем построение линейной регрессии и вывод уравнения на экран.
Диаграмма разброса
Подобный анализ проводится при построении инвестиционного портфеля. Прочитайте статью Бета-коэффициент в Википедии.
Вызываем функцию CORREL и находим значение коэффициента линейной корреляции: r = 0.924. Это значение говорит о тесной положительной линейной связи между котировками акций и значением индекса.
Благодарности
Автор выражает глубокую признательность студентам, участвовавшим в подготовке данных методических указаний. Следующие студенты активно помогали в постановке лабораторных работ и составлении чернового варианта методических указаний:
— Корнеева Мария
— Кострюкова Анна
— Токарева Татьяна
— Муслимов Роберт
В тестировании учебных материалов, выявлении упущенных моментов и оценке трудоёмкости работ участвовали следующие студенты:
— Яковлева Виктория
— Исхаков Радмир
— Халиков Ильшат
— Мурадян Гарик
— Мухаметьянова Ольга
— Хайретдинова Ирина
— Макаров Владислав
— Макрушин Константин
— Портнов Владислав
В рамках проекта тестировщики общались с составителями, и после каждого очередного исправления текст становился всё более понятным.
Такое разделение труда позволило автору сосредоточиться на творческой части работы и увидеть картину в целом, не отвлекаясь на текущие вопросы.
Кстати, в процессе работы попутно выяснилось, насколько большой поток информации требуется обрабатывать для текущей координации проекта. Так что в следующий раз нам дополнительно потребуется куратор, который освоит управление проектом.
Послесловие
В данном выпуске мы познакомились с анализов взаимосвязи методами корреляционного и регрессионного анализа. Это второй выпуск в серии «Бизнес-аналитика и статистика в Excel». В нашей серии учебных пособий имеются следующие выпуски:
1. Анализ распределения. Сводка и группировка данных. Оценка формы распределения. Вычисление статистических показателей. Соответствующие разделы предмета: «Введение», «Распределение», «Статистические показатели», «Сводка и группировка», «Статистические графики». В конечном счёте мы построим графики (гистограмму и кумуляту), а также оценим значения показателей — таких как «сигма».
2. Корреляционный и регрессионный анализ. Статистическое изучение взаимосвязи. Соответствующий раздел предмета: «Взаимосвязь явлений», «Фондовые индексы». Мы узнаем, что прямую линию можно провести через любое количество точек, если делать это «в среднем». А ещё нам предстоит выяснить, как связаны между собой различные отрасли экономики — если посмотреть на них глазами Московской биржи.
3. Анализ динамики. Компоненты рядов динамики. Скользящие средние. Уравнение и линия тренда. Соответствующий раздел предмета: «Динамика». Здесь мы рассмотрим составные части динамики (изменения во времени) и биржевые графики, на которых наносят общую тенденцию (тренд).
4. Бизнес-аналитика на основе сводных таблиц. Соответствующий раздел предмета: «Бизнес-аналитика». Это одновременно и самый простой, и самый сложный раздел, потому что настройка таблиц делается визуально, через меню, а внутри спрятаны уже изученные статистические методы: сводка и группировка, показатели, взаимосвязь, динамика и т. д. Снаружи мы в конечном счёте увидим «приборную панель» руководителя предприятия — такую же, как приборная панель автомобиля.