Министерство образования и науки ДНР

ГОУ ВПО «Донецкая академия управления и государственной службы при Главе ДНР»

Кафедра информационных технологий

Индивидуальная работа №1

по теме: «Анализ данных в MS EXCEL»

Вариант № 12

Выполнил: студент группы МП-20

Федько А.Г.

Проверил: к.э.н., доцент Стешенко И.В.

Донецк, 2021 год

ОГЛАВЛЕНИЕ

1. Описательная статистика 4

­2. Оценка взаимосвязи 5

2.1. Корреляция 5

2.2. Регрессия 5

2.3. Множественная линейная регрессия 7

3. График зависимости 9

4. Предсказ 10

5. Тенденция 11

Задание

1. Описательная статистика

Рисунок 1. Выполнение описательной статистики

Рисунок 2. Описательная статистика

Выполнена описательная статистика совокупности данных стоимости помещения.

(Рисунок 1,2). При помощи Сервис – Анализ данных – Описательная статистика.

­2. Оценка взаимосвязи

Рисунок 3. Корреляция

2.1. Корреляция

На рисунке 3 выполнена оценка взаимосвязи и взаимное влияние стоимости от общей площади. Стоимость помещения к общей площади, равна 0,1 что находится близко к нулю, это значит, что зависимость между данными величинами слабая, либо не носит линейного характера.

Рисунок 4. Корреляция с учётом срока эксплуатации

- коэффициент корреляции общей площади равен 1;

- срок эксплуатации сильно связан с Общей площадью помещения коэффициент корреляции равен 0,75;

- стоимость помещения слабо связана с общей площадью помещения и равняется 0,12

- стоимость помещения слабо связана с сроком эксплуатации (коэффициент корреляции 0,35);

2.2. Регрессия

Рисунок 5. Линейная регрессия

Стоимость помещения = 82,525х + Общая площадь помещения

у = 82,525х + 2363,326

Для анализа адекватности полученного уравнения линейной регрессии в MS Excel используют параметры (см. рис. 5): коэффициент множественной корреляции (множественный R); коэффициент детерминации (R-квадрат); критерий Фишера (F-статистика); критерий Стьюдента (t-статистика).

Коэффициент множественной корреляции R позволяет оценить тесноту вероятностной связи между зависимой и независимой переменными. Высокое значение этой величины свидетельствует о сильной связи между переменными (при равенстве R=1 – связь функциональная). Таким образом, величина R=0,119 говорит о слабой степени связи переменных общая площадь помещения и стоимость помещения. Однако, характер этой связи пока неясен.

Величина квадрата коэффициента множественной корреляции или коэффициент детерминации R2(RI) показывает долю общего разброса (относительно выборочного среднего зависимой переменной), которая объясняется построенной регрессией. Иными словами, данная величина показывает, разброс какой части полученных экспериментальных данных (зависимая переменная) соответствует полученному уравнению линейной регрессии. В задаче эта величина составляет 14,1%, что говорит о том, что имеющиеся статистические данные с низкой степенью точности описаны полученным уравнением регрессии.

F-статистика (критерий Фишера) используется для оценки значимости полученной линейной зависимости, то есть, он подтверждает или опровергает гипотезу о существовании линейной зависимости. Если полученное значение F-критерия выше критического Fкр, то гипотеза о незначимости линейной зависимости отвергается. Величина Fкр должна быть получена из специальных таблиц в соответствии с числом степеней свободы df. Однако в окне рис. 5 приведено значение величины p – уровня значимости, показывающего вероятность непринятия верной гипотезы. Иными словами, если выдвинута гипотеза о незначимости линейной зависимости, то если уровень значимости p стремится к единице, то между переменными (зависимой и независимой) не существует линейной зависимости (гипотеза принимается), и, наоборот, если p стремится к нулю, то гипотеза отвергается (между переменными существует линейная зависимость). Для задачи F=0,101; df=1,7; p=2,70886, то есть вероятность отвергнуть верную гипотезу о незначимости коэффициента при независимой переменной составляет 2,7%.

Значение t-статистики (критерий Стьюдента) используется для оценки значимостей коэффициента при неизвестной и свободного члена полученной линейной зависимости. Если полученное значение t-критерия выше критического tкр, то гипотеза о незначимости свободного члена линейной зависимости отвергается. Аналогично как уровню значимости F-критерия, для t-критерия также приведено значение величины p – уровня значимости, показывающего вероятность непринятия верной гипотезы. В задаче 1 для свободного члена t=19,07490; p=2,7, то есть вероятность отвергнуть верную гипотезу о незначимости свободного члена находится близко к нулю. Для коэффициента при неизвестной t=0,31732; p=0,76025, то есть вероятность отвергнуть верную гипотезу о незначимости коэффициента при неизвестной составляет 0,8%.

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

2.3. Множественная линейная регрессия

Рисунок 6. Множественная регрессия

По этим результатам может быть построено следующее уравнение регрессии:

CP = -1080,377х1 + 5918,008х2 + 2825669,988

или

y = -1080,377 x1 + 5918,008 x2 + 2825669,988

Коэффициент множественной корреляции R =0,519 говорит о средней степени связи переменных СРОКА ЭКСПЛУАТАЦИИ ПОМЕЩЕНИЯ, лет и СТОИМОСТИ ПОМЕЩЕНИЯ. Однако, характер этой связи пока неясен.

Коэффициент детерминации R2(RI) 26,9%, что говорит о том, что имеющиеся статистические данные с низкой степенью точности могут быть описаны полученным уравнением регрессии.

F-статистика. Для задачи F=1,108; df=2,6; p=0,258, вероятность отвергнуть верную гипотезу о незначимости коэффициента при независимой переменной составляет 0,25%.

t-статистика t=1,249; p=0,258, то есть вероятность отвергнуть верную гипотезу о незначимости свободного члена практически равна нулю. Для коэффициента при неизвестной t=-1,080; p=0,321, то есть вероятность отвергнуть верную гипотезу о незначимости коэффициента при неизвестной составляет 0,32%. Для коэффициента при неизвестной t=-1,449; p=0,197, то есть вероятность отвергнуть верную гипотезу о незначимости коэффициента при неизвестной составляет 0,19%.

3. График зависимости

Рисунок 7. Линейная линия тренда

На рисунке 7 изображена линейная линия тренда

Рисунок 8. Полиоминальная линия тренда 4-й степени

На рисунке 8 изображена полиоминальная линия тренда четвёртой степени. Эта линия тренда немногим лучше описывает процесс, чем линейная так как R² больше приближен к единице по сравнению с линейной. А если быть точнее, то R² =0,3.

Рисунок 9. Прогнозирующая линия

На рисунке 9 изображена линия тренда с прогнозом для 2517, 2518 и 2540 кв.м общей площади помещения. Из рисунка видно, что прогноз, который строится по предшествующим данным при увеличении общей площади помещения, показывает стабильное повышение стоимости помещении.

4. Предсказ

Рисунок 10. Предсказ

На рисунке 10 сделан ПРЕДСКАЗ() для прогнозирования стоимости общей площади помещения 2517 кв.м.

5. Тенденция

Рисунок 11. Тенденция

Используя массив и формулу ТЕНДЕНЦИЯ() (рис. 11) была спрогнозирована стоимость общей площади помещения 2517 кв.м, 2518 кв.м и 2540 кв.м с учетом срока эксплуатации помещения.