Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования




НазваниеРоссийской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
страница7/8
В И Коваленок
Дата конвертации09.02.2016
Размер0.49 Mb.
ТипМетодические указания
источникhttp://tu.tusur.ru/upload/posobia/k24.doc
1   2   3   4   5   6   7   8

5 Список литературы



5.1. Одиноков В.В. Автоматизированное управление в технических системах. Исследование операций. Детерминированные методы: Учебное пособие. – Томск: Томский межвузовский центр дистанционного образования, 2002. – 137 с.

5.2. Турунтаев Л.П. Теория принятия решений: Учебное пособие. – Томск: Томск. ун-т. систем управления и радиоэлектроники, 2003. – 222 с.

5.3. Горобцов г.я., Мастяева И.Н., Семенихина О.Н. Управленческие решения: Учебное пособие / Московский государственный университет экономики, статистики и информатики. – М.: МЭСИ, 2002. – 58 с.


Примечание. В методических указаниях, как пример, использованы материалы курсовой работы «Разработка модели перспективного плана предприятия DANATOIS» студентов группы 190 – 2 РТФ ТУСУРА Дементьевой Н.А. и Казанцевой Д.П., выполненной в 2003 году.


Приложение А




Краткие методические указания по Excel



РЕШЕНИЕ задачи ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ

С ПОМОЩЬЮ EXCEL


Пример. Предприятие производит два вида продукции А1, А2, используя сырье трех видов: S1, S2, S3. Нормы расхода сырья каждого вида на единицу продукции, наличие сырья в распоряжении предприятия, а также прибыль от реализации единицы продукции приведены в таблице 1. Требуется составить такой план выпуска продукции, при котором прибыль предприятия от ее реализации является максимальной.


Таблица 1  Информация о расходе и запасах сырья


Вид сырья

Норма расхода сырья на единицу продукции

Наличие сырья




А1

А2




S1

1

1

5

S2

2

1

9

S3

1

2

7

Прибыль от единицы продукции

3

4





Математическая модель данной задачи может быть записана следующим образом:

 max – целевая функция,

при ограничениях:



где x1 и x2 – количество единиц продукции соответственно А1 и А2.

Требуется определить объем производства продукции видов А1 иА2, при котором достигается максимум целевой функции.


ВВОД УСЛОВИЙ ЗАДАЧИ


Ввод условий задачи состоит из следующих основных шагов:

1. Создание формы для ввода условий задачи.

2. Ввод исходных данных.

3. Ввод зависимостей из математических моделей.

4. Назначение целевой функции и ввод ограничений.

Алгоритмы ввода условий:


1. Для автоматизации распределения ресурсов на основе таблицы Excel делается форма (рис. 1).

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


Примечание 1

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


2. С помощью формы в компьютер вводятся начальные условия задачи.




Рис. 1  Часть таблицы Excel с исходной формой




Рис. 2  Форма после ввода исходных данных


В результате вид таблицы Excel изменяется (рис. 2).


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


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

Для этого, необходимо пройти путь: Сервис, Параметры, Формулы.

 Клик над словом Сервис в верхней части экрана в линейке инструментов. Развернется ниспадающее меню. В нем внизу клик над словом Параметры.

Появится таблица Параметры.


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


 Клик над ОК внизу справа в таблице Параметры.
Снова появится вся таблица Excel.



3.1. Ввод зависимостей для целевой функции:

Алгоритм ввода зависимостей:

 Клик над ячейкой Е6.

Знаки Е и 6 – координаты ячейки таблицы, отведенной под значение целевой функции.

 Клик над кнопкой Мастер функций (значком fx вверху экрана в панели инструментов).

На экране появится диалоговое окно Мастер функций шаг 1 из 2.

 Выбрать в левой части этого окна, с помощью движка и мыши, в подокне Категории категорию Математические.

 Клик в подокне Категория над категорией Математические.

 Выбрать в правой части окна, с помощью движка и мыши, категорию Функция.

 Клик в окне Функция над функцией СУММПРОИЗВ.

 Клик над ОК справа внизу окна.

Слева вверху экрана появится окно СУММПРОИЗВ. Выше, в строке функций таблицы Excel (она частично видна), появится слово и круглые скобки СУММПРОИЗВ ().

Установить курсор в начале строки Массив 1 окна СУММПРОИЗВ.

 В массив 1 ввести код В$3:C$3.

Установить курсор в начале строки Массив 2 окна СУММПРОИЗВ.

 В массив 2 ввести код В6:С6.

 Клик над ОК справа внизу окна СУММПРОИЗВ.

Окно СУММПРОИЗВ исчезнет.

Появится полностью таблица Excel с надписью Готово внизу слева.

Вверху экрана в строке функций отобразится полный код закодированного текста целевой функции.

В ячейке Е6 таблицы установится значение 0. Этим закончится ввод целевой функции.

3.2. Ввод зависимостей для левых частей ограничений:

 Клик над Е6. Перед этим там обязательно должно быть установлено значение 0.

 Клик слева вверху экрана над словом правка.

