Статобработка экспериментальных данных в ms excel

Статобработка экспериментальных данных в MS Excel, Учебное пособие, Бондарчук С.С., Бондарчук И.С., 2018.

 Издание охватывает базовые направления математической статистики, используемые при анализе экспериментальных данных в областях биологии, медицины, химии, при анализе физических и педагогических измерений. Каждый раздел издания посвящен конкретному аспекту анализа и обработки данных эксперимента. Теоретические положения сопровождается и дополняется методическими указаниями и примерами, реализованными исключительно в среде MS Ехсеl. Критические значения статистик, не поддерживаемых функциями электронных таблиц, представлены соответствующими аппроксимациями. Для студентов (бакалавров, магистрантов и аспирантов), преподавателей и научных работников, занимающихся анализом и статистической обработкой экспериментальных данных.

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

Оглавление.
Предисловие.
1.Базовые термины.
2.Дисперсионный анализ. Однородность.
3.Критерии согласия.
4.Параметрические критерии.
5.Непараметрические критерии.
6.Линейные и нелинейные зависимости. Регрессия. Коэффициент корреляции.
7.Таблицы сопряженности. Корреляции качественных признаков.
8.Исключение грубых погрешностей.
9.Основы планирования эксперимента.
Приложения.
Вопросы для самопроверки.
Каталог примеров.
Предметный указатель.
Перечень использованных источников.

Бесплатно скачать электронную книгу в удобном формате, смотреть и читать:

Скачать книгу Статобработка экспериментальных данных в MS Excel, Учебное пособие, Бондарчук С.С., Бондарчук И.С., 2018 — fileskachat.com, быстрое и бесплатное скачивание.

Скачать pdf
Ниже можно купить эту книгу по лучшей цене со скидкой с доставкой по всей России.Купить эту книгу

Скачать
— pdf — Яндекс.Диск.

Дата публикации: 21.10.2022 11:45 UTC

Теги:

Бондарчук :: статистика :: математика :: биология :: химия :: медицина :: excel

Статьи
Карта сайта
Главная страница

Ввод текста помогает оформлять заголовки таблиц, записывать
определенные пояснения. Допустим, нам надо рассчитать объем раствора по его
массе 10 г и плотности 1,25 г/мл, используя простейшую формулу V=m/d. Введем
в ячейки В5, С5, D5 заголовки столбцов будущей таблицы,
обозначения величин m, d и V, и приступим к вводу чисел. В
ячейку В6 введем численное значение массы 10. Заканчиваем ввод, нажимая Enter, и убеждаемся, что тест в ячейке, как правило, смещен к правой границе, а число к левой. Это удобно, так как позволяет замечать ошибки
ввода. В ячейку С6 введем дробное число 1,25. Здесь надо учесть, что в
зависимости от настройки конкретного компьютера для разделения целой и дробной
части числа может использоваться или запятая, или точка. При неправильном вводе
наши символы будут восприниматься как текст,  или даже как дата (янв.25).

Наконец, в ячейке D6 введем формулу, по
которой Excel будет проводить вычисления. Ввод формулы начинается со знака
равенства (=). Затем надо показать программе, где находится первое число в
нашей формуле, масса раствора, дать адрес этой ячейки  — В6. Конечно, можно
набрать этот адрес с клавиатуры, надо только учитывать, что В – это символ
английского алфавита. Поэтому, гораздо проще просто щелкнуть по нужной ячейке и
ее адрес будет введен автоматически (=В6). Далее надо ввести знак
арифметического действия. Эти знаки удобно вводить с правой части клавиатуры,
напоминающей клавиатуру калькулятора. Здесь есть клавиши со знаком сложения
(+), вычитания (-), умножения (*) и деления (/). И, наконец, надо показать
компьютеру, где находится делитель – щелкаем мышкой по ячейке С6 и получаем
окончательный вид формулы (=В6/С6). Нажимаем Enter, и,
если все было набрано правильно, получаем в ячейке D6 результат
(8). Таким образом, формулы возвращают в ячейку результат вычислений, число. Но
если щелкнуть по ячейке и посмотреть на строку формул, мы увидим, что на самом
деле находится в ней.

