Линейная модель в excel

Построение регрессионных моделей средствами Excel

ПОСТРОЕНИЕ РЕГРЕССИОННЫХ МОДЕЛЕЙ средствами excel

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

Пусть имеются ряды наблюдаемых величин t и у. Пусть ряд у представляет наблюдаемую величину продаж некоторым предприятием товара определенного вида за каждую неделю.

Значения элементов рядов представлены в табл.1.

Требуется построить линейную и нелинейную регрессионную модели yt=at+b, yt=b*exp(at). Параметры a и b подбираются так, чтобы минимизировать сумму квадратов разностей между наблюдаемым рядом y и теоретическими значениями yt, т. е. так, чтобы величина всех отклонений отвечала условию U=Σ(yi – ati – b)2 →min для i=1,2,3,…,n.

2.Последовательность выполнения задания

v Подготовим начальный рабочий лист с исходными данными как показано на рис.1.

Рис.1.

В диапазон ячеек А2:А9 введены значения из ряда t, в диапазон ячеек В2:В9 – значения ряда из табл.1. Под переменные a и b поиска решения отведены ячейки D2, Е2 соответственно. В ячейку F2 введена формула для минимизируемой функции цели:

=СУММКВРАЗН(B2:B9;E2+D2*A2:A9) (1)

В этой формуле использована функция СУММКВРАЗН(), вычисляющая сумму квадратов разностей соответствующих элементов двух массивов.

v Выберем команду Сервис/Поиск решения. Откроется диалоговое окно Поиск решения.

v Заполним диалоговое окно Поиск решения.

При заполнении окна Поиск решения введем абсолютную ссылку на ячейку с целевой функцией F2, в группе Равной выберем минимальному значению, так как требуется найти минимальное значение целевой функции, в поле Изменяя ячейки укажем диапазон ячеек D2:Е2.

v Далее установим параметры поиска решения, получим решение и далее повторим его с большей точностью и с меньшим допустимым отклонением и создадим отчет Excel по результатам (Рис.2.)

Microsoft Excel 12.0 Отчет по результатам

Рабочий лист: [работа3.xlsx]Лист1

Отчет создан: 07.04.2010 14:50:32

Целевая ячейка (Минимум)

v Найдем параметры а и b в линейной регрессионной модели с помощью статистических функций НАКЛОН() и ОТРЕЗОК(). Функция НАКЛОН() определяет коэффициент наклона линейного тренда. Ее формат записи – НАКЛОН( ; ), функция ОТРЕЗОК() определяет точку пересечения линейного тренда с осью ординат. Ее синтаксис – ОТРЕЗОК( ; ).

Аргументы этих функций:

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

— это массив известных значений зависимой наблюдаемой величины.

=НАКЛОН(B2:B9;A2:A9) (2)

=ОТРЕЗОК(B2:B9;A2:A9) (3)

в ячейки D4 и Е4 соответственно и сравним результаты с содержимым ячеек D2 и Е2.

v Найдем параметры а и b линейной регрессионной модели, используя команду Добавить линию тренда. Для этого:

ü построим точечный график по данным диапазона ячеек А2:В9, выделим точки графика двойным щелчком, затем щелкнем на них правой кнопкой мыши. Раскроется контекстное меню, в нем выберем команду Добавить линию тренда;

ü в раскрывшемся диалоговом окне Линия тренда на вкладке Тип выберем Линейная, затем на вкладке Параметры установим флажки Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации. Щелкнем кнопку ОК. (Рис.3.)

Рис.4. Диаграмма с линией тренда типа Линейная

v Вычислим теоретическое значение наблюдаемой величины yt при t из ячейки А2. Для этого в ячейку С2 введем формулу:

Сравним результат с содержимым ячейки В2.

v Вычислим теоретическое значение yt при t из ячейки А4 с помощью функции ПРЕДСКАЗ(). Ее синтаксис — ПРЕДСКАЗ(ti; ; ). Аргумент ti — это точка данных из массива t, для которой предсказывается теоретическое значение yti. Теоретическое значение в ячейке С4 вычислим по формуле:

Буксировкой формулы (5) вниз заполним диапазон С5:С9 новыми yt.

Сравним значения в диапазонах В4:В12 и С4:С12.

