ПРИМЕНЕНИЕ ЭЛЕКТРОННЫХ ТАБЛИЦ ДЛЯ

ГЕНЕРИРОВАНИЯ СТАТИСТИЧЕСКИХ ДАННЫХ

ПРИ ПРОВЕДЕНИИ ЗАНЯТИЙ ПО ДИСЦИПЛИНЕ

«МАТЕМАТИЧЕСКАЯ СТАТИСТИКА»

Информационные технологии на современном этапе развития общества

проникли во все сферы человеческой деятельности. Это касается, в том чис-

ле, и системы образования. Применение вычислительной техники и соответ-

ствующего программного обеспечения во всех областях учебной и препода-

вательской деятельности является одной из приоритетных задач современно-

го образования. Все сказанное относится практически ко всем аспектам про-

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

при подготовке к занятию, в ходе его проведения и при подведении его ито-

гов. Точно также и учащиеся имеют возможность применять вычислитель-

ную технику и соответствующее программное обеспечение перед занятием,

на занятии и после занятия.

Причем применение компьютерных технологий в образовании много-

гранно и практически неисчерпаемо. Речь идет, прежде всего, о доступе к

информационным базам данных – текстовым, видео- и аудиоматериалам; ис-

пользовании

всевозможных

обучающих

программ,

способных

выполнять

функции информирования, тренировки, контроля; применении различных

вычислительных процедур при решении математических, естественнонауч-

ных, технических и экономических задач.

Среди всех программных продуктов, используемых в современном обра-

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

кеты приложений. Как правило, они содержат следующий набор компонен-

тов:

•

текстовый процессор – средство для создания документов, содержащих

текст, таблицы, графические объекты и т.д.;

•

электронные таблицы – средство для проведения связанных табличных

вычислений;

•

программа подготовки презентаций – средство для создания красочных

электронных презентаций;

•

система управления базами данных – средство для создания и управле-

ния базами данных;

•

графическая редактор – средство для создания и редактирования графи-

ческих объектов.

Генерирование статистических данных для интервального ряда

распределения

В данной статье речь пойдет о применении электронных таблиц для со-

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

изучении данной дисциплины студенты всех специальностей изучают тему

«Математическая статистика», где выполняют расчетную работу «Критерий

согласия Пирсона». В ходе ее выполнения они обрабатывают большой объем

данных – несколько сотен чисел, распределенных по нормальному закону.

Для создания индивидуальных заданий к данному занятию можно использо-

вать (и уже используются) электронные таблицы.

В электронных таблицах MS Excel из пакета Microsoft Office есть функ-

ция «СЛЧИС» (категория «Математические»). При обращении к этой функ-

ции программа выдает случайное значение из промежутка [0, 1). Распределе-

ние соответствующей случайной величины является равномерным. В то же

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

тии, должно быть нормальным. Есть несколько возможностей преобразова-

ния равномерного распределения в нормальное. В данной работе для этого

использовалась центральная предельная теорема (Ляпунова): всегда, когда

случайная величина образуется в результате сложения большого числа неза-

висимых случайных величин, дисперсии которых малы по сравнению с дис-

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

практически нормальным законом.

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

ствует 250 нормально распределенных случайных величин, использовалась

сумма двадцати слагаемых (распределенных равномерно). Практика показы-

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

синтезируемого нормального распределения – математическое ожидание и

дисперсия (или среднеквадратическое отклонение). Они определяются той

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

качестве рассматриваемой величины выступает рост человека (мужчины), то

математическое ожидание должно быть примерно равно 170 см, а средне-

квадратическое отклонение 8 см (тогда дисперсия 64 см

2

); а если речь идет о

массе таблеток, изготовленных на фармацевтической фабрике, тогда матема-

тическое ожидание можно положить равным 0,5 г, а среднеквадратическое

отклонение 0,03 г и т.д.

Математическое ожидание и дисперсия случайной величины X, распре-

деленной

равномерно

от

0

до

