Name: Корреляционный и регрессионный анализ в электронных таблицах
Author: Валентин Арьков

Методические указания к выполнению лабораторной работы на тему «Корреляция и регрессия». Работа выполняется в пакете типа электронных таблиц.

digital

paper-a5-bw

https://store.ridero.ru/text-utils/v1/free-text/slug/korrelyacionnyi_i_regressionnyi_analiz_v_excel?volumeLimit=0.25

ridero

Лабораторный практикум

Корреляционный и регрессионный анализ в электронных таблицах

<div class="preview-fragment">Предисловие
Данная лабораторная работа позволяет освоить базовые методы исследования взаимосвязей в электронной таблице — корреляционный и регрессионный анализ.
При выполнении лабораторной работы используются знания и навыки, полученные на предыдущих занятиях.
Условные обозначения:
— жирный шрифт — названия функций и пунктов меню;
— КНОПКИ на экране компьютера;
— КЛАВИШИ на клавиатуре компьютера.
Описывается работа в англоязычной версии Microsoft Excel из пакета Office 365. В тексте приводятся названия пунктов меню и функций на английском и на русском языках. На рисунках используется английская версия пакета.
Введение
В данной лабораторной работе рассматривается корреляционная зависимость, или корреляция [1–4].
В работе для изучения взаимосвязи используются методы корреляционного и регрессионного анализа, а также сводка и группировка данных. Работа выполняется в пакете Excel [5].
Корреляционный анализ позволяет оценить степень тесноты связи. Регрессионный анализ используется для построения линейных и нелинейных моделей.
На первом этапе исходные данные получают путём имитационного моделирования. На втором этапе проводится анализ реальных данных.
Требования к оформлению отчёта приведены в описании предыдущей лабораторной работы [6].
Общие сведения
Корреляция — это связь между двумя случайными величинами, которые часто называют следующим образом:
X — факторный признак;
Y — результативный признак.
Изображение исходных данных называется диаграммой разброса. Каждая пара чисел X и Y изображается отдельной точкой. Точки между собой не соединяют.
Пример диаграммы разброса для корреляционной зависимости показан на рисунке ниже.
<figure class="Figure"><img src="https://ridero.ru/books/korrelyacionnyi_i_regressionnyi_analiz_v_excel/image/5d890704764bf01b987634ec"/><figcaption>Корреляция</figcaption></figure>
Варианты задания
Варианты заданий представлены в таблицах.
В каждом варианте рассматриваются два набора данных — примеры линейной и нелинейной регрессии. Анализ проводится для обоих случаев.
Факторный признак Х — случайная величина с равномерным распределением.
Случайная составляющая Е — случайная величина со стандартным нормальным распределением (нулевое среднее и единичная дисперсия).
Результативный признак Y вычисляется по формуле.
Объём выборки n = 200.
<figure class="Figure"><img src="https://ridero.ru/books/korrelyacionnyi_i_regressionnyi_analiz_v_excel/image/5d890749764bf01b9876353a"/></figure>
На новом листе опишите вариант задания.
<figure class="Figure"><img src="https://ridero.ru/books/korrelyacionnyi_i_regressionnyi_analiz_v_excel/image/5d890745764bf01b98763537"/></figure>
Зарисовки функций
Сделайте зарисовки общей формы зависимости и диаграммы разброса на бумаге. Вставьте зарисовку в отчёт.
Для построения линейной функции оцените значения на границах диапазона значений факторного признака. Оцените случайный разброс по правилу трёх сигм.
<figure class="Figure"><img src="https://ridero.ru/books/korrelyacionnyi_i_regressionnyi_analiz_v_excel/image/5d89073d764bf01b98763534"/><figcaption>Зарисовка линейной функции</figcaption></figure>
Для зарисовки графика параболы дополнительно определите координаты её вершины кривой и направление ветвей.
Выберите масштаб так, чтобы данные занимали всё поле графика. Метки на осях должны быть круглыми числами.
<figure class="Figure"><img src="https://ridero.ru/books/korrelyacionnyi_i_regressionnyi_analiz_v_excel/image/5d890739764bf01b98763531"/><figcaption>Зарисовка нелинейной функции</figcaption></figure>
Исходные данные
Сгенерируйте исходные данные в соответствии c вариантом задания.
Вызовите надстройку:
Data Analysis
Анализ данных.

