Надстройка «Поиск решения» в Microsoft Office

Надстройка «Поиск решения» Надстройка – это вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей Включение надстроек осуществляется в диалоговом окне Параметры Excel в группе параметров Надстройки. На странице Управление надстройками в поле Управление нужно выбрать пункт Надстройки Excel  и нажать кнопку Перейти.  В диалоговом окне Надстройки выбрать нужную надстройку Команда Поиск решения добавляется на вкладку Данные в группу Анализ Общие понятия надстройки Поиск решения Традиционными задачами, решаемыми с помощью настройки «Поиск решений» являются:  • Решение математических уравнений, систем уравнений. • Поиск экстремумов функций. • Составление плана выпуска продукции при ограничении сырья. • Составление штатного расписания для достижения лучшей занятости и при наименьших расходах. • Планирование перевозок – минимизирование затрат на перевозки. • Составление смеси заданного качества при наименьших затратах. Общие свойства, которые характерны для задач, решаемых с помощью надстройки Поиск решения: • Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть найдено как максимальное, минимальное или же равное, какому-то конкретному значению. • Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. • Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке. • Может быть задано некоторое количество ограничений – условий или соотношений, которым должны удовлетворять изменяемые ячейки. Прежде чем обращаться к инструменту Поиск решения, нужно проанализировать задачу и построить математическую модель. Для построения модели необходимо: 1. определить, каковы переменные модели. Переменные – неизвестные величины, которые нужно найти при решении задачи; 2. выбрать целевую функцию. Целевая функция – функция, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели; 3. задать ограничения, т.е. условия, которым должны удовлетворять переменные. При подготовке рабочего листа к решению задачи нужно: 1.отвести ячейку (или диапазон ячеек) для хранения переменных (изменяемых) величин; 2.в отдельную ячейку ввести функцию цели. Функция цели всегда зависит от переменных, поэтому в ячейке с целевой функцией будут использованы ссылки на ячейки, где хранятся переменные; 3.подготовить значения и формулы для задания ограничений. Поскольку ограничения накладываются на переменные, то в формулах для задания ограничений будут использованы ссылки на ячейки, где хранятся переменные. Пример 1 Нахождение корней уравнения Нахождение корней уравнения   1. Построить таблицу значений функции левой части уравнения  2. Построить график уравнения. Найти графическое решение уравнения (определить  абсциссы точек пересечения графика с осью OX) 3. Уточнить с помощью надстройки Поиск решения найденные ранее начальные приближения корней уравнения. 1. В отдельные ячейки задать начальные приближения корней (изменяемые ячейки) 2. В соседних ячейках задать формулы (целевые функции), ссылающиеся на изменяемые ячейки 3. При необходимости задать ограничения (условия на значения переменных) 4. Включить надстройку поиск решения и задать в диалоговом окне параметры. Выполнить поиск 5. Оценить правильность результата.  Параметры диалогового окна: Значения в изменяемой и целевой ячейках после решения с помощью надстройки "Поиск решения" Пример 2 Задача рационального использования сырья Условие задачи: Для изготовления двух видов продукции P1 и P2 используется три вида сырья S1, S2, S3. Запасы и расход сырья, а также величина прибыли от реализации единицы продукции, приведены в таблице. Необходимо составить такой план выпуска продукции, чтобы при ее реализации получить максимальную прибыль. Исходные данные задачи Составим математическую модель данной задачи Обозначим через x1 количество продукции P1, за x2 – количество продукции P2. Суммарная прибыль от реализации продукции двух видов вычисляется по формуле Учитывая расход сырья на изготовление единицы продукции, а также запасы сырья, получим систему ограничений. Эта система показывает, что расход сырья не может превысить запасов, а количество продукции не может быть отрицательным. Условием задачи не оговорена неделимость единицы продукции, поэтому x1 и x2 могут быть дробными числами. Цель решаемой задачи – получение максимальной прибыли от реализации продукции. Решение задачи в Excel Исходные данные расположены в ячейках A1:D6. В ячейках E3:E5 заданы формулы, вычисляющие расход сырья на изготовление всей продукции. Ячейки C7:D7 предназначены для значений переменных x1 и x2. В ячейку E8 введена целевая функция. Для запуска надстройки выбираем команду Поиск решения на вкладке Данные и задаем необходимые параметры При корректно заданных ограничениях и верно указанных изменяемых ячеек и целевой функции решение будет найдено. В изменяемых ячейках появятся значения, соответствующие условиям оптимальности. При решении задач оптимизации можно экспериментировать с различными ограничениями