Задача 1. Лабораторная работа №4 по EXCEL

Лабораторная работа №4 по EXCEL

(план работы)

Цель работы освоение следующих технологических приемов работы:

- Поиск оптимальных решений

- Работа с макросами

I. Ознакомление с примером поиска оптимального решения корпорации Microsoft

1. Загрузить Microsoft Excel.

2. Открыть табличный файл Solvsamp.xls, находящийся в папке C: \Program Files\Micsoft Office\Office\Examples\Solver. Если такая папка отсутствует или в не нет соответствующего файла вы­пол­нить поиск файла Solvsamp.xls.

3. Установить рабочий лист «Краткий обзор». Освоить технологию решения на модели, связывающей за­тра­ты на рекламу с прибылью:

- найти затраты на рекламу в 1-м квартале, при которых достигается максимальная прибыль;

- найти ежеквартальные затраты на рекламу, обеспечивающие максимальную годовую прибыль без ограничений на затраты;

- найти ежеквартальные затраты на рекламу, обеспечивающие максимальную годовую прибыль при наличии ограничений на затраты

Выполнить поиск оптимальных решений для экономико-математических моделей, расположенных на других листах рабочей книги:

- транспортная задача;

- график дежурств;

- управление капиталом;

- портфель ценных бумаг.

II. Поиск решений

Задача 1

Найти оптимальные объемы выпуска трех видов продукции для получения максимальной прибыли от их продажи.

При решении данной задачи должны быть учтены следующие ограничения:

h общий объем производства – всего 300 изделий;

h должно быть произведено не менее 50 изделий А;

h должно быть произведено не менее 40 изделий В;

h должно быть произведено не более 40 изделий С.

Технология:

1. Вести в новый рабочий лист данные для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6 должны быть введены формулы.

2. Запустить задачу поиска решений. Для этого: выполнить команду Сервис/Поиск решений … и в окне “Поиск решений” ввести данные:

· в поле «Установить целевую ячейку» указать адрес D6;

· установить флажок «Равной максимальному значению»;

· в поле «Изменяя ячейки» определить изменяемые ячейки (B3: B5);

· в поле «Ограничения» по одному добавить каждое из следующих четырех ограничений задачи (B6=300; B3> =50; B4> =40; B5< =40). Для этого щелкнуть по кнопке «Добавить» и в появившемся окне «Добавление ограничения» ввести ссылку на ячейку (B6), оператор ограничения (=) и значение (300), для добавления следующего ограничения щелкнуть кнопку «Добавить» и повторить процедуру добавления ограничения; после ввода последнего ограничения щелкнуть кнопку «ОК».

· в диалоговом окне «Поиск решения» щелкнуть кнопку “ Выполнить ”;

· в диалоге “ Результаты поиска решения ” установить переключатель «Сохранить найденное решение», в окне «Тип отчета» выбрать «Результаты» и нажать кнопку “Ok”;

· ознакомиться с отчетом по результатам, помещенным на новом листе.