Используйте функцию:
Random Number Generation
Генерация случайных чисел.

Округлите сгенерированные числа до целых.
Задавайте разные начальные состояния генератора для получения фактора Х и случайной составляющей Е.
Для округления используйте функцию
ROUND (number, num_digits)
ОКРУГЛ (число; число разрядов).

Для округления до целых укажите нулевое число разрядов после запятой.
В отчёте опишите параметры генератора случайных чисел.
В дальнейшей работе используйте округлённые значения Х и Y.
Диаграмма разброса
Постройте диаграмму разброса:
Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter
Вставка — Диаграммы — Вставить точечную (X, Y) или пузырьковую диаграмму — Точечная — Точечная.
Настройте оформление графика. Задайте масштаб и информативные заголовки. Пример оформления показан на рисунке.
<figure class="Figure"><img src="https://ridero.ru/books/korrelyacionnyi_i_regressionnyi_analiz_v_excel/image/5d890737764bf01b9876352e"/><figcaption>Оформление графика</figcaption></figure>
В отчёте опишите внешний вид графиков.
Корреляционный анализ
Корреляционный анализ — это изучение степени тесноты связи.
Коэффициент линейной корреляции — это показатель степени линейной связи и разброса точек вокруг прямой линии. Знак коэффициента говорит о направлении связи, а величина коэффициента — о степени тесноты линейной зависимости.
Для вычисления коэффициента корреляции используйте следующие способы:
— надстройка Анализ данных;
— готовая функция CORREL;
— формулы, вводимые вручную.
Надстройка
Вызовите надстройку Анализ данных и выберите модуль Корреляция.
Задайте диапазоны исходных данных и укажите, что они расположены по столбцам:
Grouped By — Columns
Группирование — по столбцам.

С помощью надстройки получите таблицу коэффициентов линейной корреляции. Настройте ширину столбца, чтобы увеличить точность представления результата.
Опишите в отчёте полученные результаты.
Функция CORREL
Вычислите коэффициент корреляции с помощью следующей функции:
CORREL (array1, array2)
КОРРЕЛ (диапазон_x; диапазон_y).

В качестве аргументов функции укажите диапазоны ячеек факторного и результативного признаков.
Изучите внешний вид диаграмм разброса в зависимости от коэффициента корреляции. Для этого на новом листе сгенерируйте наборы данных и подберите значения множителя S в уравнении, чтобы получить следующие значения коэффициента корреляции:
0,3
0,5
0,7
1,0.
Постройте диаграммы разброса. Укажите на них значения коэффициента корреляции и степень тесноты связи.
Формулы</div>

Корреляционный анализ

Сравнение результатов

Регрессия на диаграмме

Надстройка — линейная регрессия

Система нормальных уравнений

Анализ данных интернет-магазина

Анализ биржевых котировок

https://store.ridero.ru/text-utils/v1/free-text/slug/korrelyacionnyi_i_regressionnyi_analiz_v_excel?volumeLimit=0.25&template=domFragment

Авторская орфография и пунктуация сохранены. Корректорская правка не проводилась.

Книга публикуется в авторской орфографии и пунктуации

Помощник в магазине Rideró

У этой книги нет оглавления

Купите книгу, чтобы продолжить чтение.

Бесплатный фрагмент закончился.

У этой книги нет бесплатного отрывка

Бесплатный фрагмент

Издай свою книгу

У этой книги ещё нет описания

О книге

книги автора

книги авторов

книги импринта

Аудиокнига

Электронная книга

Печатная книга

Автор

Авторы

Мы дарим вам эту книгу бесплатно, читайте с удовольствием, а Rideró начислит автору роялти.

Для этого подтвердите адрес электронной почты.

Получите книгу бесплатно

Начните обсуждать бесплатный фрагмент прямо сейчас и получите электронную версию бесплатно, оставив отзыв длиной не менее 200 символов. 
 Автор при этом получит роялти, как при обычной продаже.

{countAuthors, plural, =0 {} one {Автор {authorName}, } other {{authorName} {isOther, select, true {и другие авторы} other {}} - }}книга {title}{subtitle} {countFormats, plural, =0 { } other {в }}{isPaper, select, true {печатном} other {}}{countFormats, plural, =0 {} one {} other {, }}{isDigital, select, true {электронном} other {}}{countFormats, plural, =3 {, } other {}}{isAudio, select, true {аудио} other {}} {countFormats, plural, =0 {} other {формате }}в интернет магазине Ridero

