ЦЕЛЬ: освоение приемов решения задач оптимизации (линейного и нелинейного программирования).
Для решения задач оптимизации используется инструмент Данные анализ- Поиск решения. Предварительно необходимо, как говорят, поставить задачу, то есть записать на бумаге систему уравнений или неравенств (ограничений) и критерии оптимальности. Только после этого стоит приступать к ее решению. При этом можно выделить несколько этапов:
· для каждой переменной (неизвестной) следует отвести одну ячейку определить для нее имя. Желательно разместить эти ячейки рядом, а в клетках сверху записать их имена. Тогда процедура определения имени через пункт Формулы - Определение имени - Присвоить Имя будет проще, а анализ исходных данных – более наглядным. Для еще большей наглядности можно закрасить ячейки каким-либо цветом. В этих ячейках будет размещен результат решения задачи. Проблема здесь состоит в том, что искомые переменные в математике чаще всего называют и т.д. Многие стремятся дать такие же имена ячейкам, где они будут храниться. Однако сделать это нельзя, так как в таблице существуют ячейки с адресами X1, X2, X3. Поэтому следует использовать другие имена, например, XX1, XX2, XX3 и так далее, или буквы русского алфавита (что имеет свои достоинства и недостатки). Если же решается конкретная экономическая задача, где в качестве неизвестных выступают, например, виды продукции, то неизвестным можно присваивать имена в виде сокращенных названий видов продукции;
|
|
· отвести ячейку для критерия оптимальности и записать его в виде формулы, ссылаясь не на адреса, а на имена ячеек, определенных на предыдущем этапе, например: =xx1+4*xx2-3*xx3+2*xx4-xx5. Здесь в качестве имен использованы латинские буквы. Ячейке, содержащей критерий оптимальности, также можно присвоить имя. Особенно это удобно при решении экономической задачи. Например, ячейка может называться “Прибыль” или “затраты” и т.д., то есть то, что подлежит максимизации или минимизации;
· отвести на каждое ограничение дону ячейку (желательно разместив их друг под другом). В эти ячейки следует ввести левые части ограничений в виде формул. Знак сравнения и правая часть ограничения задаются позже. Например: =xx1+3*xx2+xx3-xx4-2*xx5.
· Вызвать инструмент Данные – Анализ – Поиск решения. При этом на экран выводится окно, в котором будет нужно:
1) указать ячейку, где находится критерий оптимальности;
2) указать диапазон ячеек, где будет сформирован результат (ячейки переменных);
3) указать ячейки, где записаны ограничения, и задать для каждого ограничения операцию сравнения и правую часть;
4) как правило, также задаются ограничения на неотрицательность переменных. Для удобства их целесообразно ввести в виде одного ограничения, указав диапазон, где записаны переменные, а не перечислять переменные по одной;
|
|
5) изменить, если это необходимо, параметры расчетов (точность, время расчета, количество итераций и др.). На этапе изучения, кроме точности результата, ничего менять не рекомендуется;
6) нажать кнопку Выполнить;
7) оформить полученное решение в виде отчета. При решении реальных задач целесообразно сформировать и другие виды отчетов (они перечислены в меню). Каждый отчет записывается на новый лист;
8) проанализировать результат, при необходимости внести поправки в ограничения или критерий оптимальности и повторить расчеты.
Здесь приведены самые необходимые сведения для решения задач оптимизации. Кроме этого, инструмент Поиск решения предоставляет много сервисных функций, которые обеспечивают удобство при многовариантных расчетах и проведение разнообразных экспериментов, например с использованием сценариев. В данной работе эти средства не рассматриваются. Все внимание должно быть направлено на изучение приемов решения задачи.
1. В справке найдите раздел «Поиск решения» и изучите его. Потратьте на это не менее 10-15 минут. Скорее всего, вам не все будет понятно. Старайтесь уловить общий смысл изучаемого материала.
2. На чистом листе сформулируйте исходные данные для решения следующей задачи:
Например, ячейкам А2, В2, С2, D2, E2 присвойте имена переменных, в нашем случае это хх1, хх2, хх3, хх4, хх5.
В ячейку А4 введите ограничение =4*хх1+3*хх2+хх3+2*хх4+12*хх5 (левая часть первого уравнения),
в ячейку В4 - =13 (правая часть уравнения).
В ячейку А5 введите ограничение =хх2-хх3+2*хх4+8*хх5 (левая часть второго уравнения),
в ячейку В5 - =7 (правая часть уравнения).
В ячейке F2 будет находиться результат оптимальности (целевая функция =хх1+4*хх2-3*хх3+2*хх4-хх5)
Войдите Данные – Анализ - Поиск решения и в окне Параметры поиска решения выполнить пункты с 1) по 4) и найдите решение
Ответ: X=(0,0,1,0,1) при F=-4.
3. Решите более сложную задачу.
Войдите в пункт Данные – Анализ – Поиск решения и укажите целевую ячейку, диапазон ячеек с переменными и 6 ограничений. Найдите решение и проанализируйте его. Сохраните файл на диске.
Если вы решите задачу верно, то значение целевой функции F=53,125.
4. Вновь найдите в справке раздел «Поиск решения» и изучите его с учетом полученных знаний. Вы должны теперь понять гораздо больше в этом материале. Сделайте необходимые записи в свои тетради об инструменте Поиск решения. Помните, что этот инструмент является важнейшим в тех случаях, когда нужно найти наилучшее решение при ограниченных возможностях. Он работает на стыке трех отраслей знаний: математики, экономики и информационных технологий.