Команда поиск решения в excel

Команда поиск решения в excel

Одной из самых интересных функций в программе 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».

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Вопросы и ответы в сфере it технологий и настройке ПК

Функция поиска решения пригодится при необходимости определить неизвестную величину

Табличный процессор Microsoft Excel может выполнять не только простые операции с числами (сложение, умножение), расчет суммы или среднего значения. У этой программы имеется очень мощный функционал, который позволяет решать задачи разной сложности. Например, Эксель может оптимизировать значения в таблице, подставляя их таким образом, чтобы они удовлетворяли определенным критериям. Для этого программа оснащена специальными средствами для анализа данных: первый — это подбор параметра, а второй — поиск решения.

Видео пример поиска решения в Excel

Функция «Подбор параметра»

Подбор параметра в Excel позволяет подобрать какой-то определенный параметр, значение которого неизвестно. Чтобы было понятней, можно привести такой пример. Допустим, есть прямоугольник со сторонами A и B. Известно, что общая площадь этой фигуры составляет 400 квадратных метров, а сторона B — 40 метров. Сторона A неизвестна и, соответственно, нужно ее найти. Для решения такой задачи необходимо заполнить рабочий лист программы теми данными, которые уже известны. Для этого нужно создать таблицу с 2 колонками и 3 строками (диапазон ячеек A1:B3).

Первый столбец будет содержать название сторон прямоугольника и букву, обозначающую его площадь (т.е. A, B и S). А во втором столбце необходимо указать известные значения:

  • в соседней ячейке для стороны B (ячейка B2) написать — 40 (значение для стороны А остается пустым);
  • а в соседнем поле для площади прямоугольника (поле B3) написать следующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было сделано правильно, то в поле B3 должно быть значение 0. Затем надо выделить эту ячейку и выбрать в панели меню пункты: «Сервис — Подбор параметра». В появившемся окне нужно указать то значение, которое должно быть получено в результате, т.е. 400. В строке «Установить в ячейке» будет указано поле «B3»: менять его не нужно, так и должно быть (сюда будет выведен результат). А в строке «Изменяя значение» необходимо выбрать неизвестный параметр, т.е. поле B1. После нажатия кнопки «ОК» программа выдаст результат: сторона А — 10 метров, а в поле общей площади прямоугольника будет указано число 400.

Это была очень простая задача на уровне 3 класса, но с помощью такой функции можно решать и более сложные задачи. Например, вы решили приобрести себе автомобиль в кредит. Вы точно знаете, что сможете выплачивать ежемесячную выплату в размере 1000 $ (но не больше), а также, что банк выдает автокредит с процентной ставкой 6,5%. Суть задачи заключается в следующем: «Какова максимальная сумма машины, которую можно взять в кредит на таких условиях?». То есть теперь программа будет искать стоимость автомобиля, отталкиваясь от того, что ежемесячный платеж не должен превышать 1000 $. Такой пример является уже более сложным, а также более практичным, нежели расчет площади прямоугольника.

Читайте также:  Защитный лак для печатных плат

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

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

Еще одним средством анализа данных в Экселе, с помощью которого решают похожие задачи, является надстройка«Поиск решения». Если в первом случае Excel мог подбирать значение только в одной ячейке, то с помощью этой надстройки можно оптимизировать одновременно несколько значений. Эта функция имеется во всех версиях Excel, но по умолчанию она отключена. Чтобы включить эту надстройку в Excel 2003 версии, необходимо в панели меню выбрать пункты «Сервис — Надстройки» и поставить галочку напротив пункта «Поиск решения». После этого эту надстройку можно вызвать через этот же пункт «Сервис». В новых версиях существует другой способ: надо щелкнуть пункты «Файл — Параметры — Надстройки», затем выбрать «Надстройки Excel — Перейти» и поставить галочку напротив нужной строки.

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

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

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

Отблагодари меня, поделись ссылкой с друзьями в социальных сетях:

На этом шаге мы рассмотрим процедуру поиска решения.

В Excel предусмотрен мощный инструмент — Поиск решения , который позволяет расширить процедуру подбора параметра следующим образом:

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

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

  • Значение в целевой ячейке зависит от других значений в ячейках и результатов расчета по формулам. Нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным или заранее определенным.
  • Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками . Их значения надо подобрать так, чтобы получить желаемый результат в целевой ячейке.
  • Решение (значение изменяемых ячеек) должно находиться в определенных пределах или удовлетворять определенным ограничениям .

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

Знакомство с процедурой поиска решения начнем с простого примера. На рис. 1 показан рабочий лист, с помощью которого можно вычислить прибыль от продажи трех видов продукции. В столбце В отображено количество товара по каждому изделию, в столбце С — доход от продажи одной единицы изделия. В столбце D содержатся формулы, по которым вычисляется доход от продажи каждого вида товара путем умножения их количества на прибыль от продажи одной единицы изделия.

Рис. 1. Рабочий лист, иллюстрирующий пример расчета количества изделий, приносящих максимальную прибыль

У компании есть несколько ограничений, которые она должна учитывать:

  • Общий объем производства — всего 300 единиц изделий в день.
  • Компании нужно произвести 50 единиц изделия А для выполнения существующего заказа.
  • Компании нужно произвести 40 единиц изделия В для выполнения планового заказа.
  • Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не больше 40 единиц этого изделия.

