Примеры решения оптимизационных задач средствами Excel

Ввод условий задачи состоит из следующих основных шагов:

2). Ввод исходных данных (коэффициентов математической модели).

3). Ввод целевой функции, ограничений и граничных условий.

Последовательность работ рассмотрим на примере задачи распределения ресурсов.

Ресурс	Продукт1	Продукт2	Продукт3	Продукт4	Наличие
Трудовые
Сырье
Финансы
Прибыль

Составим математическую модель, для чего введем следующие обозначения:

x_j— количество выпускаемой продукции j-го типа j=1,2,3,4;

b_i— количество располагаемого ресурса i-го вида i=1,2,3;

a_ij— норма расхода i-го ресурса для выпуска единицы продукции j-го типа;

c_j— прибыль, получаемая от реализации единицы продукции j-го типа.

Из табл. видно, что для выпуска единицы Продукта1 требуется 6 единиц сырья, значит, для выпуска всей продукции первого типа требуется 6x₁ единиц сырья, где x₁— количество выпускаемой продукции Продукт1. С учетом того, что для других видов продукции зависимости будут аналогичны, ограничение по сырью будет иметь вид:

В этом ограничении левая часть равна величине требуемого ресурса, а правая показывает количество имеющегося ресурса.

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

Математическая модель задачи выглядит следующим образом.

1). Форма ввода условий задачи представлена на рис. 6. Весь текст на рисунке (и в дальнейшем) является комментарием и на решение задачи не влияет.

3). Рассмотрим алгоритмы ввода уравнений целевой функции и ограничений:

• Установить курсор в ячейку, содержащую целевую функцию (F6).
• Щелкнуть мышью по кнопке -Мастер функций (на панели инструментов). На экране: диалоговое окно «Мастер функций шаг 1 из 2» (рис. 8).
• Выбрать категорию Мат. и тригонометрия
• Выбрать функцию СУММПРОИЗВ
• Щелкнуть по кнопке Шаг >. На экране: диалоговое окно «Мастер функций шаг 2 из 2» (рис. 9).
• В массив 1 ввести $B$3:$E$3.

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

В ячейке F6 отображается значение целевой функции, оно равно 0.

Теперь таблица примет вид, представленный на рис. 10. В режиме представления формул она показана на рис. 11.

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

1. 2. Работа в диалоговом окне «Поиск решения»

1). Выберите последовательно опции меню Сервис, Поиск решения. На экране появится соответствующее окно (рис. 12).

Установить целевую ячейку- определяет целевую ячейку, значение которой необходимо максимизировать или минимизировать, или сделать равным конкретному значению.

Изменяя ячейки- определяет изменяемые ячейки. Изменяемая ячейка- это ячейка, которая может быть изменена в процессе Поиска Решения для достижения нужного результата в ячейке из окна Установить целевую ячейку с удовлетворением поставленных ограничений.

Предположить- отыскивает все неформульные ячейки, прямо или непрямо зависящие от формулы в окне Установить целевую ячейку, и помещает их ссылки в окно Изменяя ячейки.

Ограничения- перечисляет текущие ограничения в данной проблеме.

Добавить- выводит окно диалога “Добавить ограничение”, в котором можно добавить ограничения к текущей проблеме.

Изменить- выводит окно диалога “Изменить ограничение”, в котором можно модифицировать имеющиеся ограничения.

Выполнить- запускает процесс решения определенной проблемы.

Закрыть- закрывает окно диалога, не решая проблемы. Сохраняются лишь изменения, сделанные при помощи кнопок Параметры, Добавить, Изменить и Удалить. Не сохраняются изменения, произведенные после использования данных кнопок.

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

Восстановить- очищает все текущие установки проблемы и возвращает все параметры к их значениям по умолчанию.

2). В поле Равной выберите флажок Максимальному значению.

3). Введите адреса искомых переменных, для этого выделите мышью область таблицы B3:E3.

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

Ограничение- определяет условие, налагаемое на содержимое окна Ссылка на ячейку. Выберите из списка отношение, которое нужно установить между ячейкой или интервалом и ограничением, которое нужно ввести в окне справа от списка. Можно выбрать =, или «цел». Если Вы выбрали «цел» для указания на то. что переменная должна быть целочисленной, то слово «Целое» появляется в окне справа от списка.

