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

Точка пересечения линий

Можно видеть, что точка {Хср, Yср}является общей для обоих уравнений. Другими словами, уравнения линий регрессии выполняются для указанных значений.

Вычисляем средние значения Xи Y. Наносим эту точку на график. Настраиваем тип и размер маркера, цвет заливки и границы.

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

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

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

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

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

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

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

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

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