Аппроксимация экспериментальных данных в программе Microsoft Excel.

По территориям региона приводятся данные за 200Х г.

Номер региона	Среднедушевой прожиточный минимум в день одного трудоспособного, руб., х	Среднедневная заработная плата, руб., у
1	78	133
2	82	148
3	87	134
4	79	154
5	89	162
6	106	195
7	67	139
8	88	158
9	73	152
10	87	162
11	76	159
12	115	173

Задание:

1. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

4. Дайте с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости .

Решение:

Решим данную задачу с помощью Excel.

1. Сопоставив имеющиеся данные х и у, например, ранжировав их в порядке возрастания фактора х, можно наблюдать наличие прямой зависимости между признаками, когда увеличение среднедушевого прожиточного минимума увеличивает среднедневную заработную плату. Исходя из этого, можно сделать предположение, что связь между признаками прямая и её можно описать уравнением прямой. Этот же вывод подтверждается и на основе графического анализа.

Чтобы построить поле корреляции можно воспользоваться ППП Excel. Введите исходные данные в последовательности: сначала х, затем у.

Выделите область ячеек, содержащую данные.

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессии
воспользуемся встроенной статистической функцией ЛИНЕЙН .

Для этого:

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций : в главном меню выберете Формулы / Вставить функцию .
4) В окне Категория выберете Статистические , в окне функция - ЛИНЕЙН . Щёлкните по кнопке ОК как показано на Рисунке 2;

Рисунок 2 Диалоговое окно «Мастер функций»

5) Заполните аргументы функции:

Известные значения у

Известные значения х

Константа - логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;

Статистика - логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щёлкните по кнопке ОК ;

Рисунок 3 Диалоговое окно аргументов функции ЛИНЕЙН

6) В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу , а затем на комбинацию клавиш ++ .

Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:

Значение коэффициента b	Значение коэффициента a
Стандартная ошибка b	Стандартная ошибка a
Стандартная ошибка y
F-статистика
Регрессионная сумма квадратов

Рисунок 4 Результат вычисления функции ЛИНЕЙН

Получили уровнение регрессии:

Делаем вывод: С увеличением среднедушевого прожиточного минимума на 1 руб. среднедневная заработная плата возрастает в среднем на 0,92 руб.

Означает, что 52% вариации заработной платы (у) объясняется вариацией фактора х - среднедушевого прожиточного минимума, а 48% - действием других факторов, не включённых в модель.

По вычисленному коэффициенту детерминации можно рассчитать коэффициент корреляции: .

Связь оценивается как тесная.

4. С помощью среднего (общего) коэффициента эластичности определим силу влияния фактора на результат.

Для уравнения прямой средний (общий) коэффициент эластичности определим по формуле:

Средние значения найдём, выделив область ячеек со значениями х, и выберем Формулы / Автосумма / Среднее , и то же самое произведём со значениями у.

Рисунок 5 Расчёт средних значений функции и аргумент

Таким образом, при изменении среднедушевого прожиточного минимума на 1% от своего среднего значения среднедневная заработная плата изменится в среднем на 0,51%.

С помощью инструмента анализа данных Регрессия можно получить:
- результаты регрессионной статистики,
- результаты дисперсионного анализа,
- результаты доверительных интервалов,
- остатки и графики подбора линии регрессии,
- остатки и нормальную вероятность.

Порядок действий следующий:

1) проверьте доступ к Пакету анализа . В главном меню последовательно выберите: Файл/Параметры/Надстройки .

2) В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

3) В окне Надстройки установите флажок Пакет анализа , а затем нажмите кнопку ОК .

Если Пакет анализа отсутствует в списке поля Доступные надстройки , нажмите кнопку Обзор , чтобы выполнить поиск.

Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да , чтобы установить его.

4) В главном меню последовательно выберите: Данные / Анализ данных / Инструменты анализа / Регрессия , а затем нажмите кнопку ОК .

5) Заполните диалоговое окно ввода данных и параметров вывода:

Входной интервал Y - диапазон, содержащий данные результативного признака;

Входной интервал X - диапазон, содержащий данные факторного признака;

Метки - флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа - ноль - флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал - достаточно указать левую верхнюю ячейку будущего диапазона;

