Табличный процессор EXCEL. Средства анализа данных: подбор параметров, сценарии, поиск решений.
Команда Подбор параметра меню СЕРВИС позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат. Технология использования команды следующая:
— решить нужную задачу с каким-либо начальным значением параметра;
— выбрать команду Подбор параметра меню СЕРВИС;
— в окне диалога Подбор параметра в поле Установить в ячейке задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение – то значение, которое следует получить в качестве результата формулы;
— в поле Изменяя значение ячейки ввести ссылку на ячейку с параметром;
— нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;
— для сохранения найденного значения нажать кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды Подбор параметра нажать кнопку Отмена.
По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры на вкладке Вычисления команды Параметры меню СЕРВИС.
Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений.
При сохранении книги после использования поиска решения все значения, введенные в окнах диалога поиска решений, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в книге можно сохранить один набор значений параметров поиска. Однако, пользуясь кнопкой Сохранить модель в окне диалога Параметры поиска решений, можно сохранить несколько таких наборов.
Окно диалога Параметры поиска решений дает возможность задать еще ряд показателей, управляющих процессом поиска:
2) Относительная погрешность используется поиском решения для определения точности выполнения ограничений. Чем ближе это значение к 1, тем ниже точность. Задание относительной погрешности, меньше установленной по умолчанию (0,000 001) приводит к росту времени поиска решения.
3) Допустимое отклонение служит для задания допуск на отклонение от оптимального решения и применяется только в задачах с целочисленными ограничениями.
4) Переключатели Оценки, Разности и Метод поиска позволяют уточнить применяемые методы оптимизации, обычно используются значения, установленные по умолчанию.
5) Флажок Линейная модель может быть активизирован только для моделей, в которых отсутствуют нелинейные зависимости, т.е. график зависимости целевой функции от каждого ограничения может быть представлен прямой линией. Если этот флажок установлен, решение линейной задачи будет найдено быстрее.
Поиск решений может представлять свои результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов следует выбрать их названия в окне диалога Результаты поиска решений. Каждый отчет сохраняется на отдельном листе текущей книги, а имена отчетов отображаются на ярлычках.
Отчет по устойчивости содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных. В отчете показывается, как целевая функция реагирует на увеличение значений изменяемых ячеек на единицу и на увеличение значения ограничения на единицу
Отчет по пределам сообщает о том, в каких пределах значения изменяемых ячеек могут быть увеличены или уменьшены без нарушения ограничений задачи. Для каждой изменяемой ячейки этот отчет содержит оптимальное значение, а также наименьшее и наибольшее значения, которые ячейка может принимать без нарушения ограничений.
Поиск решений может решить не каждую предложенную задачу. Если оптимальное решение не найдено, в окне диалога Результаты поиска решений выводится сообщение о неуспешном завершении.
Подбор параметра в MS Excel
В результате вычислений (с учётом дробного остатка, поскольку условие работы только с целыми числами добавлено не было), получилось, что эффективнее всего производить 1 и 3 йогурты, а второй полностью игнорировать.
Примеры и задачи на поиск решения в Excel
Мы можем изменять переменные значения в ячейках B1 и B2 так, чтобы подобрать необходимые условия для накопления необходимой суммы денег.
Надстройка «Поиск решения» – позволяет нам одновременно использовать 2 этих варианта, чтобы быстро смоделировать наиболее оптимальные условия для достижения поставленной цели. Для этого:
-
Перейдите в ячейку B14 и выберите инструмент: «Данные»-«Анализ»-«Поиск решения».
Как видно программа немного увеличила процентную ставку и сумму ежегодных взносов.
Решение нелинейных уравнений с помощью средства MS Excel Подбор параметра
- Определите ячейки с переменными модели (decision variables);
- Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
- Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
- С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
- Запустите Поиск решения для нахождения оптимального решения.
«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил. Рассказываем, как освоить функцию поиска решений.
