Методика расчета ошибки и однородности выборки средствами MS Excel и VBA Текст научной статьи по специальности «Компьютерные и информационные науки»

МЕТОДИКА РАСЧЕТА ОШИБКИ И ОДНОРОДНОСТИ ВЫБОРКИ СРЕДСТВАМИ MS EXCEL И VBA

УДК 330.4(075.8)

Ольга Викторовна Зинюк

к.т.н., доцент кафедры дизайна и режиссуры в рекламе Московского гуманитарного университета Тел. 8-916-130-86-94, E-mail: [email protected]

В статье рассматривается методика расчета ошибки выборки и проверки ее однородности с использованием средств автоматизации обработки данных MS Excel и VBA, таких как создание макросов и пользовательских функций, с целью ее универсального использования в экономико-статистических исследованиях без знания закона распределения.

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

Olga Zinyuk

Doctorate of Technology Sciences, Associate Professor, the Department of Design and Directions in advertisement Moscow Humanitarian University Tel.: 8-916-130-86-94 E-mail: [email protected]

THE CALCULATION METHODS OF ERROR AND UNIFORMITY OF RETRIEVAL USING MS EXCEL AND VBA

The author considers the calculation methods of error, retrieval and check-up its uniformity with using automation equipment of data-processing operation MS Excel and VBA, such as creation of macros and custom function. The aim of research is multi use in economic and statistic studies without learning the distribution law.

Keywords: sampling error, uniformity, macros, custom function, confidence interval, the statistics module.

1. Введение

Одним из основных этапов экономико-статистического анализа анкетных данных является определение ошибки выборки и расчет ее однородности [1].

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

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

2. Формирование исходной выборки

В качестве исходных данных рассматриваются выборки, полученных в результате сбора информации на сайте по количеству заказов обуви в течение 20 контрольных дней. В рамках проведения исследования из совокупности потребительских качеств обуви выделены три основных, каждый из которых характеризуется рядом факторов. Потребительским качествам и факторам присвоены квалификационные коды для их использования в создании аналитических баз данных: КГГ -повышенная гигиеничность; КНП - снижение нагрузки на позвоночник; КОВ -обувь для водителей; КПВ - повышенная влагонепроницаемость; КПГ - повышенная гибкость; КПП - противоскользящая подошва; КСЭ - защита от статического электричества; КФС - форма подошвы, соответствующая стопе; ДАМ - аналоги известных марок; ДДК - дизайн-комфорт; ДМК - модная коллекция; ДНЗ -аналоги обуви знаменитостей; ДПК - перспективная коллекция; ЭВФ - высокая формоустойчивость; ЭНИ - низкая истираемость верха и низа; ЭПП - повышенная прочность; ЭЭЧ - экологическая чистота.

Полученная база данных, подготовленная к обработке с MS Excel, состоит из семнадцати выборок (по количеству факторов) и содержит номер измерения от 1 до 20 (по количеству дней), общее количество заказов в день (n) и количество заказов обуви по факторам (m) (таблица 1.1).

3. Расчет ошибки выборки

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

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

Таблица 1.1. Количество заказов обуви по измерениям (Лист «Исходные данные» - фрагмент)

A B C D E F G H I J K L M N O P Q R S

1 № измерения о ^ m к н --b S3 и Д <N S S S S чЗ s S Oo S £ .S, .S, <N S .S, S .S, .S, .S,

S Н S <s ^ M 3 Я u u « С m « m о « m с « u С « с с « m о « О О « < « « en К ti « с 0 m m S m m С с m F m m

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

Экономика, Статистика и Информатика ЦЦ №4, 2011

I

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

В вероятностной модели предполагается, что случайные величины выбора факторов независимы и одинаково распределены. Поскольку эти случайные величины принимают два значения, то ситуация описывается одним параметром р - долей выбирающих первый фактор во всей генеральной совокупности [2].

Оценкой вероятности р является частота р*:

р*=т/п. (2.1)

Точность оценивания выборки можно определить по доверительному интервалу, вычисленному по теореме Муав-ра-Лапласа [4].

Нижняя и верхняя доверительные границы рассчитываются по уравнению:

4П

Рнижн = Р* - U00-

Реерх = Р* + U M

