651

Рис.2 – Окно «Анализ данных»

в диалоговом окне Регрессия (Рис.3) в поле “Входной интервал Y” ввести диапазон ячеек столбца Y с заголовком, в поле “Входной интервал X” ввести диапазон ячеек двух Х с заголовками

так как выделены заголовки столбцов, то установить флажок в Метки

ОК.

Рис.3 – Заполнение окна «Регрессия»

В результате получаем вывод итогов в виде таблиц (Рис.4).

Рис.4 – Результаты регрессионного анализа

В последней таблице во втором столбце содержатся коэффициенты уравнения регрессии а0, а1, а2.

Уравнение регрессии зависимости объема реализации от затрат на рекламу и индекса потребительских расходов имеет вид:

Y= - 1471,314 + 9,568 x1 + 15,754 x 2

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

Коэффициент детерминации R2 равен 0.859, он показывает долю вариации результативного признака под воздействием

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

Анализ влияния факторов на зависимую переменную по

модели

Коэффициент при X1 показывает, что при увеличении расходов на рекламу на 1 тыс. руб. объем реализации вырастет на

9,568 ц.

Коэффициент при X2 показывает, что при увеличении индекса потребительских расходов на 1 объем реализации вырастет на 15,754 ц.

Использование уравнения регрессии заключается в подстановке каких-либо значений факторов Х в уравнение.

Например, рассчитаем, каким ожидать объем реализации при затратах на рекламу Х1 =15 тыс.руб. и индексе потребительских расходов Х 2 = 110.

Подставляем Х1 =15 и Х 2 = 110 в уравнение регрессии:

Y= - 1471,314 + 9,568*15 + 15,754 *110 = 405,15 ц.

Библиографический список

1.Бадевиц З. Математическая оптимизация в сельском хозяйстве. Под ред.Р.Г.Кравченко. М.: Колос, 2002

2.Бараз В.Р. Корреляционно-регрессионный анализ связи показателей коммерческой деятельности с использованием программы Excel: учебное пособие. - Екатеринбург: ГOУ ВПO УГТУ-УПИ, 2005

3.Власов М.П., Шимко П.Д. Моделирование экономических процессов. Ростов н/Д.: Феникс ,2005

4.Глухов В.В, Медников М.Д., Коробко С.Б. Математические методы и модели для менеджмента. СПб.:Лань, 2000

5.Математическое моделирование экономических процессов в сельском хозяйстве / Под ред. А.М.Гатаулина. М., Изд-во: ИТК Гранит, 2009.

6.Орлова И.В., Половников В.А. Экономикоматематические методы и модели: компьютерное моделирование. Учеб. пособие. – М.: Вузовский учебник,

2007

7.Практикум по математическому моделированию экономических процессов в сельском хозяйстве / Под ред. А.Ф.Карпенко. М.: Финансы и статистика, 1995.

8.Экономико-математические методы и прикладные модели: Учеб. пособие для вузов / В.В. Федосеев, А.Н. Гармаш, И.В. Орлова и др.; под ред. В.В. Федосеева. – 2-е изд., перераб. и доп. – М.: ЮНИТИ-ДАНА, 2005.

ПРИЛОЖЕНИЯ

Приложение 1

Решение задачи линейного программирования с помощью Поиска решения в среде EXCEL

(на примере задачи оптимизации ассортимента продукции хлебокомбината).

Для решения задачи необходимо:

1.Создать форму для ввода условий задачи.

2.Указать адреса ячеек, в которые будет помещен результат решения (изменяемые ячейки).

3.Ввести исходные данные.

4.Ввести зависимость для целевой функции.

5.Ввести зависимости для ограничений.

6.Указать назначение целевой функции (установить целевую

ячейку).

7.Ввести ограничения.

8.Ввести параметры для решения задачи линейного программирования.

1. Подготовим форму для ввода условий (см. рис 1).

Рисунок 1 Форма для ввода условий.

