Обработка экспериментальных данных в металлургии с использованием Microsoft Excel

Размещено на http://www.allbest.ru/

Министерство образования и науки Российской Федерации

Филиал федерального государственного бюджетного образовательного учреждения высшего профессионального образования

«Южно-Уральский государственный университет» (НИУ) в г. Аше

Кафедра «Металлургические процессы и оборудование»

Курсовая работа

по дисциплине «Методы анализа и обработки экспериментальных данных в металлургии»

Обработка экспериментальных данных в металлургии с использованием Microsoft Excel

Аша 2015



Введение

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

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

Целью курсовой работы является анализ и обработка экспериментальных данных с использованием программы Microsoft Excel.

Задачи курсовой работы:

· исследовать возможности программы Microsoft Excel при проведении статистического анализа;

· провести обработку данных, полученных в ходе исследования зависимости ударной вязкости нержавеющей стали 08Х18Н10Т от содержания в ней фосфора и титана;

· выявить взаимосвязь между ударной вязкость и химическим составом стали.



1. Статистический анализ. основные понятия и определения

1.1 Математическая статистика

Раздел математики, посвященный методам сбора, анализа и обработки статистических данных для научных и практических целей, называется математической статистикой.

Математическая статистика имеет дело с массовыми явлениями. Она тесно связана с теорией вероятностей и базируется на ее математическом аппарате.

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

Статистические данные представляют собой данные, полученные в результате обследования большого числа объектов или явлений.

Математическая статистика подразделяется на две основные области: описательную и аналитическую статистику. Описательная статистика охватывает методы описания статистических данных, представления их в форме таблиц, распределений и т. п.

Аналитическая статистика, или теория статистических выводов, ориентирована на обработку данных, полученных в ходе эксперимента, с целью формулировки выводов, имеющих прикладное значение для самых различных областей человеческой деятельности.

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

Основным методом не сплошного наблюдения является выборочный метод.

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

Выборка - это группа элементов, выбранная для исследования из всей совокупности элементов. Задача выборочного метода в том, чтобы сделать правильные выводы относительно всего собрания объектов, их совокупности.

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

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

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

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

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

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

Для построения выборочной функции распределения весь диапазон изменения случайной величины разбивают на ряд интервалов одинаковой ширины. Число интервалов обычно выбирают не менее 5 и не более 15. Затем определяют число значений случайной величины , попавших в каждый интервал. Поделив эти числа на общее количество наблюдений , находят относительную частоту попадания случайной величины в заданные интервалы. По найденным относительным частотам строят гистограммы выборочных функций распределения. Если соответствующие точки относительных частот соединить ломаной линией, то полученная диаграмма будет называться полигоном частот.

Кумулятивная кривая будет получена, если по оси абсцисс откладывать интервалы, а по оси ординат - число или долю элементов совокупности, имеющих значение, меньшее или равное заданному.

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

В Microsoft Excel для построения выборочных функций распределения используются специальная функция ЧАСТОТА и процедура Пакета анализа Гистограмма. Функция ЧАСТОТА вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив чисел. Функция задается в качестве формулы массива.

Синтаксис: ЧАСТОТА (массив данных; массив карманов), где массив данных - это массив или ссылка на множество данных, для которых вычисляются частоты; массив карманов - это массив или ссылка на множество интервалов, в которые группируются значения аргумента массив данных [1].

Количество элементов в возвращаемом массиве на единицу больше числа элементов в массив карманов. Дополнительный элемент в возвращаемом массиве содержит количество значений, больших, чем максимальное значение в интервалах.

Процедура Гистограмма используется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. Процедура выводит результаты в виде таблицы и гистограммы.

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

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

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

Простейшим показателем, характеризующим центр выборки, является мода.

Мода - это элемент выборки с наиболее часто встречающимся значением.

Средним значением выборки, или выборочным аналогом математического ожидания, называется величина

где - количество элементов в выборке.

Иначе говоря, среднее значение - это центр выборки, вокруг которого группируются элементы выборки. При увеличении числа наблюдений среднее приближается к математическому ожиданию.

Выборочная медиана - это число, которое является серединой выборки, то есть половина чисел имеет значения большие, чем медиана, а половина чисел имеет значения меньшие, чем медиана. Для нахождения медианы обычно выборку ранжируют - располагают элементы в порядке возрастания. Если количество членов ранжированного ряда нечетное, медианой является значение ряда, которое расположено посередине, то есть элемент с номером . Если число членов ряда четное, то медиана равна среднему значению членов ряда с номерами и .