Добавить- в окне диалога “Добавить ограничение” можно добавить новое ограничение без возврата в диалог “Параметры поиска решений”.

Если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делается с помощью кнопок Изменить, Удалить (рис. 12). На этом ввод условий задачи закончен.

5). Установка параметров решения. Щелкните мышью по кнопке Параметры. На экране появится окно «Параметры поиска решения» (рис. 14).

Максимальное время- ограничивает время, требующееся для процесса отыскания решения. Это значение должно быть положительным целым числом. Значение по умолчанию равно 100 (секунд), что вполне годится для большинства малых задач, хотя Вы можете ввести любое значение до 32767.

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

Точность- контролирует точность ответов, получаемых при поиске решений. Число, вводимое в поле Точность:

• используется при определении того, удовлетворяет ли значение ячейки ограничения нужному равенству или находится ли оно в указанных границах.
• должно быть дробным числом от 0 до 1 (не включая концы).
• имеет значение по умолчанию равно 0,000001.

указывает на меньшую точность, если число введено с меньшим количеством десятичных знаков; например, 0,0001.

Вообще говоря, чем большая точность определяется (чем меньше число), тем больше времени понадобится для поиска решения. Методы, используемые Поиском Решения, позволяют существенно ускорить поиск, если установить исходное значение, достаточно близкое к искомому решению.

Линейная модель- ускоряет процесс отыскания решения. Команда может быть использована только, если все связи в модели линейны.

Показать результаты итераций- прерывает Поиск Решения и показывает результаты после каждой итерации.

Автоматический масштаб- включает автоматический масштаб. Это полезно, когда параметры ввода (Изменяя ячейки) и вывода (Установить целевую ячейку и Ограничения) сильно различаются по величине; например, максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

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

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

• прямая- такой способ дифференцирования установлен по умолчанию.
• центральная- этот способ требует больше вычислений на рабочем листе, но он может помочь в тех случаях, когда Вы получаете сообщение о том, что Поиск Решений не может улучшить решение.

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

• метод Ньютона- это метод поиска по умолчанию, использующий квази-ньютоновский подход. Этот метод обычно требует больше памяти, чем метод сопряженного градиента, но меньшее количество итераций.
• метод сопряженного градиента- поиск методом сопряженного градиента требует меньше памяти, чем ньютоновский метод, но обычно большее число итераций для достижения конкретного уровня точности. Если проблема достаточно велика и важно экономное использование памяти, то стоит применить этот метод. Он также особенно полезен, если Вы видите, что последовательные итерации дают слишком малое отличие последовательных приближений.

Загрузить модель- выводит окно диалога «Загрузить Модель», в котором можно указать, какую именно модель нужно загрузить.

Сохранить модель- выводит окно диалога «Сохранить Модель», в котором можно указать, где именно нужно сохранить данную модель. Используйте кнопку Сохранить модель только в том случае, если нужно сохранить более, чем одну модель Поиска Решения вместе с данным рабочим листом. Первая модель Поиска Решений автоматически сохраняется вместе с рабочим листом.

Установите флажок Линейная модель, остальные параметры будем использовать по умолчанию.

6). Нажмите OK, затем кнопку Выполнить в окне «Поиск решения». Через некоторое время на экране появится окно «Результаты поиска решения» (рис. 15).

Окно диалога «Результаты поиска решения» выводит результаты последнего вычисления, используя значения ячеек, наиболее близкие к нужному решению.

Когда Поиск Решения завершает попытки отыскания решения, то на экран в верху окна диалога «Результаты поиска решений» выводится сообщение о завершении.

Сохранить найденное решение- принимает решение, найденное Поиском Решения, и подставляет найденные значения в соответствующие ячейки.

Восстановить исходные значения- восстанавливает исходные значения в изменяемых ячейках.

Сохранить сценарий- открывает окно диалога Сохранить сценарий, в котором можно сохранить данную проблему для использования Диспетчером Сценариев пакета Microsoft Excel.

