Валентин Арьков - Статистический анализ взаимосвязи в 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.

Для начала ознакомимся с форматом файла. Откроем загруженный файл в текстовом редакторе Notepad( Блокнот). В первой строке находятся заголовки столбцов. Поля разделены точками с запятой. Целая и дробная часть числа разделены точкой. Напомним, что в английской версии Excel в качестве разделителя используется ТОЧКА, в русской версии — ЗАПЯТАЯ. Закрываем окно редактора.

Текстовый файл

Переходим в окно Excel и выбираем в меню

File — Open — Browse — Text Files

Файл — Открыть — Обзор — Текстовые файлы.

Загрузка текстового файла

Выбираем файл и нажимаем кнопку

Open

Открыть.

В окне

Text Import Wizard — Step 1 of 3

Мастер текстов (импорт) — шаг 1 из 3

устанавливаем пункт

My data has headers

Мои данные содержат заголовки.

Убеждаемся, что в разделе

Original data type

Формат исходных данных

указан формат данных

Delimited

( с разделителями).

Нажимаем кнопку

Next

Далее.

Мастер импорта — шаг 1

Результат импорта можно контролировать в нижней части диалогового окна мастера импорта

Preview of file

Предварительный просмотр файла.

На следующих шагах импорта эта часть окна будет называться

Data preview

Образец разбора данных.

По умолчанию в качестве разделителя полей установлен символ табуляции

Delimiters — Tab

Символом-разделителем является — табуляция.

На этом шаге программа не разделяет поля и рассматривает содержимое каждой строки как одну ячейку.

Мастер импорта — шаг 2

Установим точку с запятой в качестве разделителя:

Delimiters — Semicolon

Символом-разделителем является — точка с запятой.

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

Убеждаемся, что в разделе предварительного просмотра появились столбцы.

Нажимаем кнопку

Next

Далее.

Выбранный формат данных для каждого столбца выводится в нижней части окна