Применение пакета анализа MS Excel для обработки  и визуализации результатов измерений физического  эксперимента

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

Для предварительной математической обработки и анализа результатов физических экспериментов используют стандартные пакеты прикладных программ, такие как Microsoft Office Excel с дополнительной надстройкой «Пакет анализа». В сравнении со специализированными статистическими программами (Stastistica, Origin), используемыми чаще всего в научной практике, MS Excel обычно не вызывает сложностей у обучающихся. Если встроенных статистических функций не хватает, есть возможность использовать надстройку «Пакет анализа», который предоставляет следующие возможности:

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

Воспользоваться надстройкой Пакет анализа можно, выполнив следующую команду: Сервис - Анализ данных.

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

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

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

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

(1)

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

(2)

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

(3)

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

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

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

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

Гистограммы, или линейчатые диаграммы – это удобный и понятный способ обработки и отображения результатов измерений физических величин. Область значений измеряемой величины разделяется на несколько интервалов, которые именуются также карманами, где в виде столбцов отражается количество попавших в такой интервал измерений, называемое частотой. Карманы могут быть не равны между собой, но они должны быть распределены по возрастанию границ. MS Excel позволяет оставлять поле Интервал карманов пустым, в таком случае равномерно распределяя карманы в промежутке от минимального до максимального значения вводимых данных. Число карманов при этом будет равно квадратному корню из количества исходных значений.

Пакет анализа MS Excel позволяет выполнять три типа анализа:

1. Вывод графика.
2. Интегральный процент.
3. Парето (отсортированная гистограмма).

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

Рисунок 1. Пример ввода исходных данных для построения гистограммы.

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

Если установить флажок Интегральный процент в заданной таблице появится столбец с накопляемым процентным вкладом каждого кластера. При выборе вида анализа Парето выходная таблиц будет отсортирована по убыванию частоты. Инструмент Гистограмма создает таблицу числовых констант. При необходимости связи с исходными значениями можно использовать формулы с функцией ЧАСТОТА (например, массив данных, массив карманов).

В качестве примера рассмотрено построение выборочного распределения по данным эксперимента определения периода колебаний. Для этого необходимо указать диапазон карманов – граничных значений, при которых данные будут разделены на группы в интервалы от 0 до 1, от 1 до 1,2, от 1,2 до 1,3 и т.д. При этом следует помнить, что в карман включаются значения по правой границе и не включаются значения по левой границе.

Для построения гистограммы следует выполнить следующий алгоритм действий:

1. Создать таблицу в MS Excel на основе данных проводимого физического эксперимента.
2. Выполнить команду Данные – Анализ данных – Гистограмма.
3. Заполнить параметры инструмента Гистограмма в диалоговом окне. Пример ввода данных для построения гистограммы приведен на рисунке 2.

Рисунок 2. Пример ввода данных для построения гистограммы.

4. Осуществить формирование входного интервала на основе диапазона исследуемых данных, в приведенном примере это будут ячейки А2:В15.
5. Заполнить поле Интервал карманов, которое представляет собой границы группировки входных данных, в рассматриваемом примере это ячейки С2:С8. Значения Интервала карманов будут скопированы при построении гистограммы, поэтому для удобства их лучше заполнить числовыми константами, а не формулами. В случае необходимости ввода именно формул, следует использовать абсолютные ссылки, так как в ином случае результаты копирования могут оказаться неверны.
6. Установить Выходной интервал, который является ячейкой для отображения результата, также для вывода гистограммы при необходимости можно использовать новый рабочий лист.
7. Установить флажок Вывод графика для отображения гистограммы. Флажок Интегральный процент используют при вычислении процентов частот с накоплением и для вывода графика интегральных процентов.

Результат использования инструмента Гистограмма отображен на рисунке 3.

Рисунок 3. Результат применения инструмента Гистограмма

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

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