4П

(2.2)

Р = 3,92

4Р* 1 - Р* )

4n

(2.3)

Дргуденты функции

ОйЫй

FI a

ftfil

- 1,-нм«ж

где у - доверительная вероятность; и(у) -функция распределения.

Наиболее распространенным (в прикладных исследованиях) значением доверительной вероятности является у = 0,95. Тогда П(у) = 1,96 [2].

Уравнение для вычисления полного доверительного интервала после преобразования формул (2.2) имеет вид:

Полученная формула для расчета доверительного интервала может быть использована для определения ошибки выборки средствами VBA в среде Excel. В сравнении с встроенной функцией «ДОВЕРИТ()» [3] формула не требует нормальности распределения и содержит не среднее, а абсолютное (суммарное) значение выборок.

Исходными данными для расчета являются суммарные значения общего и пофакторного количества заказов (таблица 2.1), полученные по данным таблицы 1.1. В таблице 2.1 приведен также процент пофакторных сумм от общей.

Для оптимизации расчетов ошибки выборки по уравнению (2.3) на языке VBA разработана пользовательская функция «Oshibka», аргументами которой являются суммы по факторам (F1) и общее количество заказов (Kz):

Public Function Oshibka(F1 As Single, Kz

As Single)

Dim Op As Single

Op = (3.92* Sqr((F1/Kz) * (1- (F1/Kz)))/Sqr(Kz))* 100 Oshibka = Op End Function

При выборе функции для возможности ее последующего копирования в качестве аргументов вводятся относительная ссылка на ячейку C2 и абсолютная - на $B$2 (рисунок 1).

С целью визуализации анализа полученных табличных результатов на VBA создан макрос «SearchMaxMin», предназначенный для выделения цветом максимального и минимального значения ошибки:

Option Explicit

Public Sub SearchMaxMin()

Рис.1. Окно выбора аргументов функции «Oshibka»

Dim rg As Range Set rg = Selection

Dim i As Integer, maxNum As Single, minNum As Single maxNum = rg.Cells(1, 1)

For i = 1 To rg.Columns.Count

rg.Cells(1, i).Interior.Color = RGB(255, 255, 255) Next i

For i = 1 To rg.Columns.Count

If rg.Cells(1, i).Value > maxNum Then

maxNum = rg.Cells(1, i).Value End If Next i

For i = 1 To rg.Columns.Count

If rg.Cells(1, i).Value = maxNum Then

rg.Cells(1, i).Interior.Color =

RGB(0, 255, 0) End If Next i

minNum = rg.Cells(1, 1)

For i = 1 To rg.Columns.Count If rg.Cells(1, i).Value < minNum Then

minNum = rg.Cells(1, i).Value End If Next i

For i = 1 To rg.Columns.Count

If rg.Cells(1, i).Value = minNum Then

rg.Cells(1, i).Interior.Color =

RGB(255, 255, 0) End If Next i End Sub

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

Для возможности повторного использования макроса при изменении исходных данных используется макрос «ClearSelection» для очистки выделенных ячеек:

Public Sub clearSelection() Dim rg As Range

Dim i As Integer, j As Integer Set rg = Selection

If Not IsArray(rg.Value) Then MsgBox "Выделите блок ячеек ", vbExclamation, "" Exit Sub End If

For j = 1 To rg.Cells.Columns.Count For i = 1 To rg.Cells.Rows.Count rg.Cells(i, j).Interior.Color = RGB(255, 255, 255) Next i Next j End Sub

Таблица 2.1. Абсолютная и процентная ошибка выборки (Лист «Ошибка выборки»)

А B C D E F G H I J

Показатель Кол-во заказов (n ) ^ S КНП КОВ КПВ КПГ КПП КСЭ КФС

1 Сумма 3671 194 364 134 261 149 310 119 215

2 % 5,28 9,92 3,65 7,11 4,06 8,44 3,24 5,86

3 Ошибка % 1,45 1,93 1,21 1,66 1,28 1,80 1,15 1,52

4 Ошибка абс. 0,75 0,53 0,91 0,64 0,86 0,58 0,96 0,71

K L M N O P Q R S

ДАМ К Д ДМК ДОЗ К П Д ЭВФ ЭНИ ЭПП ЭЭЧ