2. В нашей задаче оптимальные значения вектора Х = (Х1, Х2, Х3, Х4, Х5, Х6, Х7) (другими словами – ответ задачи) будут помещены в ячейках D4:M4, оптимальное значение целевой функции – в ячейку

N15.

3. Введите исходные данные модели по вашему варианту в созданную форму. Результат ввода по данным примера (вариант 0), показан на рисунке 2.

Рисунок 2 Введенные данные по варианту "0".

4. Введем зависимость для целевой функции (обозначим через

«М1» – «один щелчок левой кнопкой мыши»):

Курсор в ячейку N15.

Курсор на кнопку Мастер функций М1.

На экране диалоговое окно Мастер функций шаг 1 из 2.

Курсор в окно Категория на категорию Математические.

М1.

Курсов в окно Функции на СУММПРОИЗВ, М1.

В массив 1 ввести $D$4:$M$4 - (адреса ячеек во все диалоговые окна удобно вводить не с клавиатуры, а протаскивая мышь по заданному интервалу ячеек. Знак $ обязателен, ввести с клавиатуры).

В массив 2 ввести D15:M15. ОК. (рис.3).

Таким образом, мы ввели функцию в ячейку N15, где должен появится «0».

Рисунок 3 Пример ввода функции.

5. Введем ту же функциональную зависимость для левых частей ограничений:

Скопируем формулу из ячейки N15 в ячейки - Объемов левой части ограничений;

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

На этом ввод зависимостей закончен.

Запуск «Поиска решения» в MS Excel 2007

В верхнем левом углу Листа щелкнуть по кнопке ▼ (настройка панели быстрого доступа) → выбрать команду «Другие команды» → «Перейти» → галочку в «Поиск решения» → ОК →выбрать пункт меню «Данные» → в верхнем правом углу меню выбрать команду «Поиск решения» → ОК (Рис.2)

6.Назначение целевой функции (установить целевую ячейку).

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

Ввести адрес $N$15, с помощью мышки;

Ввести направление целевой функции: Максимальному

значению.

Ввести адреса искомых переменных:

Курсор в поле «Изменяя ячейки»;

Ввести адреса $D$4:$M$4, с помощью мышки.

7.Ввод ограничений.

Нажать на кнопку «Добавить». Появится диалоговое окно

-Добавление ограничения Рис 4.

Рисунок 4 Добавление ограничения.

В поле «Ссылка на ячейку» ввести адрес $N$6.

Ввести знак ограничения (<=).

Курсор в правое окно.

Ввести адрес $P$6.

Добавить. На экране опять диалоговое окно Добавление ограничения.

Ввести, внимательно, остальные ограничения. (Более быстрый и рациональный способ введения зависимостей – массивами, протаскивая мышь по нужным ячейкам). Для левой части (ограничений по сырью, знак “<=”) массив: $N$6:$N$12, правой части: $P$6:$P$12 (рис 5). По остальным ограничениям (типа “>=”)

получим массив $N$13:$N$14 >= $P$13:$P$14.

Рисунок 5 Добавление ограничения массивом.

Для решения задачи, достаточно, введенных ограничений, но добавим еще одно: $N$15 = $N$15, его необходимость вызвана тем, что расчетное значение целевой функции (кое, нам - важно) не отображается в отчете по Устойчивости … После данного приема, значение явится в виде последнего ограничения, что, затем можно увидеть во второй таблице отчета по Устойчивости.

 После ввода последнего ограничения ОК.

На экране диалоговое окно Поиск решения с введенными условиями, рисунок 6.

Рисунок 6 Диалоговое окно Поиск решения.

8. Ввод параметров для решения задачи линейного программирования (рис. 7).

Открыть окно Параметры поиска решения;

Установить флажок Линейная модель, что обеспечит применение симплекс-метода;

Установить флажок Неотрицательные значения;

ОК. (На экране диалоговое окно Поиска решения);

Выполнить.(На экране диалоговое окно Результаты поиска решения рис. 8).

Рисунок 7 Параметры поиска решения