Основными показателями рассеяния вариант являются интервал, дисперсия выборки, стандартное отклонение и стандартная ошибка.

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

Более точно отражают рассеяние показатели, учитывающие не только крайние, но и все значения элементов выборки.

Дисперсией выборки, или выборочным аналогом дисперсии, называется величина

Дисперсия выборки - это параметр, характеризующий степень разброса элементов выборки относительно среднего значения. Чем больше дисперсия, тем дальше отклоняются значения элементов выборки от среднего значения.

Выборочным стандартным отклонением (среднее квадратичное отклонение) называется величина

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

Стандартная ошибка или ошибка среднего находится из выражения

Стандартная ошибка - это параметр, характеризующий степень возможного отклонения среднего значения, полученного на исследуемой ограниченной выборке, от истинного среднего значения, полученного на всей совокупности элементов. С помощью стандартной ошибки задается так называемый доверительный интервал. 95-процентный доверительный интервал, равный , обозначает диапазон, в который с вероятностью (при достаточно большом числе наблюдений n > 30) попадает среднее генеральной совокупности MX.

Выборочной квантилью называется решение уравнения

1.2 Использование инструментов Мастера функций и Пакета анализа Excel при статистической обработке данных

В результате наблюдений или эксперимента получаются наборы данных, называемые выборками. Для проведения их анализа данные подвергаются статистической обработке. Первое, что всегда делается при обработке данных, это вычисление элементарных статистических характеристик выборок по каждому параметру и по каждой группе. Полезно также вычислить эти характеристики для объединения родственных групп и суммарно по всем данным.

В Мастере функций Excel имеется ряд специальных функций, предназначенных для вычисления выборочных характеристик. Прежде всего, это функции, характеризующие центр распределения [1].

Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Функция МЕДИАНА позволяет получать медиану заданной выборки. Функция МОДА вычисляет наиболее часто встречающееся значение. Функция ДИСП позволяет оценить дисперсию по выборочным данным. Функция СТАНДОТКЛОН вычисляет стандартное отклонение.

В пакете Excel помимо Мастера функций имеется набор более мощных инструментов для работы с несколькими выборками и углубленного анализа данных, называемый Пакет анализа, который может быть использован для решения задач статистической обработки выборочных данных [1].

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

1.3 Принятие статистических решений

Статистическая гипотеза - это предположение о виде или отдельных параметрах распределения вероятностей, которое подлежит проверке на имеющихся данных.

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

В большинстве случаев рассматривают так называемую нулевую гипотезу (нуль-гипотезу ), состоящую в том, что все события произошли случайно, естественным образом. Альтернативная гипотеза () состоит в том, что события случайным образом произойти не могли, и имело место воздействие некого фактора [1].

Обычно нулевая гипотеза формулируется таким образом, чтобы на основании эксперимента или наблюдений ее можно было отвергнуть с заранее заданной вероятностью ошибки . Эта заранее заданная вероятность ошибки называется уровнем значимости.

Уровень значимости - максимальное значение вероятности появления события, при котором событие считается практически невозможным. В статистике наибольшее распространение получил уровень значимости, равный . Поэтому, если вероятность, с которой интересующее событие может произойти случайным образом , то принято считать это событие маловероятным, и если оно все же произошло, то это не было случайным. В наиболее ответственных случаях, когда требуется особая уверенность в достоверности полученных результатов, надежности выводов, уровень значимости принимают равным или даже .

Величину , равную , называют доверительной вероятностью (уровнем надежности), то есть вероятностью, признанной достаточной для того, чтобы уверенно судить о принятом статистическом решении. Соответственно, в качестве доверительных вероятностей выбирают значения 0,95, 0,99 или 0,999.

Интервал, в котором с заданной доверительной вероятностью находится оцениваемый параметр, называется доверительным интервалом. В соответствии с доверительными вероятностями на практике используются 95-, 99-99,9-процентные доверительные интервалы. Граничные точки доверительного интервала называют доверительными пределами.

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

Для определения относиться та или иная варианта к данной статистической совокупности достаточно использовать правило трех сигм. Согласно этому правилу в пределах находится 99,7 % всех вариант. Поэтому если варианта попадает в этот интервал, то она считается принадлежащей к данной совокупности. Если не попадает, то она может быть отброшена. Хотя этот метод и предполагает нормальность исходного распределения, на практике он успешно работает и может быть использован в большинстве других случаев.

