Офисные прикладные программы MS Office 2007: Microsoft Excel

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

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

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

Z = f{X,Y),

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

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

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

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

Не забудьте поставить галочку Создать копию, в противном случае лист просто переместиться в конец книги. Закончите клавишей ОК.

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

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

Приведите таблицу эксперимента к виду, показанному на Рис. 116.

Для добавления столбца необходимо выделить столбец G, щелкнув по заголовку этого столбца, и выполнить команду Главная-Ячейки-Вставить-Вставить столбцы на лист.

Внесите необходимые изменения в таблицу. Для удаления строки необ­ходимо ее выделить, щелкнув по заголовку этой строки, и выполнить команду Главная-Ячейки-Удалить-Удалить строки с листа.

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

Координаты точек 1-9 можно вычислить по следующим формулам:

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

Значения Yэкспер мы должны взять из эксперимента (рис. 117). Yрасчет должны вычисляться по формуле:

Прежде, чем вводить формулу для Ypaсчет, необходимо модифицировать таблицу коэффициентов согласно Рис. 118, введя начальные значения коэффициентов 1.

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

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

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

• Автоматическое масштабирование;
• разности - Центральные.

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

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

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

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

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

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

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

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

=$В$7+$В$8*$А17+$В$9*В$16+$В$10*$А17 ^2+$В$11* $А17*В$16+ +$В$12 *В$16^2

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