Процедура поиска решения выполняется следующим образом.

  1. Введите в рабочий лист исходные данные и формулы (рис. 1).
  2. Вызовите диалоговое окно Поиск решения .
  3. Укажите целевую ячейку.
  4. Укажите изменяемые ячейки.
  5. Задайте ограничения.
  6. При необходимости измените опции процедуры поиска решения.
  7. Позвольте процедуре поиска решения выполнить поставленную задачу.

Чтобы запуcтить процедуру поиска решения , выберите команду Сервис | Поиск решения .

Читайте также:  Toshiba satellite l650 1l2

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

В результате выполнения указанных действий появится диалоговое окно Поиск решения (рис. 2).

Рис. 2. Диалоговое окно Поиск решения

В этом примере в целевой ячейке D6 вычисляется общая прибыль по трем видам изделий. В поле Установить целевую ячейку укажите адрес ячейки D6 или щелкните в рабочем листе на этой ячейке. Поскольку наша цель — максимизировать значение в этой ячейке, установите переключатель Равной максимальному значению . Затем определите изменяемые ячейки, которые в данном случае находятся в диапазоне В3:В5 .

Дальше введите ограничения задачи. Ограничения добавляются по одному за один раз и отображаются в окне Ограничения . Для добавления ограничений щелкните на кнопке Добавить . Появится диалоговое окно Добавление ограничения (рис. 3).

Рис. 3. Диалоговое окно Добавление ограничения

В этом диалоговом окне нужно ввести ссылку на ячейку, оператор и значение. Первое ограничение — общий объем продукции должен быть равен 300 единицам изделий. Введите адрес ячейки B6 , выберите оператор равно (=) из раскрывающегося списка операторов и введите в качестве значения число 300 . Для добавления очередного ограничения щелкните на кнопке Добавить . В таблице 1 приведены все ограничения для этой задачи.

После ввода последнего ограничения щелкните но кнопке OK . Вы вернетесь к диалоговому окну Поиск решения , которое в нашем примере должно содержать четыре ограничения. Теперь для выполнения процедуры поиска решения введены все исходные данные. Чтобы начать процесс решения задачи, щелкните на кнопке Выполнить . В строке состояния будет отображаться ход решения задачи. Через некоторое время на экране появится информация о том, что решение найдено. Диалоговое окно Результаты поиска решения показано на рис. 4.

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

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

  • Заменить исходные данные значения в изменяемых ячейках на те, которые были найдены в результате решения задачи.
  • Восстановить исходные значения в изменяемых значениях.
  • Создать несколько отчетов о процедуре поиска решения (для выбора нескольких отчетов из списка нажмите клавишу Shift и щелкните на нужном типе отчета).
  • Щелкнуть на кнопке Сохранить сценарий для сохранения решения в виде сценария, который может быть использован в средстве Диспетчер сценариев .

Если Вы указали, что нужно создать отчет, он будет помещен на новом рабочем листе, которому будет присвоено соответствующее имя. На рис. 5 показан отчет по результатам поиска решения . В разделе отчета Ограничения будет указано состояние всех ограничений. Тип связанный означает, что данное ограничение удовлетворено, но при этом соответствующий параметр принял свое предельное значение, которое уже нельзя изменять.

Рис. 5. Отчет по результатам поиска решения

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

Рис. 6. Диалоговое окно Параметры поиска решения

Данное окно содержит следующие параметры процедуры поиска решения:

  • Максимальное время — ограничение времени решения задачи.
  • Предельное число итераций — максимальное число промежуточных решений, допускаемых при поиске.
  • Относительная погрешность — точность выполнения ограничений и соответствия вычисленного значения в целевой ячейке с заданным.
  • Допустимое отклонение — максимальное отклонение в процентах для целочисленных решений.
  • Сходимость — применяется к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость , поиск прекращается.
  • Линейная модель — служит для ускорения поиска решения, применяя к задаче оптимизации линейную модель.
  • Неотрицательные значения — устанавливает нулевую нижнюю границу для тех влияющих ячеек, для которых не заданы ограничения.
  • Автоматическое масштабирование — автоматическая нормализация входных и выходных значений, значительно различающихся по величине.
  • Показывать результаты итераций — если установить эту опцию, то после выполнения очередной итерации процесс поиска решения приостанавливается и на экране отображается найденный результат.
  • Разделы Оценки, Разности, Метод поиска — используются для контроля некоторых технических аспектов решения задачи. В большинстве случаев нет необходимости их изменять.
  • Загрузить модель — отображает диалоговое окно Загрузить модель , в котором можно задать ссылку на диапазон ячеек рабочего листа, содержащих параметры модели.
  • Сохранить модель — отображает диалоговое окно Сохранить модель , в котором нужно определить ссылку на диапазон ячеек рабочего листа, где будут сохранены параметры модели.

На следующем шаге мы рассмотрим примеры задач поиска решений .

Ссылка на основную публикацию
Ключ для word windows 10
Рано или поздно любой пользователь может столкнуться с проблемой поиска ключа Office. Конечно, если лицензия на него приобретена, то таких...
Калибровка монитора macbook pro
Сервисный центр MacPlus (ремонт Apple) раскрывает секреты калибровки дисплея iMac и Macbook Вы замечали, что одни и те же изображения...
Калькулятор градусов и минут сложение и вычитание
Калькулятор, поддерживающий основные арифметические действия над выражениями с градусами. Создан по запросу пользователя. Этот калькулятор выполняет арифметические действия над градусами....
Ключ для эксель 365 лицензионный ключ бесплатно
Еще несколько лет назад компаня Майкрософт планировала свой пакет продавать вместе уже с компьютерами, как сейчас происходит с операционной системой...
Adblock detector