• 

  1 слайд

  Задачи оптимизации
  использование надстройки
  «Поиск решения»
  
  Автор разработки:
  преподаватель информационных технологий Васильева Е.С.
  БОУ СПО ВО «Великоустюгский автотранспортный техникум»
  Великий Устюг 2014 г.
  

• 

  2 слайд

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

• 

  3 слайд

  Технология использования надстройки Поиск решения показана на примере трех задач:
  
  1. Производство конфет
  2. Задача о рюкзаке
  3. Транспортная задача
  

• 

  4 слайд

  Задача 1.
  Производство конфет
  Предположим, что мы решили производить несколько видов конфет. Назовем их условно «А», «В», «С».
  Известно, что реализация 10 кг конфет «А» дает прибыль 9 у.е., «В» − 10 у.е., «С» − 16 у.е.
  Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограмм необходимо производить, чтобы общая прибыль от реализации была максимальной.
  Нормы расхода сырья на производстве 10 кг конфет каждого вида приведены в таблице 1.
  
  

• 

  5 слайд

  Таблица 1
  Нормы расхода сырья
  

• 

  6 слайд

  Технология работы:
  Запустите табличный процессор Excel.
  Заполните таблицу в соответствии с образцом:
  
  

• 

  7 слайд

  Технология работы:
  В меню Сервис активизируйте команду Поиск решения
  Установите целевую ячейку:
  равной значению
  Укажите изменяемые ячейки:
  Опишите ограничения:
  
  
  В Параметрах укажите Линейность модели
  Запустите Поиск решения
  
  
  
  $C$6
  максимальному
  $B$3:$B$5
  $A$10<=360$B$10<=192$C$10<=180
  $B3>=0$B$4>=0$B$5>=0
  

• 

  8 слайд

  Поиск решения
  
  

• 

  9 слайд

  Результаты вычислений:
  Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет «В» и 20 кг конфет «С». Конфеты «А» производить не стоит. Полученная прибыль составит 400 у.е.
  

• 

  10 слайд

  Имеется 4 предмета, каждый из которых характеризуется весом и ценой. Нужно выбрать из них такие и столько, чтобы их общий вес не превышал 83, а суммарная цена была максимальной.
  

• 

  11 слайд

• 

  12 слайд

  Запустите табличный процессор Excel.
  Заполните таблицу в соответствии с образцом:
  
  

• 

  13 слайд

  
  В меню Сервис активизируйте команду Поиск решения
  Установите целевую ячейку:
  равной значению
  Укажите изменяемые ячейки:
  Опишите ограничения:
  
  
  
  
  В Параметрах укажите Линейность модели
  Запустите Поиск решения
  
  $G$7
  $E$3:$E$6
  максимальному
  $E$3:$E$6 >=0 количество предметов
  $E$3:$E$6 = целое предметы не разделяются
  $F$7<=B$1 общий вес <= допустимому
  

• 

  14 слайд

• 

  15 слайд

  Из решения видно, что необходимо приобрести только предметы первого и четвертого вида.
  

• 

  16 слайд

  Транспортная задача является классической задачей исследования операций.
  
  Множество задач распределения ресурсов сводится именно к этой задаче – минимилизации расходов.
  Задача 3.
  Транспортная задача
  

• 

  17 слайд

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

• 

  18 слайд

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

• 

  19 слайд

  Запустите табличный процессор Excel.
  Заполните таблицу в соответствии с образцом (где ячейки В4-В8, С9-F9, B16-F16 вычисляются по соответствующим формулам):
  
  Технология работы:
  

• 

  20 слайд

  В ячейке В16  по формуле  =СУММ(С16:F16) будет вычисляться общий объем грузооборота минеральных удобрений.
  В строке 16 по столбцам C-F вводим формулу =СУММПРОИЗВ(C4:C8;C11:C15))
  Значение ячейки С9 вычисляется по формуле =СУММ(C4:C8), D9-F9 аналогично по соответствующим столбцам
  Значение ячейки B4 вычисляется по формуле =СУММ(C4:F4) , B5-B9 аналогично по соответствующим строкам
  Технология работы:
  

• 

  21 слайд

  В меню Сервис активизируйте команду Поиск решения
  Установите целевую ячейку:
  равной значению
  Укажите изменяемые ячейки:
  Опишите ограничения:
  
  
  В Параметрах укажите Линейность модели
  Запустите Поиск решения
  Технология работы:
  $B$16
  минимальному
  $С$4:$F$8
  $B$4:$B$8 <=$B$11:$B$15, $С$4:$F$8>=0
  $С$9:$F$9>=$C$10:$F$10
  

• 

  22 слайд

  Поиск решения
  

• 

  23 слайд

  Результаты вычислений:
  Из решения видно, что минимальный грузооборот перевозок при соблюдении всех условий равен 3540 т.-км.
  

• 

  24 слайд

  Спасибо за работу!
  

Краткое описание материала