1 Сумма 178 189 284 133 230 238 267 305 101

2 о/ % 4,85 5,15 7,74 3,62 6,27 6,48 7,27 8,31 2,75

3 Ошибка % 1,39 1,43 1,73 1,21 1,57 1,59 1,68 1,79 1,06

4 Ошибка абс. 0,78 0,76 0,61 0,91 0,68 0,67 0,63 0,59 1,05

Анализ результатов показывает, что в абсолютном выражении ошибка выборки лежит в интервале [0,53-1,05] (не превышает одну пару обуви), что позволяет делать вывод о репрезентативности выборки и использовать ее для дальнейшего экономико-статистического анализа.

4. Проверка однородности

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

Обсу^даемая далее постановка задачи в терминах эконометрики такова. В первой группе из n сделавших заказы (измерение №1 - 239) m человек (13) выбрали фактор КГГ (повышенная гигиеничность), во второй группе из n2 (измерение №2 - 188) сделавших заказы ш12 человек выбрали фактор КГГ (10) и т.д. (таблица 1.1).

Для удобства проведения дальнейших расчетов в таблице 1.1 проведена сортировка количества заказов по возрастанию.

Однородность двух групп означает, что соответствующие им вероятности равны, неоднородность - что эти вероятности отличаются. В терминах прикладной математической статистики: необходимо проверить гипотезу однородности (нулевую гипотезу) Ид : p1 = p2 при альтернативной гипотезе

H : Pi Ф Р2 [2].

Оценкой вероятности р1 является частота p1 *=m/n1 (2.1), а оценкой вероятностир2 является частотар2 *=m,/n2 . Даже при совпадении вероятностей р1 и р2 частоты, как правило, различаются.

В рассматриваемой базе данных для проверки однородности с помощью разработанного макроса «SearchMaxMin» проводится поиск попарных значений количества заказов с наибольшим расхождением частот (наибольшая и наименьшая разница n. и m).

Код макроса «SearchMaxMin»:

Option Explicit Public Sub SearchMaxMin() Dim rg As Range Set rg = Selection

Dim i As Integer, maxNum As Single,

minNum As Single maxNum = rg.Cells(1, 1)

For i = 1 To rg.Columns.Count

rg.Cells(1, i).Interior.Color = RGB(255, 255, 255) Next i

For i = 1 To rg.Columns.Count

If rg.Cells(1, i).Value > maxNum Then

maxNum = rg.Cells(1, i).Value End If Next i

For i = 1 To rg.Columns.Count

If rg.Cells(1, i).Value = maxNum Then

rg.Cells(1, i).Interior.Color =

RGB(0, 255, 0) End If Next i

minNum = rg.Cells(1, 1)

For i = 1 To rg.Columns.Count If rg.Cells(1, i).Value < minNum Then

minNum = rg.Cells(1, i).Value End If Next i

For i = 1 To rg.Columns.Count

If rg.Cells(1, i).Value = minNum Then

rg.Cells(1, i).Interior.Color =

RGB(255, 255, 0) End If Next i End Sub

Для переноса позиций выделенных ячеек с наибольшим расхождением частот на соответствующие ячейки таблицы 1.1, отсортированной по количеству заказов, разработан макрос «SetPosition»:

Option Explicit Public Sub SetPosition() Dim rg1 As Range, rg2 As Range Set rg1 = Range("B2:S21") Set rg2 = Range("U2:AL21") Dim colorCode1, colorCode2 colorCode1 = RGB(255, 255, 0) colorCode2 = RGB(0, 255, 0)

Dim i As Integer, j As Integer For i = 1 To rg2.Rows.Count

For j = 1 To rg2.Columns.Count If rg2.Cells(i, j).Interior.Color = colorCode1 Then

rg1.Cells(i, j).Interior.Color = RGB(255, 255, 0) End If

If rg2.Cells(i, j).Interior.Color = colorCode2 Then

rg1.Cells(i, j).Interior.Color = RGB(0, 255, 0) End If Next j Next i End Sub

Результат работы макроса «SetPosition» показан в таблице 3.1 (значения с наибольшим расхождением частот выделены курсивом).