Определения границ доверительного интервала находится по формуле

где - среднее значение;

- табличное значение распределения Стьюдента с числом степеней свободы и доверительной вероятностью .

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

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

Графический метод позволяет давать ориентировочную оценку расхождения или совпадений распределений.

Наиболее убедительные результаты дает использование критериев согласия. Критериями согласия называют статистические критерии, предназначенные для проверки согласия опытных данных и теоретической модели. Здесь нулевая гипотеза представляет собой утверждение о том, что распределение генеральной совокупности, из которой получена выборка, не отличается от нормального. Среди критериев согласия большое распространение получил непараметрический критерий (хи-квадрат). Он основан на сравнении эмпирических частот интервалов группировки с теоретическими (ожидаемыми) частотами, рассчитанными по формулам нормального распределения.

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

Для применения критерия желательно, чтобы объем выборки был > 40, выборочные данные были сгруппированы в интервальный ряд с числом интервалов не менее 7, а в каждом интервале находилось не менее 5 наблюдений (частот).

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

В Microsoft Excel критерий хи-квадрат реализован в функции ХИ2ТЕСТ. Функция ХИ2ТЕСТ вычисляет вероятность совпадения наблюдаемых (фактических) значений и теоретических (гипотетических) значений. Если вычисленная вероятность ниже уровня значимости (0,05), то нулевая гипотеза отвергается и утверждается, что наблюдаемые значения не соответствуют нормальному закону распределения. Если вычисленная вероятность близка к 1, то можно говорить о высокой степени соответствия экспериментальных данных нормальному закону распределения.

Функция имеет следующий синтаксис: ХИ2ТЕСТ (фактический интервал; ожидаемый интервал), где фактический интервал - это интервал данных, которые содержат наблюдения, подлежащие сравнению с ожидаемыми значениями; ожидаемый интервал - это интервал данных, который содержит теоретические (ожидаемые) значения для соответствующих наблюдаемых [1].

Параметрические критерии служат для проверки гипотез о положении и рассеивании. Из параметрических критериев наибольшей популярностью при проверке гипотез о равенстве генеральных средних (математических ожиданий) пользуется t-критерий Стьюдента (t-критерий различия). Он наиболее часто используется для проверки следующей гипотезы: «Средние двух выборок относятся к одной и той же совокупности». Критерий позволяет найти вероятность того, что оба средних относятся к одной и той же совокупности. Если эта вероятность ниже уровня значимости ( < 0,05), то принято считать, что выборки относятся к двум разным совокупностям.

При использовании t-критерия можно выделить два случая. В первом случае его применяют для проверки гипотезы о равенстве генеральных средних двух независимых, несвязанных выборок (так называемый двухвыборочный t-критерий). В этом случае есть контрольная группа и опытная группа.

Во втором случае, когда одна и та же группа объектов порождает числовой материал для проверки гипотез о средних, используется так называемый парный t-критерий. Выборки при этом называют зависимыми, связанными.

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

В Microsoft Excel для оценки достоверности отличий по критерию Стьюдента используются специальная функция ТТЕСТ и процедуры Пакета анализа. Эти перечисленные инструменты вычисляют вероятность, соответствующую критерию Стьюдента, и используются, чтобы определить, насколько вероятно, что две выборки взяты из генеральных совокупностей, которые имеют одно и то же среднее.

Функция ТТЕСТ имеет следующий синтаксис: ТТЕСТ (массив1; массив2; хвосты; тип), где массив1 - это первое множество данных; массив2 - это второе множество данных; хвосты - число хвостов распределения. Обычно число хвостов равно 2; тип - это вид исполняемого t-теста. Возможны три варианта выбора: парный тест; двухвыборочный тест с равными дисперсиями; двухвыборочный тест с неравными дисперсиями [1].

Критерий Фишера используют для проверки гипотезы о принадлежности двух дисперсий одной генеральной совокупности и, следовательно, их равенстве. При этом предполагается, что данные независимы и распределены по нормальному закону. Гипотеза о равенстве дисперсий принимается, если отношение большей дисперсии к меньшей меньше критического значения распределения Фишера:

