Лабораторная работа №6 анализ «что - если». Подбор параметра и поиск решения
Цель работы:
научиться определять значения с помощью Подбора параметра;
применение инструмента Поиск решения;
научиться применять полученные навыки.
Теоретическая часть
Подбор параметра
Подбор параметра является удобным средством для решения задач, которые имеют точное целевое значение, зависящее от одного неизвестного параметра. С помощью Подбор параметра можно определить значение, которое будет давать желаемый результат.
Чтобы воспользоваться средством Подбор параметра, нужно выбрать команду меню Сервис - Подбор параметра. В поле Установить в ячейке диалогового окна Подбор параметра нужно указать ячейку, в которой находится конечная формула (обычно данная ячейка выделяется до выбора команды меню Сервис - Подбор параметра).
В поле Значение нужно ввести значение, которое должно быть достигнуто в ячейке, указанной в поле Установить в ячейке.
В поле Изменяя значение ячейки необходимо ввести ссылку на ячейку, значение которой нужно подобрать таким образом, чтобы в ячейке из поля Установить в ячейке было получено значение, указанное в поле Значение, т.е. в этом поле нужно указать ссылку на ячейку, значение которой является неизвестным.
Поиск решения
Инструмент Поиск решения может применяться для решения задач, которые включают много изменяемых ячеек и помогают найти комбинации переменных, которые устанавливают целевую ячейку в требуемом значении (например, максимальное или минимальное). Он также позволяет задать одно или несколько ограничений - условий, которые должны выполняться при поиске решений.
Поиск решения применяется при решении транспортных задач, задач о назначениях, составлении оптимального плана производства, решении систем нелинейных уравнений и уравнений регрессии.
Прежде чем воспользоваться инструментом Поиск решения, нужно проанализировать задачу и построить математическую модель. Для построения модели необходимо: определить переменные, выбрать целевую функцию, задать ограничения.
После построения математической модели командой меню Сервис - Поиск решения вызвать диалоговое окно Поиск решения. (Если данная команда недоступна, то следует вызвать диалоговое окно Надстройки с помощью команды меню Сервис - Надстройки и установить флажок опции Поиск решения).
В поле Установить целевую функцию диалогового окна Поиск решения нужно указать ссылку на ячейку с целевой функцией. Выбрать переключатель, позволяющий задать какое значение целевой функции должно быть достигнуто при решении задачи. Поле Изменяя ячейки позволяет задать диапазон, в котором располагаются неизвестные величины, влияющие на целевую функцию. Список Ограничения представляет все ограничения, накладываемые кнопкой Добавить.
После того, как все данные заданы, нужно воспользоваться кнопкой Выполнить. После выполнения результат расчета будет размещен в соответствующей ячейке.
Практическая часть
Практическое задание 1.
Создайте новую книгу, сохраните ее под именем Задачи. На Листе 1 решите следующую задачу.
Вы желаете положить в банк деньги под 4,5% и получить ровно 1000р. по истечении года. Необходимо определить сумму вклада.
Для использования средства Подбор параметра необходимо определить следующее: если положить Х р. под 4,5% годовых, то выплата составит (1+0,045)*Х=1,045*Х. По условию в конце года нужно получить сумму 1000р., т.е. 1,054*Х=1000.
Решение задачи на рисунке 19. Заполните ячейки А1:А3, В1, В3 - формула (1+В1)*В2. Вызвать диалоговое окно Подбор параметра, заполнить его поля. Нажать кнопку ОК. Итого работы - найдено число 956,94р. и размещено в ячейке В2.
Рис. 19. Решение задачи Подбор параметра
Практическое задание 2.
На Листе 2 книги Задачи решите следующую задачу.
Для покупки автомобиля необходима сумма 200000р. Существует возможность взять ссуду на 30 лет со ставкой 8% годовых. Первоначальный взнос составляет 20%. Определить сумму, которую нужно взять в банке.
Пусть в банке выдали Хр. Первоначальный взнос составляет 20%*Х. Остается сумма Х - 20% Х.
Практическое задание 3.
На Листе 3 книги Задачи решите транспортную задачу.
Имеются два завода - производителя и три потребителя их продукции - домостроительные комбинаты. Известен суточный объем производства цемента, суточная потребность комбинатов в цементе и стоимость перевозки 1 тонны цемента от каждого завода к каждому комбинату.
Требуется составить план суточных перевозок цемента с целью минимизации транспортных расходов.
В
качестве неизвестной величины Хij
выступает объем перевозок цемента с
i-го
завода на j-ий
комбинат, при этом стоимость перевозки
1 тонны цемента с i-го
завода на j-ий
комбинат - Рij.
Тогда цель задачи можно представить в
виде:
.
При этом отмечаются ограничения:
объемы
перевозок не могут быть отрицательными
(
);
весь
цемент с завода должен быть перевезен
(
,
где аi
- объем производства цемента на i-ом
заводе);
потребности
всех комбинатов в цементе должны быть
удовлетворены (
,
где bi
- потребность в цементе j-го
комбината).
Подготовьте рабочий лист для решения задачи:
Заполните диалоговое окно Поиск решения и выполните поиск:
Практическое задание 4.
На Листе 4 книги Задачи решите следующую задачу.
Цель задачи: составить оптимальный план перевозки всей продукции с трех складов в два пункта назначения, при котором стоимость перевозок будет наименьшей.
Создайте и заполните следующие таблицы:
В ячейки В17:С19 занесите начальные значения «0», все итого рассчитываются по столбцам и строкам (функция СУММ). В ячейки В24:С26 ввести формулу для расчета стоимости перевозок. Для В24 это =ПРОИЗВЕД(В11;В17).
В диалоговое окно Поиск решения в поле Установить целевую ячейку введите ссылку на ячейку D27. Установите переключатель на Минимальное значение. В поле Изменяя ячейки укажите В17:С19.
Введите следующие ограничения D17=В4, D18=B5, D19=B6, B20=E4, C20=E5, В17:С19 = целое и В17:С19>=0. Выполните Поиск.
Практическое задание 5.
На Листе 5 книги Задачи решите следующую задачу оптимизации расходов предприятия.
Цель работы: необходимо составить график работы, обеспечивающий минимальные расходы предприятия на оплату труда работникам.
Предприятию в разные дни недели требуется разное количество работников. Если работник выходит на работу пять дней подряд, то недельная оплата труда – 500 руб., если же у работника только один выходной (суббота или воскресенье), то недельная оплата труда – 700 руб.
Для решения задачи подготовьте следующую таблицу:
Где ячейки D2:J10 содержат 1, если это рабочий день и 0, если – выходной.
Ячейки D12:J12 содержат формулу для расчета работников, занятых по дням недели, т.е. ячейка D12 содержит:
=D8*$B8+D2*$B2+D3*$B3+D4*$B4+D5*$B5+D6*$B6+D7*$B7+D10*$B10+D9*$B9.
В ячейку В12 введем формулу для расчета «приведенного» количества работников с учетом увеличения оплаты труда при шестидневной рабочей неделе. Формула будет следующей:
=СУММ(B2:B8)+СУММ(B9:B10)*1,4.
Ячейка В16 будет содержать формулу для расчета общей недельной оплаты, т.е. произведение «приведенного» количества работников на недельную оплату. Именно это значение и нужно свести к минимуму.
Для решения задачи выполните команду Сервис – Поиск решения. В диалоговом окне в поле Установить целевую ячейку введите ссылку на ячейку В16. Установите переключатель на Минимальное значение. В поле Изменяя ячейки укажите В2:В10.
Введите следующие ограничения В2:В8>=1, B9:B10>=0, D12:J12>=D13:J13, В2:B10 = целое. Выполните поиск.
Копируйте Лист и внесите изменения в диапазон ячеек D2:J10. Выполните снова команду меню Сервис – Поиск решений.