Иногда формула может возвращать и сообщение об ошибке. Щелкнем
по ячейке В6 и введем вместо числа 10 символы «10 г». В ячейке D6
тут же окажется сообщение #ЗНАЧ!, которое говорит о
неверном значении в одной из ячеек. Действительно, запись «10 г» воспринимается
уже как текст. Чтобы исправить ошибку надо снова вместо «10 г»  ввести число
10. (Для исправления неверных действий можно использовать и кнопку «Отменить»
на панели инструментов). Щелкнем теперь по ячейке С6 и нажмем клавишу “Del”. Этим мы удалим содержимое ячейки, и в соседней ячейке
тут же получим сообщение #ДЕЛ/0! (ошибка деления на 0). Действительно,
на ноль делить нельзя и ошибку надо исправить.

Итак, мы научились вводить числа и формулы, а значит и проводить
простейшие вычисления в Excel. Но как упростить эту процедуру, если таких
вычислений много? Здесь помогают приемы копирования, и автоматического
заполнения ячеек методом «протягивания». Пусть у нас 10 порций раствора массой 10 г, и в ячейки В6, В7 …, В16 надо ввести 10, 10, … и т.д. Щелкнем по ячейке В6, где число 10 уже
введено. В черной рамке выделенной ячейки, внизу справа, есть маленький черный
квадратик. При наведении на него указателя мышки, последний меняет форму. Если
в этот момент «взяться» (нажать левую кнопку мыши) и потянуть вниз, до ячейки
В16, то все десять ячеек окажутся автоматически заполнены нужным числом. Не
труднее заполнить и 100 ячеек!

А если массы растворов отличаются на некоторую постоянную
величину, например 10, 12,5, 15 г и т.д.? В этом случае достаточно ввести два
значения: число 10 в ячейку В6 и число 12,5 в ячейку В7. Теперь надо выделить
эти две ячейки. Для этого щелкаем по первой ячейке и, не отпуская кнопки, ведем
до второй. Теперь обе ячейки обведены жирной рамкой. Снова беремся за черный
квадратик и тянем вниз. Получаем ряд значений от 10 до 35.

Поскольку предполагается, что раствор у нас один и тот же,
оставим колонку С в покое и попробуем методом протягивания скопировать формулу,
которая у нас набрана в ячейке D6. Проделываем уже
описанную операцию: выделяем ячейку, беремся, протягиваем… и получаем во всех
ячейках, кроме первой, ошибку! Разберемся, почему это произошло, для чего
щелкнем по ячейке D7 и посмотрим на строку формул. В
ячейке D6 было написано «=В6/С6», а в ячейке D7 уже «=В7/С7»! То есть, при копировании формул Excel
автоматически меняет адреса ячеек, откуда он берет данные для расчетов. И это
совершенно правильно, когда речь идет о массе раствора. Но плотность раствора у
нас постоянная, как показать программе, что адрес этой ячейки менять не надо?

Для этого мы должны познакомиться с такими понятиями, как
относительный и абсолютный адрес. Те адреса, которые мы использовали,
называются относительными и меняются при копировании. Адрес в абсолютной форме
сопровождается знаками доллара и выглядит так: $C$6. Вот
эту поправку нам и надо внести в формулу в ячейке D6.

Исправлять записи в ячейках удобнее в строке формул. Щелкнем
сначала по ячейке D6, (формула появится в строке
формул), затем в нужном месте строки формул – там появится курсор. Конечно
знаки доллара можно ввести с клавиатуры, но проще, установив курсор на адресе
С6, нажать на клавиатуре клавишу F4. Понажимайте ее
несколько раз и посмотрите, как будет меняться адрес. Он может быть полностью
абсолютным, абсолютным по строчке, по колонке, и полностью относительным.
Добейтесь нужного вида и нажмите Enter. Формула
исправлена, теперь ее снова можно протянуть до ячейки D16.
Если все сделано правильно, вы получите ряд значений от 8 до 28 мл.

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

Формулы с
функциями.

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

Для перехода на нужный лист достаточно щелкнуть по его ярлычку.
Начнем работу с краткого повторения пройденного: дадим листу 2 имя «Ошибки», в
ячейку А3 введем текст «Данные эксперимента», в ячейки А5 и В5 — заголовки
новой таблицы «№» и «Х». Предполагается что мы проделали серию из 10 опытов,
измеряя некоторую величину Х (здесь не важно, что это, длина побега или объем
раствора). Номера опытов от 1 до 10 легко ввести протягиванием, а вот численные
значения Х надо последовательно ввести (табл.1).

