6.2 Пример постановки задачи для «Поиска решения»

Решим задачу производственного планирования, поставленную в разделе 1.1, с помощью «Поиска решения».

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

Для этого необходимо, во-первых, решить, где именно на листе электронной таблицы будут находиться переменные задачи. Отведем для них, например, ячейки В6 (для x₁) и С6 (для х₂). Вначале можно оставить эти ячейки пустыми, а можно ввести в них какое-нибудь число (на всякий случай, чтобы быть уверенными, что в них нет невидимого текста). Например, введем в них 0.

Слева от этого диапазона – в А6 – введем текст «Производство карамели». Сверху в В5 введем текст ««Снежинка», т», и в С5 – ««Яблочная, т». Результат приведен в таблице 26. Это делается для того, чтобы переменные получили правильные названия. «Поиск решения» впоследствии составит имя ячейки из двух частей - из содержания ближайших к данной ячейке слева и сверху ячеек, содержание которых начинается с текстового символа (воспринимаемых Excel как текст). Если таких ячеек нет, имя ячейки может отсутствовать или состоять из одной части. Таким образом, переменная x₁получит имя «Производство карамели «Снежинка», т», а переменнаяx₂- «Производство карамели «Яблочная», т».

Затем подготовим ограничения. Для удобства ввода формул, запишем в диапазоне В1:С4 коэффициенты в левых частях ограничений и в целевой функции, как показано в таблице 26. Затем в ячейку В8 введем формулу =СУММПРОИЗВ(B1:C1;B$6:C$6). Поскольку в ячейках B1:C1 находятся числа 0,8 и 0,5, а в B6:C6 – переменные, то в результате в ячейке В8 будет находиться левая часть первого ограничения, т.е. (0,8х₁+ 0,5х₂).

Ссылки на строку изменяемых ячеек сделаны абсолютными (со знаком «$») для того, чтобы удобно было ввести остальные формулы. А именно, скопируем формулу в В8 на ячейки B9:В11. В результате копирования на ячейки с переменными (B6:C6) будут поочередно умножаться коэффициенты второго, третьего ограничения и целевой функции. Например, в В11 появится формула =СУММПРОИЗВ(B4:C4;B$6:C$6), т.е. (108х₁+ 140х₂). Разумеется, при нулевых значениях переменных результатом вычислений по всем этим формулам будет 0. Если в B6:C6 находятся другие значения, не нулевые, то и результат вычислений будет другим.

Затем расположим введенные формулы в одну строку (для удобства оформления). Для этого формулу в В9 перенесем в С8, а формулу в В10 – в D8^*. Ячейки В9 и В10 в результате переноса останутся пустыми. Теперь формулы для левых частей ограничений находятся в диапазоне В8:D8.

Чтобы ограничения получили правильные имена, слева от этого диапазона (в А8) введем текст «Расход»; а сверху в В7 – «сахарного песка, т», в С7 – «патоки, т», в D7 – «фруктового пюре, т».

В ячейки В9:D9 введем свободные члены ограничений – 800, 600 и 120.

В ячейке В11 осталась формула для целевой функции. Этой ячейке тоже надо дать имя. Для этого в А11 введем текст «Прибыль от производства», а в В10 – «карамели, руб.».

Подготовленные данные представлены в таблице 26.

Таблица 26 – Исходные данные для «Поиска решения»

	А	B	C	D
1	Коэффициенты	0,8	0,5
2		0,2	0,4
3		0,01	0,1
4		108	140
5		«Снежинка», т	«Яблочная», т
6	Производство карамели	0	0
7		сахарного песка, т	патоки, т	фруктового пюре, т
8	Расход	0	0	0
9		800	600	120
10		карамели, руб.
11	Прибыль от производства	0

В диалоговое окно «Поиска» данные здесь следует ввести так, как показано на рисунке 30.

Рисунок 30 – Пример заполнения диалогового окна «Поиск решения»

Можно вводить ограничения и по отдельности. Тогда вид поля «Ограничения» будет другим (представлен на рисунке 31). В случае, когда знаки в ограничениях разные, ограничения никогда не удастся ввести в одну строчку. Однако, если они одинаковые, то лучше пользоваться вариантом, представленным на рисунке 30.

Рисунок 31 – Поле «Ограничения» в диалоговом окне «Поиск решения» при вводе ограничений по отдельности