v Вычислим значения уравнения линейной регрессии для целого диапазона значений независимой переменной с помощью функции ТЕНДЕНЦИЯ(). Ее синтаксис – ТЕНДЕНЦИЯ( ; ; ;[ ]).

Аргумент — это массив значений t, для которых функция ТЕНДЕНЦИЯ() возвращает соответствующие значения yt.

Новое значение зависимой переменной вычислим в ячейке В10 по формуле:

Буксировкой формулы (6) вниз заполним диапазон В11:В12 новыми значениями у.

v Найдем параметры а и b линейной регрессионной модели с помощью статистической функции ЛЕНЕЙН(). Эта функция возвращает массив значений параметров уравнения многомерной регрессии, для двумерной регрессии – параметры а и b. Ее синтаксис – ЛЕНЕЙН( ; ;[ ];[ ]), где — это логическое значение.

Введем в ячейки D6:Е6 формулу:

Результаты, полученные в диапазонах ячеек D2:Е2, D4:Е4, D6:Е6 и на диаграмме с линией тренда типа Линейная, сравним между собой.

v Построим нелинейную экспоненциальную модель. Она описывается уравнением yt=b*exp(a*t).

Значения параметров b, m степенной модели вида yt=b*m^t определяется с помощью функции ЛГРФПРИБЛ( ; ;[ ];[ ]), где — это логическое значение, которое указывает, требуется ли вывести дополнительную статистику по регрессии, например корреляции.

ü Для определения параметров нелинейной экспоненциальной модели в ячейки D8:Е8 введем формулу:

а в ячейку Е9 – формулу: LN(D8) (9)

ü Значения экспоненциального тренда предсказывает функция РОСТ. Для построения экспоненциального тренда в ячейку G2 введем формулу:

и отбуксируем ее на диапазон G3:G12.

ü вычислим теоретическое значение yt (нелин) наблюдаемой величины, используя экспоненциальную модель.

Для этого введем в ячейку F4 формулу:

Буксировкой формулы (11) заполним диапазон F5:F12 результатами вычислений. Сравним значения в ячейках B4:B12, С4:С12, F4:F12, G4:G12.

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

Рис.5. График с линией тренда типа Экспоненциальная

ü Сравним между собой линейную и экспоненциальную модели по коэффициенту корреляции. Коэффициент корреляции в линейной и экспоненциальной модели различается на 0,042 (R2лин > R2экс).

ü Результаты решения задания представлены на Рис.6.

Построение полиномиальной регрессионной модели.

Требуется построить полиномиальные модели различных степеней вида

и сравнить оценки их погрешностей. Определение коэффициентов таких уравнений осуществить средством Поиск решения.

v Подготовим начальный рабочий лист кА показано на рис.7. Заданные ряды Y и t помещены в диапазоны С7:С16 и А7:А16 соответственно. В диапазонах С7:С16, D7:D16, E7:E16 будем отображать квадраты погрешности между фактическим значением Yi и полученным из полиномов второй, третьей и четвертой степени уравнений регрессии соответственно.

v Введем следующие формулы:

v Cкопируем эти формулы в диапазон С8:С15, D8:D15, E8:E15 соответственно. Полученные результаты сравним между собой ряды квадратов погрешностей полиномов.

v В ячейке С16 вычислим сумму квадратов погрешностей для приближения полиномом 2-й степени, введя формулу:

С17 = СУММ (С7:С16) (15)

Скопируем эту формулу методом буксировки вправо на диапазон D17:Е17, чтобы вычислить сумму квадратов погрешностей приближений полиномами 3-й и 4-й степени. Сравним результаты в ячейках С17:Е17 между собой.

v Для вычисления коэффициентов а, b, c полинома второй степени выберем команду Сервис/Поиск решения. В диалоговом окне Сервис/Поиск решения установим целевую ячейку С17, в поле Равной установим минимальному значению, в поле Изменяя ячейки – диапазон B3:D3. После щелчка Выполнить, результаты поиска – значения коэффициентов а, b, с появляются в ячейках B3:D3.

v Аналогично предыдущему пункту, используя средство Поиск решения, определим коэффициенты а, b, c, d в ячейках В4:Е4 для приближения полиномом 3-й степени, затем то же самое для приближения полиномом 4-й степени в ячейках В5:F5 определим коэффициенты а, b, c, d, e.

