РОСТ(известные значения_Y; известные значения_X; новые_значения_X; константа).

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

Воспользуемся данными, размещенными в ячейках А3:С21 рабочего листа Excel. (см. рис. 1).

Рассчитаем экспоненциальную регрессионную однофакторную модель вида

y= b∙m^x,

для чего в ячейки H5:I9 введем функцию ЛГРФПРИБЛ в формате: =ЛГРФПРИБЛ(C6:C21;B6:B21;1;1) (см. рис.3).

Результатом работы функции является массив значений (ячейки H5:I9, рис. 3):

ячейки H5, I5 – коэффициенты уравнения регрессии m=1.0001 и b=4553.49;

ячейка H7 – коэффициент детерминированности R² =0.8501;

ячейка H8 – критерий Фишера F=79.42.

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

Y = 4553.49∙1.0001 ^x

Рис.3 – Расчет экспоненциальной однофакторной модели

Вывод:поскольку коэффициент детерминированности R²=0.8501 лежит в пределах 0,75 – 1, расчетное значение критерия Фишера 79,41 больше табличного (4.6), модель следует признать адекватной, и следовательно ее можно использовать для прогнозирования.

В ячейке H20 рассчитаем прогнозное значение среднедневной заработной платы при уровне среднедушевого прожиточного минимума в день, равном 4000 руб. (ячейка В22) по формуле =I5*H5^B22.

В ячейках H12 – H14 рассчитаем прогнозное значение среднедневной заработной платы с использованием функции РОСТ:

=РОСТ(C6:C21;B6:B21;B22:B24;1)

В ячейке H15 рассчитаем значение коэффициента корреляции R:

=КОРРЕЛ(C6:C21;B6:B21).

Полученные значения совпали c результатами, возвращенными функцией ЛГРФПРИБЛ.

Лекция 7

ПОИСК ОПТИМАЛЬНЫХ РЕШЕНИЙ

План лекции

· Задачи оптимизации

· Возможности ТП MS Excel для решения оптимизационных задач

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

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

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

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

Модель задачи линейного программирования должна иметь вполне определенный вид: требуется найти максимум (минимум) значения целевой функции L при переменных x₁, x_2…, x_n

(1)

при соблюдении линейных ограничений

(2)

Каждая из переменных не может принимать отрицательного значения, то есть

(3)

В выражениях (5) и (6) коэффициенты a_ij и c_j при переменных и величины b_i - постоянные числа.

Решение системы уравнений (2) при выполнении условия (3) называется допустимым решением задачи линейного программирования.

Оптимальное решение – это допустимое решение, удовлетворяющее условию (5). Для нахождения оптимального решения следует иметь множество допустимых решений., в которых максимизируемая (минимизируемая) функция F(X) является линейной, а ограничения Gзадаются линейными неравенствами.

К типовым оптимизационным задачам линейного программирования можно отнести:

§ оптимизация производственной программы

§ оптимизация раскроя материалов

§ оптимизация состава смеси

§ оптимизация перевозок

§ оптимизация финансовых показателей

§ оптимизация штатного расписания и т.п.

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