На этой странице вы найдете примеры решений различных оптимизационных задач с использованием пакета электронных таблиц MS Excel (используется как надстройка Поиск решения, так и ручные вычисления).
- Задачи оптимизации и Excel
- Примеры решений задач оптимизаци
- Полезные ссылки
- Методички
Задачи оптимизации и Excel
Задачи оптимизации имеют огромное прикладное значение и возникают в самых разных разделах экономики, техники, военного дела и т.п. В таких задачах нас интересуют поиск некоторого оптимального решения (минимизующего или максимизирующего целевую функцию: прибыль, затраты, калорийность и т.п.) в условиях ограничений (наличия ресурсов, дорог, времени, продуктов и т.п.).
Вот некоторые примеры экономических задач: минимизация расходов при формировании состава сырья (например, на текстильных предприятиях), оптимизация раскроя (например, на швейных производствах), минимизация расходов при формировании штатного расписания, оптимизация калорийности и стоимости рациона (как для людей, так и для животных), минимизация расходов на перевозку грузов по маршрутам, оптимизация расходов на изготовление при выборе ассортимента продукции, максимизация прибыли при формировании инвестиционной программы и др.
решение задач в Excel
Часто эти задачи (даже учебные, даже в случае линейности) содержат более десяти переменных(а в случае, например, транспортных задач, и вовсе десятки), что делает ручные расчеты нерациональными. В то же время привычная для всех программа Excel прекрасно подходит для поиска решения.
Алгоритм решения с помощью надстройки «Поиск решения» следующий:
- составить математическую модель задачи: выделить и обозначить переменные, ограничения на них в виде равенств и неравенств (естественные, например, неотрицательность количества, и дополнительные, например, «запасов железной руды не более 10 т»), целевую функцию (то, что нужно оптимизировать) выразить через переменные.
- выделить место под переменные задачи; внести ограничения (левые части — в виде формул от переменных, правые — в виде констант) в файл электронной таблицы Excel,
- внести в ячейку формулу для целевой функции,
- запустить надстройку Поиск решения,
- установить нужные параметры решения (ограничения в листе, ограничения неотрицательности, условие линейности при необходимости и т.п.) и запустить выполнение.
Excel вычислит оптимальные значения переменных и покажет их в ячейках, а также значение целевой функции. Дополнительно можно построить отчеты для анализа решения задачи.
Некоторые задачи оптимизации решаются не с помощью надстройки Поиск решения, а путем подбора параметра или ручных расчетов. Ниже вы найдете примеры разных задач, а также ссылки на другие разделы со сходными заданиями.
Лучшее спасибо — порекомендовать эту страницу
Задачи оптимизации: примеры в Excel
Решение задач в Microsoft Excel. Определение оптимального объёма производства
Задача 1. Намечается крупномасштабное производство легковых автомобилей. Имеются четыре варианта проекта автомобиля $R_j$. Определена экономическая эффективность $К$ — каждого проекта в зависимости от рентабельности производства. По истечении трех сроков $S_i$ рассматриваются как некоторые состояния среды (природы). Значения экономической эффективности для различных проектов и состояний природы приведены в следующей таблице (д. е.):
Выберите оптимальное решение в соответствии с критериями Лапласа, Вальда, Сэвиджа и Гурвица (при $а = 0,5$).
Задача 2. Для производства двух видов изделий А и В предприятие использует три вида сырья. Нормы расхода каждого вида сырья на изготовление единицы продукции данного вида в таблице 6. В ней же указаны прибыль от реализации единицы изделия каждого вида и общее количество сырья данного, которое может быть использовано предприятием.
Требуется такой составить такой план производства изделий А и В, при котором прибыль от реализации будет максимальной?
Задача 3. Фирма N, имеющая филиалы (k), производит продукцию. Каждый филиал фирмы выпускает четыре вида продукции из пяти (i=1-5). Данные, характеризующие производство филиалов $b_$, приведены в табл.1.
Филиалы фирмы закупают сырье, из которого производят продукцию, у семи АО (j =1-7). Выход готового продукта из 1 тонны сырья $a_$ показан в табл.2.
Прибыль филиалов фирмы при закупке 1тн сырья у разных АО, $С_$ , показана в табл.3.
В разделе 1 работы требуется:
1.1.Определить количество закупаемого заданным филиалом фирмы сырья у каждого АО, ($x_j$), максимизируя прибыль филиала. Далее, студент формулирует экономико-математическую модель общей задачи линейного программирования (ОЗЛП).
1.2.С помощью полученных в результате реализации модели отчетов сделать рекомендации филиалу фирмы по расширению программы выпуска ассортимента продукции.
Задача 4. Для изготовления одного пирожка требуется 0,8 ед. начинки и 4 ед. теста, одного пирожного 4 ед. начинки и 0,5 ед. теста, одного рулета 2 ед. начинки и 2,5 ед. теста. Сколько пирожков, пирожных и рулетов нужно сделать кондитерской, если в наличии имеется 120 ед. теста и 300 ед. начинки?
Определите доход от реализации кондитерских изделий, если доход от продажи одного пирожка составляет 3 рубля, одного пирожного 2 рубля, одного рулета 1,5.
Для решения задачи используется ППП Excel.
Задача 5. Менеджер проекта по строительству нового торгового гипермаркета компании Наше дело надеется завершить проект за пару недель до Рождества.
После обзора оценок времени выполнения отдельных стадий выяснилось, что потребуются дополнительные инвестиции, чтобы сократить длительность проекта так, чтобы он действительно завершился вовремя. В таблице приведены оценки длительностей стадий и стоимость их сокращения на 1 и на 2 недели.
a. Нарисуйте сетевую диаграмму проекта и найдите критический путь.
b. Определите минимальную стоимость сокращения проекта на 5 недель.
Источник: www.matburo.ru
Функция в Excel: поиск решения
Наряду со множеством других возможностей, в Microsoft Excel есть одна малоизвестная, но очень полезная функция под названием “Поиск решения”. Несмотря на то, что найти и освоить ее, может быть, непросто, ее изучение и применение может помочь в решении огромного количества задач. Функция берет данные, перебирает их и выдает самое оптимальное решение из возможных. Итак, давайте разберемся, как именно работает поиск решения и попробуем применить данную функцию на практике
Как включить функцию “Поиск решения”
Смотрите также: “Как в Экселе пронумеровать страницы”
Несмотря на свою эффективность, функция “Поиск решения” не находится в первых рядах панели инструментов или контекстного меню. Многие пользователи, работающие в Excel годами, даже не подозревают о ее существовании. Дело в том, что по умолчанию она вообще отключена и для ее добавления на ленту нужно проделать следующие шаги:
- Открываем меню “Файл”, кликнув по соответствующему названию.
- Кликаем по разделу “Параметры”, который находится внизу вертикального перечня с левой стороны.
- Далее щелкаем по подразделу “Надстройки”. Здесь отображаются все надстройки программы, а внизу будет надпись “Управление”. Справа от нее представлено выпадающее меню, в котором должны быть выбраны “Надстройки Excel”, обычно уже установленные по умолчанию. Нажимаем кнопку “Перейти”.
- На экране появится новое вспомогательное окно “Надстройки”. Устанавливаем флажок напротив опции “Поиск решения” и нажимаем ОК.
- Все готово. Требуемая функция появится на ленте в правой части вкладки “Данные”.
Подготовительный этап
Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.
Итак, у нас есть данные про продаже товаров, представленные в табличном виде.
И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.
Наша задача – вычислить скидку, на которую будут умножены все суммы по продажам всех наименований. Она и будет найдена с помощью функции “Поиск решения”, а ячейка с этой скидкой будет называется искомой.
Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.
Применение функции и ее настройка
Смотрите также: “Как построить график в Excel”
Формула готова. Теперь нужно применить саму функцию.
- Переключаемся во вкладку “Данные” и нажимаем кнопку “Поиск решения”.
- Откроются “Параметры”, где необходимо задать нужные настройки. В поле “Оптимизировать целевую функцию:” указываем адрес целевой ячейки, где планируется вывести сумму по всем скидкам. Можно прописать координаты вручную, либо выбрать из таблицы, для чего сначала кликаем по области ввода, затем – по нужной ячейке.
- Переходим к настройке других параметров. В пункте “До:” можно задать максимальную границу, минимальную границу или же точное число. Исходя из поставленной задачи ставим отметку рядом с опцией “Значение” и набираем “4500000” – сумма скидок по всем наименованиям.
- Следующее для заполнения поле – “Изменяя значения переменных:”. В него нужно внести координаты искомой ячейки, содержащей определенное значение. Это значение и есть та самая скидка, которую мы пытаемся вычислить. Также, как и с выбором целевой ячейки, координаты можно написать вручную, либо кликнуть по нужной ячейке в самой таблице.
- Теперь нужно отредактировать раздел “В соответствии с ограничениями:”, в котором задаем ограничения используемых данных. Например, можно исключить десятичные дроби или, скажем, отрицательные числа. Это делается через кнопку “Добавить”.
- Откроется вспомогательно окно, позволяющее добавить ограничения во время вычислений. В первом поле указываем координаты определенной ячейки или области ячеек, для которых это условие должно действовать. Согласно нашей задаче, указываем координаты искомой ячейки, в которой будет выводиться значение скидки. Следующий шаг – определить знак сравнения. Устанавливаем “больше или равно”, чтобы итоговое число не могло быть отрицательным. “Ограничение”, которое устанавливается в третьем поле, в этом случае будет равно цифре 0, поскольку именно относительно этого значения задается условие.
Можно установить еще одно ограничение с помощью кнопки “Добавить”. Дальнейшие действия по его настройке будут аналогичными. По готовности щелкаем OK. - После выполнения описанных выше действий в самом большом поле окна появится установленное только что ограничение. Список может быть довольно большим и зависит от сложности предполагаемых расчетов, но в данном случае будет достаточно и одного условия.
Под этим полем также есть опция, позволяющая делать все остальные переменные, не затрагиваемые ограничениями, неотрицательными. Однако, будьте внимательны и проследите за тем, чтобы между этим параметром и поставленными ограничениями не было противоречия, иначе при расчете в программе может возникнуть конфликт. - Также можно задать немалое количество дополнительных настроек. Чуть ниже справа есть кнопка “Параметры”, позволяющая это сделать. Нажимаем на нее и открываем новое окно.
- В этих настройках у нас есть возможность установить “Точность ограничения” и “Пределы решения”. В нашем случае задавать данные параметры нет необходимости, поэтому после ознакомления с представленным окном, его можно закрыть, нажав OK.
- Итак, все настройки выполнены и параметры установлены. Пора запускать функцию – для этого нажимаем кнопку “Найти решение”.
- После этого программа сделает все необходимые расчеты и выдаст результаты в нужных ячейках. При этом сразу же откроется окно “Результаты поиска решения”, где можно сохранить/отменить результаты или настроить параметры поиска заново. Если результаты нас устраивают, оставляем отметку напротив опции “Сохранить найденное решение” и нажимаем ОК. При этом, если мы предварительно установим галочку слева от надписи “Вернуться в диалоговое окно параметров поиска решения”, после того, как мы щелкнем OK, мы обратно переключимся к настройке функции поиска решения.
- Вполне вероятно, что расчеты могут показаться неправильными, либо возникнет желание немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно посмотреть поля с введенными данными.
- Если с данными все нормально, можно попробовать задействовать другой метод решения. Для этого щелкаем по текущему варианту и из раскрывшегося перечня выбираем способ, который нам кажется наиболее подходящим:
- Первый – ищет решение методом обобщенного приведенного градиента (ОПГ) для нелинейных задач. Стандартно выбран именно этот вариант, но можно попробовать и другие.
- Второй – пытается отыскать решение для линейных задач, используя симплекс-метод.
- Третий – для выполнения поставленной задачи использует эволюционный поиск.
- В том случае, если ни один из методов не принес удовлетворительных результатов, стоит проверить данные в таблице и параметрах еще раз, поскольку именно это является самой частой ошибкой в подобного рода задачах.
- Теперь, когда мы получили требуемую скидку, осталось ее применить, чтобы рассчитать суммы скидок по всем наименованиям. Для этого отмечаем первую ячейку столбца “Сумма скидки”, пишем в ней формулу “=D2*$G$2” и нажимаем Enter. Знаки доллара ставятся для того, чтобы при растягивании/копировании формулы на другие строки, ячейка G2 со скидкой оставалась неизменной в расчетах.
- Мы получили сумму скидки для первого наименования. Теперь наводим курсор на нижний правый угол ячейки с результатом, как только он поменяет форму на крестик, зажав левую кнопку мыши растягиваем формулу на все строки, по которым хотим посчитать аналогичную сумму.
- Теперь наша таблица полностью готова в соответствии с поставленной задачей.
Заключение
Таким образом, функция “Поиск решения” в Эксель может помочь в решении определенных задач, которые достаточно сложно или невозможно решить простыми методами. Однако, проблема в использовании данного способа заключается в том, что по умолчанию данная функция скрыта в программе, из-за чего многие пользователи не догадываются о ее существовании. Также функция довольно трудна в освоении и использовании, но при ее должном изучении, она может принести значительную пользу и облегчить работу.
Источник: microexcel.ru
Постановка и решение задачи с помощью надстройки «Поиск решения»
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
«Поиск решения» — это надстройка для Microsoft Excel, которую можно использовать для анализ «что если». С ее помощью можно найти оптимальное значение (максимум или минимум) формула, содержащейся в одной ячейке, называемой целевой, с учетом ограничений на значения в других ячейках с формулами на листе. Надстройка «Поиск решения» работает с группой ячеек, называемых ячейками переменных решения или просто ячейками переменных, которые используются при расчете формул в целевых ячейках и ячейках ограничения. Надстройка «Поиск решения» изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке.
Проще говоря, с помощью надстройки «Поиск решения» можно определить максимальное или минимальное значение одной ячейки, изменяя другие ячейки. Например, вы можете изменить планируемый бюджет на рекламу и посмотреть, как изменится планируемая сумма прибыли.
Примечание: В версиях надстройки «Поиск решения», выпущенных до Excel 2007, ячейки переменных решения назывались изменяемыми или регулируемыми. В Excel 2010 надстройка «Поиск решения» была значительно улучшена, так что работа с ней в Excel 2007 будет несколько отличаться.
Пример вычисления с помощью надстройки «Поиск решения»
В приведенном ниже примере количество проданных единиц в каждом квартале зависит от уровня рекламы, что косвенно определяет объем продаж, связанные издержки и прибыль. Надстройка «Поиск решения» может изменять ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5) до ограничения в 20 000 рублей (ячейка F5), пока общая прибыль (целевая ячейка F7) не достигнет максимального значения. Значения в ячейках переменных используются для вычисления прибыли за каждый квартал, поэтому они связаны с формулой в целевой ячейке F7, =СУММ (Q1 Прибыль:Q2 Прибыль).
1. Ячейки переменных
2. Ячейка с ограничениями
3. Целевая ячейка
После выполнения процедуры получены следующие значения.
Постановка и решение задачи
- На вкладке Данные в группе Анализ нажмите кнопку Поиск решения.
Примечание: Если команда «Найти решение» или «Анализ» недоступна, необходимо активировать надстройка. См. также: Активация надстройки «Надстройка».
Примечание Отношения int,binи dif можно применять только в ограничениях для ячеек переменных решения. Чтобы изменить или удалить существующее ограничение, выполните указанные ниже действия.
Просмотр промежуточных результатов поиска решения
- После постановки задачи нажмите кнопку Параметры в диалоговом окне Параметры поиска решения.
- Чтобы просмотреть значения всех найденных решений, в диалоговом окне Параметры установите флажок Показывать результаты итераций и нажмите кнопку ОК.
- В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение.
- В диалоговом окне Показать предварительное решение выполните одно из указанных ниже действий.
- Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.
- Чтобы продолжить процесс поиска решения и просмотреть следующий вариант решения, нажмите кнопку Продолжить.
Изменение способа поиска решения
- В диалоговом окне Параметры поиска решения нажмите кнопку Параметры.
- В диалоговом окне на вкладках Все методы, Поиск решения нелинейных задач методом ОПГ и Эволюционный поиск решения выберите или введите значения нужных параметров.
Сохранение или загрузка модели задачи
- В диалоговом окне Параметры поиска решения нажмите кнопку Загрузить/сохранить.
- Введите диапазон ячеек для области модели и нажмите кнопку Сохранить или Загрузить. При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель оптимизации. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.
Совет: Чтобы сохранить последние параметры, настроенные в диалоговом окне Параметры поиска решения, вместе с листом, сохраните книгу. Каждый лист в книге может иметь свои параметры надстройки «Поиск решения», и все они сохраняются. Кроме того, для листа можно определить более одной задачи, если нажимать кнопку Загрузить или сохранить для сохранения задач по отдельности.
Методы поиска решения
В диалоговом окне Параметры поиска решения можно выбрать любой из указанных ниже алгоритмов или методов поиск решения.
- Нелинейный метод обобщенного понижающего градиента (ОПГ). Используется для гладких нелинейных задач.
- Симплекс-метод. Используется для линейных задач.
- Эволюционный метод Используется для негладких задач.
Важно: Сначала нужно включить надстройку «Поиск решения». Дополнительные сведения см. в статье Загрузка надстройки «Поиск решения».
Пример вычисления с помощью надстройки «Поиск решения»
В приведенном ниже примере количество проданных единиц в каждом квартале зависит от уровня рекламы, что косвенно определяет объем продаж, связанные издержки и прибыль. Надстройка «Поиск решения» может изменять ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5) до ограничения в 20 000 рублей (ячейка D5), пока общая прибыль (целевая ячейка D7) не достигнет максимального значения. Значения в ячейках переменных используются для вычисления прибыли за каждый квартал, поэтому они связаны с формулой в целевой ячейке D7, =СУММ (Q1 Прибыль:Q2 Прибыль).
В результате выполнения получены следующие значения:
Постановка и решение задачи
- В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
- В разделе Оптимизировать целевую функцию, введите ссылка на ячейку или имя целевой ячейки.
Примечание: Целевая ячейка должна содержать формулу.
- В диалоговом окне Параметры поиска решения нажмите кнопку Добавить.
- В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.
- Во всплывающем меню задайте требуемое отношение между целевой ячейкой и ограничением. Если вы выбрали , =, или >= в поле Ограничение, введите число, имя ячейки, ссылку на нее или формулу.
Примечание: Отношения int, бин и раз можно использовать только в ограничениях для ячеек, в которых находятся переменные решения.
Необходимые действия
Принять ограничение и добавить другое
Нажмите кнопку Добавить.
Принять ограничение и вернуться в диалоговое окно Параметры поиска решения
Нажмите кнопку ОК.
- Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Excel будет пересчитан с учетом последних найденных значений для ячеек переменных.
- Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, отчет не будет доступен.
- Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.
Просмотр промежуточных результатов поиска решения
- В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
- После постановки задачи нажмите кнопку Параметры в диалоговом окне Параметры поиска решения.
- Чтобы просмотреть значения всех предварительных решений, установите флажок Показывать результаты итераций и нажмите кнопку ОК.
- В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение.
- В диалоговом окне Показать предварительное решение выполните одно из следующих действий:
ЗадачаНеобходимые действия Остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения Нажмите кнопку Стоп. Продолжить поиск и просмотреть следующее предварительное решение Нажмите кнопку Продолжить.
Изменение способа поиска решения
Примечание: Если будет достигнуто максимальное время поиска решения или количество итераций, а решение еще не будет найдено, средство «Поиск решения» выведет диалоговое окно Показать предварительное решение.
Сохранение или загрузка модели задачи
- В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
- Щелкните Загрузить/сохранить, укажите диапазон ячеек для области модели и нажмите кнопку Сохранить или Загрузить. При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель оптимизации. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.
Совет: Чтобы сохранить последние параметры, настроенные в диалоговом окне Параметры поиска решения, вместе с листом, сохраните книгу. Каждый лист в книге может иметь свои параметры надстройки «Поиск решения», и все они сохраняются. Кроме того, для листа можно определить более одной задачи, если нажимать кнопку Загрузить/сохранить для сохранения задач по отдельности.
Методы поиска решения
Метод решения
Нелинейный метод обобщенного понижающего градиента (ОПГ)
Используется по умолчанию для моделей со всеми функциями Excel, кроме ЕСЛИ, ВЫБОР, ПРОСМОТР и другие ступенчатые функции.
Поиск решения линейных задач симплекс-методом
Используйте этот метод для задач линейного программирования. В формулах модели, которые зависят от ячеек переменных, должны использоваться функции СУММ, СУММПРОИЗВ, +, — и *.
Эволюционный поиск решения
Этот метод, основанный на генетических алгоритмах, лучше всего подходит в том случае, если в модели используются функции ЕСЛИ, ВЫБОР и ПРОСМОТР с аргументами, которые зависят от ячеек переменных.
Примечание: Авторские права на части программного кода надстройки «Поиск решения» версий 1990–2010 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.
Поскольку надстройки не поддерживаются в Excel в Интернете, вы не сможете использовать надстройку «Поиск решения» для анализа данных «что если», чтобы найти оптимальные решения.
Если у вас есть Excel, вы можете нажать кнопку Открыть в Excel, чтобы открыть книгу для использования надстройки «Поиск решения».
Дополнительная справка по надстройке «Поиск решения»
За дополнительной справкой по надстройке «Поиск решения» обращайтесь по этим адресам:
Авторские права на части программного кода надстройки «Поиск решения» версий 1990-2009 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Источник: support.microsoft.com