Список использованных источников
Федеральное государственное автономное
образовательное учреждение
высшего профессионального образования
«СИБИРСКИЙ ФЕДЕРАЛЬНЫЙ УНИВЕРСИТЕТ»
Институт управления Бизнес-процессами и экономики
Кафедра экономики и информационных технологий менеджмента
ОТЧЕТ ПО ЛАБОРАТОРНОЙ РАБОТЕ
Решение задач оптимизации
Студент ПЭ 11-04 ______________ Е.С.Миготина
группа подпись, дата
Преподаватель ______________ А.В. Федорова
подпись, дата
Красноярск 2011
Содержание
1.Теоретическое введение3
2.Практическая часть4
2.1 Постановка задачи 4
2.2 Ход работы 5
3.Вывлод 10
4 Список использованных источников11
Теоретическое введение
В данном отчете по лабораторной работе представлен начальный спектр использования приложения MicrosoftExcel. Этот табличный процессор имеет множество приспособлений и калькуляторов для решения задач оптимизации. Так называются задачи о нахождении экстремума (минимума или максимума) вещественной функции в некоторой области. Также мы можем изучать результаты этих задач и подвергать их различным изменениям.
В MicrosoftExcel используют два способа оптимизации и анализа данных:
―анализ «что, если» или Подбор параметра
―анализ экономических решений с помощью технологии Поиск решения
|
|
|
Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра MicrosoftExcel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.
Поиск решений является частью блока задач, который иногда называют анализ "что-если". Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.
Практическая часть
Постановка задачи
Требуется оптимизировать значение общей прибыли банка из таблицы 1 до установленного значения (например, на 25% выше фактической цены) разными способами:
|
|
|
1) с помощью поочередного изменения курса валют
2) посредством одновременного изменения курсов валют без ограничений на курсы
3) с помощью одновременного изменения курсов валют с ограничением на курс одной валюты (не выше 5% от исходного курса)
4) изменением количества реализованной валюты за месяц с ограничением по целочисленности
Таблица 1 – Исходные данные задачи «Оптимизация прибыли»
Размеры в рублях
| Валютные средства | Реализованные средства за месяц | Всего | Курс валюты | Выручка | |||
| 1 неделя | 2 неделя | 3 неделя | 4 неделя | ||||
| $ | 56 748 | 4 857 | 48 909 | 65 447 | 175 961 | 36 | 6 382 105 |
| € | 49 576 | 54 936 | 25 183 | 34 648 | 164 343 | 43 | 7 022 376 |
| ¥ | 8 760 | 167 334 | 3 748 | 47 834 | 227 676 | 5 | 1 104 229 |
| £ | 9 879 | 87 349 | 13 754 | 9 480 | 120 462 | 49 | 5 885 773 |
| ₴ | 2 345 | 4 575 | 3 648 | 12 938 | 23 506 | 4 | 90 968 |
| Общий доход | |||||||
| *Коэффициент реализации 0,15 | |||||||
Продолжение таблицы 1
| Прибыль | |||
| 957 316* | |||
| 1 053 356* | |||
| 165 634*
| |||
| 882 866* | |||
| 13 645* | |||
| 3 072 818 |
Ход работы
Создаем документ MicrosoftExcelпод названием «Laba_6.xls». В нем подписываем листы как Исходные данные и Подбор параметра. Затем скопируем таблицу 1 на лист Исходные данные и на лист Подбор параметра в пяти экземплярах. Найдем на главной панели Данные – Работа с данными – Анализ «что, если» - Подбор параметра. В строке Установить значение укажем величину, на 5% большую в отличие от той, которая находится в ячейке I10, то есть получится цена 3841022,25р. В Изменяя значение ячейки введем G5, курс первой валюты. Далее нажимаем Выполнить. Курс валюты доллара изменился до 65, 37р., а цена в ячейке I10 стала 3841022,25р. Исходная таблица преобразуется в таблицу оптимальных результатов, то есть показывает, на сколько необходимо поднять первоначальную цену для достижения желаемого результата. Выделим ячейку G5 заливкой для удобного нахождения.
После этого проделаем такую же операцию с ячейками, содержащими курс евро, юани, фунта стерлингов и белорусского рубля. Будем выделять заливкой ячейки с измененным курсом. Ниже на этом листе составим таблицу прироста (табл. 2).
Таблица 2 – Прирост цен
| Валютные средства | Старая цена
| Новая цена | Прирост цен | |||
| $ | 36,27р. | 65,38р. | 80% | |||
| € | 42,73р. | 73,89р. | 73% | |||
| ¥ | 4,85р. | 27,34р. | 464% | |||
| £ | 48,86р. | 91,37р. | 87% | |||
| ₴ | 3,87р. | 221,74р. | 5630% |
Возьмем старую цену, новую цену, прирост считается по формуле: (стар.цена – нов. цена)/стар. цена. При этом используется функция ABS слева от строки формул. Подученный результат выразим в процентах. По результату построим диаграмму – гистограмму, как на рисунке 1. Для этого выделим первый и последний столбики таблицы и выберем графу Вставка – Гистограмма. Исправим некоторые недочеты, если они имеются, и лист Подбор параметра завершен.
Рисунок 1 – Гистограмма по приросту цен
Далее перейдем к технологии решения задач Поиск решения. Эта графа находится на вкладке Данные – Анализ - Поиск решения. Перейдем на лист Исходные данные откроем диалоговое окно Поиска решений. Где написано, Установить целевую ячейку, пишем ячейку с адресом I10, равной значению 3841022,25р., как в Подборе параметра. Изменяемые ячейки – это ячейки курса валют G5 – G9. В области ограничений ничего не пишем. Нажимаем Выполнить. Появляется еще одно окно, в котором выбираем Восстановить исходные значения, а Тип отчета - Результат. Нажимаем ОК и ждем, пока компьютер посчитает и выдаст отчет по результатам. Он откроется в новом листе под этим же названием. На рисунке 2 показано, как должен выглядеть отчет. Гистограмма строится по приросту цен, взятому из Исходного значения и Результата по формуле: (Исход.знач. – Результат)/Исход. знач. При этом используется функция ABS и процентный вид.
Для выполнения третьей поставленной задачи необходимо ввести ограничения. Для этого выбираем Поиск решения - Добавить и добавляем цену доллара в графу Ссылка на ячейку, устанавливаем знак меньше либо равно «<=» и пишем цену на 5% больше, чем значение в таблице, то есть 38,08р. Остальные данные, находящиеся в поиске решения, не меняются. 
Рисунок 2Отчет по результатам 1
Рисунок 3 Отчет по результатам 2
Четвертый пункт поставленной задачи: изменение количества реализованной валюты за месяц с ограничением по целочисленности. Для этого заходим в Поиск решения, не меняем целевую ячейку и её значение. В ограничениях удаляем число 38, 08 и пишем новое ограничение: нажимаем Добавить - Ссылка на ячейку выделим 4 недели B5 –E9 в таблице Исходных данных. Важно запомнить, что добавлять можно только ячейки, не содержащие формул. Ограничение выбираем целое и нажимаем ОК. Чтобы получить третий отчет в появившемся окне выбираем: Восстановить 
исходные значения, Тип отчета - Результат. В отдельном листе откроется Отчет по результатам 3, как на рисунке 4
Рисунок 4 Отчет по результатам 3, лист 1
Рисунок 4, лист 2
Вывод
Мы получили общие сведения о задачах оптимизации, освоили две технологии Поиск решений и Подбор параметра. Они подходят для решения экономических вопросов оптимизации производства.
При подборе параметра высчитывается выходное значение за счет изменения определенного параметра. В этом случае ограничения не вводятся, а оптимальное значение устанавливается поставщиком задачи. Таким образом, можно увеличить прибыль до желаемого объема за счет изменения влияющих факторов.
При Поиске решения для изменяемых параметров можно устанавливать границы, в которых они изменяются. В данном случае оптимальное решение находится по критериям минимума, максимума или по значению, задаваемому поставщиком.
Система Поиск решения является более многофункциональной и подвижной, выдает более точные решения, в то время как Подбор параметра исследует только целевые функции путем изменения определенного параметра.
Список использованных источников
1. ЕжеманскаяС.Н., Корпачева А.В., Федорова А.В, Джиоева Н.Н. Информатика: методические указания к практическим занятиям для студентов экономических специальностей учеб. М.: изд-е/ ГУЦМиЗ. – Красноярск, 2005. – 72с.
2. Данные сайта http://office.microsoft.com/ru-ru/excel-help
Дата добавления: 2018-04-05; просмотров: 108; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!