Таблица 1. Примерный вид листа
«Ошибки»

Записи в колонках D и
Е – это подсказки, которые помогут разобраться с тем, какие характеристики мы
будем рассчитывать. Колонка F у
Вас должна быть пока пустой, в нее будем помещать наши формулы.

Обработку результатов начнем с расчета числа опытов n. Казалось бы это очевидное число, но в ходе работы, какой-то
результат мы можем отбросить, или провести еще пару опытов. Желательно, чтобы
нам не пришлось при этом переделывать все формулы. Для определения числа
значений используется специальная функция, которая называется СЧЕТ. Для ввода
формулы с функциями используется Мастер функций, который запускается командой
«Вставка функции» через меню «Вставка» – «Функция» или кнопкой на панели
инструментов с обозначением   f_x. Щелкнем мышкой по ячейке F6,
где должен находиться результат и запустим Мастер функций.

Первый шаг работы (рисунок 1) служит
для выбора нужной функции. Все функции разделены, в зависимости от своего
назначения на несколько категорий (математические, логические и др.). Для
обработки данных эксперимента используются в основном статистические функции.
Поэтому, прежде всего в списке категорий выбираем категорию «Статистические».
Во втором окне появляется список статистических функций. Если щелкнуть по любой
из них, внизу появляется краткое описание функции. Специальной ссылкой можно
вызвать систему помощи Excel, в которой данная функция будет разобрана
подробно, с примерами. Список функций упорядочен по алфавиту, что позволяет без
труда нужную нам функцию СЧЕТ («Подсчитывает количество чисел в списке
аргументов»). Выделив щелчком эту функцию, нажимаем кнопку Ok и переходим к шагу 2.

Второй шаг (рисунок 2) служит для задания аргументов функции. 
Функции СЧЕТ надо указать, какие числа ей надо пересчитывать, или в каких
ячейках находятся эти числа. Диапазон ячеек указывается адресами первой и
последней ячейки, записанными через двоеточие, в нашем случае данные находятся
в ячейках В6:В15. Как и в других случаях эти адреса лучше не вводить, а показать
мышкой. Для этого устанавливаем указатель мышки на первую ячейку, нажимаем
левую кнопку и ведем до последней. Обратите внимание, что окно аргументов можно
перемещать, если оно заслоняет нужную часть экрана. Кроме того, рядом с полем
для ввода есть маленькая кнопка с красной стрелочкой. При щелчке по ней окно
аргументов сворачивается до узкой полоски. Когда мы показываем в основном окне
диапазон ячеек, в окне аргументов появляется запись диапазона адресов, а рядом
с ним – значения чисел из первых ячеек. Предварительное значение функции тоже
показывается после ввода ее аргументов. Это помогает избегать ошибок. Помогает
работе с мастером функций и подсказка под полем для ввода аргументов, в которой
разъясняется их смысл и возможные значения. Заканчивается работа с мастером
функций нажатием кнопки “Ok” или клавиши “Enter”. Если все сделано правильно, в ячейке F6 появится нужное значение “10”.

Следующие два этапа обработки серии опытов проводятся
аналогично. В ячейке F7 c
помощью функции СРЗНАЧ рассчитывается
среднее значение выборки, в ячейке F8 – стандартное
отклонение выборки, с помощью функции СТАНДОТКЛОН.
. Будьте аккуратны при выборе функций
– среди них есть очень похожие по названию. Аргументами этих функций служит все
тот же диапазон ячеек.

Следующая формула сложная, частично она набирается как обычная
формула, начиная с символа ”=”. Указав, где находится делимое S и набрав знак операции (=F8/), вызываем
мастер функций. Функция КОРЕНЬ – математическая, поэтому на первом шаге
выбираем категорию математических функций. Аргументом этой функции служит число
опытов, которое мы рассчитали в ячейке F6. Окончательный
вид формулы “=F8/ КОРЕНЬ(F6)”.