1,

будут

равны

2

1

2

0

1

2

)

(

=

+

=

+

=

a

b

X

M

;

12

1

12

)

(

)

(

2

=

−

=

a

b

X

D

.

Тогда у случайной величины, равной сумме 20 таких

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

ния

10

20

2

1

)

(

20

)

(

=



=

=

X

M

Y

M

;

3

5

12

1

20

)

(

20

)

(

=



=



=

Y

D

Y

D

. Поэтому при

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

если в задании речь идёт о росте человека, то получившуюся случайную ве-

личину надо умножить на

2

,

6

4

,

38

3

5

:

64



=

(при этом дисперсия станет

равной 64) и прибавить

108

4

,

38

10

170



−

(тогда математическое ожидание

будет равно 170).

На Рис. 1 показан фрагмент листа MS Excel, где генерируются данные в

соответствии с рассмотренным выше примером. В строке формул после зна-

ка «=» записано выражение, которое находится в ячейке B1 (там ровно два-

дцать слагаемых СЛЧИС() – значений равномерно распределенной случай-

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

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

данном случае – десять строк и 25 столбцов от A до Y). Таким образом, со-

здается массив данных из 250 нормально распределенных случайных значе-

ний.

Рис. 1. Фрагмент листа MS Excel

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

виде таблицы в текстовый редактор (например, MS Word) и преобразуются в

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

ку:

Вариант 1. При изучении случайной величины X были получены следующие результаты:

178; 169; 170; 161; 172; 170; 169; 155; 171; 173; 171; 173; 179; 168; 180; 188; 173; 175; 176; 172; 156;

175; 172; 162; 170; 164; 165; 166; 173; 179; 169; 176; 166; 157; 182; 159; 157; 183; 177; 179; 166; 165;

160; 170; 176; 163; 163; 158; 163; 168; 160; 169; 175; 173; 161; 153; 173; 180; 156; 166; 178; 171; 186;

163; 168; 174; 179; 168; 165; 175; 166; 172; 158; 162; 185; 162; 183; 164; 179; 173; 155; 160; 176; 170;

169; 176; 174; 174; 158; 158; 172; 165; 178; 177; 164; 175; 172; 174; 168; 171; 168; 171; 175; 168; 175;

171; 170; 160; 177; 171; 166; 164; 149; 156; 154; 160; 177; 165; 170; 169; 158; 166; 158; 174; 157; 160;

163; 184; 165; 174; 165; 163; 149; 170; 178; 176; 168; 169; 150; 169; 175; 162; 177; 176; 163; 156; 162;

170; 171; 160; 175; 158; 170; 173; 174; 160; 182; 168; 178; 168; 164; 174; 188; 166; 165; 173; 173; 162;

173; 172; 158; 164; 181; 160; 175; 170; 178; 181; 168; 162; 170; 165; 186; 169; 173; 175; 179; 158; 171;

158; 156; 176; 169; 173; 172; 173; 174; 180; 162; 190; 180; 182; 173; 179; 184; 172; 172; 172; 175; 165;

166; 167; 157; 179; 183; 176; 166; 169; 179; 168; 171; 184; 172; 177; 192; 163; 166; 170; 178; 165; 169;

178; 176; 176; 172; 172; 178; 179; 174; 189; 177; 173; 170; 149; 167; 172; 163; 171; 173; 165.

По выборке объёма n = 250 составьте интервальный ряд распределения. Количество ин-

тервалов найдите по формуле Стерджеса, ширину интервала округлите до целых (в боль-

шую сторону). Постройте гистограмму относительных частот и кумуляту. Найдите сред-

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

При доверительной вероятности



= 0,98 определите доверительный интервал для гене-

ральной средней.

При уровне значимости α = 0,1 проверьте по критерию Пирсона гипотезу о нормальном

распределении рассматриваемой величины.

Таким образом, для каждого курсанта формируется свой собственный

уникальный набор числовых данных и всю обработку этого массива он вы-

полняет самостоятельно.

Но в электронных таблицах можно не только генерировать случайные

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

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

грамму распределения, вычислять все необходимые параметры (среднее зна-

чение, выборочную и исправленную дисперсию, соответствующие средне-

квадратические отклонения, выборочные моду и медиану). Причем среднее

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

так и по исходным. А также определять доверительный интервал для средне-

го значения с заданной доверительной вероятностью. И проверять гипотезу о

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

Пирсона). Все это позволяет преподавателю быстро проверять все расчеты,

выполненные курсантами, и в случае необходимости определить, где именно

совершена ошибка в вычислениях. Например, для приведенного выше вари-

анта задания расчеты представлены в следующей таблице:

Вариант 1

интервал

m



(x

1

)



(x

2

)

P

m'





min

149

148

153

5

-0,5

-0,48272

0,01728

4,319992

0,10704

max

192

153

158

22

-0,48272

-0,43167

0,051051

12,76279

6,68553

h

5

158

163

29

-0,43167

-0,30598

0,125684

31,421

0,18654

x cp

169,904

169,34

163

168

43

-0,30598

-0,09408

0,211905

52,97614

1,87865

D

63,9711

68,0544

168

173

69

-0,09408

0,150654

0,244735

61,18364

0,99856



7,99819

8,24951

173

178

49

0,150654

0,344286

0,193631

48,40784

0,00724

мода

170,826

178

183

22

0,344286

0,449223

0,104938

26,23444

0,68347

мед.

170,5

169,884

183

188

8

0,449223

0,488167

0,038944

9,735922

0,2261

t



2,32635

188

193

3

0,488167

0,5

0,011833

2,958231

-



1,17678

1,21376

1

250





18,4753

10,7731

Во второй и третьей колонках найдены числовые характеристики рас-

пределения:

минимальное

и

максимальное

значения,

ширина

интервала,

среднее значение, дисперсия и среднее квадратическое отклонение, мода и

медиана, ширина доверительного интервала при соответствующей вероятно-

сти. Причем во второй колонке соответствующие величины найдены по ис-

ходным данным, а в третьей колонке – те же величины, рассчитанные по

сгруппированным данным. Исходя из найденного минимального значения и

рассчитанной ширины интервала, определяются границы интервалов (соот-

ветствующие графы в таблице). В соседней колонке найдены абсолютные ча-

стоты (m); они определяются функцией ЧАСТОТА (по заданному массиву

исходных данных и известным границам интервалов). В остальной части

таблицы проведены расчеты по проверке гипотезы о нормальном распреде-

лении изучаемой случайной величины.

Причем проверять работу курсантов следует с самого начала – с нахож-

дения наименьшего и наибольшего значения среди всех данных. Ведь имен-

но по ним строиться начальная таблица и происходит дальнейшее ее запол-

нение. Если эти значения будут найдены неправильно, то в дальнейшем при-

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

ся, что все курсанты правильно нашли эти величины.

На рис. 2 приведена гистограмма (столбиковая диаграмма) абсолютных

частот найденного распределения. Аналогичную диаграмму строят в своих

работах и курсанты.

Рис. 2. Гистограмма частот

Генерирование статистических данных для дискретного ряда

распределения

На следующем занятии решалась аналогичная проблема. Но речь шла не

о нормальном, а о биномиальном распределении. Если нормальное распреде-

ление описывает непрерывную случайную величину (принимающую, вообще

говоря, значения от -∞ до +∞), биномиальное распределение говорит о дис-

кретной случайной величине, которая принимает только целые значения – от

0 до n (числа испытаний). Для генерирования массива данных, распределен-

ных по этому закону, используется уже принципиально другой подход. По-

кажем его на конкретном примере. Биномиальное распределение задается

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

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

на примет значение m (0 ≤ m ≤ n) находится по формуле Бернулли:

m

n

q

m

p

m

n

C

m

n

P

−

=

)

(

,

где n – количество испытаний, m – число произошедших событий, p – веро-

ятность наступления события в одном испытании, q– вероятность противо-

положного события (q = 1 – p),

)!

(

!

!

m

n

m

n

m

n

C

−

=

– биномиальный коэффици-

ент. Пусть, например, проводятся 6 испытаний, а вероятность наступления

интересующего нас события в каждом из них постоянна и равна 0,7. Тогда

придавая величине m значения от 0 до 6, можно по приведенной выше фор-

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

случайной величине:

X

0

1

2

3

4

5

6

P

0,000729

0,010206

0,059535

0,18522

0,324135

0,302526

0,117649

F(X)

0,000729

0,010935

0,07047

0,25569

0,579825

0,882351

1

В первой строке приведены возможные значения рассматриваемой случай-

ной величины, во второй – соответствующие вероятности, а в третьей строке

– значения функции распределения. Именно этими последними значениями

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

ленной по биномиальному закону.

Функция MS Excel (СЛЧИС) задается в нужном количестве ячеек элек-

тронных таблиц. В каждой из них появляется некоторое случайное число от 0

до 1. А далее производится анализ этих чисел и создание нужной случайной

величины: если значение числа меньше 0,000729, то записывается 0, если

значение принадлежит промежутку от 0,000729 до 0,010935, то это будет со-

ответствовать 1, если промежуток будет от 0,010935 до 0,07047, то появится

число 2, если промежуток от 0,07047 до 0,25569, то ставим число 3, если от

0,25569 до 0,579825, то это соответствует значению 4, если интервал от

0,579825 до 0,882351, то будет число 5 и, наконец, при значении, большем

0,882351 окажется число 6. Таким образом, создается набор значений от 0 до

6, имеющих псевдобиномиальное расширение.

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

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

для каждого варианта можно задавать свои значения параметров распределе-

ния – вероятности наступления события в одном испытании (p) и количество

испытаний (n). При этом получаются наборы данных, существенно отлича-

ющиеся друг от друга.

Например, с помощью синтезированных данных создается такая подго-

товленная карточка-задание:

Вариант 1. Курсанты первого курса накануне проводили стрельбы. Они делали по 6 вы-

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

сантом):

4; 5; 3; 2; 3; 5; 4; 4; 1; 6; 5; 3; 3; 5; 3; 6; 4; 4; 2; 6; 3; 5; 3; 4; 6; 3; 6; 6; 6; 4; 6; 4; 4; 5; 4; 3; 1; 3; 3; 2; 4; 5;

5; 5; 4; 6; 5; 4; 4; 4; 6; 3; 5; 5; 3; 4; 4; 5; 4; 5; 4; 4; 4; 4; 5; 3; 3; 2; 4; 3; 5; 4; 4; 2; 3; 2; 6; 4; 5; 3; 3; 3; 3; 5;

5; 5; 5; 5; 3; 4; 6; 5; 3; 5; 6; 5; 4; 4; 4; 6; 4; 4; 4; 5; 4; 5; 4; 3; 2; 4; 3; 6; 5; 5; 3; 2; 6; 5; 5; 5; 4; 5; 4; 4; 6; 2;

5; 5; 3; 6; 4; 6; 3; 4; 1; 3; 4; 4; 3; 4; 5; 5; 4; 4; 5; 2; 4; 5; 2; 6; 5; 5; 5; 5; 4; 5; 5; 5; 4; 6; 5; 5; 4; 4; 5; 2; 5; 5;

4; 5; 4; 4; 5; 4; 5; 6; 5; 3; 4; 4; 3; 2; 6; 6; 3; 5; 4; 5; 5; 6; 6; 4; 4; 5; 4; 6; 4; 6; 4; 6.

По выборке объёма n = 200 составьте дискретный ряд распределения количества попада-

ний. Постройте полигон частот. Найдите среднее значение, выборочные дисперсию, сред-

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



= 0,95

определите доверительный интервал для генеральной средней.