6) Новый рабочий лист - можно задать произвольное имя нового листа.

Затем нажмите кнопку ОК .

Рисунок 6 Диалоговое окно ввода параметров инструмента Регрессия

Результаты регрессионного анализа для данных задачи представлены на рисунке 7.

Рисунок 7 Результат применения инструмента регрессия

5. Оценим с помощью средней ошибки аппроксимации качество уравнений. Воспользуемся результатами регрессионного анализа представленного на Рисунке 8.

Рисунок 8 Результат применения инструмента регрессия «Вывод остатка»

Составим новую таблицу как показано на рисунке 9. В графе С рассчитаем относительную ошибку аппроксимации по формуле:

Рисунок 9 Расчёт средней ошибки аппроксимации

Средняя ошибка аппроксимации рассчитывается по формуле:

Качество построенной модели оценивается как хорошее, так как не превышает 8 - 10%.

6. Из таблицы с регрессионной статистикой (Рисунок 4) выпишем фактическое значение F-критерия Фишера:

Поскольку при 5%-ном уровне значимости, то можно сделать вывод о значимости уравнения регрессии (связь доказана).

8. Оценку статистической значимости параметров регрессии проведём с помощью t-статистики Стьюдента и путём расчёта доверительного интервала каждого из показателей.

Выдвигаем гипотезу Н 0 о статистически незначимом отличии показателей от нуля:

.

для числа степеней свободы

На рисунке 7 имеются фактические значения t-статистики:

t-критерий для коэффициента корреляции можно рассчитать двумя способами:

I способ:

где - случайная ошибка коэффициента корреляции.

Данные для расчёта возьмём из таблицы на Рисунке 7.

II способ:

Фактические значения t-статистики превосходят табличные значения:

Поэтому гипотеза Н 0 отклоняется, то есть параметры регрессии и коэффициент корреляции не случайно отличаются от нуля, а статистически значимы.

Доверительный интервал для параметра a определяется как

Для параметра a 95%-ные границы как показано на рисунке 7 составили:

Доверительный интервал для коэффициента регрессии определяется как

Для коэффициента регрессии b 95%-ные границы как показано на рисунке 7 составили:

Анализ верхней и нижней границ доверительных интервалов приводит к выводу о том, что с вероятностью параметры a и b, находясь в указанных границах, не принимают нулевых значений, т.е. не являются статистически незначимыми и существенно отличны от нуля.

7. Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение прожиточного минимума составит:

Тогда прогнозное значение прожиточного минимума составит:

Ошибку прогноза рассчитаем по формуле:

где

Дисперсию посчитаем также с помощью ППП Excel. Для этого:

1) Активизируйте Мастер функций : в главном меню выберете Формулы / Вставить функцию .

3) Заполните диапазон, содержащий числовые данные факторного признака. Нажмите ОК .

Рисунок 10 Расчёт дисперсии

Получили значение дисперсии

Для подсчёта остаточной дисперсии на одну степень свободы воспользуемся результатами дисперсионного анализа как показано на Рисунке 7.

Доверительные интервалы прогноза индивидуальных значений у при с вероятностью 0,95 определяются выражением:

Интервал достаточно широк, прежде всего, за счёт малого объёма наблюдений. В целом выполненный прогноз среднемесячной заработной платы оказался надёжным.

Условие задачи взято из: Практикум по эконометрике: Учеб. пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. - М.: Финансы и статистика, 2003. - 192 с.: ил.

Теоретическая справка

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

Такого рода задачи приближения функций часто возникают:

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

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

В данной статье рассмотрены возможности пакета MS Excel для решения такого рода задач, кроме того, приведены методы и приемы построения (создания) регрессий для таблично заданных функций (что является основой регрессионного анализа).

В Excel для построения регрессий имеются две возможности.

1. Добавление выбранных регрессий (линий тренда - trendlines) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (доступно лишь при наличии построенной диаграммы);
2. Использование встроенных статистических функций рабочего листа Excel, позволяющих получать регрессии (линии тренда) непосредственно на основе таблицы исходных данных.

Добавление линий тренда в диаграмму

Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анализа, позволяющий:

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

На основе данных диаграммы Excel позволяет получать линейный, полиномиальный, логарифмический, степенной, экспоненциальный типы регрессий, которые задаются уравнением:

y = y(x)

где x - независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).

1 . Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении модель исследуемого процесса. Она

y = mx + b

где m - тангенс угла наклона линейной регрессии к оси абсцисс; b - координата точки пересечения линейной регрессии с осью ординат.

2 . Полиномиальная линия тренда полезна для описания характеристик, имеющих несколько ярко выраженных экстремумов (максимумов и минимумов). Выбор степени полинома определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать процесс, имеющий только один максимум или минимум; полином третьей степени - не более двух экстремумов; полином четвертой степени - не более трех экстремумов и т. д.

В этом случае линия тренда строится в соответствии с уравнением:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

где коэффициенты c0, c1, c2,... c6 - константы, значения которых определяются в ходе построения.

3 . Логарифмическая линия тренда с успехом применяется при моделировании характеристик, значения которых вначале быстро меняются, а затем постепенно стабилизируются.

Строится в соответствии с уравнением:

y = c ln(x) + b

4 . Степенная линия тренда дает хорошие результаты, если значения исследуемой зависимости характеризуются постоянным изменением скорости роста. Примером такой зависимости может служить график равноускоренного движения автомобиля. Если среди данных встречаются нулевые или отрицательные значения, использовать степенную линию тренда нельзя.

Строится в соответствии с уравнением:

y = c xb

где коэффициенты b, с - константы.

5 . Экспоненциальную линию тренда следует использовать в том случае, если скорость изменения данных непрерывно возрастает. Для данных, содержащих нулевые или отрицательные значения, этот вид приближения также неприменим.

Строится в соответствии с уравнением:

y = c ebx

где коэффициенты b, с - константы.

При подборе линии тренда Excel автоматически рассчитывает значение величины R2, которая характеризует достоверность аппроксимации: чем ближе значение R2 к единице, тем надежнее линия тренда аппроксимирует исследуемый процесс. При необходимости значение R2 всегда можно отобразить на диаграмме.

Определяется по формуле:

Для добавления линии тренда к ряду данных следует:

• активизировать построенную на основе ряда данных диаграмму, т. е. щелкнуть в пределах области диаграммы. В главном меню появится пункт Диаграмма;
• после щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.

Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 1).

После этого необходимо:

Выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень следует задать степень выбранного полинома.

1 . В поле Построен на ряде перечислены все ряды данных рассматриваемой диаграммы. Для добавления линии тренда к конкретному ряду данных следует в поле Построен на ряде выбрать его имя.

При необходимости, перейдя на вкладку Параметры (рис. 2), можно для линии тренда задать следующие параметры:

• изменить название линии тренда в поле Название аппроксимирующей (сглаженной) кривой.
• задать количество периодов (вперед или назад) для прогноза в поле Прогноз;
• вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;
• вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);
• задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке;
• щелкнуть на кнопке OK, чтобы закрыть диалоговое окно.

Для того, чтобы начать редактирование уже построенной линии тренда, существует три способа:

воспользоваться командой Выделенная линия тренда из меню Формат, предварительно выбрав линию тренда;

выбрать команду Формат линии тренда из контекстного меню, которое вызывается щелчком правой кнопки мыши по линии тренда;

двойным щелчком по линии тренда.

