Пример анализа с помощью надстройки "Поиск решения"

Надстройка Поиск решения является частью блока задач, которые иногда называют средствами анализ "что если". С помощью надстройки Поиск решения можно найти оптимальное (максимальное или минимальное) значение для формула в одной ячейке, называемой ячейкой целевой функции, с учетом ограничений, налагаемых на значения других ячеек формулы на листе. Поиск решения работает с группой ячеек, называемых переменными решения или просто ячейками переменных, которые участвуют в расчете формул в ячейках ограничений и ячейке целевой функции. Поиск решения подбирает значения в ячейках переменных решения таким образом, чтобы они удовлетворяли ограничениям в соответствующих ячейках и давали искомый результат в ячейке целевой функции.

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

В приведенном ниже примере уровень рекламы в каждом квартале влияет на объем продаж и косвенно определяет сумму дохода, а также связанные издержки и прибыль. Чтобы найти максимально возможную валовую прибыль (ячейка целевой функции D7), Поиск решения может повышать ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5), пока общие расходы не превысят ограничение в 20 000 рублей (ячейка D5). Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с ячейкой целевой функции D7, =СУММ(Q1 Прибыль:Q2 Прибыль).

Пример анализа с помощью надстройки "Поиск решения"

Выноска 1 Ячейки переменных

Выноска 2 Ячейка с ограничением

Выноска 3  Ячейка целевой функции

В результате выполнения Поиск решения получены показанные ниже значения.

Пример анализа с помощью надстройки "Поиск решения" с использованием новых значений

Выполните одно из перечисленных ниже действий.

Постановка задачи и ее решение

  1. На вкладке Данные в разделе Анализ нажмите кнопку Поиск решения.

    Вкладка "Данные", группа "Анализ", надстройка "Поиск решения"

    Добавление надстройки "Поиск решения" в раздел "Анализ" на вкладке "Данные"

    1. В меню Сервис выберите пункт Надстройки.

    2. В списке Доступные надстройки установите флажок Solver.Xlam и нажмите кнопку ОК.

  2. В поле Оптимизировать целевую функцию, введите ссылка на ячейку или имя ячейки целевой функции.

    Примечание : Ячейка целевой функции должна содержать формулу.

  3. Выполните одно из указанных ниже действий.

Чтобы

Выполните указанные ниже действия

Нахождение в ячейке целевой функции максимального значения

Установите переключатель Максимум.

Нахождение в ячейке целевой функции минимального значения

Установите переключатель Минимум.

Нахождение в ячейке целевой функции конкретного значения

Установите переключатель Значение и введите значение в поле.

  1. В поле Изменяя ячейки переменных введите имена всех диапазонов ячеек переменных решения или ссылки на них. Ссылки на несмежные диапазоны следует разделять запятыми.

    Ячейки переменных должны быть напрямую или косвенно связаны с ячейкой целевой функции. Можно задать до 200 ячеек переменных.

  2. В поле В соответствии с ограничениями добавьте все ограничения, которые необходимо применить.

    Добавление ограничения

    1. В диалоговом окне Поиск решения нажмите кнопку Добавить.

    2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых накладывается ограничение.

    3. Во всплывающем меню операторов <= выберите оператор, который должен располагаться между ячейкой, на которую указывает ссылка, и ограничением.При выборе оператора <=, = или >= в поле Ограничение необходимо ввести число, ссылку на ячейку или ее имя либо формулу.

      Примечание : Операторы "цел", "бин" и "раз" можно использовать только в ограничениях, накладываемых на ячейки переменных решения.

    4. Выполните одно из указанных ниже действий.

    Чтобы

    Выполните указанные ниже действия

    Принятие ограничение и добавление еще одного

    Нажмите кнопку Добавить.

    Принятие ограничения и возврат к диалоговому окну Поиск решения

    Нажмите кнопку ОК.

  3. Нажмите кнопку Найти решение и выполните одно из указанных ниже действий.

Чтобы

Выполните указанные ниже действия

Сохранение найденного решения на листе

В диалоговом окне Результаты поиска решения выберите вариант Сохранить найденное решение .

Восстановление исходных данных

Выберите вариант Восстановить исходные значения.

Примечания : 

  1. Чтобы прервать поиск решения, нажмите клавишу ESC . Лист Excel будет пересчитан с учетом последних найденных значений для регулируемых ячеек.

  2. Чтобы создать отчет на основе решения, найденного с помощью Поиск решения, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет создан на новом листе книги. Если Поиск решения не удастся найти решение, параметры создания отчета будут недоступны.

  3. Чтобы сохранить значения регулируемых ячеек в виде сценария, который затем можно будет просмотреть, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения и введите имя сценария в поле Имя сценария.

Просмотр промежуточных результатов поиска решения

  1. На вкладке Данные в разделе Анализ нажмите кнопку Поиск решения.

    Вкладка "Данные", группа "Анализ", надстройка "Поиск решения"

    Добавление надстройки "Поиск решения" в раздел "Анализ" на вкладке "Данные"

    1. В меню Сервис выберите пункт Надстройки.

    2. В списке Доступные надстройки установите флажок Solver.Xlam и нажмите кнопку ОК.

  2. После постановки задачи нажмите кнопку Параметры в диалоговом окне Поиск решения.

  3. Чтобы видеть значения всех пробных решений, в диалоговом окне Параметры поиска решения установите флажок Показывать результаты итераций и нажмите кнопку ОК.

  4. В диалоговом окне Поиск решения нажмите кнопку Найти решение.

  5. В диалоговом окне Показать предварительное решение выполните одно из указанных ниже действий.

