Поиск решений в Excel — пример использования сервиса поиск решений в Excel. Оптимизировать целевую функцию

«Поиск решений» в программе Excel 2007 не является стандартной надстройкой. Она необходима для сложных вычислений при наличии более одной неизвестной. Таким образом, она не включена в обыкновенный набор параметров программы. Однако когда в ней существует необходимость, надстройка предлагает пользователю эффективную работу, а также высокую продуктивность.

Что представляет собой «Поиск решений»?

Это надстройка программы. В обыкновенной конфигурации, которая выпускается производителем, данный пакет не предусмотрен. Он должен быть загружен и настроен отдельно. Зачастую пользователи обходятся без него. Кроме того, надстройку часто называют «Решателем», так как она ведет точные и оперативные вычисления, независимо от того, какой сложности является задача. В случае с оригинальной версией Microsoft Office проблемы с установкой не возникают. Пользователю необходимо выполнить следующие переходы: Параметры – Сервис – Надстройки – Управление − Надстройки Excel. В результате на экране отобразится окно, где будет присутствовать кнопка перехода. Если нажать на нее, появится список всех надстроек, которые предлагаются пользователю, как установленных, так и невостребованных. Далее потребуется отыскать «Поиск решений», а после этого установить около него отметку. Инструмент станет активным, его можно использовать когда угодно.

Для чего необходим Решатель? Зачем используется «Поиск решений» в программе Excel 2007, и какова необходимость в его установке? Если у пользователя имеется целевая функция, которая зависит от нескольких параметров, надстройка подберет решения задачи, соответствуя исходным данным. Ними может оказаться переменная, неизвестная или, допустим, итоговое значение. Другими словами, пользователь будет иметь начальные характеристики, а также ответ. Что касается самой программы, она подберет ход решения, предоставит формулу. Стоит отметить, что с помощью надстройки существует возможность отыскать следующее:

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

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

Принцип работы Решателя? Кроме Решателя, в программе Excel существует такая функция, как выбор параметра. Она необходима для использования тогда, когда существует лишь одно неизвестное значение. Такая возможность нуждается в меньших затратах ресурсов, поэтому результат выдается оперативнее. Поиск решений в программе Excel 2007 используется для задач наибольшей сложности, в которых присутствует несколько неизвестных, часто наблюдаются переменные. Таким образом, все можно представить так: Найти неизвестные − несколько «x». При условии, что − формула либо функция. При ограничениях в данном случае обычно указано на неравенство или минимальные/максимальные значения. Кроме того, следует указать на ячейки, с которыми нужно проводить вычисления. Существует возможность решать сразу несколько различных задач при условии задания программе соответствующих параметров.

Настройка параметров «Поиска решений» Для корректной работы функции «Поиска решений» в программе Excel 2007 работала следует ввести правильные параметры. Как правило, они ограничиваются несколькими (1−3) характеристиками, однако с задачами большей сложности нужна глобальная настройка. Параметры в «Поиске решений» Office Excel 2007 бывают такие:

1. Максимальное время – число секунд, выделяемые пользователем для программы на решение, зависит от уровня сложности задачи.
2. Максимальное число интеграций. В данном случае речь идет о количествах ходов, выполняемых программой при решении задачи. Когда параметр увеличивается, то ответ не доходит.
3. Погрешность либо точность, зачастую используется в ходе решения десятичных дробей (например, до 0,0001).
4. Допустимое отклонение. Применяется в процессе работы с процентами.
5. Неотрицательные значения. Используется в том случае, когда решается функция, имеющая два правильных ответа (допустим, +/-X).
6. Отображение результатов интеграций. Эта настройка предусматривается тогда, когда важен как результат решений, так и их ход.
7. Метод поиска – подбор оптимизационного алгоритма. Как правило, используется «метод Ньютона». Когда все настройки будут выбраны, необходимо нажать кнопку сохранения.

Параметры задачи в функции «Поиска решений»

Работа этой надстройки выполняется в соответствии с указанными характеристиками вычисления. Самой важной из них выступает метод. Существует два их варианта. «Метод Ньютона» представляет собой настройку по умолчанию. Он работает с большей памятью, однако меньшими интеграциями. Таким образом, для стандартных и несложных уравнений он подходит. Кроме того, существует «метод сопряженных градиентов». В данном случае запрашивается меньше памяти, однако необходимо больше интеграций. Таким образом, в случае его использования существует возможность решать даже самые сложные уравнения, применять масштабные формулы и функции.

Формула в Excel

Стоит указать на обязательный элемент, без которого не способна работать надстройка «Поиска решений» в программе Excel 2007. В данном случае речь идет о формулах. Они являются выражением, которое осуществляет определенное вычисление. Без равенства формулы работать не могут. Таким образом, программа не способна распознавать таковую, если нет соответствующего знака. Формула состоит из следующих составляющих:

1. Функция. Стандартная формула, в которой имеется определенная и конкретная очередность действий, изменить ее не получится.
2. Ссылка. Указывает на число клеток, которые требуется решить. Ячейки при этом способны размещаться хаотично либо в определенном порядке.
3. Оператор. Представляет собой символ, задающий тип вычисления (+ – сложение, * – умножение и прочее.).
4. Константа. Является постоянным значением, которое всегда остается неизменным. Для его получения не требуется выполнять вычисления.

Решение формул производится слева направо, важно соблюдение всех математических правил.

Создание формулы

