Возможности использования графических опций программы MS EXCEL для решения задач регрессионного анализа

Линии тренда, аппроксимирующие экспериментальные данные, представляют собой графические представления регрессионных моделей. Построение линии тренда инициируется нажатием правой кнопки мыши на одну из точек ряда данных предварительно построенного графика    (рис. 5.1).

 

 

Рис. 5.1. Опции, открывающиеся при нажатии правой кнопки мыши на ряд данных

 

После нажатия «Добавить линию тренда» открываются большие возможности выбора типа регрессионной зависимости: линейного, логарифмического, экспоненциального, степенного или полиномиального до    шестой степени включительно (рис. 5.2).

Кроме того, появляется возможность использовать опцию «Линейная фильтрация», которая при правильном использовании должна сглаживать отклонения в данных и более чётко показывать форму линии тренда. Число точек, образующих линию тренда с линейной фильтрацией (Скользящее среднее. Последовательность средних значений, вычисленных по частям рядов данных. На диаграмме линия, построенная по точкам скользящего среднего, позволяет построить сглаженную кривую, более ясно показывающую закономерность в развитии данных.), равно общему числу точек ряда за вычетом числа, указанного для параметра «Точки». Если линейная фильтрация добавляется к точечной диаграмме, она базируется на порядке расположения значений X в диаграмме. Поэтому для получения нужного результата перед добавлением линейной фильтрации, возможно, потребуется отсортировать значения X.

 

 

 

Рис. 5.2. Окно, открывающеся при выборе опции «Добавить линию тренда»

 

Для более развёрнутой характеристики выбранной модели и построенной линии тренда служит опция «Параметры». Она позволяет «заказать» для выбранной линии тренда представление на диаграмме уравнения регрессии и коэффициента детерминации R². Опция «Параметры» открывается на второй вкладке окна «Линия тренда» (рис. 5.2) или вкладке окна «Формат линии тренда» (рис. 5.3), которое открывается нажатием правой кнопки мыши на уже построенную линию тренда.

В данном случае за отсутствием суммы квадратов остатков R² - единственная, но действенная характеристика точности аппроксимации. Перебором различных линий тренда с определением их коэффициентов детерминации можно быстро выбрать оптимальную модель.

Уравнение линии тренда округляется, что делает его удобочитаемым, но число десятичных знаков в подписи выбранной линии тренда (и R²) можно изменить в поле «Число десятичных знаков» на вкладке «Число» диалогового окна «Формат подписи данных». (При этом должен быть выбран числовой формат «Числовой».) Окно открывается нажатием правой кнопки мыши на уравнение регрессии (или R²) или через меню «Формат» - «Выделенные подписи данных».

 

Рис. 5.3. Опция «Параметры» в окне «Формат линии тренда»

 

Как видно из рис. 5.3, дополнительная опция «Прогноз» позволяет осуществлять экстраполяцию линии тренда на заданное количество       «периодов» или «единиц» по оси абсцисс «вперёд» и «назад».

Таким образом, с использованием графических опций можно существенно расширить возможности программы MS EXCEL в области регрессионного анализа и применять нелинейную регрессию в её многообразных вариантах. Однако множественный регрессионный анализ в рамках программы MS EXCEL за исключением, разумеется, возможностей функций ЛГРФПРИБЛ и РОСТ (см. подраздел 4.4), может быть только линейным.

Литература: 

1. Козлов А.Ю., Шишов В.Ф. Пакет анализа MS Ехсеl в экономико-статистических расчётах. - М.: ЮНИТИ-ДАНА, 2003. - 139 с.

2. Орлов А.И. Прикладная статистика: учебник для вузов. - М.: Экзамен, 2004. - 656 с.

3. Клячкин В.Н. Статистические методы в управлении качеством: компьютерные технологии. - М.: Финансы и статистика, 2007. - 304 с.

4. Макарова Н.В., Трофимец В.Я. Статистика в Ехсеl. - М.: Финансы и статистика, 2002. - 368 с.

5. Кобзарь А.И. Прикладная математическая статистика. Для инженеров и научных работников. - М.: ФИЗМАТЛИТ, 2006. - 816 с.

6. Зарубин В.С. Математическое моделирование в технике: учебник для вузов / под ред. В.С. Зарубина, А.П. Крищенко. - М.: Изд-во МГТУ им. Н.Э. Баумана, 2001. - 496 с. (Сер. Математика в техническом университете. Вып. XXI, заключительный).