На экране появится диалоговое окно Формат линии тренда (рис. 3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (рис.1-2). На вкладке Вид, можно задать тип линии, ее цвет и толщину.

Для удаления уже построенной линии тренда следует выбрать удаляемую линию тренда и нажать клавишу Delete.

Достоинствами рассмотренного инструмента регрессионного анализа являются:

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

К недостаткам можно отнести следующие моменты:

построение линии тренда осуществляется лишь при наличии диаграммы, построенной на ряде данных;

процесс формирования рядов данных для исследуемой характеристики на основе полученных для нее уравнений линий тренда несколько загроможден: искомые уравнения регрессий обновляются при каждом изменении значений исходного ряда данных, но только в пределах области диаграммы, в то время как ряд данных, сформированный на основе старого уравнения линии тренда, остается без изменения;

в отчетах сводных диаграмм при изменении представления диаграммы или связанного отчета сводной таблицы имеющиеся линии тренда не сохраняются, то есть до проведения линий тренда или другого форматирования отчета сводных диаграмм следует убедиться, что макет отчета удовлетворяет необходимым требованиям.

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

Нельзя дополнить линиями тренда ряды данных на объемных, нормированных, лепестковых, круговых и кольцевых диаграммах.

Использование встроенных функций Excel

В Excel имеется также инструмент регрессионного анализа для построения линий тренда вне области диаграммы. Для этой цели можно использовать ряд статистических функций рабочего листа, однако все они позволяют строить лишь линейные или экспоненциальные регрессии.

В Excel имеется несколько функций для построения линейной регрессии, в частности:

• ТЕНДЕНЦИЯ;
• ЛИНЕЙН;
• НАКЛОН и ОТРЕЗОК.

А также несколько функций для построения экспоненциальной линии тренда, в частности:

• РОСТ;
• ЛГРФПРИБЛ.

Следует отметить, что приемы построения регрессий с помощью функций ТЕНДЕНЦИЯ и РОСТ практически совпадают. То же самое можно сказать и о паре функций ЛИНЕЙН и ЛГРФПРИБЛ. Для четырех этих функций при создании таблицы значений используются такие возможности Excel, как формулы массивов, что несколько загромождает процесс построения регрессий. Заметим также, что построение линейной регрессии, на наш взгляд, легче всего осуществить с помощью функций НАКЛОН и ОТРЕЗОК, где первая из них определяет угловой коэффициент линейной регрессии, а вторая - отрезок, отсекаемый регрессией на оси ординат.

Достоинствами инструмента встроенных функций для регрессионного анализа являются:

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

А к недостаткам относится то, что в Excel нет встроенных функций для создания других (кроме линейного и экспоненциального) типов линий тренда. Это обстоятельство часто не позволяет подобрать достаточно точную модель исследуемого процесса, а также получить близкие к реальности прогнозы. Кроме того, при использовании функций ТЕНДЕНЦИЯ и РОСТ не известны уравнения линий тренда.

Следует отметить, что авторы не ставили целью статьи изложение курса регрессионного анализа с той или иной степенью полноты. Основная ее задача - на конкретных примерах показать возможности пакета Excel при решении задач аппроксимации; продемонстрировать, какими эффективными инструментами для построения регрессий и прогнозирования обладает Excel; проиллюстрировать, как относительно легко такие задачи могут быть решены даже пользователем, не владеющим глубокими знаниями регрессионного анализа.

Примеры решения конкретных задач

Рассмотрим решение конкретных задач с помощью перечисленных инструментов пакета Excel.

Задача 1

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг. необходимо выполнить следующие действия.

1. Построить диаграмму.
2. В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.
3. Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 1995-2004 г.г.
4. Составить прогноз по прибыли предприятия на 2003 и 2004 гг.

Решение задачи

1. В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 4.
2. Выделив диапазон ячеек В4:С11, строим диаграмму.
3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда (см. рис. 1) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры (см. рис. 2), в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на экране и поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда (см. рис. 3). Полученная диаграмма с добавленными линиями тренда представлена на рис. 5.
4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 1995-2004 гг. воспользуемся уравнениями линий тренда, представленными на рис. 5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии - диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2003 и 2004 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 6.

Задача 2

1. Построить диаграмму.
2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 1995-2002 гг.
5. Составить прогноз о прибыли предприятия на 2003 и 2004 гг., используя эти линии тренда.

Решение задачи

Следуя методике, приведенной при решении задачи 1, получаем диаграмму с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис. 7). Далее, используя полученные уравнения линий тренда, заполняем таблицу значений по прибыли предприятия, включая прогнозируемые значения на 2003 и 2004 гг. (рис. 8).

На рис. 5 и рис. видно, что модели с логарифмическим трендом, соответствует наименьшее значение достоверности аппроксимации

R2 = 0,8659

Наибольшие же значения R2 соответствуют моделям с полиномиальным трендом: квадратичным (R2 = 0,9263) и кубическим (R2 = 0,933).

Задача 3

С таблицей данных о прибыли автотранспортного предприятия за 1995-2002 гг., приведенной в задаче 1, необходимо выполнить следующие действия.

1. Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.
2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2003 и 2004 гг.
3. Для исходных данных и полученных рядов данных построить диаграмму.

Решение задачи