Критическое значение Фишера зависит от уровня значимости и числа степеней свободы для дисперсий в числителе и знаменателе.

В Microsoft Excel для расчета уровня вероятности выполнения гипотезы о равенстве дисперсий могут быть использованы функция ФТЕСТ (массив1; массив2) и процедура Пакета анализа Двухвыборочный F-тест для дисперсий.

Важным разделом статистического анализа является корреляционный анализ, служащий для выявления взаимосвязей между выборками.

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

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

Коэффициент корреляции - параметр, характеризующий степень линейной взаимосвязи между двумя выборками. Коэффициент корреляции изменяется от -1 (строгая обратная линейная зависимость) до 1 (строгая прямая пропорциональная зависимость). При значении коэффициента равном 0 линейной зависимости между двумя выборками нет. Здесь под прямой зависимостью понимают зависимость, при которой увеличение или уменьшение значения одного признака ведет, соответственно, к увеличению или уменьшению второго. При обратной зависимости увеличение одного признака приводит к уменьшению второго и наоборот.

Выборочный коэффициент линейной корреляции между двумя случайными величинами и рассчитывается по формуле

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

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

В Microsoft Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КOРРЕЛ. Функция имеет следующий синтаксис: КОРРЕЛ (массив1; массив2), где массив1 - это диапазон ячеек первой случайной величины; массив2 - это второй интервал ячеек со значениями второй случайной величины [1].

1.4 Регрессионный анализ

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

В линейном регрессионном анализе связь между случайными величинами предполагается линейной. В самом простом случае в линейной регрессионной модели имеются две переменные и . И требуется по парам наблюдений (), (),..., () построить (подобрать) прямую линию, называемую линией регрессии, которая наилучшим образом приближает наблюдаемые значения. Уравнение этой линии является регрессионным уравнением. С помощью регрессионного уравнения можно предсказать ожидаемое значение зависимой величины , соответствующее заданному значению независимой переменной .

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

В случае, когда рассматривается зависимость между одной зависимой переменной и несколькими независимыми переменными , ,..., , говорят о множественной линейной регрессии. В этом случае регрессионное уравнение имеет вид

где , ,..., - коэффициенты;

, ,... - независимые переменные;

- константа.

Мерой эффективности регрессионной модели является коэффициент детерминации (R-квадрат). Он определяет, с какой точностью полученное регрессионное уравнение описывает (аппроксимирует) исходные данные.

Значимость регрессионной модели исследуется с помощью F-критерия (Фишера). Если величина F-критерия значима (), то регрессионная модель является значимой.

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

В Microsoft Excel экспериментальные данные аппроксимируются линейным уравнением до 16 порядка:

где - зависимая переменная;

,..., - независимые переменные;

, ..., - искомые коэффициенты регрессии.

Для получения коэффициентов регрессии используется процедура Регрессия из Пакета анализа. Кроме того, могут быть использованы функция ЛИНЕЙН для получения параметров регрессионного уравнения и функция ТЕНДЕНЦИЯ для получения предсказанных значений в требуемых точках [1].



2. Анализ и обработка экспериментальных данных

2.1 Предварительная статистическая обработка экспериментальных данных

В таблице 1 приведены результаты исследования зависимости ударной вязкости стали 08Х18Н10Т от содержания в ней фосфора и титана.

Таблица 1 - Результаты исследования

Номер плавки	Ti, ()	P, ()	(y)
1	0,56	0,027	30,25
2	0,57	0,025	33,75
3	0,41	0,026	30,00
4	0,62	0,034	24,67
5	0,58	0,034	25,18
6	0,60	0,032	28,55
7	0,63	0,034	23,18
8	0,63	0,034	24,00
9	0,53	0,035	23,04
10	0,62	0,025	34,58
11	0,62	0,034	25,29
12	0,63	0,033	23,66
13	0,67	0,030	24,25
14	0,56	0,033	22,16
15	0,62	0,023	34,16
16	0,62	0,023	33,48
17	0,46	0,022	35,23
18	0,60	0,025	33,33
19	0,58	0,030	23,68
20	0,58	0,030	24,16
21	0,59	0,030	24,01
22	0,59	0,030	23,78
23	0,49	0,034	22,19
24	0,57	0,023	34,26
25	0,71	0,024	32,16
26	0,71	0,024	32,78
27	0,67	0,024	32,65
28	0,57	0,033	22,98
29	0,63	0,028	26,15
30	0,63	0,026	30,47
31	0,62	0,032	24,71
32	0,58	0,025	31,84
33	0,57	0,030	24,05
34	0,51	0,034	22,65
35	0,63	0,033	23,48