7. Тюрин Ю.Н., Макаров А.А. Анализ данных на компьютере: учеб. пособие. - М.: ИД "Форум", 2008. - 368 с.

8. Математическая статистика / В.Б. Горяинов, И.В. Павлов, Г.М. Цветкова и др.; под ред. В.С. Зарубина, А.П. Крищенко. - М.: Изд-во МГТУ им. Н.Э. Баумана, 2001. - 424 с. - (Сер. Математика в техническом университете. Вып. XVII).

9. Ниворожкина Л.И., Арженовский С.В. Многомерные статистические методы в экономике: учебник. - М.: Издательско-торговая корпорация «Дашков и К°»; Ростов н/Д: Наука-Спектр, 2008. - 224 с.

10. Козлов А.Ю., Мхитарян В.С., Шишов В.Ф. Статистические функции MS Excel в экономико-статистических расчетах / под ред. В.С. Мхитаряна. - М.: ЮНИТИ-ДАНА, 2003. - 231 с.

11. Теория статистики / под ред. Р.А. Шмойловой. - 2-е изд., доп. и перераб. - М.: Финансы и статистика, 2009. - 655 с.

12. Суслов, А.Г. Научные основы технологии машиностроения / А.Г. Суслов, А.М. Дальский. - М.: Машиностроение, 2002. - 684 с.

13. Гмурман В.Е. Теория вероятностей и математическая статистика. - М.: Высш. образование, 2008. - 479 с.

14. Вуколов Э.А. Основы статистического анализа. Практикум по статистическим методам и исследованию операций с использованием пакетов STATISTICA и EXCEL. - М.: ФОРУМ; ИНФРА-М, 2004. - 464 с. - (Профессиональное образование).

15. Дубров, А.М. Многомерные статистические методы: Для экономистов и менеджеров: учебник для экон. спец. вузов / А.М. Дубров, В.С. Мхитарян, Л.И. Трошин. - М.: Финансы и статистика, 2003. - 352 с.

16. Шведов, А.С. Теория вероятностей и математическая статистика [Текст]: учеб. пособие для вузов / А.С. Шведов; Гос. ун-т - Высшая школа экономики. - 2-е изд., перераб. и доп. — М: Изд. дом ГУ ВШЭ, 2005. - 254, [I, II] с. - (Учебники Высшей школы экономики). - Литерат.: с. 247-249.

17. Палий, И.А. Прикладная статистика: учеб. пособие для вузов / Ирина Абрамовна Палий. - М.: Дашков и К0: Наука Спектр, 2008. - 223 с.

18. Шелест, Вячеслав Дмитриевич Начала вычислительной математики: введение в численный эксперимент / Житомирский, Михаил Сергеевич; С.-Петербургский гос. политехнич. ун-т. - СПб.: Изд-во Политехнического ун-та, 2005. - 201 с.

19. Рогов, Владимир Александрович Методика и практика технических экспериментов: учеб. пособие для вузов по напр. подготовки бакалавров и магистров "Технология, оборудование и автоматизация машиностроительных производств" и по напр. подготовки дипломированных спец. "Конструкторско-технологическое обеспечение машиностроит. производств" / В.А. Рогов, Г.Г. Позняк. - М.: Академия, 2005. - 283 с.

20. Современный эксперимент: подготовка, проведение, анализ результатов / В.Г. Блохин, О.П. Глудкин, А.И. Гуров, М.А. Ханин; под ред. О.П. Глудкина. - М.: Радио и связь, 1997. - 232 с.

21. Боровиков, В.П. STATISTICA. Искусство анализа данных на компьютере: для профессионалов: 2-ое изд. / В.П. Боровиков. – СПб.: Питер, 2003. – 688 с.

22. Берк, К. Анализ данных с помощью Microsoft Excel / К. Берк, П. Кэйри. - М.: Издательский дом «Вильямс», 2005. - 560 с.

23. Саймон, Д. Анализ данных в Excel: наглядный курс создания отчетов, диаграмм и сводных таблиц: пер. с англ. / Д. Саймон. - М.: Издательский дом «Вильямс», 2004. - 528 с.

24. Натан, А.А. Математическая статистика / А.А. Натан, О.Г. Горбачев, С.А. Гуз. - М.: МЗ Пресс, 2005. - 160 с.

