Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.
«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.
Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»
Первые 6 задач на оптимизацию в EXCEL 2016
Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».
После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке
Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.
Пример первый.
Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.
То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.
В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).
Excel. Задачи оптимизации. Часть 1
Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.
Под «1» обозначена наша целевая ячейка. Она может быть только одна.
«2» — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.
«3» — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.
«4» — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.
«5» — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.
Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.
Но в нашем примере ограничение может быть лишь одно – положительный коэффициент. Задать его, конечно, можно несколькими способами – либо используя «Добавить» (что называют «явно указать ограничение»), либо просто отметить действующей функцию «Сделать переменные без ограничений неотрицательными». Это можно сделать в надстройке «Поиск решения», нажав на кнопочку «Параметры».
Кстати, после подтверждения параметров и запуска программы (кнопочка «Выполнить»), вы сможете в таблице просмотреть полученный результат. Тогда программа продемонстрирует окошко «результатов поиска».
Если продемонстрированный результат полностью вам подходит, тогда останется только вновь подтвердить его (кнопочка «ОК»), что зафиксирует результат в вашей таблице. Если же что-то в расчетах вас не устраивает, то необходимо отменить результат (кнопочка «Отмена»), вернуться к предыдущему состоянию нашей таблицы и исправить допущенные ошибки.
Правильное решение задачи примера должно получиться вот таким
Очень важно — чтобы получить правильный результат даже при малейшем изменении исходных данных необходимо перезапустить «Поиск решений».
Чтобы более подробно взглянуть на то, как действует данная программа, давайте разберем еще один пример.
Допустим, вы являетесь владельцем крупного мебельного предприятия и необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Вы производите только книжные полки, при этом всего двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).
Модель «А» требует 3 м 3 досок, а модель «В» — на 1 м 3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м 3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.
Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?
Поскольку порядок действия известен, то начинаем создавать необходимую нам таблицу с данными и формулами. Расположение ячеек, как и ранее, вы можете установить на свое усмотрение. Или же воспользоваться нашим
Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.
Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).
Конечно, в этом случае можно было не указывать адреса ячеек, а напрямую прописать необходимые цифровые значения, однако если использовать адреса, то изменения ограничений можно будет проводить и в таблице, что поможет рассчитывать прибыль этого предприятия в будущем, при смене исходных данных.
Активируем программу, и она подготавливает решение.
Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было
Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».
Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.
Так что будьте предельно внимательны в будущем.
Третий и, пожалуй, последний пример. Попробуем минимизировать затраты транспортной компании используя поиск решений в Excel 2010.
Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.
Решение задачи линейной оптимизации с помощью Excel
Выбираем ячейку, в которой надо оптимизировать целевую функцию, в нашем случае B5. Ставим галочку на максимум, затем выбираем ячейки с изменяемыми переменными это x1 и x2 – A4 и B4 и прописываем ограничения, нажимаем на кнопку добавить.
Из условия задачи значения выражений левой части меньше или равно значений правой части. Указываем сразу диапазон значений. Жмём на кнопку добавить ограничения.
И выбираем из списка метод решения – решения линейной задачи симплекс методом.
Вылетает информационное окно — результаты поиска решения, жмём Ок.
| Переменные | |
| x1 | x2 |
| 75 | 100 |
| Функция целевая | 1775 |
| 200 | 200 |
| 150 | 150 |
| 800 | 800 |
В результате, в исходной таблице появятся значения неизвестных переменных и значение целевой функции. В итоги мы получили оптимизированные значения переменных, на этом задачи оптимизации линейного программирования решена.
5320
Источник: www.matematicus.ru
Решение задач оптимизации средствами Microsoft Excel
Для решения задач оптимизации в Microsoft Excel предназначена надстройка Поиск решения. Постановка задачи В общем виде задача оптимизации ставится следующим образом: найти оптимальное (максимальное или минимальное) значение функции 
. Если функции 
и 
являются линейными, т. е. 
, то такая задача называется задачей линейного программирования. Подготовкаблока данных
- На рабочем листе отвести блок данных под изменяемые ячейки, т. е. ячейки для хранения переменных
. В результате решения задачи в этих ячейках появятся искомые значения
. - В отдельную ячейку ввести формулу для целевой функции
. Эта ячейка называетсяцелевой. - отдельные ячейки ввести формулы для левой части ограничений
.
Запуск программы «Поиск решения»
- На вкладке Данныев группеАнализвыбрать командуПоиск решения.
- В поле Установить целевую ячейкуввести ссылку нацелевую ячейку.
- В поле Изменяя ячейкиввести ссылку на диапазонизменяемых ячеек.
- Для задания ограниченийв группеОграничения щелкнуть по кнопкеДобавить.
В открывшемся диалоговом окне выполнить следующие действия:
- в поле Ссылка на ячейкуввести ссылку на ячейку с формулой, определяющей первоеограничение(
); - во втором поле выбрать оператор ограничения (>,
- в поле Ограничениеввести значение ограничения
- Для задания следующего ограничения щелкнуть по кнопке Добавитьи повторить операции пункта 4.
- Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окноПоиск решения.
- Изменять и удалять ограничения можно с помощью кнопок ИзменитьиУдалить.
- При необходимости можно задать максимальное время решения, предельное число итераций, относительную погрешность, допустимое отклонение, сходимость, метод поиска. Для этого с помощью кнопки Параметрыоткрыть диалоговое окноПараметры поиска решения.
Если известно, что решаемая задача линейная, то следует включить режим Линейная модель: процесс решения значительно ускорится. Для возврата в диалоговое окно Поиск решениящелкнуть по кнопкеОК.
- Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить.
Полученные результаты будут выведены на рабочий лист. После завершения процедуры решения в диалоговом окне Результаты поиска решенияможно выполнить один из следующих вариантов:
- сохранить найденное решение или восстановить исходные значения на рабочем листе;
- сохранить параметры поиска решения в виде модели;
- сохранить решение в виде сценария;
- просмотреть любой из встроенных отчетов.
Сохранение модели Текущие установочные параметры для поиска решения можно сохранить в виде модели. Для этого надо в диалоговом окне Параметры поиска решениящелкнуть по кнопкеСохранить модельи указать на рабочем листе область для сохранения модели (можно указать только верхнюю ячейку области). При сохранении модели запоминаются целевая ячейка, изменяемые ячейки, ограничения и параметры поиска решения. Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модельв диалоговом окнеПараметры поиска решения. Сохранение сценария Найденные решения (значения изменяемых ячеек) можно сохранить в качестве сценария. Для этого нужно:
- В диалоговом окне Результаты поиска решениявыбратьСохранить сценарий.
- В поле Название сценарияввести имя.
Просмотреть сценарии можно с помощью команды Диспетчера сценариев(на вкладкеДанныев группеРабота с даннымив спискеАнализ «что-если»выбрать командуДиспетчер сценариев). Создания отчета по результатам поиска решения С помощью программы Поиск решенияможно создать три типа отчетов по результатам, полученным при успешном завершении процедуры решения. Каждый отчет создается на отдельном листе текущей рабочей книги. Для создания отчета надо в диалоговом окне Результаты поиска решениявыбрать нужный тип в полеТип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавишаCtrl). Типы отчетов:
- результаты– отчет содержит целевую ячейку, список изменяемых ячеек, их исходные и конечные значения, ограничения и сведения о них;
- устойчивость– отчет содержит сведения о степени зависимости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограничений);
- пределы– выводится целевая ячейка и ее значение, а также список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов.
Ограничение
Для продолжения скачивания необходимо пройти капчу:
Источник: studfile.net