Пример построения поверхности z = x 2 – y 2; 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 пар значений, например, Xi, Yi, i=1…n,требуется описать эти результаты некоторой функциональной зависимостью. Чтобы не решать классическую задачу аппроксимации экспериментальных данных (что для студентов 1-го курса достаточно сложно), Excel позволяет решить эту задачу путем выбора функциональной зависимостииз некоторых предлагаемых вариантов функций: линейной, полиномиальной, логарифмической, степенной и экспоненциальной.
Линейная – Y(x) = a*x + b используется, когда экспериментальные данные изменяются практически с постоянной скоростью.
Полиномиальная – Y(x) = a0 + a1*x + a2*x2+…an*xn, степень полинома зависит от вида экспериментального графика.
Логарифмическая – Y(x) = a*ln(x) + b. Применяется для описания экспериментальных данных, которые вначале быстро растут или убывают, а затем стабилизируются.
Экспоненциальная –Y(x) = b*eax. Применяется достаточно часто для описания экспериментальных данных.
Степенная–Y(x) =b*xn. Данные не должны иметь отрицательных и нулевых значений.
Линией тренда для графика в Excel будет линия, описанная математическим уравнением, построенная компьютером и максимально приближенная к экспериментальному графику. В Excel степень близости аппроксимации экспериментальных данных выбранной функцией оценивается коэффициентом достоверности аппроксимации R2 (в математике называемым коэффициентом детерминации).
Для построения линии тренда предварительно вводим данные в таблицу Excel и строим график экспериментальной зависимости. На рис. 17 представлены таблица и график экспериментальных данных.
Рис. 17.Таблица исходных данных и график экспериментальных данных
Затем проводится подбор линии тренда (аппроксимирующей зависимости). Для этого на графике экспериментальных данных щелчком правой клавиши мыши вызывается контекстное меню, в котором выбирается пункт «Добавить линию тренда». Далее выбирается вид аппроксимирующей зависимости и задаются дополнительные параметры.
После построения и оформления графика переходим на вкладку «Макет» и нажимаем кнопку «Линия тренда» (рис. 18), затем в выпадающем меню выбираем кнопку «Дополнительные параметры линии тренда». Появится окно:
Рис. 18.Окно параметров линии тренда
Необходимо определить, к какому типу из предложенных в программе будет относиться диаграмма, и поставить флажок напротив описания этого типа. После этого линия тренда появится в области построения диаграммы. Далее, не закрывая окно, поставим флажки напротив надписей: «Показывать уравнение на диаграмме» и «Показывать величину достоверности аппроксимации».
После закрытия окна (рис. 19) в области построения диаграммы должны появиться следующие изменения:
- линия тренда,
- описание линии тренда в легенде,
- уравнение, описывающее линию тренда,
- R2 – величина достоверности аппроксимации.
Рис. 19. График неизвестной функции с линией тренда
