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

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

2. Имеется функция одной или нескольких переменных и необходимо найти значения переменных, при которых функция равна либо минимуму либо максимуму с учётом дополнительных ограничений.

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

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

Рассмотрим решение такой задачи. Надо найти максимум функции при следующих ограничениях:

Эти данные можно внести на лист следующим образом:

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

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

Поле «Установить целевую ячейку» должно содержать адрес целевой ячейки. В нашем случае это адрес А5.

Затем установить переключатель «Равной» в положение «Максимальному значению».

Поле «Изменяя ячейки» должно содержать диапазон ячеек, содержащих независимые переменные. В нашем случае это А3:В3.

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

Появится диалоговое окно с тремя полями расположенными в ряд. Самое левое поле «Ссылка на ячейку», правее поле со списком, и ещё правее поле «Ограничение».

Поле «Ссылка на ячейку» должно содержать адрес ячейки с формулой ограничения. В поле со списком выбирается знак ограничения. Поле «Ограничение» содержит либо величину ограничения, либо адрес ячейки, где эта величина содержится.

В нашем случае, формула первого ограничения представляет собой просто переменную х1, поэтому указываем адрес А3, затем выбираем из списка неравенство больше или равно и величину ограничения указываем равной нулю. Чтобы добавить следующее ограничение надо в этом же окне щелкнуть по кнопке «Добавить» и внести новые данные. И так, до тех пор, пока не будут набраны данные по всем ограничениям. После внесения данных по последнему ограничению надо щелкнуть по кнопке ОК.

После этого можно щелкнуть по кнопке «Выполнить».

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

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

В нашем случае ячейки должны содержать: А3=2, В3=4, А5=1400, А8=10, А9=8.

Если появится сообщение «Процесс не сходится», то это означает, что решения не существует или оно не найдено при установленных параметрах поиска. Эти параметры можно изменить, если до щелчка по кнопке «Выполнить» нажать кнопку «Параметры». Можно попробовать изменить параметры «Максимальное время», «Предельное число итераций», «относительная погрешность» и «Допустимое отклонение».

Анализ данных с использованием метода наименьших квадратов

Пусть экспериментально изучается зависимость одной величины у от другой величины х. Причём, величину х мы можем задавать сами, а величину у получаем в результате измерения или наблюдения. Например, ежедневное наблюдение за курсом доллара. В этом случае величина х это дата, а величина у – курс доллара. Или, например, экспериментальное изучение закона Ома для участка цепи, где х представляет собой напряжение на участке цепи, а у – силу тока в цепи.

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

Если такая зависимость подобрана, то её график называют линией тренда. Процедура подбора зависимости, да и сама зависимость называются аппроксимацией.

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

Делается это следующим образом.

Сначала на лист заносятся имеющиеся данные в виде таблицы. Например, пусть данные по величине х находятся в ячейках А2:А11, а соответствующие данные по величине у - в ячейках В2:В11. В ячейках А1 и В1 соответственно находятся заголовки.

Выделяется массив А1:В11 и вызывается мастер диаграмм. Обязательно выбирается тип диаграммы «Точечная». Вид диаграммы лучше оставить просто точками без всяких линий и можно сразу нажать кнопку «Готово».

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

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

Появится диалоговое окно с двумя закладками «Тип» и «Параметры».

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

На закладке «Параметры»:

• ввести название кривой (не обязательно);
• Указать на сколько шагов вперёд или назад сделать прогноз (не обязательно);
• Если точно известно, что при х=0 у должен быть равен какой-то определённой величине, то можно установить флажок «пересечение кривой с осью У в точке» и внести это значение;
• Обязательно установить флажок «показывать уравнение на диаграмме»;
• Установить флажок «поместить на диаграмму величину достоверности аппроксимации R^2».

Нажав на кнопку ОК, получим на диаграмме уравнение зависимости у от х и величину R².

Чем ближе величина R2 к единице, тем лучше аппроксимация.

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

Так как полиномиальная зависимость представляет собой уравнение вида , то при выборе полиномиальной зависимости надо ещё указать на закладке «Тип» и наивысшую степень (по умолчанию стоит 2).

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

Контрольные вопросы

1. Какого типа задачи можно решать с помощью инструмента «Поиск решения»?

2. Как учитываются ограничения при решении задач оптимизации?

3. Что такое аппроксимация?

4. Что такое линия тренда и как её добавить на диаграмму?

5. Что характеризует величина R^2?