Построение функции двух переменных (построение поверхности)

Пример построения поверхности z = x ² – y ²; xÎ[–2,2] и yÎ[–2,2]

Перед решением этой задачи необходимо вспомнить про относительную и абсолютную адресацию ячеек. Во всех предыдущих примерах использовалась относительная адресация ячеек, т.е. при «растягивании» (копировании) формул на диапазон аргумента, ссылки на адреса ячеек аргумента автоматически изменялись. В тех случаях, когда адрес ячейки не должен меняться, применяют абсолютную адресацию. Для этой цели используют знак $, который вводится вручную либо с использованием функциональной клавиши F4. Например, $A$5 – это абсолютный адрес ячейки A5, который не будет меняться при копировании формул. Пример смешанной адресации: $A5, столбец A меняться не будет, а номер строки будет меняться (рис. 15).

Рис. 15. Использование относительной и абсолютной адресации ячеек

Для построения поверхности указанного выше типа создаем таблицуданных.  В столбец A, начиная со второй строки, вводим значения аргумента Х, значения второго аргумента У – в первую строку, начиная со столбца B.

В ячейку В2 вводим формулу:

=$A2^2–B$1^2.

Затем растягиваем формулу на все ячейки диапазона.

Знак $ в имени ячейки, стоящий перед номером строки (столбца), создает абсолютную ссылку на строку (столбец). При растягивании формулы на все ячейки диапазонабудут вычислены все значения функции Z при соответствующих значениях аргументов X и Y. Заметим, что абсолютные ссылки (помеченные символом $) остаются неизменными.

Переходим непосредственно к построению поверхности. Выделяем все значения аргументов и функции. Затем через верхнее меню обращаемся к «Мастеру диаграмм». В меню «вставка»→«диаграммы»→«другие» выбираем тип, и поверхность построена. Далее, через меню «Макет» можно сделать дополнительные подписи; на графике они отсутствуют, чтобы не усложнять рисунок (рис. 16).

Рис. 16.Поверхность как функция двух переменных

Построение линий тренда

Часто при обработке результатов эксперимента, которые можно представить совокупностью n пар значений, например, X_i, Y_i, i=1…n,требуется описать эти результаты некоторой функциональной зависимостью. Чтобы не решать классическую задачу аппроксимации экспериментальных данных (что для студентов 1-го курса достаточно сложно), Excel позволяет решить эту задачу путем выбора функциональной зависимостииз некоторых предлагаемых вариантов функций: линейной, полиномиальной, логарифмической, степенной и экспоненциальной.

Линейная – Y(x) = a*x + b используется, когда экспериментальные данные изменяются практически с постоянной скоростью.

Полиномиальная – Y(x) = a0 + a1*x + a2*x²+…a_n*xⁿ, степень полинома зависит от вида экспериментального графика.

Логарифмическая – Y(x) = a*ln(x) + b. Применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем стабилизируются.

Экспоненциальная –Y(x) = b*e^ax. Применяется достаточно часто для описания экспериментальных данных.

Степенная–Y(x) =b*xⁿ. Данные не должны иметь отрицательных и нулевых значений.

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

Для построения линии тренда предварительно вводим данные в таблицу Excel и строим график экспериментальной зависимости. На рис. 17 представлены таблица и график экспериментальных данных.

Рис. 17.Таблица исходных данных и график экспериментальных данных

Затем проводится подбор линии тренда (аппроксимирующей зависимости). Для этого на графике экспериментальных данных щелчком правой клавиши мыши вызывается контекстное меню, в котором выбирается пункт «Добавить линию тренда». Далее выбирается вид аппроксимирующей зависимости и задаются дополнительные параметры.

После построения и оформления графика переходим на вкладку «Макет» и нажимаем кнопку «Линия тренда» (рис. 18), затем в выпадающем меню выбираем кнопку «Дополнительные параметры линии тренда». Появится окно:

 

Рис. 18.Окно параметров линии тренда

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

После закрытия окна (рис. 19) в области построения диаграммы должны появиться следующие изменения:

- линия тренда,

- описание линии тренда в легенде,

- уравнение, описывающее линию тренда,

- R² – величина достоверности аппроксимации.

 

Рис. 19. График неизвестной функции с линией тренда