Инструмент «Поиск решения» в Excel

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

Как включить «Поиск решений» в Excel

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

1. Запустите Excel. Лучше заранее открыть в нём какой-либо документ. Чтобы сделать это просто нажмите два раза по файлу XLSX или XLS. Также нужный файл можно перенести в рабочую область программы.
2. Далее нажмите на кнопку «Файл» в верхней левой части окна.



3. Обратите внимание на левое меню приложения. Там нужно будет воспользоваться кнопкой «Параметры».



4. Будет открыто отдельное окошко со всеми настройками Excel. Вам нужно перейти в раздел «Надстройки», что расположен в левом меню.
5. В поле «Управление» поставьте значение «Надстройки Excel». Оно должно там стоять по умолчанию. Нажмите «Перейти».



6. Здесь установите галочку у пункта «Поиск решения» и нажмите «Ок».

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

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

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

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

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

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

1. Рядом с таблицей выделим несколько ячеек в одном столбце. Желательно от основной таблицы отступить несколько столбцов.
2. Залейте ячейки цветом для удобства их дальнейшего определения. Чтобы это сделать, нажмите по иконке заливки в основной панели (левая часть) и выберите там наиболее удобный цвет для заливки.
3. Над выделенными ячейками создайте заголовок «Коэффициенты» или назовите его как будет удобно.

Подробно про то, как создать заголовок в Excel мы писали в отдельной статье.

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

В строку с формулами не нужно вводить общий бюджет для премий. Он вводится на другом этапе!

Работа с инструментом «Поиск решения»

Когда таблица полностью готова к работе, вам осталось только воспользоваться функцией «Поиск решения»:

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



3. Будет открыто окошко для внесения пользовательских данных. У поля «Оптимизировать целевую функцию» нажмите на иконку в виде таблички.
4. Откроется строка, куда нужно вписать параметры поиска решения. В данном случае нужно будет выделить ячейку, куда вы прописывали специальную формулу из предыдущего заголовка. Программа сама её оптимизирует под конкретную задачу. После этого потребуется снова кликнуть по иконке таблицы, чтобы вернуться в редактор настроек.
5. Затем поставьте маркер у пункта «Значения», чтобы вписать нужное число. В данном случае это будет 30 000 – наш бюджет, закладываемый на премию сотрудникам.
6. Теперь пропишите в «Изменяя значения переменных» адрес ячейки, в которой должен находится коэффициент. Умножением на него заработной платы мы получим подробный расчёт величины премии для каждого сотрудника.
7. Далее воспользуйтесь кнопкой «Добавить», которая расположена в левой части окошка.
8. Откроется окошко добавления ограничений. В нашем случае ограничением является искомая ячейка с коэффициентом.
9. Затем нужно будет выбрать знак для операции. Программа предлагает несколько знаков: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное» и другие. В нашем случае разумнее всего будет выбрать «больше или равно».
10. В следующее поле «Ограничение» укажите число «0». Если вы хотите добавить какое-то дополнительное ограничение, то придётся нажать на иконку в виде таблички.
11. Заполнив все данные жмите на «Ок», чтобы параметры применились.
12. Теперь в окошке с настройками поставьте галочку у пункта «Сделать переменные без ограничений отрицательными».

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

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

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

Читайте также:
Как сделать таблицу в Microsoft Excel
Как работать с формулами в Excel: пошаговая инструкция
Умная таблица в Excel (Эксель): создание и использование
Способы объединения строк в редакторе Excel без потери данных

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

Если была допущена ошибка

Возможно, вы видите, что расчёты не совпадают с вашими собственными предположениями или вы поняли, что где-то в формулах/таблице допустили ошибку. Тогда можно просто внести изменения в работу скрипта.

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

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