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

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

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

Наша задача - обработать данные, приведенные в файлах Sil00.xls и Sil1.xls, с помощью этих функций, а также представить результаты эксперимента в графическом виде.

I Запустите Excel, рассмотрите элементы рабочего окна. Откройте рабочие файлы. Скопируйте лист Ошибка из книги Sil00.xls в книгу Sil1.xls перед листом Данные. Сохранить как Фамилия-Sil-1.xls в папке НИТ на рабочем столе (тем самым создаем копию).

Вспомните основные приемы работы с Мастером функций.

Запуск: кнопкой вставки функции fх. (или командой меню Вставка-Функция).

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

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

=> Особенности Office-2007. Для работы с функциями можно использовать вкладку Формулы, где представлена библиотека функций, разбитая на категории. Можно прийти к вставке функций и через значок Автосумма, выбрав там команду Другие функции. Еще один способ - значок fx, рядом со строкой формул.

I Запустите Мастер функций. Перейдите на категорию статистических функций, и просмотрите их список. Найдите функции СЧЕТ, разберитесь, используя подсказку, чем отличаются разные варианты этой функции.

Для получения количества числовых ячеек в интервалах или массивах ячеек используется функция СЧЁТ. Использовать эту функцию удобно, особенно при большом числе данных, при их отбраковке.

Синтаксис: СЧЁТ(значение1; значение2; ...) где
Значение1, значение2, ... - это числа, адреса ячеек и диапазонов (до 30 аргументов). Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

Функцию СЧЁТ можно вызвать через список функций значка АВТОСУММА (команда Число).

I Лист Ошибка. В ячейке B14 введите формулу для подсчета числа экспериментальных точек. Проверьте работу формулы, удаляя/возвращая одно из данных (кнопка Отменить).

Функция СРЗНАЧ возвращает среднее арифметическое аргументов.

Синтаксис СРЗНАЧ(значение1; значение2; ...), где
- значение1, значение2, ... - это числа, адреса ячеек и диапазонов (до 30 аргументов), для которых вычисляется среднее. Пустые ячейки, логические значения, тексты и значения ошибок игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

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

Синтаксис: УРЕЗСРЕДНЕЕ(массив;доля), где
- массив - это массив или интервал усредняемых значений. - доля - это доля точек данных, исключаемых из вычислений. Например, если доля = 0,2, то из множества данных, содержащих 25 точек исключаются 4 точки (25*0,2= 5, но из соображений симметрии функция исключит 2 точки с наибольшими значениями и 2 точки с наименьшими значениями). Если доля < 0 или доля > 1, то функция возвращает ошибку #ЧИСЛО!.

I Лист Ошибка. В ячейках B15 и B16 введите формулу для среднего и урезанного среднего (доля 20%) значения. Проверьте работу формулы, удаляя/возвращая одно из данных (кнопка Отменить)

Разброс экспериментальных данных, оценивается дисперсией по выборке, которое определяется функцией ДИСП или стандартным отклонением (функция СТАНДОТКЛОН)

Синтаксис: ДИСП(число1;число2; ...), или СТАНДОТКЛОН(число1; число2; ...)
где число1, число2, ... - это до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются.

ДИСП и СТАНДОТКЛОН используют формулы:

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

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

I Лист Ошибка. В ячейках B17, B18 введите формулы для дисперсии и стандартного отклонения выборки, в ячейках B19, B20 - формулы для дисперсии и стандартного отклонения среднего значения. Сохраните файл.

Для определения доверительного интервала, в который с заданной надежностью попадает измеряемая величина, используется критерий Стюдента t(a;f). Он был предложен в 1908 году англиским математиком и химиком Госсетом, который опубликовал их в работе под псевдонимом Student (студент). Для нахождения критерия Стюдента используется функция СТЬЮДРАСПОБР(a;f) .