Появится ниспадающее меню.

 Клик в меню над Копировать в буфер.

 Клик в таблице над ячейкой Е9.

 Клик в меню над Вставить из буфера.

 Клик в таблице над ячейкой Е10.

 Клик в меню над Вставить из буфера.

 Клик в таблице над ячейкой Е11.

 Клик в меню над Вставить из буфера.

В результате ячейки таблицы Е9, Е10 и Е11 заполнятся нулями, как это показано на рис. 3.



Рис. 3  Вид таблицы после ввода целевой функции и зависимостей
для левых частей ограничений


На этом ввод зависимостей закончен.

Примечание 4. Клики над ячейками E9, E10 и E11 вызывают в строку функций любой из кодов функций условий. Курсор имеет форму белого креста.



Примечание 5. Если при копировании нуля из ячейки целевой функции в ячейки левой части устанавливаются не нули, а другие числа, например число 1313, содержимое этих ячеек следует откорректировать. Для этого:

Клик над корректируемой ячейкой. В данном примере это ячейка H13.

В правом окне строки функций появится неверный код. Например, = СУММПРОИЗВ (B10:F10;B13:F13).

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

Вместо = СУММПРОИЗВ (B10:F10;B13:F13) получится = =СУММПРОИЗВ (B3:F3;B13:F13).

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

Клик на пустой ячейке ниже формы.

Код функции в корректируемой ячейке исчезнет. На его месте появится ноль.


4. Переход к назначению целевой функции и вводу ограничений.

4.1. Пройти путь: Сервис, Поиск решения. Для этого:

 Клик над Сервис в верхней части экрана в линейке инструментов.

Появится ниспадающее меню.

Клик над словами Поиск решения в нижней части меню.

Появится таблица Поиск решения (рис. 4А).


Примечание 4. Поиск решения является дополнением. Если такой команды нет, то выполните команду Дополнение и выделите Поиск решения в списке Доступные дополнения. Если же этого дополнения нет в списке, то необходимо обратиться к администратору сети и установить его, запустив команду Setup установки Microsoft Excel и выбрав вариант установки Полная / Выборочная.





Рис. 4А  Диалоговое окно Поиск решения с введенными условиями

4.2. Назначить целевую функцию:

 Курсор в окно Установить целевую ячейку.

 Ввести адрес: $Е$6.

 Ввести направление целевой функции. Для этого клик на кнопке с надписью Максимальному значению.

4.3. Ввести адреса искомых переменных. Для этого:

 Курсор в поле Изменяя ячейки.

 Ввести адреса: $В$3:$С$3.

4.4.  Клик над словом Добавить справа от окна Ограничения.

На экране появится узкая таблица. Это – диалоговое окно Добавление ограничения (рис. 4Б)





Рис. 4Б  Таблица (окно) Добавление ограничения


Ввести через его подокна граничные условия на переменные (x1, x2  0). Для этого:

 Поместить курсор в левой части таблицы Добавление ограничения.

 Набрать адреса $B$3:$C$3.

 Поместить курсор в среднюю часть таблицы Добавление ограничения.

 Выбрать с помощью движка условие >=.

 Поместить курсор в правую часть таблицы Добавление ограничения.

 Набрать адреса $B$4:$C$4.

 Клик над ОК.

Таблица Добавление ограничения исчезнет. Снова появится таблица Поиск решения с первой строкой кода ограничений в окне Ограничения. В ней отобразится код – $B$3:$C$3 >= $B$4:$C$4.

4.5. Чтобы ввести ограничения на ресурсы S1, S2, S3 поступают аналогично:

 Клик над словом Добавить справа от окна Ограничения.

На экране снова появится узкая таблица – диалоговое окно Добавление ограничения.

Ввести через подокна граничные условия на ресурсы (S1, S2, S3 <= 0). Для этого:

 Поместить курсор в левой части таблицы Добавление ограничения.

 Набрать адреса $E$9:$E$11.

 Поместить курсор в среднюю часть таблицы Добавление ограничения.

 Выбрать с помощью движка условие <=.

 Поместить курсор в правую часть таблицы Добавление ограничения.

 Набрать адреса $G$9:$G$11.

 Клик над ОК.


Таблица Добавление ограничения исчезнет. Снова появится таблица Поиск решения с второй строкой кода ограничений в окне Ограничения.

В ней отобразится код – $E$9:$E$11 >= $G$9:$G$11.

На этом ввод условий задачи заканчивается. Делается переход к решению задачи.

Поступая по аналогии, набирают остальные ограничения, если их больше.





Рис. 5  Таблица Параметры поиска решения

РЕШЕНИЕ ЗАДАЧИ


Решение задачи производится сразу же после ввода данных, когда на экране находится диалоговое окно Поиск решения.


Алгоритм:

 Клик над словом Параметры справа у края диалогового окна Поиск решения.

Появится таблица Параметры поиска решения (рис. 5).

 Клик в этой таблице над словами Линейная модель, что обеспечивает установку флажка и применение симплекс-метода.





