Перейти к содержимому

Содержимое ячейки целевой функции должно быть формулой что делать

  • автор:

Содержимое ячейки целевой функции должно быть формулой что делать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Щелкните связь (<=, =, >=, int,binили dif), которая требуется между ячейкой, на которую ссылается ссылка, и ограничением. Если щелкнуть int, в поле Ограничение появится integer. Если щелкнуть бин,в поле Ограничение появится двоичное поле. Если нажать кнопку dif,в поле Ограничение появится ссылкаalldifferent.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В результате выполнения получены следующие значения:

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

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

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

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

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

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

Необходимые действия

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

Выберите значение Макс.

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

Выберите значение Мин.

Сделать так, чтобы целевая ячейка имела определенное значение

Щелкните Значение, а затем введите нужное значение в поле.

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

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

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

Для этого выполните следующие действия:

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

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

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

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

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

Необходимые действия

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

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

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

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

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

Необходимые действия

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

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

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

Щелкните Восстановить исходные значения.

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

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

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

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

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

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

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

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

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

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

Необходимые действия

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

Ошибки при работе со средством «Подбор параметра»

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

Ячейка должна содержать формулу. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Установить в ячейке диалогового окна Подбор параметра, не содержит формулы. Чаще всего причиной этой ошибки является то, что вы в этом поле ввели адрес ячейки, который должен быть указан в поле ввода Изменяя значение ячейки. Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле Установить в ячейке адрес «правильной» ячейки, содержащей формулу. Затем сно­ва щелкните на кнопке ОК.

Введено недопустимое значение. Следует ввести число. Это сооб­щение об ошибке появляется тогда, когда вы в поле ввода Значение кроме числа ввели еще какие-то символы, которые Excel не может распознать как числа. Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле Значение правильное число (целое или десятичное). Затем снова щелкните на кнопке ОК.

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

Ячейка должна содержать значение. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Изменяя значение ячейки диалогового окна Подбор параметра, не со­держит числового значения (а содержит, например, текст или фор­мулу). Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле Изменяя значение ячейки адрес «пра­вильной» ячейки, содержащей числовое значение. Затем снова щел­кните на кнопке ОК.

Введенный текст не является правильной ссылкой или именем. Это сообщение об ошибке появляется тогда, когда в поле ввода Установить в ячейке или в поле ввода Изменяя значение ячейки диалогового окна Подбор параметра введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда вы вручную вводите адрес ячейки, а не указываете ячейку путем щелчка на ней. (Например, если вы вводите адрес ячейки «русскими» бук­вами.) Чтобы исправить эту ошибку, закройте сначала сообщение об ошибке, а затем введите в поле ввода правильный адрес ячейки. Затем снова щелкните на кнопке ОК.

Решение не найдено. Это сообщение появляется в диалоговом окне Результат подбора параметра (а не как сообщение об ошибке) тогда, когда Excel не может подобрать такое значение для изменяе­мого параметра, чтобы указанная формула возвратила заданное вами значение. Такое же сообщение появится, если в поле Значение введе­но экстремально маленькое или экстремально большое число. Чтобы исправить эту ошибку, сначала в диалоговом окне Результат подбора параметра щелкните на кнопке Отмена, данное окно закроется. Затем снова выберите команду Подбор параметра и в поле Значение диалогового окна Подбор параметра введите другое число.

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

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

Назначение средства «Поиск решения»

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

Для примера рассмотрим задачу вычисления оптимальных цен и количеств театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В театре имеются билеты трех категорий: детские, для взрослых и льготные (рис. 4.1).

В целевой ячейке В6 подсчитывается сумма стоимостей билетов всех категорий (для каждой категории билетов их стоимость подсчитыва­ется как произведение количества билетов и их цены).

Рис. 4.1. Рабочий лист для поиска оптимальной цены театральных билетов

