Методы одномерной оптимизации в эксель

Обновлено: 06.01.2025

Оптимизационные модели при экономической и технической сфере.

Цель занятия: подобрать сбалансированное решение, оптимальное в конкретных условиях (количество продаж для получения определенной выручки, лучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации используются следующие команды:

Подбор параметров («Данные» - «Работа с данными» - «Анализ «что-если»» - «Подбор параметра») – находит значения, которые обеспечат нужный результат.

Поиск решения (надстройка Microsoft Excel; «Данные» - «Анализ») – рассчитывает оптимальную величину, учитывая переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».

Диспетчер сценариев («Данные» - «Работа с данными» - «Анализ «что-если»» - «Диспетчер сценариев») – анализирует несколько вариантов исходных значений, создает и оценивает наборы сценариев.

Для решения простейших задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Рассмотрим пример решения оптимизационной задачи с помощью надстройки «Поиск решения».

Условие. Фирма производит несколько сортов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 сом. «2» - 250 сом. «3» - 300 сом. Сбыт, налажен, но количество имеющегося сырья ограничено. Нужно найти, какой йогурт и в каком объеме необходимо делать, чтобы получить максимальный доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

Количество изделий нам пока неизвестно. Это переменные.

В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.

Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). То есть мы норму расхода умножили на количество.

Цель – найти максимально возможную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим параметры.

После нажатия кнопки «Выполнить» программа выдает свое решение.

Оптимальный вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» производить не стоит.

Лазаренко Виктория Сергеевна студент 4 курса, Ставропольский государственный педагогический институт, РФ, г. Ставрополь Кузина Наталья николаевна научный руководитель, Старший преподаватель кафедры математики и информатики, Ставропольский государственный педагогический институт, РФ, г. Ставрополь

« РЕАЛИЗАЦИЯ МНОГОМЕРНЫХ ЗАДАЧ ОПТИМИЗАЦИИ В excel» Аннотация: В статье рассмотрены основные методы многомерной оптимизации, ПРИВЕДЁН ПРИМЕР РЕАЛИЗАЦИИ ЗАДАЧИ БЕЗУСЛОВНОЙ ОПТИМИЗАЦИИ (МЕТОДА ПОКООРДИНАТНОГО СПУСКА) В среде Excel. КЛЮЧЕВЫЕ СЛОВА: многомерная оптимизация, условная оптимизация, БЕЗУСЛОВНАЯ ОПТИМИЗАЦИЯ.

Изучение задач многомерной оптимизации заключается в нахождении точек минимума функции почти всех переменных на всем промежутке определенной размерности.

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

В данной статье мы рассмотрим численные методы оптимизации, т.е. способы построения алгоритмов нахождения оптимального (минимального или максимального) познания некой функции и для наглядности реализуем пример многомерной оптимизации в Excel. Ведь Excel – это одна из наиболее узнаваемых и известных программ, возможности которой разрешают стремительно отыскивать действенное решение, начиная с математических задач и заканчивая применением оптимизации в самых различных сферах работе человека.

Численные методы многомерной оптимизации

Существуют разные методы безусловной и условной оптимизации. К безусловной оптимизации относятся методы: покоординатного спуска, метод наискорейшего спуска и подпрограмма EXCEL “Поиск решения”. Линейное программирование, метод штрафных функций и подпрограмма EXCEL “Поиск решения” – это условная оптимизация.

Суть методов безусловной оптимизации состоит в поиске минимуму функции методом неоднократных вычислений, при разных значениях характеристик х = k>, где k = 0, 1, 2, . Методы условной оптимизации применяются при наличии ограничений. Задача условной оптимизации заключается в поиске минимального или максимального значения скалярной функции f(x) n-мерного векторного аргументах. Решение задачи основывается на линейной или квадратичной аппроксимации целевой функции для определения приращений x₁, …,x_n на каждой итерации.

Рассмотрим более подробно безусловную оптимизацию, её методы и реализацию примера в Excel.

Метод покоординатного спуска (метод прямого поиска), в котором используются лишь значения целевой функции. Чтобы воспользоваться этим методом, необходимо иметь такие исходные данные, как: формулу целевой функции, имеющую вид f(X₁,X₂, . , Xn); Е - точность нахождения значений независимых переменных, при которых функция достигает минимума; начальные приближения X₁₀,X₂₀ . , Xn₀.

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

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

Условие задачи: число независимых переменных равняется двум. Ограничения отсутствуют. Требуется найти минимум функции

из начальной точки (0,5;0,5) c точностью 0,0001. Проанализировав функцию, заметим, что она будет иметь минимум, равный нулю. Для чего и первое, и второе слагаемое тоже должны быть равны нулю. Откуда координаты точки минимума (1;1).

Решим эту задачу на EXCEL. Откроем новый рабочий лист, где столбец А -значения X₁, столбец В - значения X₂, а столбец С - значения целевой функции и, наконец, столбец D - значения погрешности D.

Занесем в ячейки А3 и В3 значения начальных приближений, равных 0,5 и в ячейку С3 формулу =(В3-А3^2)^2+(1-A3)^2. Скопируем эту формулу в блок ячеек С4:С17. На этом заканчивается подготовительный этап.

Опишем первую итерацию, для этого скопируем содержимое ячейки В3 в ячейку В4. Сделаем текущей ячейку С4. Процесс одномерной оптимизации для нахождения X₁ выполним с помощью подпрограммы EXCEL «Поиск решения». Вызовем эту подпрограмму командой меню Сервис - Поиск решения.

Теперь скопируем содержимое ячейки А4 в ячейку А5. Сделаем текущей ячейку С5. Зададим команду меню Сервис - Поиск решения. В открывшемся диалоге в поле Установить целевую ячейку занесем адрес С5, а в поле Изменяя ячейки - адрес В5. В результате в ячейке В5 получим числовое значение, при котором целевая функция достигает минимального значения в ячейке С5 по координате X₂.

Вычислим погрешности решения на первом шаге, для этого внесём в ячейку D5 формулу =ABS(A3-A5)+ABS(B3-B5). На этом заканчивается первая итерация.

Вторая и все последующие итерации проводятся аналогично, но с учетом соответствующих адресов ячеек.

Можно построить диаграмму изменения на каждой итерации, на которой видны перпендикулярные ломаные линии движения от точки к точке параллельно одной из осей координат.

Реализация метода покоординатного спуска представлена в Excel на рисунке 1.

Рисунок 1. Метод покоординатного спуска в Excel

Можно сказать, что сегодня оптимизационные задачи и задачи принятия решений моделируются и решаются в самых различных областях[1], в связи с этим растёт необходимость развития математического аппарата оптимизации. Решение задач с помощью Excel, позволяем наглядно разобраться с задачей и решить её.

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

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

1. Корнеенко В. П. Методы оптимизации: Учебник / В. П. Корнеенко. - М.: Высш. шк., 2007.

2. Пантелеев А. В. Методы оптимизации в примерах и задачах: Учеб. пособие / А. В. Пантелеев, Т. А. Летова. - М.: Высш. шк., 2005.

3. Батищев Д. И. Оптимизация в САПР: Учебник / Д. И. Батищев, Я. Е. Львович, В. Н. Фролов. - Воронеж: Изд-во ВГУ, 1997.

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

Бундаев В.В. Решение задач линейной оптимизации с использованием MathCad и Excel

формат pdf
размер 373.14 КБ
добавлен 26 марта 2011 г.

Методическое пособие и контрольные задания. - Улан-Удэ: Издательство ВСГТУ, 2006. - 30 с. Методическое пособие содержит необходимые теоретические сведения и практический материал для решения задач линейного программирования с использованием возможностей математического пакета Mathcad и табличного процессора Excel. На конкретных типовых примерах подробно разобраны порядок выполнения лабораторных работ по линейной оптимизации. В целях закрепления п.