Воспользуемся рабочей таблицей задачи 1 (см. рис. 4). Начнем с функции ТЕНДЕНЦИЯ:

1. выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия;
2. вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов.
3. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11;
4. чтобы вводимая формула стала формулой массива, используем комбинацию клавиш + + .

Введенная нами формула в строке формул будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11)}.

В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 9).

Для составления прогноза о прибыли предприятия на 2003 и 2004 гг. необходимо:

1. выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ.
2. вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y - диапазон ячеек C4:C11; в поле Известные_значения_х - диапазон ячеек B4:B11; а в поле Новые_значения_х - диапазон ячеек B12:B13.
3. превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.
4. Введенная формула будет иметь вид: ={ТЕНДЕНЦИЯ(C4:C11;B4:B11;B12:B13)}, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 9).

Аналогично заполняется ряд данных с помощью функции РОСТ, которая используется при анализе нелинейных зависимостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ.

На рис.10 представлена таблица в режиме показа формул.

Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 11.

Задача 4

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

1. Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.
2. Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
3. Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
4. Для исходных и полученных рядов данных построить диаграмму.

Решение задачи

Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

1 . Построим линейную регрессию, имеющую уравнение:

y = mx+b

с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b - функцией ОТРЕЗОК.

Для этого осуществляем следующие действия:

1. заносим исходную таблицу в диапазон ячеек A4:B14;
2. значение параметра m будет определяться в ячейке С19. Выбираем из категории Статистические функцию Наклон; заносим диапазон ячеек B4:B14 в поле известные_значения_y и диапазон ячеек А4:А14 в поле известные_значения_х. В ячейку С19 будет введена формула: =НАКЛОН(B4:B14;A4:A14);
3. по аналогичной методике определяется значение параметра b в ячейке D19. И ее содержимое будет иметь вид: =ОТРЕЗОК(B4:B14;A4:A14). Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C19, D19;
4. далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C*A4+$D. В этой формуле ячейки С19 и D19 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 12). В связи с тем, что количество заявок - целое число, следует установить на вкладке Число окна Формат ячеек числовой формат с числом десятичных знаков 0.

2 . Теперь построим линейную регрессию, заданную уравнением:

y = mx+b

с помощью функции ЛИНЕЙН.

Для этого:

1. вводим в диапазон ячеек C20:D20 функцию ЛИНЕЙН как формулу массива: ={ЛИНЕЙН(B4:B14;A4:A14)}. В результате получаем в ячейке C20 значение параметра m, а в ячейке D20 - значение параметра b;
2. вводим в ячейку D4 формулу: =$C*A4+$D;
3. копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.

3 . Строим экспоненциальную регрессию, имеющую уравнение:

y = bmx

с помощью функции ЛГРФПРИБЛ оно выполняется аналогично:

в диапазон ячеек C21:D21 вводим функцию ЛГРФПРИБЛ как формулу массива: ={ ЛГРФПРИБЛ (B4:B14;A4:A14)}. При этом в ячейке C21 будет определено значение параметра m, а в ячейке D21 - значение параметра b;

в ячейку E4 вводится формула: =$D*$C^A4;

с помощью маркера заполнения эта формула копируется в диапазон ячеек E4:E17, где и расположится ряд данных для экспоненциальной регрессии (см. рис. 12).

На рис. 13 приведена таблица, где видны используемые нами функции с необходимыми диапазонами ячеек, а также формулы.

Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 14.

ЗАВИСИМОСТЕЙ

Excel располагает средствами, позволяющими прогнозировать процессы. Задача аппроксимации возникает в случае необходимости аналитически описать явления, имеющие место в жизни и заданные в виде таблиц, содержащих значения аргумента (аргументов) и функции. Если зависимость удается найти, можно сделать прогноз о поведении исследуемой системы в будущем и, возможно, выбрать оптимальное направление ее развития. Такая аналитическая функция (называемая еще трендом) может иметь разный вид и разный уровень сложности в зависимости от сложности системы и желаемой точности представления.

10.1. Линейная регрессия

Самый простой и популярной является аппроксимация прямой линией – линейная регрессия.