При уровне значимости α = 0,1 проверьте по критерию Пирсона гипотезу о биномиальном

распределении рассматриваемой величины.

Также как и в предыдущем случае, одновременно с генерированием дан-

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

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

вычисление всех необходимых параметров (среднее значение, выборочную и

исправленную дисперсию, соответствующие среднеквадратические отклоне-

ния, выборочные моду и медиану). Правда, для дискретных случайных вели-

чин средние значение и дисперсии, найденные по сгруппированным данным

и по исходным, совпадают. Поэтому все вычисленные параметры помещают-

ся в одной колонке (а не в двух, как ранее). А также определяется довери-

тельный интервал для среднего значения с заданной доверительной вероят-

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

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

выше варианта задания расчеты представлены в следующей таблице:

Вариант 1

x

m

xm

P

m'





x ср

D

0

0

0

0,0006263

0,125252

-

4,245

1,374975

1

3

3

0,0090888

1,817762

-



мода

2

13

26

0,0549601

10,99202

0,726244

1,172593

4

3

33

99

0,1772503

35,45006

0,169331

медиана

4

63

252

0,3215502

64,31004

0,026686

4

5

59

295

0,3111067

62,22134

0,166776

t



6

29

174

0,1254177

25,08353

0,611506

1,959964

0,16251



200

849

1

200

1,700543

Одновременно строится полигон частот (рис. 3).

0

10

20

30

40

50

60

70

0

1

2

3

4

5

6

7

8

Рис. 3. Полигон частот

Сравнивая полученные курсантом результаты расчетов с таблицей и

графиком легко проверить его решение, а в случае необходимости предло-

жить ему провести поправочные расчеты.

Еще одним статистическим заданием является расчет не сгруппирован-

ных данных. Он проводится, когда объем исследуемых величин невелик и

сводить их в статистическую таблицу нецелесообразно или невозможно. В

этом случае вычисление всех параметров ведется непосредственно по основ-

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

становке:

При исследовании курсантов отделения были собраны данные об их IQ.

Вычислите среднее, исправленную дисперсию и среднеквадратическое от-

клонение. Найдите размах варьирования, среднее абсолютное (линейное) от-

клонение, коэффициент вариации, линейный коэффициент вариации, коэф-

фициент осцилляции.

Предполагая, что данная случайная величина имеет нормальное распре-

деление, определите доверительный интервал для генеральной средней с до-

верительной вероятностью



.

Вариант

Значения IQ



1

44

53

39

46

42

43

40

44

0,9

2

86

59

70

66

55

67

49

71

78

0,95

3

86

77

110

77

103

90

84

80

79

97

0,99

4

93

82

79

62

70

69

72

0,999

В этом задании не надо проверять закон распределения данной случай-

ной величины. Но зато необходимо найти как абсолютные, так и некоторые

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

создается и файл с ответами на данное задание:

x

ср

S

2

S

R

d

V

V

R

V

d

t



43,875

18,696

4,324

14

2,875

0,099

0,319

0,066

1,895

2,896

66,778

129,944

11,399

37

8,469

0,171

0,554

0,127

2,306

8,762

88,300

133,344

11,547

33

9,360

0,131

0,374

0,106

3,250

11,867

75,286

104,571

10,226

31

8,041

0,136

0,412

0,107

5,959

23,031

Таким образом, электронные таблицы позволяют для каждого курсанта

создать индивидуальное задание, а преподавателю быстро проверить его вы-

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

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

1.

Нуриахметов Р.Р. Применение электронных таблиц для обучения осно-

вам статистики студентов естественных специальностей. Открытое и

дистанционное образование. 2013 г. № 1 (49). С. 60-63,

2.

Каймин В.А. Информатика: Учебник. 6-ое изд. Бакалавриат. – М.: Ин-

фра-М. 2015.

3.

Годин А.М. Статистика: Учебник. 11-е изд., перераб. и испр.– М.: Даш-

ков и К

0

.

2014, 412 с.