Графический подбор параметра
Excel предоставляет проведение подбора параметра с помощью манипулирования диаграммами. Рабочий лист, отображает предполагаемый объем продаж развивающейся компании. Предположим, что из опыта известно, что рост объема продаж компаний, работающих в этой отрасли, может увеличиваться по показательному закону: =y*(b^x)
| коэффициент | 1,148698355 | |
| первый год | ||
| прогноз продаж | ||
| формула – первый год * коэффициент | ||
| По данным прогноза продаж построена диаграмма | ||
Для выполнения графического подбора параметра следует: дважды щелкнуть левой кнопкой мыши на диаграмме, в результате ее можно будет редактировать, выберать столбики диаграммы. Щелкните на последнем столбике, чтобы его выделить. Поместите указатель курсора на вершину столбика, при этом он должен изменить форму. Перетащите столбик вверх и наблюдайте за изменением значения в поле адреса ячейки. Когда значение станет равным 10000000, отпустите кнопку мыши.
Описанные действия приводят к появлению диалогового окна Подбор параметра. Следует указать программе, какую ячейку использовать для ввода данных. Введите в это текстовое поле адрес ячейки B1. Программа вычислит значение коэффициента, необходимое для получения результата указанного на диаграмме. Если Вам нужно сохранить это число, щелкните на кнопке OK. Excel заменит текущее значение коэффициента новым, а диаграмма изменится автоматически.
Основным ограничением средства Подбор параметра является то, что оно может находить значение только в одной входной ячейке. Для изменения нескольких переменных одновременно необходимо использовать процедуру Поиск решения.
Поиск решения
В Excel предусмотрен мощный инструмент - Поиск решения, который позволяет расширить процедуру подбора параметра следующим образом:
· Указывать несколько изменяемых ячеек.
· Указывать ограничения на значения изменяемых ячеек.
· Находить решение, при котором значение в определенной ячейке рабочего листа достигает максимума или минимума.
· Получить несколько решений задачи.
Задачи, выполняемые с использованием процедуры поиска решения, относятся к сравнительно узкой области. Обычно они затрагивают случаи, удовлетворяющие следующим критериям:
· Значение в целевой ячейке зависит от других значений в ячейках и результатов расчета по формулам. Нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным или заранее определенным.
· Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками. Их значения надо подобрать так, чтобы получить желаемый результат в целевой ячейке.
· Решение (значение изменяемых ячеек) должно находиться в определенных пределах или удовлетворять определенным ограничениям.
После соответствующей подготовки рабочего листа можно использовать процедуру поиска решения для подбора значений в изменяемых ячейках и получения в целевой ячейке нужного результата, который одновременно удовлетворяет всем установленным Вами ограничениям.
Пример 1. Данные введены на рабочий лист, с помощью которого можно вычислить прибыль от продажи трех видов продукции. В столбце В отображено количество товара по каждому изделию, в столбце С - доход от продажи одной единицы изделия. В столбце D содержатся формулы, по которым вычисляется доход от продажи каждого вида товара путем умножения их количества на прибыль от продажи одной единицы изделия.
| A | B | C | D |
| Кол-во | Доход за ед. | Доход от подажи | |
| Изделие А | 13,00р. | 1 300,00р. | |
| Изделие В | 18,00р. | 1 800,00р. | |
| Изделие С | 22,00р. | 2 200,00р. | |
| Всего | 5 300,00р. |
У компании есть несколько ограничений, которые она должна учитывать:
1. Общий объем производства - всего 300 единиц изделий в день.
2. Компании нужно произвести 50 единиц изделия А для выполнения существующего заказа.
3. Компании нужно произвести 40 единиц изделия В для выполнения планового заказа.
4. Поскольку сбыт изделий С относительно небольшой, то должно быть изготовлено не больше 40 единиц этого изделия.
Процедура поиска решения выполняется следующим образом.
1. Введите в рабочий лист исходные данные и формулы (рис. 1).
2. Вызовите диалоговое окно Поиск решения.
3. Укажите целевую ячейку.
4. Укажите изменяемые ячейки.
5. Задайте ограничения.
6. При необходимости измените опции процедуры поиска решения.
7. Позвольте процедуре поиска решения выполнить поставленную задачу.
В результате выполнения указанных действий появится диалоговое окно Поиск решения
В этом примере в целевой ячейке D6 вычисляется общая прибыль по трем видам изделий. В поле Установить целевую ячейку укажите адрес ячейки D6 или щелкните в рабочем листе на этой ячейке. Поскольку наша цель - максимизировать значение в этой ячейке, установите переключатель Равной максимальному значению. Затем определите изменяемые ячейки, которые в данном случае находятся в диапазоне В3:В5.
Дальше введите ограничения задачи. Ограничения добавляются по одному за один раз и отображаются в окне Ограничения. Для добавления ограничений щелкните на кнопке Добавить.
Появится диалоговое окно Добавление ограничения
В этом диалоговом окне нужно ввести ссылку на ячейку, оператор и значение. Первое ограничение - общий объем продукции должен быть равен 300 единицам изделий. Введите адрес ячейки B6, выберите оператор равно (=) из раскрывающегося списка операторов и введите в качестве значения число 300. Для добавления очередного ограничения щелкните на кнопке Добавить. В таблице 1 приведены все ограничения для этой задачи.
Список ограничений
| Ограничения | Сокращенная запись |
| Объем выпуска 300 единиц | В6 = 300 |
| Не меньше 50 единиц изделия А | B3 >= 50 |
| Не меньше 40 единиц изделия В | B4 >= 40 |
| Не больше 40 единиц изделия С | B5 <= 40 |
После ввода последнего ограничения щелкните но кнопке OK. Вы вернетесь к диалоговому окну Поиск решения, которое в нашем примере должно содержать четыре ограничения. Теперь для выполнения процедуры поиска решения введены все исходные данные. Чтобы начать процесс решения задачи, щелкните на кнопке Выполнить. В строке состояния будет отображаться ход решения задачи. Через некоторое время на экране появится информация о том, что решение найдено.
Диалоговое окно Результаты поиска решения будет иметь вид
После решения задачи можно выбрать одну из следующих возможностей:
· Заменить исходные данные значения в изменяемых ячейках на те, которые были найдены в результате решения задачи.
· Восстановить исходные значения в изменяемых значениях.
· Создать несколько отчетов о процедуре поиска решения (для выбора нескольких отчетов из списка нажмите клавишу Shift и щелкните на нужном типе отчета).
· Щелкнуть на кнопке Сохранить сценарий для сохранения решения в виде сценария, который может быть использован в средстве Диспетчер сценариев.
Если указано, что нужно создать отчет, он будет помещен на новом рабочем листе, которому будет присвоено соответствующее имя.
В разделе отчета Ограничения будет указано состояние всех ограничений. Тип связанный означает, что данное ограничение удовлетворено, но при этом соответствующий параметр принял свое предельное значение, которое уже нельзя изменять.
Вид отчета
Диалоговое окно Параметры поиска решения, вызывается щелчком на кнопке Параметры в диалоговом окне Поиск решения. С помощью данного диалогового окна можно контролировать многие аспекты процесса решения задачи, загружать и сохранять спецификации моделей, заданных в виде диапазона.
Данное окно содержит следующие параметры процедуры поиска решения:
· Максимальное время - ограничение времени решения задачи.
· Предельное число итераций - максимальное число промежуточных решений, допускаемых при поиске.
· Относительная погрешность - точность выполнения ограничений и соответствия вычисленного значения в целевой ячейке с заданным.
· Допустимое отклонение - максимальное отклонение в процентах для целочисленных решений.
· Сходимость - применяется к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается.
· Линейная модель - служит для ускорения поиска решения, применяя к задаче оптимизации линейную модель.
· Неотрицательные значения - устанавливает нулевую нижнюю границу для тех влияющих ячеек, для которых не заданы ограничения.
· Автоматическое масштабирование - автоматическая нормализация входных и выходных значений, значительно различающихся по величине.
· Показывать результаты итераций - если установить эту опцию, то после выполнения очередной итерации процесс поиска решения приостанавливается и на экране отображается найденный результат.
· Разделы Оценки, Разности, Метод поиска - используются для контроля некоторых технических аспектов решения задачи. В большинстве случаев нет необходимости их изменять.
· Загрузить модель - отображает диалоговое окно Загрузить модель, в котором можно задать ссылку на диапазон ячеек рабочего листа, содержащих параметры модели.
· Сохранить модель - отображает диалоговое окно Сохранить модель, в котором нужно определить ссылку на диапазон ячеек рабочего листа, где будут сохранены параметры модели.
Таблицы подстановки