v Сделаем нелинейный регрессионный анализ, используя средства деловой графики Excel, не прибегая к вычислениям, сначала для модели 2-й степени. Для этого построим график Y(t), используя ряды t и Y в ячейках А7:А16, В7:В16. Затем кликнув щелчком на этом графике правой кнопкой мыши и в появившемся контекстном меню выберем пункт Добавить линию тренда. В появившемся окне Линия тренда, в котором выберем тип уравнения аппроксимации Полиномиальная и его степень; на вкладке Параметры установим флажки Показывать уравнение на диаграмме, Поместить на диаграмму величину достоверности аппроксимации. Будет выведен график уравнения регрессии и само уравнение с числовыми значениями коэффициентов и квадрат коэффициента корреляции R^2.

v Аналогично выполним графическое построение линий тренда для полиномов 3-й, 4-й степеней с показом уравнений на графике. Сравним регрессионные модели полиномами

Результаты решения показаны на рис.8.

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

С помощью команды Сервис/ Подбор параметра, функций НАКЛОН(), ОТРЕЗОК() и ЛИНЕЙН() я разными способами рассчитала значения а и b линейной регрессионной модели. Затем сравнила полученные значения, и они получились одинаковыми. По полученным данным я построила диаграмму с линией тренда типа Линейная. Также вычислила теоретическое значение yt при t с помощью функции ПРЕДСКАЗ() и новые значения у с помощью функции ТЕНДЕНЦИЯ().

Для определения параметров нелинейной экспоненциальной модели использовала формулу ЛГРФПРИБЛ, LN. Для построения экспоненциального тренда – функцию РОСТ. Затем вычислила теоретические значения yt (нелин) наблюдаемой величины, используя экспоненциальную модель и построила диаграмму с линией тренда типа Экспоненциальная.

Коэффициент корреляции в линейной и экспоненциальной модели различается на 0,0303 (R2лин > R2экс).

Во второй части данной работы я построила полиномиальные модели различных степеней. Для этого были использованы формулы . Для вычисления коэффициентов а, b, c полинома различных степеней использовала команду Сервис/Поиск решения. По полученным данным построила график Excel для модели 2-й, 3-й, 4-й степени. Сравнивая оценки погрешностей полиномонов 2-й, 3-й, 4-й степеней можно сделать вывод, что погрешность уменьшается с увеличением степени, особенно это наглядно представлено из вычисленных сумм квадратов разностей.

Archie Goodwin

Авторизация

Рубрики блога

• Public / Общие темы
  • Новость + Мнение
  • Размышления
  • Уроки или советы
  • Это интересно
  • Юмор
  • Креатив
  • Рецензия
  • Личность
  • Притчи, истории
  • Новости сайта
• Special / СпецРубрики
  • Записки вебмастера
  • Вопрос дизайна
  • Мой ПК
  • MS Office и VBA
  • Прочие офисные программы
• Diary / Личный дневник
  • Дневник

Рекомендуем

Последние комментарии

Облако тегов

Устами великих

Реклама

Линейная регрессия в Excel через Анализ данных

Что такое линейная регрессионная модель и зачем это нужно

Это наиболее распространенный способ показать зависимость какой-то переменной от других, например, как зависит уровень ВВП от величины иностранных инвестиций или от кредитной ставки Нацбанка или от цен на ключевые энергоресурсы.

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

Общий вид модели линейной регрессии:

где a — параметры (коэффициенты) регрессии, x — влияющие факторы, k — количество факторов модели.

Исходные данные

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

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

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

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

Пакет анализа

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

В Excel есть надстройка Пакет анализа, который является довольно мощным инструментом в помощь аналитику. Этот инструментарий, помимо всего прочего, умеет рассчитывать параметры регрессии, по тому же МНК, всего в несколько кликов, собственно, о том как этим инструментом пользоваться дальше и пойдет речь.

Активируем Пакет анализа

По умолчанию эта надстройка отключена и в меню вкладок вы ее не найдете, поэтому пошагово рассмотрим как ее активировать.

В эксель, слева вверху, активируем вкладку Файл, в открывшемся меню ищем пункт Параметры и кликаем на него.

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

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

Инструкция по поиску параметров линейной регрессии с помощью Пакета анализа

После активации надстройки Пакета анализа она будет всегда доступна во вкладке главного меню Данные под ссылкой Анализ данных

