Рис.4 — Результат поиска решения
Результат вполне закономерный: цены в накладной образуют ряд 1,05 0,75 и 2,20 грн. Разумеется, при таких ценах нельзя подобрать количества, чтобы выйти на сумму ровно 860,83 грн.
Примечание
Если сохранить «промежуточное» решение, оно в нашем случае будет практически оптимальное. Это — следствие малого количества переменных
Чтобы Excel смог найти решение, нужно переформулировать задачу: искать не точное значение, а минимальное отклонение результата поиска от заданной суммы в 860,83 грн.
Для этого в отдельных ячейках запишем искомое значение и формулу для определения разности между результатом поиска и заданной суммой. Например, в ячейку L42 запишем число 860,83 , а в M42 – формулу =ABS(L42-K42). Функция ABS() находит модуль разности между текущим решением и заданной суммой.
Соответственно целевой ячейкой в поиске решения будет M42, ограничения и варьируемые переменные остаются прежними, а вот критерий изменится: вместо строгого равенства устанавливаем флажок «Минимальному значению». Нажимаем «Выполнить» и Excel предлагает вариант решения: Ручка гел. 123 шт , Карандаш 92 шт., Роллер 236 шт. на общую сумму 860,82 грн. Отклонение составило 0,01 грн., что вполне допустимо в практической работе.
4.2.3. Настройка параметров оптимизатора и оптимизация модели
Получив некоторый опыт использования надстройки «Поиск решения», поговорим подробнее о том, что у нее внутри. Надстройка позволяет решать линейные и нелинейные задачи оптимизации, содержащие до 200 переменных. Если задача линейная, количество ограничений может быть любым. Для нелинейных задач допустимо использовать до 100 сложных ограничений и до 400 простых (верхний предел, нижний предел). Для решения нелинейных задач используется алгоритм нелинейной оптимизации Generalized Reduced Gradient (GRG2). Линейные задачи решаются симплекс – методом, линейные целочисленные – методом ветвей и границ.
В практике бухгалтера действительно нелинейные задачи (например, подсчет суммы сложных процентов) встречаются редко, целочисленные — сплошь и рядом. Иногда от нелинейности можно избавиться, перестроив модель. Поясним сказанное, вернувшись к последнему примеру. Предположим, что требуется отыскать решение, в котором на каждую единицу товара «Ручка гел» приходилось бы три Роллера. Если записать в ячейку L31 формулу =E31/E29 и добавить в оптимизатор ограничение $L$31=3, модель станет нелинейной и будет тем самым неоправданно усложнена. При попытке отметить в параметрах оптимизатора пункт «Линейная модель», в окне результатов поиска решения будет выдано сообщение «Условия для линейной модели не удовлетворяются», и оптимизатор будет вынужден использовать гораздо более сложный алгоритм GRG2. В правой части окна для ввода ограничений (рис. 3) можно указывать не только число или адрес, но и формулу. Если мы введем ограничение $E$29=3*$E$31, задача останется линейной. Но действовать таким способом не рекомендуется: оптимизатор в отличие от Excel не умеет пересчитывать формулы «на лету», и сильно замедляет работу. Кроме того, такое ограничение будет считаться сложным, а их допускается применять не более 100. Лучше перенести формулу в отдельную ячейку листа и сослаться на ее адрес. Оптимальным решением в данном случае будет следующее: расценивать три роллера и одну ручку как комплект, и решать нашу задачу относительно переменных «Карандаш» и «Комплект». Задача останется линейной, а число переменных сократится до двух.
Несколько слов о вычислительной сложности задач. Сложность линейной задачи пропорциональна количеству переменных. Вычислительная сложность нелинейной задачи пропорциональна количеству переменных и характеру математических связей между ними. Оптимум задачи с простыми и гладкими функциями вида x2 или x*y, надстройка «Поиск решения» находит легко и быстро. Если функции сложные, например, периодические, в диапазоне допустимых значений переменных может оказаться несколько локальных оптимумов. Придется повозиться, чтобы отыскать среди них один глобальный. Если же в оптимизируемой функции есть разрывы, например, в модели используется хотя бы одна формула вида =ЕСЛИ(A1>0;B1*C1;B2*C2), найти решение будет очень тяжело. Возможно, придется разбить задачу на части, решить их по отдельности и сравнить результаты.
Отметим, что при использовании надстройки «Поиск решения» нельзя применять функции, обладающие «внутренней логикой» работы. Например, нельзя использовать функцию СУММЕСЛИ(). Применение этой функции превратит задачу из алгебраической в логическую, а их надстройка «Поиск решения» не решает.
Наш пример является «маленькой, но вредной» задачей и это неспроста. Вычислительная сложность целочисленных задач экспоненциально растет с ростом числа переменных. Целевая функция при этом – дискретная (значит, разрывная) для каждого значения целочисленной переменной модели. Для того чтобы отыскать глобальный оптимум, нужно в пределе перебрать все сочетания значений переменных. Оптимизатор борется с экспоненциальным ростом сложности задачи, применяя метод ветвей и границ. Сначала задача решается симплекс – методом в рациональных числах, потом решение проверяется для целых значений переменных. Ветви, дающие результат хуже рекорда, отбрасываются, т.е. дальше не исследуются. Это существенно ускоряет поиск,. но существует вероятность ошибочно отбросить самое лучшее решение.
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.