Решение задачи оптимизации в Microsoft Excel
Инструкция по использованию Microsoft Excel для решения задач. Создание экранной формы, ввод условия задачи. Определение зависимости для левых частей ограничений, задание знаков ограничений. Ввод зависимостей из математической модели в экранную форму.
| Рубрика | Программирование, компьютеры и кибернетика |
| Вид | реферат |
| Язык | русский |
| Дата добавления | 26.09.2016 |
| Размер файла | 1,1 M |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Размещено на http://www.allbest.ru/
Решение задачи оптимизации в Microsoft Excel
Задачу оптимизации в Microsoft Excel решают в программе Поиск решения. Средство поиска решения Microsoft Excel использует алгоритм нелинейной оптимизации Generalized Reduced Gradient (GRG2), разработанный Леоном Ласдоном (Leon Lasdon, University of Texas at Austin) и Аланом Уореном (Allan Waren, Cleveland State University). Алгоритмы симплексного метода и метода «branch-and-bound» для решения линейных и целочисленных задач с ограничениями разработаны Джоном Уотсоном (John Watson) и Деном Филстра (Dan Fylstra) из Frontline Systems, Inc. Чтобы получить более подробные сведения об используемых алгоритмах оптимального поиска, обратитесь по адресу:
The solution of the optimization problem in Microsoft Excel
The problem of optimization in Microsoft Excel program to solve the solution search. Finder Microsoft Excel uses an algorithm to solve nonlinear optimization Generalized Reduced Gradient (GRG2), designed by Leon Lasdonom (Leon Lasdon, University of Texas at Austin) and Alan Warren (Allan Waren, Cleveland State University). Algorithms simplex method and the method «branch-and-bound» for solving linear and integer problems with restrictions designed by John Watson (John Watson) and Filstra Dan (Dan Fylstra) from Frontline Systems, Inc. To get more detailed information about the algorithms used by the search of optimal, please contact:
Frontline Systems, Inc.
P.O. Box 21288
Incline Village, NV 892150-21288
(775) 831-0300
Адрес в Интернете: http://www.frontsys.com
Электронная почта: info@frontsys.com
Web Address: http://www.frontsys.com
E-mail: info@frontsys.com
Оптимизация модели с помощью процедуры поиска решения:
Model Optimization by searching procedures solutions:
1. В меню Сервис выберите команду Поиск решения.
1. On the Tools menu, click Solver.
2. Если команда Поиск решения отсутствует в меню Сервис, установите «Поиск решения».
2. If the command (team) search for a solution is not on the Tools menu,
Set up command "Search for solutions”.
Инструкции Instructions
1. В меню Сервис выберите команду Надстройки.
1. On the Tools menu, click Add-Ins.
2. Нажмите кнопку Обзор, чтобы найти надстройку (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.), которой нет в окне Список надстроек.
2. Click the Browse button to locate the add-in (add-in. The auxiliary program that serves to add to the Microsoft Office-specific commands or options.), Which is not in the list of add-ons window.
3. Установите в окне Список надстроек флажок надстройки «Поиск решения» которую необходимо загрузить, а затем нажмите кнопку OK.
3. Install in the window Add-on List "Solver" add-in check box that you want to upload, and then click OK.
4. Следуйте инструкциям программы установки, если они имеются.
4. Follow the installation instructions, if any.
Инструкция по использованию Microsoft Excel для решения задач
Instructions on the use of Microsoft Excel to solve problems
Для того чтобы решить задачу ЛП в табличном редакторе Microsoft Excel, необходимо выполнить следующие действия. In order to solve the problem of LP in the table Microsoft Excel editor, follow these steps.
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
· переменных,
· целевой функции (ЦФ),
· ограничений,
· граничных условий;
1. Enter the condition of the problem:
a) create a screen form for entering the conditions of the problem:
· variables,
· objective function (OF)
· restrictions
· boundary conditions;
b) ввести исходные данные в экранную форму:
· коэффициенты ЦФ,
· коэффициенты при переменных в ограничениях,
· правые части ограничений;
b) enter the initial data in the screen form:
* OF rates (coefficients),
* coefficients of the variables in the constraints (restrictions);
* the right part of the restrictions;
c) ввести зависимости из математической модели в экранную форму:
· формулу для расчета ЦФ,
· формулы для расчета значений левых частей ограничений;
c) introduce dependence of the mathematical model in the screen form:
* formula for the calculation of OF,
* formulas for calculating the values of the left sides of limitations;
d) задать ЦФ (в окне "Поиск решения"):
d) set the OF (in "Finding the solution"):
· направление оптимизации ЦФ;
ввести ограничения и граничные условия (в окне "Поиск решения"): целевую ячейку
* optimization direction DF;
* impose restrictions and boundary conditions (in the "Solver") the target cell,
· ячейки со значениями переменных,
· граничные условия для допустимых значений переменных,
· соотношения между правыми и левыми частями ограничений.
* with the values of the variables of the cell,
* boundary conditions for acceptable values of the variables,
* the ratio between the right and left sides of restrictions.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) выбрать формат вывода решения (в окне "Результаты пои
c) запустить задачу на решение (в окне "Поиск решения");
d) ска решения").
2. Solve the problem:
a) set the parameters of the solution of the problem (in the "Solver");
b) select the output format of the decision (on the "poi Results
c) start a task to solve (in the "Solver");
d) ska solutions ").
Ввод исходных данных
Создание экранной формы и ввод в нее условия задачи
Экранная форма для ввода условий исходной задачи вместе с введенными в нее исходными данными представлена на рис.1.
Рис.1. Экранная форма исходной задачи (курсор в ячейке P19)
В экранной форме на рис.1 каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка в Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект задачи ЛП.
Так, например, переменным исходной задачи соответствуют ячейки C21(), D21(), E21 (), F21 (), G21 (), H21 (), I21 (), J21 (), K21 (), L21 (), M21() и N21 () коэффициентам системы ограничений матрицы исходной задачи соответствуют диапазон ячеек C5:N18, коэффициентам целевой функции соответствуют диапазон ячеек C19:N19(30,22,5, 30, 27 и т.д.), правым частям ограничений соответствуют ячейки Q5 (200), Q6 (400), Q7 (300) и т.д.
Примечание1. Символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись C19:N19 указывает на ячейки C19, D19, E19, F19, G19, H19, I19, J19, K19, L19 M19 и N19).
Ввод зависимостей из математической модели в экранную форму
Зависимость для целевой функции
В ячейку P19, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно исходной задачи значение целевой функции определяется выражением
.(1.2)
Используя обозначения соответствующих ячеек в Excel (см. рис.1.), формулу для расчета ЦФ (1.2) можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи в диапазоне ячеек C21:N21, на соответствующую ячейку P19, отведенную для коэффициентов ЦФ C19:N19, то есть
.(1.3)
Чтобы задать формулу (1.3) необходимо в ячейку P19 ввести следующее выражение и нажать клавишу "Enter"
=СУММПРОИЗВ(C19:N19;C$21:N$21), (1.4)
где символ $ перед номером строки 21 означает, что при копировании этой формулы в другие места листа Excel номер строки 21 не изменится;
После этого в целевой ячейке появится 0 (нулевое значение) (рис.1.).
Примечание 2. Существует другой способ задания функций в Excel с помощью режима "Вставка функций", который можно вызвать из меню "Вставка" или при нажатии кнопки "" на стандартной панели инструментов. Так, например, формулу (1.4) можно задать следующим образом:
· курсор в поле F6;
· нажав кнопку "", вызовите окно "Мастер функций - шаг 1 из 2";
· выберите в окне "Категория" категорию "Математические";
· в окне "Функция" выберите функцию СУММПРОИЗВ;
· в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение C19:N19, а в строку "Массив 2" - выражение C$21:N$21 (рис.1.2);
· после ввода ячеек в строки "Массив 1" и "Массив 2" в окне "СУММПРОИЗВ" появятся числовые значения введенных массивов (см. рис.1.2), а в экранной форме в ячейке P19 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).
Рис.1.2. Ввод формулы для расчета ЦФ в окно "Мастер функций"
Зависимости для левых частей ограничений
Левые части ограничений задачи (1.1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи C21:N21, на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (C5:N5 - 1-е ограничение, C6:N6 - 2-е ограничение, C7:N7 - 3-е ограничение т.д.). Формулы, соответствующие левым частям ограничений, представлены в табл.1.1.
Таблица 1.1
Формулы, описывающие ограничения модели (1.1)
|
Левая часть ограничения |
Формула Excel |
|
|
или |
=СУММПРОИЗВ(С$21:N$21;C5:N5) |
|
|
или |
=СУММПРОИЗВ(С$21:N$21;C6:N6) |
|
|
или |
=СУММПРОИЗВ(С$21:N$21;C7:N7) |
Как видно из табл.1.1, формулы, задающие левые части ограничений задачи (1.1), отличаются друг от друга и от формулы (1.4) в целевой ячейке P19 только номером строки во втором массиве. Этот номер определяется той строкой, в которой ограничение записано в экранной форме. Поэтому для задания зависимостей для левых частей ограничений достаточно скопировать формулу из целевой ячейки в ячейки левых частей ограничений.
Дальнейшие действия производятся в окне "Поиск решения", которое вызывается из меню "Сервис" (рис.1.6):
· поставьте курсор в поле "Установить целевую ячейку";
· введите адрес целевой ячейки $P$19 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме это будет равносильно вводу адреса с клавиатуры;
введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "максимальному значению".
Рис.1.6. Окно "Поиск решения" задачи (1.1)
Ввод ограничений и граничных условий
Задание ячеек переменных
В окно "Поиск решения" в поле "Изменяя ячейки" впишите адреса $C$21:$N$21. Необходимые адреса можно вносить в поле "Изменяя ячейки" и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Задание граничных условий для допустимых значений переменных
В нашем случае на значения переменных накладывается только граничное условие неотрицательности, то есть их нижняя граница должна быть равна нулю (см. рис.1.1).
· Нажмите кнопку "Добавить", после чего появится окно "Добавление ограничения" (рис.1.7).
· В поле "Ссылка на ячейку" введите адреса ячеек переменных $P$21:$N$21. Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.
· В поле знака откройте список предлагаемых знаков и выберите .
В поле "Ограничение" введите адреса ячеек нижней границы значений переменных, то есть $P$4:$P$17. Их также можно ввести путем выделения мышью непосредственно в экранной форме.
Рис.1.7. Добавление условия задачи (1.1)
Задание знаков ограничений , , =
· Нажмите кнопку "Добавить" в окне "Добавление ограничения". microsoft excel ограничение экранный
· В поле "Ссылка на ячейку" введите адрес ячейки левой части конкретного ограничения, например $P$18. Это можно сделать как с клавиатуры, так и путем выделения мышью нужной ячейки непосредственно в экранной форме.
· В соответствии с условием задачи (1.1) выбрать в поле знака необходимый знак, например =.
· В поле "Ограничение" введите адрес ячейки правой части рассматриваемого ограничения, например $Q$18.
· Аналогично введите ограничения: $P$17>=$Q$17, $P$16<=$Q$16.
· Подтвердите ввод всех перечисленных выше условий нажатием кнопки OK.
Окно "Поиск решения" после ввода всех необходимых данных задачи (1.1) представлено на рис.1.6.
Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки "Изменить" или "Удалить" (см. рис.1.6).
Решение задачи
Установка параметров решения задачи
Задача запускается на решение в окне "Поиск решения". Но предварительно для установления конкретных параметров решения задач оптимизации определенного класса необходимо нажать кнопку "Параметры" и заполнить некоторые поля окна "Параметры поиска решения" (рис.1.8).
Рис.1.8. Параметры поиска решения, подходящие для большинства задач ЛП
Параметр "Максимальное время" служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).
Параметр "Предельное число итераций" служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.
Параметр "Относительная погрешность" служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.
Параметр "Допустимое отклонение" служит для задания допуска на отклонение от оптимального решения в целочисленных задачах. При указании большего допуска поиск решения заканчивается быстрее.
Параметр "Сходимость" применяется только при решении нелинейных задач.
Установка флажка "Линейная модель" обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.
Подтвердите установленные параметры нажатием кнопки "OK".
Запуск задачи на решение
Запуск задачи на решение производится из окна "Поиск решения" путем нажатия кнопки "Выполнить".
После запуска на решение задачи ЛП на экране появляется окно "Результаты поиска решения" с одним из сообщений, представленных на рис.1.9, 1.10 и 1.11.
Рис.1.9. Сообщение об успешном решении задачи
В окне "Результаты поиска решения" представлены названия трех типов отчетов: "Результаты", "Устойчивость", "Пределы". Они необходимы при анализе полученного решения на чувствительность. Для получения же ответа (значений переменных, ЦФ и левых частей ограничений) прямо в экранной форме просто нажмите кнопку "OK". После этого в экранной форме появляется оптимальное решение задачи (рис.1.12).
Размещено на Allbest.ru
...Подобные документы
Особенности решения задач линейного программирования (ЛП) в табличном редакторе Microsoft Excel. Создание экранной формы для ввода условия задачи. Ограничения и граничные условия, перенесение зависимостей из математической модели в экранную форму.
лабораторная работа [160,5 K], добавлен 26.05.2015Пакет Microsoft Office. Электронная таблица MS Excel. Создание экранной формы и ввод данных. Формулы и функции. Пояснение пользовательских функций MS Excel. Физическая постановка задач. Задание граничных условий для допустимых значений переменных.
курсовая работа [3,4 M], добавлен 07.06.2015Построение и использование математических и алгоритмических моделей для решения линейных оптимизационных задач. Освоение основных приемов работы с инструментом "Поиск решения" среды Microsoft Excel. Ввод системы ограничений и условий оптимизации.
лабораторная работа [354,7 K], добавлен 21.07.2012Решение задачи расчета структуры и объема товарооборота методом линейного программирования. Формулы ограничений, транспортная задача оптимизации доставки товаров. Решение задачи о назначениях на основе матрицы стоимостей в электронной таблице Excel.
контрольная работа [1023,6 K], добавлен 27.05.2013Краткие сведения об электронных таблицах MS Excel. Решение задачи линейного программирования. Решение с помощью средств Microsoft Excel экономической оптимизационной задачи, на примере "транспортной задачи". Особенности оформления документа MS Word.
курсовая работа [1,1 M], добавлен 27.08.2012Основные приемы работы в Excel. Селекция блока ячеек, ввод данных. Копирование формул, установка границ ячеек. Изменение ширины столбца. Решение транспортной задачи: ввод исходных данных, формирование элементов математической модели и целевой функции.
курсовая работа [2,4 M], добавлен 30.01.2012Microsoft Office как семейство программных продуктов Microsoft, его возможности и функции. Решение пользовательских задач с помощью встроенных функций Excel, создание базы данных. Формирование блок-схемы алгоритма с использованием Microsoft Visio.
контрольная работа [1,4 M], добавлен 28.01.2014Знакомство с программой Microsoft Office Excel. Табличный процессор. Ввод данных в таблицу. Работа с буфером и формулами. Относительная и абсолютная адресация. Диаграммы и графики. Создание информационной системы средствами Microsoft Office Excel.
методичка [1,9 M], добавлен 12.05.2008Особенности использования электронной таблицы Microsoft Excel для решения оптимизационных задач. Выполнение команды "Поиск решения" в меню "Сервис". Запись ограничений через использование кнопки "Добавить". Сообщение о найденном решении на экране.
лабораторная работа [4,5 M], добавлен 03.08.2011Принцип работы и особенности интерфейса табличного процессора Microsoft Office Excel. Описание правил адресации ячеек, освоение приемов их выделения и заполнения данными. Обобщение приемов ввода и редактирования данных в ячейках. Элементы окна Excel.
лабораторная работа [2,7 M], добавлен 15.11.2010Решение в среде Microsoft Excel с помощью программной модели "Поиск решения" транспортной задачи, системы нелинейных уравнений, задачи о назначениях. Составление уравнения регрессии по заданным значениям. Математические и алгоритмические модели.
лабораторная работа [866,6 K], добавлен 23.07.2012История использования механических и полуавтоматических средств для арифметических операций. Работа с табличным процессором Microsoft Excel. Поиск и замена данных в таблице Microsoft Access. Сортировка записей в запросе, его создание с помощью мастера.
контрольная работа [22,8 K], добавлен 13.01.2010Правила работы с текстовым редактором Microsoft Word, оценка его возможностей и перспектив развития. Ввод текста и закономерности работы с таблицами. Создание документа "Рекламный лист". Особенности работы с табличным редактором MS Excel, создание диаграм
методичка [6,4 M], добавлен 15.02.2011Характеристика влияния компьютера на здоровье человека. Определение корней уравнения в Microsoft Excel с точностью до шестого знака после запятой. Решение системы линейных уравнений методом вычисления определителей и матричным способом в Microsoft Excel.
контрольная работа [734,0 K], добавлен 19.03.2012Ввод, редактирование и форматирование данных в табличном редакторе Microsoft Excel, форматирование содержимого ячеек. Вычисления в таблицах Excel при помощи формул, абсолютные и относительные ссылки. Использование стандартных функций при создании формул.
контрольная работа [430,0 K], добавлен 05.07.2010Проектирование редактора журнала по информатике, выплачивающего гонорар за статьи, предоставленные авторами для номера. Задание на применение относительных и абсолютных ссылок. Последовательность действий по формированию таблиц. Ввод данных для расчетов.
курсовая работа [4,5 M], добавлен 04.05.2014Использование электронных таблиц Microsoft Excel в решении производственных задач. Определение инерционных характеристик главного вала горячештамповочного автомата. Обработка эксперимента по определению приведенного модуля объемной упругости жидкости.
методичка [429,3 K], добавлен 06.06.2011Программа Microsoft Excel для работы с таблицами данных и формулами. Абсолютные и относительные ссылки. Использование мастера функций, ввод ее параметров. Суммирование, построение диаграмм и графиков. Арифметические и логические табличные формулы.
курсовая работа [47,3 K], добавлен 28.11.2009Ввод информации на рабочий лист. Документы предметной области, содержащие информацию, необходимую для оформления товарно-транспортной накладной. Общее понятие о Microsoft Excel: автоматизация, создание формул. Технология создания шаблона документа.
контрольная работа [332,1 K], добавлен 18.11.2012Функциональные возможности табличного процессора Microsoft Excel. Понятия программы создания электронных таблиц. Ввод данных в ячейки. Вычисления в таблицах, форматирование ячеек. Особенности построения диаграмм. Использование стандартных функций.
презентация [723,9 K], добавлен 31.10.2016