В главе 1 при использовании средства Подбор параметра за один раз мы могли изменять значение только одной переменной: значение цены или количество билетов одной категории. Средство Поиск решения также может изменять значения только одной переменной (т.е. может работать в режиме подбора параметра). Однако, чтобы оценить гибкость и мощь этого средства, следует использовать его для изменения нескольких переменных. Кроме того, это средство мо­жет добиться от целевой ячейки (точнее, от формулы, содержащейся в этой ячейке) не только определенного заранее значения, но и максимально или минимально возможного (для этой формулы) значения.

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

Цена детских билетов постоянна и равна 60 руб.

Цена билетов для взрослых постоянна и равна 100 руб.

Цена льготных билетов постоянна и равна 90 руб.

Билетов каждой категории можно продать не более 100 шт.

В театре может быть аншлаг, но «лишних билетиков» нет.

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

Другой пример решения задачи с ограничениями показан на рис. 4.2. Здесь некий завод производит три вида изделий, но может производить в смену суммарно не более 300 изделий. Необходимо определить при этом ограничении, а также при условии, что должно производиться не менее 30 шт. любого изделия, количество выпуска­емых изделий таким образом, чтобы получить максимальный доход. Ответ, который дает Поиск решения, показан на том же рис. 4.2.

Рис. 4.2. Определение оптимальной структуры производства

Поиск решения EXCEL. Знакомство

Поиск решения будем рассматривать в MS EXCEL 2010 (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в MS EXCEL 2007) . В этой статье рассмотрим:

  • создание оптимизационной модели на листе MS EXCEL
  • настройку Поиска решения;
  • простой пример (линейная модель).

Установка Поиска решения

Команда Поиск решения находится в группе Анализ на вкладке Данные .

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

  • На вкладке Файл выберите команду Параметры , а затем — категорию Надстройки ;
  • В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
  • В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

Примечание . Окно Надстройки также доступно на вкладке Разработчик . Как включить эту вкладку читайте здесь .

После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно .

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

О моделях

Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.

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

Ниже приведен небольшой ликбез по этой теме.

Надстройка Поиск решения помогает определить лучший способ сделать что-то :

  • «Что-то» может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
  • «Лучший способ» или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.

Вот некоторые типичные примеры оптимизационных задач:

  • Определить план производства , при котором доход от реализации произведенной продукции максимальный;
  • Определить схему перевозок , при которой общие затраты на перевозку были бы минимальными;
  • Найти распределение нескольких станков по разным видам работ , чтобы общие затраты на производство продукции были бы минимальными;
  • Определить минимальный срок исполнения всех работ проекта (критический путь).

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

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

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

Подготовка оптимизационной модели в MS EXCEL

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

Совет . Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).

Приведем алгоритм работы с Поиском решения , который советуют сами разработчики ( ]]> www.solver.com ]]> ):

  • Определите ячейки с переменными модели (decision variables);
  • Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
  • Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
  • С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
  • Запустите Поиск решения для нахождения оптимального решения.

Проделаем все эти шаги на простом примере.

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

Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик — 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.

Данные модели организуем следующим образом (см. файл примера ).

