Наряду со многими другими функциями Microsoft Excel имеет малоизвестную, но очень полезную функцию под названием «Найти решение». Хотя его может быть трудно найти и освоить, его изучение и применение могут помочь решить множество проблем. Функция берет данные, перебирает их и выдает наиболее оптимальное возможное решение. Итак, давайте выясним, как именно работает поиск решения и попробуем реализовать эту функцию
Как включить функцию “Поиск решения”
Читайте также: «Как пронумеровать страницы в Excel”
Несмотря на свою эффективность, функция «Найти решение» не находится в передней части панели инструментов или в контекстном меню. Многие пользователи, годами работающие в Excel, даже не подозревают о его существовании. Дело в том, что по умолчанию он обычно отключен и для того, чтобы его добавить на ленту, нужно выполнить следующие действия:
- Откройте меню «Файл», нажав на соответствующее имя.
- Нажмите на раздел «Настройки», который находится внизу вертикального списка с левой стороны.
- Далее нажмите на подраздел «Плагины». Здесь отображаются все плагины программы, а внизу будет надпись «Администрирование». Справа есть выпадающее меню, где необходимо выбрать «Надстройки Excel», которые обычно уже установлены по умолчанию. Нажмите кнопку «Перейти”.
- На экране появится новое вспомогательное окно «Дополнения». Установите флажок рядом с параметром «Поиск решения» и нажмите «ОК.
- Все готово. Нужная функция появится на ленте справа от вкладки «Данные”.
Подготовительный этап
Добавление функции на ленту программы — полдела. Вы также должны понимать, как это работает.
Итак, у нас есть данные о реализации товаров, представленные в табличной форме.
И перед нами стоит задача назначить скидку на каждый товар таким образом, чтобы сумма всех скидок составила 4,5 млн рублей. Он должен появиться в отдельной ячейке под названием target. Ориентируясь на него, мы должны вычислить остальные значения.
Наша задача — рассчитать скидку, на которую будут умножены все объемы продаж всех товаров. Его найдут с помощью функции «Поиск решения», и ячейка с этой скидкой будет называться нужной.
Мы соединяем данные ячейки (поиск и пункт назначения) вместе с формулой, которую мы пишем в ячейке назначения следующим образом: =D13*$G$2, где ячейка D13 содержит общие продажи всех товаров, а ячейка $G$2 содержит абсолютный (без изменений) координаты нужной ячейки.
Применение функции и ее настройка
См также: «Как построить график в Excel”
Формула готова. Теперь нам нужно применить саму функцию.
- Перейдите на вкладку «Данные» и нажмите кнопку «Найти решение”.
- Откроются «Опции», где нужно установить нужные настройки. В поле «Оптимизировать целевую функцию:» укажите адрес целевой ячейки, в которой вы планируете отображать сумму всех скидок. Координаты можно ввести вручную или выбрать их из таблицы, для чего кликаем сначала по области ввода, а затем по нужной ячейке.
- Перейдем к другим настройкам. В пункте «До:» можно установить максимальный лимит, минимальный лимит или точное число. В зависимости от задачи ставим галочку напротив опции «Стоимость» и пишем «4500000» — сумма скидок по всем позициям.
- Следующее поле для заполнения – «Изменить значения переменных:». Вам нужно ввести координаты нужной ячейки, которая содержит определенное значение. Это значение и есть та самая скидка, которую мы пытаемся рассчитать. Как и при выборе целевой ячейки, координаты можно ввести вручную, либо нажав на нужную ячейку в самой таблице.
- Теперь нужно отредактировать раздел «По ограничениям:», в котором устанавливаем ограничения на используемые данные. Например, вы можете исключить десятичные дроби или, скажем, отрицательные числа. Делается это через кнопку «Добавить”.
- Откроется вспомогательное окно, позволяющее добавлять ограничения во время расчета. В первом поле укажите координаты конкретной ячейки или диапазона ячеек, для которых должно действовать это условие. В соответствии со своей задачей указываем координаты нужной ячейки, в которой будет отображаться значение скидки. Следующим шагом является определение знака сравнения. Мы устанавливаем «больше или равно», чтобы итоговое число не могло быть отрицательным. «Ограничение», которое устанавливается в третьем поле, в данном случае будет равно числу 0, так как именно относительно этого значения устанавливается условие.
Вы можете установить другое ограничение с помощью кнопки «Добавить». Дальнейшие действия по его настройке будут аналогичными. Нажмите OK, когда будете готовы.
- После выполнения описанных выше действий вновь установленное ограничение появится в самом большом поле окна. Список может быть достаточно большим и зависит от сложности предлагаемых расчетов, но в этом случае будет достаточно одного условия.
Ниже этого поля также есть возможность сделать все другие переменные, на которые не влияют ограничения, неотрицательными. Однако будьте внимательны и следите за тем, чтобы между этим параметром и установленными ограничениями не было противоречия, иначе может возникнуть конфликт при расчете в программе.
- Вы также можете настроить значительное количество дополнительных параметров. Чуть ниже справа есть кнопка «Опции», позволяющая это сделать. Нажмите на нее и откройте новое окно.
- В этих настройках у нас есть возможность установить «Предел точности» и «Пределы принятия решений». В нашем случае эти параметры настраивать не нужно, поэтому после ознакомления с представленным окном его можно закрыть, нажав кнопку ОК.
- Итак, все настройки сделаны и параметры заданы. Пришло время запустить функцию: для этого нажмите кнопку «Найти решение”.
- После этого программа сделает все необходимые расчеты и выведет результаты в нужные ячейки. Это сразу откроет окно «Результаты решения», где вы можете сохранить/отменить результаты или перенастроить параметры поиска. Если результаты нас устраивают, оставляем галочку напротив опции «Сохранить найденное решение» и нажимаем ОК. В этом случае, если мы сначала поставим галочку слева от надписи «Вернуться к диалогу параметров поиска решения», после нажатия ОК мы снова настроим функцию поиска.
- Возможно, расчеты покажутся вам неверными, или вы захотите немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно просмотреть поля с введенными данными.
- Если с данными все в порядке, можно попробовать использовать другой метод решения. Для этого нажимаем на текущий вариант и из открывшегося списка выбираем метод, который кажется наиболее подходящим:
- Первый ищет решение с использованием метода обобщенного приведенного градиента (GRG) для нелинейных задач. По умолчанию выбран этот вариант, но вы можете попробовать и другие.
- Второй пытается найти решение линейных задач с помощью симплекс-метода.
- Третий использует эволюционный поиск для выполнения задачи.
- В том случае, если ни один из способов не дает удовлетворительных результатов, стоит еще раз проверить данные в таблице и параметры, так как это самая распространенная ошибка в подобных задачах.
- Теперь, когда мы получили требуемую скидку, осталось применить ее для расчета суммы скидки по всем товарам. Для этого проверьте первую ячейку в столбце «Сумма скидки», введите формулу «=D2*$G$2» и нажмите Enter. Знаки доллара расставлены так, что при расширении/копировании формулы в другие строки дисконтированная ячейка G2 остается в расчетах неизменной.
- Получаем сумму скидки на первый товар. Теперь наводим курсор на правый нижний угол ячейки с результатом, как только он изменит форму на крестик, зажав левую кнопку мыши, растягиваем формулу на все строки, для которых хотим вычислить то же самое количество.
- Теперь наш стол полностью готов по заданию.
Заключение
Поэтому функция «Найти решение» в Excel может помочь вам решить определенные задачи, которые довольно сложно или невозможно решить простыми методами. Однако проблема использования этого метода в том, что по умолчанию эта функция скрыта в программе, поэтому многие пользователи не подозревают о ее существовании. Также фича довольно сложна в освоении и использовании, но при должном изучении может принести существенную пользу и облегчить работу.