Аналитическое описание
Экспериментальных зависимостей
(простая регрессия)
В практике статистического исследования весьма часто возникает необходимость определить не только корреляционное соотношение между изучаемыми характеристиками, но и установить определенную обусловленность между ними, представив выявленную связь в строгой аналитической форме. В этом случае результат исследования – экспериментальная зависимость воздействия какого-либо фактора (скажем, производительности труда, уровня образования, практического стажа работы и т.д.) на изменение изучаемого параметра (например, величины прибыли фирмы) - может быть не только представлен в виде графика (что весьма наглядно), но и описан математически с использованием аппроксимирующего выражения (эмпирической формулы).
Компьютерный подбор оптимального уравнения регрессии
Анализируемый процесс может быть описан в математической форме, при этом используемые эмпирические формулы могут иметь различный вид. Поэтому выбор оптимального уравнения диктуется только одним соображением – данные теоретического расчёта (т.е. полученные из уравнения) должны в наибольшей степени совпадать с фактическими результатами.
Рассмотрим на конкретном примере возможность решения подобной задачи с использованием приложения Excel.
Обсуждается следующая задача:
Проведено N=8 опытов по изучению некоторой зависимости y=f(x). В каждом варианте опыты повторялись n раз, при этом число параллельных измерений для каждого конкретного варианта опыта могло заметно различаться (от 3 дублей до 5). Полученные экспериментальные данные представлены в табличной форме (табл.1).
Таблица 1
Результаты опыта по исследованию зависимости y=f(x)
| Номер опыта N | Значение аргумента x | Значение функции y в повторных опытах | ||||
| - | - - - |
Надлежит выполнить следующие процедуры:
1. Провести первичную статистическую обработку экспериментальных данных с выявлением грубых промахов, определением среднеквадратичного отклонения и вычислением доверительного интервала для уровня значимости =0,05.
2. Построить график рассматриваемой зависимости и подобрать для неё эмпирическую формулу.
3. Дать статистическую оценку подобранному уравнению.
Приступим к решению данного примера. Удобнеё всего придерживаться привычного алгоритма, т.е. будем указывать пошаговую последовательность наших манипуляций при работе с компьютером.
1. Сначала запустим Excel и откроем рабочий лист, в котором будет формироваться наш документ.
2. Теперь нужно ввести опытные данные. Для этого фактически придется повторить исходную таблицу, т. е. указать номера опытов, значения аргумента x и всезначения функции y в параллельных опытах. Далеё добавим к нашей таблице ещё два столбца, в которые будут введены среднее арифметическое x, среднеквадратичное отклонение Sn и доверительный интервал ∆ х для каждого опыта, т.е. итоговые расчёты для каждой строки.
3. Приступим теперь к расчёту среднего арифметического и стандартного отклонения для каждой строки. Для этого нужно воспользоваться Мастером функций. Перед запуском Мастера нужно выделить ту ячейку, в которую будет помещён искомый результат. Например, для определения среднего арифметического значения данных первой строки активизируем верхнюю ячейку предпоследней колонки. Затем запустим Мастер функций (кнопкой fx. или же в строке меню используем команды Вставка/Функция).
Действия Мастера функций:
- в появившемся диалоговом окне следует выбрать нужную функцию из списка (все функции разбиты на категории). Для этого в левой части панели (там перечислены категории) выберем требуемую под названием Статистические, затем в правой части, где указаны функции, активизируем собственно нужную функцию Срзнач и далеё нажмём на кнопку ОК;
- выделим теперь все ячейки первой строки, относящиеся к параметру y, т.е. это те ячейки, где расположены дубли первого опыта. После чего – кнопка ОК. Если теперь взглянуть на содержимое ячейки среднего арифметического, то там и будет указан полученный результат.
4. Далеё полагалось бы подобную процедуру проделать для всей матрицы (таблицы). Делаем следующеё. Выделим ячейку, где содержится среднее арифметическое, и протянем Маркер заполнения (маленький квадратик в правом нижнем углу) вдоль всей предпоследней колонки вниз. Что получим? Во всех соответствующих ячейках будут содержаться готовые расчётные данные среднего значения!
5. Подобные манипуляции проделываем и для следующей колонки – среднеквадратичного (стандартного) отклонения. Сделаем только одно пояснение. При работе с Мастером функций нужно будет активизировать функцию Стандотклон.
Если окажется, что число знаков после запятой велико, то разрядность можно отрегулировать, активизировав соответствующую ячейку с данным числом, а затем в инструментальной строке использовать команду Уменьшить разряд.
6. Для расчёта доверительного интервала используем те же опции посредством Мастера функций. Вся необходимая процедура становится понятной из рис.3.5-3.6: нужно выделить функцию Доверит (рис.1), а затем в появившемся окне Аргументы функции заполнить запрашиваемые строки (рис.3.6). Для уровня значимости α укажем 0,05; затем введем значение уже рассчитанного стандартного отклонения Sn и число дублей n. Для первой строки это будет выглядеть так, как показано на рис.2.
Рис.1. Поиск функции Доверит
Рис.2. Панель для заполнения опции Аргументы функции
Тут следует обратить внимание на следующеё обстоятельство. При вычислении доверительного интервала нужно указывать число дублей, но их значения оказываются неодинаковыми – меняются от 3 (в 7-ом опыте) до 5 (в 5-и случаях). Поэтому такой расчёт нужно будет провести самостоятельно для каждой строки. Итоговый результат можно видеть на рис. 3.
Рис.3. Экспериментальные данные после статистической обработки
7. Теперь пришел черёд проверить имеющиеся экспериментальные данные на наличие грубого промаха. Так, в первой серии настораживает результат 2-го измерения. Проверку надлежит провести по методу максимального относительного отклонения. Как делать – это уже знакомая процедура (см. ДЗ-1) Допустим, выполненные расчёты показали, что с вероятностью 95% этот результат следует признать грубым промахом (он не соответствует данной числовой совокупности). По этой причине его надлежит исключить из дальнейшего рассмотрения (т.е. в окончательном варианте число дублей первого опыта составит n =4).
8. Казалось бы, в очередной раз придется заняться расчётом среднего и стандартного отклонения (в данном случае для первой строки). Однако поступим следующим образом. Выделим ту ячейку, в которой содержится выскакивающий результат, и нажмём клавишу Delete. Ячейка станет свободной, но при этом автоматически поменяются значения Срзнач и Стандотклон.
Несколько иначе выглядит процедура определения доверительного интервала. Особенность структуры данной электронной таблицы такова, что изъятие выскакивающего результата не повлияет на изменение данных в ячейке. Причина та же – число дублей, как отмечалось, в разных опытах неодинаково. Поэтому для анализируемого варианта (1-й строки) придется отдельно вновь рассчитать Dу.
Окончательный результат показан на рис.4. Незанятые (пустые) позиции в таблице означают отсутствие данных измерения в указанном повторном опыте или изъятие "нехорошего" (выскакивающего) результата.
Наконец, приступим к самому интересному этапу нашего задания – строим в графической форме анализируемую зависимость. В этом случае нам будет помогать Мастер диаграмм. Он запускается либо нажатием клавиши на стандартной панели инструментов, либо через команды Вставка/Диаграмма в строке меню.
Рис.4. Итоговые данные
9.1. Запустим Мастер диаграмм и выполним рекомендации первого шага – выберем тип диаграммы. В появившемся окне, в левой его части, высветим тип диаграммы – График. Здесь же, нажав кнопку Просмотр результата, можно будет посмотреть, как станут выглядеть наши данные на диаграмме выбранного типа.
9.2. Нажмём на клавишу Далеё и перейдём, следовательно, ко второму шагу. В окне будет активизирована вкладка Диапазон данных. Теперь в кнопке Ряды в следует указать, что наши данные представлены в Столбцах. Отметим, что на оси ординат будут указаны заданные численные значения аргумента, а вот на оси абсцисс пока содержатся некие нейтральные показатели типа 1, 2, 3 и проч.
9.3. В пределах окна второго шага высветим вкладку Ряд и в строке Подписи оси X ставим маркер. После чего сдвинем это кно так, чтобы можно было увидеть ту колонку таблицы, где сидят наши данные по аргументу x. Выделим весь этот столбец – на графике по оси абсцисс появятся фактические значения аргумента.
9.4. Совершим затем следующий, третий шаг (клавиша Далее). Он позволяет указать конкретные параметры диаграммы. Запустив вкладку Заголовки, присвоим название диаграмме ("Экспериментальная зависимость"), а также отметим оси координат (записываем символы X и Y). По желанию можно "украсить" график – добавить или убрать сетку (вкладка Линии сетки), дать необходимые комментарии к графику (вкладка Легенда).
9.5. Последний шаг – укажем, где желательно разместить график. Для этого вновь нажимаем на кнопку Далеё и отмечаем место расположения его – на имеющемся листе или же отдельном. После завершения этой процедуры последняя приятная операция – прикоснуться к кнопке Готово. Получаем график, имеющий вид, представленный на рис.3.9.
Рис.5. Графика исследуемой зависимости
Может оказаться, что габариты графика нас решительно не устраивают. Для придания ему более благообразного и удобного вида выделим Область диаграммы (должны появиться по периметру маркеры-засечки) и поменяем размеры (указатель мыши подведём к маркерам – должны возникнуть двойные стрелки, которые и нужно перемещать). Схожим образом можно изменить габариты самого графика (в пределах имеющейся области диаграммы), выделив Область построения диаграммы
В случае надобности можно также исправить вид осей координат, изменив шрифт или размер цифр шкалы, добавив промежуточные засечки. Для этого нужно подвести стрелку мыши к выбранной оси и щёлкнуть правой клавишей. Появится окно Формат оси, которое после его активизации и позволит осуществить нужные манипуляции.
10. Заключительная процедура нашей работы (своеобразный "высший пилотаж" статистической обработки результатов измерения) – это аналитическое описание построенной экспериментальной зависимости. Для этого подведём стрелку мыши к линии графика и щёлкнем правой клавишей. Появится окно Формат рядов данных. Выделим опцию Добавить линию тренда, в результате появится всплывающеё окно Линия тренда. На вкладке Тип выберем похожий на нашу кривую график-шаблон. Для данного случая вполне подходящей оказывается полиноминальную зависимость второй степени (квадратное уравнение). Перейдём затем к вкладке Параметры и укажем засечками команды Показать уравнение на диаграмме и Поместить на диаграмме величину достоверной аппроксимации R2. После нажатия клавиши ОК график примет окончательный вид маленького компьютерного шедевра (рис.3.10). Отметим, что наша экспериментальная кривая практически полностью совпала с теоретической. Это и неудивительно, поскольку аппроксимирующий коэффициент близок к 1 – идеальное соответствие!
Рис.6. Окончательный вид аналитической зависимости
Фактически данную работу на этом можно считать и законченной. Однако сделаем ещё некоторые оценки. Дело в том, что мы, пользуясь эталонным набором кривых аналитических зависимостей (вкладка Тип из окна Формат рядов данных), удачно выбрали полиноминальный вид функции. Количественно об этом можно судить по величине аппроксимирующего коэффициента R2. Можно вполне обоснованно показать, что выбранная зависимость является, похоже, наилучшей. С этой целью для наглядности проверим и другие функции, нанеся на график соответствующую линию тренда, а также показав получаемые уравнения регрессии и величины коэффициента R2.
Рис.7 Экспоненциальная зависимость
Рис.8. Линейная зависимость
Рис.9. Логарифмическая зависимость
Рис.10. Степенная зависимость
Такую процедуру нетрудно выполнить, после чего для рассмотренного примера полученные показатели R2 для разных уравнений регрессии будут иметь следующий вид:
экспоненциальная - R2 =0,999;
полиномиальная - R2 =0,997;
линейная - R2 =0,925;
степенная - R2 =0,922;
логарифмическая - R2 =0,730.
Как видно, обсуждаемая зависимость y=f(x) лучше всего, как и предполагалось, описывается экспоненциальным уравнением. Этот вывод базируется не только на визуальных впечатлениях (вполне адекватное совпадение экспериментальной кривой и линии тренда), но и на строгом количественном расчёте с использованием статистического коэффициента R2. Вместе с тем можно утверждать, что ещё более обоснованным представляется описание аппроксимации в виде экспоненциального уравнения, поскольку в этом случае рассчитанное значение коэффициента фактически оказывается равным единице.
Библиографический список
1. Бараз В.Р. Применение программы Excel для статистических расчетов в материаловедении. Екатеринбург: ГОУ ВПО УГТУ-УПИ, 2003. 46 с.
2. Бараз В.Р., Пегашкин В.Ф. Использование MS Excel для анализа статистических данных. Учебное пособие. – Нижний Тагил: НТИ(ф) УГТУ-УПИ, 2014. – 188 с.