Лекция 11 Excel 2007. Анализ «что-если»

1.  Подбор параметра

1.1.  Инструмент подбор параметра

2.  Создание сценариев для анализов «что-если»

2.1.  Создание сценария

2.2.  Просмотр сценария

2.3.  Создание итогового отчета по сценариям

3.  Таблица подстановок

3.1.  Создание таблицы подстановки с одной переменной

3.2.  Создание таблицы подстановки с двумя переменными

3.3.  Ускорение пересчета листа, содержащего таблицу подстановки данных

3.4.  Удаление таблицы подстановки данных

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

4.1.  Установка надстройки Поиск решения

4.2.  Постановка задачи и оптимизация модели  с помощью процедуры поиска решения

4.3.  Изменение способа поиска решения Советы.

4.4.  Просмотр промежуточных результатов поиска решения

4.5.  Сохранение и загрузка  модели оптимизации

4.6.  Решение задач на оптимизацию

 

 

1. Подбор параметра

Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра». Подбор параметра является частью блока задач, который иногда называют инструментами анализа "чтоесли". Термины и определения.

Анализ «что-если» - процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. 

Подбор параметра - способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При подборе параметра Microsoft Excel изменяет значение в одной конкретной в ячейке до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.

1.1. Инструмент подбор параметра

1.    MS Office 2007. На вкладке Данные в группе Работа с данными выбрать пиктограмму Анализ

«что-если», а затем выбрать в списке пункт Подбор параметра.  

MS Office ‘97-2003. Выполнить команду Сервис \ Подбор параметра.

2.    В поле Установить в ячейке ввести ссылку на ячейку, содержащую необходимую. 

3.    Ввести искомый результат в поле Значение. 

4.    В поле Изменяя значение ячейки ввести ссылку на ячейку, значение которой нужно подобрать.

! Формула в ячейке, указанной в поле Установить в ячейке должна ссылаться на эту ячейку.

Требования к использованию возможности «Подбор параметра»:

1.    Ячейка, адрес которой указывается в поле Значение должна содержать формулу, а значение в ней должно быть наиболее близким к тому, которое требуется получить. 

2.    Ячейка, в которой должно быть выведено искомое значение, должна прямо или косвенно влиять на результат в ячейке с формулой (п.1).

Задача 1. По сумме займа (100 000$) и сроку займа (180 месяцев) определить процентную ставку, при которой ежемесячная плата составит 900 $. 

Решение.

1. Создать таблицу:  в ячейках В1:В2 – исходные данные;  в ячейке В3 – подбираемый параметр;  в ячейке В4 – формула расчета ежемесячной выплаты: 

             =ПЛТ(B3%/12;B2;B1)  

Справка. 

ПЛТ(ставка;кпер;пс;бс;тип) - возвращает размер периодического платежа, необходимого для погашения ссуды за определенный период времени.

Ставка - процентная ставка по ссуде.

Кпер – количество выплат по ссуде.

Пс – значение ссуды или общая стоимость.

Бс  - требуемое значение будущей стоимости, или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (нулю), т.е. для займа, например, значение бс равно 0.

Тип — число 0или 1 обозначающее, когда должна производиться выплата (0 или опущен - в конце периода; 1 - в начале).  2. Выполнить команду Сервис \ Подбор параметра.

3.    В поле Установить в ячейке ввести  ссылку на ячейку, содержащую необходимую формулу ($B$4).

4.    Ввести искомый результат в поле Значение (-900). 

В данном случае значение надо указать с минусом, потому что это выплаты.

5.    В поле Изменяя значение ячейки ввести ссылку на ячей-

ку, значение которой нужно подобрать ($B$3).  

Ответ:

 

Задача 2. Какие должны быть ежемесячные вклады в течение 12 месяцев при процентной ставке 10,5%годовых, чтобы по истечении срока на счету накопилась сумма в 100 000 руб.?

Решение.

 

БС(ставка;кпер;плт;пс;тип) - возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.

Например, =БС(10%/12;30*12;-2000;;1)

(расчет процентов в начале месяца, если 0 – в конце и тогда общий результат меньше)

Задача 3. Решить уравнение: ax+b=0. Решение.

 

Задача 4. Тело брошено вертикально вверх со скоростью v=10 м/с с высоты h=5 м. Известно, что его положение относительно поверхности земли у (в метрах) в зависимости от времени t (в секундах) описывается функцией y=h+vt-4,9t². Определить момент времени, когда тело упадет на землю. Решение.

 

