CC BY
1156
ПРОВЕРКА ВЫБОРКИ НА НОРМАЛЬНОСТЬ И РАСЧЕТ КОРРЕЛЯЦИОННОГО ОТНОШЕНИЯ В СРЕДЕ MS EXCEL И VBA
УДК 330.4(075.8)
Ольга Викторовна Зинюк
к. т.н., доцент кафедры дизайна и режиссуры в рекламе Московского гуманитарного университета Тел. 8-916-130-86-94, Эл. почта: ol gazi nyu k@rambl er. ru
В статье рассматривается методика проверки выборки на нормальность и расчета корреляционного отношения с использованием средств автоматизации обработки данных MS Excel и VBA, таких как создание макросов и пользовательских функций, с целью ее универсального использования в экономико-статистических исследованиях.
Ключевые слова: нормальность выборки, корреляционное отношение, макрос, функция пользователя, медиана, эксцесс, коэффициент детерминации.
Olga V. Zinyuk
PtD, Associate Professor of Chair of design and directions in advertisement in Moscow University for the Humanities Tel. 8-916-130-86-94, E-mail: olgazinyuk@rambler.ru
CHECK SAMPLE FOR THE NORMALITY AND CALCULATION CORRELATION RELATIONSHIPS IN MS EXCEL AND VBA
The article discusses method of test sample for the normality and calculate the correlation relationship with the use of automated data processing of MS Excel and VBA, such as creating macros and custom functions, with a view to its universal use in economy-statistical studies.
Keywords: normality, correlation relationship, macros, custom function, median, kurtosis, the coeffici ent of determination.
1. Введение
Совместный анализ выборок, полученных в результате экономико-статистических исследований, требует решения вопроса о выборе параметрических или непараметрических критериев статистики для оценки их взаимосвязи [1].
Выбор параметрических критериев методов математической статистики основывается на предположении о том, что распределение выборок подчиняется нормальному (гауссовому) закону распределения, в связи с чем одной из задач статистического анализа является проверка вида распределения выборок [2].
В работе представлена методика проверки выборки на нормальность и определения корреляционного отношения с использованием средств автоматизации расчетов MS Excel и VBA. Представленная методика дает возможность максимально автоматизировать операции расчета данных и может быть использована для выборок любого объема в статистических исследованиях, проводимых в любой области экономики.
2. Формирование исходной выборки
В качестве исходных данных рассматриваются выборки, полученных в результате сбора информации на сайте по количеству заказов обуви в течение 20 контрольных дней. Потребительским качествам и факторам обуви присвоены квалификационные коды для их использования в создании аналитических баз данных: КГГ - повышенная гигиеничность; КНП - снижение нагрузки на позвоночник; КОВ - обувь для водителей; КПВ - повышенная влагонепроницаемость; КПГ -повышенная гибкость; КПП - противоскользящая подошва; КСЭ - защита от статического электричества; КФС - форма подошвы, соответствующая стопе; ДАМ - аналоги известных марок; ДДК - дизайн-комфорт; ДМК - модная коллекция; ДНЗ - аналоги обуви знаменитостей; ДПК - перспективная коллекция; ЭВФ -высокая формоустойчивость; ЭНИ - низкая истираемость верха и низа; ЭПП -повышенная прочность; ЭЭЧ - экологическая чистота.
Полученная база данных, подготовленная к обработке с MS Excel, состоит из семнадцати выборок (по количеству факторов) и содержит номер измерения от 1 до 20 (по количеству дней), общее количество заказов в день (n) и количество заказов обуви по факторам (m) (таблица 2.1).
Таблица 2.1. Количество заказов обуви по измерениям (Лист «Исходные данные» - фрагмент)
A B C D E F G H I J K L M N O P Q R S
1 № измерения Количество заказов (и,) I 1-4 Ы | I ! 1 £ и va 1 Ьч & о и °о % « СЛ S v—✓ Е I PO S I « S Ji e и CD *-> S S X E a Я о jf Ж О
2 1 239 13 25 8 18 10 21 5 15 12 12 19 8 15 17 18 19 4
3 2 188 10 18 7 14 8 16 6 11 8 9 15 7 12 12 14 16 5
20 19 197 10 19 10 14 8 16 6 11 9 9 16 9 12 12 15 16 5
21 20 155 10 16 6 12 7 14 5 6 9 9 14 6 6 4 12 14 5
3. Проверка выборки на нормальность
Проверка распределения на нормальность включает следующие этапы [2]:
1) Вычисляются среднее арифметическое, медиана и мода. Если полученные значения друг от друга значительно не отличаются, мы имеем дело с нормальным распределением.
Формула для вычисления среднего арифметического (выборочного) - сумма значений переменной (х; ... хп), деленная на п (число значений перемен-ной -
Экономика, Статистика и Информатика^! 109 №5, 2011
имеет вид:
x =
лср
x, + x2 + x3 +... + xn
I x
i=1
-(3.1)
и п
Медиана разбивает выборку на две равные части. Половина значений переменной лежит ниже медианы, половина - выше. Медиана дает общее представление о том, где сосредоточены значения переменной, иными словами, где находится ее центр.
Мода представляет собой максимально часто встречающееся значение пере-менной.
2) Вычисляется эксцесс - мера крутости кривой распределения, который для нормального распределения должен быть равен 0. Эксцесс определяется по уравнению:
X (х* ~ хср )Ь
Ex =■
--3
(3.2)
Таблица 3.1. Формулы для расчета статистических параметров (Лист «Нормальность»)
B C
1 Код фактора КГГ
22 Среднее =СРЗНАЧ(С2:С21)
23 Медиана =МЕДИАНА(С2:С21)
24 Мода =МОДА(С2:С21)
25 Эксцесс =ЭКСЦЕСС (С2:С21)
,061853647
Рис. 1. Окно выбора аргументов функции «Normal»
где x - среднее значение переменной;
n - число значений перемен-ной; а - стандартное отклонение выборки.
Вычисление среднего, медианы, моды и эксцесса средствами MS Excel в режиме формул показано в таблице 3.1.
Для расчета абсолютных отклонений среднего значения от медианы и моды на VBA разработана пользовательская функция «Normal», аргументами которой являются среднее значение выборки (Sr) и медиана или мода (M):
Public Function Normal(Sr As Single, M As Single) Dim Nl As Single
Nl = Abs ( ( (Sr - M) / Sr) * 100) Normal = Nl End Function
При выборе функции для возможности ее последующего копирования в качестве аргументов вводятся относительные ссылки на ячейку C22 и C23, C24 (рисунок 1).
Анализ отклонений среднего значения от медианы и моды показывают, что перечисленные величины не совпадают (рисунок 2), а эксцесс кривой распределения отличен от 0 (рисунок 3).
Вышеперечисленные расчеты позволяют сделать вывод о том, что распределение рассматриваемых выборок не подчиняется нормальному (гауссо-вому) закону распределения и позволяют использовать для анализа только непараметрические критерии статистики, которые свободны от допущения о законе распределения выборок и бази-
Рис. 2. Отклонение среднего значения от медианы и моды
Рис. 3. Значение эксцесса кривой распределения
руются на предположении и независимости наблюдений.
4. корреляционный анализ
В эконометрических и экономико-математических моделях, применяемых, в частности, при изучении и оптимизации процессов маркетинга, наиболее популярно нормальное распределение, однако практика показывает, что в большинстве случаев распределения существенно отличаются от нормальных [3], в связи с чем для корреляционного анализа в работе рассматриваются только непараметрическое оценивание.
Для количественного анализа эффективности маркетинговых мероприятий необходимо установить зависимость между генеральной совокупностью (общим количеством заказов) и количеством заказов обуви по факторам.
Отсутствие нормальности выборок делает невозможным использование коэффициента корреляции, который предполагает наличие линейной связи между признаками. При наличии нелинейной связи коэффициент корреляции может быть равен нулю. В таких случаях для выявления связи применяют другой показатель - корреляционное отношение [2], которое фиксирует наличие любой связи между признаками. Алгоритм расчета корреляционного отношения включает следующие шаги:
- область значений одного признака (генеральной совокупности) разбивается на участки;
- для каждого из участков определяется среднее значение другого признака (пофакторной выборки);
- вычисляется корреляционное отношение, величина которого, как и коэффициента корреляции, лежит между нулем и единицей; чем ближе значение корреляционного отношения к единице, тем более тесной является связь.
Разработку методики расчета корреляционного отношения рассмотрим на примере установления зависимости между общим количество заказов обуви (п) и заказов по каждому из факторов комфортности (m.) - повышенной гигиеничности (КГГ) (таблица 1.1).
Произведем группировку общего количества заказов (факторный признак), образовав 5 групп с равными интервалами, предварительно отсортировав данные по количеству заказов.
Количество групп (5) выбрано на основании того, что в практике статистических исследований руководствуют-
ся тем, чтобы в интервалы попадало число наблюдений не менее 5-10 [3].
Величина интервала группировки (й) определяется как:
й _ птах ~ пшш (4.1)
где n , n . - максимальное и ми-
max min
нимальное значения общего количества заказов.
Границы интервалов групп nd определяются как:
nd.+1 = nd. + d. (4.2)
где nd.+1, nd. - верхняя и нижняя границы интервалов.
Нижней границей первого интеграла является минимальное значение количества заказов n .
min
Для определения номера интервала на VBA разработана пользовательская функция «Interval», аргументами которой являются максимальное (nmax), минимальное (nmin) и текущее (Kz) количество заказов.
P>ublic Function Interval (nmax As Single, nmin As Single, Kz As Single) As Single Dim d As Single, II As Single d = (nmax - nmin) / 5
If Kz < (nmin + d) Then
II = 1 Else
If Kz < (nmin + d * 2) Then
II = 2 Else
If Kz < (nmin + d * 3) Then
II = 3 Else
If Kz < (nmin + d * 4) Then
II = 4 Else
If Kz <= (nmin + d * 5)
Then
II = End If End If End If End If End If Interval = End Function
5
II
При выборе функции для возмож-
ности ее последующего копирования в качестве аргументов вводятся абсолютные ссылки на ячейки $В$21 и $В$2 и относительная - на ячейку В2 (рисунок 4).
После сортировки и проведения расчетов в таблицу исходных результатов (таблица 1.1) добавляется столбец «№ интервала» (таблица 4.1).
Корреляционное отношение определяется по формуле:
D
межгр
D
(4.3)
общ
где DMexp - межгрупповая диспер-
сия:
z m
D
межгр
n
D
общ - общая дисперсия: D
общ
Dмежгр ^ DbK;
(4.4)
(4.5)
D - внутригрупповая дисперсия:
Z Di n
D
внгр
(4.6)
т.ср - групповые пофакторные средние (по интервалам);
тр - общее пофакторное среднее; п. - количество заказов в группах; п - общее количество заказов; k - количество групп; D. - дисперсия в группе. Дисперсия в группе вычисляется по формуле: N
2 (m' " micp У
Di =-
N
(4.7)
где mi - текущее значение;
N - число значений в группе.
Вышеперечисленные данные, рассчитанные с помощью промежуточных итогов в Excel, показаны на рисунке 5.
Для определения корреляционного отношения нецелесообразно создавать макросы и пользовательские функции, так как в расчетах необходимо в каче-
Рис. 4. Окно выбора аргументов функции «Interval»
Экономика, Статистика и Информатика
№5, 2011
2
n
-1
n
-1
стве аргументов использовать относительные, смешанные и абсолютные ссылки и нефиксированный диапазон данных. В связи с этим проведение вычислений с помощью переадресации, встроенных функций и групповых операций MS Excel [4] дает больший эффект автоматизации расчетов.
Полученные данные (рисунок 5) сводятся в единую таблицу (рисунок 6) для проведения расчета межгрупповой, общей, внутренней дисперсии и корреляционного отношения (формулы 4.3-4.6).
Графический анализ пофакторного коэффициента корреляционного отношения (рисунок 7) показывает, что ряд одних факторов имеют высокую (0,70,9) и весьма высокую (0,9-0,99) по шкале Чеддока [1] функциональную связь с общим количеством заказов, в то время как другие - умеренную и заметную (0,3-0,7).
Фрагмент расчетной таблицы (рисунок 6) в режиме отображения формул показан в таблице 4.2.
При значениях показателей тесноты связи меньше 0,7 величина коэффициента детерминации [3] всегда будет ниже 50 %. Это означает, что на долю вариации факторных признаков приходится меньшая часть по сравнению с остальными неучтенными в модели факторами, влияющими на изменение результативного показателя (общего количества заказов обуви).
Таки образом, формирование маркетинговой политики и построение регрессионных моделей необходимо про-
Таблица 4.1. Расчет интервалов групп по количеству заказов (Лист «Корреляционное отношение» - фрагмент)
A B C D E F G H I J K L M N O P Q R S T
1 0 1 а Количество заказов (и,) I и ы I С «Ч Е И § •» Е И £ 1 к S СП о и £ £ Е i JN & & ЭВФ (т14,) I Я К т I И С О I £ О о № интервала
2 ii 133 7 14 4 10 5 10 4 8 6 7 11 4 9 10 11 10 3 1
3 7 153 7 14 7 10 6 11 8 9 6 7 11 7 10 11 10 11 8 1
20 1 239 13 25 8 18 10 21 5 15 12 12 19 8 15 17 18 19 4 5
21 6 239 12 24 9 17 10 21 8 14 11 12 18 9 15 15 17 20 7 5
с
I S g: а — л — - 3 1 KIT LJ P
1
it" Б
6 7.3 14,3
а 7 0J 0.3
за 3fi7igi>
39 9.7 IS,2
НО 9.П
9
С Г' К r & z: %
1 Итог
Щ 5 A 1 Cpe.iiKf
1.0 7.0 1 Л-lif lli|itH u
Ойший JiiuJ'
15.3 Onuitf средня
7,1 2,1 OujljilH ^Ш'ПфсШ!
Рис. 5. Расчет средних значений, количества элементов и дисперсии (Лист «Корреляционное отношение 1» - фрагмент)
l_ Ш l 1 i s & £ KIT s £ ft s?) Л £ i я s J-
"E m,
J39.0 W 14iJ п,о 5.0 t
ЮОО 12,3 23.0 19,0 6,3 5
П IH
1671,0 9,7 IS. 2 JJ,J
О i 0Д J.O 7,0 I
0,3 0.7 ; v i
2-48.9 7929,4 1,1 i
6047,3 21427.2 13078,1 1339,2 i
| Ашжр «Л 6Л
146,3 307i,0 1
232,5 3100.0 (520,0 ■1572,5 ;
Den 0f4 1,3 o.e 2,1
\fio6m 10,0
6fP4 в,91 6,94 0,4S
Таблица 4.2. Формулы для расчета корреляционного
отношения 77
B C
50 D =C7
51 =C15
55
56 =((C42-C$48)A2*$B42)
61 Бмежгр =СУММ(С56: C60)/$B$48
62
63 =C50*$B42
68 DeH =СУММ(C63: C67)/$B$48
69
70 Бобщ =C61+C68
71
72 Л =КОРЕНЬ(С61/ C70)
2
водить только на основании высокого и весьма высокого корреляционного отношения. В противном случае они могут быть ошибочны и не достоверны.
Для удобства навигации в книге MS Excel на рабочих листах созданы командные кнопки для перехода по страницам (рисунок 8).
Коды VBA для программирования командных кнопок приведены ниже.
Код для открытия листа «Исходные данные»:
Private Sub ConniandButton1_Click () Sheets ("Исходные данные") .Activate End Sub
Код для открытия листа «Нормальность»:
Private Sub CcmnandButton2_Click() Sheets("Нормальность").Activate End Sub
5. Заключение
Описанные шаги анализа нормальности выборки и определения корреляционного отношения объединены в URL-диаграмму, представленную на рисунке 9.
В показанной диаграмме выделены основные шаги проверки однородности и корреляционного анализа, а также средства автоматизации расчетов с помощью созданных пользовательских функций. Автоматизация работы с выборкой реализуется также за счет использования встроенных стандартных функций, групповых операций и переадресации данных MS Excel, за счет чего схема приобретает универсальность и работает для выборки любого размера. В схеме также показана возможность после корреляционного анализа выбрать факторы, по которым
Корреляционное отношение
1,00 0.90 0,60 0.70 0,60 0,50 0,40 0,30 0,20 0,10 0,00
0.940,93
^^bàD091 0,900.90
0.93
0.74
0,52
0,84
0,37
0.32
0 94
0,77
О. S3
0,45
I
lu
0J
S
(El
О g
£ <
СГ
S
гг
I
it §
в
S Г I Г
п о
m m
Рис. 7. Пофакторные значение корреляционного отношения
можно строить дельнейшую маркетинговую и экономическую стратегию.
Литература
1. Гмурман В. Е. Теория вероятностей и математическая статистика. М.: Юрайт, 2011. 480 с.
2. Орехов С. А. Статистика. М.: ЭКС-МО, 2010. 448с.
3. Громов Е.И., Гладилин А. В., Ге -расимов А. Н. Эконометрика. М.: Феникс, 2011 г. 304 с.
4. Лялин В. С., Зверева И. Г., Никифорова Н. Г. Название: Статистика. Теория и практика в Excel. Издательство:
Финансы и статистика, Инфра-М, 2010. 448 с.
References
1. Gmurman VE. Probability and Mathematical Statistics. MM: Yurayt, 2011. 480 pp.
2. Orehov SA. Statistics. MM: EKSMO, 2010. 448с.
3. Gromov, EI, Gladilin AV, Gerasimov AN Econometrics. MM: Phoenix, 2011. 304 pp.
4. Lyalin VS, Zverev, IG, Nikiforov N. Title: Statistics. Theory and practice in Excel. Publisher: Finance and Statistics, Infra-M, 2010. 448 pp.
Рис. 8. Командные кнопки навигации
Экономика, Статистика и Информатика
№5, 2011
3
Рис. 9. Ц^-диаграмма анализа нормальности и корреляции
