ПОСОБИЕ ИНФОРМАТИКА

138

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

Опции «Чередующиеся столбцы» и «Чередующиеся строки» контекстной вкладки «Конструктор» группы «Параметры стилей сводной таблицы» позволяют улучшить качество визуализации данных таблицы.

12.1.2 Анализ изменения во времени и взаимного влияния показателей правовой статистики с помощью «MS Excel»

На рабочий лист Excel в диапазон А1:C11 перенести данные о динамике убийств и краж в США в период с1991 по 2000 год, как показано в Таблице 12.1.

Таблица 12.1 Динамика убийств и краж в США с 1991 по 2000 год

	A	B	C
1	Год	Кражи	Убийства
2	31.12.1991	3 157 200	24 700
3	31.12.1992	2 979 900	23 760
4	31.12.1993	2 834 800	24 530
5	31.12.1994	2 712 800	23 330
6	31.12.1995	2 593 800	21 610
7	31.12.1996	2 506 400	19 650
8	31.12.1997	2 461 100	18 210
9	31.12.1998	2 329 950	16 914
10	31.12.1999	2 100 739	15 522
11	31.12.2000	2 049 946	15 517

Число краж – случайная величина X. Число убийств – случайная величина Y.

А) Построение графиков – временных рядов.

Выделить диапазон А1:С11. Выбрать «Вставка», прямоугольник «Диаграммы», «График» (выбрать первый вариант графика). На экране появилась координатная плоскость, на которой изображены два временных ряда: «Убийства» и «Кражи».

139

На вкладке «Работа с диаграммами» выбрать «Конструктор», «Переместить диаграмму», «на отдельном листе: График», «ОК».

На вкладке «Работа с диаграммами» выбрать «Макет», «Сетка», «Вертикальные линии сетки по основной оси», «Основные линии сетки».

На вкладке «Макет» выбрать «Оси», «Основная вертикальная ось», «Дополнительные параметры основной вертикальной оси». В появившемся диалоговом окне «Формат оси» выбрать «Параметры оси», «минимальное значение», «фиксированное», ввести 2000000, «максимальное значение», «фиксированное», ввести 3200000, «Закрыть».

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

Для построения дополнительной вертикальной оси на вкладке «Формат» в прямоугольнике «Текущий фрагмент», в верхней строке прямоугольника, выбрать «Ряд «Убийства». Затем выбрать вторую строку прямоугольника: «Формат выделенного». В появившемся диалоговом окне «Формат ряда данных» выбрать «Параметры ряда», «Построить ряд», «По вспомогательной оси», «Закрыть».

На вкладке «Макет» выбрать «Оси», «Вспомогательная вертикальная ось», «Дополнительные параметры вспомогательной вертикальной оси». В появившемся диалоговом окне«Формат оси» выбрать «Параметры оси», «минимальное значение», «фиксированное», ввести 15000, «максимальное значение», «фиксированное», ввести 25000, «Закрыть».

На вкладке «Макет» выбрать «Оси», «Основная горизонтальная ось», «Дополнительные параметры основной горизонтальной оси». В появившемся диалоговом окне«Формат оси» выбрать «Параметры оси», «Положение оси», «по делениям», «Закрыть».

На вкладке «Макет» выбрать «Оси», «Вспомогательная горизонтальная ось», «Дополнительные параметры вспомогательной горизонтальной оси». В появившемся диалоговом окне«Формат оси» выбрать «Параметры оси», «Положение оси», «по делениям», «Закрыть».

На вкладке «Макет» в прямоугольнике «Подписи» выбрать «Название диаграммы», «Над диаграммой». Присвоить построенному графику название «График краж и убийств, 1991 - 2000 годы, США».

На вкладке «Макет» в прямоугольнике «Подписи» выбрать «Названия осей». Основной горизонтальной оси присвоить название«Год», основной вертикальной оси присвоить название «Кражи», вспомогательной вертикальной оси присвоить название«Убийства». Для вертикальных осей использовать опцию«Повёрнутое название», для горизонтальной оси использовать опцию «Название под осью».