Синтаксис: СТЬЮДРАСПОБР(альфа;степень_свободы), где
- альфа - это вероятность ошибки, используемая для вычисления уровня надежности (Р). Уровень надежности определяется как Р = 1 - a, или, другими словами, a равная 0,05 означает 95% уровень надежности.
- степень_свободы - это число степеней свободы, характеризующее распределение. Для среднего значения степень свободы f = n-1.

Функция СТЬЮДРАСПОБР использует метод итераций для вычисления результата. Если СТЬЮДРАСПОБР не сходится после 100 итераций, то функция возвращает значение ошибки #Н/Д (не достижимо).

Доверительный интервал среднего рассчитывается по формуле ДИ = ± s_ср*t(a;f) где s_ср - стандартное отклонение среднего.
Окончательно получаем X = X_ср ± s_ср*t(a;f)

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

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

Коэффициенты линейной регрессии Y = mX+b и их ошибки вычисляются по довольно сложным формулам (рассмотренным на прошлом занятии). Excel предлагает ряд функций, облегчающих эти расчеты.

Коэффициент m (наклон) линии, описываемой уравнением Y = mX+b может быть получен при помощи статистической функции НАКЛОН.

Коэффициент b, показывающий отрезок, отсекаемый линией Y = mX+b на оси Y, может быть определен с помощью функции ОТРЕЗОК.

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

Синтаксис:
НАКЛОН(известные_значения_y; известные_значения_x)
ОТРЕЗОК (известные_значения_y; известные_значения_x)
КВПИРСОН (известные_значения_y; известные_значения_x)

I Лист МНК. Введите формулы для рассчета коэффициентов линейной регрессиии и коэффициента корреляции R² для зависимости Y(dG) от Х(T). Сравните с данными полученными ранее, оцените приемлемость линейной модели.

Наиболее полную статистическую информацию по линейной регрессии выдает функция ЛИНЕЙН. Кроме того, эта функция позволяет обрабатывать данные по линейным функциям нескольких переменных (многофакторный эксперимент). Поскольку функция возвращает не одно значение, а несколько, массив значений, она относится к формулам массива. Ввод этих формул имеет ряд особенностей:

• Начинать ввод следует с выделения массива ячеек, куда будут выводиться данные. Для функции ЛИНЕЙН это прямоугольник N*5, где N - число коэффициентов в уравнении.
• Заканчивать ввод функции следует сочетанием клавиш Ctrl-Shift-Enter (или, удерживая нажатыми клавиши Ctrl-Shift щелкнуть мышкой Ok).

Синтаксис: ЛИНЕЙН(извест_знач_y; извест_знач_x; конст; статистика)
Константа определяет, нужно ли рассчитывать отрезок b, отсекаемый линией на оси OY. Если из теоретических представлений известно, что линия проходит через начало координат и отрезок b, отсекаемый ею на оси OY равен 0, то вводится значение константы ЛОЖЬ (или 0). В противном случае вводится значение ИСТИНА (1), и отрезок b будет вычислен из массива данных.
Еще одно логическое значение Стат указывает, требуется ли вернуть полную статистику по регрессии (ИСТИНА) или нет (ЛОЖЬ) В результате правильного ввода функции в выделенных ячейках получаем таблицу данных (пример в таблице 1):

В ячейке Е7 находится коэффициент m (наклон) линии, в ячейке F7 - коэффициент b (отрезок);
В ячейках E8 и F8 приведены стандартные значения ошибок для коэффициентов m и b. (s_m, s_b) Стандартные значения ошибок могут быть использованы для оценки значимости коэффициентов регрессии.
В ячейке Е9 - квадрат коэффициента корреляции Пирсона (R²) В ячейке F9 - cтандартная ошибка для оценки Y (s_y).
В ячейке Е10 выводится F-статистика, используемая для определения адекватности модели. В ячейке F10 представлено число степеней свободы (f). Число степеней свободы используются для нахождения F-критических значений в статистической таблице. Оно находится по формуле f = n - (k + 1), где k - это число независимых переменных, а n - число точек данных. В нашем случае f = 10 - (1 + 1) = 8
В ячейках Е11 и F11 представлены, соответственно, регрессионная сумма квадратов и остаточная сумма квадратов.

