Практическое задание "Подбор параметра. Поиск решения."
Автор: Панова Надежда Александровна (преподаватель информатики, ГБПОУ НАО "Нарьян-Марский социально-гуманитарный колледж имени И.П. Выучейского")
Тип документа: Другое. Аудитория: Преподаватели информатики. Дата публикации: 26.09.2017
Описание: Практическое задание по теме "Подбор параметра. Поиск решения"В Excel реализован поиск значения параметра формулы, удовлетворяющего конкретному значению.


                                                Панова Надежда Александровна
                    ГБПОУ НАО "Нарьян-Марский социально-гуманитарный колледж
                                                     имени И.П. Выучейского"
                                                   Преподаватель информатики
                              Подбор параметра
      В Excel реализован поиск значения параметра формулы,  удовлетворяющего
конкретному значению.
      Эту процедуру  используют  для  поиска  такого  значения  ячейки,  при
котором значение другой ячейки, вычисляемое по формуле,  заранее  задано.  В
формуле должна быть ссылка на ячейку, значения которой ищут. Ограничения  на
искомое значение ячейки не налагают.
      Познакомимся  с  этой  процедурой  на  примере  составления   штатного
расписания.
      Пусть известно, что для нормальной работы турфирмы необходимо 5 гидов,
8 экскурсоводов,  3  менеджера,  главный  бухгалтер,  генеральный  директор,
уборщица. Общий месячный фонд зарплаты составляет 200000 рублей.
      Построим модель решения этой задачи. За основу возьмем оклад уборщицы,
а остальные оклады будем вычислять, исходя из него: во  столько-то  раз  или
на столько-то больше. Говоря математическим языком,  каждый  оклад  является
линейной функцией от оклада уборщицы: Ai*С+Вi, где С - оклад уборщицы; Аi  и
Вi  -  коэффициенты,  которые  для  каждой  должности  определяют  следующим
образом:
      гид получает в 2,5 раза больше уборщицы (А2=2,5; В2=0);
      экскурсовод - в 3 раза больше уборщицы (А3=3; В3=0)
      менеджер - на 2000 рублей больше, чем экскурсовод (А4=3; B4=2000);
      главный бухгалтер - в 5 раз больше уборщицы (А5=5; В5=0);
      генеральный директор  - на  5000  рублей  больше  главного  бухгалтера
(А6=5; В6=5000);
      Зная  количество  человек  на  каждой  должности,  нашу  модель  можно
записать как уравнение
      N1*A1*C+N2*(A2*C+B2)+...+N6*(A6*C+B6) = 200000,
      где N1 - количество уборщиц, N2 - количество гидов и т.д.
      В этом уравнении нам  известны  A1...A6,  B1...B6  и  N1...  N6,  а  С
неизвестно.
      Анализ уравнения показывает, что задача составления расписания свелась
к решению линейного уравнения относительно С. Решим его.
      Введите исходные  данные  в  рабочий  лист  электронной  таблицы,  как
показано ниже.
      В столбце D вычислите заработную плату для каждой должности. Например,
для ячейки D6 формула расчета имеет вид =B6*$С$3+C6. Скопируйте формулу  для
других должностей с помощью автозаполнения.


      В столбце F вычислите заработную плату всех рабочих данной  должности.
Например, для ячейки F6 формула расчета имеет вид =D6*E6.
      В ячейке F12 вычислите суммарный  фонд  заработной  платы  турфирмы  с
помощью Автосумма. Рабочий лист электронной  таблицы  будет  выглядеть,  как
показано ниже.
       Необходимо подключить пакет анализа Нажмите кнопку Microsoft Office
[pic], а затем щелкните Параметры Excel.
       Выберите команду Надстройки и в окне Управление выберите пункт
Надстройки Excel.
       Нажмите кнопку Перейти.
       В окне Доступные надстройки установите флажок Пакет анализа, а затем
нажмите кнопку ОК.
       Определите  оклад  уборщицы  так,  чтобы  расчетный  фонд  был  равен
заданному:
      перейдите во вкладку Данные ленты инструментов.
      Щелкните на кнопке Анализ «что-если» и  в  открывшемся  меню  выберите
элемент Подбор параметра. Откроется диалоговое окно Подбор параметра;
      в поле "Установить в  ячейке"  появившегося  окна  введите  ссылку  на
ячейку F12, содержащую формулу;
      в поле "Значение" наберите искомый результат 200000;
      в поле "изменяя значение ячейки" введите ссылку на  изменяемую  ячейку
С3 и щелкните на кнопке ОК.
      При фонде заработной платы 200000 рублей, зарплата  уборщицы  составит
3345 рублей 13 копеек.
      Сохраните таблицу в личном каталоге под именем турфирма.xls.


      Анализ задачи показывает, что с помощью Excel  можно  решать  линейные
уравнения. Благодаря этому простому  примеру  стало,  очевидным,  что  поиск
значения параметра формулы, удовлетворяющего ее конкретному значению, -  это
не что иное, как численное решение  уравнений.  Другими  словами,  используя
Excel, можно решать любые уравнения с одной переменной.
      Например,  поиск  нескольких  параметров,   обеспечивающих   некоторый
наперед заданный результат.
      Кроме того, иногда интересует не конкретный  результат,  а  минимально
или  максимально  возможный.  Например,  как   минимизировать   затраты   на
содержание персонала или максимизировать прибыли от реализации продукции?
      Такие задачи в Excel решают с помощью Поиска решения.
      Если математическая модель  исследуемого  процесса  и  ограничения  на
значения ее параметров линейны, то задача достижения цели  является  задачей
линейного программирования.
      Познакомимся с решением этих задач на следующем примере.
      Пусть известно, что для  нормальной  работы  турфирмы  необходимо  5-7
гидов, 8-10 экскурсоводов, 3-4  менеджера,  главный  бухгалтер,  генеральный
директор, уборщица. Общий месячный  фонд  зарплаты  должен  быть  минимален.
Необходимо определить, какими должны быть оклады сотрудников  турфирмы,  при
условии, что зарплата уборщицы не должна быть меньше  прожиточного  минимума
10891.
      В качестве модели решения этой задачи возьмем, как и в  первой  главе,
линейную. Запишем ее так:
      N1*A1*C+N2*(A2*C+B2)+...+N6*(A6*C+B6) = Минимум.
      Используя Поиск решения, найдем их.
      Откройте созданный ранее файл со штатным расписанием турфирмы.
       Необходимо подключить поиск решения Нажмите кнопку Microsoft Office
[pic], а затем щелкните Параметры Excel.
       Выберите команду Надстройки и в окне Управление выберите пункт
Надстройки Excel.
       Нажмите кнопку Перейти.
       В окне Доступные надстройки установите флажок Поиск решения, а затем
нажмите кнопку ОК.
      На вкладке Данные активизируйте окно Поиск решения.
      В окне  Установить  целевую  ячейку  укажите  ячейку  F12,  содержащую
модель.
      Поскольку необходимо минимизировать общий месячный фонд  зарплаты,  то
активизируйте кнопку Минимальному значению.
      Используя кнопку Добавить, опишите ограничения задачи.
      Окончательно окно Поиска решения будет выглядеть так:
      Опишите Параметры поиска, как показано на рис.
      Щелкните на кнопке ОК, а затем - Выполнить.










      Решение приведено на рис.  Оно тривиально: чем  меньше  сотрудников  и
чем меньше их оклад, тем меньше месячный фонд заработной платы.
      Для закрепления пройденного материала решим следующую задачу.
      План выгодного производства
      Предположим, что мы решили предлагать  несколько  видов  туристических
маршрутов. Назовем их условно "A",  "B"  и  "C".  Известно,  что  реализация
маршрута "А" дает прибыль 9000 рублей, "В" – 10000 рублей.  и  "С"  –  16000
рублей.
      Маршруты можно предлагать в любых количествах  (спрос  обеспечен),  но
количество оборудования ограничено. Необходимо определить,  каких  конфет  и
сколько десятков килограмм необходимо произвести,  чтобы  общая  прибыль  от
реализации была максимальной.
      Нормы оборудования на турмаршруты каждого вида приведены ниже.
      Оборудование       Нормы   использования    оборудования    Количество
оборудования
                             А          В          С
      Лыжи                   18         15         12         360
      Снегоходы              6          4          8          192
      Сани                   5          3          3          180
      Прибыль                19000           10000      16000


      Введите исходные данные и формулы в электронную таблицу,  как  указано
ниже.


      В меню Данные  активизируйте  команду  Поиск  решения  и  опишите  его
параметры, как указано на рис.


      Не забудьте указать в Параметрах на Линейность модели.


      Запустите Поиск решения. Если Вы сделали все верно, то  решение  будет
таким, как на рис.