В отчёте опишите параметры генератора случайных чисел.
В дальнейшей работе используйте округлённые значения Х и Y.
Диаграмма разброса
Постройте диаграмму разброса:
Insert — Charts — Insert Scatter (X, Y) or Bubble Chart — Scatter — Scatter
Вставка — Диаграммы — Вставить точечную (X, Y) или пузырьковую диаграмму — Точечная — Точечная.
Настройте оформление графика. Задайте масштаб и информативные заголовки. Пример оформления показан на рисунке.
Оформление графика
В отчёте опишите внешний вид графиков.
Корреляционный анализ
Корреляционный анализ — это изучение степени тесноты связи.
Коэффициент линейной корреляции — это показатель степени линейной связи и разброса точек вокруг прямой линии. Знак коэффициента говорит о направлении связи, а величина коэффициента — о степени тесноты линейной зависимости.
Для вычисления коэффициента корреляции используйте следующие способы:
— надстройка Анализ данных;
— готовая функция CORREL;
— формулы, вводимые вручную.
Надстройка
Вызовите надстройку Анализ данных и выберите модуль Корреляция.
Задайте диапазоны исходных данных и укажите, что они расположены по столбцам:
Grouped By — Columns
Группирование — по столбцам.
С помощью надстройки получите таблицу коэффициентов линейной корреляции. Настройте ширину столбца, чтобы увеличить точность представления результата.
Опишите в отчёте полученные результаты.
Функция CORREL
Вычислите коэффициент корреляции с помощью следующей функции:
CORREL (array1, array2)
КОРРЕЛ (диапазон_x; диапазон_y).
В качестве аргументов функции укажите диапазоны ячеек факторного и результативного признаков.
Изучите внешний вид диаграмм разброса в зависимости от коэффициента корреляции. Для этого на новом листе сгенерируйте наборы данных и подберите значения множителя S в уравнении, чтобы получить следующие значения коэффициента корреляции:
0,3
0,5
0,7
1,0.
Постройте диаграммы разброса. Укажите на них значения коэффициента корреляции и степень тесноты связи.
Формулы
Вычислите значение коэффициенты линейной корреляции с помощью формул. Используйте соотношение, приведённое ниже.
Коэффициент корреляции
Постройте вспомогательную таблицу и определите суммы, необходимые для расчётов. Для нахождения сумм используйте функцию экспресс-анализа.
Для вычисления коэффициента корреляции используйте функцию извлечения корня:
SQRT (number)
КОРЕНЬ (число).
Сравнение результатов
Скопируйте на новый лист значения коэффициента линейной корреляции, полученные разными способами.
Сделайте выводы о степени тесноты связи и качестве расчётов.
Регрессионный анализ
Регрессионный анализ сводится к построению линии, которая проходит в среднем по исходным точкам, а также к нахождению уравнения этой линии.
Уравнение линейной регрессии приводится ниже.
Линейная регрессия
Регрессионный анализ в Excel проводится следующими способами:
— встроенный элемент диаграммы;
— модуль Регрессия надстройки Анализ данных;
— готовая функция LINEST;
— решение системы нормальных уравнений с помощью формул.
Регрессия на диаграмме
Вызовите встроенную функцию графика, включив Линию тренда как элемент диаграммы разброса, как показано на рисунке:
Trendline — Linear
Линия тренда — Линейный.
Линия тренда
Включите вывод уравнения регрессии на поле графика:
Design — Add Chart Element — Trendline — More Trendline options — Format Trendline — Trendline options — Display Equation on chart