140

На вкладке «Макет», в прямоугольнике «Подписи» выбрать «Легенда», «Добавить легенду снизу».

Отформатировать названия графика и осей, используя прямоугольник «Шрифт» на закладке «Главная» как показано на рис. 12.1.19.

Рис. 12.1.19 Графики краж и убийств в США в период с 1991 года по 2000 год на отдельном рабочем листе «MS Excel»

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

Из графиков на рис. 12.1.19 видна схожесть поведения случайных величин «Число краж» и «Число убийств» во времени. Следовательно, справедливо предположение о наличии формальной связи между этими показателями. Эта связь выявляется с помощью инструмента«Диаграмма точечная».

Выделить диапазон 1:ВС11. Выбрать «Вставка», прямоугольник «Диаграммы», «Точечная» (выбрать первый вариант точечной диаграммы). На экране появилась координатная плоскость: по оси абсцисс отложены количества краж (случайная величина X), а по оси ординат отложены количества убийств (случайная величина Y).

141

На вкладке «Работа с диаграммами» выбрать «Конструктор», «Переместить диаграмму», «на отдельном листе: Диаграмма», «ОК».

На вкладке «Работа с диаграммами» выбрать «Макет», «Сетка», «Вертикальные линии сетки по основной оси», «Основные линии сетки».

На вкладке «Макет» выбрать «Оси», «Основная вертикальная ось», «Дополнительные параметры основной вертикальной оси». В появившемся диалоговом окне «Формат оси» выбрать «Параметры оси», «минимальное значение», «фиксированное», ввести 15000, «максимальное значение», «фиксированное», ввести 25000, «Закрыть».

На вкладке «Макет» выбрать «Оси», «Основная горизонтальная ось», «Дополнительные параметры основной горизонтальной оси». В появившемся диалоговом окне«Формат оси» выбрать «Параметры оси», «минимальное значение», «фиксированное», ввести 2000000, «максимальное значение», «фиксированное», ввести 3200000, «Закрыть».

На вкладке «Макет» в прямоугольнике «Подписи» выбрать «Название диаграммы» и присвоить построенной диаграмме название «Диаграмма зависимости числа убийств от числа краж».

На вкладке «Макет» в прямоугольнике «Подписи» выбрать «Названия осей». Присвоить основной горизонтальной оси название«Кражи», а основной вертикальной оси – название «Убийства». Для горизонтальной оси использовать опцию «Название под осью», для вертикальной оси использовать опцию «Повёрнутое название». На вкладке «Макет», в прямоугольнике «Подписи» выбрать «Легенда», «Нет».

Отформатировать названия диаграммы и осей, используя прямоугольник «Шрифт» на закладке «Главная».

На вкладке «Макет» выбрать «Линия тренда», «Дополнительные параметры линии тренда». В появившемся диалоговом окне «Формат линии тренда» выбрать «Параметры линии тренда», «Линейная», «показывать уравнение на диаграмме», «поместить на диаграмму величину достоверности аппроксимации», «Закрыть». В области диаграммы появилась прямая линия, аппроксимирующая зависимость числа убийств от числа краж. Рядом с линией выведено её уравнение– уравнение линейной зависимости числа убийств от числа краж(y = 0,0098x - 4724,7) и коэффициент детерминации или достоверности полученной модели(R² = 0,911): рис. 12.1.20.

142

Рис. 12.1.20 Диаграмма зависимости числа убийств от числа краж в США в период с 1991 года по 2000 год на отдельном рабочем листе«MS Excel»

В) Расчёт параметров линейной модели видаy = a + bx, коэффициента корреляции, характеризующего связь двух показателей правовой ста-

тистики (rXY) и коэффициента достоверности парной линейной модели

(R2).

а) Расчёт параметров линейной модели вида y = a + bx по формулам. На диаграмме парной зависимости построена линейная модель вида

