41815

Задачи оптимизации в EXCEL

Лабораторная работа

Информатика, кибернетика и программирование

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

Русский

2013-10-25

50.22 KB

75 чел.

МИНИСТЕРСТВО ОБРАЗОВАНИЯ НАУКИ РОССИЙСКОЙ ФДЕРАЦИИ ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССНАНГО ОБРАЗОВАНИЯ «УФИМСКАЯ ГОСУДАРСТВЕННАЯ АКАДЕМИЯ ЭКОНОМИКИ И СЕРВИСА»

(УГАЭС)

Кафедра гуманитарных, естественнонаучных, математических и социально-экономических дисциплин

ЛАБОРАТОРНАЯ РАБОТА №7

По дисциплине: «Информационные технологии»  

На тему: «Задачи оптимизации в EXCEL»

Выполнил:

студент группы 2БЭД-11

Горябина О.С.

Проверил: Шаванова В.Г.

2012


Оглавление

Теоретическая часть 3

Решение уравнений и задач оптимизации 3

Подбор параметров 3

Диспетчер сценариев «что  если» 4

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

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

Создание отчётов по сценарию 5

Алгоритм выполнения работы 6


Теоретическая часть

Решение уравнений и задач оптимизации

Для решения задач оптимизации широкое применение находят различные средства Excel. В этом разделе рассмотрим команды:

  1. Подбор параметров для нахождения значения, приводящего к требуемому  результату.
  2. Надстройку Поиск решения для расчёта оптимальной величины по нескольким переменным и ограничениям.
  3. Диспетчер сценариев для создания и оценки наборов сценариев «что  если» с несколькими вариантами исходных данных.

Подбор параметров

Основной командой для решения оптимизационных задач в Excel является команда  Сервис/Подбор параметров параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату.

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка.

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

  1. формула для расчёта;
  2. пустая ячейка для искомого значения;
  3. другие величины, которые используются в формуле.

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

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдёт решения, лежащие в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления).

Диспетчер сценариев «что  если»

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

Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что  если». Командой Сервис/Сценарий можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчёты.

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

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

Создание сценариев происходит следующим образом:

  1. Выполните команду Сервис/Сценарий. Открывается изображение окна диалога Диспетчер сценариев.
  2. Нажмите кнопку Добавить, чтобы создать первый сценарий. Откроется окно диалога Добавление сценария.
  3. Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.
  4. Нажмите кнопку Добавить, чтобы создать второй сценарий. Введите название Худший вариант. После завершения создания двух сценариев можно приступить к просмотру результатов.
  5. Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

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

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

  1. Выполните команду Сервис/Сценарий. Открывается окно диалога.
  2. Выберите из списка сценарий для просмотра.
  3. Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.
  4. Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что  если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Создание отчётов по сценарию

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

Эту задачу можно выполнить с помощью кнопки Отчёт в окне диалога Диспетчер сценариев. Созданный сводный отчёт будет автоматически отформатирован и скопирован на новый лист текущей книги.

Создание отчёта по сценарию происходит следующим образом:

  1. Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.
  2. Нажмите кнопку отчёт. Открывается окно диалога Отчёт по сценарию, в котором предлагается выбрать ячейки, входящие в отчёт, а также его тип. Отчет типа структура представляет собой форматированную таблицу, которая выводится на отдельном листе. Отчёт сводная таблица является специальной таблицей, которую можно настраивать за счёт перестановки столбцов и строк.

Алгоритм выполнения работы

  1. Разобрать условие задачи
  2. Построить математическую модель
  3. Заполнить таблицу, указав произвольные значения для поисковых переменных.
  4. Найти оптимальное решение, для этого:
  5. выделить целевую ячейку В7
  6. выбрать Сервис, Поиск решения (если Поиска решения нет, установить с помощью ).
  7. установить целевую ячейку, равную минимальному значению
  8. указать мышью диапазон изменяемых ячеек
  9. выбрать кнопку Добавить для записи ограничений
  10. после записи ограничения нажать Добавить (для последнего ограничения – ОК)
  11. нажать кнопку Выполнить
  12. выбрать Тип отчета, Результаты и нажать ОК.
  13. Проанализировать полученные результаты.


Таблица 1

Поисковые переменные

поисковые переменные

 

 

 

 

имя

значение

 

 

 

a

18,1

 

 

 

b

7,6

 

 

 

S1

10

 

 

 

критерий оптимизации

 

 

 

 

n

м

 

13,756

 

 

 

 

 

 

 

 

 

 

 

посковые переменные

 

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

 

 

имя

значение

имя

формула

максимальное значение

a

10

V

2000

2000

b

10

 

 

 

h

20

 

 

 

 

 

 

 

 

площадь поверхности (критерий оптимизации)

 

 

 

 

имя

формула

 

 

 

S

900

 

 

 

Таблица 2

Отчёт


 

