Поддержки принятия решений «Поиск решения»

 

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

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

Математическая суть задачи состоит в решении уравнения f(x1, з2,…хn)=a, где функция f(x) описывается заданной формулой, х1, х2, …хn – искомые параметры, а – требуемый результат.

То есть, в отличие от «Подбора параметра» «Поиск решения» - это средство нахождения желаемого решения за счет изменения нескольких параметров.

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

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

Пример применения «Поиска решения»

1. Создадим на листе «Поиск решения» таблицы согласно Приложению 2. Исходные данные занимают на листе строки с 1 по 29.

2. В ячейку С33 введем целевую функцию (формулу определения прибыли в краткосрочный период), Приложение 2.

3. Для упрощения ввода ограничений при использовании Поиска решения введем формулы, Приложение 2:

3.1. В ячейки B36, C36, D36 введем формулы для подсчета затрат производственных мощностей, требуемых для выполнения производственной программы, т.е. времени, необходимого для изготовления искомого объема каждого вида продукции.

3.2. В ячейку В37 введем формулу для подсчета рабочего времени персонала, требуемого для выполнения производственной программы.

3.3. В ячейку В38 введем формулу для определения количества сырья, необходимого для выполнения производственной программы.

4. Выберем вкладку Данные - Поиск решения.

Если кнопка Поиск решения отсутствует, то нужно выбрать вкладку Файл- Параметры – Надстройки – кнопка Перейти – установить флажок Поиск решения.

5. В диалоговом окне «Параметры поиска решения» в поле «Оптимизировать целевую ячейку» введем адрес ячейки С33, содержащей формулу определения прибыли (рис. 12).

Рис.12. Диалоговое окно «Параметры поиска решения»

6. Найдем максимальное значение прибыли, возможное в заданных производственных условиях. Для этого включим флажок «максимум».

Если требуется найти заданное значение прибыли, то в окне «Поиск решения» в поле «Значения» нужно ввести конкретное значение прибыли.

7. В поле «Изменяя ячейки переменных» укажем диапазон ячеек, в которых должен быть найден объем выпускаемой продукции. Это ячейки Е6, Е7, Е8.

8. Введем ограничения. Например, для ввода ограничения на трудовые ресурсы выполним следующие действия:

- щелкнем по кнопке Добавить. Появится окно «Добавление ограничения» (рис. 13).

- в поле «Ссылка на ячейку» введем адрес ячейки, в которой подсчитывается рабочее время персонала, требуемое для выполнения производственной программы. Это ячейка В37.

- выберем нужный знак сравнения. В данном случае это «меньше или равно».

- в поле «Ограничение» укажем адрес ячейки, в которой находится ограничивающее число. В данном случае это ячейка В23, в которую введен фонд времени персонала. Таким образом, мы задали условие, что время, требуемое персоналу для выполнения всего объема производства, не может превышать фонд рабочего времени персонала.

Рис. 13. Диалоговое окно «Добавление ограничения»

- щелкнем по кнопке Добавить. Введем следующее ограничение. После ввода всех ограничений щелкнем по кнопке ОК.

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

Ограничения на производственные мощности:

$B$36<=$F$14; $C$36<=$F$15; $D$36<=$F$16

Ограничение на сырье:

$B$38<=$E$29+$F$29

Ограничения на выпускаемую продукцию:

$E$6<=$C$14; $E$6>=$B$14; $E$7<=$C$15; $E$7>=$B$15;

$E$8<=$C$16; $E$8>=$B$16

Если объем выпускаемой продукции может измеряться только в целых числах (например, столы, стулья), то необходимо ввести соответствующие ограничения:

$E$6=целое; $E$7=целое; $E$8=целое

Если потребуется добавить, изменить или удалить какое-либо ограничение, то нужно использовать соответствующие кнопки, расположенные рядом с полем «Ограничения».

Рис. 14. Окно «Параметры поиска решения» после ввода ограничений

 

9. После ввода всех ограничений окно «Поиск решения» примет вид, представленный на рис. 14.

10. Нажмем кнопку Найти решение. После выполнения поиска решения на экран будет выведено диалоговое окно «Результаты поиска решения» (рис. 15).

Рис. 15. Диалоговое окно «Результаты поиска решения»

11. Установим в этом окне флажок «Сохранить найденное решение». При этом в изменяемых ячейках (Е6:Е8) будут выведены найденные значения объема производства.

12. Создадим отчет по результатам поиска решения. Для этого в окне «Результаты поиска решения» в поле «Отчеты» выберем Результаты. Нажмем кнопку ОК. В рабочей книге появится лист с именем «Отчет о результатах».

 

3.2. Задание для самостоятельного выполнения № 3. Нахождение вариантов оптимальной производственной программы по выпуску продукции с использованием «Поиска решения»

Назовите лист рабочей книги «Поиск решения» и выполните на нем задания в соответствии со своим вариантом (стр. 24 - 51).

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

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

Если поиск решения заканчивается сообщением «Поиск не может найти подходящего решения», измените либо значение прибыли либо исходные данные.

Получите следующие распечатки:

1) лист «Поиск решения» с исходными данными и расчетными формулами в режиме формул с координатной сеткой и заголовками строк и столбцов (Распечатка 10);

2) после выполнения пункта 1 данного задания выполните распечатку в режиме чисел (Распечатка 11) и отчет «Результаты» (Распечатка 12).

3) после выполнения пункта 2 задания выполните распечатку в режиме чисел (Распечатка 13) и отчет «Результаты» (Распечатка 14).

 

 

Варианты для выполнения задания № 3