Поиск решения двухпараметрической задачи в Excel

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

Пусть был проведен эксперимент, например, измерили зависимость какого-то параметра от температуры и давления. Средняя температура была равна 100^оC. Шаг изменения – 50^оС. Среднее давление – 2 атм. Шаг изменения – 1 атм. Такая система будет описываться зависимостью

, (4)

являющейся поверхностью, которую часто показывают в виде, подобной контурной карте (Рис.113).

Рис.113. Поверхность, описываемая зависимостью

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

В открывающемся диалоговом окне (Рис.114) можно выбрать:

Рис.114.

ü куда мы хотим переместить (скопировать) наш лист (в текущую книгу или новую). Выберите название текущей книги;

ü перед каким листом мы хотим поместить текущий лист или его копию. Выберите «(переместить в конец)».

Не забудьте поставить галочку Создать копию, в противном случае лист просто переместиться в конец книги. После чего нажмите Ok.

Рис.114. Диалоговое окно Переместить или скопировать

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

Сначала перестроим таблицу исходных данных, как показано на Рис.115.

Рис.115. Исходные данные

Рис.116. Таблица Эксперимент

Выделите две ячейки в верхней части старой таблицы (те, в которых было помещено название параметра «Температура» и ее значение) и выполните команду Вставка Þ Ячейки…. При этом откроется диалоговое окно Добавление ячеек, в котором будет предложено их местоположение. Поставьте переключатель в положение «ячейки со сдвигом вниз» и нажмите кнопку Ok.

Выделите пустой столбец С (щелкните по заголовку этого столбца) и выполните команду Вставка Þ Столбцы. Внесите необходимые изменения в таблицу.

Приведите подобным образом таблицу эксперимента к виду, показанному на рис.116. Напомним, что заголовки столбцов Температура и Давление должны вводиться по формулам, чтобы сделать заготовку более универсальной.

Заполним теперь данные таблицы Эксперимент.

Координаты точек 1 – 9 можно вычислить в соответствии с рисунком (Рис.113) по следующим формулам:

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

Значения Y_экспер мы должны взять из эксперимента. Пусть они равны:

Y_расчет должны вычисляться по формуле:

(5)

Рис.117. Таблица коэффициентов

Прежде, чем вводить формулу (5), необходимо модифицировать таблицу коэффициентов, как показано на рис.117, введя начальные значения коэффициентов.

Для подбора функции воспользуемся методом минимизации суммы квадратов разности экспериментальных (Y_экспер) и расчетных (Y_расчет) данных, который мы рассматривали в прошлом уроке.

Формулы расчета квадрата разности и формула расчета критерия Пирсона у нас на листе уже есть. Теперь достаточно поправить в них ссылки и выполнить.

Рис.118. Параметры поиска решения

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

ü допустимое отклонение – 1%;

ü Автоматическое масштабирование;

ü оценки – Квадратичные;

ü разности – Центральные.

После этого нажать кнопку Ok и в окне Поиск решения ð Выполнить. Если удовлетворительной точность не достигнута с первой попытки, операцию поиска решения можно повторить.

Наконец, нам осталось только построить поверхность.

Для этого сначала на новом листе построим матрицу данных (Рис.119).

На этом же листе пониже введите заголовок таблицы. В ячейке А16 вводим Х/Y.

Далее необходимо заполнить в матрице данных диапазоны температур и давлений, при которых проводился наш эксперимент. Для этого в ячейки А17 и В16 вводим минимальные значения температуры и давления соответственно. Отчитываем 11 ячеек вниз по столбцу А и вводим максимальное значение температуры (равное 150). Выделяем интервал между минимальным и максимальным

Рис.119. Матрица данных

Рис.120. Окно прогрессии

значениями, и во вкладке Главная выбираем Вид ð Заполнить ()ð Прогрессия. В открывшемся диалоговом окне (рис.120) все оставляем без изменения и нажимаем ОК. Наш интервал температур заполнится значениями от минимального до максимального значений с определенным шагом. Те же самые действия необходимо выполнить в строке 16 для ввода значений давления, при которых проводился эксперимент.

Осталось ввести в матрицу данных только значения функции Y_расчет (5). Чтобы не вводить функцию снова, скопируем любую набранную формулу из столбца Y_расчет таблицы Эксперимент. Далее выделяем ячейку В17 и вставляем в окно ввода формул скопированную формулу. В этой формуле ссылки на температуру и давление остались прикрепленными к столбцам таблицы Эксперимент, их необходимо перенести или изменить соответственно данным матрицы. Для этого в строке ввода формул ставим курсор мыши на формулу в любом месте и получаем разноцветные ссылки на элементы формулы в таблице Эксперимент. Все ссылки на температуру (а их три) аккуратно переносим в первую ячейку колонки температур в матрице, а ссылки на давление (тоже три) переносим на строку в первую ячейку в матрице данных.

Поскольку значения температур изменяются по колонке, необходимо все ссылки на температуру сделать смешанными, т.е. закрепить колонку знаком доллара ($А17). Изменение давления происходит по строке, значит, закрепляем строку (В$16). После закрепления всех ссылок на температуру и давление растягиваем формулу на всю область.

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

=$B$7+$B$8*$A17+$B$9*B$16+$B$10*$A17^2+$B$11*$A17*B$16+$B$12*B$16^2

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

Рис.121. Диаграмма поверхности

Окончательный вид диаграммы будет примерно таким, как показано на Рис.121.