Применение функции поиска решения в Excel

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

Как включить функцию “Поиск решения”

Хотя поиск решения эффективен, он не находится на переднем крае панели инструментов или контекстного меню. Многие пользователи, которые годами работали в Excel, даже не подозревают о его существовании. Дело в том, что по умолчанию он отключен полностью и для добавления его на ленту необходимо выполнить следующие действия:

1. Откройте меню «Файл», щелкнув соответствующее имя.
2. Щелкните раздел «Параметры», расположенный внизу вертикального списка слева.
3. Затем щелкните подраздел «Дополнения». Здесь отображаются все надстройки программы, а внизу будет «Контроль». Справа от него находится раскрывающееся меню, в котором следует выбрать «Надстройки Excel», обычно уже установленные по умолчанию. Щелкните кнопку «Перейти”.
4. На экране появится новое вспомогательное окно «Дополнения». Установите флажок рядом с «Искать решение» и нажмите «ОК.
5. Все готово. Необходимая функция отображается на ленте в правой части вкладки «Данные”.

Подготовительный этап

Добавление функции в ленту программы — это половина дела. Также нужно понимать, как это работает.

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

И перед нами стоит задача назначить скидку на каждый товар так, чтобы сумма всех скидок составляла 4,5 миллиона рублей. Он должен появиться в отдельной ячейке под названием target. Ориентируясь на него, нам нужно рассчитать остальные значения.

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

Эти ячейки (желаемая и целевая) связаны между собой формулой, которую мы записываем в целевой ячейке следующим образом: = D13 * $ G $ 2, где ячейка D13 содержит общую сумму продаж всех товаров, а ячейка $ G $ 2 — абсолютные (неизменные) координаты искомой ячейки.

Применение функции и ее настройка

Формула готова. Теперь нам нужно применить саму функцию.

1. Переходим на вкладку «Данные» и нажимаем кнопку «Найти решение”.
2. Откроется «Параметры», где нужно выставить желаемые настройки. В поле «Оптимизировать целевую функцию:» ​​укажите адрес целевой ячейки, в которой вы планируете отображать суммы для всех скидок. Вы можете ввести координаты вручную или выбрать их из таблицы, для чего нужно сначала щелкнуть область ввода, а затем нужную ячейку.
3. Перейдем к настройке остальных параметров. В разделе «До:» вы можете установить максимальный лимит, минимальный лимит или точное число. В зависимости от активности поставьте галочку напротив опции «Стоимость» и наберите «4500000» — размер скидки на все товары.
4. Следующее поле для заполнения — «Изменение значений переменных:». Вам необходимо ввести координаты нужной ячейки, содержащей определенное значение. Это значение представляет собой скидку, которую мы пытаемся вычислить. Помимо выбора ячейки назначения, координаты можно записать вручную или щелкнув нужную ячейку в самой таблице.
5. Теперь нам нужно отредактировать раздел «В соответствии с ограничениями:», в котором мы устанавливаем ограничения на используемые данные. Например, вы можете исключить десятичные дроби или, например, отрицательные числа. Это делается с помощью кнопки «Добавить”.
6. Откроется вспомогательное окно, в котором можно добавлять ограничения во время расчетов. В первом поле мы указываем координаты конкретной ячейки или области ячейки, для которой должно применяться это условие. По нашей задаче указываем координаты нужной ячейки, в которой будет отображаться значение скидки. Следующим шагом будет определение знака сравнения. Мы устанавливаем «больше или равно», чтобы окончательное число не могло быть отрицательным. «Лимит», который задается в третьем поле, в этом случае будет равен цифре 0, так как именно относительно этого значения ставится условие.вы можете установить другое ограничение с помощью кнопки «Добавить». Дальнейшие действия по настройке будут аналогичными. Когда все будет готово, нажмите ОК.
7. После выполнения вышеуказанных действий только что установленный лимит отобразится в большом поле окна. Список может быть довольно длинным и зависит от сложности предлагаемых расчетов, но в этом случае достаточно одного условия.В этом поле также есть возможность сделать все другие переменные, на которые не влияют ограничения, неотрицательными. Однако будьте осторожны и убедитесь, что нет противоречия между этим параметром и установленными ограничениями, иначе может возникнуть конфликт при расчетах в программе.
8. Вы также можете установить значительное количество дополнительных настроек. Чуть в правом нижнем углу находится кнопка «Параметры», которая позволяет вам это сделать. Щелкните по нему и откройте новое окно.
9. В этих настройках у нас есть возможность установить «Предел точности» и «Предел решения». В нашем случае устанавливать эти параметры не обязательно, поэтому, ознакомившись с представленным окном, вы можете закрыть его, нажав кнопку ОК.
10. Затем производятся все настройки и устанавливаются параметры. Пришло время запустить функцию: для этого нажимаем кнопку «Найти решение”.
11. После этого программа выполнит все необходимые вычисления и отобразит результаты в необходимых ячейках. Сразу откроется окно «Результаты поиска решения», в котором вы можете сохранить / удалить результаты или снова изменить параметры поиска. Если результаты нас устраивают, оставьте галочку напротив опции «Сохранить найденное решение» и нажмите OK. В этом случае, если мы предварительно установим флажок слева от сообщения «Вернуться в диалоговое окно для поиска решения», после нажатия кнопки «ОК» мы вернемся к настройке функции поиска решения.
12. вполне вероятно, что расчеты покажутся некорректными или возникнет желание немного изменить исходные данные и получить другой результат. В этом случае необходимо повторно открыть окно с параметрами поиска решения и внимательно посмотреть на поля с введенными данными.
13. Если с данными все в порядке, вы можете попробовать использовать другой метод решения. Для этого нажмите на текущий вариант и выберите из выпадающего списка наиболее подходящий нам способ:
    • Первый ищет решение нелинейных задач с помощью метода обобщенного приведенного градиента (GPG). Это вариант по умолчанию, но вы можете попробовать другие.
    • Второй — попытаться найти решение линейных задач симплексным методом.
    • Третий использует эволюционные исследования для выполнения поставленной задачи.
    • В случае, если ни один из методов не дал удовлетворительных результатов, стоит еще раз проверить данные в таблице и параметры, так как это наиболее частая ошибка в этом виде деятельности.
14. Теперь, когда мы получили запрошенную скидку, осталось применить ее для расчета суммы скидки для всех товаров. Для этого отметьте первую ячейку столбца «Размер скидки», напишите формулу «= D2 * $ G $ 2» и нажмите Enter. Символы доллара используются для того, чтобы при растягивании / копировании формулы в другие строки ячейка скидки G2 оставалась неизменной в расчетах.
15. Получена скидка на первый товар. Теперь перемещаем курсор в правый нижний угол ячейки с результатом, как только его форма изменится на крест, удерживая левую кнопку мыши, мы распространяем формулу на все строки, для которых хотим вычислить то же самое количество.
16. Теперь наша таблица полностью готова в соответствии с поставленной задачей.

Заключение

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