Отчеты- создает указанный тип отчета. Каждый отчет появляется на отдельном листе рабочей книги.

• Результаты- перечисляет изменяемые ячейки и ячейку в окне Установить целевую ячейку вместе с исходным и конечным значением. Также показывает ограничения и информацию о них.
• Устойчивость- предоставляет информацию о том, насколько чувствительно решение к малым изменениям в формуле окна Установить целевую ячейку или ограничениях. Для нелинейных моделей, отчет предоставляет двойственные значения (нормированные градиенты и множители Лагранжа). Для линейных моделей отчет включает редуцированную стоимость, теневые цены, objective coefficient (с допустимыми отклонениями в обе стороны), и ограничения на изменение правой стороны равенства.
• Пределы- перечисляет изменяемые ячейки вместе с соответствующими значениями, ячейку в окне Установить целевую ячейку, верхние и нижние пределы и целевые значения. Нижний предел есть наименьшее значение, которое может находиться в изменяемой ячейке, если фиксировать остальные ячейки и удовлетворить все ограничения. Верхний предел есть наибольшее значение. Целевое значение есть значение ячейки в окне Установить целевую ячейку, когда значение изменяемой ячейки достигает наименьшего или наибольшего предела.

На рис. 16 видно, что в оптимальном решении Продукт1=B3=10; Продукт2=C3=0; Продукт3=D3=6; Продукт4=E3=0. При этом максимальная прибыль будет составлять F6=1320, а количество использованных ресурсов равно: трудовых=F9=16, сырья=F10=84, финансов=F11=100.

Таково оптимальное решение рассматриваемой задачи распределения ресурсов. Однако решение задачи находится не всегда. Если условия задачи несовместны, на экране появится диалоговое окно (рис. 17):

Если целевая функция неограничена, то на экране появится диалоговое окно (рис. 18):

По умолчанию инструмент выполняет 100 повторений (итераций) с точностью 0.001. Если нужно увеличить количество повторений или повысить точность вычисления измените настройки: «Файл»-«Параметры»-«Формулы»-«Параметры вычислений»:

Поиск решения в Экселе

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

Ресурс	Продукт1	Продукт2	Продукт3	Продукт4	Наличие
Трудовые
Сырье
Финансы
Прибыль

Подготовка таблицы

Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить с помощью функции Поиска решений. Ячейка, в которой он располагается, называется искомой.

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

2) находиться в рамках допустимого диапазона, чтобы дать возможность обосновать для контролирующих органов, что аналогичные цены использовали бы компании, действующие независимо друг от друга

Поиск решений в Excel для экономиста

Цена
Минимальная трансфертная цена не может быть ниже себестоимости, увеличенной на 5%.
Максимальная трансфертная цена для компании не может быть больше чем средняя продажная цена покупателям уменьшенная на 5%.
Цены не должны отличаться между собой не более, чем на 20%.

Запуск инструмента Поиск решения

После того, как таблица подготовлена, находясь во вкладке «Данные», жмем на кнопку «Поиск решения», которая расположена на ленте в блоке инструментов «Анализ».

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

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

Ниже расположено поле «Изменяя ячейки переменных». Тут нужно указать адрес искомой ячейки, где, как мы помним, находится коэффициент, умножением на который основной заработной платы будет рассчитана величина премии. Адрес можно прописать теми же способами, как мы это делали для целевой ячейки.

В поле «В соответствии с ограничениями» можно выставить определенные ограничения для данных, например, сделать значения целыми или неотрицательными. Для этого, жмем на кнопку «Добавить».

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

Дополнительные настройки можно задать, кликнув по кнопке «Параметры».

Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.

После того, как все настройки установлены, жмем на кнопку «Найти решение».

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Поиск решения будем рассматривать в MS EXCEL 2010 (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в MS EXCEL 2007).
В этой статье рассмотрим:

И последнее ограничение: минимальная сумма прибыли после уплаты налогов. Здесь значение будем задавать через формулу: выручка, умноженная на 1%, потому что на Кроне в зависимости от изменения трансфертных цен выручка будет изменяться.

