Хамухин А.А. Решение оптимизационных задач в среде Microsoft Excel - файл n1.doc

Хамухин А.А. Решение оптимизационных задач в среде Microsoft Excel
Скачать все файлы (3444 kb.)

Доступные файлы (1):
n1.doc3444kb.31.03.2014 22:40скачать

n1.doc

  1   2   3   4   5
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

Государственное образовательное учреждение высшего профессионального образования

«НАЦИОНАЛЬНЫЙ ИССЛЕДОВАТЕЛЬСКИЙ

ТОМСКИЙ ПОЛИТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ»

А.А. Хамухин
РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ В СРЕДЕ

MICROSOFT EXCEL

Методические указания к выполнению лабораторных работ
для магистрантов

Издательство
Томского политехнического университета
2011

Содержание





Содержание 2

Введение 3

5

6

Лабораторная работа № 1. Оптимизация производства набора изделий для получения максимальной прибыли 9

Контрольные вопросы 13

Лабораторная работа № 2. Оптимизация графика работы персонала 14

Контрольные вопросы 17

Лабораторная работа № 3. Оптимизация производства и сбыта для минимизации затрат 18

Контрольные вопросы 23

Лабораторная работа № 4. Оптимизация планирования капиталовложений 24

Контрольные вопросы 32

Лабораторная работа № 5. Оптимизация кредитных операций 33

Контрольные вопросы 36

Лабораторная работа № 6. Оптимизация пенсионных накоплений 37

Контрольные вопросы 42

ЛИТЕРАТУРА 43

Введение


Приложение Excel имеет две мощные надстройки: «Анализ данных» и «Поиск решения». Применение надстройки «Анализ данных» позволяет извлекать дополнительную информацию из имеющихся числовых данных, но требует знания основ теории вероятностей и математической статистики. Надстройка «Поиск решения» предназначена для решения интуитивно понятной всем задачи – поиска оптимального значения (минимума или максимума) заданной функции, которую называют целевой, при наличии некоторых ограничений на варьируемые параметры. Например, при покупке продуктов на рынке ограничениями могут быть: список продуктов, которые надо купить; имеющиеся в наличии деньги; время нахождения на рынке; вес купленных продуктов, который мы можем унести. Целевая функция наверное почти у всех будет одинакова: минимум истраченных на покупку денег. Пример целевой функции, для которой мы всегда ищем максимальное значение, наблюдается в задаче трудоустройства. Человек всегда ищет работу с максимальной зарплатой, но при наличии целого ряда ограничений: образование, стаж предыдущей работы, возраст и т.п.

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

Изначально надстройка «Поиск решения» не видна в меню и ее необходимо активировать с помощью меню Файл–Параметры–Надстройки

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

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

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



После этого можно сохранить найденное решение.



Теперь потренируемся задавать ограничения. Например, потребуем, чтобы аргумент был не менее 7. Для этого в поле ограничений (через кнопку «Добавить») укажем адрес аргумента (А20) и адрес конкретного ограничения (С20). В ячейку А20 занесем адрес изменяемой ячейки (-А17).

Теперь результат работы надстройки будет уже другим.

Задание: сделать тоже самое для целевой функции с двумя аргументами.

  1   2   3   4   5
Учебный текст
© perviydoc.ru
При копировании укажите ссылку.
обратиться к администрации