! В данном примере, в отличие от рассмотренных выше, перед запуском команды Подбор параметра в ячейке В3 надо установить значение близкое к искомому (например, 2). В противном случае будет получен неправдоподобный результат.

Рассмотрим случай косвенного влияния. 

Задача 5. Необходимо подобрать такой диаметр провода, включенного в электрическую цепь, чтобы ток, проходящий по нему, был равен 4 А. Удельное сопротивление материала провода – 1,5 Ом∙см, длина – 10 см. Напряжение 220В.

Примечание. Формулы:

U

Площадь сечения: S                       . Сила тока: I, гдеUнапряжение, R           сопротивление.

2. Создание сценариев для анализов «что-если»

Сценарии являются частью блока задач «что-если». 

Сценарий - это набор значений, которые в приложении Microsoft Office Excel сохраняются и могут автоматически подставляться в лист. 

Сценарии можно использовать для прогноза результатов моделей расчетов листа. Существует возможность создать и сохранить в листе различные группы значений, а затем переключаться на любой из этих новых сценариев, чтобы просматривать различные результаты.

2.1. Создание сценария

Например, если требуется создать бюджет, но доходы точно не известны, можно определить различные значения дохода, а затем переключаться между сценариями для выполнения анализов «что-если».

 

В приведенном примере можно назвать сценарий «Худший случай», установить в ячейке B1 значение 50 000р., а в ячейке B2 значение 13 200р.

 

Второй сценарий можно назвать «Лучший случай» и изменить значения в ячейке B1 на 150 000р., а в ячейке B2 на 26 000р.

1.      На вкладке Данные в группе Работа с данными выбрать команду Анализ «что-если», а затем выбрать в списке пункт Диспетчер сценариев. 

2.      Нажать кнопку Добавить. 

3.      Ввести в поле Название сценария название для сценария. 

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

5.      Установить необходимые флажки в группе Защита. 

6.      Нажать кнопку ОК. 

7.      Ввести необходимые значения в диалоговом окне Значения ячеек сценария. 

8.      Чтобы создать сценарий, нажать кнопку ОК. 

9.      Если требуется создать дополнительные сценарии, надо повторить шаги 2-8. После завершения создания сценариев нажать кнопку ОК, а затем кнопку Закрыть в диалоговом окне Диспетчер сценариев.

2.2. Просмотр сценария

При просмотре сценария изменяются ячейки, сохраненные как часть этого сценария.

10.  На вкладке Данные в группе Работа с данными выбрать команду Анализ «что-если», а затем выбрать в списке пункт Диспетчер сценариев.

11.  Выберите имя сценария, который требуется просмотреть. 

12.  Нажмите кнопку Вывести. 

2.3. Создание итогового отчета по сценариям

Чтобы сравнить несколько сценариев, можно создать отчет, обобщающий их на одной странице. Сценарии в отчете могут располагаться один рядом с другим либо могут быть обобщены в отчете сводной таблицы.

13.  На вкладке Данные в группе Работа с данными выбрать команду Анализ «что-если», а затем выбрать в списке пункт Диспетчер сценариев.

14.  Нажмите кнопку Отчет. 

15.  Выбрать Тип отчета - Структура или Сводная таблица. 

16.  В поле Ячейки результата ввести ссылки на ячейки, значения которых изменяются с помощью сценариев. В качестве разделителя ссылок используется точка с запятой.   

Примечание. Не обязательно указывать ячейки результата в итоговом отчете, однако это потребуется в сценарии «Отчет сводной таблицы».

3. Таблица подстановок

Таблицы данных являются частью блока задач, который иногда называют инструментами анализа «что-если». 

Термины и определения.

Таблица данных, таблица подстановок  - диапазон ячеек, содержащий результаты подстановки различных значений в одну или несколько формул. 

Таблица данных представляет собой диапазон ячеек, показывающий, как изменение определенных значений в формулах влияет на результаты этих формул. 

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

Существует два типа таблиц данных: таблицы с одним переменной и таблицы с двумя переменными.

Ячейка ввода - ячейка, в которую подставляются все значения из таблицы данных. Ячейкой ввода может быть любая ячейка листа. Хотя ячейка ввода не обязана входить в таблицу данных, формулы в таблице данных должны ссылаться на ячейку ввода.

Массив - объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.

3.1. Создание таблицы подстановки с одной переменной 

Следует сформировать таблицу подстановки с одной переменной, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке). Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода.

 

1.      В отдельный столбец или в отдельную строку ввести список значений, которые следует подставлять в ячейку ввода. 

2.      Выполнить одно из следующих действий. 

§  Если значения в таблице данных ориентированы по столбцу, ввести формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения. Правее первой формулы ввести любые другие формулы. 

§  Если значения в таблице данных ориентированы по строке, ввести формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения. В том же столбце, но ниже ввести любые другие формулы.

3.      Выделить диапазон ячеек, содержащий формулы и значения подстановки. 

4.      MS Office 2007. На вкладке Данные в группе Работа с данными выбрать пиктограмму Анализ «что-если», а затем выбрать в списке пункт Таблица данных.  

MS Office ‘97-2003. В меню выбрать команду Данные \ Таблица подстановки. 

5.      Выполнить одно из следующих действий: 

§  если значения в таблице расположены по столбцам, ввести ссылку на ячейку ввода в поле Подставлять значения по строкам в; 

§  если значения в таблице расположены по строкам, ввести ссылку на ячейку ввода в поле Подставлять значения по столбцам в. 

Задача 1. Посмотреть, как различные процентные ставки влияют на размер ежемесячных выплат по закладной. 

 

 

Решение:

1.    Создать таблицу: 

      В2 – ячейка ввода;            

      B3:В4 – исходные данные;          

      Е2 – формула для расчета:         =ПЛТ(B2/12;$B$3;-$B$4) 

D3:D5 – список значений, которые подставляются в ячейку ввода В2;       D2:E5 – таблица подстановок.

2.    Выделить диапазон D2:E5. 3. Вызвать Данные \ Таблица подстановки.

4. В появившемся окне в поле  Подставлять значения по строкам в; указать ячейку ввода В2.

3.2. Создание таблицы подстановки с двумя переменными

Таблицы подстановки  с двумя переменными используют одну формулу с двумя наборами значений.

Формула должна ссылаться на две различные ячейки ввода.

1.      В ячейку листа ввести формулу, которая ссылается на две ячейки ввода. 

2.      В том же столбце ниже формулы ввести значения подстановки для первой переменной. 

3.      Ввести значения подстановки для второй переменной справа от формулы в той же строке. 

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

5.      MS Office 2007. На вкладке Данные в группе Работа с данными выбрать пиктограмму Анализ «что-если», а затем выбрать в списке пункт Таблица данных.  

MS Office ‘97-2003. В меню выбрать команду Данные \ Таблица подстановки. 

6.      В поле Подставлять значения по столбцам в ввести ссылку на ячейку ввода для значений подстановки в строке. 

7.      В поле Подставлять значения по строкам в ввести ссылку на ячейку ввода для значений подстановки в столбце. 

8.      Нажать кнопку OK. 

Задача 2. Составить таблицу умножения чисел от 1 до 5.

Таблица умножения представляет собой таблицу данных с двумя переменными.

 

1.      Настроить лист со следующей структурой. 

 

А1:В1 – ячейки ввода с начальными значениями.

В3:F3 - значения для подстановки в ячейку ввода А1;

А4:А8 – значения для подстановки в ячейку ввода В1; А3 – формула для расчета, зависящая от ячеек ввода =А1*В1.

2.      Выделить диапазон A3:F8.

3.      Данные \ Таблица подстановки. 

4.      В поле Подставлять значения по столбцам в ввести A1. 

5.      В поле Подставлять значения по строкам в ввести В1. 

6.      Нажать кнопку OK. 

7.      При необходимости закрепить таблицу данных, скопировав с помощью специальной вставки только значения. Задача 3.  

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

1.      Настроить лист со следующей структурой. 

 

 

В2:В3 – ячейки ввода с начальными значениями;

В4 – исходные данные;

Е2:G2 - значения для подстановки по столбцам в ячейку ввода B3;

D3:D5 – значения для подстановки по строкам в ячейку ввода В2;

D2 – формула для расчета, зависящая от ячеек ввода =ПЛТ(B2/12;B3;-B4).

2.      Выделить диапазон D2:G5.

3.      Данные \ Таблица подстановки. 

4.      В поле Подставлять значения по столбцам в ввести В3. 

5.      В поле Подставлять значения по строкам в ввести В2. 

6.      Нажать кнопку OK. 

3.3. Ускорение пересчета листа, содержащего таблицу подстановки данных

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

MS Office 2007

1.      Щелкнуть по кнопке Microsoft Office , выбрать Параметры Excel, а затем выбрать категорию Формулы. 

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

MS Office ‘97-2003

3.      В меню выбрать команду Сервис \ Параметры: Вычисления. 

4.      Выбрать вариант Автоматически кроме таблиц. 

Примечание. При выборе этого варианта вычисления при пересчете книги таблицы не пересчитываются. Чтобы выполнить пересчет таблиц вручную, надо выделить формулу и нажать клавишу F9.

3.4. Удаление таблицы подстановки данных

Удаление всей таблицы

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

2.      MS Office 2007. На вкладке Главная  в группе Редактирование выбрать команду Очистить, а затем выбрать в списке команду Очистить все (или нажать клавишу Delete).  MS Office ‘97-2003. Выбрать команду Правка \ Очистить \ Все.  Удаление рассчитанных значений из таблицы подстановки данных

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

Если удалять всю таблицу данных не требуется, формулы и значения подстановки выделять не надо.

1.      Выделить в таблице данных все рассчитанные значения. Формулы и значения подстановки не выделять!

2.      MS Office 2007. На вкладке Главная  в группе Редактирование выбрать команду Очистить, а затем выбрать в списке команду Очистить содержимое (или нажать клавишу Delete). 

MS Office ‘97-2003. Выбрать команду Правка \ Очистить \ Содержимое (или нажать клавишу Delete).

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

Поиск решений является частью блока задач, который иногда называют анализ "что-если". Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.

Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму (максимуму или минимуму).

Примеры расчетов с использованием процедуры поиска решения

В составе Microsoft Excel в папке Office\Samples находится книга с примерами (Solvsamp.xls)  использования процедуры поиска решения: «Структура производства», «Транспортная задача», «График занятости», «Управление капиталом», «Портфель ценных бумаг» и «Проектирование цепи».

В примерах уже подобраны целевая и влияющие ячейки, а также ограничения.

4.1. Установка надстройки Поиск решения

MS Office 2007

1.      Щелкнуть по кнопке Microsoft Office , выбрать Параметры Excel, а затем выбрать категорию Надстройки. 

2.      В поле Управление выбрать Надстройки Excel и нажать на кнопку Перейти. 

3.      В поле Доступные надстройки установить флажок рядом с пунктом Поиск решения и нажать ОК. 

MS Office ‘97-2003

1.      Сервис \ Надстройки;

2.      Нажать кнопку Обзор, чтобы найти надстройку которой нет в окне Список надстроек. 

3.      Установить в окне Список надстроек флажок той надстройки, которую необходимо загрузить. 

4.      Следовать инструкциям программы установки, если они имеются. 

4.2. Постановка задачи и оптимизация модели  с помощью процедуры поиска решения

1.      MS Office 2007. На вкладке Данные в группе Анализ щелкните Поиск решения.             MS Office ‘97-2003. В меню выбрать команду Сервис \ Поиск решения. 

2.      В поле Установить целевую ячейку ввести ссылку на ячейку или имя конечной ячейки, которая должна содержать формулу. 

3.      В поле Равной установить переключатель в одно из трех положений: 

§   максимальному значению - чтобы максимизировать значение конечной ячейки путем изменения значений влияющих ячеек; 

§   минимальному значению - чтобы минимизировать; 

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

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

Примечание. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажать кнопку Предположить. 

5.      В поле Ограничения ввести все ограничения, накладываемые на поиск решения.  

Примечание. 

§   Ограничение состоит из трех составных частей: в поле Ссылка на ячейку ввести адрес или имя ячейки, на значение которой накладываются ограничения; выбрать из раскрывающегося списка условный оператор (<=, =, >=, цел или двоич ); в поле Ограничение ввести число, ссылку на ячейку или ее имя либо формулу, если было выбрано цел, то в поле Ограничение появится «целое», а если выбрано двоич - «двоичное».

§   Чтобы принять ограничение и приступить к вводу нового, надо нажать кнопку Добавить.

§   Чтобы принять ограничение и вернуться в диалоговое окно Поиск решения, надо нажать OK.

 

 

6.      Перейти в диалоговое окно Параметры. Флажок Линейная модель позволяет задать любое количество ограничений. При решении нелинейных задач на значения изменяемых ячеек можно наложить более 100 ограничений, в дополнение к целочисленным ограничениям на переменные. Здесь же можно установить флажок Неотрицательные значения. 