Переменные модели (количество каждого вида тары) выделены зеленым. Целевая функция (общий вес всех коробок и ящиков) – красным. Ограничения модели: по минимальному количеству тары (>=110) и по общему объему ( =СУММПРОИЗВ(B8:C8;B6:C6) – это общий вес всех коробок и ящиков, загруженных в контейнер. Аналогично рассчитываем общий объем — =СУММПРОИЗВ(B7:C7;B8:C8) . Эта формула нужна, чтобы задать ограничение на общий объем коробок и ящиков ( =СУММ(B8:C8) . Теперь с помощью диалогового окна Поиск решения введем ссылки на ячейки содержащие переменные, целевую функцию, формулы для ограничений и сами значения ограничений (или ссылки на соответствующие ячейки). Понятно, что количество коробок и ящиков должно быть целым числом – это еще одно ограничение модели.

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

Совет : в статье » Поиск решения MS EXCEL. Экстремум функции с несколькими переменными. Граничные условия заданы уравнениями » показано решение задачи, в которой функция и граничные условия заданы в явном виде, т.е. математическими выражениями типа F(x1, x2, x3)=x1+2*x2+6*x3, что существенно облегчает построение модели, т.к. не требуется особо осмыслять задачу: можно просто подставить переменные x в поле переменные, а ограничения ввести в соответствующее поле окна Поиска решения.

Резюме

На самом деле, основной проблемой при решении оптимизационных задач с помощью Поиска решения является отнюдь не тонкости настройки этого инструмента анализа, а правильность построения модели, адекватной поставленной задаче. Поэтому в других статьях сконцентрируемся именно на построении моделей, ведь «кривая» модель часто является причиной невозможности найти решение с помощью Поиска решения . Зачастую проще просмотреть несколько типовых задач, найти среди них похожую, а затем адаптировать эту модель под свою задачу. Решение классических оптимизационных задач с помощью Поиска решения рассмотрено в этом разделе .

Поиску решения не удалось найти решения (Solver could not find a feasible solution)

Это сообщение появляется, когда Поиск решения не смог найти сочетаний значений переменных, которые одновременно удовлетворяют всем ограничениям. Если вы используете Симплекс метод решения линейных задач , то можно быть уверенным, что решения действительно не существует. Если вы используете метод решения нелинейных задач, который всегда начинается с начальных значений переменных, то это может также означать, что допустимое решение далеко от этих начальных значений. Если вы запустите Поиск решения с другими начальными значениями переменных, то, возможно, решение будет найдено. Представим, что при решении задачи нелинейным методом, ячейки с переменными были оставлены не заполненными (т.е. начальные значения равны 0), и Поиск решения не нашел решения. Это не означает, что решения действительно не существует (хотя это может быть и так). Теперь, основываясь на результатах некой экспертной оценки, в ячейки с переменными введем другой набор значений, который, по Вашему мнению, близок к оптимальному (искомому). В этом случае, Поиск решения может найти решение (если оно действительно существует).

Примечание . О влиянии нелинейности модели на результаты расчетов можно прочитать в последнем разделе статьи Поиск решения MS EXCEL (4.3). Выбор места открытия нового представительства .

В любом случае (линейном или нелинейном), Вы должны сначала проанализировать модель на непротиворечивость ограничений, то есть условий, которые не могут быть удовлетворены одновременно. Чаще всего это связано с неправильным выбором соотношения (например, =) или граничного значения. Если, например, в рассмотренном выше примере, значение максимального объема установить 16 м3 вместо 32 м3, то это ограничение станет противоречить ограничению по минимальному количеству мест (110), т.к. минимальному количеству мест соответствует объем равный 16,5 м3 (110*0,15, где 0,15 – объем коробки, т.е. самой маленькой тары). Установив в качестве ограничения максимального объема 16 м3, Поиск решения не найдет решения.

При ограничении 17 м3 Поиск решения найдет решение.

Некоторые настройки Поиска решения

Метод решения Рассмотренная выше модель является линейной, т.е. целевая функция (M – общий вес, который может быть максимален) выражена следующим уравнением M=a1*x1+a2*x2, где x1 и x2 – это переменные модели (количество коробок и ящиков), а1 и а2 – их веса. В линейной модели ограничения также должны быть линейными функциями от переменных. В нашем случае ограничение по объему V=b1*x1+b2*x2 также выражается линейной зависимостью. Очевидно, что другое ограничение — Максимальное количество тары (n) – также линейно x1+x2

Функция в Excel: поиск решения

Наряду со множеством других возможностей, в Microsoft Excel есть одна малоизвестная, но очень полезная функция под названием “Поиск решения”. Несмотря на то, что найти и освоить ее, может быть, непросто, ее изучение и применение может помочь в решении огромного количества задач. Функция берет данные, перебирает их и выдает самое оптимальное решение из возможных. Итак, давайте разберемся, как именно работает поиск решения и попробуем применить данную функцию на практике

Как включить функцию “Поиск решения”

Несмотря на свою эффективность, функция “Поиск решения” не находится в первых рядах панели инструментов или контекстного меню. Многие пользователи, работающие в Excel годами, даже не подозревают о ее существовании. Дело в том, что по умолчанию она вообще отключена и для ее добавления на ленту нужно проделать следующие шаги:

  1. Открываем меню “Файл”, кликнув по соответствующему названию.Как включить функцию
  2. Кликаем по разделу “Параметры”, который находится внизу вертикального перечня с левой стороны.Как включить функцию
  3. Далее щелкаем по подразделу “Надстройки”. Здесь отображаются все надстройки программы, а внизу будет надпись “Управление”. Справа от нее представлено выпадающее меню, в котором должны быть выбраны “Надстройки Excel”, обычно уже установленные по умолчанию. Нажимаем кнопку “Перейти”.Как включить функцию
  4. На экране появится новое вспомогательное окно “Надстройки”. Устанавливаем флажок напротив опции “Поиск решения” и нажимаем ОК.Как включить функцию
  5. Все готово. Требуемая функция появится на ленте в правой части вкладки “Данные”.Как включить функцию

Подготовительный этап

Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.

Итак, у нас есть данные про продаже товаров, представленные в табличном виде.

Подготовительный этап перед использованием фукнции

И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.

Подготовительный этап перед использованием фукнции

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

Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.

Подготовительный этап перед использованием фукнции

Применение функции и ее настройка

Формула готова. Теперь нужно применить саму функцию.

  1. Переключаемся во вкладку “Данные” и нажимаем кнопку “Поиск решения”.Применение функции
  2. Откроются “Параметры”, где необходимо задать нужные настройки. В поле “Оптимизировать целевую функцию:” указываем адрес целевой ячейки, где планируется вывести сумму по всем скидкам. Можно прописать координаты вручную, либо выбрать из таблицы, для чего сначала кликаем по области ввода, затем – по нужной ячейке.Применение функции
  3. Переходим к настройке других параметров. В пункте “До:” можно задать максимальную границу, минимальную границу или же точное число. Исходя из поставленной задачи ставим отметку рядом с опцией “Значение” и набираем “4500000” – сумма скидок по всем наименованиям.Применение функции
  4. Следующее для заполнения поле – “Изменяя значения переменных:”. В него нужно внести координаты искомой ячейки, содержащей определенное значение. Это значение и есть та самая скидка, которую мы пытаемся вычислить. Также, как и с выбором целевой ячейки, координаты можно написать вручную, либо кликнуть по нужной ячейке в самой таблице.Применение функции
  5. Теперь нужно отредактировать раздел “В соответствии с ограничениями:”, в котором задаем ограничения используемых данных. Например, можно исключить десятичные дроби или, скажем, отрицательные числа. Это делается через кнопку “Добавить”. Применение функции
  6. Откроется вспомогательно окно, позволяющее добавить ограничения во время вычислений. В первом поле указываем координаты определенной ячейки или области ячеек, для которых это условие должно действовать. Согласно нашей задаче, указываем координаты искомой ячейки, в которой будет выводиться значение скидки. Следующий шаг – определить знак сравнения. Устанавливаем “больше или равно”, чтобы итоговое число не могло быть отрицательным. “Ограничение”, которое устанавливается в третьем поле, в этом случае будет равно цифре 0, поскольку именно относительно этого значения задается условие.Применение функции Можно установить еще одно ограничение с помощью кнопки “Добавить”. Дальнейшие действия по его настройке будут аналогичными. По готовности щелкаем OK.
  7. После выполнения описанных выше действий в самом большом поле окна появится установленное только что ограничение. Список может быть довольно большим и зависит от сложности предполагаемых расчетов, но в данном случае будет достаточно и одного условия.Применение функции Под этим полем также есть опция, позволяющая делать все остальные переменные, не затрагиваемые ограничениями, неотрицательными. Однако, будьте внимательны и проследите за тем, чтобы между этим параметром и поставленными ограничениями не было противоречия, иначе при расчете в программе может возникнуть конфликт.
  8. Также можно задать немалое количество дополнительных настроек. Чуть ниже справа есть кнопка “Параметры”, позволяющая это сделать. Нажимаем на нее и открываем новое окно.Применение функции
  9. В этих настройках у нас есть возможность установить “Точность ограничения” и “Пределы решения”. В нашем случае задавать данные параметры нет необходимости, поэтому после ознакомления с представленным окном, его можно закрыть, нажав OK.Применение функции
  10. Итак, все настройки выполнены и параметры установлены. Пора запускать функцию – для этого нажимаем кнопку “Найти решение”.Применение функции
  11. После этого программа сделает все необходимые расчеты и выдаст результаты в нужных ячейках. При этом сразу же откроется окно “Результаты поиска решения”, где можно сохранить/отменить результаты или настроить параметры поиска заново. Если результаты нас устраивают, оставляем отметку напротив опции “Сохранить найденное решение” и нажимаем ОК. При этом, если мы предварительно установим галочку слева от надписи “Вернуться в диалоговое окно параметров поиска решения”, после того, как мы щелкнем OK, мы обратно переключимся к настройке функции поиска решения.Применение функции
  12. Вполне вероятно, что расчеты могут показаться неправильными, либо возникнет желание немного изменить исходные данные и получить другой результат. В этом случае нужно снова открыть окно с параметрами поиска решения и внимательно посмотреть поля с введенными данными.
  13. Если с данными все нормально, можно попробовать задействовать другой метод решения. Для этого щелкаем по текущему варианту и из раскрывшегося перечня выбираем способ, который нам кажется наиболее подходящим:
    • Первый – ищет решение методом обобщенного приведенного градиента (ОПГ) для нелинейных задач. Стандартно выбран именно этот вариант, но можно попробовать и другие.
    • Второй – пытается отыскать решение для линейных задач, используя симплекс-метод.
    • Третий – для выполнения поставленной задачи использует эволюционный поиск.
    • В том случае, если ни один из методов не принес удовлетворительных результатов, стоит проверить данные в таблице и параметрах еще раз, поскольку именно это является самой частой ошибкой в подобного рода задачах.Применение функции
  14. Теперь, когда мы получили требуемую скидку, осталось ее применить, чтобы рассчитать суммы скидок по всем наименованиям. Для этого отмечаем первую ячейку столбца “Сумма скидки”, пишем в ней формулу “=D2*$G$2” и нажимаем Enter. Знаки доллара ставятся для того, чтобы при растягивании/копировании формулы на другие строки, ячейка G2 со скидкой оставалась неизменной в расчетах.Применение функции
  15. Мы получили сумму скидки для первого наименования. Теперь наводим курсор на нижний правый угол ячейки с результатом, как только он поменяет форму на крестик, зажав левую кнопку мыши растягиваем формулу на все строки, по которым хотим посчитать аналогичную сумму.Применение функции
  16. Теперь наша таблица полностью готова в соответствии с поставленной задачей.Применение функции

Заключение

Таким образом, функция “Поиск решения” в Эксель может помочь в решении определенных задач, которые достаточно сложно или невозможно решить простыми методами. Однако, проблема в использовании данного способа заключается в том, что по умолчанию данная функция скрыта в программе, из-за чего многие пользователи не догадываются о ее существовании. Также функция довольно трудна в освоении и использовании, но при ее должном изучении, она может принести значительную пользу и облегчить работу.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *