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

Пересечение линий регрессии

Убеждаемся, что линии регрессии действительно пересекаются в указанной точке.

Второе примечательное свойство линейной регрессии — это взаимосвязь коэффициентов регрессии с коэффициентом линейной корреляции — см. формулы.

Взаимосвязь коэффициентов

Проверяем выполнение указанных соотношений.

Скопируем оба уравнения на отдельный лист и организуем расчёты.

Для извлечения квадратного корня используем функцию

SQRT

КОРЕНЬ.

Сравнение коэффициентов

Находим разность оценок коэффициента корреляции. Можно видеть, что эта разность практически равна нулю.

Мы познакомились с основными методами изучения взаимосвязи. Это корреляционный и регрессионный анализ. Далее мы применим рассмотренные методы к реальным данным.

Данные для работы будем загружать из глобальной сети интернет, причём это будут открытые и общедоступные данные. Никаких платных сервисов и закрытых подписок. Мы рассматриваем реальные примеры, в которых ЯВНО просматриваются некоторые закономерности.

Конечно, реальные данные отличаются от идеальных, смоделированных. Здесь появляются более сложные закономерности, распределения отличаются от стандартных, а уравнение связи может изменяться со временем.

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

В соответствии с вариантом задания загрузите реальные данные о компьютерных компонентах с любого сайта компьютерного магазина, например, www.nix.ru. Выберите не менее 10 ОДНОТИПНЫХ изделий с РАЗНЫМИ характеристиками. Постройте диаграмму разброса, проведите корреляционный и регрессионный анализ тремя способами. Установите, есть ли какая-то связь и можно ли ее описать уравнением.

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

Первое требование к исходным данным: выбирать ОДНОТИПНЫЕ изделия. Нет смысла сравнивать диски для массового применения и для серверов. Это разные типы изделий, разный уровень качества, надёжности и стоимости.

Второе требование к данным: должно быть РАЗНООБРАЗИЕ параметров в пределах одного типа. Разброс параметра должен быть как можно шире. В нашем случае, это объём диска.

Открываем сайт www.nix.ru. Заходим в раздел Комплектующие для компьютерови выбираем Жёсткие диски — HDD для серверов. HDD означает Hard Disk Drive, или жёсткий диск, или «винчестер».

Диски для серверов

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

Результаты поиска приведены на рисунке. Найдено 8 дисков. Размеры от 600 гигабайт до 8 терабайт. По цифрам просматривается общая тенденция: большие диски стоят дороже. Но есть и отклонения от этой закономерности.

Результаты поиска

Вводим данные в Excel вручную. Пока данных немного, мы можем себе это позволить. Когда информации много, используют другие технологии импорта данных.

Второй вариант ввода данных — выделить текст на странице сайта, скопировать в буфер обмена и вставить на новом листе Excel. Результаты вставки из буфера показаны на рисунке. Здесь есть некоторые проблемы. При копировании страницы сайта через буфер нам достаются не только текстовые и числовые данные, но и элементы оформления и интерфейса. К тому же, некоторые ячейки объединены, что нарушает структуру таблицы.

Вставка из буфера

Для улучшения процесса копирования используем программу Punto Switcher. Она доступна бесплатно по адресу:

https://yandex.ru/soft/punto/

Punto Switcher

Программа позволяет автоматически переключать раскладку клавиатуры и конвертировать текст. В данной работе нам пригодится ещё одна полезная функция: вставка текста без форматирования.

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

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

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

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

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

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

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

Find and Replace

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

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

Find what

Найти

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

Replace with

Заменить на

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

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

Replace All

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

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

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

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

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

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

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

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