Функция в Excel: поиск решения

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

Содержание

• Как активировать функцию «Поиск решения”
• Подготовительный этап
• Использование функции и ее настройка
• Заключение

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

Читайте также: "Как пронумеровать страницы в Excel”

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

1. откройте меню «Файл», нажав на соответствующее имя.

    

   

    

2. Нажмите на раздел «Настройки», который находится внизу вертикального списка с левой стороны.

    

   

    

3. Затем нажмите на подраздел «Дополнения». Здесь будут отображаться все дополнения к программе, а внизу будет надпись «Управление». Справа от него находится выпадающее меню, в котором следует выбрать «надстройки Excel», обычно уже установленные по умолчанию. Нажмите кнопку «Перейти”.

    

   

    

4. На экране появится новое дополнительное окно «Дополнения». Установите флажок рядом с «Поиск решения» и нажмите «ОК.

    

   

    

5. Все готово. Нужная функция отображается на ленте справа от вкладки «Данные”.

    

   

    

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

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

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

 

 

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

 

 

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

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

 

 

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

См также: «Как построить график в Excel”

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

1. Перейдите на вкладку «Данные» и нажмите кнопку «Искать решение”.

    

   

    

2. Открывается «Опции», где необходимо ввести нужные настройки. В поле «Оптимизировать целевую функцию:» ​​введите адрес целевой ячейки, в которой вы планируете отображать сумму всех скидок. Можно ввести координаты вручную, или выбрать из таблицы, которую мы сначала нажимаем на область ввода, затем на нужную ячейку.

    

   

    

3. Перейдем к другим настройкам. В элемент «Кому:» можно ввести максимальный лимит, минимальный лимит или точное число. Исходя из задачи, ставим отметку возле опции «Стоимость» и пишем «4500000» - сумма скидок на все товары.

    

   

    

4. Следующее поле для заполнения – «Изменить значения переменных:». Необходимо ввести координаты нужной ячейки, содержащей определенное значение. Это значение является фактической скидкой, которую мы пытаемся рассчитать. Помимо выбора целевой ячейки, координаты можно ввести вручную, либо нажав на нужную ячейку в самой таблице.

    

   

    

5. Теперь нужно отредактировать раздел «По ограничениям:», где мы устанавливаем ограничения на используемые данные. Например, вы можете исключить десятичные дроби или, например, отрицательные числа. Это делается через кнопку «Добавить”.

    

   

    

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

    

   

    

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

    

   

    

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

    

   

    

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

    

   

    

10. Итак, все настройки сделаны и параметры заданы. Пришло время запустить функцию — для этого нажмите на кнопку «Найти решение”.

     

    

     

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

     

    

     

12. Вполне вероятно, что расчеты могут оказаться неверными, либо возникнет желание немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно просмотреть поля с введенными данными.
13. Если с данными все в порядке, можно попробовать другой метод решения. Для этого нажимаем на подходящий вариант и из открывшегося списка выбираем способ, который нам кажется наиболее подходящим:
    • Первый ищет решение, используя метод обобщенного приведенного градиента (GRG) для нелинейных задач. По умолчанию выбран этот вариант, но вы можете попробовать и другие.
    • Второй — попытаться найти решение линейных задач с помощью симплекс-метода.
    • Третий использует эволюционный поиск для выполнения задачи.
    • В том случае, если ни один из способов не дал удовлетворительных результатов, стоит еще раз проверить данные в таблице и параметры, так как это самая распространенная ошибка в подобных задачах.

       

      

       

14. Теперь, когда мы получили требуемую скидку, осталось с ее помощью рассчитать суммы скидок по всем товарам. Для этого отметьте первую ячейку в столбце «Сумма скидки», напишите в ней формулу «=D2*$G$2» и нажмите Enter. Знаки доллара расставлены таким образом, чтобы при растягивании/копировании формулы на другие строки ячейка G2 со скидкой оставалась в расчетах неизменной.

     

    

     

15. Мы получили сумму скидки на первый товар. Теперь подводим курсор к правому нижнему углу ячейки с результатом, как только он изменит форму на крестик, зажав левую кнопку мыши, растягиваем формулу на все строки, по которым хотим посчитать одинаковую сумму.

     

    

     

16. Теперь наш стол полностью готов по заданию.

     

    

     

Заключение

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

См также: «Как объединить столбцы в Excel”