Пример 2. Мебельное производство

Фирма производит две модели А и В сборных книжных полок.

Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.

Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В - 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.

Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В - 30 мин. в неделю можно использовать 160 ч машинного времени.

Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В - 120 руб. прибыли?

Порядок действий уже известен.

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

Запускаем Поиск решения и в диалоговом окне устанавливаем необходимые параметры

1. Целевая ячейка B12 содержит формулу для расчёта прибыли

2. Параметр оптимизации - максимум

3. Изменяемые ячейки B9:C9

4. Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D16); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D15). Здесь вместо ссылок на ячейки D15 и D16 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице

5. Нажимаем кнопку Найти решение (Выполнить) и после подтверждения получаем результат

Но даже если Вы правильно создали формулы и задали ограничения, результат может оказаться неожиданным. Например, при решении данной задачи Вы можете увидеть такой результат:

И это несмотря на то, что было задано ограничение целое. В таких случаях можно попробовать настроить параметры Поиска решения. Для этого в окне Поиск решения нажимаем кнопку Параметры и попадаем в одноимённое диалоговое окно

Первый из выделенных параметров отвечает за точность вычислений. Уменьшая его, можно добиться более точного результата, в нашем случае - целых значений. Второй из выделенных параметров (доступен, начиная с версии Excel 2010) даёт ответ на вопрос: как вообще могли получиться дробные результаты при ограничении целое? Оказывается Поиск решения это ограничение просто проигнорировал в соответствии с установленным флажком.

И последнее, на что следует обратить внимание, это выбор метода решения. Если задача достаточно сложная, то для достижения результата может потребоваться подобрать метод решения

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

Найти: x 1, x 2, …, xn такие, что F (x 1, x 2, …, xn) → (Max; Min; = Value) при ограничениях: G (x 1, x 2, …, xn) → (≤ Value; ≥ Value; = Value), где Value – это значение. Искомые переменные x 1, x 2, …, xn – ячейки рабочего листа – являются изменяемыми ячейками. Целевая функция F (x 1, x 2, …, xn) должна задаваться в виде формулы в ячейке рабочего листа. Эта формула может содержать функции, определённые пользователем, и должна зависеть (ссылаться) от изменяемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Возможен выбор одной из следующих задач:

1) найти максимум целевой функции;

2) найти минимум целевой функции;

3) добиться того чтобы целевая функция принимала фиксированное значение: F (x 1, x 2, …, xn)= a.

Функции G (x 1, x 2, …, xn) называются ограничениями. Их можно задать как в виде равенств, так и неравенств. На изменяемые ячейки можно наложить и другие дополнительные ограничения.

Для уточнения корня с помощью Поиска решения необходимо выполнить следующие действия:

1) в ячейку (например, I5) ввести текст x=;

2) в ячейку справа от I5 (K5) ввести значение начальной границы или (число) заданного отрезка;

3) в соседнюю ячейку снизу (I6) ввести текст f(x)=;

4) в расположенную справа от I6 ячейку (K6) ввести формулу для вычисления значений функции, в качестве которой использовать левую часть преобразованного уравнения (в нашем случае формула имеет вид =K5*TAN(K5)-1);

5) щёлкнуть мышью по ячейке с целевой функцией (K6);

6) открыть диалоговое окно Поиск решения выполнить следующие установки:

- в окне Установить целевую ячейку: щелчком мыши по ячейке K6 установить абсолютный адрес ячейки с целевой функцией ($K$6);

- установить переключатель варианта в положение "значению:" (используется значение по умолчанию – ноль);

- в окне Изменяя ячейки: щелчком мыши по ячейке K5 установить абсолютный адрес ячейки со значением x ($K$5);

- щёлкнуть мышью по кнопке Выполнить;

- в появившемся диалоговом окне Результаты поиска решения щёлкнуть по кнопке ОK, после чего полученное решение (уточненный корень) будет записано в изменяемую ячейку K5.

Пример оформления на рабочем листе:

Варианты задания

Вариант задания соответствует порядковому номеру студента в списке группы. Заданное уравнение привести к нормальному виду, то есть все, расположенное в правой части уравнения, перенести в левую половину и приравнять ее нулю. В дальнейшем левая часть уравнения и будет являться целевой функцией. Упростить целевую функцию насколько это возможно и найти первую производную от целевой функции. Уравнение решить каждым из рассмотренных способов и сравнить полученные результаты. Допустимое их различие не должно быть меньше ɛ=10^-6.

Для решения использовать Лист2. Из двух результатов решения выбрать более точный.