Инструмент «Поиск Решения»

В состав MS Excel входит мощное инструментальное средство – Решатель. С помощью этого средства можно решать задачи нелинейного программирования. В частных случаях с помощью Решателя может быть получено решение функционального уравнения, системы линейных уравнений, найден максимум или минимум функции нескольких переменных.

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

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

Искомые переменные – ячейки рабочего листа Excel называются регулируемыми ячейками. На регулируемые ячейки можно наложить дополнительные ограничения, например положительности или целочисленности значений.

Решатель может быть применен и для решения оптимизационных задач.

Чтобы воспользоваться Решателем, его нужно установить, т.к. по умолчанию в стандартной комплектации MS Excel, он не установлен.

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

После завершения установки на панели меню «Данные» появится пункт «Поиск решения».

Рассмотрим работу решателя на примере

Пример.

Имеется 400 кг чугуна СЧ-20, есть заказ на изготовление отливок массой 1,7 кг, 13,2 кг и 0,4 кг. Прибыль от заготовок составляет 14, 29 и 12,5 рублей соответственно. Известно, что каждого вида заготовок не должно быть менее 20 и более 100.

Сколько каких заготовок выгоднее сделать:

· с позиции получения максимальной прибыли.

· с позиции максимального расхода имеющегося материала.

Решение.

Для облегчения выполнения расчета следует применить рабочий лист Excel.

1. На листе Лист1 создайте таблицу, соответствующую рис. 29. При этом в ячейки B10:B17 запишите очевидные расчетные формулы. Ячейки B4:B6 должны быть заполнены нулями.

Рис. 29. Подготовка таблицы для автоматизации выполнения расчетов

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

3. Для решения подобной задачи нужно определить 3 категории данных:

a. неизвестные данные, от которых зависит результат решения (в данном случае это количество отливок каждого типа) – изменяемые параметры;

b. цель решения задачи, т.е. то, к чему мы стремимся (в данном случае мы стремимся получить максимальную прибыль) – целевая функция;

c. ограничительные данные. Они определяются исходя из условия задачи или исходя из здравого смысла, например, очевидно, что нельзя сделать половину отливки, а, следовательно, этот параметр должен быть целочисленным. (В данном случае ограничениями будут: целочисленность количества изготовленных заготовок; исходя из условия задачи, количество заготовок должно входить в диапазон [20, 100]; количество израсходованного материала не должно превышать исходного количества материала).

4. Для решения этой задачи поиска максимума полученной прибыли применить Решатель. В электронной таблице ячейки В4:В6 будут играть роль изменяемых ячеек, а ячейка B16 – это ячейка с целевой функцией.

5. Выделите ячейку B16 с целевой функцией и выполните команду меню «Данные» → Поиск решения. Появится окно Поиск решения (рис. 30), в поле «Установить целевую ячейку» которого уже должна быть абсолютная ссылка на ячейку B16. Если же этой ссылки там не оказалось, то ее следует туда поместить. Для этого надо щелкнуть в поле «Установить целевую ячейку», чтобы установить там курсор. Затем нужно щелкнуть на ячейке B16, в которой содержится целевая функция.

Рис. 30. Окно Поиск решения

6. Проверьте, чтобы был включен переключатель, показывающий, что нужно найти максимум целевой функции.

7. Поместите в поле «Изменяя ячейки» диапазон изменяемых ячеек. Для этого поместите курсор в этом поле и выполните перемещение мыши при нажатой левой кнопке по изменяемым ячейкам (в рассматриваемом примере – это ячейки B6:B8).

8. Добавьте ограничения, которые имеют место в рассматриваемом примере (см. п. 2). Щелкните на кнопке «Добавить». В появившемся окне «Добавление ограничения» (рис. 3) в поле «Ссылка на ячейку» поместите ссылку на ячейку, на которую накладывается ограничение, в поле «Ограничение» поместите ссылку на ячейку, содержащей значение ограничения. Выберите нужный знак отношения между полями «Ссылка на ячейку»и«Ограничение».

Например, установим ограничение не превышения расхода материала:

Рис. 31. Добавление ограничения

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

В новом окне «Добавление ограничения» введите в поле «Ссылка на ячейку» диапазон B6:B8.

В поле, расположенном справа от поля «Ссылка на ячейку», раскройте список значений и выберите значение «цел». Окно «Добавление ограничения» должно выглядеть, как показывает рис. 32.

Рис. 32. Учет целочисленности количества заготовок

10. Введите все оставшиеся ограничения.

11. Чтобы начать поиск решения, щелкните на кнопке «Выполнить» окна «Поиск решения». После окончания процесса поиска появится окно «Результаты поиска».

12. В окне «Результаты поиска» включите переключатель «Сохранитьнайденное решение» и закройте окно щелчком на кнопке ОК.

13. Обратите внимание на значения изменяемых ячеек (рис. 33), которые они приобрели после окончания поиска максимума целевой функции.

Рис. 33. Результат поиска максимума прибыли от изготовленных заготовок

14. Решите задачу, выбрав в качестве целевой функции расход материала. Проанализируйте полученные результаты.

Задания

1. Для изготовления трех видов изделий А, В и С используется токарное, фрезерное, шлифовальное и сварочное оборудование. Затраты времени на обработку одного изделия по каждому из типов оборудования приведены в таблице, там же указан общий фонд рабочего времени каждого из типов указанного оборудования, а также прибыль от реализации одного изделия каждого вида.

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

Решение:

Предположим, что будет изготовлено х₁ единиц изделий вида А, х₂ единиц изделий вида В и х3 единиц изделий вида С. Тогда для производства такого количества изделий потребуется затратить 2х₁+4х₂+5х₃ станко-часов фрезерного оборудования.

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

2х₁+4х₂+5х₃ ≤ 120

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

Прибыль от реализации

F = 10х₁+14х₂+12х₃

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

2. Сборочный узел содержит детали четырех типов:

Валик – 1 шт;

Вилка корданная – 2 штуки;

Болт – 4 шт;

Гайка – 4 шт.

Все детали изготавливаются на одном участке, данные по изготовлению сведены в таблицу:

* Период стойкости – время до полного износа инструмента.

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

Не нашли, что искали? Воспользуйтесь поиском по сайту: