Вопрос 27

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

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

Зависимости между параметрами некоторого объекта, процесса, явления могут быть выражены с помощью математических формул. Но в некоторых случаях коэффициенты в этих формулах могут быть получены в результате статистической обработки экспериментальных данных. Статистика — это наука о сборе, измерении и анализе больших массивов количественных данных. Статистические данные носят приближенный, усредненный характер, получаются путем многократных измерений. Математический аппарат статистики разрабатывает раздел науки под названием «Математическая статистика». Статистические данные используются, в частности, для получения упрощенного математического описания сложной или неизвестной зависимости между данными некоторой системы (регрессионные модели). Статистические функции электронных таблиц позволяют обрабатывать статистические данные, например, вычислять, среднее арифметическое числовых данных (СРЗНАЧ), среднее геометрическое положительных числовых данных, минимальное и максимальное значения из набора данных, выполнять подсчеты (СЧЁТ, СЧЁТЕСЛИ, СЧЁТЗ, СЧИТАТЬПУСТОТЫ и т. д.).

Статистический анализ данных широко используется:

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

в социологии и психологии для обработки и анализа результатов опросов, тестирования, анкетирования;

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

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

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

На основании физического смысла статистических данных принять вид аналитических зависимостей. Это может быть, например, полином второй степени у = ах2 + bx + с, линейная зависимость у = ах + b и т. п. Во всех формулах x — аргумент y — значение функции, а, b, с — параметры функций.

С помощью метода наименьших квадратов (МНК) по имеющимся статистическим данным найти значения величин а, Ь, с, определяющих конкретный вид принятой зависимости.

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

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

Алгоритм построения регрессионной модели:

Выделить обе колонки исходных данных (наборы X и Y).

Вызвать Мастер диаграмм.

Построить Точечную диаграмму.

Для полученной диаграммы выбрать меню Диаграмма - Добавить линию тренда.

Тренд — это график регрессионной модели (от англ. trend — тенденция).

Выбрать, например, линейную линию тренда. Перейти к вкладке Параметры.

В открывшемся диалоговом окне параметров выбрать опции: показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2).

На экране появится линия тренда, уравнение с числовыми параметрами и коэффициент R2.

Проанализировать линию тренда (как часто ее точки совпадают с точками точечной диаграммы) и значение R2 и повторить построение регрессионной модели для других видов аналитических зависимостей.

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

Примеры построения регрессионных моделей

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

Определить зависимость температуры воздуха от широты города.

Город	Широта, град.с.ш.	Температура
Воронеж	51,5
Краснодар	45,0
Липецк	52,6
Новороссийск	44,8
Ростов на Дону	47,3
Рязань	54,5
Северодвинск	64,8
Череповец	59,4
Ярославль	57,7

 

 

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

Линейная регрессионная модель:

Квадратичная регрессионная модель:

 

Регрессионный анализ показал, что зависимость средней температуры от широты города является квадратичной, так как R2 = 0,98, что ближе к 1, чем R2 для линейной зависимости. Используя зависимость у = 0,0367х2 - 4,9784х + + 173,59 можно восстановить значение средней температуры для города, находящегося на 50-й широте (16,42 °С), и высказать прогноз средней температуры для города, находящегося на 70-й (4,9 °С) или 40-й (33,2 °С) широте. Если представится возможность получить данные о температуре на этих широтах, то можно проверить точность модели и убедиться в достоверности (или ошибочности) данных, которые получены при ее использовании. Во втором случае новый набор статистических данных позволит уточнить модель или ее коэффициенты и предоставит возможность для более точного прогноза температуры.

Экологический пример. Медицинская статистика

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

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

С, мг/куб.м	Р, бол./тыс
2,0
2,5
2,9
3,2
3,6
3,9
4,2
4,6
5,0

 

 

Линейная зависимость P(C):

 

Квадратичная зависимость Р(С) (полиномная 2-ой степени):

 

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

Социальные опросы

Опросы разных категорий населения страны проводят для того, чтобы выявить, например, проблемы трудоустройства, места размещения магазинов, школ и детских садов, мнения избирателей перед выборами и т. п. Анализ результатов опросов позволяет принять правильные решения по тому или иному вопросу. Выбор будущей профессии является одним из важных решений, принимаемых человеком в своей жизни. Поэтому ответы на вопрос: «Какой фактор является для вас наиболее значимым при выборе профессии, и, соответственно, вуза?» (Журнал «Обучение и карьера», 2006, № 4) представляют интерес для многих специалистов.

Фактор	Процент выбравших данный фактор
Интерес к определенной деятельности	54,63
Престиж и доходность выбираемой специальности	16,49
Возможность последующего трудоустройства	12,37
Другое	7,21
Предназначение	5,25
Возможности родителей	3,09
Советы родителей и друзей	1,06

 

 

 

Естественно-научный эксперимент

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

Н(м)	t (с) из опыта	t (с) расчетное	Среднее абсолютных значении отклонений точек данных от среднего — по функции СРОТКЛ
	0,8	0,78	0,01
	1,1	1,11	0,005
	1,3	1,35	0,025
	1,5	1,56	0,03
	1,7	1,75	0,025
	1,9	1,92	0,01
	2,0	2,07	0,035
	2,2	2,21	0,005
	2,3	2,35	0,025
	2,4	2,47	0,035

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

Для вычисления t расчетное в MS Excel была использована функция извлечения квадратного корня из числа — КОРЕНЬ (Х):

=КОРЕНЬ((2*А2/9,81)), где А2 — адрес ячейки, содержащей значение высоты.

Сравнение опытных и расчетных данных таблицы позволяет сделать вывод об адекватности использованной модели зависимости времени падения тела на землю от первоначальной высоты Н без учета сопротивления воздуха. Этот вывод можно сделать, проанализировав отклонение опытных данных от расчетных значений, полученных с помощью встроенной функции «Среднее отклонение» СРОТКЛ, которая возвращает среднее абсолютных значений отклонений точек данных от среднего. СРОТКЛ является мерой разброса множества данных.