Валентин Арьков - Статистический анализ взаимосвязи в Excel

Вставим данные из буфера в Excel без форматирования. Теперь мы получаем стандартное расположение ячеек электронной таблицы. Стандартный шрифт. И никаких лишних элементов.

Вставка без форматирования

Создадим копию листа и «очистим» данные. Удалим лишние столбцы. Оставим только размер и цену. Данные придётся скопировать в нужные ячейки вручную, потому что они находятся в разных строчках. Можно взять весь диапазон ячеек с ценами, вырезать его в буфер и вставить в нужное место таблицы. Удалим лишние строки. Сравним с исходной страничкой сайта и убедимся, что данные введены правильно. По дороге пропала одна строчка. Вводим данные вручную.

Обратите внимание на выравнивание содержимого ячеек. Первый столбец прижат влево — это текст. Второй столбец прижат вправо — это числа.

Исходные данные

Осталось избавиться от лишних букв. После каждого числа имеется пробел и буквы Тб. В нижней строке указаны гигабайты — нужно удалить буквы Гб и перевести число в терабайты, то есть поделить его на 1000.

Вызываем функцию поиска и замены. Нажимаем комбинацию клавиш [Ctrl + H]. Появляется диалоговое окно

Find and Replace

Найти и заменить.

В строке поиска

Find what

Найти

вводим символ пробела, знак вопроса(искать любой символ) и букву б.

Replace with

Заменить на

Оставляем пустым.

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

Replace All

Заменить все.

В результате в первом столбце остались только числа. Первый столбец прижат вправо — теперь это числа.

Поиск и замена

Осталось вручную заменить 600 на 0.6. Напомним нашу «таблицу умножения»: в одном терабайте примерно 1000 гигабайт. Если быть точным, то вообще-то должно быть 1024. Но многие производители компьютерной техники используют множитель 1000. Ну а сами числа хорошенько округляют.

Теперь поработаем со вторым столбцом. Здесь цены в рублях. И это пятизначные числа. Тысячи и десятки тысяч рублей. Разделим на 1000. Получаем числа попроще.

Данные для анализа

Убираем второй столбец и оставляем только очищенные, предварительно обработанные исходные данные. Добавляем комментарии в духе «Что? Где? Когда?» Вставляем копию страницы сайта, чтобы можно было убедиться в правильности загрузки данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

Y = 4,272 +1,558 X.

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