Для расчета доверительного интервала необходимо определить
коэффициент Стьюдента. Он зависит от вероятности ошибки (при обычно задаваемой
надежности 95% вероятность ошибки составляет 5%), и от числа степеней свободы n-1). Для нахождения коэффициента Стьюдента используется
статистическая функция Excel СТЬЮДРАСПОБР (“Стьюдента распределение обратное“).
Особенностью этой функции является то, что первый аргумент, число 5% (или 0,05)
вводится в соответствующее окно с клавиатуры. Для второго указываем адрес
ячейки, где находится значение n,
затем дописываем в окне “-1”. Получаем запись “F6-1”.

Для нахождения
доверительного интервала используется обычная формула умножения. Конечно,
вместо букв там должны стоять адреса ячеек, где находятся коэффициент Стьюдента
и стандартное отклонение среднего. Как правило, значение доверительного
интервала округляется до одной значащей цифры, такой же порядок окружения
должен быть и у среднего. Поэтому окончательный результат можно записать так: с
95%-ной надежностью Х = 14,80±0,05. В заключение посчитаем относительную ошибку определения Х: d = ДИ / Х_ср (формула: “=F11/F7”).
Значение относительной ошибки обычно выражают в процентах, у нас 0,3%.

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

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

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

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

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

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

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

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

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

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

Рисунок 3. 

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

На втором шаге необходимо указать, где у нас находится
независимая величина Х и зависящая от нее Y (рисунок 4).
Для этого щелкаем по ярлычку вкладки «Ряд» и затем по кнопке «Добавить».

Рисунок 4.