y = a + bx,

где

a = - 4724,7 b = 0,0098

Эти параметры рассчитываются по формулам(12.1.1) и (12.1.2)			с
помощью инструментов Excel:
=	̅	(12.1.1)
	̅

= − ̅	143
	(12.1.2)

где

– среднее арифметическое попарных произведений десяти значений случайной̅ величины X (число краж) на десять значений случайной величины Y (число убийств) – Таблица 12.2,1

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

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

̅ – среднее арифметическое десяти значений случайной величиныX, возведённых в квадрат,

– среднее арифметическое десяти значений случайной величиныX, возведённое во вторую степень.

Инструмент «СРЗНАЧ» (среднее арифметическое) находится на вкладке «Формулы», прямоугольник «Библиотека функций», «Статистические».

Инструмент «СТЕПЕНЬ» находится на вкладке «Формулы», прямоугольник «Библиотека функций», «Математические».

Таблица 12.2. Выборка из десяти значений переменных X и Y.

Номер X	Значение X	Номер Y	Значение Y
x1	3 157 200	y1	4 700
x2	2 979 900	y2	23 760
x3	2 834 800	y3	24 530
x4	2 712 800	y4	23 330
x5	2 593 800	y5	21 610
x6	2 506 400	y6	19 650
x7	2 461 100	y7	18 210
x8	2 329 950	y8	16 914
x9	2 100 739	y9	15 522
x10	2 049 946	y10	15 517

Используя инструменты «СРЗНАЧ» и «СТЕПЕНЬ», получить значения параметров линейной моделиa = - 4724,7 и b = 0,0098 по формулам

(12.1.1) и (12.1.2).

В диапазон D2:D11 ввести произведения X*Y: D2: =B2*C2

D3: =B3*C3

итак далее до D11.

1Данные Таблицы 12.2 уже занесены на рабочий лист Excel в диапазон А1:С11

–Таблица 12.1.

				144
В диапазон E2:E11 ввести вторую степень X2:
E2: =СТЕПЕНЬ(B2;2)
E3: =СТЕПЕНЬ(B3;2)
и так далее до E11.
В ячейке B13 вычислить	:			=СРЗНАЧ(B2:B11)
В ячейке C13 вычислить	̅:			=СРЗНАЧ(C2:C11)
В ячейке D13 вычислить		:		=СРЗНАЧ(D2:D11)
В ячейке E13 вычислить		:		=СРЗНАЧ(E2:E11)
В ячейке D15 вычислить	̅		:	=B13*C13
В ячейке E15 вычислить						: =D13-F13
В ячейке D16 вычислить		:−			СТЕПЕНЬ(B13;2)
				=̅
В ячейке E16 вычислить	̅		̅		: =E13-D16
В ячейке D18 получить					=E15/E16 (0,009756)
			−: ̅
В ячейке E18 получить			̅	: =C13-D18*B13 (- 4724,6727).
	показаны на рис. 12.1.21.
Результаты этих расчётов−				̅

Рис. 12.1.21 Расчёт параметров линейной модели видаy = a + bx по формулам (12.1.1) и (12.1.2) на рабочем листе «MS Excel»

б) Для определения параметров линейной модели «MSв Excel» имеется встроенный статистический инструмент«ЛИНЕЙН», который

145

находится на вкладке «Формулы», прямоугольник «Библиотека функций», «Статистические».

Инструмент «ЛИНЕЙН» вводится как функция ссылок и массивов, поэтому перед выбором этого инструмента необходимо на изображённом на рис. 12.1.21 рабочем листе курсором выделить две смежные ячейки D20:E20. Затем вызвать инструмент «ЛИНЕЙН».

Известные значения у – диапазон C2:C11

известные значения х – диапазон B2:B11

Конст – 1, означает, что свободный коэффициент линейного уравнения не равен нулю

Статистика – 0, означает, что другая статистическая информация, кроме коэффициентов линейного уравнения, не требуется.