Задачи на подбор параметра в excel

1. Ставим курсор в ячейку С1. Нажимаем значок функций. Выбираем «ЕСЛИ».
2. Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
3. Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение задач – одно из важных применений Excel. Самый простой инструмент предназначен для подбора значений и называется «что-если» анализ: задается некоторая целевая функция и ее числовое значение, Excel автоматически подбирает параметры целевой функции до получения целевого значения. Формула в целевой функции должна логически зависеть от подбираемого параметра.

Аннотация к презентации

Смотреть презентацию онлайн с анимацией на тему «Решение задач оптимизации в MS Excel» по информатике. Презентация состоит из 18 слайдов. Для учеников 10-11 класса. Материал добавлен в 2016 году. Средняя оценка: 4.0 балла из 5.. Возможность скчачать презентацию powerpoint бесплатно и без регистрации. Размер файла 1.44 Мб.

Решение задач оптимизации в MS Excel

ГБОУ Центр образования № 133 Невского района авт. Баринова Е.А.

Слайд 2

Для решения задач оптимизации необходимо:

Задать целевую функцию Создать математическую модель задачи Решить задачу на компьютере

Слайд 3

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

Слайд 4

Задача

Слайд 5

Целевая функция

Слайд 6

Ограничение на объем производства:

«…Агенты по продаже считают, что неделю на рынке может быть реализовано до 550 полок…» Очевидно, что совокупный объем производства полок не должен превышать 550 единиц, или, в математическом виде: x1 + x2  550

Слайд 7

Ограничение на использование оборудования:

Слайд 8

Ограничение на использование материалов:

«…Для каждой полки типа А требуется 2 м2 материала, для полки типа В — 3 м2 материала. Компания может получить до 1200 м2 материала в неделю…» На основе этой информации можно сделать вывод, что общее количество материала, затрачиваемого для реализации данного проекта, не должно превышать 1200 м2: 2×1 + 3×2  1200

Слайд 9

Граничные условия

В качестве граничных условий в данном примере могут быть использованы следующие утверждения, вытекающие из сути поставленной задачи: Объем производства полок типа А и полок типа В – неотрицательное значение. Объем производства полок типа А и полок типа В – целое число, запишем таким образом: x1, x2  0 x1, x2 – целое

Слайд 10

Ввод условий задачи

Ввод условий задачи состоит из следующих основных шагов: Создание формы для ввода данных, необходимых для последующего решения. Ввод исходных данных и зависимостей из математической модели. Указание целевой ячейки (ячейки, в которую введена целевая функция), ввод ограничений и граничных условий в диалоговом окне Поиск решения.

Слайд 11

Создание формы для ввода данных

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

Слайд 12

Ввод исходных данных

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

Слайд 13

Назначение целевой функции, ввод ограничений и граничных условий

Данная стадия ввода условия задачи осуществляется в диалоговом окне Поиск решения

Слайд 14

Назначить целевую ячейку

Для этого в поле «Установить целевую ячейку:» вводится адрес ячейки, содержащей целевую функцию. Затем устанавливается направление последней – значение, к которому она должна стремиться исходя из условий задачи (минимальное, максимальное, конкретное, задаваемое пользователем). В поле «Изменяя ячейки:» ввести адреса ячеек, зарезервированных для искомых переменных.

Слайд 15

Ввести ограничения и граничные условия

Слайд 16

Получение результата

После нажатия на кнопку Выполнить диалогового окна Поиск решения на экране появляется диалоговое окно Результаты поиска решения.

Слайд 17

Решение найдено

Слайд 18

Оптимальное решение поставленной задачи

полок типа А — в количестве 450 штук (В3); полок типа В – в количестве 100 штук (С3). При этом максимальная прибыль будет составлять 1720 единиц, а ресурсы используются следующим образом: потребление материала – 1200 единиц (D10); использование оборудования – 140 часов (D11).

На последнем шаге рассчитаем значения прибыли для тех же значений количества при ценах 80, 85, 95 и 100руб. Используем для этого таблицу подстановки с двумя изменяемыми параметрами. Результат показан на рис. 2.37.

Метод поиска решения в excel

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».