Рис. 6 Таблица Excel с решением задачи


ОК в окне Параметры поиска решения.

Окно исчезнет. На экране снова отобразится диалоговое окно Поиск решения.

 Клик справа вверху окна над словом Выполнить.

Окно Поиск решения исчезнет.

Появится таблица Excel (рис. 6) с решением задачи, накрытая таблицей Результаты поиска решения (рис. 7).



Рис. 7  Окно Результаты поиска решения

 Клик над словами Сохранить найденное решение.

 Клик в подокне Тип отчета над словом Результаты.

 ОК слева внизу окна.

Окно Результаты поиска решения исчезнет.

Появится таблица Excel с решением задачи в форме. Готово.


Полученное решение означает, что объем производства продукции А1 равен трем единицам, а продукции А2 равен двум единицам. Прибыль от реализации составит 17 денежных единиц, а количество использованных ресурсов равно:

S1 = 5, S2 = 8, S3 = 7.





Рис. 8  Фрагмент Отчета по результатам


Отчеты о результатах и исследованиях


Просмотр отчета о полученных результатах.

Клик внизу таблицы на словах Отчет по результатам i, где i – номер отчета. Появится отчет о полученных результатах, фрагмент которого приведен на рис.8.

 Клик внизу отчета справа над Лист 1.

Отчет исчезнет. Снова появится таблица Excel с результатами в форме.

 Клик над Сервис в верхней части экрана в линейке инструментов.

Появится ниспадающее меню.

Клик над словами Поиск решения в нижней части меню.

Появится таблица Поиск решения (рис. 4А). В ней сохранится полученное ранее заполнений.

Для проверки:

 Клик над словом Параметры справа у края диалогового окна Поиск решения.

Появится таблица Параметры поиска решения (рис. 5).

Если в ней сохранилось заполнение:

ОК в окне Параметры поиска решения.

Окно исчезнет. На экране снова отобразится диалоговое окно Поиск решения.

 Клик справа вверху окна над словом Выполнить.

Окно Поиск решения исчезнет.

Появится таблица Excel (рис. 6) с решением задачи, накрытая таблицей Результаты поиска решения (рис. 7).

 Клик над словами Сохранить найденное решение.

 Клик в подокне Тип отчета над словом Устойчивость.

 ОК слева внизу окна.

Окно Результаты поиска решения исчезнет.

Появится таблица Excel с решением задачи в форме.




Рис. 9  Фрагмент Отчета по устойчивости

Клик внизу таблицы на словах Отчет по устойчивости.

Появится Отчет по устойчивости, фрагмент которого приведен на рис.9.

 Клик внизу отчета справа над Лист 1.

Отчет исчезнет. Снова появится таблица Excel с результатами в форме.

 Клик над Сервис в верхней части экрана в линейке инструментов.

Появится ниспадающее меню.

Клик над словами Поиск решения в нижней части меню.

Появится таблица Поиск решения (рис. 4). В ней сохранится полученное ранее заполнение.

Для проверки:

 Клик над словом Параметры справа у края диалогового окна Поиск решения.

Появится таблица Параметры поиска решения (рис. 5).

Если в ней сохранилось заполнение:

ОК в окне Параметры поиска решения.

Окно исчезнет. На экране снова отобразится диалоговое окно Поиск решения.

 Клик справа вверху окна над словом Выполнить.

Окно Поиск решения исчезнет.

Появится таблица Excel (рис. 6) с решением задачи, накрытая таблицей Результаты поиска решения (рис. 7).

 Клик над словами Сохранить найденное решение.

 Клик в подокне Тип отчета над словом Пределы.

 ОК слева внизу окна.

Окно Результаты поиска решения исчезнет.

Снова появится таблица Excel с решением задачи в форме.

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

 Клик внизу отчета справа над Лист 1.

Отчет исчезнет. Снова появится таблица Excel с результатами в форме.

КОНЕЦ.





Рис. 10  Фрагмент Отчета по пределам


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


1   2   3   4   5   6   7   8

Похожие:

Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconРоссийской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconРоссийской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconРоссийской федерации
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconРоссийской федерации
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconФедеральное государственное бюджетное образовательное учреждение высшего профессионального образования «томский государственный педагогический университет» (тгпу) «утверждаю» Проректор (декан факультета)
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconФедеральное государственное бюджетное образовательное учреждение высшего профессионального образования «томский государственный педагогический университет» (тгпу) программа дисциплины
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconМинистерство образования и науки российской федерации
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconФинансовый университет при правительстве российской федерации
Федеральное государственное образовательное бюджетное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconФинансовый университет при правительстве российской федерации
Федеральное государственное образовательное бюджетное учреждение высшего профессионального образования
Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования iconФинансовый университет при Правительстве Российской Федерации
Федеральное государственное образовательное бюджетное учреждение высшего профессионального образования
Разместите кнопку на своём сайте:
Документы


База данных защищена авторским правом ©kzdocs.docdat.com 2012
обратиться к администрации
Документы
Главная страница