В случае обработки данных случайных выборок первостепенную значимость имеет вычисление их числовых параметров, описывающих тенденции, разброс и изменчивость данных. Для этого используют инструмент Описательная статистика из Пакета анализа, который предоставляет возможность вывода единого статистического отчета по всем характеристикам исследуемых данных. Данный инструмент позволяет создать таблицу параметров описательной статистики для исследуемых данных. В выходном интервале будет отображена таблица, содержащая следующие данные: Среднее, Стандартная ошибка, Медиана, Мода, Стандартное отклонение, Дисперсия выборки, Эксцесс, Асимметричность, Интервал, Минимум, Максимум, Сумма, Счет, Наибольший (k), Наименьший (k) (для любого заданного k) и Уровень надежности (доверительный интервал). На рисунке 4 приведены некоторые из параметров итоговой статистики и формулы для их расчета.

Рисунок 4. Параметры итоговой статистики

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

Для получения статистических данных с помощью инструмента Описательная статистика используется следующий алгоритм:

1. Данные – Анализ данных – Описательная статистика.
2. Входной интервал представляет собой диапазон исследуемых данных. В приведенном примере это ячейки А2:В15, данные выборки расположены по столбцам, соответственно в поле Группирование установлена метка По столбцам. Пример ввода данных в диалоговое окно Описательная статистика отображен на рисунке 5.

Рисунок 5. Пример заполнения диалогового окна инструмента Описательная статистика

3. Выходной интервал является ячейкой, в которой будет отображена итоговая таблица.
4. В поле Уровень надежности можно выбрать необходимый уровень доверительной вероятности. В большинстве случаев данный показатель равен 95%, соответственно уровень значимости 0,05. Уровень надежности х% – граница доверительного интервала для неизвестного математического ожидания с доверительным уровнем х%, доверительный интервал при этом строится как выборочное среднее плюс-минус данное значение.
5. Установив флажок Итоговая таблица, в выходном интервале отобразится совокупность статистических показателей исследуемых данных. Результаты использования инструмента Описательная статистика отображен на рисунке 6.

Рисунок 6. Результат применения инструмента Описательная статистикапри вычислении статистических характеристик данных физического эксперимента

На основе полученных статистических характеристик, рассчитанных с помощью Пакета анализа MS Excel, результат измерения периода колебаний рассмотренного примера будет равен для первой и второй совокупности исследуемых данных соответственно Т1 = 1,326 ± 0,138 и Т2 = 1,369 ± 0,0984 при уровне надежности 95%.

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

Следует обратить внимание, что расчет параметров в режиме Описательная статистика имеет ряд особенностей:

• не требуется предварительное ранжирование исходных данных (при вычислении показателей ранжирование выделяется автоматически;
• появление в ячейке Мода индикатора ошибки #H/Д указывает, что в анализируемых данных нет одинаковых значений признака;
• индикатор ошибки #ДЕЛ/0! в ячейке Эксцесс и/или Асимметричность означает, что в таблице результатов стандартное отклонение является нулевым или заданный исходный диапазон содержит менее четырех элементов данных.

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

Список литературы:

1. Андрусенко Н.Е., Использование стандартных функций Excel для поиска и связи данных в таблице//информатика и образование.-2003.-№11.-с.7-12.
2. Ивинская Н.Л., решение прикладных задач в Excel//информатика и образование.-2003.-№6.-с.62-64.
3. Леонтьева Н. В. Применение ИКТ в эксперименте лабораторного практикума по физике // Молодой ученый. — 2013. — №6. — С. 700-703.
4. Сидоров М.Г., Обработка данных в Excel //информатика и образование. 2000.-№6.-с. 25-36.
5. Симонович С. В., Информатика: Базовый курс: учеб. / под ред. С. В. Симоновича. – СПб.:Питер, 2005. – 640 с.
6. Романова Ю. Д., Информатика и информационные технологии : учеб. пособие / под ред. Романовой Ю. Д. – М.: Изд-во Эксмо, 2005. – 544 с.
7. Рудикова, Л. В. Microsoft Excel для студента. – СПб.: БВХ-Петербург, 2006. – 386 с.
8. Шрамкова, И. Г. Основы компьютерных технологий: сборник лабораторных работ / И. Г. Шрамкова, Ю. Г. Крат. – Хабаровск: Изд-во ДВГУПС, 2010. – 167 с.