Ввод: последовательное нажатие клавиш<Ctrl> и <Shift>, затем, удерживая эти клавиши, нажать <Ввод> (<Enter>). Как уже отмечалось, инструмент «ЛИНЕЙН» является инструментом ссылок и массивов.

Результат в ячейке D20: 0,009756. Результат в ячейке E20: - 4724,6727.

в) Расчёт коэффициентов rXY и R2.

Полученная зависимость y = - 4724,6727 + 0,009756x называется модель парной линейной регрессии.

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

Корреляция: характеристика сопряжённости вариации двух случайных величин, статистическая мера их взаимодействия.

Корреляционная зависимость: связь между переменными, при которой каждому значению независимой переменной соответствует определённое математическое ожидание зависимой переменной.

Расчёты продолжаются на изображённом на рис. 12.1.21 рабочем листе.

Используя данные Таблицы12.1, занесённые в диапазон А1:C11

рассчитать коэффициент корреляции rXY по формуле (12.1.3).
=	∑	∑	(	(	̅)(	(	)	)		(12.1.3)
					̅) ∑

Инструменты сумма произведений «СУММПРОИЗВ» и квадратный корень «КОРЕНЬ» находятся на вкладке«Формулы», прямоугольник «Библиотека функций», «Математические».

	146
В диапазон F2 : F11 ввести	:
F2: =B2-$B$13	− ̅

F3: =B3-$B$13

и так далее до F11 (абсолютная ссылка на ячейку B13).

В диапазон G2 : G11 ввести

−

:

G2: =C2-$C$13

G3: =C3-$C$13

и так далее до G11 (абсолютная ссылка на ячейку C13).

В диапазон H2 : H11 ввести

(

− ̅)

:

H2: =СТЕПЕНЬ(F2;2)

H3: =СТЕПЕНЬ(F3;2)

(

− )

и так далее до H11

В диапазон I2 : I11 ввести

:

I2: =СТЕПЕНЬ(G2;2)

I3: =СТЕПЕНЬ(G3;2)

и так далее до I11

∑

(

−

)

:

=СУММ(H2:H11)

В ячейке H13 получить

∑

(

− ̅):

В ячейке I13 получить

=СУММ(I2:I11)

В ячейке H15 получить

∑

(

− ̅)(

−

): =СУММПРОИЗВ(F2:F11;G2:G11)

В ячейке I15 получить

∑

(

− ̅)

∑

(

−

)

:

=КОРЕНЬ(H13*I13)

В ячейке I17 получить

)

=

∑

(

̅) ∑

(

∑

(

̅)(

)

: =H16/I16

(0,954482) – рисунок 12.1.22.

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

Коэффициент R2 = (rXY)2. Поэтому на диаграмме R2 = 0,911. В ячейке I18 делается проверка: =I17^2. Результат: 0,911035.

Для определения коэффициентовrXY и R2 в пакете«Excel» предусмотрены встроенные инструменты«КОРРЕЛ» и «КВПИРСОН», которые находятся на вкладке«Формулы», прямоугольник «Библиотека функций», «Статистические».

147

Вячейке I19 вызвать инструмент «КОРРЕЛ», и введя в его диалоговое окно массивы B2:B11 и C2:C11, получить значение 0,954482.

Вячейке I20 вызвать инструмент «КВПИРСОН», и введя в его диалоговое окно массивы B2:B11 и C2:C11, получить значение 0,911035.

При работе с инструментами«КОРРЕЛ» и «КВПИРСОН» последовательность ввода массивов не важна.

Рис. 12.1.22 Расчёт коэффициентов парной корреляции и детерминации на рабочем листе «MS Excel»

г) Автоматизация расчёта некоторых статистических показателей. Курсором выделить диапазон ячеек размером пять строк на два

столбца: D22:E26. Затем вызвать инструмент «ЛИНЕЙН». Известные значения у – диапазон C2:C11, известные значения х – диапазон B2:B11,

Конст – 1, Статистика – 1.