7.      Нажать кнопку Выполнить, в диалоговом окне Результаты поиска решения выбрать одно из следующих действий:

§   Сохранить найденное решение, чтобы установить найденное решение на листе;

§   Восстановить исходные значения, чтобы восстановить исходные данные;

§   В правой части в поле Тип отчета выбрать тип отчета (Результаты, Устойчивость, Пределы), чтобы создать отчет, основанный на найденном решении (если решение не найдено, параметры создания отчета не будут доступны), а затем нажать кнопку ОК. Отчет будет помещен на новый лист книги.

§   Сохранить сценарий, чтобы сохранить значения изменяющейся ячейки в качестве сценария, который можно будет отобразить позже. В появившемся диалоговом окне в поле Название сценария ввести имя для этого сценария. 

Чтобы просмотреть, изменить или выполнить сценарий, надо: 

MS Office 2007. На вкладке Данные в группе Работа с данными выберите команду Анализ «чтоесли», а затем выберите в списке пункт Диспетчер сценариев.  

MS Office ‘97-2003. В главном меню выбрать команду Сервис / Сценарии.

4.3. Изменение способа поиска решения

1.            В диалоговом окне Параметры поиска решения задать один или несколько следующих параметров:

§ в поле Максимальное время задается интервал в секундах – отведенное время на поиск решения;

§ в поле Предельное число итераций задается максимальное количество итераций (вариантов, повторений), отводимое на достижение конечного результата;

§ в поле Относительная погрешность задается необходимая погрешность -  чем меньше введенное число, тем выше точность результатов;

§ в поле Допустимое отклонение задается значение допустимого отклонения в процентах;

§ в поле Сходимость задается значение относительного изменения, при достижении которого в последних пяти итерациях поиск решения прекращается - чем меньше это значение, тем выше точность результатов.

2.            Нажать ОК.

3.            В диалоговом окне Поиск решения нажать кнопку Выполнить или Закрыть. 

Примечание. Чтобы восстановить параметры поиска решения по умолчанию в диалоговом окне Поиск решения выбрать кнопку Восстановить.

Советы.

1.      Чтобы прервать поиск решения, надо нажать клавишу ESC. Лист Microsoft Office Excel будет пересчитан с учетом последних найденных значений для влияющих ячеек.

2.      Если найденное решение задачи существенно отличается от ожидаемого результата или не найдено, можно попробовать запустить процедуру поиска решения с другими начальными значениями влияющих ячеек. Если задать такие значения влияющих ячеек, которые расположены близко к экстремальной точке целевой функции, можно значительно сократить время поиска решения.

3.      Если значения влияющих ячеек и целевых ячеек или ячеек, на которые наложены ограничения, различаются на несколько порядков, в диалоговом окне Параметры поиска решения надо установить флажок Автоматическое масштабирование. В случае нелинейных задач, прежде чем нажать кнопку Выполнить в диалоговом окне Поиск решения, убедитесь, что начальные значения во влияющих ячейках того же порядка, что и ожидаемые конечные значения.

При решении задач следует учитывать, что не каждая решаемая задача имеет однозначное решение.

4.4. Просмотр промежуточных результатов поиска решения 1. После постановки задачи в диалоговом окне Поиск решения нажать  кнопку Параметры. 

2.      Чтобы просмотреть значения всех найденных решений, в диалоговом окне Параметры поиска решения установить флажок Показывать результаты итераций, а затем нажать кнопку ОК. 

3.      В диалоговом окне Поиск решения нажать кнопку Выполнить. 

4.      В диалоговом окне Текущее состояние поиска решения нажать одну из кнопок:

§   Стоп, чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения;

§   Продолжить, чтобы выполнить следующую итерацию и просмотреть ее результаты 

4.5. Сохранение и загрузка  модели оптимизации

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

1.      После постановки задачи в диалоговом окне Поиск решения нажать  кнопку Параметры. 

2.      В зависимости от ситуации выполнить одно из двух:

§   для сохранения нажать кнопку Сохранить модель нажать кнопку и ввести ссылку на верхнюю ячейку столбца, в котором следует разместить модель оптимизации;

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

4.6. Решение задач на оптимизацию

Задача 1.

Фирма производит две модели А и В сборных книжных полок. 

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

§ для каждого изделии модели А требуется 3 кв.м досок, а изделия модели В – 4 кв.м; 

§ фирма может получать от своих поставщиков до 1700 кв.м. досок в неделю;

