Содержание:
«Поиск решения» — надстройка Excel, с помощью которой можно выбрать лучшее решение задачи, исходя из заданных ограничений. Функция дает возможность планировать работу сотрудников, распределять затраты или инвестиции. Знание того, как работает эта функция, сэкономит вам время и усилия.
Что такое поиск решений
В сочетании с различными другими опциями в Excel есть еще одна менее популярная, но крайне необходимая функция «Поиск решения». Несмотря на то, что найти его непросто, знакомство с ним и использование помогают в решении многих проблем. Опция обрабатывает данные и выдает оптимальное решение из разрешенных. В статье описано, как непосредственно функционирует «Поиск решения».
Как включить функцию «Поиск решения»
Несмотря на эффективность, рассматриваемая опция не находится на видном месте панели инструментов или контекстного меню. Большинство пользователей, работающих в Excel, не знают о его наличии. По умолчанию эта функция отключена, для ее отображения выполните следующие действия:
- Откройте «Файл», нажав на соответствующее имя.
- Нажмите на раздел «Настройки».
- Затем выберите подраздел «Дополнения». Здесь будут отображаться все дополнения программы, внизу появится надпись «Управление». В правой части от него появится всплывающее меню, в котором следует выбрать «Надстройки Excel». Затем нажмите «Перейти».
1 - На мониторе отобразится дополнительное окно «Надстройки». Установите флажок рядом с нужной функцией и нажмите «ОК».
- Нужная функция появится на ленте справа от раздела «Данные».
О моделях
Эта информация будет чрезвычайно полезна для тех, кто только знакомится с понятием «модель оптимизации». Перед использованием «Поиска решения» рекомендуется изучить материалы по методам построения моделей:
- рассматриваемый вариант позволит определить лучший метод для распределения средств на инвестиции, загрузку помещения, поставку товаров или другие действия, где необходимо найти лучшее решение.
- «Оптимальный метод» в такой ситуации будет означать: увеличение дохода, снижение затрат, улучшение качества и т. д.
Типичные задачи оптимизации:
- Определение плана производства, при котором прибыль от реализации выпущенной продукции будет максимальной.
- Определение транспортных схем, при которых затраты на транспортировку минимизируются.
- Искать распределение нескольких станков для различных видов работ, чтобы снизить затраты на производство.
- Определение кратчайших сроков выполнения работ.
Важно! Для формализации задачи необходимо создать модель, отражающую основные параметры предметной области. В Excel модель представляет собой набор формул, в которых используются переменные. Рассматриваемый вариант ищет такие показатели, у которых целевая функция больше (меньше) или равна заданному значению.
Подготовительный этап
Прежде чем размещать функцию на ленте, необходимо понять, как работает опция. Например, есть информация о реализации товаров, указанных в таблице. Задача — назначить для каждого товара скидку, которая составит 4.5 млн руб. Параметр отображается внутри ячейки с именем target. На основе него рассчитываются другие параметры.
Нашей задачей будет вычислить скидку, на которую умножаются суммы продажи различных товаров. Эти 2 элемента связаны формулой, записанной следующим образом: =D13*$G$2. Где в D13 записано общее количество для реализации, а $G$2 — адрес искомого элемента.
Использование функции и ее настройка
Когда формула готова, нужно использовать непосредственно саму функцию:
- Вам необходимо переключиться в раздел «Данные» и нажать «Поиск решения».
- Откроется «Параметры», где выставлены необходимые настройки. В строке «Оптимизировать целевую функцию:» следует указать ячейку, в которой отображается сумма скидок. Координаты можно прописать самостоятельно или выбрать из документа.
- Далее необходимо перейти к настройкам других параметров. В разделе «Кому:» можно установить максимальный и минимальный лимиты или точное число.
- Затем заполняется поле «Изменение значений переменных:». Здесь вводятся данные нужной ячейки, в которой содержится определенное значение. Координаты прописываются самостоятельно или по клику на соответствующую ячейку в документе.
- Затем редактируется вкладка «По ограничениям:», где задаются ограничения на применяемые данные. Например, исключаются десятичные дроби или отрицательные числа.
- После этого откроется окно, позволяющее добавить ограничения в расчетах. В начальной строке содержатся координаты ячейки или целого диапазона. Следуя условиям задачи, указываются данные нужной ячейки, где отображается индикатор скидки. Затем определяется знак сравнения. Для него установлено значение «больше или равно», чтобы окончательное значение не было со знаком минус. В этой ситуации «Лимит», установленный в строке 3, равен 0. Также можно установить лимит с помощью «Добавить». Дальнейшие действия такие же.
- После выполнения вышеуказанных действий установленный лимит появится в самой большой строке. Список может быть большим и будет зависеть от сложности вычислений, однако в конкретной ситуации достаточно и 1 условия.
- Кроме того, можно выбрать и другие расширенные настройки. В правом нижнем углу есть опция «Параметры», позволяющая это сделать.
- В настройках можно установить «Ограничение точности» и «Пределы решения». В нашей ситуации нет необходимости использовать эти варианты.
- Когда настройки будут завершены, запустится сама функция – нажмите «Найти решение».
- После программа производит необходимые расчеты и выдает окончательные расчеты в нужные ячейки. Затем открывается окно с результатами, где результаты сохраняются/отменяются, либо настраиваются параметры поиска по новому. Когда данные соответствуют требованиям, найденное решение сохраняется. Если заранее установить флажок «Вернуться к диалоговому окну параметров поиска решения», откроется окно с настройками функции.
- Есть вероятность, что расчеты оказались ошибочными или возникла необходимость изменить исходные данные для получения других показателей. В такой ситуации нужно заново открыть окно настроек и перепроверить информацию.
- Если данные точны, можно использовать альтернативный метод. Для этих целей необходимо нажать на текущий вариант и выбрать наиболее подходящий способ из появившегося списка:
- Поиск решения с использованием обобщенного градиента для нелинейных задач. По умолчанию используется этот вариант, но можно использовать и другие.
- Поиск решений линейных задач на основе симплекс-метода.
- Использование эволюционного поиска для выполнения задачи.
Внимание! Когда вышеперечисленные варианты не справились с задачей, следует еще раз проверить данные в настройках, так как зачастую это является основной ошибкой в подобных задачах.
- Когда желаемая скидка получена, остается применить ее для расчета суммы скидки по каждому товару. Для этого выделяется начальный элемент графы «Сумма скидки», записывается формула «=D2*$G$2» и нажмите «Ввод». Знаки доллара проставлены для того, чтобы при растягивании формулы на соседние строки G2 не менялась.
- Теперь будет получена сумма скидки для исходного товара. Затем следует навести курсор на угол ячейки, когда он станет «плюсом», нажимается ЛКМ и формула растягивается на нужные строки.
- После этого стол будет окончательно готов.
Загрузить/сохранить параметры поиска
Эта опция полезна при применении различных опций ограничений.
- В меню «Параметры поиска решений» нажмите «Загрузить/сохранить».
- Введите диапазон области модели и нажмите «Сохранить» или «Загрузить».
При сохранении модели вводится ссылка на 1 ячейку пустого столбца, куда будет помещена оптимизационная модель. Во время загрузки модели вводится ссылка на весь диапазон, содержащий оптимизационную модель.
Важно! Для сохранения последних настроек в меню «Параметры решения» сохраняется рабочая книга. Каждый лист в нем имеет свои собственные параметры надстройки Solver. Кроме того, на листе можно задать более 1 задачи, нажав кнопку «Загрузить или сохранить», чтобы сохранить отдельные задачи.
Простой пример использования Solver
Необходимо загрузить контейнер тарой так, чтобы его масса была максимальной. Резервуар имеет объем 32 кубических метра. м. Заполненный ящик имеет вес 20 кг, объем 0,15 куб.м. м. Ящик – 80 кг и 0,5 куб. м. Требуется, чтобы общее количество контейнеров было не менее 110 шт. Данные организованы следующим образом:
Переменные модели отмечены зеленым цветом. Целевая функция выделена красным цветом. Ограничения: по наименьшему количеству контейнеров (больше или равно 110) и по весу (=СУММПРОИЗВ(B8:C8,B6:C6) – общий вес тары в контейнере.
По аналогии считаем общий объём: =СУММПРОИЗВ(B7:C7,B8:C8). Такая формула необходима для установления ограничения на общий объем контейнеров. Затем через «Поиск решения» вводятся ссылки на элементы с переменными, формулами и самими показателями (или ссылки на конкретные ячейки). Разумеется, количество контейнеров — целое число (это тоже ограничение). Нажимаем «Найти решение», в результате чего находим такое количество контейнеров, когда общая масса максимальна и учтены все ограничения.
Поиск решения не дал результатов
Такое уведомление появляется, когда рассматриваемая функция не нашла комбинации оценок переменных, удовлетворяющих каждому ограничению. При использовании симплекс-метода вполне возможно, что решения не будет.
Когда используется метод решения нелинейных задач, во всех случаях начиная с начальных показателей переменных, это свидетельствует о том, что возможное решение далеко от таких параметров. Если запустить функцию с другими исходными показателями переменных, то решение наверняка есть.
Например, при использовании нелинейного метода элементы таблицы с переменными не заполнялись, и функция не находила решения. Это не означает, что решения нет. Теперь, учитывая результаты определенной оценки, в элементы вводятся другие данные с переменными, близкими к полученным.
В любой ситуации изначально следует проверить модель на отсутствие конфликта ограничений. Зачастую это связано с неправильным выбором коэффициента или ограничивающего показателя.
В приведенном примере максимальный показатель объема составляет 16 куб. м вместо 32, поскольку такое ограничение противоречит показателям минимального количества мест, поскольку оно будет соответствовать числу 16,5 куб.м. м.
Заключение
Исходя из этого, опция «Поиск решения» в Excel поможет в решении конкретных задач, которые достаточно сложно или невозможно решить обычными способами. Сложность применения этого метода в том, что изначально эта опция скрыта, из-за чего большинство пользователей не подозревают о ее наличии. Кроме того, функцию довольно сложно изучить и использовать, но при правильном исследовании она принесет большую пользу и облегчит расчеты.