Статистический анализ взаимосвязи в Excel

Переходим к анализу. Вначале построим диаграмму разброса. Пока всё делаем так же, как и в предыдущих разделах. Рассматриваем график. Разброс по объёму хороший. А вот цены слишком разные. Возможно, здесь собраны диски двух видов. И для каждого вида будет своя зависимость.

Все загруженные данные

Возвращаемся к исходному списку. Выясняем, что большинство дисков имеют следующие параметры:

— интерфейс подключения — SATA 6Гб/сек;

— размер диска — 3.5 дюйма;

— частота вращения 7200 оборотов в минуту.

Обзначение rpm расшифровывается как revolutions per minute, то есть оборотов в минуту.

Несколько дисков выпадают из общей картины. Это другой производитель и другой тип дисков — SAS. На досуге разберитесь, что такое SATA и что такое SAS.

Для обработки оставим только более-менее однотипные изделия одного семейства Ultrastar. Диаграмма разброса стала более привычной. Между делом ознакомьтесь с семействами Ultrastar и Gold. Конечно, для серьёзного анализа нужно взять данных побольше. И из разных источников. И учесть рекомендованные цены производителей.

Однотипные диски

То, что мы оставили для обработки, называется ОДНОРОДНЫЕ данные. Такие данные можно обрабатывать статистическими методами. А если у нас объекты разных типов, разных свойств, то статистика даёт совершенно дикие и никому не нужные результаты. Это всё равно, что взять взрослых людей ростом два метра и детей ростом полметра. Вычисляем средний рост: «метр с кепкой». Привозим в магазин одежду такого размера, а её никто не купит! Просто таких «средних» покупателей нет среди наших клиентов. Но зато можно найти самый ходовой размер для взрослых и для детей ПО ОТДЕЛЬНОСТИ. Вот такую одежду мы сможем продать. В статистике этот показатель называется МОДА. Это значение статистического признака, которое встречается чаще всего.

Итак, у нас осталось четыре диска. Находим коэффициент линейной корреляции. Получаем значение 0,9993. Это практически прямая линия. Почти никакого случайного разброса вокруг прямой.

Находим уравнение регрессии с помощью надстройки:

Y = 4,272 +1,558 X.

Можно записать это уравнение в «экономическом» стиле. То есть русскими словами:

Цена диска т.р. = 4,272 +1,558 • Объём диска Гб.

По уравнению регрессии строим вспомогательную табличку из двух точек.

Попутно найдём ЛИНЕЙНЫЙ ПРОГНОЗ. Это наш прогноз значения Y для выбранного значения X по линейному уравнению регрессии. В исходных данных нет диска на 4 Гб. Судя по нашему уравнению, он должен стоить примерно 10,5 тыс. руб.

Вспомогательная таблица

Наносим линию регрессии на диаграмму разброса. Точки почти лежат на прямой линии.

Линия регрессии

Рассмотрим ещё один популярный момент — «Цена за гигабайт». Во сколько обходится хранение данных на дисках разного размера? Просто поделим рубли на гигабайты. С увеличением объёма диска стоимость хранения данных падает. Так что для большого сервера могут оказаться более экономичными большие диски.

Стоимость хранения данных

Фондовый рынок

В этом разделе мы будем анализировать биржевые данные. Мы посмотрим, как выглядит взаимосвязь между котировками наиболее ликвидных акций и значениями соответствующего отраслевого индекса Московской биржи. Для этого нужно будет скачать два файла — котировки акции и отраслевой индекс (в соответствии с вариантом задания). Затем мы загрузим эти файлы в Excel и проведём корреляционный и регрессионный анализ.

Для начала ознакомимся с заданием. Создадим новый лист в рабочей книге Excel и опишем свой вариант задания.

В интернете есть много ресурсов, где можно бесплатно загрузить биржевые котировки — так называемые ИСТОРИЧЕСКИЕ ДАННЫЕ. Адреса могут со временем изменяться. Работа сайтов может быть нестабильной. Могут появляться новые источники данных.

В качестве примера рассмотрим три источника данных:

MOEX.RU

FINAM.RU

INVESTING.COM

В данной работе нас будет интересовать качество полученных данных и удобство их загрузки в Excel для дальнейшей обработки.

Финам

Откроем сайт компании «Финам» www.finam.ru в браузере. Перейдём по ссылке Теханализ.

Сайт «Финам»

На открывшейся странице переходим по ссылке Экспорт котировок.

Ссылка на страницу экспорта

Выбираем Российские индексы в выпадающем списке.

Выбор индексов

В выпадающем списке инструментов выбираем IMOEX.

Выбор инструмента

Установим следующие настройки экспорта данных:

— интервал: диапазон дат 10 лет

— периодичность: 1 день

— разделитель полей: точка с запятой

— разделитель разрядов: нет

— формат записи: TICKER, PER, DATE, TIME, OPEN, HIGH, LOW, CLOSE, VOL

— добавить заголовок файла: да

Настройки экспорта

Нажимаем кнопку Получить файл. Сохраняем файл на диске и обращаем внимание, куда именно его сохранили. Ведь этот файл нам предстоит загружать в Excel.