Для статистической обработки выборочных данных воспользуемся инструментом Microsoft Excel Пакет анализа. Чтобы определить характеристики выборки используется процедура Описательная статистика.

Проанализировав данные, получим следующие результаты (рисунок 1)

Рисунок 1 - Результаты анализа

Представим результаты измерений в виде вариационного ряда (таблица 2)

Таблица 2 - Вариационный ряд

Номер плавки	Ti, ()	P, ()	(y)
1	0,41	0,022	22,16
2	0,46	0,023	22,19
3	0,49	0,023	22,65
4	0,51	0,023	22,98
5	0,53	0,024	23,04
6	0,56	0,024	23,18
7	0,56	0,024	23,48
8	0,57	0,025	23,66
9	0,57	0,025	23,68
10	0,57	0,025	23,78
11	0,57	0,025	24,00
12	0,58	0,026	24,01
13	0,58	0,026	24,05
14	0,58	0,027	24,16
15	0,58	0,028	24,25
16	0,59	0,030	24,67
17	0,59	0,030	24,71
18	0,60	0,030	25,18
19	0,60	0,030	25,29

Вычислим доверительные интервалы для среднего арифметического при -ной доверительной вероятности (таблица 3), используя процедуру Описательная статистика.

Таблица 3 - Доверительные интервалы

Уровень надежности	ДИ (Титан)	ДИ (Фосфор)	ДИ (Ударная вязкость)
95 %	0,5930,021	0,0290,001	27,5651,572
99 %	0,5930,028	0,0290,002	27,5652,110
99,9 %	0,5930,038	0,0290,003	27,5652,785

2.2 Проверка гипотезы о нормальном распределении случайной величины

Для оценки соответствия имеющихся экспериментальных данных нормальному закону распределения, воспользуемся графическим методом и критерием согласия хи-квадрат.

Сформулируем нулевую гипотезу и альтернативную гипотезу [1, 3]:

- «Отличие экспериментальных данных от нормального закона распределения не существенно»,

- «Экспериментальные данные не подчиняются закону нормального распределения».

Если , где - экспериментальное значение критерия Пирсона, а - теоретическое значение критерия Пирсона, то нуль-гипотеза о нормальном законе распределения экспериментальных данных принимается с доверительной вероятностью . В противном случае нуль-гипотеза отвергается и принимается альтернативная гипотеза.

1. Для ударной вязкости

Таблица 4 - Данные для вычисления критерия Пирсона

Интервал
22,1 - 24,3	15	0,238	0,238	8,322	6,678	5,359
24,3 - 26,5	5	0,408	0,170	5,958	-0,958	0,154
26,5 - 28,7	1	0,598	0,190	6,649	-5,649	4,800
28,7 - 30,9	3	0,767	0,169	5,915	-2,915	1,437
30,9 - 33,1	4	0,887	0,120	4,194	-0,194	0,009
33,1 - 35,3	7	0,955	0,068	2,370	4,630	9,042

Экспериментальное значение критерия Пирсона определяется суммированием данных последнего столбца таблицы 4. Теоретическое значение критерия Пирсона определяется при заданном уровне значимости и числе степеней свободы c использованием функции Microsoft Excel ХИ2ОБР(;). Тогда .

Т.к. , т.е. , то принимается альтернативная гипотеза, следовательно, экспериментальные данные не подчиняются закону нормального распределения. Для построения гистограммы необходимо предварительно сгруппировать данные и вычислить относительные частоты (таблица 5).



Таблица 5 - Данные для построения гистограммы

№ интервала	Интервал
1	22,1 - 24,3	15	0,43	0,19
2	24,3 - 26,5	5	0,14	0,06
3	26,5 - 28,7	1	0,03	0,01
4	28,7 - 30,9	3	0,09	0,04
5	30,9 - 33,1	4	0,11	0,05
6	33,1 - 35,3	7	0,20	0,09

Построим гистограмму относительных частот (рисунок 2)

Рисунок 2 - Гистограмма частот

По гистограмме видно, что экспериментальные данные не подчиняются закону нормального распределения.

2. Для титана

Таблица 6 - Данные для вычисления критерия Пирсона

