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

В соответствии с вариантом задания загрузите реальные данные о компьютерных компонентах с любого сайта компьютерного магазина, например, 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. Получаем числа попроще.

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

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