Материал: 1263

аппаратуры) по нескольким известным их сочетаниям (экспериментальным моделям).

Пусть необходимо определить цену продукции с качеством х2 = 530 дней (гарантированное время работы прибора) при производительности в х1 = 610 единиц в час. Уравнение линейной множественной регрессии будем определять для исходных данных, приведенных на рис. 25. Здесь представлено два технических параметра (n = 2):

х1 производительность (количество операций в час); х2 характеристика качества (время наработки на отказ в днях)

и один экономический:

у цена аппаратуры (тыс. руб.).

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

Алгоритм (состоит из 3 шагов).

Шаг №1. Определение уравнения регрессии.

Пусть известно 6 исходных моделей. Ввести исходные данные, как показано на рис. 25.

Определить минимальные и максимальные значения переменных: курсор в В11 Мастер функций Статистические МИН(В4:В9) Ок (аналогично в В12 ввести функцию =МАКС(В4:В9) и скопировать блок В11:В12 в

С11:С12).

Выделить блок В14:D18, в котором количество строк всегда = 5, а количество столбцов = n + 1 = 2 + 1 = 3.

В ячейку В14 ввести функцию =ЛИНЕЙН(D4:D9; В4:С9; истина; истина) и затем нажать ни в коем случае не Ok, а на сочетание клавиш <Ctrl> + <Shift> + <Enter>. На экране в ячейках B14:D18 получен результат вычислений.

Смысл полученных величин показан в таблице соответствий на рис. 25, где приняты следующие обозначения: m2, m1, b искомые коэффициенты в уравнении линейной регрессии. Таким образом, искомое уравнение имеет вид y = 1721,33 + 3,88x1 + 5,69x2. Полученное уравнение описывает зависимость экономического параметра (цены) от технических (производительности и качества). При этом нельзя забывать, что это уравнение регрессии справедливо

35

только в пределах определенных минимальных и максимальных значений переменных

120 х1 860; 145 х2 960,

на базе которых оно и было получено.

Рис. 25. Оформление задачи множественной регрессии

Далее в блоке В14:D18 (см. рис. 25) представлены величины, необходимые для оценки достоверности полученного уравнения регрессии:

[mn], [mn 1] … [b] средние квадратичные отклонения полученных значений;

df число степеней свободы, определяемое по формуле df = k (n + 1), где k число строк (наблюдений) в таблице исходных данных, т.е. k = 6, n число аргументов.

Следовательно, df = 6 (2 + 1) = 3;

F расчетное значение F-распределения;

Sreg регрессионная сумма квадратов отклонений;

Sresid остаточная сумма квадратов отклонений между теоретическим значением у и фактическим уi;

R2 коэффициент детерминации.

Сумма квадратов отклонений между фактическим значением уi и средним его значением называется общей суммой квадратов,

36

которая равна сумме Sreg и Sresid, поэтому, чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше R2 и, следовательно, лучше регрессионные связи в полученном уравнении.

Оценка достоверности зависимости y от хi производится по величине R2, которая находится в ячейке В16. Полученное значение R2 = 0,88 (или R2 = 88%) достаточно высокое и подтверждает достоверность наличия зависимости между у и хi.

Шаг №2. Определение значимости R2.

Далее определяется значение значимости самой величины R2. Это производится с помощью F-распределения, которое определяетвероятность того, что зависимость у от xi отсутствует. Отсюда следует, что величина (1 ) определяет вероятность того, что такая зависимость существует.

Для этого (рис. 26) курсор в В21 Мастер функций Статистические FРАСП (x, степени свободы 1, степени свободы 2). Здесь х Fрасч (т. е. В17 на рис. 25), степени свободы 1 число аргументов (n=2), степени свободы 2 = df (т. е. C17 на рис. 25).

В ячейку В22 ввести формулу [= 1 В21].

Рис. 26. Степень значимости полученного уравнения

Значение (1 ) = 0,96 или 96% показывает вероятность существования полученных регрессионных связей.

Шаг №3. Определение значимости коэффициентов b и mi.

Далее при помощи t-распределения Стьюдента сначала оценивается недостоверность значений коэффициентов уравнения регрессии b и mi, а затем вероятность того, что эти коэффициенты достоверны (1 ).

Для этого (рис. 27 формулы, рис. 28 результаты): 1) ввести в В25 = В14/В15;

37

2) курсор в В26 Мастер функций Статистические

СТЬЮДРАСП (x, степени свободы, хвосты). Здесь х ti (т. е. В25 на рис. 27), степени свободы = df (т. е. C17 на рис. 25),

хвосты = 2 (это признак используемого нами двухстепенного распределения Стьюдента);

3)ввести в В27 = 1 В26;

4)скопировать В25:В27 в ячейки С25:D27.

Рис. 27. Запись формул для оценки значимости коэффициентов

Рис. 28. Степень значимости коэффициентов уравнения

На рис. 28 в ячейках В27:D27 показаны вероятности того, что коэффициенты уравнения регрессии достоверны. Самый значимый параметр это качество, то есть параметр x2 = 520 при m2 = 5,69.

Тогда прогноз по условию задачи будет рис. 29.

Рис. 29. Прогноз цены по данным заказчика

38

В Excel возможен и другой вариант решения этой задачи надстройка «Анализ данных», то есть последовательность команд

Сервис Анализ данных Регрессия.

Функция ЛИНЕЙН() может быть использована для определения вероятностей достоверности коэффициентов уравнения парной линейной регрессии.

5. ЗАДАЧИ ОПТИМИЗАЦИИ

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

оптимизации и планирования капиталовложений.

Экономические вопросы, возникающие в производстве при проектировании, управлении и изготовлении, никогда не менялись и меняться, в сторону упрощения не будут. Это вопросы: что, каких размеров, из чего, кому, где, когда, сколько, в какой последовательности и т.п. Совершенствование технологий производства приводит к увеличению количества таких вопросов, и плата за ошибку при принятии решений неимоверно возрастает. Например, допустим, что при интуитивном распределении людей на работы возможность их использования по сравнению с оптимальным вариантом ухудшается всего на 3%. Если на предприятии 40 работающих, то такая ошибка привела бы к недоиспользованию одного человека. Но, если принять, что в производстве задействовано 500 000 человек, то такая ошибка аналогична снижению трудовых ресурсов на 15 000. Поэтому, чтобы избежать подобных ошибок, необходимо обоснованно выбирать критерии оптимизации и после получения решения всесторонне его проанализировать.

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

39