Анализ данных таблицы 3. 1 показывает, что по минимальным значениям имеет место полное совпадение с минимумом количества заказов, по максимальным - отклонение на одно измерение только по фактору.

Правило принятия решения при проверке однородности двух выборок состоит из двух этапов [2]: 1. Вычисление статистики по уравнению:

Q = -

P1 - Р2

/Р* ( - Р* ) , Р* I1 - Р*2 )

(3.1)

V "1 "2

Для расчета статистики по формуле (3.1) на языке VBA разработана пользовательская функция «Statistika», аргументами которой являются количество заказов по факторам (F1, F2) и суммарное количество заказов по номеру измерения (Kz1, Kz2) с максимальным расхождением частот:

Public Function Statistika(F1 As Single,

F2 As Single, _

Kz1 As Single, Kz2 As Single)

Dim St As Single

St = ((F1/Kz1) - (F2/Kz2)) / Sqr(((F1/

Kz1) * _

(1 - (F1/Kz1))) / Kz1 + ((F2/Kz2) *

(1 - (F2/Kz2)))/Kz2)

Statistika = St End Function

Полученные значения Q приведены в таблице 3.2.

2. Сравнение значения модуля статистика |Q| с граничным значением (в экономико-статистических исследованиях наиболее распространено значение 1,96). Если IQI превышает граничное значение, то можно говорить об отсутствии однородности и принять альтернативную гипотезу И, в противном случае принимается гипотеза однородности Ид.

Поскольку максимальное значение |Q|, равное 0,52, меньше 1,96 (таблица 3.1), то можно сделать вывод об однородности всех групп, участвующих в экспертном опросе.

5. Заключение

Описанная в работе методика оценки выборки представлена в виде схемы, показанной на рисунке 2.

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

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

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

Таблица 3.1. Перенос позиций наименьших и наибольших расхождений количества заказов (Лист «Однородность» - фрагмент)

A B C D E F G H I J K L M N O P Q R S

1 № измерения о Р sf н о ^ m д <N Д S S S S ûo S .S. .S. Д Д .S.

Е ^ g й о 3 « т U и « С К « m о « m с « u С « С с « m о « о О « < « en К ti « с О m m s m m с с m F m m

2 11 133 7 14 4 10 5 10 4 8 6 7 11 4 9 10 11 10 3

3 7 153 7 14 7 10 6 11 8 9 6 7 11 7 10 11 10 11 8

20 1 239 13 25 8 18 10 21 5 15 12 12 19 8 15 17 18 19 4

21 6 239 12 24 9 17 10 21 8 14 11 12 18 9 15 15 17 20 7

Таблица 3.2. Значения модулей статистики по факторам

Код фактора КГГ КНП КОВ КПВ КПГ КПП КСЭ КФС

Q 0,10 0,15 -0,18 0,14 -0,20 -0,43 0,52 0,06

ДАМ ДДК ДМК ДОЗ ДПК ЭВФ ЭНИ ЭПП ЭЭЧ

Q -0,04 0,10 0,25 -0,18 0,18 0,45 0,40 -0,15 0,38

Рис. 2. Схема расчета ошибки и однородности выборки

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

Литература

1. Громов Е.И., Гладилин А.В., Герасимов А.Н. Эконометрика. М.: Феникс, 2011. 304 с.

2. Орлов А.И. Прикладная статистика. М.: Экзамен, 2006. 672 с.

3. Лялин В. С., Зверева И. Г., Никифорова Н. Г. Название: Статистика. Теория и практика в Excel. Издательство: Финансы и статистика, Инфра-М, 2010. 448 с.

4. Гмурман В. Е. Теория вероятностей и математическая статистика. М.: Юрайт, 2011. 480 с.

References

1. Gromov E.I., Gladilin A.V, Gerasimov A.N. Econometrics. MM: Phoenix, 2011. 304 pp.

2. Orlov AI Applied Statistics. M.: Examination, 2006. 672 pp.

3. Lyalin VS, Zverev, IG, Nikiforov N. Title: Statistics. Theory and practice in Excel. Publisher: Finance and Statistics, Infra-M, 2010. 448 pp.

4. Gmurman VE Probability and Mathematical Statistics. MM: Yurayt, 2011. 480 pp.