Лабораторная работа - Безусловная многомерная оптимизация

формат doc
размер 91.71 КБ
добавлен 17 декабря 2009 г.

УГАТУ, Преподаватель: Хасанов А. Ю. Безусловная многомерная оптимизация по дисциплине "Методы оптимизации" Реализовано 6 методов: а) градиентный метод с постоянным шагом; б) градиентный метод с дроблением шага; д) метод Гаусса-Зейделя (с использованием метода Золотого сечения); е) эвристический алгоритм; к) метод конфигураций; л) метод симплекса. Программы написаны на C++. Цель работы: знакомство с методами многомерной безусловной оптимизации.

Лабораторная работа - Безусловная одномерная оптимизация

формат xls, doc
размер 69 КБ
добавлен 17 декабря 2009 г.

УГАТУ, Преподаватель: Хасанов А. Ю. по дисциплине "Методы оптимизации". Реализовано 5 методов: а) алгоритм блочного равномерного поиска; б) метод деления интервала по полам; в) метод золотого сечения; г) метод Фибоначчи; д) метод парабол. Программы написаны на C++. Цель работы: знакомство с оптимизационными задачами, изучение различных методов одномерной оптимизации и сравнение эффективности их применения для конкретных целевых функций.

Лабораторная работа - Методы нулевого и первого порядка

формат exe, txt, pdf, docx
размер 6.66 МБ
добавлен 07 ноября 2011 г.

Лабораторная работа по дисциплине "Методы оптимизации", СФУ ИКИТ, 4-ый курс, преподаватель Сергеева Н.А. Задание: Найти минимум двух функций (функция общего вида и параболоид), используя следующие методы: Метод наилучшей пробы. Метод Ньютона – Рафсона. Программа написана в среде C++ Builder 6.

Лабораторная работа - многомерная безусловная оптимизации первого и нулевого порядка

формат doc
размер 753.68 КБ
добавлен 15 марта 2010 г.

Найти минимум функции f(x)= с начальным приближением x0(-1,0) при ?=0,0001. Для решения задачи использовать методы: метод конфигураций; метод наискорейшего спуска(метод одномерной оптимизации– золотое сечение); метод покоординатного спуска с постоянным шагом; овражный метод 1; В отчете Графики всех методов, блок схемы и код программы.

Лабораторная работа - Решение задачи нелинейной оптимизации средствами Excel

формат xlsx
размер 25.19 КБ
добавлен 31 марта 2010 г.

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

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

формат doc, txt
размер 94.97 КБ
добавлен 09 апреля 2005 г.

Методы одномерной безусловной оптимизации. Знакомство с оптимизационными задачами, изучение различных методов одномерной оптимизации и сравнение эффективности их применения для конкретных целевых функций. Пассивный оптимальный алгоритм. Метод дихотомии. Метод парабол.

Лабораторная работа №2 - Многомерная безусловная оптимизация

формат doc, vsd
размер 481.04 КБ
добавлен 13 мая 2010 г.

Предмет: Методы оптимизации Преподаватель: Хасанов А. Ю. ФИРТ, АСОИ. Найти минимум функции с помощью следующих методов: 1) Поиск по образцу; 2) Метод регулярного симплекса; 3) Метод конфигураций; 4) Метод деформируемого симплекса; 5) Градиентный метод с постоянным шагом; 6) Градиентный метод с дроблением шага; 7) Градиентный метод с оптимальным шагом; 8) Метод покоординатного спуска; 9) Метод Гаусса-Зейделя; 10) Овражный метод II. Есть все нео.

Методы оптимизации ЭЭС

формат xls
размер 193.5 КБ
добавлен 29 января 2009 г.

Все методы оптимизации в EXCEL: нужно только ввести свои данные. Метод Ньютона-Рафсона. метод итерации. Метод ускоренной итерации. Метод Гаусса-Зейделя.