Читайте онлайн оглавление книги {title}{subtitle}{countAuthors, plural, =0 {} one {, автор {authorName}} other {, авторы: {authorName} {isOther, select, true {и другие} other {}}}}

Читайте бесплатно книгу {title}{subtitle}{countAuthors, plural, =0 {} one {, автор {authorName}} other {, авторы: {authorName} {isOther, select, true {и другие} other {}}}}

{title} - купить книгу в интернет магазине{countAuthors, select, 1 {, автор {authorName}} other {}} Ridero

, автор {authorName}

*Facebook признан экстремистской организацией и запрещен в РФ

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Мы проверили вашу книгу и готовы отправить ее в магазины. Проверьте, пожалуйста, почту {userEmail}, чтобы заключить договор.

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Ой! Кажется, ваша книга нарушает закон РФ, и мы не можем ее опубликовать. Проверьте почту {userEmail}, чтобы узнать подробности.

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Ваша книга поступила в отдел публикации. Проверка книги может занять от 1 до 3 рабочих дней

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} Ваша книга добавлена в акцию «{discountDescription}», которая продлится с {discountStart} до {discountEnd}. Расскажите об акции своим друзьям и читателям, чтобы они ее не пропустили.

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}/#freebiePrint"'>'{objectName}'<'/a'>'{period} {formattedDate} Ура, товарищ! Мы тебе почти за свой счёт готовы напечатать 1 экземпляр новой книги!

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} {formattedDate} вашу книгу скачали {cnt} раз. Хотите больше? Узнайте, как привести больше '<'a target=_blank href="https://beta.ridero.ru/book/share.html#!/{objectId}/"'>'новых читателей'<'/a'>'.

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Аннотация для книги готова

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Аудиокнига готова

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Буктрейлер для книги готов

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Обложка книги готова

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Редактор завершил работу над книгой

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Иллюстрации для книги готовы

Услуга "консультация юриста" оказана

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Помощник завершил работу над книгой

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Отрисовка схем и элементов для книги завершена

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Завершёна услуга "Ridero POD"

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Рекламная кампания книги закончена

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Последняя версия книги опубликована

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Работы по заказу №{number} завершены

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Корректура книги готова

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Верстка книги готова

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Завершена услуга "набор текста" для книги

Мы получили от вас оплату по счету №{number}'<'/i'>' и скоро напишем в почту {userEmail}, чтобы обсудить детали

Мы получили от вас оплату по счету №'<'i'>'{number}'<'/i'>' и скоро напишем в почту {userEmail}, чтобы обсудить детали

'<'a class="ga_feed_anchor" target="_blank" href="{bookLink}"'>'{objectName}'<'/a'>'{period} Мы проверили вашу книгу и опубликовали ее в магазине Rideró. Скоро она будет опубликована и в других магазинах, которые вы выбрали. Проверьте, пожалуйста, почту {userEmail}, чтобы узнать подробности

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} {formattedDate} Поздравляем! Вашу книгу кто-то только что добавил в избранное! Узнайте, как еще больше '<'a target=_blank href="https://beta.ridero.ru/book/share.html#!/{objectId}/"'>'привлечь внимание читателей'<'/a'>'.

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} Нам понравилась ваша книга, и мы добавили ее в тематическую подборку '<'a target="_blank" href="https://ridero.ru/listing/{annotation_id}"'>'{annotation_displayname}'<'/a'>' в магазине Rideró.

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} {formattedDate} для старта продаж мы включили книгу “{objectName}” в специальную подборку. Ваши первые читатели получат электронную версию совершенно бесплатно, а Rideró начислит вам роялти. '<'br/'>' Не забудьте отправить ссылку на книгу друзьям или поделиться ей в соцсетях

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Тираж вашей книги по заказу '<'i'>'{number}'<'/i'>' готов и передан в службу доставки

Мы получили от вас оплату по счету №'<'i'>'{number}'<'/i'>' и скоро напишем в почту {userEmail}, чтобы обсудить детали

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Читатель оформляет покупку вашей книги в электронном формате

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Читатель оформляет покупку вашей книги в бумажном виде

