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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel Mobile Еще…Меньше

“Поиск решения” — это программная надстройка для Microsoft Office Excel, которая доступна при установке Microsoft Office или приложения Excel.

Чтобы можно было работать с надстройкой “Поиск решения”, ее нужно сначала загрузить в Excel.

  1. В Excel 2010 и более поздних версий выберите Файл > Параметры.

    Примечание: В Excel 2007 нажмите кнопку Microsoft Office кнопку Изображение кнопки Officeи выберите Excel параметры.

  2. Выберите команду Надстройки, а затем в поле Управление выберите пункт Надстройки Excel.

  3. Нажмите кнопку Перейти.

  4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

    Примечания: 

    •   Если надстройка Поиск решения отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы найти ее.

    • Если появится сообщение о том, что надстройка “Поиск решения” не установлена на компьютере, нажмите кнопку Да, чтобы установить ее.

  5. После загрузки надстройки для поиска решения в группе Анализ на вкладки Данные становится доступна команда Поиск решения.

  1. В меню Сервис выберите Надстройки Excel.

  2. В поле Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

    • Если надстройка Поиск решения отсутствует в списке поля Доступные надстройкинажмите кнопку Обзор, чтобы найти ее.

    • Если появится сообщение о том, что надстройка “Поиск решения” не установлена на компьютере, нажмите в диалоговом окне кнопку Да, чтобы ее установить.

    После загрузки надстройки “Поиск решения” на вкладке Данные станет доступна кнопка Поиск решения.

В настоящее время надстройка “Поиск решения”, предоставляемая компанией Frontline Systems, недоступна для Excel на мобильных устройствах.

“Поиск решения” — это бесплатная надстройка для Excel 2013 с пакетом обновления 1 (SP1) и более поздних версий. Для получения дополнительной информации найдите надстройку “Поиск решения” в Магазине Office.

В настоящее время надстройка “Поиск решения”, предоставляемая компанией Frontline Systems, недоступна для Excel на мобильных устройствах.

“Поиск решения” — это бесплатная надстройка для Excel 2013 с пакетом обновления 1 (SP1) и более поздних версий. Для получения дополнительной информации найдите надстройку “Поиск решения” в Магазине Office.

В настоящее время надстройка “Поиск решения”, предоставляемая компанией Frontline Systems, недоступна для Excel на мобильных устройствах.

“Поиск решения” — это бесплатная надстройка для Excel 2013 с пакетом обновления 1 (SP1) и более поздних версий. Для получения дополнительной информации найдите надстройку “Поиск решения” в Магазине Office.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Постановка и решение задачи с помощью надстройки “Поиск решения”

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах

Сочетания клавиш в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Нужна дополнительная помощь?

Нужны дополнительные параметры?

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

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

Поиск решения в Microsoft Excel

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

Включение функции

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

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

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

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

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

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

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

Целевая ячейка в Microsoft Excel

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

Искомая ячейка в Microsoft Excel

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

Связующая формула в Microsoft Excel

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

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

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

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

    Lumpics.ru

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

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

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

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

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

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

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

  21. После того, как все настройки установлены, жмем на кнопку «Найти решение».
  22. Переход к поиску решения в Microsoft Excel

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

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

Выбор метода решения в Microsoft Excel

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Наряду со множеством других возможностей, в 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, в которой требуется найти оптимальное значение для определенного диапазона данных, может вручную перебирать все возможные варианты или использовать вспомогательные формулы для расчетов. Однако это все сложно и часто не нужно, ведь можно обратиться к надстройке «Поиск решения», задать для нее цель, ограничения и указать область с переменными значениями, чтобы программа сама высчитала идеальное решение для вас.

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

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

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

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

Заполнение исходных сумм счетов в таблице для использования функции Поиск решения в Microsoft Excel

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

Добавление фиксированных процентов в таблице для использования функции Поиск решения в Microsoft Excel

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

Добавление максимальных довложений в таблице для использования функции Поиск решения в Microsoft Excel

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

Отображение итоговых сумм в таблице для использования функции Поиск решения в Microsoft Excel

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

Сложение итоговых счетов в таблице для использования функции Поиск решения в Microsoft Excel

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

Перенос суммы счета на следующие циклы для использования функции Поиск решения в Microsoft Excel

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

Функция суммы значений для счета для использования функции Поиск решения в Microsoft Excel

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

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

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

Подписаться

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

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

  1. В таблице перейдите на вкладку «Файл».Переход на вкладку Файл для использования функции Поиск решения в Microsoft Excel

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

  3. Из появившегося меню выберите пункт «Параметры».Переход в Параметры для использования функции Поиск решения в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ExcelИнструменты ms office+2

Анонимный вопрос

14 марта 2019  · 47,0 K

ОтветитьУточнить

tDots.ru5,6K

Мы смотрим на бизнес через цифры и знаем, как получить максимум пользы.   · 19 мар 2019  · tdots.ru

Файл – Параметры – Надстройки – Надстройки Excel – Перейти

Ставите галочку “Поиск решения”, жмете ОК.

Ищете надстройку на вкладке “Данные” в группе “Анализ”

30,1 K

Аля

10 сентября 2019

Спасибо)

Комментировать ответ…Комментировать…

Вы знаете ответ на этот вопрос?

Поделитесь своим опытом и знаниями

Войти и ответить на вопрос

Добавить комментарий