Формулы представляют собой уравнения, которые необходимы для выполнения вычислений программы. Когда они не вводятся, «Поиск решения» в программе Excel не будет работать. Задачи также не будут решаться. Таким образом, для корректной работы нужно правильно ввести формулу. Расчет начинается с равенства. Если в ячейке указано «=КОРЕНЬ(номер клетки)», то используется соответствующая функция. Когда напечатана главная формула со знаком «=», необходимо указать на данные, с которыми она взаимодействует. Чтобы отыскать требуемую информацию, следует использовать функцию поиска.

Постановка задачи

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

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

Перед началом оптимизации необходимо будет составить несложную таблицу на листе Excel – нашу математическую модель, описывающую ситуацию:

Подразумевается, что:

• Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.
• Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
• Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.
• Желтые (C12:G12) и синие (H10:H11) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
• Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.

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

Решение

В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно же, давно разработаны способы их решения. Excel предоставляет пользователю один из них – с помощью мощной надстройки Поиск решения (Solver) , доступной в Excel 2003 через в меню Сервис (Tools) или с вкладки Данные (Data) в новых версиях Excel.

Если в меню Сервис или на вкладке Данные вашего Excel такой команды нет – ничего страшного - значит надстройка просто еще не подключена. Для ее подключения:

• в Excel 2003 и старше - откройте меню Сервис – Надстройки (Tools – Add - Ins ) , в появившемся окне установите флажок Поиск решения (Solver ) и нажмите ОК . Excel активирует выбранную надстройку и в меню Сервис (Tools ) появится новая команда – Поиск решения (Solver ) .
• в Excel 2007 и новее - нажать кнопку Офис , далее выбрать Параметры Excel – Надстройки – Перейти (Excel Options - Add-Ins - Go To) .

Запустим надстройку. Откроется вот такое окно:

В этом окне нужно задать следующие настройки:

Кроме очевидных ограничений, связанных с физическими факторами (вместимость складов и средств перевозки, ограничения бюджета и сроков и т.д.) иногда приходится добавлять ограничения «специально для Excel». В нашем случае, например, нужно будет добавить вот такое ограничение:

Оно дополнительно уточнит, что объем перевозимого товара (зеленые ячейки) не может быть отрицательным – для человека такое само собой очевидно, но для компьютера это надо прописать явно.

После настройки всех необходимых параметров окно должно выглядеть следующим образом:

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

Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.

Если найденное решение нам подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий . По желанию пользователя Excel может построить три типа Отчетов по решаемой задаче на отдельных листах: отчет по результатам, отчет по математической устойчивости решения и отчет по пределам (ограничениям) решения, однако они, в большинстве случаев, интересны только специалистам.

Бывают, однако, ситуации, когда Excel не может найти подходящего решения. Имитировать такой случай можно, если указать в нашем примере требования магазинов в сумме большие, чем общая вместимость складов. Тогда при выполнении оптимизации Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение. Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.

Рассмотренный пример, конечно, является относительно простым, но легко масштабируется под решение гораздо более сложных нелинейных задач. Например:

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

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

Надстройка Excel «Поиск решения» – это аналитический инструмент, который позволяет нам быстро и легко определить, когда и какой результат мы получим при определенных условиях. Возможности инструмента поиска решения намного выше, чем может предоставить «подбор параметра » в Excel.

Основные отличия между поиском решения и подбором параметра:

1. Подбор нескольких параметров в Excel.
2. Наложение условий ограничивающих изменения в ячейках, которые содержат переменные значения.
3. Возможность использования в тех случаях, когда может быть много решений одной задачи.

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

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

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

Надстройка «Поиск решения» - позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:

Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.

Ограничение параметров при поиске решений

Допустим, вы пошли в банк с этой таблицей, но банк отказывается поднять Вам процентную ставку. В таком случаи нам нужно узнать, насколько нам придется повысить сумму ежегодных вложений. Мы должны установить ограничение на ячейку с одним переменным значением. Но перед началом измените значения в переменных ячейках на исходные: в B1 на 5%, а в B2 на -1000$. А теперь делаем следующее.

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

Можно долго искать на ленте, где находится Поиск решения, но так и не найти данный инструмент. Просто, для активации данной функции, нужно её включить в настройках программы.

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

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

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

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

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

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

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

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

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

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

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

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

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

После этого, открывается окно добавления ограничения. В поле «Ссылка на ячейки» прописываем адрес ячеек, относительно которых вводится ограничение. В нашем случае, это искомая ячейка с коэффициентом. Далее проставляем нужный знак: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное», и т.д. В нашем случае, мы выберем знак «больше или равно», чтобы сделать коэффициент положительным числом. Соответственно, в поле «Ограничение» указываем число 0. Если мы хотим настроить ещё одно ограничение, то жмем на кнопку «Добавить». В обратном случае, жмем на кнопку «OK», чтобы сохранить введенные ограничения.

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

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

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

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

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

Если по какой-либо причине результаты поиска решений вас не удовлетворяют, или при их подсчете программа выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше способом, в диалоговое окно параметров. Пересматриваем все введенные данные, так как возможно где-то была допущена ошибка. В случае, если ошибка найдена не была, то переходим к параметру «Выберите метод решения». Тут предоставляется возможность выбора одного из трех способов расчета: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По умолчанию, используется первый метод. Пробуем решить поставленную задачу, выбрав любой другой метод. В случае неудачи, повторяем попытку, с использованием последнего метода. Алгоритм действий всё тот же, который мы описывали выше.

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