Одно- и многомерная оптимизация

формат doc
размер 50.28 КБ
добавлен 07 октября 2010 г.

Лабораторная работа. Рассмотренны методы поисковой оптимизации. приведены алгоритмы для Матлаба. (метод градиента, простой перебор, шаговый метод, покоординатный метод и др. )

МИТХТ, 1999, 64 стр. , 2 изд., испр. и доп. Методическое пособие по дисциплине Применение информационных технологий в химии и химической технологии.
Пособие предназначено для самостоятельного изучения раздела дисциплины Применение информационных технологий в химии и химической технологии, изучаемой в МИТХТ на втором году обучения для всех направлений бакалавриата, при подготовке к выполнению лабораторных работ.
Основное внимание уделено тщательно подобранным примерам, позволяющим наиболее ярко проиллюстрировать те или иные особенности каждого метода. Все примеры выполнены в MICROSOFT EXCEL.
Пособие охватывает все темы раздела учебной программы указанной дисциплины. Кроме методов, входящих в учебную программу, в пособии описаны алгоритмы и вычислительные процедуры встроенных в EXCEL специальных подпрограмм и функций, позволяющих реализовать те или иные численные методы, например, матричные вычисления, линейный регрессионный анализ, метод сопряженных градиентов, линейное программирование и т. п.

Содержание:
Решение нелинейного уравнения с одним неизвестным.
Отделение корней.
Пример.
Уточнение корней: метод итераций.
Пример.
Уточнение корней: метод Ньютона
Пример.
Уточнение корней: метод бисекции (деления отрезка пополам ).
Пример.
Уточнение коней: подпрограмма EXCEL Подбор параметра.
Системы линейных алгебраических уравнений.
Матричный метод.
Пример.
Метод приближенных вычислений.
Пример.
Метод Гаусса – Зайделя.
Пример.
Решение систем нелинейных уравнений.
Выбор начальных приближений.
Пример.
Метод Ньютона.
Пример.
Метод итераций.
Пример.
Численные методы одномерной оптимизации.
Метод дихотомии.
Пример.
Метод золотого сечения.
Пример.
Встроенная подпрограмма EXCEL Поиск решения.
Многомерные задачи оптимизации.
Безусловная оптимизация: метод покоординатного спуска.
Пример.
Безусловная оптимизация: метод наискорейшего спуска.
Пример.
Безусловная оптимизация: подпрограмма EXCEL Поиск решения.
Условная оптимизация: метод штрафных функций.
Пример.
Условная оптимизация: подпрограмма EXCEL Поиск решения.
Условная оптимизация: линейное программирование.
Пример.
Метод наименьших квадратов.
Пример.
Пример.
Вычисление определённых интегралов.
Пример.
Численное решение обыкновенного дифференциального уравнения.
Пример.
Метод Рунге-Кутта четвертого порядка.
Пример.
Метод прогноза и коррекции: метод Адамса.
Пример.
Решение систем обыкновенных дифференциальных уравнений.
Задача Коши.
Пример.
Краевая задача: метод стрельбы.
Пример.
Краевая задача: метод прогонки.
Пример.
Численное решение уравнений с частными производными.
Пример.

Березин И.С., Жидков Н.П. Методы вычислений (том 2)

формат djvu
размер 4.46 МБ
добавлен 20 августа 2008 г.

М.: ГИФМЛ, 1959. - 620 с. Во втором томе книги рассмотрены численные методы решения систем линейных алгебраических уравнений, уравнений высших степеней и трансцендентных уравнений, численные методы отыскания собственных значений, приближенные методы решения обыкновенных дифференциальных уравнений, уравнений в частных производных и интегральных уравнений. Книга предназначена в качестве учебного пособия для студентов механико-математических и физи.

Васильев А.Н. Научные вычисления в Microsoft Excel

формат djvu
размер 10.47 МБ
добавлен 26 февраля 2010 г.

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

Денежкина И.Е Численные методы. Курс лекций

