Задача 2. Составление пищевого рациона
Формулировка задачи
Прежде чем обращаться к инструменту Поиск решения, нужно проанализировать задачу и построить математическую модель. Для построения модели необходимо:
а) определить, каковы переменные модели;
б) выбрать целевую функцию;
в) задать ограничения, которым должны удовлетворять переменные.
При подготовке рабочего листа к решению задачи нужно:
1. отвести диапазон ячеек для хранения переменных величин;
2. в отдельную ячейку ввести функцию цели. Функция цели всегда зависит от переменных, поэтому в ячейке с целевой функцией будут использованы ссылки на ячейки, где хранятся переменные;
3. подготовить значения и формулы для задания ограничений. Поскольку ограничения накладываются на переменные, то в формулах для задания ограничений будут использованы ссылки на ячейки, где хранятся переменные.
Элементы диалогового окна Поиск решения
После построения математической модели можно обратиться к средству Поиск решения. Для этого нужно воспользоваться командой Данные – Поиск решения. (Если эта команда недоступна , она должна находиться в правом углу, то сначала нужно выполнить следующую последовательность действий: вызвать диалоговое окно Надстройки нажав кнопку Офис в левом верхнем углу окна в 2007 версии и пункт Файл в 2010, далее Параметры – Надстройки , затем внизу окна Управление – Надстройки Excel – Перейти…и установить флажок Поиск решения.) На экране появится окно диалога Поиск решения (рис. 1).
Рис.1 Окно Поиск решения.
В поле Установить целевую нужно указать ссылку на ячейку с целевой функцией. Если перед вызовом инструмента Поиск решения выделить ячейку с целевой функцией (рекомендуется), то это поле уже будет заполнено.
Ниже расположены элементы управления - переключатели, - позволяющие задать, какое значение целевой функции должно быть достигнуто при решении задачи: минимальное, максимальное или некоторое конкретное значение.
Поле Изменяя ячейки позволяет задать диапазон, в котором располагаются неизвестные величины, влияющие на целевую функцию.
Список Ограничения представляет все ограничения, накладывающиеся на условие кнопкой Добавить. На экране появится диалоговое окно (рис. 2).
Рис.2. Окно диалога для задания ограничений
Поле Ссылка на ячейку появившегося окна предназначено для указания ссылки на ячейки, где хранятся переменные или формулы, используемые для задания ограничений. В поле Ограничение можно задать константу, ссылку на ячейки со значениями или формулами. Значения из полей Ссылка на ячейку и Ограничение сравниваются с помощью операции («>=», «<=», «=», «цел», «двоич»), которою можно выбрать из списка, расположенного между этими двумя полями.
Кнопка Добавить позволит задать несколько ограничений, кнопка ОК добавляет ограничение и закрывает это окно.
Кнопка Изменить диалогового окна Поиск решения позволяет модифицировать выделенное в списке Ограничения ограничение. При этом на экране появится диалоговое окно Добавление ограничение (рис. 2) с уже заполненными элементами управления.
Кнопка Удалить диалогового окна Поиск решения позволяет удалить выделенное ограничение. После того как все данные для инструмента Поиск решения будут заданы, следует воспользоваться кнопкой Выполнить. Если решение будет найдено, Excel выдаст об этом сообщение, и результаты расчета задачи будут размещены в соответствующие ячейки. Если решение не может быть найдено, то Excel также выдаст об этом сообщение.
Параметры поиска решения
Чтобы изменить параметры поиска решения исследуемой задачи, нужно воспользоваться кнопкой Параметры диалогового окна Поиск решения (рис. 1) для вызова окна диалога Параметры поиска решения (рис. 3).
Рис. 3. Задание параметров поиска решения
В табл. 1. приведено описание элементов этого окна. Следует отметить, что значения и состояния элементов управления, используемые по умолчанию обычно достаточны для решения большинства задач.
Таблица 1.Параметры поиска решения
| Название | Описание | ||
| Поле Максимальное время | Определяет время, отпускаемое на решение задачи | ||
| Поле Предельное число итераций | Позволяет ограничить число промежуточных вычислений | ||
| Поле Относительная погрешность | Задает точность выполнения ограничений. Чем ближе значение к 1, тем ниже точность | ||
| Поле Допустимое отклонение | Служит для указания значения отклонения от оптимального решения (используется в задачах с целочисленными ограничениями) | ||
| Поле Сходимость | Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного вполе Сходимость, поиск прекращается. Сходимость применяется только к нелинейным задачам | ||
| Флажок Линейная модель | Используется для поиска решения задачt в которых отсутствуют нелинейные зависимости. Нелинейные зависимости возникают при умножении одних изменяемых ячеек (переменных величин) на другие | ||
| Флажок Автоматическое масштабирование | Позволяет включить автоматическую нормализацию входных и выходных значении, качественно различающихся по величине, - например, минимизация расходов в процентах по отношению к стоимостям, представленным е тысячах рублей | ||
| Флажок Показывать результаты итераций | Может быть использован для просмотра процесса нахождения решения | ||
| Группы Оценки, Разности, Метод поиска | Служат для выбора метода экстраполяции» метода численного дифференцирования и алгоритма оптимизации соответственно | ||
Задача 1. План выгодного производства
Предположим, что мы решили производить несколько видов конфет. Назовем их условно "A", "B" и "C". Известно, что реализация 10 килограмм конфет "А" дает прибыль 90 р., "В" - 100 р. и "С" - 160 р.
Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже:
Таблица1
| Сырье | Нормы расхода сырья | Запас сырья | ||
| А | В | С | ||
| Какао | ||||
| Сахар | ||||
| Наполнитель | ||||
| Прибыль |
Введите исходные данные и формулы в электронную таблицу, как указано ниже:
Рис.4
В меню Сервис активизируйте команду Поиск решения и опишите его параметры, как указано ниже:
Рис. 5
Не забудьте указать в Параметрах на Линейность модели.
Запустите Поиск решения. Если Вы сделали все верно, то решение будет таким, как на рис. 6:
Рис. 6
Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет "В" и 200 кг конфет "С". Конфеты "А" производить не стоит. Полученная Вами прибыль составит 4000 р.
Задача 2. Составление пищевого рациона
Небольшое сельскохозяйственное предприятие производит откорм уток.
При этом есть реальная возможность применять два вида кормов - Корм1 и Корм2.
Стоимость 1 кг: Корма1 - 0,8 руб. и Корма2 - 1 руб.
Каждый корм в своем составе содержит жизненно важные для птиц питательные вещества В1, В2 и В3, но в разных пропорциях. Эти пропорции приведены в таблице 14.
Таблица 2.
Количество единиц питательных веществ в 1 кг корма:
| Питательные вещества | Корм1 | Корм2 |
| В1 | ||
| В2 | ||
| В3 |
Каждая птица в ежедневном рационе должна получать не менее:
9 единиц питательного вещества В1,
8 единиц вещества В2 и
12 единиц вещества В3.
Требуется составить минимальный по стоимости дневной рацион птицы, состоящий из Корма1 и Корма2, но так, чтобы заданные условия по содержанию в нем питательных веществ были выполнены.