Ход работы. 1. Открываем книгу "laba_5.xls" и сохраняем её как книгу "laba_6.xls"

1. Открываем книгу "laba_5.xls" и сохраняем её как книгу "laba_6.xls".

2. Удаляем в книге лист "Исходные данные"; лист "Расчет" переименовываем в "Исходные данные"; добавляем листы "Подбор параметра" и "Поиск решения".

3. Копируем таблицу с листа "Исходные данные" на лист "Подбор параметра".

4. В меню Сервис выбираем пункт Подбор параметра. В строке Установить в ячейке введем ссылку на ячейку с адресом H8, содержащую формулу для прибыли. В строке Установить значение укажем величину, до которой должна увеличиться прибыль, то есть 200 000. В строке Изменяя значение ячейки введем ссылку на ячейку с адресом F5, содержащую значение цены первого изделия. Подтвердим задание Выполнить. Исходная таблица преобразуется в таблицу оптимальных результатов, полученных при помощи подбора цены первого изделия (рисунок 21 показывает, что для увеличения прибыли с 162 577 р. до 200 000 р. необходимо повысить цену изделия с 299 р. до 391,81 р.). Выделим оптимизируемую и изменяемую ячейку, например, заливкой.

Рисунок 21 – Оптимизация прибыли изменением цены первого изделия

5. Повторим пункты 3, 4 и оптимизируем прибыль, изменяя цены на второе и третье изделие.

6. На том же листе "Подбор параметра" построим вспомогательную таблицу (рисунок 22) и выполним графический анализ результатов оптимизации прибыли, для чего построим диаграмму изменения цены всех изделий. На этой диаграмме (рисунок 23) видим, что для увеличения прибыли требуется весьма существенно изменить цену на третье изделие, почти наполовину поднять в цене второе изделие и значительно менее повысить цену на первое изделие.

Рисунок 22 – Лист "Подбор параметра"

Рисунок 23 – Графический анализ оптимизации прибыли подбором параметра

7. Копируем таблицу с листа "Исходные данные" на лист "Поиск решения".

8. Рассмотрим, как можно увеличить общую прибыль, одновременно изменяя цены на все изделия без ограничений на последние. Выберем команду Сервис/Поиск решения. Откроется диалоговое окно Поиск решения. В поле Установить целевую ячейку укажем выделенную ячейку, в которой вычисляется значение прибыли (ячейка H8). В строке Установить равной значению введем значение 200 000. В поле Изменяя ячейки укажем диапазон подбираемых параметров (ячеек), содержащих значения цен (ячейки F5:F7 ). В строке Ограничение ничего указывать не будем, так как мы решили на данном этапе решения ограничения не вводить (рисунок 24). Нажмем кнопку Выполнить.

Рисунок 24 – Оптимизация прибыли изменением цен без ограничений

По завершении оптимизации открывается диалоговое окно Результаты поиска решения, в котором при установке флажка Сохранить найденное решение, можно сохранить протокол оптимизации. В этом случае автоматически появляется новый лист, содержащий Отчет по результатам 1 (рисунок 25).

Рисунок 25 – Отчет по результатам оптимизации

9. Скопируем исходную таблицу еще раз на лист "Поиск решения". Рассмотрим, как можно увеличить общую прибыль, вновь одновременно изменяя цены на все изделия, но с ограничением на рыночную цену изделия9/001(не более 309 р.). Поиск решения по таким условиям будет отличаться от вышеприведенной схемы наличием дополнительных действий: чтобы определить набор ограничений, в окне Поиск решения выполним действие Добавить. В поле Ссылка на ячейку укажем F15, а в качестве Условия выберем неравенство <=309 (рисунок 26). Окно Поиск решения представлено на рисунке 27.

Рисунок 26 – Ввод ограничений в окне Поиск решения

Рисунок 27 – Оптимизация прибыли c ограничением на цену 1-го изделия

Далее подтвердим операции Выполнить и Сохранить найденное решение. В результате можно получить новую таблицу оптимальных результатов и отчет по результатам оптимизации.

10. Можно попробовать оптимизировать значение прибыли до установленного значения изменением выпуска изделий, например за 1 квартал, с ограничением на целочисленность выпуска. Окно Поиск решения для данной оптимизации представлено на рисунке 28.