Чтобы

Выполните указанные ниже действия

Остановка поиска решения и отображение окна Результаты поиска решения

Нажмите кнопку Остановить.

Продолжение поиска решения и отображение следующего пробного решения

Нажмите кнопку Продолжить.

Изменение способа поиска решения

  1. На вкладке Данные в разделе Анализ нажмите кнопку Поиск решения.

    Вкладка "Данные", группа "Анализ", надстройка "Поиск решения"

    Добавление надстройки "Поиск решения" в раздел "Анализ" на вкладке "Данные"

    1. В меню Сервис выберите пункт Надстройки.

    2. В списке Доступные надстройки установите флажок Solver.Xlam и нажмите кнопку ОК.

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

Чтобы

Выполните указанные ниже действия

Время поиска и количество итераций

На вкладке Все методы в разделе Пределы решения в поле Максимальное время (в секундах) укажите время, отводимое на поиск решения, в секундах. Затем в поле Число итераций укажите максимальное допустимое количество итераций.

Примечание : Если по прошествии отведенного времени или по достижении максимального количества итераций решение не будет найдено, на экране появится диалоговое окно Показать предварительное решение.

Степень точности

На вкладке Все методы в поле Точность ограничения укажите требуемую степень точности. Чем меньше введенное число, тем выше точность результатов.

Степень сходимости

На вкладке Поиск решения нелинейных задач методом ОПГ или Эволюционный поиск решения в поле Сходимость укажите допустимую величину относительного изменения для последних пяти итераций, по достижении которой Поиск решения завершит обработку данных, найдя решение. Чем меньше введенное число, тем меньшее относительное изменение является допустимым.

  1. Нажмите кнопку ОК.

  2. В диалоговом окне Поиск решения нажмите кнопку Найти решение или Закрыть.

Сохранение или загрузка модели задачи

  1. На вкладке Данные в разделе Анализ нажмите кнопку Поиск решения.

    Вкладка "Данные", группа "Анализ", надстройка "Поиск решения"

    Добавление надстройки "Поиск решения" в раздел "Анализ" на вкладке "Данные"

    1. В меню Сервис выберите пункт Надстройки.

    2. В списке Доступные надстройки установите флажок Solver.Xlam и нажмите кнопку ОК.

  2. Нажмите кнопку Загрузить или сохранить модель, укажите диапазон ячеек для области модели и нажмите кнопку Сохранить или Загрузить.

    При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель задачи. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель задачи.

    Совет : Последние выбранные значения в диалоговом окне Поиск решения можно сохранить вместе с листом при сохранении книги. Для каждого листа книги можно задать соответствующие значения Поиск решения, и все они будут сохранены. Чтобы определить на листе несколько задач, их нужно сохранять по отдельности с помощью кнопки Загрузить или сохранить модель.

Выбор используемого метода решения

  1. На вкладке Данные в разделе Анализ нажмите кнопку Поиск решения.

    Вкладка "Данные", группа "Анализ", надстройка "Поиск решения"

    Добавление надстройки "Поиск решения" в раздел "Анализ" на вкладке "Данные"

    1. В меню Сервис выберите пункт Надстройки.

    2. В списке Доступные надстройки установите флажок Solver.Xlam и нажмите кнопку ОК.

  2. Во всплывающем меню Выберите метод решения выберите один из указанных ниже вариантов.

Метод решения

Описание

Поиск решения нелинейных задач методом ОПГ

Этот метод используется по умолчанию для моделей, которые содержат большинство функций Excel, отличных от ЕСЛИ, ВЫБОР, ПРОСМОТР и других ступенчатых функций.

Поиск решения линейных задач симплекс-методом

Этот метод используется для решения задач линейного программирования. Модели должны содержать функции СУММ, СУММПРОИЗВ, + - и * в формулах, зависящих от ячеек переменных.

Эволюционный поиск решения

Этот метод, основанный на генетических алгоритмах, лучше всего подходит для моделей, которые содержат функции ЕСЛИ, ВЫБОР или ПРОСМОТР с аргументами, зависящими от ячеек переменных.

Авторские права на фрагменты кода программы Поиск решения, созданные в 1990–2010 гг., принадлежат компании Frontline Systems, Inc (1990–2010). Авторские права на фрагменты, созданные в 1989 г., принадлежат компании Optimal Methods, Inc.

Для получения дополнительных сведений о Поиск решения,

обращайтесь по адресу:

Frontline Systems, Inc.

P.O. Box 4288

Incline Village, NV 89450-4288

Телефон: (775) 831-0300

Адрес электронной почты: info@solver.com

Веб-сайт: Веб-сайт компании Frontline Systems

Более подробную справку по Поиск решения от компании Frontline Systems см. по адресу Справка надстройки "Поиск решений" на веб-сайте Frontline Systems.

См. также

Создание сводной таблицы

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×