Изучение зависимостей.

Часто в исследованиях изучается зависимость некоторой величины от другой. Характер этих зависимостей стремятся выразить математическими формулами, коэффициенты которой могут иметь определенный физический смысл.

ПРИМЕР 2. Наиболее употребительна и проста в обработке линейная зависимость, которую можно выразить уравнением прямой у = kx + b. При этом коэффициент k показывает степень влияния х на у, а b – некоторое начальное значение у. Поскольку значения, полученные в ходе эксперимента, всегда включают некоторую ошибку, экспериментальные точки не лежат строго на прямой. Как же провести по этим разбросанным точкам наилучшую линию. Для этого используется статистический метод «наименьших квадратов» предлагающий достаточно сложные функции для нахождения коэффициентов k и b, а также для оценки их достоверности.

В Excel эта задача решается при помощи статистических функций НАКЛОН (наклон прямой относительно оси Х, коэффициент k) и ОТРЕЗОК (отрезок отсекаемый прямой на оси Y, коэффициент b). Кроме того, Excel позволяет построить график зависимости, саму прямую, которая называется линией тренда, а также вывести уравнение прямой на график.

Для знакомства с этим возможностями перейдем на Лист 3 нашей книги, назовем его «Зависимость» и введем необходимые исходные данные (таблица 2).

Таблица 2. - “Примерный вид листа «Зависимость»”

В колонках В и С вводятся данные эксперимента по измерению величин Х и У, записи в колонке Е играют роль подсказок, колонка F заполняется по мере обработки. Начнем с ячейки F3.

Ввод формул проводится с помощью мастера функций так, как это описывалось ранее. Маленькое отличие заключается в том, что у функций НАКЛОН и ОТРЕЗОК два аргумента: диапазон ячеек со значениями Y и диапазон ячеек со значениями Х. Щелкаем мышкой сначала по полю для ввода первого аргумента, показываем нужный диапазон (С3:С13). Затем щелкаем по второму поля и повторяем ввод (В3:В13). Также рассчитывается и значение функции ОТРЕЗОК в ячейке F4.

Для оценки достоверности можно использовать квадрат коэффициента корреляции Пирсона (R2). Если он равен 1, то имеет место полная корреляция с моделью, т.е. точки лежат строго на прямой. В противоположном случае, если коэффициент равен 0, то уравнение линейной зависимости полностью неудачно. Для его нахождения используется статистическая функция КВПИРСОН. Таким образом, данные нашего эксперимента с достоверностью 0,98 описываются уравнением у = 1,42х+0,905.

Рассмотрим теперь второй метод обработки и представления результатов эксперимента в виде графика. Для построения графиков и диаграмм в Excel’e используется Мастер диаграмм, который можно запустить, используя меню Вставка – Диаграмма, или кнопки на панели инструментов с условным изображением диаграммы. Предварительно щелкнем мышкой по любой свободной ячейке нашего листа.

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