Как найти параметры поиска решения в эксель

Наряду со множеством других возможностей, в Microsoft Excel есть одна малоизвестная, но очень полезная функция под названием “Поиск решения”. Несмотря на то, что найти и освоить ее, может быть, непросто, ее изучение и применение может помочь в решении огромного количества задач. Функция берет данные, перебирает их и выдает самое оптимальное решение из возможных. Итак, давайте разберемся, как именно работает поиск решения и попробуем применить данную функцию на практике

Как включить функцию “Поиск решения”

Несмотря на свою эффективность, функция “Поиск решения” не находится в первых рядах панели инструментов или контекстного меню. Многие пользователи, работающие в Excel годами, даже не подозревают о ее существовании. Дело в том, что по умолчанию она вообще отключена и для ее добавления на ленту нужно проделать следующие шаги:

1. Открываем меню “Файл”, кликнув по соответствующему названию.
2. Кликаем по разделу “Параметры”, который находится внизу вертикального перечня с левой стороны.
3. Далее щелкаем по подразделу “Надстройки”. Здесь отображаются все надстройки программы, а внизу будет надпись “Управление”. Справа от нее представлено выпадающее меню, в котором должны быть выбраны “Надстройки Excel”, обычно уже установленные по умолчанию. Нажимаем кнопку “Перейти”.
4. На экране появится новое вспомогательное окно “Надстройки”. Устанавливаем флажок напротив опции “Поиск решения” и нажимаем ОК.
5. Все готово. Требуемая функция появится на ленте в правой части вкладки “Данные”.

Подготовительный этап

Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.

Итак, у нас есть данные про продаже товаров, представленные в табличном виде.

И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.

Наша задача – вычислить скидку, на которую будут умножены все суммы по продажам всех наименований. Она и будет найдена с помощью функции “Поиск решения”, а ячейка с этой скидкой будет называется искомой.

Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.

Применение функции и ее настройка

Формула готова. Теперь нужно применить саму функцию.

1. Переключаемся во вкладку “Данные” и нажимаем кнопку “Поиск решения”.
2. Откроются “Параметры”, где необходимо задать нужные настройки. В поле “Оптимизировать целевую функцию:” указываем адрес целевой ячейки, где планируется вывести сумму по всем скидкам. Можно прописать координаты вручную, либо выбрать из таблицы, для чего сначала кликаем по области ввода, затем – по нужной ячейке.
3. Переходим к настройке других параметров. В пункте “До:” можно задать максимальную границу, минимальную границу или же точное число. Исходя из поставленной задачи ставим отметку рядом с опцией “Значение” и набираем “4500000” – сумма скидок по всем наименованиям.
4. Следующее для заполнения поле – “Изменяя значения переменных:”. В него нужно внести координаты искомой ячейки, содержащей определенное значение. Это значение и есть та самая скидка, которую мы пытаемся вычислить. Также, как и с выбором целевой ячейки, координаты можно написать вручную, либо кликнуть по нужной ячейке в самой таблице.
5. Теперь нужно отредактировать раздел “В соответствии с ограничениями:”, в котором задаем ограничения используемых данных. Например, можно исключить десятичные дроби или, скажем, отрицательные числа. Это делается через кнопку “Добавить”.
6. Откроется вспомогательно окно, позволяющее добавить ограничения во время вычислений. В первом поле указываем координаты определенной ячейки или области ячеек, для которых это условие должно действовать. Согласно нашей задаче, указываем координаты искомой ячейки, в которой будет выводиться значение скидки. Следующий шаг – определить знак сравнения. Устанавливаем “больше или равно”, чтобы итоговое число не могло быть отрицательным. “Ограничение”, которое устанавливается в третьем поле, в этом случае будет равно цифре 0, поскольку именно относительно этого значения задается условие.Можно установить еще одно ограничение с помощью кнопки “Добавить”. Дальнейшие действия по его настройке будут аналогичными. По готовности щелкаем OK.
7. После выполнения описанных выше действий в самом большом поле окна появится установленное только что ограничение. Список может быть довольно большим и зависит от сложности предполагаемых расчетов, но в данном случае будет достаточно и одного условия.Под этим полем также есть опция, позволяющая делать все остальные переменные, не затрагиваемые ограничениями, неотрицательными. Однако, будьте внимательны и проследите за тем, чтобы между этим параметром и поставленными ограничениями не было противоречия, иначе при расчете в программе может возникнуть конфликт.
8. Также можно задать немалое количество дополнительных настроек. Чуть ниже справа есть кнопка “Параметры”, позволяющая это сделать. Нажимаем на нее и открываем новое окно.
9. В этих настройках у нас есть возможность установить “Точность ограничения” и “Пределы решения”. В нашем случае задавать данные параметры нет необходимости, поэтому после ознакомления с представленным окном, его можно закрыть, нажав OK.
10. Итак, все настройки выполнены и параметры установлены. Пора запускать функцию – для этого нажимаем кнопку “Найти решение”.
11. После этого программа сделает все необходимые расчеты и выдаст результаты в нужных ячейках. При этом сразу же откроется окно “Результаты поиска решения”, где можно сохранить/отменить результаты или настроить параметры поиска заново. Если результаты нас устраивают, оставляем отметку напротив опции “Сохранить найденное решение” и нажимаем ОК. При этом, если мы предварительно установим галочку слева от надписи “Вернуться в диалоговое окно параметров поиска решения”, после того, как мы щелкнем OK, мы обратно переключимся к настройке функции поиска решения.
12. Вполне вероятно, что расчеты могут показаться неправильными, либо возникнет желание немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно посмотреть поля с введенными данными.
13. Если с данными все нормально, можно попробовать задействовать другой метод решения. Для этого щелкаем по текущему варианту и из раскрывшегося перечня выбираем способ, который нам кажется наиболее подходящим:
    • Первый – ищет решение методом обобщенного приведенного градиента (ОПГ) для нелинейных задач. Стандартно выбран именно этот вариант, но можно попробовать и другие.
    • Второй – пытается отыскать решение для линейных задач, используя симплекс-метод.
    • Третий – для выполнения поставленной задачи использует эволюционный поиск.
    • В том случае, если ни один из методов не принес удовлетворительных результатов, стоит проверить данные в таблице и параметрах еще раз, поскольку именно это является самой частой ошибкой в подобного рода задачах.