Интервал
0,41 - 0,46	2	0,016	0,016	0,543	1,457	3,913
0,46 - 0,51	2	0,090	0,075	2,618	-0,618	0,146
0,51 - 0,56	3	0,301	0,211	7,383	-4,383	2,602
0,56 - 0,61	12	0,617	0,316	11,046	0,954	0,082
0,61 - 0,66	12	0,868	0,251	8,777	3,223	1,183
0,66 - 0,71	4	0,973	0,106	3,702	0,298	0,024



Т.к. , т.е. , то принимается нулевая гипотеза, следовательно, отличие экспериментальных данных от нормального закона распределения не существенно.

Построим гистограмму относительных частот (рисунок 3)

Таблица 7 - Данные для построения гистограммы

№ интервала	Интервал
1	0,41 - 0,46	2	0,06	1,13
2	0,46 - 0,51	2	0,06	1,13
3	0,51 - 0,56	3	0,09	1,70
4	0,56 - 0,61	12	0,34	6,79
5	0,61 - 0,66	12	0,34	6,79
6	0,66 - 0,71	4	0,11	2,26

Рисунок 3 - Гистограмма частот

По гистограмме видно, что отличие экспериментальных данных от нормального закона распределения не существенно.

3. Для фосфора

Таблица 8 - Данные для вычисления критерия Пирсона

Интервал
0,023 - 0,025	11	0,167	0,167	5,851	5,149	4,531
0,025 - 0,027	3	0,324	0,157	5,483	-2,483	1,125
0,027 - 0,029	1	0,520	0,197	6,883	-5,883	5,028

Т.к. , т.е. , то принимается альтернативная гипотеза, следовательно, экспериментальные данные не подчиняются закону нормального распределения.

Построим гистограмму относительных частот (рисунок 4)

Таблица 9 - Данные для построения гистограммы

№ интервала	Интервал
1	0,023 - 0,025	11	0,31	145,05
2	0,025 - 0,027	3	0,09	39,56
3	0,027 - 0,029	1	0,03	13,19
4	0,029 - 0,032	6	0,17	79,12
5	0,032 - 0,034	6	0,17	79,12
6	0,034 - 0,036	8	0,23	105,49

Рисунок 4 - Гистограмма частот

По гистограмме видно, что экспериментальные данные не подчиняются закону нормального распределения.



2.3 Проверка экспериментальных данных на наличие грубой погрешности

Для проверки данных применим статистические критерии Граббса и трех сигм.

Сформулируем нулевую гипотезу и альтернативную гипотезу :

- грубой погрешности (промаха) нет,

- грубая погрешность (промах) есть.

При использовании критерия Граббса, если , то нулевую гипотезу отвергают и принимают альтернативную.

При использовании критерия трех сигм, если , то нулевую гипотезу отвергают и принимают альтернативную.

Таблица 10 - Проверка на промахи

Ударная вязкость	Фосфор	Титан
30,25	0,59	0,027	0,496	0,56	0,033
33,75	1,35	0,025	0,965	0,57	0,023
30,00	0,53	0,026	0,731	0,41	0,183
24,67	0,63	0,034	1,146	0,62	0,027
25,18	0,52	0,034	1,146	0,58	0,013
28,55	0,22	0,032	0,677	0,6	0,007
23,18	0,96	0,034	1,146	0,63	0,037
24,00	0,78	0,034	1,146	0,63	0,037
23,04	0,99	0,035	1,381	0,53	0,063
34,58	1,53	0,025	0,965	0,62	0,027
25,29	0,50	0,034	1,146	0,62	0,027
23,66	0,85	0,033	0,912	0,63	0,037
24,25	0,72	0,030	0,208	0,67	0,077
22,16	1,18	0,033	0,912	0,56	0,033
34,16	1,44	0,023	1,435	0,62	0,027
33,48	1,29	0,023	1,435	0,62	0,027
35,23	1,68	0,022	1,669	0,46	0,133
33,33	1,26	0,025	0,965	0,60	0,007
23,68	0,85	0,030	0,208	0,58	0,013
24,16	0,74	0,030	0,208	0,58	0,013
24,01	0,78	0,030	0,208	0,59	0,003
23,78	0,83	0,030	0,208	0,59	0,003
22,19	1,17	0,034	1,146	0,49	0,103
34,26	1,46	0,023	1,435	0,57	0,023
32,16	1,00	0,024	1,200	0,71	0,117
32,78	1,14	0,024	1,200	0,71	0,117
32,65	1,11	0,024	1,200	0,67	0,077
22,98	1,00	0,033	0,912	0,57	0,023

