Данная работа является продолжением предыдущей работы, но анализ будет проведен с использованием методов автоматизации вычислений.
Цель работы: овладеть практическими навыками построения линий тренда при аппроксимации табличных данных и выполнения по ним прогноза электротехнических показателей.
Задачи: научиться обрабатывать данные в приложении Excel.
Теоретическое введение
Обработка результатов наблюдений, представленных в виде таблиц данных, является важной частью любого анализа. Проводите ли Вы лабораторные исследования или изучаете результаты испытаний, у Вас всегда присутствует номер опыта и количественная величина того, что вы измеряете, будь то число особей в популяции или количество проданных компьютеров. XY-точечная диаграмма наиболее подходящее средство для обработки такого рода результатов исследований.
XY-точечная диаграмма и линия тренда. При обработке результатов измерений достаточно часто возникает вопрос о тенденции их развития или изменения. В среде Excel этот вопрос решается при помощи тренда. Линии тренда графически иллюстрируют тенденцию изменения рядов данных. Они обычно используются в тех случаях, когда нужно построить диаграммы для задач прогнозирования и экстраполяции данных (так называемых задач регрессионного анализа). С помощью регрессионного анализа можно продолжить линию тренда вперед или назад, экстраполировать ее за пределы, в которых данные уже известны, и показать тенденцию их изменения.
На рис. 1 построен линейный тренд, то есть проведена прямая линия, дающая минимальное среднеквадратичное отклонение от результатов наблюдений в классе прямых линий.
Рис. 1. Таблица результатов, точечная диаграмма и линейный тренд
Вычисленна также среднеквадратичная ошибка аппроксимации R2. Помимо линейного тренда можно также построить скользящее среднее, которое сглаживает случайные отклонения, флюктуации данных, яснее демонстрирует модель и прослеживающуюся тенденцию изменения данных (тренд).
Линии тренда можно проводить на гистограммах, графиках, линейчатых и XY-точечных диаграммах. Нельзя строить линии тренда для рядов данных на объемных, круговых и кольцевых диаграммах. Если Вы измените тип представления группы рядов на один из вышеперечисленных, то соответствующие этим рядам данных линии тренда будут потеряны.
В Excel можно выбрать один из пяти типов экстраполяции с помощью диалогового окна, изображенного на рис. 2:
· линейный,
· полиномиальный,
· логарифмический,
· экспоненциальный,
· степенной.
Рис. 2. Диалоговое окно Линия тренда
Тип выбранной экстраполяции задает способ вычислений линии тренда. В зависимости от пользуемых данных, некоторые типы экстраполяции могут оказаться надежнее других с точки зрения оценки результатов сделанных прогнозов. Таким образом, может оказаться полезным проделать несколько различных экстраполяций, чтобы смотреть, какой из типов лучше всего подходит в данной ситуации.
Ход выполнения работы
1. заполните в MSExcel таблицу, полученную в предыдущей лабораторной работе, причем расчет мощности вставляем как расчет формул в данном приложении.
2. По данным таблицы постройте XY-точечную диаграмму
3. Для построения линии тренда активизируйте ряд данных, после чего нажмите правую клавишу мыши. В появившемся списке команд выберите Линии тренда, а затем в соответствующем окне диалога — рисунок с линейным трендом
4. Щелкните в открытом окне диалога по закладке Параметры и поставьте галочку напротив команды Поместить на диаграмму величину достоверности аппроксимации. После этого нажимайте кнопку ОК
5. Активизировав ряд данных и нажимая правую клавишу мыши для вызова меню с командой Вставить линии тренда, постройте аналогично полиномиальную (степень 2) линию тренда.
6. В закладке Параметры поставьте галочки напротив команд Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации.
7. Отформатируйте линии трендов, уравнения и области.
8. Проверкой правильности экстраполяции линии тренда может служить среднеквадратичное отклонение R2. По полученным результатам можно сделать вывод, что полиномиальная линия тренда аппроксимирует данные лучше линейной.
9. Выполните прогноз по полученным моделям, подставляя вместо переменной х значение по заданию преподавателя.
10. Нанести точки прогноза на график.
Лабораторная работа 7