Эксель поиск решения: пример использования функции для решения задачи с неизвестными параметрами

Надстройка «Поиск решения» в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

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

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

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

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

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

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

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

6. Активируйте галочку возле пункта

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

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

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

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

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

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

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

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

   Нажмите «Добавить», чтобы создать первое ограничение.

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

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

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

   «Найти решение».

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

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

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

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

«Поиск решения» в Microsoft Excel — как найти числа из которых складывается сумма | Денис Мокшин

		Опубликовано d. mokshin в чтв, 11/06/2015 — 22:59.

Хороший инструмент — надстройка Поиск решения в MS Excel!

Например, можно использовать в ситуации, когда вам нужно найти вариант из каких различных чисел могла сложиться определнная сумма (может вы ищете из каких счетов могла сложиться сумма оплаты). Допустим, нужно найти по приведенным числам сумму 10:

Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти… Отметить флагом Поиск решения, нажать ОК

На ленте на вкладке Данные появился Поиск решения:

Теперь нужно придумать, как мы можем параметрами в разных строках «подбирать» сумму. Я сделал вариант, когда в столбце мы указываем множитель 0 или 1, в соседнем столбце считаем произведение, и потом значения складываем в итоговую сумму:

• в ячейках столбца B
  указываем 0 или 1 (сейчас неважно, что конкретно)
• в ячейке C4 формула =A4*B4
• в ячейках C5:C14 — аналогично, с учетом номера строки
• в ячейке C3 формула =СУММ(C4:C14)

Теперь запускаем Поиск решения. И заполняем:

• Оптимизировать целевую функцию: $C$3
• До: Значения: 10
• Изменяя ячейки переменных: $B$4:$B$14
• В соответствии с ограничениями: — добавляем (кнопка Добавить) три условия: 1) значения переменных должны быть целые, 2) значения должны быть >= 0, 3) 2) значения должны быть
• Выберите метод решения: Эволюционный поиск решения (выбираем это значение, т. к. у нас негладкая задача)

И нажимаем Найти решение.

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

Через какое-то время отобразится окно Результаты поиска решений. В данном примере всё срослось удачно и решение было найдено:

Снимаем флаг Вернулься в диалоговое окно параметров (если установлено), нажимаем ОК и любуемся результатом.

Если немного изменить начальные данные, то можно получить другие результаты:

Если точный результат не может быть найден, то выдается такое сообщение:

А иногда случается так, что этот функционал вообще не может найти подходящее решение (скорее всего из-за очень большого количества вариантов, которые он просто не успевает перебрать), и сообщает следуещее:

В таком случае иногда помогает изменить немного подход к поиску результата, и искать не конкретное значение, а Минимум отклонения. В нашем примере делаем так:

• в ячейке C3 формула =ABS(СУММ(C4:C14)-B1)

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

• До: Минимум

И мы получаем прекрасный результат!

Solver в Excel (Простое руководство)

Загрузить надстройку Solver | Сформулируйте модель | Проб и ошибок | Решение модели

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

Загрузить надстройку решателя

Чтобы загрузить надстройку решателя, выполните следующие шаги.

1. На вкладке «Файл» щелкните «Параметры».

2. В разделе «Надстройки» выберите «Надстройка Solver» и нажмите кнопку «Перейти».

3. Отметьте надстройку Solver и нажмите OK.

4. Решатель находится на вкладке Данные в группе Анализ.

Формулировка модели

Модель , которую мы собираемся решить , выглядит в Excel следующим образом.

1. Чтобы сформулировать эту модель линейного программирования, ответьте на следующие три вопроса.

а. Какие решения предстоит принять? Для этой задачи нам нужен Excel, чтобы узнать, сколько нужно заказать каждого товара (велосипедов, мопедов и детских кресел).

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

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

2. Чтобы упростить понимание модели, создайте следующие именованные диапазоны.

Название диапазона	Ячейки
Единая прибыль	С4:Е4
Размер заказа	С12:Е12
Используемые ресурсы	G7:G8
Доступные ресурсы	И7:И8
Общая прибыль	И12