14. Теперь, когда мы получили требуемую скидку, осталось ее применить, чтобы рассчитать суммы скидок по всем наименованиям. Для этого отмечаем первую ячейку столбца “Сумма скидки”, пишем в ней формулу “=D2*$G$2” и нажимаем Enter. Знаки доллара ставятся для того, чтобы при растягивании/копировании формулы на другие строки, ячейка G2 со скидкой оставалась неизменной в расчетах.
15. Мы получили сумму скидки для первого наименования. Теперь наводим курсор на нижний правый угол ячейки с результатом, как только он поменяет форму на крестик, зажав левую кнопку мыши растягиваем формулу на все строки, по которым хотим посчитать аналогичную сумму.
16. Теперь наша таблица полностью готова в соответствии с поставленной задачей.

Заключение

Таким образом, функция “Поиск решения” в Эксель может помочь в решении определенных задач, которые достаточно сложно или невозможно решить простыми методами. Однако, проблема в использовании данного способа заключается в том, что по умолчанию данная функция скрыта в программе, из-за чего многие пользователи не догадываются о ее существовании. Также функция довольно трудна в освоении и использовании, но при ее должном изучении, она может принести значительную пользу и облегчить работу.

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

Как раз об этой опции и пойдет речь далее.

Используемый пример для поиска решения

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

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

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

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

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

Для этого я сначала добавляю функцию СУММ для суммы счетов и считаю сумму каждого в последнем цикле.

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

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

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

Включение надстройки «Поиск решения»

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

1. В таблице перейдите на вкладку «Файл».

2. Откройте раздел «Другие».

3. Из появившегося меню выберите пункт «Параметры».

4. Откройте категорию настроек «Надстройки» и отыщите пункт с названием «Поиск решения», после чего выделите его нажатием левой кнопки мыши.

5. Кликните по кнопке «Перейти», находящейся внизу окна.

6. Активируйте галочку возле пункта «Поиск решения» и нажмите «ОК», чтобы выйти из данного окна.

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

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

Настройка «Поиска решений» для таблицы

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

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

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

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

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

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

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

7. Расчет происходит буквально за несколько секунд, после чего мы видим оптимальное решение. В моем случае каждый цикл на балансы начислялось меньше 250, в один месяц даже 0, а в конце всех циклов получилось достичь нужной суммы с точностью до сотых. «Найти решение» показало, как мне действовать каждый цикл, чтобы вкладывать минимальную сумму, но дойти до нужного результата в конце. У вас решение может быть совершенно другим.

8. Если же программа посчитала все возможные исходы и в итоге не нашла решения, на экране появится информация об ошибке. Сравните полученные значения в таблице, чтобы понять, на каком этапе произошло завершение вычислений, то есть программа уперлась в установленные ограничения. В итоге вам нужно будет увеличить количество циклов или изменить эти самые ограничения.

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

Я ставил цель показать вам, как работает программа «Поиск решения» в Microsoft Excel, чтобы вы узнали, как можно автоматически найти оптимальные значения для большой таблицы, избегая ручной переборки значений. Надеюсь, все объяснения и примеры были вам понятны, и теперь вы освоили еще одну очень удобную функцию, упрощающую взаимодействие с электронными таблицами, созданными в Экселе.

«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил. Рассказываем, как освоить функцию поиска решений.

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

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

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

Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).

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

Целевая функция — результирующий показатель, для которого Excel подбирает наилучшие показатели. Чтобы программа понимала, какие данные наилучшие, мы задаем функцию в виде формулы. Эту формулу мы отображаем в отдельной ячейке. Результирующий показатель может принимать максимальное или минимальное значения, а также быть конкретным числом. 

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

Пример использования поиска решений

Теперь перейдем к самой функции. 

1) Чтобы включить «Поиск решений», выполните следующие шаги:

• нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
• в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
• в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым — изменяемые ячейки.

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение». 

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения: 

• общее количество изделий 1000 штук ($D$13 = $D$3); 
• число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0); 
• количество дней меньше либо равно 30 ($F$9:$F$12 < = $D$6, либо как в примере в ячейке F13 задать функцию МАКС(F9:F12) и поставить ограничение $F$13 < = $D$6).

5) В конце проверьте полученные данные на соответствие заданному целевому значению. Если что-то не сходится — нужно пересмотреть исходные данные, введенные формулы и ограничения.

Хотите научиться решать задачи в Excel, как это делают в компаниях-лидерах? Приходите на наш онлайн-курс, на котором вы освоите этот инструмент на уровне профи. Вашими преподавателями будут эксперты-практики, а после обучения вы сможете дополнить резюме весомой строчкой. Регистрируйтесь!

Была ли эта статья полезной?