Алгоритм решения типового примера

Для того чтобы решать задачи линейного программирования, в Excel есть надстройка, которая называется «Поиск решения». Для начала надо проверить подключена ли она. Если у вас Excel 2003, то идёте в меню «Сервис», дальше в «Надстройки…». Откроется список, где рядом с «Поиском решения» должна стоять галочка, как на картинке.

Если галочки нет, то ставите её сами и нажмете «ОК» - надстройка установится сама. После этого, в меню «Сервис» появляется пункт «Поиск решения», который нам и нужен. Если у вас Excel 2007, то нажмёте на кнопку главного меню вверху слева, нажмёте внизу кнопку «Параметры Excel»,

справа выбираете закладку «Надстройки»

и смотрите, какие из них уже активны. Если всё, как на картинке,

то ничего делать не надо. Если же нет, то снизу нажимаете «Перейти…»,

ставите галочку напротив «Поиска решения» и жмёте «ОК». После этого пункт «Поиск решения» появляется в меню «Данные».

Теперь нужно подготовить типовой задачи для вставки в Excel.

Когда в матрице есть элементы <= 0, то надо прибавить ко всей матрице одно такое число, чтобы все элементы стали > 0. Для наглядности покажем решение примера. Делаем в Excel табличку, куда записываем наши ограничения, и оставляем ячейки для иксов, суммы иксов и оптимальной стратегии для первого игрока p*. Иксов нам нужно столько, сколько у нас в матрице строк.

Дальше вставляем в ячейки иксов нули, а для их суммы пишем в ячейку формулу =СУММ(B1:B3), т.е. суммировать все ячейки в диапазоне от B1 до B3. Если у вас больше иксов, то просто замените B3 на ячейку последнего икса (например, для четырёх иксов =СУММ(B1:B4)). Чтобы автоматически считалась оптимальная стратегия p*, напишите в первой её ячейке формулу =(1/СУММ($B$1:$B$3))*B1 и растяните её вниз на столько ячеек, сколько у вас иксов, не забывая исправлять $B$3 на нужную, если у вас больше двух иксов. Ячейка здесь записывается со знаками $, чтобы при растягивании она не сбилась, а осталась такой же. Excel напишет ошибку деления на 0, но всё в порядке – иксы у нас пока и вправду нулевые.

Теперь нам нужно записать формулы ограничений. Ставим курсор в ячейку под первым столбиком ограничений и пишем туда =СУММПРОИЗВ($B$1:$B$3;D1:D3), меняя, если нужно, адреса ячеек с иксами и адреса с первым столбиком ограничений. Такая формула просуммирует все произведения иксов на нужные числа из матрицы. Теперь копируем эту формулу вправо на все столбцы.

Теперь всё готово, и можно искать решение. Вызываем меню «Поиск решения», открывается такое окно:

Целевой ячейкой устанавливаете ячейку с суммой иксов (у меня это $B$4). Переключатель ниже устанавливаете на «равной минимальному значению». Потом нажмёте кнопку «Предположить», и если вы вписали в иксы нули, то в окне автоматически появится диапазон ячеек иксов. Дальше вводим ограничения, всего их два: все иксы >= 0 и все ограничения >=1. Можно не выделять каждую клетку отдельно, а добавить весь диапазон для ограничений сразу, вот так:

Нажимаете «Добавить», записываете другое ограничение и нажмёте «ОК». Если вы всё сделали правильно, вы увидите примерно такое заполненное окно:

Всё готово, нажмёте «Выполнить». Если всё ок, то появится такое окно:

Можно нажать «ОК» и наслаждаться результатом – в ячейках иксов стоят нужные значения, а в ячейках p* нас ждёт уже посчитанная оптимальная стратегия для первого игрока! Это стратегия 3. Подставляя в формулу получаем ответ Y = 10 – 0,6 * (4 + 4)/2 = 7,6 (тыс.ед)

Оптимальную стратегию для второго игрока считать абсолютно так же – игроков должно быть по количеству столбцов, в окне решения надо указать уже «равной максимальному значению», а ограничения должны быть <= 1. Выглядит это примерно так:

Результат решения

Для второго игрока является оптимальной третья стратегия, что соответствует ответу.

Самостоятельная работа (N=5) к лабораторной работе №6

Борьба двух предприятий за рынок в регионе (N – номер варианта)

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

В зависимости от сделанного выбора компании могут установить цену реализации единицы продукции на уровне 25, 22, 19 и 16 условных единиц соответственно. Соотношение цен реализации и себестоимость представлены в таблице:

Вариант продвижения нового продукта	Цена реализации единицы продукции, у.е.	Полная себестоимость единицы продукции, у.е.
Компания А	Компания В
			21–0,1*N
			10+0,1*N
		10+0,1*N

N – номер варианта, предложенный преподавателем.

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

Y = 20 – 0,5*X,

где Y – количество продукции, которое будет реализовано в регионе (тыс. ед.), а X – средняя цена продукции компаний, у.е.

Значения долей продукции, реализованной компанией А, зависят от соотношения цен на продукцию компании А и компании В. Маркетинговое исследование позволило установить эту зависимость:

Цена реализации 1 ед. продукции, у.е.	Доля реализованной продукции компании А
Компания А	Компания В
		0,31
		0,33
		0,25
		0,2
		0,4
		0,35
		0,32
		0,28
		0,52
		0,48
		0,4
		0,35
		0,6
		0,58
		0,55
		0,5

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

2. Существуют ли варианты, которые компании заведомо не будут выбирать вследствие невыгодности?

3. Сколько продукции будет реализовано в ситуации равновесия? Какая компания получит больше прибыль в ситуации равновесия? Какая компания будет иметь большую долю рынка в ситуации равновесия? Дайте краткую экономическую интерпретацию результатов решения задачи.