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

Важно :  Данная статья переведена с помощью машинного перевода, см. Отказ от ответственности. Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала.

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

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

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

Примечание : 

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

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

1. Ячейки переменных

2. Ячейка с ограничениями

3. Целевая ячейка

После выполнения процедуры получены следующие значения.

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

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

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

    Изображение диалогового окна "Поиск решения" в Excel 2010 +
  2. В поле Оптимизировать целевую функцию введите ссылка на ячейку или имя целевой ячейки. Целевая ячейка должна содержать формулу.

  3. Выполните одно из следующих действий.

    • Чтобы значение целевой ячейки было максимальным из возможных, установите переключатель в положение Макс.

    • Чтобы значение целевой ячейки было минимальным из возможных, установите переключатель в положение Мин.

    • Чтобы задать для целевой ячейки конкретное значение, установите переключатель в положение Значение и введите в поле нужное число.

    • В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

  4. В поле В соответствии с ограничениями введите любые ограничения, которые требуется применить. Для этого выполните указанные ниже действия.

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

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

    3. Выберите отношение ( <=, =, >=, int, бин или раз ), которое необходимо задать между указанной ячейкой и ограничением. Если вы выберете вариант int, то в поле Ограничение появится значение целое число. Если вы выберете вариант бин, то в поле Ограничение появится значение бинарное число. Если вы выберете вариант раз, то в поле Ограничение появится значение все разные.

    4. Если в поле Ограничение было выбрано отношение <=, = или >=, введите число, ссылку на ячейку (или имя ячейки) или формулу.

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

      • Чтобы принять данное ограничение и добавить другое, нажмите кнопку Добавить.

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

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

    6. В диалоговом окне Параметры поиска решения щелкните ограничение, которое требуется изменить или удалить.

    7. Нажмите кнопку Изменить и внесите изменения либо нажмите кнопку Удалить.

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

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

    • Чтобы восстановить исходные значения перед нажатием кнопки Найти решение, выберите вариант Восстановить исходные значения.

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

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

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

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

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

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

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

    • Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.

    • Чтобы продолжить процесс поиска решения и просмотреть следующий вариант решения, нажмите кнопку Продолжить.

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

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

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

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

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

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

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

  • Нелинейный метод обобщенного понижающего градиента (ОПГ).    Используется для гладких нелинейных задач.

  • Симплекс-метод.    Используется для линейных задач.

  • Эволюционный метод    Используется для негладких задач.

Важно : Следует включить надстройки "Поиск решения" сначала. Дополнительные сведения читайте в статье Загрузка надстройки "Поиск решения".

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

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

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

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

Выноска 3  Целевой ячейки

После выполнения процедуры получены следующие значения.

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

  1. В Excel 2016 для Mac: выберите данные > "Поиск решения".

    Поиск решения

    В Excel для Mac 2011: на вкладке данные в группе Анализ, нажмите кнопку "Поиск решения".

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

  2. В Задать цель введите имя для целевой ячейки или ссылка на ячейку.

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

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

    Задача

    Действие

    Сделать максимально значение целевой ячейки

    Нажмите кнопку Макс.

    Сделать минимальным возможных значение целевой ячейки

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

    Значение целевой ячейки конкретное значение

    Выберите Значение из, а затем введите значение в поле.

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

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

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

    Чтобы добавить ограничение, выполните следующие действия.

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

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

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

      Примечание : Можно применить только int, корзины и dif связи в ограничений ячейки переменных решения.

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

    Задача

    Действие

    Принять ограничение и добавить другое

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

    Принять ограничение и вернуться в диалоговое окно " Параметры поиска решения "

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

  6. Нажмите кнопку найти решение, а затем выполните одно из следующих действий:

    Задача

    Действие

    Сохранить значения решения на листе

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

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

    Нажмите кнопку восстановить исходные значения.

Примечания : 

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

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

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

  1. В Excel 2016 для Mac: выберите данные > "Поиск решения".

    Поиск решения

    В Excel для Mac 2011: на вкладке данные в группе Анализ, нажмите кнопку "Поиск решения".

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

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

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

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

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

    Задача

    Действие

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

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

    Продолжить процесс поиска решения и просмотреть следующий вариант решения

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

  1. В Excel 2016 для Mac: выберите данные > "Поиск решения".

    Поиск решения

    В Excel для Mac 2011: на вкладке данные в группе Анализ, нажмите кнопку "Поиск решения".

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

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

    Задача

    Действие

    Настройка времени решение и итераций

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

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

    Настройка погрешность

    На вкладке Все методы введите в поле Точность ограничения погрешность, который вы хотите. Чем меньше число, тем выше точность.

    Задайте степень объединения

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

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

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

  1. В Excel 2016 для Mac: выберите данные > "Поиск решения".

    Поиск решения

    В Excel для Mac 2011: на вкладке данные в группе Анализ, нажмите кнопку "Поиск решения".

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

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

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

    Совет : Выбор последнего можно сохранить в диалоговом окне Параметры поиска решения с листом путем сохранения книги. Каждый лист в книге могут содержать собственный выбор "Поиск решения", а все они сохраняются. Также можно определить несколько проблемы для листа, нажав кнопку Загрузить/сохранить, чтобы сохранить проблемы по отдельности.

  1. В Excel 2016 для Mac: выберите данные > "Поиск решения".

    Поиск решения

    В Excel для Mac 2011: на вкладке данные в группе Анализ, нажмите кнопку "Поиск решения".

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

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

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

Описание

Нелинейный ОПГ (нелинейный метод обобщенного понижающего градиента)

Выбор по умолчанию для модели, использующих большинство функций Excel, отличные от Если, выбор, ПОДСТАНОВКИ и другие функции «шаг».

Симплекс ЯЗЫКОВОЙ

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

Эволюционный метод

При модели используется, если этот метод с учетом генетическую алгоритмы рекомендуется выбрать или ПОДСТАНОВКИ с аргументами, которые зависят от ячейки переменных.

Примечание : Части "Поиск решения" программный код об авторских правах 1990 2010 рядовые систем, Inc. фрагментов об авторских правах 1989 оптимальная методы, Inc.

Дополнительная справка по надстройке "Поиск решения"

За дополнительной справкой по надстройке "Поиск решения" обращайтесь по этим адресам:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Веб-сайт: http://www.solver.com
Электронная почта: info@solver.com
Справка по надстройке "Поиск решения" на сайте www.solver.com.

Авторские права на части программного кода надстройки "Поиск решения" версий 1990-2009 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

См. также

Использование надстройки "Поиск решения" для создания бюджета

Использование надстройки "Поиск решения" для финансового планирования

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

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

Введение в анализ "что если"

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш в Excel 2016 для Windows

Сочетания клавиш в Excel 2016 для Mac

Функции Excel (по алфавиту)

Функции Excel (по категориям)

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

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

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

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

×