Поиск решения в Экселе

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

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


Активация «Поиска решения»

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

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

Далее в появившемся разделе переходим во вкладку «Надстройки». После переключения возле клавиши «Управление» появится новый пункт – «Надстройки Excel». Собственно, для перехода кликаем по клавише «Перейти».


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

Собственно, на этом всё. Теперь специальная клавиша для активации данного инструмента появится на верхней панели инструментов вкладки «Данные».


Конфигурация таблицы

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

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

Собственно, в данной формуле расчёта неизвестной переменной является только коэффициент. Именно его мы и будем рассчитывать используя обсуждаемую функцию. Та ячейка, в которой должен располагаться коэффициент называется «искомой».

Обе ячейки (целевая и искомая) необходимо связать между собой при помощи формулы. В данном примере формула располагается в нашей целевой ячейке, и имеет вид «=C10*$G$3», в которой $G$3 – это абсолютный адрес искомой ячейки, а код «C10» - это сумма зарплат, которые вместе получают все работники конкретной фирмы. Итак, давайте перейдём к расчётам.


Практическое применение функции «Поиск решения»

Итак, как только мы подготовили таблицу к произведению расчётов, переходим в раздел «Данные» и в блоке инструментов «Анализ» кликаем по клавише «Поиск решения»,

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

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

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

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

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

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

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

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

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

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

Если вы хотите более тонко настроить данную функцию, то переключитесь во вкладку «Параметры».

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

Итак, как только мы выставили все необходимые параметры, нам необходимо кликнуть по клавише «Найти решение».

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

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

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

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

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


Заключение

Итак, как вы видите, применить функцию «Поиск решения» достаточно просто. Надеемся, что данная статья смогла помочь вам.

Всем мир!

Рейтинг:(голосов:1)