В активном окошке инструмента Анализа данных из списка возможностей ищем и выбираем Регрессия

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

После того как выбрали исходные данные и нажали кнопочку ОК, Excel выдает расчеты на новом листе активной книги (если в настройках не было выставлено иначе), эти расчеты имеют следующий вид:

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

Итак, 0,865 — это R 2 — коэффициент детерминации, показывающий что на 86,5% расчетные параметры модели, то есть сама модель, объясняют зависимость и изменения изучаемого параметра — Y от исследуемых факторов — иксов. Если утрировано, то это показатель качества модели и чем он выше тем лучше. Понятное дело, что он не может быть больше 1 и считается неплохо, когда R 2 выше 0,8, а если меньше 0,5, то резонность такой модели можно смело ставить под большой вопрос.

Теперь перейдем к коэффициентам модели:
2079,85 — это a — коэффициент который показывает какой будет Y в случае, если все используемые в модели факторы будут равны 0, подразумевается что это зависимость от других неописанных в модели факторов;
-0,0056 — a₁ — коэффициент, который показывает весомость влияния фактора x₁ на Y, то есть количество предприятий в пределах данной модели влияет на показатель экономически активного населения с весом всего -0,0056 (довольно маленькая степень влияния). Знак минус показывает что это влияние отрицательно, то есть чем больше предприятий, тем меньше экономически активного населения, как бы это ни было парадоксальным по смыслу;
-0,0026 — a₂ — коэффициент влияния объема инвестиций в капитал на величину экономически активного населения, согласно модели, это влияние также отрицательно;
0,0028 — a₃— коэффициент влияния доходов населения на величину экономически активного населения, здесь влияние позитивное, то есть согласно модели увеличение доходов будет способствовать увеличению величины экономически активного населения.

Соберем рассчитанные коэффициенты в модель:

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

Расчетные значения модели и прогноз

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

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

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

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

Линейное программирование в Excel

В Excel 2007 для включения пакета анализа надо нажать перейти в блок Параметры Excel, нажав кнопку в левом верхнем углу, а затем кнопку «Параметры Excel» внизу окна:

Для того чтобы решить задачу ЛП в табличном процессоре Microsoft Excel , необходимо выполнить следующие действия:
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
· переменных,
· целевой функции (ЦФ),
· ограничений,
· граничных условий;
b) ввести исходные данные в экранную форму:
· коэффициенты ЦФ,
· коэффициенты при переменных в ограничениях,
· правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
· формулу для расчета ЦФ,
· формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне «Поиск решения» ):
· целевую ячейку,
· направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне «Поиск решения» ):
· ячейки со значениями переменных,
· граничные условия для допустимых значений переменных,
· соотношения между правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне «Поиск решения» );
b) запустить задачу на решение (в окне «Поиск решения» );
c) выбрать формат вывода решения (в окне «Результаты поиска решения» ).

Рассмотрим подробно использование MS Excel на примере решения следующей задачи.

Фабрика «GRM pic» выпускает два вида каш для завтрака — «Crunchy» и «Chewy». Используемые для производства обоих продуктов ингредиенты в основ­ном одинаковы и, как правило, не являются дефицитными. Основным ограничением, накладываемым на объем выпуска, является наличие фонда рабочего времени в каждом из трех цехов фабрики.

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

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

б) Решить ее c помощью MS Excel.

Ввод исходных данных
Создание экранной формы и ввод исходных данных

Экранная форма для решения в MS Excel представлена на рисунке 1.

В экранной форме на рисунке 1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка на листе Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП. Так, например, переменным задачи 1 соответствуют ячейки B4 (x₁), C4 (x₂), коэффициентам ЦФ соответствуют ячейки B6 (c₁=150), C6 (c₂=75), правым частям ограничений соответствуют ячейки D18 (b₁=1000), D19 (b₂=360), D20 (b₃=600) и т.д.

Ввод зависимостей из формальной постановки задачи в экранную форму

Для ввода зависимостей определяющих выражение для целевой функции и ограничений используется функция MS Excel СУММПРОИЗВ , которая вычисляет сумму попарных произведений двух или более массивов.

Одним из самых простых способов определения функций в MS Excel является использование режима «Вставка функций» , который можно вызвать из меню «Вставка» или при нажатии кнопки f_x (рисунок 2) на стандартной панели инструментов.

