Lumpics lumpics.ru

Функция 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
  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

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

20 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
Сергей
13 августа 2018 12:01

Для Максима Тютюшева. Спасибо за содействие. На обычном ПК

Аноним
20 декабря 2017 02:20

Не показана формула в столбце D

Алексей
22 марта 2018 14:44

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

Алена
10 мая 2018 17:34

А можно ли как-то посмотреть, как уже найдено готовое решение? ну то есть как формулу можно посмотреть, можно ли здесь открыть и посмотреть, что заложено в форму «параметры поиск решения»?

Сергей
9 августа 2018 09:02

После попытки установки описанным способом выдается сообщение: нужный файл находиться на сетевом ресурсе, который сейчас недоступен.
…..укажите другой путь к папке, содержащей пакет установки «SingleImageWW.msi»,
что делать?

Евгений
18 августа 2018 17:03

excel 2003 solver не пересчитывает все заданные переменные, переменных всего 7-мь. И ещё один вопросик, в excel 2003 надстройка в
формате *.xll встаёт без вопросов, как установить ту-же настройку в ms office exsel 2016, в частности ta-lib.xll

Lilya
9 декабря 2018 21:22

Здравствуйте! Подскажите пожалуйста, в чёс может быть проблема? Буквально на днях Поиск решения работал, сейчас выдаёт вот такую ошибку.
В параметра Центра безопасности указала разрешения для всех макросов.

comment_image_reloaded_45467280
Lilya
12 декабря 2018 03:15
Ответить на  Максим Тютюшев

В одной книге, после её копирования на новый.

Аноним
7 декабря 2020 16:43
Ответить на  Lilya

не заработало у вас? У меня такая же проблема появилась

Заглянувший
28 июня 2019 10:24

В столбце D3:D9 должны стоять формулы, это произведение зарплаты на вычисленный коэффициент. Иначе столбик премии сотрудникам будут пустые 🙂

Аноним
28 июня 2019 17:38

Это элементарщина, свой вопрос я уже решил

Дарья
10 октября 2019 11:10

Здравствуйте. При поиске решений есть только отчет о результатах, как включить отчет об устойчивости?

comment_image_reloaded_45467168
Евгения
20 ноября 2019 17:47

Здравствуйте! Почему-то в Excel 2007 очень странный Поиск решения — нет формы выбора метода решения, поэтому выдаваемый ответ далёк от эталонного. Как можно это исправить?

Евгения
20 ноября 2019 20:13
Ответить на  Евгения

Появляется только такая флрма Поиска решения

screenshot_20191120_174732_com.android.gallery3d
screenshot_20191120_174736_com.android.gallery3d
Евгения
29 декабря 2019 00:39

Спасибо! Установила новую версию, теперь всё в порядке

Задать вопрос