А также другие работы, которые могут Вас заинтересовать

40109. Методы штрафных функций и методы центров в выпуклом программировании 90 KB
  Методы штрафных функций и методы центров в выпуклом программировании Метод штрафных функций Постановка задачи Даны непрерывно дифференцируемые целевая функция fx = fx1 xn и функции ограничений gjx = 0 j = 1 m; gjx 0 j = m1 p определяющие множество допустимых решений D. Требуется найти локальный минимум целевой функции на множестве D т. Стратегия поиска Идея метода заключается в сведении задачи на условный минимум к решению последовательности задач поиска безусловного минимума вспомогательной функции: Fx Ck =...
40110. Методы наискорейшего и координатного спуска для минимизации выпуклой функции без ограничений. Их алгоритмы и геометрическая интерпретация 94.5 KB
  Все методы спуска решения задачи безусловной минимизации различаются либо выбором направления спуска, либо способом движения вдоль направления спуска. Решается задача минимизации функции f(x) на всём пространстве Rn. Методы спуска состоят в следующей процедуре построения последовательност
40111. Субградиент как обобщение понятия градиента. Субградиент для функции максимума. Субградиентный метод и его геометрическая интерпретация в R2 141 KB
  Субградиент для функции максимума. Градиентом дифференцируемой функции fx в точке называется вектор частных производных.x0 y0 а значение lim называется частной производной функции f по x в т. Вектор называется субградиентом опорным вектором функции fx в точке если выполняется: Таких с множество но это множество ограничено и замкнуто.
40112. Типичные производственные функции с несколькими ресурсами: линейная ПФ, степенная ПФ, ПФ с постоянными пропорциями. Коэффициенты эффективности использования ресурсов для этих типов функций 162 KB
  Коэффициенты эффективности использования ресурсов для этих типов функций. Производственные возможности н х в любой момент времени определяются 2мя группами факторов: технологические условия производства которые выражают зависимости между затратами разных ресурсов и выпуском продукции объем и качество используемых ресурсов fx производственная функция зависимость результата производства объема выпуска продукции от затрат ресурсов. X = х1 хm вектор затрат ресурсов. ПФ характеризует максимально возможный выпуск продукции при...
40113. Показатели эффективности использования производственных ресурсов (коэффициенты средней и предельной эффективности). Коэффициент эластичности выпуска. Вычисление этих показателей для степенной производственной функции 134.5 KB
  Средняя эффективность использования ресурсов показывает отдачу от каждой единицы iго ресурса. Предельная эффективность показывает предельный прирост выпуска продукции при увеличении затрат iго ресурса на малую величину. При этом важен характер изменения эффективности дополнительных количеств используемого ресурса. Если найдем максимальный то определим от какого ресурса получим наибольшую отдачу т.
40114. Модель оптимального поведения потребителей на рынке товаров в условиях товарно-денежных отношений 85.5 KB
  Модель оптимального поведения потребителей на рынке товаров в условиях товарноденежных отношений. Исследуется поведение некоторой группы потребителей на рынке на котором представлены n товаров которые будем обозначать: y = y1 yn набор товаров услуг р = р1 рn заданные цены на товары услуги. Тогда задача имеет вид: Графическая интерпретация для случая двух товаров: Линии уровня имеют такой вид так как чем больше потребитель потребляет товар тем менее предпочтительным он становится Присутствующий в модели принцип...
40115. Вариантная задача развития и размещения производства. Метод коэффициентов интенсивности 98 KB
  Отраслевая модель перспективного планирования разрабатывается на 5-15 лет. В пределах этого времени очень часто показатели принимаются за постоянные. Если же относительно некоторых экономических показателей нельзя сделать предположение о постоянстве, то учитывается изменение во времени за некоторый период времени. При этом показатели вычисляются приближенно с помощью коэффициента дисконтирования.
40116. Модель с фиксированным размером заказа 51 KB
  Модель с фиксированным размером заказа Целесообразность создания запасов: 1 наличие запасов позволяет быстро удовлетворять потребности потребителей. В рассматриваемой системе размер заказа является постоянной величиной и повторный заказ подается при условии что уровень наличных заказов снижается до определенного критического уровня который в теории управления запасами называется точкой заказа. Система с фиксированным размером заказа основана на выборе размера партии минимизирующего общие издержки управления запасами. При этом...
40117. Модель с фиксированным уровнем запасов 44.5 KB
  Модель с фиксированным уровнем запасов основана на фиксированных моментах подачи заказа. В модели издержки управления запасами в явном виде не рассматриваются и фиксированный размер заказа отсутствует. Mx уровень запасов M определяется по формуле: М = В SL L R 1 где L время выполнения заказа R интервал м у проверками 0 R 2R моменты проверки наличия товара на складе 0 L R L 2R L моменты поставки заказа. примерно в случаев фактический сбыт за время доставки заказа м.