Рисунок 2

Так, например, выражение для целевой функции из задачи 1 определяется следующим образом:
· курсор в поле D6;
· нажав кнопку f_x , вызовите окно «Мастер функций — шаг 1 из 2»;
· выберите в окне «Категория» категорию «Математические»;
· в окне «Функция» выберите функцию СУММПРОИЗВ (рис. 3);

Рисунок 3
· в появившемся окне «СУММПРОИЗВ» в строку «Массив 1» введите выражение B$4:C$4 , а в строку «Массив 2» — выражение B6:C6 (рис. 4);

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи ( B3, C3 ), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения ( B13, C13 — 1-е ограничение; B14, С14 — 2-е ограничение и B15, С15 — 3-е ограничение). Формулы, соответствующие левым частям ограничений, представлены в табл.1.
Таблица 1.

Формулы, описывающие ограничения модели (1)

Дальнейшие действия производятся в окне «Поиск решения» , которое вызывается из меню «Сервис» (рис.5):

· поставьте курсор в поле «Установить целевую ячейку» ;

· введите адрес целевой ячейки $D$6 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме ¾ это будет равносильно вводу адреса с клавиатуры;

· введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке «максимальному значению».

Ввод ограничений и граничных условий
Задание ячеек переменных

В окно «Поиск решения» в поле «Изменяя ячейки» впишите адреса $B$4:$С$4 . Необходимые адреса можно вносить в поле «Изменяя ячейки» и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Задание граничных условий для допустимых значений переменных

В нашем случае на значения переменных накладывается только граничное условие неотрицательности, то есть их нижняя граница должна быть равна нулю (см. рис. 1).
· Нажмите кнопку «Добавить» , после чего появится окно «Добавление ограничения» (рис.6).
· В поле «Ссылка на ячейку» введите адреса ячеек переменных $B$4:$С$4 . Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.
· В поле знака откройте список предлагаемых знаков и выберите ≥ .
· В поле «Ограничение» введите 0.

Рис.6 — Добавление условия неотрицательности переменных задачи (1)

Задание знаков ограничений ≤ , ≥ , = .
· Нажмите кнопку «Добавить» в окне «Добавление ограничения» .
· В поле «Ссылка на ячейку» введите адрес ячейки левой части конкретного ограничения, например $B$18 . Это можно сделать как с клавиатуры, так и путем выделения мышью нужной ячейки непосредственно в экранной форме.
· В соответствии с условием задачи (1) выбрать в поле знака необходимый знак, например, ≤ .
· В поле «Ограничение» введите адрес ячейки правой части рассматриваемого ограничения, например $D$18 .
· Аналогично введите ограничения: $B$19 , $B$20 .
· Подтвердите ввод всех перечисленных выше условий нажатием кнопки OK .

Окно «Поиск решения» после ввода всех необходимых данных задачи (1) представлено на рис. 5.
Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки «Изменить» или «Удалить» (см. рис. 5).

Решение задачи
Установка параметров решения задачи

Задача запускается на решение в окне «Поиск решения» . Но предварительно для установления конкретных параметров решения задач оптимизации определенного класса необходимо нажать кнопку «Параметры» и заполнить некоторые поля окна «Параметры поиска решения» (рис. 7).

Рис. 7 — Параметры поиска решения, подходящие для большинства задач ЛП

Параметр «Максимальное время» служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).
Параметр «Предельное число итераций» служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.
Параметр «Относительная погрешность» служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.
Параметр «Допустимое отклонение» служит для задания допуска на отклонение от оптимального решения в целочисленных задачах. При указании большего допуска поиск решения заканчивается быстрее.
Параметр «Сходимость» применяется только при решении нелинейных задач.Установка флажка «Линейная модель» обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.
Подтвердите установленные параметры нажатием кнопки «OK» .

Запуск задачи на решение
Запуск задачи на решение производится из окна «Поиск решения» путем нажатия кнопки «Выполнить» .

После запуска на решение задачи ЛП на экране появляется окно «Результаты поиска решения» с сообщением об успешном решении задачи, представленном на рис. 8.

Рис. 8 -. Сообщение об успешном решении задачи

