Функция Поиск решения в Excel. Включение, пример использования со скриншотами
Поиск решения MS EXCEL. Знакомство. Поиск решения – это надстройка Microsoft Excel, с помощью которой можно найти оптимальное решение задачи с учетом заданных пользователем ограничений.
Можно долго искать на ленте, где находится Поиск решения, но так и не найти данный инструмент. Просто, для активации данной функции, нужно её включить в настройках программы.
Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».
В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».
Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».
После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».
Оптимизация доставки
- Рассматриваемую функцию лучше всего использовать для решения таких задач, как планирование графиков сотрудников, определение минимальной цены (которую можно установить за единицу товара и получить желаемый доход) и составление бюджета.
Ожидать, что функция сработает в обратном порядке можно, но для этого потребуется изменять формулу и вводные данные для этой формулы. Фактически Excel следует строгой логике и сама по себе функция не решит проблему, но поможет прийти к корректному решению подбором или перебором вводных данных.
Решение
В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно же, давно разработаны способы их решения не тупым перебором (что очень долго), а за весьма небольшое количество итераций. Excel предоставляет пользователю такой функционал с помощью надстройки Поиск решения (Solver) с вкладки Данные (Data) :
Чтобы этого не случилось лучше оставить включенным флажок Сделать переменные без ограничений неотрицательными или даже иногда явно прописать такие моменты в списке ограничений.
После настройки всех необходимых параметров окно должно выглядеть следующим образом:
В выпадающем списке Выберите метод решения (Select a solving method) дополнительно требуется подобрать подходящий математический метод для решения на выбор из трех вариантов:
- Симплекс-метод — простой и быстрый метод для решения линейных задач, т.е. задач, где выход линейно зависит от входа.
- Метод общего понижающего градиента (ОПГ) — для нелинейных задач, где между входными и выходными данными есть сложные нелинейные зависимости (например, зависимость продаж от расходов на рекламу).
- Эволюционный поиск решения — относительно новый метод оптимизации, основанный на принципах биологической эволюции (привет Дарвину). Этот метод работает в разы дольше первых двух, но может решать практически любые задачи (нелинейные, дискретные).
Наша задача явно относится к линейным: доставили 1 шт — затратили 40 р., доставили 2 шт — затратили 80 р. и т.д., так что симплекс-метод будет наилучшим выбором.
Теперь, когда данные для расчета введены, нажмем кнопку Найти решение (Solve) , чтобы начать оптимизацию. В тяжелых случаях с большим количеством изменяемых ячеек и ограничений нахождение решения может занять продолжительное время (особенно с эволюционным методом), но наша задача для Excel проблемы не составит – через пару мгновений мы получим следующие результаты:
Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.
Рассмотренный пример, конечно, является относительно простым, но легко масштабируется под решение гораздо более сложных задач. Например:
- Оптимизация распределения финансовых средств по статьям расходов в бизнес-плане или бюджете проекта. Ограничениями, в данном случае, будут являться объемы финансирования и сроки выполнения проекта, а целью оптимизирования – максимизация прибыли и минимизация расходов на проект.
- Оптимизация расписания сотрудников с целью минимизации фонда заработной платы предприятия. Ограничениями, в этом случае, будут пожелания каждого сотрудника по графику занятости и требования штатного расписания.
- Оптимизация инвестиционных вложений – необходимость грамотно распределить средства между несколькими банками, ценными бумагами или акциями предприятий с целью, опять же, максимизации прибыли или (если это более важно) минимизации рисков.
В любом случае, надстройка Поиск решения (Solver) является весьма мощным и красивым инструментом Excel и достойна того, чтобы вы обратили на нее свое внимание, поскольку может выручить во многих сложных ситуациях, с которыми приходится сталкиваться в современном бизнесе.
Как включить поиск решений в excel на mac
- Светло-желтая таблица (C4:G6) описывает стоимость доставки одной единицы товара от каждого склада до каждого магазина.
- Лиловые ячейки (C15:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
- Красные ячейки (J10:J13) отображают емкость каждого склада – предельное количество товара, которое склад вмещает.
- Желтые (C13:G13) и синие (H10:H13) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
- Общая стоимость доставки (J18) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки — для подсчёта здесь используется функция СУММПРОИЗВ(SUMPRODUCT).
Надстройка Поиск решений в Excel 2007 не является стандартной. Она предназначается для сложных вычислений, когда имеется больше одной неизвестной. Поэтому она не включается в обычный набор параметров программы. Но если в ней есть необходимость, то она способна предложить пользователю эффективную работу и высокую продуктивность.