§ для каждого изделия модели А требуется 12 мин (0,2 ч.) машинного времени, а для изделия модели В – 30 мин. (0,5ч.);

§ в неделю можно использовать 160 ч машинного времени. 

Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 120 руб. прибыли, а каждое изделие модели В – 240 руб. прибыли?

Решение. 

Математическая модель.

Обозначим: x – количество деталей модели А, выпускаемых в течение недели; y – количество деталей модели В.

Тогда прибыль будет 120х+240у руб.

Эту прибыль надо максимализировать. 

Функцию, для которой ищется экстремум (максимум или минимум) называют целевой функцией. 

Беспредельному увеличению количества изделий препятствуют ограничения:

Ограничено количество материала для полок, отсюда неравенство: 3х+4у<=1700

Ограничено машинное время на изготовление полок, следовательно: 0,2х+0,5у<=160 Кроме того, количество изделий – неотрицательное целое число, поэтому х>=0, у>=0.

Формально наша задача оптимизации записывается следующим образом:

                                                                                            120x    240y     max      (1)

                                                                                            3x    4y      1700             (2)

             

0,2x 0,5y 160 (3) x 0, y 0 (4)

Создать таблицу в Excel:

 

Комментарии.

В ячейках С3:Е4, С10:11 заданы исходные данные и ограничения. В ячейках В7, В10, В11 записаны формулы аналогичные формулам системы: (1), (2), (3) соответственно, где вместо х, у и фактических данных – ссылки на ячейки.

1.    Выделить ячейку с целевой функцией

(В7).

2.    Вызвать решатель: Сервис \ Поиск решения. 

3.    В диалоговом окне Поиск решения задать параметры (рис.1):

Установить целевую ячейку: $В$11

Равной: максимальному значению

Изменяя ячейки: $В$3: $В$4

           Ограничения:  $В$10<=$С$10,          

                                     $В$11<=$С$11           

                                      $B$3:$B$4 цел Целое

 

Рис.1

4.    В окне Поиск решения выбрать кнопку Параметры, в появившемся окне Параметры поиска решения установить два флажка: Линейная модель (так как ограничения и целевая функция являются линейными по переменным х и у) и Неотрицательные значения (для переменных х и у – см. условие (4) в системе). После этого нажать кнопку ОК.

5.    Нажать кнопку Выполнить. Появится диалоговое окно Результаты поиска решения, в котором предлагается либо Сохранить найденное решение либо Восстановить исходные значения.

Ответ: изделие А – 300 шт., В – 200 шт.; прибыль 84000 руб. Задание 2. 

Имеются деньги достоинством в 1, 5, 10, 50, 100, 500 и 1000 руб. Определить наименьшее количество купюр разного достоинства для выдачи заданной суммы денег (например, 9999). 

Комментарии.

В ячейках В2:В8, С14 исходные данные.

В ячейке В11 – целевая функция. 

В ячейке В14 расчет ограничения.

В диалоговом окне Поиск решения задать значения:

Установить целевую ячейку: В11

Равной: минимальному значению Изменяя ячейки: $В$2: $В$8

           Ограничения:  $В$14=$С$14,             

                                      $B$2:$B$8 цел Целое

В окне Параметры поиска решения установить два флажка: Линейная модель и Неотрицательные значения.

 

Ответ: 1 руб. – 4 шт., 5 руб. – 1 шт., 10 руб. – 4 шт., 50 руб. – 1 шт., 100 руб. – 4 шт., 500 руб. – 1 шт., 1000 руб. – 9 шт.

Задание 3.  

Имеется 6 разных фруктов, каждый из которых характеризуется весом и ценой. Нужно выбрать из них такие фрукты, чтобы их общий вес колебался в пределах 11-12 кг, а суммарная цена была наименьшей. Решение:

 

Комментарии.

В ячейках В2:С7, С13:D13 – исходные данные и ограничения.

В ячейке В10 – целевая функция. =СУММПРОИЗВ(D2:D7;C2:C7) В ячейке В13 расчет ограничения. =СУММПРОИЗВ(D2:D7;C2:C7)

В диалоговом окне Поиск решения задать значения:

Установить целевую ячейку: В10 Равной: максимальному значению Изменяя ячейки: $D$2:$D$7

           Ограничения:  $В$13>=$С$13,          

                                     $В$13<=$С$14,          

                                     $D$2:$D$7 двоич Двоичное

В окне Параметры поиска решения установить два флажка: Линейная модель и Неотрицательные значения.