Пусть мы имеем фактическую информацию об уровнях прибыли Y в зависимости от размера X капиталовложений – Y(X). На рис. 10.1-1 показаны четыре такие точки М(Y,X). Пусть также у нас имеются основания предполагать, что зависимость эта линейная, т.е. имеет вид Y=А+ВX. Если бы нам удалось найти коэффициенты A и B и по ним построить прямую (например, такую, как на рисунке), в дальнейшем мы могли бы сделать осознанные предположения о динамике бизнеса и возможном коммерческом состоянии предприятия в будущем. Очевидно, что нас бы устроила прямая, находящаяся как можно ближе к известным точкам М(Y,X), т.е. имеющая минимальную сумму отклонений или сумму ошибок (на рисунке отклонения показаны пунктирными линиями). Известно, что существует только одна такая прямая.

Для решения этой задачи используют метод наименьших квадратов ошибок. Разность (ошибка) между известным значением Y1 точки М1(Y1,X1) и значением Y(X1), вычисленным по уравнению прямой для того же значения X1, составит

D1 = Y1 – A – B X1.

Такая же разность

для X=X2 составит D2 = Y2 – A – B X2;

для X=X3 D3 = Y3 – A – B X3;

и для X=X4 D4 = Y4 – A – B X4.

Запишем выражение для суммы квадратов этих ошибок

Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2

или сокращенно Ф(B,A) = å(Yi – A – BXi) 2 .

Здесь нам известны все X и Y и неизвестны коэффициенты A и B. Проведем искомую прямую так (т.е. выберем A и B такими), чтобы эта сумма квадратов ошибок Ф(A,B) была минимальной. Условиями минимальности являются известные соотношения

¶Ф(A,B)/¶A=0 и ¶Ф(A,B)/¶B=0.

Выведем эти выражения (индексы при знаке суммы опускаем):

¶[å(Yi–A–B Xi) 2 ]/¶A = å(Yi–A–B Xi)(–1)

¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).

Преобразуем полученные формулы и приравняем их нулю

Аппроксимация (от латинского "approximate" -"приближаться")- приближенное выражение каких-либо математических объектов (например, чисел или функций) через другие более простые, более удобные в пользовании или просто более известные. В научных исследованиях аппроксимация применяется для описания, анализа, обобщения и дальнейшего использования эмпирических результатов. Как известно, между величинами может существовать точная функциональная связь, когда одному значению аргумента соответствует одно определенное значение, и менее точная (корреляционная) связь, когда одному конкретному значению аргумента соответствует приближенное значение или некоторое множество значений функции, в той или иной степени близких друг к другу. При ведении научных исследований, обработке результатов наблюдения или эксперимента обычно приходиться сталкиваться со вторым вариантом. При изучении количественных зависимостей различных показателей, значения которых определяются эмпирически, как правило, имеется некоторая их вариабельность. Частично она задается неоднородностью самих изучаемых объектов неживой и, особенно, живой природы, частично обуславливается погрешностью наблюдения и количественной обработке материалов. Последнюю составляющую не всегда удается исключить полностью, можно лишь минимизировать ее тщательным выбором адекватного метода исследования и аккуратностью работы. Поэтому при выполнении любой научно-исследовательской работы возникает проблема выявления подлинного характера зависимости изучаемых показателей, этой или иной степени замаскированных неучтенностью вариабельности значений. Для этого и применяется аппроксимация - приближенное описание корреляционной зависимости переменных подходящим уравнением функциональной зависимости, передающим основную тенденцию зависимости (или ее "тренд").

При выборе аппроксимации следует исходить из конкретной задачи исследования. Обычно, чем более простое уравнение используется для аппроксимации, тем более приблизительно получаемое описание зависимости. Поэтому важно считывать, насколько существенны и чем обусловлены отклонения конкретных значений от получаемого тренда. При описании зависимости эмпирически определенных значений можно добиться и гораздо большей точности, используя какое-либо более сложное, много параметрическое уравнение. Однако нет никакого смысла стремиться, с максимальной точностью передать случайные отклонения величин в конкретных рядах эмпирических данных. Гораздо важнее уловить общую закономерность, которая в данном случае наиболее логично и с приемлемой точностью выражается именно двухпараметрическим уравнением степенной функции. Таким образом, выбирая метод аппроксимации, исследователь всегда идет на компромисс: решает, в какой степени в данном случае целесообразно и уместно «пожертвовать» деталями и, соответственно, насколько обобщенно следует выразить зависимость сопоставляемых переменных. Наряду с выявлением закономерностей, замаскированных случайными отклонениями эмпирических данных от общей закономерности, аппроксимация позволяет также решать много других важных задач: формализовать найденную зависимость; найти неизвестные значения зависимой переменной путем интерполяции или, если это допустимо, экстраполяции. Очень часто, особенно при анализе эмпирических данных возникает необходимость найти в явном виде функциональную зависимость между величинами x и y , которые получены в результате измерений.

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