'<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Только что кто-то купил вашу аудиокнигу. '<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/stat"'>'Проверьте статистику!'<'/a'>'

'<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Поздравляем, ваша аудиокнига куплена по акции «{discountDescription}». '<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/stat"'>'Проверьте статистику!'<'/a'>'

'<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Поздравляем! Только что куплена электронная копия вашей книги. '<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/stat"'>'Проверьте статистику!'<'/a'>'

'<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Поздравляем, ваша книга куплена по акции «{discountDescription}». '<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/stat"'>'Проверьте статистику!'<'/a'>'

'<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/view"'>'{objectName}'<'/a'>'{period} Поздравляем! Только что кто-то купил вашу книгу в бумажном виде. '<'a target="_blank" href="https://beta.ridero.ru/#!/book/{objectId}/stat"'>'Проверьте статистику!'<'/a'>'

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} {formattedDate} бесплатный фрагмент вашей книги {cnt, plural, one {прочитал} other {прочитали}} {cnt} {cnt, plural, one {человек} two {человека} few {человека} many {человек} other {человек}}. '<'a target=_blank href="https://beta.ridero.ru/book/share.html#!/{objectId}/"'>'Привлекайте новых читателей'<'/a'>'

Вы дочитываете книгу '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period}. Надеемся, она вам понравилась. Пожалуйста, поделитесь впечатлениями на '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'странице книги'<'/a'>'. Это очень важно для автора.

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} {formattedDate} {cnt} {cnt, plural, one {человек} two {человека} few {человека} many {человек} other {человек}} {cnt, plural, one {начал} other {начали}} читать вашу книгу, купленную в приложении Ridero Store чуть раньше

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} У вашей книги появился новый отзыв на Ридеро! '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'Перейти на сайт книги'<'/a'>'.

Спасибо за отзыв о книге '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period}! Поделитесь им в соц. сетях, чтобы рассказать о своих о впечатлениях друзьям и знакомым!

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} Кто-то оставил отзыв о вашей книге. Интересно, что там написано? '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'Перейти на сайт книги'<'/a'>'

Кто-то ответил на ваш отзыв к книге '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'. Примите участие в обсуждении.

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} Нам понравилась ваша книга, и мы добавили ее в подборку «{annotation_displayname}» на '<'a target="_blank" href="https://ridero.ru/books"'>'главной странице магазина Rideró'<'/a'>'

При загрузке книги '<'b'>'«{bookTitle}»'<'/b'>' произошла ошибка. Свяжитесь с техподдержкой, и мы поможем вам загрузить книгу в систему.

'<'a class="ga_feed_anchor" target="_blank" href="https://beta.ridero.ru/#!/book/{bookId}/view"'>'{bookTitle}'<'/a'>'{period} Поздравляем! Мы распознали вашу рукопись и сделали из нее книгу. Отправьте ее в магазины или закажите тираж.

'<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>'{period} {formattedDate} страницу вашей книги {cnt, plural, one { посмотрел} other { посмотрели}} {cnt} {cnt, plural, one {человек} two {человека} few {человека} many {человек} other {человек}}. '<'a target=_blank href="https://beta.ridero.ru/book/share.html#!/{objectId}/"'>'Продвигайте книгу'<'/a'>' с нами

Заявка '<'b'>'{number}'<'/b'>' на вывод средств принята. В ближайшее время с вами свяжется наш менеджер

Заявка '<'b'>'{number}'<'/b'>' на вывод средств отклонена. Причина: '<'i'>'{rejectionReason}'<'/i'>'

На книгу '<'a class="ga_feed_anchor" target="_blank" href="https://ridero.ru/books/{engTitle}"'>'{objectName}'<'/a'>' из вашего «Избранного» действует скидка {discountAmt}% до {discountEnd}. Самое время ее купить!

Плюсы Rideró, ISBN в подарок, буктрейлеры, вебинары и многое другое

Отзывы

Нельзя публиковать ссылки в отзывах

Только зарегистрированные пользователи могут оставлять отзывы. <button>Зарегистрироваться</button>

Если вы хотите поставить книге оценку 1 или 2, пожалуйста, расскажите, что конкретно вам не понравилось в книге, напишите текстовый отзыв из 200 знаков

Оглавление - Корреляционный и регрессионный анализ в электронных таблицах

Лабораторный практикум