I Еще раз прочитайте как вводятся формулы массива.
Лист МНК. Выделите нужное число ячеек и введите формулу массива для получения линейной статистики зависимости Y(dG) от Х(T). Сравните с данными полученными ранее. Введите подсказки к ячейкам полученных данных, используя вставку Примечаний.

Для определения уровня надежности модели нужно сравнить значения специального F-критерия F(a;f1;f2) и F-статистики, возвращаемой функцией ЛИНЕЙН. Для определения F(a;f1;f2) используется функция FРАСПОБР(a;f1; f2), где
- a вероятность ошибочного вывода (обычно применяется значение равное 0,05), а f1 и f2 - число степеней свободы, причем f1 = k = 1, где k - число независимых переменных в уравнении модели, а f2 равно числу степеней свободы, данное в таблице результатов функции ЛИНЕЙН.
Если наблюдаемое F >> F(a;f1;f2), то полученное регрессионное уравнение адекватно.

I Лист МНК. Найдите F-критерий и определите адекватность линейной модели.

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

Построение графика.

1. Выделите массивы ячеек, содержащих значения Х и Y. (Несмежные области выделяют, используя клавишу Ctrl).
2. Щелкните на значке Мастера диаграмм панели инструментов. На первом этапе его работы определите тип диаграммы (Точечная) и ее вид только точки, точки и линии, только линии и т.п.
3. На следующем этапе можно примерно увидеть будущий график. Если нужно, то на вкладке Ряд можно удалить или добавить ряды данных зависящих от Х.
4. Следующий этап позволяет ввести различные надписи, определить необходимость и расположение легенды, линий сетки.
5. На последнем этапе определяем, будет ли для графика создан отдельный Лист и нажимаем кнопку Готово

=> Особенности Office-2007. - В Excel-2007 нет мастера диаграмм. Первый этап выполняется с вкладки Вставка. При этом на листе сразу появляется выбранный тип диаграммы.

I Лист МНК. Постройте точечный график зависимости Y(dG) от Х(T), поместив его на лист Результат.

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

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

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

=> Особенности Office-2007. При щелчке на диаграмме появляются три дополнительные вкладки Ленты, предназначенные для работы с диаграммами. На вкладке Конструктор можно изменить тип диаграммы, определить исходные данные, если они не были выделены предварительно, выбрать макет, стиль и размещение диаграммы. Вкладка Макет позволяет проводить более детальную работу с элементами диаграммы (Легенда, Название, Подписи, Сетка, Оси и т.д.) Вкладка Формат предоставляет дополнительные возможности форматирования объектов диаграммы.

I Лист Результат. Отредактируйте график, добавьте название, установите оптимальные шкалы и форматы чисел, выделите маркеры экспериментальных точек синим цветом, используйте градиентную заливку. Сохраните файл.

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

На вкладках этой команды выбираем тип тренда (линейный) и его параметры. В частности, отмечаем Выводить уравнение, Выводить коэффициент R^2.

Открыв через контекстное меню точки окно "Параметры рядов данных", на график также можно нанести планки погрешностей, используя значение ошибок в определении величины Y. В качестве величины ошибки может быть использовано значение ДИ рассчитанное для одной из точек или cтандартная ошибка для оценки Y (s_y), найденая функцией ЛИНЕЙН.

=> Особенности Office-2007. Линии тренда и планки погрешностей задаются на вкладке Макет, в разделе Анализ.

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

Дополнительный материал:
Обработка экспериментальных данных в Excel.

Задать вопрос
Наверх

< И К Т   |  Карта сайта   |  Главная страница >