Корреляционный анализ позволяет дать оценку силы взаимосвязи между двумяпризнаками. С помощью регрессионного анализа происходит изучение поведенияодного из двух факторов, управление однимпризнакомс помощью другого. На практикебольшуюроль играетлинейная зависимость между факторами. Степеньтакой зависимостифакторов называется коэффициентом корреляции. Если коэффициент корреляции близок к 1, тосвязь между данными признакамисчитается линейной. Если коэффициент корреляции близокк нулю, то такая связь между величинами считается слабой или нелинейной[13]. Чтобы вывести результаты в виде таблицы используют функцию «Корреляция"из пакета Анализ данных.
В результате откроется соответствующее диалоговое окно (рис. 23).Рис. 23. Корреляция В диалоговом окне (рис.
23) можно задать такие параметры: 1. Входной интервал — вводится диапазон ячеек, которые содержат данные для анализа. 2. Группирование — переключатель можно установить в необходимое положение по расположению исходных данных. 3. Метки в первой строке — флажок устанавливается, если в первой строке содержится заголовок, иначе стандартные заголовки создаются автоматически. 4. Параметры вывода — можно указать место, куда выводится таблица результатов анализа.
Линейная регрессия Формойвзаимосвязи результативного признака Y с факторами X1, X2,…Xm называют уравнение регрессии. По типу выбранного уравнения выделяют линейную и нелинейную регрессию, по количеству факторов — парную (простую, m = 1) и множественную (многофакторную, m > 1). Задачи регрессионного анализа: 1. Выбрать общий вид уравнения регрессии и определить параметры регрессии. 2. Определить степень взаимосвязи результативного признака и факторов, проверить общее качество уравнения регрессии.
3. Проверить статистическую значимостьвсех коэффициентов уравнения регрессии и определить их доверительные интервалы. 16]Уравнение простой линейной регрессии имеет вид: множественную линейную регрессию описывают следующим уравнением:
Параметры уравнений парной и множественной регрессий определяют методом наименьших квадратов, реализованным в Excel, используя функцию.
Регрессия. Для вызова этой функции выбираютнужное имя в окне диалога Анализ данных. В итоге откроется диалоговое окно (рис. 24). Рис.
24. Регрессия В диалоговом окне (рис. 23) можно задать параметры: 1. Входной интервал Y — для ввода диапазона ячеек, содержащего исходные данные признака Y. 2. Входной интервал X — для ввода диапазона ячеек, содержащего исходные данные факторного признака.
3. Метки — если в первой строке содержатся заголовки столбцов, то необходимо поставить галочку. 4. Уровень надежности-задают в случае, если значение уровня не равно 95%. Привыключенном флажке уровень надежности принимаетсяравным95%. 5. Константа-ноль -отмечаетсяв случае, когда линия регрессии должна проходитьчерез начало координат, то есть в уравнении регрессии коэффициент6. Параметры вывода — позволяют указать, куда выводятся таблицы с результатами анализа. 7. Остатки — при необходимости выводастолбцов остатков и графиков остатков. 8.
Нормальная вероятность — флажок устанавливают при выведении графика зависимости исследуемых значений от автоматически формируемых интервалов персентилей. 5]2.2 Построение регрессионной линейной однофакторной модели.
Для исходных данных (табл. 1) построить регрессионную линейную однофакторную модель зависимости затрат на техническое обслуживание от срока службы с помощью функции Регрессия. Таблица 1X срок службы 2 5 9 3 8 Y затраты на ТО 6 13 23 5 22 Результаты решения с помощью функции Регрессия отображаются спомощью нескольких отдельных таблиц. Результаты вычислений регрессионной статистики выводятся в таблице «Регрессионная статистика» (табл. 2):Таблица 2Регрессионная статистика Множественный R 0,98 453 5285R-квадрат 0,969 309 728.
Нормированный R-квадрат 0,959 079 637.
Стандартная ошибка 1,724 792 855.
Наблюдения 5 В таблице 2 отображены элементы регрессионной статистики: Множественный R — коэффициент корреляции. R-квадрат — коэффициент детерминации. Нормированный R-квадрат — нормированное значение коэффициента корреляции. Стандартная ошибка — стандартное отклонение для остатков. Наблюдения — количество исходных наблюдений. В таблице 3указаны результаты дисперсионного анализа, использующиеся для проверки значимости коэффициента детерминации. Таблица 3Дисперсионный анализdfSSMicrosoftFЗначимость FРегрессия1281,8 752 688 281,875268894,750 843 370,002303227.
Остаток38,9 247 311 832,974910394.
Итого4290,8 В таблице 3 представлены элементы дисперсионного анализа: df- число степеней свободы. Для строки Регрессия df — это значение, с помощью которого определяетсячисло факторных признаков, для строки Остаток — количество наблюдений минус число переменных в уравнении регрессии, для строки.
Итого — сумма степеней свободы для строк Регрессия и Остаток. SS — сумма квадратов отклонений. Для строки Регрессия-это значение является суммой квадратов отклонений теоретических данных от среднего, для строки Остатокэто сумма квадратов отклонений эмпирических данных от теоретических, для строки.
Итогоэто сумма квадратов отклонений эмпирических данных от среднего. Microsoft- дисперсии. Для строки Регрессия это значение определяется как факторная дисперсия, для строки Остаток — это остаточная дисперсия. F — расчетное значение F-критерия Фишера, определяется как отношение факторной дисперсии к остаточной. Значимость F — значение уровня значимости, которое соответствует вычисленному значению F. Полученные значения коэффициентов регрессии и их статистические оценки выводятся в следующую таблицу (табл. 4): Таблица 4Коэффициенты.
Стандартная ошибка t-статистика P-Значение Y-пересечение -1,64 516 129 1,710 826 692 -0,622 223 241 0,577 881 718 срок службы 2,752 688 172 0,282 790 929 9,734 004 488 0,2 303 227 В таблице 4 отражены следующие элементы: Коэффициенты — значения коэффициентов модели. Стандартная ошибка — стандартные ошибки коэффициентов. t-статистика — расчетные значения t-критерия, которыйвычисляется как отношение значений коэффициентов к соответствующим стандартным ошибкам. PЗначение — значения уровней значимости, которое соответствует вычисленным значениям tp. На основе полученных данных из таблиц можно подвестиитоги: Уравнение регрессии имеет вид: Высокое значение коэффициента детерминации, равного 0,97 объясняет существенное влияниесрока службы на затраты на техническое обслуживание, и подтверждает правильность включения его в построенную модель. Рассчитанный уровень значимости Значимость F = 0,002 меньший 0,05 и подтверждает значимость коэффициента детерминации.
PЗначение для срока службы, равного 0,002 и меньшего 0,05,доказывает значимость коэффициента P-Значение для коэффициента превышающего 0,05указывает на незначимость данного коэффициента для модели, график которой будет проходить через точку начала координат.
ЗАКЛЮЧЕНИЕ
В современном мире практически любаяпроблема: политическая, финансовая, техническая, научно-исследовательская и даже бытовыепроблемы решаются только глубокого анализа данных. Поэтому хорошее знание статистических методов анализа данных играет важную рольв образовании, а специализированные компьютерные программы, выполняющие аналитические исследования и прогнозирование являются рабочим инструментом любого специалиста в информационной сфере. При глубоком анализе статистических данных предполагаетсяприменение различных методов и инструментов, одним из которых является табличный процессор MicrosoftExcel. Обработка статистических данных, представленных в виде электронных таблиц, в наши дни стала простой и естественной, что обусловлено тем, что сложные научные методы статистического анализаблагодаря автоматизированным возможностям специальных компьютерных программ превратились для специалиста в эффективный аналитический инструмент. Применяя разнообразные инструменты процессора MicrosoftExcel, возможнаэффективная аналитическая поддержка принятия решений и повышение их обоснованности. В данной работе были показаны широкие возможности табличного процессораMicrosoftExcel при решениистатистических задач, облегчающие работу специалистов в области обработки и анализа информации. СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫАйзек, М. П. Вычисления, графики и анализ данных в Excel 2010: Самоучитель / М. П. Айзек, В. В. Серогодский, М. В. Финков. — СПб.: НиТ, 2013.
— 352 c. Айзек, М. П. Вычисления, графики и анализ данных в Excel 2013.
Самоучитель / М. П. Айзек. — СПб.: Наука и техника, 2015. — 416 c. Глушаков, С.В. Microsoft Excel 2007.
Краткий курс / С. В. Глушаков, А. С. Сурядный. — М.: АСТ, АСТ Москва, Харвест, 2014. — 352 c. Горяинова, Е. Р. Прикладные методы анализа статистических данных: Учебное пособие / Е. Р. Горяинова, А. Р. Панков, Е. Н. Платонов. — М.: ИД ГУ ВШЭ, 2012. —.
310 c. Дайитбегов, Д. М. Компьютерные технологии анализа данных в эконометрике: Монография / Д. М. Дайитбегов. — М.: Вузовский учебник, НИЦ ИНФРА-М, 2013. — 587 c. Кацко, И. А. Практикум по анализу данных на компьютере / И. А. Кацко, Н. Б. Паклин. — М.: Колос.
С, 2009. — 278 c. Кашаев, Сергей Офисные решения с использованием Microsoft Excel 2007 и VBA / Сергей Кашаев. ;
М.: «Издательство «Питер», 2016. 352 c. Козлов, А. Ю. Статистический анализ данных в Microsoft Excel: Учебное пособие / А. Ю. Козлов, В. С. Мхитарян, В. Ф. Шишов. — М.: ИНФРА-М, 2013. — 320 c. Кравченко, Л. В.
Практикум по Microsoft Office 2007 (Word, Excel, Access), Photoshop / Л. В. Кравченко. — М.: Форум, Инфра-М, 2017. — 168 c. Крянев, А. В. Метрический анализ и обработка данных / А. В. Крянев, Г. В. Лукин, Д. К. Удумян.
— М.: Физматлит, 2012. — 308 c. Кулаичев, А. П. Методы и средства комплексного анализа данных: Учебное пособие / А. П. Кулаичев.
— М.: Форум, НИЦ ИНФРА-М, 2013. — 512 c. Курбатова, Екатерина Анатольевна Microsoft Office Excel 2007.
Самоучитель / Курбатова Екатерина Анатольевна. — Москва: Наука, 2014. — 454 c. Миркин, Б.Г.
Введение
в анализ данных.
учебник и практикум / Б. Г. Миркин. — Люберцы: Юрайт, 2016.
— 174 c. Орлов, А.И. Организационно-экономическое моделирование. В 3-х т. Т.
3. Статистические методы анализа данных: Учебник / А. И. Орлов. — М.: МГТУ им.
Баумана, 2012. — 623 c. Тюрин, Ю. Н. Анализ данных на компьютере: Учебное пособие / Ю. Н. Тюрин, А. А. Макаров; Науч. ред. В. Э. Фигурнов.
— М.: ИД ФОРУМ, 2013. — 368 c. Чашкин, Ю. Р. Математическая статистика. Анализ и обработка данных: Учебное пособие / Ю. Р. Чашкин; Под ред. С. Н. Смоленский. — Рн/Д: Феникс, 2010.
— 236 c.