3. Вставьте следующие три функции СУММПРОИЗВ.

Объяснение: Сумма используемого капитала равна сумме произведений диапазона C7:E7 и OrderSize. Объем используемого хранилища равен сумме произведений диапазона C8:E8 и OrderSize. Общая прибыль равна сумме UnitProfit и OrderSize.

Пробы и ошибки

Благодаря этой формулировке становится легко анализировать любое пробное решение.

Например, если мы закажем 20 велосипедов, 40 мопедов и 100 детских кресел, общее количество использованных ресурсов не превысит количество доступных ресурсов. Это решение имеет общую прибыль 19000.

Нет необходимости использовать метод проб и ошибок. Далее мы опишем, как можно использовать Excel Solver для быстрого поиска оптимального решения.

Решить модель

Чтобы найти оптимальное решение , выполните следующие шаги.

1. На вкладке Данные в группе Анализ щелкните Solver .

Введите параметры решателя (читайте дальше). Результат должен соответствовать картинке ниже.

У вас есть выбор: ввести имена диапазонов или щелкнуть ячейки в электронной таблице.

2. Введите TotalProfit для цели.

3. Щелкните Макс.

4. Введите OrderSize для меняющихся переменных ячеек.

5. Щелкните Добавить, чтобы ввести следующее ограничение.

6. Установите флажок «Сделать переменные без ограничений неотрицательными» и выберите «Симплекс LP».

7. Наконец, нажмите «Решить».

Результат:

Оптимальное решение:

Вывод: оптимально заказать 94 велосипеда и 54 мопеда. Это решение дает максимальную прибыль 25600. Это решение использует все доступные ресурсы. Попробуй сам. Загрузите файл Excel, введите параметры решателя (предыдущие 7 шагов) и найдите оптимальное решение.

Как использовать решатель в Excel?

Решающая программа — это математический инструмент, присутствующий в MS-Excel, который используется для выполнения расчетов при определенных ограничениях/условиях, а затем вычисляет решение задачи. Он работает с целевой ячейкой, изменяя ячейки переменных с помощью ограничений суммы.

Решатель присутствует в MS-Excel, но для его использования необходимо его активировать. Для активации решателя нам необходимо сделать следующие шаги:

Шаг 1: Перейдите к файлу и выберите опции . Появится следующее диалоговое окно.

Шаг 2: Теперь выберите опцию Надстройки и нажмите Go и, наконец, нажмите OK .

Шаг 3: После нажатия OK выберите надстройку Solver и нажмите OK . Теперь решатель будет активирован в Excel.

Шаг 4: Теперь решатель появится в раздел данных вот так.

Теперь давайте разберемся, как использовать решатель на примере.

Пример:

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

Итак, предположим, мы купили следующие предметы.

Предположим, что мы купили вышеперечисленные товары только в одном количестве, а общее количество составило 9300, но ваучер был на 10 000 рупий. Итак, теперь мы хотим использовать Solver для этой цели. Теперь посмотрим, как это будет сделано.

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

Шаг 2: В установленной цели мы должны выбрать общее значение столбца D, потому что мы хотим, чтобы значение изменилось с 9300 на 10 000. После нажатия на D7 на заданном целевом блоке отобразится следующее.

Шаг 3: Теперь в «Изменив ячейку переменной» мы выберем ячейку «Количество», потому что мы хотим изменить количество таким образом, чтобы общая сумма достигла 10 000.

Шаг 4: Теперь мы должны установить некоторые условия, при которых мы хотим, чтобы наша работа была выполнена. Итак, для установки некоторых условий / ограничений мы нажмем «Добавить».

Шаг 5 – Теперь появится диалоговое окно, и мы добавим 3 условия. Первое условие – общая сумма должна быть равна сумме ваучера. Итак, мы выберем ячейку D7 в ссылке на ячейку, а затем знак =, и, наконец, мы выберем ячейку C11.