25. Вентцель, Е.С. Теория вероятностей и ее инженерные приложения: учеб. пособие для втузов / Е.С. Вентцель, Л.А. Овчаров. - 4-е изд., стер. - М.: Высш. шк., 2007. - 491 с.

26. Седельников А.И. Планирование эксперимента при исследовании процесса резания металлов [Электронный ресурс]. - 2010. - 41 с.

27. ОБЩАЯ ТЕОРИЯ СТАТИСТИКИ Учебник / Под редакцией доктора экономических наук, профессора, академика М.Г. Назарова. Москва, 2010 (Сер. "Высшее экономическое образование") Политех

28. Клячкин В.Н. Многомерный статистический контроль технологического процесса. - М.: Финансы и статистика, 2003. - 192 с.

29. Оптимизация работы объекта управления методами многофакторного эксперимента: Метод. указания по выполнению лабораторной работы: Дисц. "Идентификация и диагностика систем": Спец. 210100 "Управление и информатика в технических системах" / ВятГУ, ФАВТ, каф. автоматики и телемеханики. - Киров: ВятГУ, 2004. - 12 с.. - -, 22.

30. Александровская Л.Н. Современные методы обеспечения без­отказности сложных технических систем / Л.Н. Александровская, А.П. Афанасьев, А.А. Лисов. - М: Логос, 2001. - 208 с.

31. Ефимов, В.В. Статистические методы в управлении качеством продукции / В.В. Ефимов, Т.В. Барт. - М.: КНОРУС, 2012. - 240 с.

32. Управление качеством в машиностроении / А.Ф. Гумеров, А.Г. Схиртладзе, В.А. Гречишников [и др.]. - Старый Оскол: ТНТ, 2011. - 168 с.

33. Коротков, Э.М. Исследование систем управления / Э.М. Коротков. - М.: ДеКа, 2003. - 336 с.

34. Вентцель, Е.С. Исследование операций: задачи, принципы, методология: учебное пособие / Е.С. Вентцель. - 5-е изд., стер. - М.: КНОРУС, 2010. - 192 с.

35. Романов, В.П. Интеллектуальные информационные системы в экономике / В.П. Романов. - М.: Экзамен, 2003. - 496 с.

36. Кувалдин, Ю.И. Исследование связи случайных величин в металлообработке с использованием программы MS EXCEL / Ю.И. Кувалдин, М.З. Певзнер. - Киров: Изд-во ВятГУ, 2011. - 66 с.

37. Полный и дробный эксперимент первого порядка: Метод. указания по выполнению лаборат. работы: Дисц. "Идентификация и диагностика систем": Спец. 210100 "Управление и информатика в технических системах" / ВятГУ, ФАВТ, каф. автоматики и телемеханики; Сост.: А.Б. Кислицын, В.В. Куклин; Рец. Д.А. Страбыкин; Ред. Е.Г. Козвонина. - Киров: ВятГУ, 2004. - 15 с.. - -, 22.

38. Международный стандарт ISO 9001. Системы менеджмента качества. Требования. - Третье издание 2000-12-15. - ISO, 2000. - 41 с.

39. ГОСТ Р ИСО 11462-1-2007. Статистические методы. Руководство по внедрению статистического управления процессами. Часть 1. Элементы. - Дата введения - 2007-09-01. - М.: Стандартинформ, 2007. - 16 с.

40. ГОСТ Р 50779.0-95. Статистические методы. Основные положения. - Дата введения 1996-07-01. - М.: Госстандарт России, 1995. - 12 с.

41. ГОСТ Р 50779.10-2000 (ИСО 3534.1-93). Статистические методы. Вероятность и основы статистики. Термины и определения. - Введ. 2001-07-01. - М.: Госстандарт России, 2001. - 35 с.

42. ГОСТ Р 50779.11-2000 (ИСО 3534.2-93). Статистическое управление качеством. Термины и определения. - Дата введения 2001-07-01. - М.: Госстандарт России, 2001. - 26 с.

43. ГОСТ 24026-80. Исследовательские испытания. Планирование эксперимента. Термины и определения. - Введ. 01.01.81. - М.: Изд-во стандартов, 1980. - 17 с.

44. Рекомендации по стандартизации Р 50.1.040 - 2002. Статистические методы. Планирование экспериментов. Термины и определения. - Дата введения 2003-07-01. - М.: Госстандарт России, 2002. - 36 с.