Проанализировав данные таблицы 10, убедимся, что грубых погрешностей нет.

2.4 Корреляционный и регрессионный анализ экспериментальных данных

Для нахождения коэффициентов корреляции воспользуемся процедурой Корреляция из Пакета анализа [1].

Получим следующие результаты

Рисунок 5 - Коэффициент корреляции

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

Проверим значимость коэффициента корреляции.

Сформулируем нулевую гипотезу и альтернативную гипотезу :

- коэффициент корреляции равен нулю,

- коэффициент корреляции не равен нулю.

Если , где , то нулевая гипотеза на уровне значимости отвергается, т.е. связь между переменными значима.

-статистика находится по формуле

- табличное значение, при , , .

Для взаимосвязи ударная вязкость-титан:

Поскольку , нулевую гипотезу принимаем, т.е. связь между ударной вязкостью и содержанием титана в стали незначима.

Для взаимосвязи ударная вязкость-фосфор:

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

Проведем регрессионный анализ с помощью процедуры Регрессия из Пакета анализа Microsoft Excel [2].

Для взаимосвязи ударная вязкость-титан, получим следующие результаты

В строке Регрессия, столбец Значимость F, рисунка 7, приводится уровень значимости критерия Фишера - , который должен быть меньше, 0,05 для значимой модели. Т.к. в нашем случае , то модель незначима.



Рисунок 6 - Регрессионная статистика

R-квадрат показывает степень точность описания моделью процесса. Поскольку R-квадрат , точность аппроксимации недостаточна.

Для взаимосвязи ударная вязкость-фосфор, получим следующие результаты

Рисунок 7 - Регрессионная статистика

Т.к. уровень значимости критерия Фишера меньше (рисунок 8), то модель значима, а поскольку R-квадрат равен , то точность аппроксимации удовлетворительна.

Значения коэффициентов модели указаны в столбце Коэффициенты, следовательно: Значимость коэффициентов указана в столбце P-Значение. При P-Значение , коэффициент значим. Т.к. P-Значение для обоих коэффициентов меньше , то коэффициенты значимы.

Выражение для определения ударной вязкости в зависимости от содержания фосфора в стали будет иметь вид:

2.5 Множественный регрессионный анализ

Проведем множественный регрессионный анализ с помощью процедуры Регрессия из Пакета анализа Microsoft Excel.

Получим следующий результат

Рисунок 8 - Множественный регрессионный анализ

Т.к. уровень значимости критерия Фишера меньше (рисунок 9), то модель значима. Поскольку R-квадрат равен , то точность аппроксимации удовлетворительна.

Значения коэффициентов модели указаны в столбце Коэффициенты, следовательно: Коэффициенты и значимы, коэффициент незначим.

Выражение для определения ударной вязкости в зависимости от содержания фосфора и титана в стали будет иметь вид:



Заключение

статистика математический excel корреляционный

В технических науках часто приходится сталкиваться с необходимостью обработки и анализа экспериментальных данных, полученных в результате наблюдения. В ходе курсовой работы было показано, что необходимым инструментарием для анализа данных обладает программа Microsoft Excel. С ее помощью были проанализированы результаты исследования зависимости механических свойств стали 08Х18Н10Т от химического состава. Была выявлена сильная обратная взаимосвязь между ударной вязкостью и содержанием фосфора в стали. Также было установлено, что изменение процентного содержания титана в стали не влияет на ударную вязкость.



Библиографический список

1. Т.В. Борздова, Основы статистического анализа и обработки данных с применением Microsoft Excel: учебное пособие /Борздова Т.В. - Минск: ГИУСТ БГУ, 2011. - 75 с.

2. В.Р. Бараз, Использование MS Excel для анализа статистических данных: учебное пособие/Бараз В.Р., Пегашин В.Ф. - 2-е изд. - Нижний Тагил: НТИ (филиал) УрФУ, 2014. - 181 с.

3. В.Е. Гмурман, Теория вероятностей и математическая статистика: учебное пособие для вузов/Гмурман В.Е. - 9-е изд. - М.: Высшая школа, 2003. - 479 с.

Размещено на Allbest.ru