Открываются поля для указания Х и Y. Ввод
значений адресов в эти поля не отличаются от работы с Мастером функций (только
при вводе Y предварительно
сотрите условное значение “={1}”. Если Вы правильно выполните эту часть работы,
на поле вверху уже появится примерный вид графика.

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

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

Рисунок 5.

Итак, мы рассмотрели важнейшие приемы работы в Microsoft Excel, необходимые для качественной
обработки данных эксперимента. Разумеется эти приемы не исчерпывают всех
возможностей Excel, и могут развиваться в ходе работы.
Автор статьи с удовольствием ответит на все вопросы, связанные с работой в
данной программе. Желаю успеха!

Задать вопрос.

•дисперсионный анализ;

•планирование регрессионных экспериментов и выборочных обследований и др.

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

Ниже в табл. 8.1 представлены адреса электронных ресурсов, содержащих информацию о некоторых распространенных статистических пакетах.

Таблица 8.1

На базе электронных таблиц можно провести некоторую статистическую обработку данных для большинства инженерных задач. Функции, реализующие статистические методы обработки и анализа данных, в Microsoft Excel реализованы в виде специального программного расширения — надстройки «Пакет анализа», которая

149

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

Установка надстройки «Пакет анализа» производится из меню «Сервис/Надстройки», после чего в диалоговом окне «Надстройки» необходимо отметить флажок пункта «Пакет анализа» и нажать кнопку ОК.

Ниже в таблице 8.2. приведены основные функции пакета анализа.

Таблица 8.2

153

Список использованных источников:

1.Подобие и моделирование

1.1Седов Л.И. Методы подобия и размерности в механике.- М.:Наука, 1981.-448 с.

1.2Веников В.А., Веников Г.В. Теория подобия и моделирование/применительно к задачам электроэнергетики/.- М.:Наука,1984.-439 с.

2.Планирование эксперимента

2.1.Планированиеэксперимента в технике / В.И.Барабащук, Б.П.Креденцер, В.И.Мирошниченко; под. ред.. Б.П.Креденцера.- К.:Техніка ,1984.-200с.

2.2.Адлер Ю.П., Маркова Е.В., Грановский Ю.В. Планирование эксперимента при поиске оптимальных условий.-М.:Наука,1971.- 283с.

2.3.Ахназарова С.Л., Кафаров В.В. Методы оптимизации в химической технологии .-М.Высшая школа, 1985.-325 с.

3.Статистическая обработка результатов эксперимента

3.1.Вентцель Е.С. Теория вероятностей,-М.Наука,1969.-576 с.

3.2.Вентцель Е.С., Овчаров Л.А. Теория вероятностей и ее инженерные приложения.-М.:Наука.-1988.- 480 с.

3.3.Коваленко И.Н., Филиппова А.А. Теория вероятностей и математическая статистика.-М., Высшая школа, 1973.-368 с.

3.4.Базара М.,Шетти К. Нелинейное программирование. Теория и алгоритмы.-М.,Мир.-1982.-583 с.

3.5.Колкер Я.Д. Математический анализ точности механической обработки деталей.-Киев, Техника.-1976.-200с.

3.6.Румшинский Л.З. Математическая обработка результатов эксперимента.-М.,Наука.-1971.-192 с.

3.7.Протасов К.С. Статистический анализ экспериментальных данных. -М., Мир, 2005.-142 с.

154

3.8.Письменный Д.Т. конспект лекций по теории вероятностей, математической статистике и случайным процессам.-М.,Айрис пресс, 2006.-288 с.

3.9.Минько А.А. Статистический анализ в MS Exel.-М.: Издательский дом «Вильямс»,2004.-448 с.

3.10.Свешников А.А. Прикладные методы теории случайных функций.:Глав.ред.физ.-матем. лит. Изд-ва «Наука», 1968.-663с.

155

Название: Статобработка экспериментальных данных в MS Excel
Автор: Бондарчук С.С., Бондарчук И.С.
Издательство: Томск: Томский государственный педагогический университет (ТГПУ)
Год: 2018
Формат: PDF
Страниц: 433
Для сайта:

vtome.ru

Размер: 17 mb
Язык: русский

Издание охватывает базовые направления математической статистики, используемые при анализе экспериментальных данных в областях биологии, медицины, химии, при анализе физических и педагогических измерений.
Каждый раздел издания посвящен конкретному аспекту анализа и обработки данных эксперимента. Теоретические положения сопровождается и дополняется методическими указаниями и примерами, реализованными исключительно в среде MS Ехсеl. Критические значения статистик, не поддерживаемых функциями электронных таблиц, представлены соответствующими аппроксимациями.
Для студентов (бакалавров, магистрантов и аспирантов), преподавателей и научных работников, занимающихся анализом и статистической обработкой экспериментальных данных.

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

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

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

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

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

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

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

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

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

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

Числа, составляющие генеральную совокупность, называются ее
элементами. Закон F(x) распределения случайной величины X называется
генеральным законом распределения, а числовые характеристики X – генеральными
числовыми характеристиками. Так как генеральная совокупность – большая, то
перебрать все ее элементы невозможно, поэтому для изучения генеральной совокупности
из нее делают выборку и по ее свойствам судят о свойствах генеральной
совокупности.

Выборкой называется множество измеренных значений n x ,x
,…,x 1 2 случайной величины X. Выборки разделяются на повторные (с
возвращением) и бесповторные (без возвращения). Требования к выборке. Для того
чтобы сделать правильный вывод о генеральной совокупности по выборке, выборка
должна быть репрезентативной, т. е. правильно представлять генеральную
совокупность. Выборка будет обладать таким свойством, если каждый объект
генеральной совокупности будет иметь один и тот же шанс быть выбранным, в этом
случае выборка является случайной. Число N объектов генеральной совокупности и
число n объектов выборки называют объемами генеральной и выборочной
совокупностей соответственно.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Параметрические критерии служат для проверки гипотез о
положении и рассеивании. Из параметрических критериев наибольшей популярностью
при проверке гипотез о равенстве генеральных средних (математических ожиданий)
пользуется 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 – это второй интервал ячеек со значениями второй
случайной величины [3].

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

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

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

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

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

где , ,…,  – коэффициенты;

, ,…  – независимые переменные;

 – константа.

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

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

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

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

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

,…,  – независимые переменные;

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

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

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

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

В таблице 1 приведены результаты исследования механических
свойств нержавеющей стали 08Х18Н10Т (твёрдости у,) от факторов – параметров
химического состава:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Для Углерода

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

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

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

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

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

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

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

2. Для Марганца

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

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

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

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

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

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

3. Для твердости

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для взаимосвязи Твердость-углерод:

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

Для взаимосвязи Твердость  -Марганец:

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

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

Для взаимосвязи Твердость – Марганец, получим следующие
результаты

1-
й способ. Функция ЛИНЕЙН.

воспользуемся статистической функцией ЛИНЕЙН.

Рисунок- 6- Функция
ЛИНЕЙН

2    
й
способ (графический). Построение линии тренда

Рисунок-7
Графический способ

3й способ. Инструмент анализа
Регрессия.

Рисунок-8
Регрессия

Анализ результатов. Построена
линейная регрессионная модель

Ан=31,91+56,22

Коэффициент корреляции между наблюдаемыми и предсказанными
моделью значениями R= 0,108

Оценка адекватности построенной модели проведена с помощью
параметра «Значимость F» Значимость F меньше  0,05 поэтому модель может
считаться адекватной с вероятностью 0,95.

Оценка значимости коэффициентов модели проведена по параметру
«Р- значение» Поскольку это значение меньше 0,05, то с вероятностью 0,95 можно
считать, что соответствующие коэффициентов модели значимы.

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

Рисунок
9 – Полиноминальная модель

По расчётам для полиноминальной модели  наибольшее значение
корреляции имеет полиноминальная модель 6 степени.

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

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

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

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

Рисунок 11 Остаток

 Так как значимость F больше значимости а=0,05 то построенная
регрессия не является значимой. (рисунок 9). Поскольку R-квадрат равен , то точность слабая.

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

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

Заключение

корреляционный

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

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

1. Математическая статистика : учеб.-метод. пособие /
авт.-сост. : С. Е. Демин, Е. Л. Демина ; М-во образования и науки РФ ; ФГАОУ ВО
«УрФУ им. первого Президента России Б.Н.Ельцина», Нижнетагил. технол. ин-т
(фил.). – Нижний Тагил : НТИ (филиал) УрФУ, 2016. – 284 с.

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

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

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

5. Веременюк В. В., Крушевский Е.А., Мороз О. А./ Статистическая
обработка экспериментальных данных/ Минск БНТУ, 2015-77с.

6. Е .А. Лукерьянова/ Математическая
статистика часть 2/ Курган/ 2018-48с.

Учебное пособие. — Томск: Издательство Томского государственного педагогического университета, 2018. – 433 с.

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

Содержание
Предисловие
Базовые термины
Измерения, шкалы и величины
Генеральная совокупность. Выборка
Функции распределения
Статистические гипотезы и критерии
Описательная статистика
Дисперсионный анализ. Однородность
Критерий Фишера
Критерий Кохрена
Критерий Бартлетта
Критерий знаков
Тест Левина
Достоверности совпадений и различий для порядковой шкалы
Достоверности совпадений и различий для дихотомической шкалы
Критерии согласия
Критерий согласия Пирсона
Критерий Колмагорова
Критерий Колмагорова-Смирнова
Критерий Крамера-Мизеса-Смирнова
Метод моментов
Параметрические критерии
Критерий Стьюдента
Z-критерий
Однофакторный анализ ANOVA
Критерий множественных сравнений
Непараметрические критерии
Критерий Крамера-Уэлча
Тест Краскела-Уоллиса
Критерий Вилкоксона-Манна-Уитни
Критерий Вилкоксона связанных выборок
Линейные и нелинейные зависимости. Регрессия. Коэффициент корреляции
Линейная регрессия
Нелинейные зависимости. Аппроксимация и идентификация параметров
Коэффициенты корреляции Пирсона и Спирмена
Градуировка. Пределы обнаружения аналита
Таблицы сопряженности. Корреляции качественных признаков
Критерий χ2 для таблиц сопряженности. Таблицы 2 х
Критерий χ2 для таблиц сопряженности r х c
Риски и шансы
Исключение грубых погрешностей
Критерии Райта и правило «трех сигм»
Критерий Романовского
Критерий Шарлье
Правило «ящик с усами»
Правило Томпсона (критерий Рошера)
Критерий Диксона (Q-критерий)
Основы планирования эксперимента
Приложения:
Однородность, дисперсия и ошибка среднего
Однородность и коэффициент вариации
Дисперсия смещенная и несмещенная. Стандартная ошибка среднего
Степень(и) свободы. Классы и группировка. Вариационный ряд
Степень(и) свободы. Классы и группировка
Описательная статистика. Интервальный ранжированный частотный ряд
Нормальное распределение
Измерения, погрешности и запись результатов
Округление чисел
Округления чисел в MS Excel
Погрешности прямых и косвенных измерений
Массивы, имена и функций MS Excel. Ошибки формул
Вопросы для самопроверки
Каталог примеров
Предметный указатель
Перечень использованных источников