формат pdf
размер 1.48 МБ
добавлен 13 марта 2011 г.

Финансовая Академия при Правительстве РФ, 2008. -132 с. Издание содержит несколько основных разделов: - вычислительные методы алгебры - методы решения нелинейных уравнений и систем - методы численного решения обыкновенных дифференциальных уравнений - численные методы оптимизации Пособие предназначено для студентов математических специальностей эконмоических ВУЗов, рекомендовано в программах "Математические методы в экономике".

Лекции по вычислительным методам

формат pdf
размер 14.43 МБ
добавлен 06 декабря 2010 г.

Лекции по вычислительным методам. НГУ. Автор неизвестен. 5-ый семестр (110 стр. ), 6-ый семестр (41 стр. ). Численные методы решения Задачи Коши для ОДУ. Погрешности методов решения. Численные методы решения краевых задач ОДУ. Методы решения нелинейной краевой задачи. В лекциях содержится подробное описание эффективных методов приближенного решения на ЭВМ многих важных и широко распространенных задач вычислительной и прикладной математики: аппр.

Мельник В.А. Численные методы

формат doc
размер 2.47 МБ
добавлен 31 мая 2010 г.

Учебное пособие составлено в соответствии с программой курса Численные методы, читаемого студентам специальности Компьютерные сети и системы . В учебном пособии рассматриваются методы вычислений, используемые в инженерной практике, которые отличаются большим объемом вычислительной работы. В пособии отобраны численные методы для решения на ПЭВМ задач математического анализа, встречающихся в инженерных расчетах и знакомых студентам из курса Высшей.

Смирнов В.А. Лекции - Численные методы

формат pdf
размер 3.9 МБ
добавлен 13 января 2011 г.

Воткинский филиал Ижевского государственного технического университета. Специальность 230102 "Автоматизированные системы обработки информации и управления". Тематика лекций: Погрешности вычислений. Численные методы линейной алгебры. Интерполяция функций. Приближение функций. Численное интегрирование и дифференцирование. Численные методы решения уравнений. Численные методы решения систем уравнений. Задачи безусловной оптимизации. Численные методы.

Срочко В.А. Численные методы: Курс лекций

формат pdf
размер 696.37 КБ
добавлен 08 апреля 2009 г.

Излагается теоретический материал курса "Численные методы" - алгебра, мат. анализ, диф. уравнения для студентов математических специальностей

формат doc
размер 443.97 КБ
добавлен 30 ноября 2011 г.

Учебное пособие. Астрахань, Астраханский гос. пед. ун-т, 2000, -70с Рассматривается на многочисленных примерах, каким образом решаются на Mathcad’e разнообразные задачи численного анализа (решение систем линейных и нелинейных уравнений, решение дифференциальных уравнений, аппроксимация функций и т. д.). Пособие не является ни учебником по численным методам, ни руководством по Mathcad’у. Предполагается, что читатель имеет представление об основных.

Учебно-методический комплекс по дисциплине Численные методы

формат doc
размер 371.39 КБ
добавлен 23 декабря 2006 г.

Для заочного отделения. Специальность: 351400, 061100, 060500. Численные методы решения нелинейных уравнений. Аппроксимация функций. Интерполяция функций. Численное дифференцирование. Численное интегрирование. Численные методы решения дифференциальных уравнений.

Формалев В.Ф., Ревизников Д.Л. Численные методы

формат djvu
размер 2.51 МБ
добавлен 05 марта 2009 г.

М.: ФИЗМАТЛИТ, 2004. - 400 с. В учебнике представлены основные численные методы решения задач алгебры и анализа, теории приближений и оптимизации, задач для обыкновенных дифференциальных уравнений и уравнений математической физики. Систематически изложены методы конечных разностей, конечных и граничных элементов, методы исследования аппроксимации, устойчивости, сходимости, оценок погрешности. Оглавление: Элементы теории погрешностей. Численные м.

Читайте также: