Формулировка задания

А, вот оно и задание. Очень простое, хотя «с непривычки» может показаться и сложным. Но трудности ограничиваются только освоением инструмента Excel, называемого поиском решения.

Решить три задачи оптимизации по следующим темам.

1 Планирование производства

2 Транспортная задача

3 Планирование персонала

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

2.2 Собственный вариант задачи «Планирование производства»

О таком можно только мечтать: сами себе выбираем что будем делать. Как в супермаркете: хотим берём, не хотим не берём.

Собственный вариант задачи «Планирование производства» создаётся на основе варианта образца путём следующих возможных изменений:

1) изменения значений констант;

2) изменения состава комплектующих деталей и производимых изделий;

3) изменение целевой функции – вместо прибыли использовать получаемый доход; для этого предусмотреть дополнительный столбец констант со значениями цен комплектующих деталей.

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

2.3 Собственный вариант «Транспортной задачи»

В этой задаче возможных изменений не много, они просты в реализации, но заметно отличают предлагаемый вариант от образца.

Собственный вариант «Транспортной задачи» создаётся из варианта образца путём следующих возможных изменений:

1) изменения значений констант;

2) изменения состава заводов-производителей;

3) изменения состава складов-получателей.

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

2.4 Собственный вариант задачи «Планирование персонала»

Эта задача очень проста. Для неё трудно придумать возможные направления «удаления» от образца. Изменение целевой функции кажется сложным, но требует только знания стандартной функции МАКС().

Собственный вариант задачи «Планирование персонала» создаётся из варианта образца путём следующих возможных изменений:

1) изменения значений констант;

2) изменение целевой функции – вместо «Общая недельная зарплата» использовать «Максимальное количество лишних работников».

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

3 Использование надстройки «Поиск решения» Excel для решения задач оптимизации

3.1 Функциональность надстройки «Поиск решения»

Эта надстройка предназначена для решения задач оптимизации.

«Поиск решения» является частью блока задач, который иногда называют анализ «что-если». Процедура поиска решения позволяет найти оптимальные значения искомых переменных, обеспечивая экстремум или заданное значение целевой функции.

При этом выражение (формула) целевой функции размещается в одной из ячеек таблицы Excel, которая получает название «целевая ячейка». Напомним, что в русскоязычной документации и литературе сложилась традиция называть математические выражения формулами.

В качестве искомых переменных выступают ячейки таблицы, называемые влияющими и/или изменяемыми ячейками. Естественно, ссылки на эти ячейки содержатся в формуле целевой функции (мы же ещё не забыли, что аргументами целевой функции являются именно искомые переменные).

Каким образом представляются ограничения? В ячейках той же таблицы размещаются функции ограничений. Содержимым этих ячеек являются формулы, содержащие ссылки на влияющие ячейки, т. е. на ячейки всё тех же искомых переменных. Ограничения формируется с помощью кнопок «Добавить», «Изменить», «Удалить» окна «Поиск решения». При нажатии на кнопку «Добавить» появляется диалоговое окно «Добавление ограничения», с помощью которого и устанавливаются отношения между функциями ограничений и константами задачи. Формируемые отношения отображаются в списке с надписью «Ограничения:».

А константы задачи? Они тоже размещаются в своих ячейках, и обычно содержат только числа. Иногда применяются константные выражения (опять же формулы), но это только при сложной структуре параметров предметной области. ссылки на ячейки констант задачи содержатся и в формуле целевой функции, и в формулах функций ограничений.

Процедура поиска решения целенаправленно перебирает значения искомых переменных (влияющих ячеек), отыскивая набор аргументов целевой функции, при котором она экстремальна или имеет значение близкое к требуемому значению.

3.2 Начальные значения искомых переменных – специфика
процедуры поиска решения

Очень многие численные методы решения задач требуют указания начальных значений искомых величин.

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

В иностранной литературе начальные значения искомых переменных имеют образное название – Guess Values – предполагаемые (угаданные) значения. Оно очень хорошо передаёт смысл начальных значений искомых переменных.

Естественно, возникает вопрос: а как задавать начальные значения искомых переменных? К счастью современные алгоритмы поиска решения настолько хороши, что практически снимают этот вопрос, поскольку допускают произвольные значения искомых переменных. Ну, конечно, здравый-то смысл нарушать не следует. А если подходить серьёзно, то начальные значения следует подбирать таким образом, чтобы ограничения задачи были выполнены, либо полностью, либо с небольшими нарушениями. Метод же здесь единственный – проб и ошибок. Как надоест пробовать, так можно и остановиться, даже если не все ограничения выполнены.

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

3.3 Способ использования файла Smpls.xls с образцами решения
задач

Способ проще некуда: открываешь книгу Excel, изучаешь элементы задачи и решаешь её.

Прежде всего, рекомендуем создать копию файла Smpls.xls, и работать именно с копией, – чтобы иметь возможность «начать сначала», в случае существенных изменений содержимого файла в процессе вычислений.

Для изучения образцов достаточно открыть файл Smpls.xls, в книге которого содержатся листы с примерами задач по указанным выше темам: «Планирование производства», «Транспортная задача», «Планирование персонала». В примерах уже содержатся и целевая ячейка, и влияющие (изменяемые) ячейки, и ячейки ограничений.

Вам остаётся только выбирать команду «Поиск решения» (в Excel 2003 это в меню «Сервис») и в появившемся окне нажимать на кнопку выполнить.

Все элементы окна «Поиск решения» просты по смыслу и легки в использовании (кроме кнопки «Предположить», но на неё не обращайте внимания).

3.4 Элементы окна «Поиск решения»