Коэффициент корреляции является мерой линейной связи между зависимыми случайными величинами: он показывает, насколько хорошо в среднем может быть представлена о (дна из величин в виде линейной функции от другой). В случае нелинейной корреляционной связи условные средние значения располагаются около кривой линии. В этом случае в качестве характеристики силы связи рекомендуется использовать корреляционное отношение, интерпретация которого не зависит от вида исследуемой зависимости. Величина используется в качестве индикатора отклонения регрессии от линейной. Корреляционное отношение является мерой корреляционной связи, y с x в какой угодно форме, но не может дать представления о степени приближенности эмпирических данных к специальной форме эмпирические данные.

В MS Excel аппроксимация экспериментальных данных осуществляется путем построения их графика (x - отвлеченные величины) или точечного графика (x - имеет конкретные значения) с последующим подбором подходящей аппроксимирующей функции (линии тренда).Возможны следующие варианты функций:

Линейная - y=ax+b. Обычно применяется в простейших случаях, когда экспериментальные данные возрастают или убывают с постоянной скоростью.

Полиномиальная - y=a 0 +a 1 x+a 2 x 2 +…+a n x n , где до шестого порядка включительно (n?6), a i - константы. Используется для описания экспериментальных данных, попеременно возрастающих и убывающих. Степень полинома определяется количеством экстремумов (максимумов или минимумов) кривой. Полином второй степени можно описать только один максимум или минимум, полином третьей степени может иметь один или два экстремума, четвертой степени - не более трех экстремумов и т.д.

Логарифмическая - y=a·lnx+b, где a и b - константы, ln - функция натурального логарифма. Функция применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем постепенно стабилизируются.

Степенная - y=b·x a , где a и b - константы. Аппроксимация степенной функцией используется для экспериментальных данных с постоянно увеличивающейся (или убывающей) скоростью роста. Данные не должны иметь нулевых или отрицательных значений.

Экспоненциальная - y=b·e ax , a и b - константы, e - основание натурального логарифма. Применяется для описания экспериментальных данных, которые быстро растут или убывают, а затем постепенно стабилизируются. Часто ее использование вытекает из теоретических соображений.Степень близости аппроксимации экспериментальных данных выбранной функцией оценивается коэффициентом детерминации (R 2)Таким образом, если есть несколько подходящих вариантов типов аппроксимирующих функций, можно выбрать функцию с большим коэффициентом детерминации (стремящимся к 1).

Для осуществления аппроксимации на диаграмме экспериментальных данных необходимо щелчком правой кнопки мыши вызвать выплывающее контекстное меню и выбрать пункт добавить линию Тренда. В появившемся диалоговом окне Линия тренда на вкладке Тип выбирается вид аппроксимирующей функции, а на вкладке Параметры задаются дополнительные параметры, влияющие на отображение аппроксимирующей кривой. Обычно определение параметров при известном виде зависимости осуществляют по методу наименьших квадратов. При этом функция ц(x) считается наилучшим приближением к f(x), если для нее сумма квадратов невязок е i =f(x i)-ц(x i) или отклонений “теоретических” значений ц(x i), найденных по эмпирической формуле, от соответствующих опытных значений y i , имеет наименьшее значение по сравнению с другими функциями, из числа которых выбирается искомое приближение.

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

Так, если функция ц(x) вполне определяется своими параметрами a, b, c, то наилучшие значения этих параметров находятся из решения системы уравнений.

Для наглядной иллюстрации тенденций изменения цены применяется линия тренда. Элемент технического анализа представляет собой геометрическое изображение средних значений анализируемого показателя.

Рассмотрим, как добавить линию тренда на график в Excel.

Добавление линии тренда на график

Для примера возьмем средние цены на нефть с 2000 года из открытых источников. Данные для анализа внесем в таблицу:

Линия тренда в Excel – это график аппроксимирующей функции. Для чего он нужен – для составления прогнозов на основе статистических данных. С этой целью необходимо продлить линию и определить ее значения.

Если R2 = 1, то ошибка аппроксимации равняется нулю. В нашем примере выбор линейной аппроксимации дал низкую достоверность и плохой результат. Прогноз будет неточным.

Внимание!!! Линию тренда нельзя добавить следующим типам графиков и диаграмм:

• лепестковый;
• круговой;
• поверхностный;
• кольцевой;
• объемный;
• с накоплением.



Уравнение линии тренда в Excel

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

Следует выбирать тот тип отображения, который наиболее точно проиллюстрирует тенденцию изменений вводимых пользователем данных. Разберемся с вариантами.

Линейная аппроксимация

Ее геометрическое изображение – прямая. Следовательно, линейная аппроксимация применяется для иллюстрации показателя, который растет или уменьшается с постоянной скоростью.

Рассмотрим условное количество заключенных менеджером контрактов на протяжении 10 месяцев:

На основании данных в таблице Excel построим точечную диаграмму (она поможет проиллюстрировать линейный тип):

Выделяем диаграмму – «добавить линию тренда». В параметрах выбираем линейный тип. Добавляем величину достоверности аппроксимации и уравнение линии тренда в Excel (достаточно просто поставить галочки внизу окна «Параметры»).

Получаем результат:

Обратите внимание! При линейном типе аппроксимации точки данных расположены максимально близко к прямой. Данный вид использует следующее уравнение:

y = 4,503x + 6,1333

• где 4,503 – показатель наклона;
• 6,1333 – смещения;
• y – последовательность значений,
• х – номер периода.

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

Чтобы спрогнозировать количество заключенных контрактов, например, в 11 периоде, нужно подставить в уравнение число 11 вместо х. В ходе расчетов узнаем, что в 11 периоде этот менеджер заключит 55-56 контрактов.

Экспоненциальная линия тренда

Данный тип будет полезен, если вводимые значения меняются с непрерывно возрастающей скоростью. Экспоненциальная аппроксимация не применяется при наличии нулевых или отрицательных характеристик.

Построим экспоненциальную линию тренда в Excel. Возьмем для примера условные значения полезного отпуска электроэнергии в регионе Х:

Строим график. Добавляем экспоненциальную линию.

Уравнение имеет следующий вид:

y = 7,6403е^-0,084x

• где 7,6403 и -0,084 – константы;
• е – основание натурального логарифма.

Показатель величины достоверности аппроксимации составил 0,938 – кривая соответствует данным, ошибка минимальна, прогнозы будут точными.

Логарифмическая линия тренда в Excel

Используется при следующих изменениях показателя: сначала быстрый рост или убывание, потом – относительная стабильность. Оптимизированная кривая хорошо адаптируется к подобному «поведению» величины. Логарифмический тренд подходит для прогнозирования продаж нового товара, который только вводится на рынок.

На начальном этапе задача производителя – увеличение клиентской базы. Когда у товара будет свой покупатель, его нужно удержать, обслужить.

Построим график и добавим логарифмическую линию тренда для прогноза продаж условного продукта:

R2 близок по значению к 1 (0,9633), что указывает на минимальную ошибку аппроксимации. Спрогнозируем объемы продаж в последующие периоды. Для этого нужно в уравнение вместо х подставлять номер периода.

Например:

Период	14	15	16	17	18	19	20
Прогноз	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Для расчета прогнозных цифр использовалась формула вида: =272,14*LN(B18)+287,21. Где В18 – номер периода.

Полиномиальная линия тренда в Excel

Данной кривой свойственны переменные возрастание и убывание. Для полиномов (многочленов) определяется степень (по количеству максимальных и минимальных величин). К примеру, один экстремум (минимум и максимум) – это вторая степень, два экстремума – третья степень, три – четвертая.

Полиномиальный тренд в Excel применяется для анализа большого набора данных о нестабильной величине. Посмотрим на примере первого набора значений (цены на нефть).

Чтобы получить такую величину достоверности аппроксимации (0,9256), пришлось поставить 6 степень.

Зато такой тренд позволяет составлять более-менее точные прогнозы.