Поиск решения.
Большинство задач, решаемых методом подбора параметра, можно решить аналитически, причём точно, а не приближенно. Более интересен другой инструмент, имеющийся в Excel, позволяющий накладывать ограничения на параметры, применять в качестве параметров области, решать задачи со многими неизвестными - поиск решения (СЕРВИС – ПОИСК РЕШЕНИЯ). При этом используется метод линейной оптимизации.
Если пункт меню ПОИСК РЕШЕНИЯ отсутствует в списке СЕРВИС, то его можно установить через СЕРВИС – НАСТРОЙКИ. В открывшемся диалоге следует поставить галочку против ПОИСК РЕШЕНИЯ.
С помощью этого инструмента можно решать сложные экономические задачи. В комплекте Excel поставляется файл Solvsamp.xls, демонстрирующий возможности метода. Мы же разберём простейшие задачи оптимизации.
Так же как и в методе «Подбор параметра», результат зависит от начальных условий, количества итераций и относительной погрешности. Но «Поиск решения» позволяет более детально настраивать процесс оптимизации с помощью бланка ПАРАМЕТРЫ ПОИСКА РЕШЕНИЯ (рис. 57), который можно вызвать из бланка ПОИСК РЕШЕНИЯ через кнопку ПАРАМЕТРЫ.
Так же, как и при подборе параметра данные в целевой ячейке должны быть связаны с изменяемыми данными формулами.
Рис. 57
Упражнение 12. «Закупки».
Задание. Необходимо произвести закупки товаров со склада для магазина на определённую сумму. Известна цена каждого товара. Даны ограничения на максимальное и минимальное количество закупаемого товара (табл. 17). Определить оптимальное количество закупаемых товаров.
Порядок выполнения.
Выполнить табл. 17 . В области F2:F6 (столбец сумма) сформировать выражение по образцу: =E2*B2 (=цена*количество).
В ячейке F7 составить формулу для суммы: =СУММ(F2:F6).
Установить курсор в ячейке F7. Вызвать бланк поиска решения (СЕРВИС – ПОИСК РЕШЕНИЯ) . Заполнить бланк как на рис. 58.
Условия в области «Ограничения» создать с помощью кнопки «Добавить», последовательно формируя условия и щёлкая по кнопке «Добавить», затем ОК (рис. 59 ).
Таблица 17
|
A
|
B
|
C
|
D
|
Е
|
F
|
|
Товар
|
Цена
|
Мин
|
Макс
|
Количество
|
Сумма
|
|
Тетрадь
|
10
|
10
|
70
|
|
|
|
Ручка
|
20
|
20
|
70
|
|
|
|
Карандаш
|
5
|
30
|
100
|
|
|
|
Пенал
|
50
|
3
|
10
|
|
|
|
Учебник
|
70
|
5
|
30
|
|
|
|
|
|
|
|
Всего в итоге
|
|
|
|
|
|
|
Всего задано
|
5000
|
Рис. 58.
 
Рис. 59.
Нажать на кнопку «Выполнить». В результате должна получиться табл. 18.
Таблица 18
Товар
|
Цена
|
Мин
|
Макс
|
Кол
|
Сумма
|
Тетрадь
|
10
|
10
|
70
|
70
|
700
|
Ручка
|
20
|
20
|
70
|
70
|
1400
|
Карандаш
|
5
|
30
|
100
|
60
|
300
|
Стиплер
|
50
|
3
|
10
|
10
|
500
|
Учебник
|
70
|
5
|
30
|
30
|
2100
|
|
|
|
|
Итого
|
5000
|
|
|
|
|
Всего
|
5000
|
Самостоятельная работа 16.
Задание. Требуется расфасовать D кг сыпучего материала по контейнерам, бочкам и канистрам. Дана вместимость каждого из видов хранения. (табл. 19). Определить, сколько контейнеров, бочек и канистр потребуется для расфасовки всего сыпучего материала. Исходные значения даны по вариантам в табл. 20.
Таблица 19
Наименование
|
Вместимость
|
Количество
|
Объём
|
Контейнер
|
А
|
0
|
0
|
Бочка
|
В
|
0
|
0
|
Канистра
|
С
|
0
|
0
|
|
|
Всего
|
0
|
|
|
Исходное количество
|
N
|
Таблица 20
Вариант
|
А
|
В
|
С
|
D
|
|
270
|
130
|
90
|
1400
|
|
200
|
100
|
50
|
1600
|
|
100
|
120
|
20
|
1000
|
|
60
|
40
|
5
|
500
|
|
160
|
150
|
50
|
2000
| Упражнение 13. «Предприятие» .
Задание. Предприятие производит два типа изделий: А и В. На производство изделия А расходуется 4 единицы условного сырья. На изделие Б расходуется 7 единиц условного сырья. На одну рабочую смену предприятие снабжается 22 единицами условного сырья.
Для изготовления изделия А требуется 8 рабочих, а для изготовления изделия Б требуется 5 рабочих. Общее количество рабочих на предприятии составляет 30 человек.
Транспортные расходы на перевозку изделия А составляют 3 условные единицы на перевозку изделия Б составляют 4 условные единицы. Общие транспортные расходы в течение рабочего дня не должны превышать 20 условных единиц.
Прибыль от реализации одного экземпляра продукта составляет 7 денежных единиц, прибыль от реализации одного экземпляра продукта Б составляет 6 денежных единиц.
Рассчитать оптимальные количества изделия А и Б, производимых за одну рабочую смену, для получения предприятием максимальной прибыли.
Порядок выполнения.
Составить таблицу и связать данные как на рис. 60.
Вызвать бланк «Поиск решения» и заполнить бланк в соответствии с рис. 61.
  
Рис. 60
Рис. 61
Результат выполнения поиска решения должен быть как в табл. 21.
Таблица 21
|
Изделие А
|
Изделие В
|
Всего
|
Лимит
|
Сырьё
|
4
|
7
|
19
|
22
|
Люди
|
8
|
5
|
29
|
30
|
Транспорт
|
3
|
4
|
13
|
20
|
Требуется произвести
|
3
|
1
|
|
|
Цена продажи
|
7
|
6
|
|
|
Всего затрат
|
21
|
6
|
|
|
Итог
|
27
|
|
|
| Самостоятельная работа 17.
Задание. Результат поиска решения в упражнении «Предприятие» оказался не совсем удачным, так как запланированные средства не полностью реализованы. Сырьё берётся со склада, поэтому можно снять ограничение на него. Увеличим количество людей до 34. Изменить условие поиска максимальной прибыли производства таким образом, чтобы были заняты все рабочие, и в смену производилось не менее одного изделия каждого вида.
|