Появление иного сообщения свидетельствует не о характере оптимального решения задачи, а о том, что при вводе условий задачи в MS Excel были допущены ошибки, не позволяющие MS Excel найти оптимальное решение, которое в действительности существует.
Если при заполнении полей окна «Поиск решения» были допущены ошибки, не позволяющие MS Excel применить симплекс-метод для решения задачи или довести ее решение до конца, то после запуска задачи на решение на экран будет выдано соответствующее сообщение с указанием причины, по которой решение не найдено. Иногда слишком малое значение параметра «Относительная погрешность» не позволяет найти оптимальное решение. Для исправления этой ситуации увеличивайте погрешность поразрядно, например от 0,000001 до 0,00001 и т.д.
В окне «Результаты поиска решения» представлены названия трех типов отчетов: «Результаты», «Устойчивость», «Пределы» . Они необходимы при анализе полученного решения на чувствительность. Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажмите кнопку «OK» . После этого в экранной форме появляется оптимальное решение задачи (рис. 9).

Эконометрика. Линейная Регрессия в MS Excel

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

Что объясняет регрессия?

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

Будем надеяться, вы знакомы с понятием функции из математики. Функция – это взаимосвязь двух переменных. При изменении одной переменной что-то происходит с другой. Изменяем X, меняется и Y, соответственно. Функциями описываются различные законы. Зная функцию, мы можем подставлять произвольные значения X и смотреть на то, как при этом изменится Y.

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

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

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

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

Линейная регрессия в MS Excel

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

• Жмём на кнопку «Файл»;
• Выбираем пункт «Параметры»;
• Жмём по предпоследней вкладке «Надстройки» с левой стороны;

• Снизу увидим Надпись «Управление» и кнопку «Перейти». Жмём по ней;
• Ставим галочку на «Пакет анализа»;
• Жмём «ок».

Пример задачи

Функция пакетного анализа активирована. Решим следующую задачу. У нас есть выборка данных за несколько лет о числе ЧП на территории предприятия и количестве трудоустроенных работников. Нам необходимо выявить взаимосвязь между этими двумя переменными. Есть объясняющая переменная X – это число рабочих и объясняемая переменная – Y – это число чрезвычайных происшествий. Распределим исходные данные в два столбца.

Перейдём во вкладку «данные» и выберем «Анализ данных»

В появившемся списке выбираем «Регрессия». Во входных интервалах Y и X выбираем соответствующие значения.

Нажимаем «Ок». Анализ произведён, и в новом листе мы увидим результаты.

Наиболее существенные для нас значения отмечены на рисунке ниже.

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

Y-пересечение и Пересечение X1 – это коэффициенты нашей регрессии. Как уже было сказано, регрессия – это функция, и у неё есть определённые коэффициенты. Таким образом, наша функция будет иметь вид: Y = 0,64*X-2,84.

Что нам это даёт? Это даёт нам возможность составить прогноз. Допустим, мы хотим нанять на предприятие 25 работников и нам нужно примерно представить, каким при этом будет количество чрезвычайных происшествий. Подставляем в нашу функцию данное значение и получаем результат Y = 0,64 * 25 – 2,84. Примерно 13 ЧП у нас будет происходить.

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

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

Точки идут вразброс, но в целом двигаются вверх, как будто посередине лежит прямая линия. И эту линию вы так же можете добавить, перейдя во вкладку «Макет» в MS Excel и выбрав пункт «Линия тренда»

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

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

Заключение

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

Пример построения многофакторной линейной модели в Excel;

Выводы по работе

Построение доверительной области для прогноза

Расчет доверительного интервала для прогноза

Доверительный интервал для прогнозируемого отклика вначале записывается в виде: , затем перечитывается для отклика y по формулам .

Доверительная область – совокупность доверительных интервалов.

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

8 Расчет максимального % ошибки прогнозирования

Максимальный % ошибки прогнозирования рассчитывается по формуле:

.

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

Среднее значение фактора , среднее значение отклика .

Полученная модель связи между фактором x и откликом y:

.

Модель адекватна исходным данным по критерию Фишера с уровнем доверия более 95%. Оба коэффициента статистически значимы по критерию Стьюдента.

Максимальный % ошибки прогнозирования составляет порядка 3%.

Листы Excel с расчетами приведены на рисунках 1.8, 1.9.

Рисунок 1.8 – Лист с расчетами степенной функции в Excel

Рисунок 1. 9 – Лист с формулами в Excel