Макарова Н.В. Статистика в Excel-1

где Хонижняя граница модального интервала; / - величина модального интервала;

/мо ^ частота модального интервала;

fhio-x "~ частота интервала, предшествующего модальному; /мо+1 — частота интервала, следующего за модальным.

В табл. 4.8 модальным интервалом продолжительности стажа сотрудников научно-исследовательского центра (НИЦ) является интервал 8—10 лет, а модой продолжительности стажа — 8,77 лет.

Ячейка С9 содержит формулу =ЛЕВСИМВ(ИНДЕКС(ВЗ:В8;

ПОИСКПОЗ(МАКС(СЗ:С8);СЗ:С8;0);1);1)+2*((МАКС(СЗ:С8Ь --ИНДЕКС(СЗ:С8;ПОИСКПОЗ(МАКС(СЗ:С8);СЗ:С8;0)-1; 1))/((МАКС(СЗ:С8ЬИВДЕКС(СЗ:С8;ПОИСКПОЗ(МАКС(СЗ:С8); СЗ:С8;0)~1;1))+(МАКС(СЗ:С8)~ИНДЕКС(СЗ:С8;ПОИСКПОЗ(МАКС (СЗ:С8); СЗ:С8;0)+1;!)))).

Безусловно, представленная формула слишком фомоздка и непонятна. Она приведена только лишь для демонстрации того факта, что, работая с Microsoft Excel, можно обойтись без проме­ жуточных вычислений на рабочем листе и заключить все расчеты в одну формулу. Но даже если в этом и есть необходимость, сове-

60

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

С целью пояснения представленной формулы рассмотрим табл, 4.9, функционально адекватную табл. 4.8.

Содержимое ячеек в табл. 4.9:

•ячейка С9 содержит формулу =МАКС(СЗ:С8) — рассчитыва­ ется модальная численность сотрудников;

•ячейка СЮ содержит формулу =ПОИСКПОЗ(С9;СЗ:С8;0) -

вмассиве СЗ:С8 вычисляется смещение на модальное значение;

•ячейка СП содержит формулу =ИНДЕКС(ВЗ:В8;С10;1) - в массиве ВЗ:В8 находится модальный интервал стаж;

•ячейка С12 содержит формулу =ЛЕВСИМВ(С11;1) — отоб­ ражается нижняя фаница модального интервала стажа;

•ячейка С13 содержит формулу =ИНДЕКС(СЗ:С8;С10-1;1) -

вмассиве СЗ:С8 находится число сотрудников с предшествующим стажем;

•ячейка С14 содержит формулу =ИНДЕКС(СЗ:С8;С10+1;1) -

вмассиве СЗ:С8 находится число сотрудников с последующим стажем;

•ячейка С15 содержит формулу ==С12+2*((С9-С13)/((С9- -С13)+(С9-С14))) - рассчитывается мода продолжительности стажа.

•В примере 4.1 значение моды (показатель Мода в табл. 4.2) рассчитывается по формуле =МОДА(В2:В10).

61

Функция СТАВДОТКЛОН

См, также ДИСП, КВАДРОТКЛ, СРОТКЛ, СТАНДОТКЛОНА, СТАНДОТКЛОНП,

Синтаксис:

СТАНДОТКЛОН (число!; число2;...)

Результат:

Оценивает генеральное стандартное отклонение по выборке.

Аргументы:

число 1, нисло2,...; от 1 до 30 аргументов, соответствующих вы­ борке из генеральной совокупности.

Замечания:

• функция СТАНДОТКЛОН предполагает, что аргументы являются выборкой из генеральной совокупности. Если дан-

62

ные представляют всю генеральную совокупность, то стан­ дартное отклонение следует вычислять с помощью функции СТАНДОТКЛОНП;

• логические значения, такие, как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текстовые и логические значе­ ния игнорироваться не должны, следует использовать функцию СТАНДОТКЛОНА.

Математико-статистинеская интерпретация:

См, описание функций ДИСП и СТАВДОТКЛОНП. Внимание! Функция СТАНДОТКЛОН рассчитывает гене­

ральное стандартное отклонение при условии, что исходные дан­ ные образуют выборочную совокупность. Если совокупность яв­ ляется генеральной, необходимо воспользоваться функцией СТАНДОТКЛОНП.

Используя выборочные данные, приведенные в табл. 4Л0, по формуле =СТАНДОТКЛОН(С4:С9) получим стандартное откло­ нение 94,66 (сравните со значением 86,41, вычисляемым функ­ цией СТАНДОТКЛОНП).

• В примере 4.1 значение стандартного отклонения (показа­ тель Стандартное отклонение в табл. 4.2) рассчитывается по фор­ муле =СТАНДОТКЛОН(В2:В10).

Таблица 4.10

В

Сбор зерна по некоторым хозяйствам района

63

в режиме «Описательная статистика» функция СТАНДОТКЛОН совместно с функцией СЧЕТ используется также для оп­ ределения средней ошибки выборки ц^ (показатель Стандартная ошибка в табл. 4.2).

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

а

где о — оценка генерального стандартного отклонения; п — объем выборочной совокупности.

• В примере 4.1 значение средней ошибки выборки (показатель Стандартная ошибка в табл. 4.2) рассчитывается по формуле -В20/КОРЕНЬ(В28),

где в ячейке В20 — значение оценки генерального стандартного от­ клонения, рассчитываемого по формуле =СТАНДОТКЛОН (В2:В10); в ячейке В28 - значение объема выборки, рассчитываемого по

формуле -СЧЕТ(В2:В10).

Средняя ошибка выборки \i^ используется для расчета пре­ дельной ошибки выборки А^ (показатель Уровень надежности в табл. 4.2), которая дает возможность выяснить, в каких пределах находится величина генеральной средней.

В математической статистике установлено, что предельная ошибка выборки А^ связана со средней ошибкой выборки \х^ со­ отношением

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

64

в Microsoft Excel коэффициент доверия /рассчитывается через функцию СТЬЮДРАСПОБР (см, подразд. 6.3.8), в которой в ка­ честве аргументов задаются уровень значимости а и число степе­ ней свободы к. Уровень значимости а связан с доверительной вероятностью у (задается в поле Уровень надежности диалогового окна Описательная статистика, рис. 4.1) выражением а = 1 - у. Число степеней свободы к зависит от объема выборки п и связано

сним выражением к — п — 1.

•В примере 4.1 значение предельной ошибки выборки с уровнем надежности 95% (показатель Уровень надежности в табл. 4,2) рассчи­ тывается по формуле =В17*СТЬЮДРАСПОБР(0,05;В28-~1),

где в ячейке В17 — значение средней ошибки выборки, рас­ считываемое по формуле =В20/КОРЕНЬ(В28)); в ячейке В28 - значение объема выборки, рассчитываемое по формуле =СЧЕТ(В2:В10).

Внимание! В раздел статистических функций Microsoft Excel для вычисления значения предельной ошибки выборки включе­ на также и функция ДОВЕРИТ {см, подразд. 6.3.1). Данную функцию можно использовать при сравнительно большом числе единиц выборочной совокупности {п > 100), когда расхождение между средней выборки и генеральной средней становится прак­ тически несущественным (распределение Стьюдента приближа­ ется к нормальному распределению). Для малых выборок это расхождение может быть весьма существенным, поэтому для рас­ чета предельной ошибки выборки в этом случае необходимо пользоваться не нормальным распределением (функцией ДОВЕ­ РИТ), а распределением Стьюдента (функцией СТЬЮДРАС­ ПОБР).

Функция ДИСП

См, также ДИСПА, ДИСПР, КВАДРОТКЛ, СРОТКЛ, СТАНДОТКЛОН.

Синтаксис:

ДИСП (число1; число2;...)

Результат:

Оценивает генеральную дисперсию по выборке,

65

^^гументы:

число 1, число2,...: от 1 до 30 аргументов, соответствующих вы­ борке из генеральной совокупности.

Замечания:

•логические значения, такие, как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если они не должны игнорироваться, пользуйтесь функцией ДИСПА;

•функция ДИСП предполагает, что аргументы являются вы­ боркой из генеральной совокупности. Если данные представляют всю генеральную совокупность, вычисляйте дисперсию, исполь­ зуя функцию ДИСПР.

Математико-статистическая интерпретация:

См. описание функции ДИСПР.

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

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

п

Для устранения систематической ошибки и получения несме­ щенной оценки нужно a^g умножить на п/{п—\). Тогда при ма­ лом числе наблюдений (особенно при п < 40—50) дисперсию

^GEN С-^ЗДУ^Т вычислять ПО формуЛС

п-\

Поскольку значение п/{п—\) при достаточно больших п близко к 1 (при п = 100 значение п/{п~\) = 1,01, а при п = 500 зна­ чение п/{п ~ 1) = 1,002 и т. д.), можно приближенно считать, что выборочная дисперсия равна генеральной дисперсии, т. е.

66

Используя выборочные данные, приведенные в табл. 4.10, по формуле =ДИСП(С4:С9) получим оценку генеральной диспер­ сии 8960.

Внимание! Функция ДИСП рассчитывает генеральную дис­ персию при условии, что исходные данные образуют выборочную совокупность. В случае если совокупность является генеральной, необходимо воспользоваться функцией ДИСПР. Так, предполо­ жив, что исходные данные в ячейках С4:С9 образуют генеральную совокупность, и применив функцию ДИСПР, получим значение генеральной дисперсии, равное 7466,67.

• В примере 4.1 значение дисперсии (показатель Дисперсия выборки втабл. 4.2) рассчитывается по формуле ==ДИСП(В2:В10).

Функция ЭКСЦЕСС

См, также СКОС.

Синтаксис:

ЭКСЦЕСС (число!; число2;...)

Результат:

Оценивает эксцесс по выборке.

Аргументы:

число!, число!, ...; от 1 до 30 аргументов, для которых вычис­ ляется эксцесс.

Замечания:

•аргументы должны быть числами или именами, массивами или ссылками, содержащими числа;

•если аргумент, который является массивом или ссылкой, со­ держит текстовые, логические значения или пустые ячейки, то такие значения игнорируются, однако ячейки с нулевыми значе­ ниями учитываются;

•если задано менее четырех точек данных или если стандарт­ ное отклонение выборки равняется нулю, то функция ЭКС­ ЦЕСС помещает в ячейку значение ошибки #ДЕЛ/0!.

Математико-статистическая интерпретация:

Эксцесс характеризует так называемую «крутость», т. е. остро­ вершинность или плосковершинность распределения. Он может быть рассчитан дпя любых распределений, но в большинстве слу-

67

чаев вычисляется только для симметричных. Это объясняется тем, что за исходную принята кривая нормального распределения (Ei^ — 0), относительно вершины которой и определяется выпад вверх или вниз вершины эмпирического распределения. Функция ЭКСЦЕСС рассчитывает значение эксцесса как для симметрич­ ных, так и для асимметричных распределений.

Наиболее точным и распространенным является определение эксцесса, основанное на расчете центрального момента 4-го по­ рядка:

а

Применение данной формулы дает возможность вычислить значение эксцесса в генеральной совокупности. При этом если Е^ > О, распределение островершинное (рис. 4.3 а), если Е^ <0 — плосковершинное (рис. 4.3 б).

Необходимо отметить, что функция ЭКСЦЕСС определяет значение эксцесса по выборочной совокупности, поэтому в ней ре­ ализована формула

гтп-- объем выборки.

68

Рассмотрим расчет эксцесса по выборочным данным, пред­ ставленным в табл. 4.11.

Таблица 4Л1

Ячейка D12 содержит формулу =ЭКСЦЕСС(03:011).

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

Содержимое ячеек в табл. 4.12:

•ячейка С14 содержит формулу =СУММ(С4:С13) — рассчи­ тывается общее количество абитуриентов;

•ячейка С15 содержит формулу {=СУММПРОИЗВ(В4:В13; С4:С13)/С14} ~ определяется средний балл сдачи экзаменов;

•ячейка С16 содержит формулу {=СУММПРОИЗВ(СТЕ- ПЕНЬ(В4:В13--С15;4);С4:С13)/С14} - вычисляется центральный момент 4-го порядка;

•ячейка С17 содержит формулу {=КОРЕНЬ(СУММПРО- ИЗВ(СТЕПЕНЬ(В4:В13-С15;2);С4:С13)/С14)} -~ рассчитыва­ ется стандартное отклонение;

69