В. Р. Бараз, В. Ф. Пегашкин

Министерство образования и науки Российской Федерации
Уральский федеральный университет
имени первого Президента России Б.Н. Ельцина






В. Р. БАРАЗ, В. Ф. ПЕГАШКИН



ИСПОЛЬЗОВАНИЕ MS EXCEL
ДЛЯ АНАЛИЗА СТАТИСТИЧЕСКИХ ДАННЫХ

Рекомендовано методическим советом ГОУ ВПО УрФу
в качестве учебного пособия для студентов,
обучающихся по направлениям подготовки
100700
– Коммерция (торговое дело,
150100 – Материаловедение и технология материалов
150400 – Металлургия

2-е издание, переработанное и дополненное



Нижний Тагил
2014

2
УДК 331.16:004.67
ББК С Б
Р е цензе н ты
кафедра технологии металлов Уральского государственного лесотехнического университета завкафедрой профессор, др техн. наук Б.А. Потехин); завлабораторией вычислительной техники Института материаловедения и металлургии Уральского федерального университета ММ. Розенбаум
Научный редактор доцент, канд. техн. наук СИ. Паршаков
Бараз, В.Р.
Б24 Использование MS Excel для анализа статистических данных : учеб. пособие / В. Р. Бараз, В. Ф. Пегашкин; М-во образования и науки РФ ФГАОУ ВПО «УрФУ им. первого Президента России Б.Н.Ельцина», Нижнетагил. техн. ин-т (филиале изд, перераб. и доп. − Нижний Тагил : НТИ (филиал) УрФУ, 2014. – 181 с.

Предназначено для ознакомления с теоретическими положениями и приобретения практических навыков при изучении курса "Статистика" с использованием программы MS Excel. Рассмотрено большое количество примеров по обработке статистической информации. Рекомендовано для студентов, обучающихся по направлению 100700 – Коммерция (торговое дело, а также для студентов других экономических и технических специальностей, изучающих соответствующие разделы курсов Статистика и Организация эксперимента.
Библиогр. 6. Рис. 71. Табл. 21. Прил. 7.
УДК 331.16:004.67
ББК С


Бараз В. Р, Пегашкин В. Ф, 2014

3
ОГЛАВЛЕНИЕ
ВВЕДЕНИЕ ........................................................................................................................ 5 1. ВЫБОРОЧНЫЙ МЕТОД СТАТИСТИЧЕСКОГО АНАЛИЗА ............................... 10 1.1. Измерение .............................................................................................................. 10 1.2. Понятие о выборном исследовании .................................................................... 15 1.3. Основные определения. 16 1.4. Репрезентативность выборки. 17 1.5. О выборочном распределении ............................................................................. 19 1.6. Стандартная ошибка как оценка стандартного отклонения ............................. 20 1.7. О доверительной вероятности и доверительном интервале. Понятие о предельной ошибке ...................................................................................................... 25 1.8. Критерий Стьюдента ............................................................................................ 28 1.9. Необходимое число измерений (оптимальный объем выборки) ..................... 30 1.10. Случайная выборка ............................................................................................. 37
1.10.1. Таблица случайных чисел ............................................................................. 37

1.10.2. Метод механического отбора .................................................................... 40

1.11. Компьютерное формирование выборочной совокупности ............................ 41
1.11.1. Повторный отбор ........................................................................................ 41

1.11.2. Бесповторный отбор ................................................................................... 45

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

1.12.2. Нахождение грубого промаха .................................................................... 54

1.13. Построение гистограмм ..................................................................................... 59 2. КОРРЕЛЯЦИОННАЯ СВЯЗЬ И ЕЕ СТАТИСТИЧЕСКОЕ ИЗУЧЕНИЕ В КОММЕРЧЕСКОЙ ДЕЯТЕЛЬНОСТИ ......................................................................... 68 2.1. Типы зависимостей ............................................................................................... 68 2.2. Методы определения корреляционной связи .................................................... 72 2.3. Расчет коэффициента парной корреляции и его статистическая проверка .... 73 2.4. О ложной корреляции (влияние "третьего фактора) ....................................... 79 2.5. Измерение степени тесноты связи между качественными признаками ранговая корреляция) ................................................................................................. 80 3. РЕГРЕССИОННЫЙ МЕТОД ОЦЕНКИ КОММЕРЧЕСКОЙ ДЕЯТЕЛЬНОСТИ 86 3.1. Аппроксимационные модели ............................................................................... 87 3.2. Выбор формул лучшего вида ............................................................................... 88 3.3. Метод наименьших квадратов ............................................................................. 90 3.4. Поиск уравнения регрессии ................................................................................. 93 3.4.1. Использование традиционных способов расчета ....................................... 94 3.4.2. Расчет с использованием компьютерной программы ................................ 99 3.5. Компьютерный подбор оптимального уравнения регрессии ......................... 101 4. МНОЖЕСТВЕННАЯ РЕГРЕССИЯ ......................................................................... 110 4.1. Расчет коэффициентов регрессии и представление уравнения множественной регрессии. 112

4 4.2. Интерпретация коэффициентов регрессии ...................................................... 116 4.3. Ошибки прогнозирования (определение качества регрессионного анализа) ... 117 4.4. Проверка значимости модели ............................................................................ 119
4.4.1. Проверка на адекватность уравнения регрессии ..................................... 119

4.4.2. Проверка на адекватность коэффициентов регрессии .......................... 122

4.5. Сравнительная оценка степени влияния факторов ......................................... 123 5. АНАЛИЗ «ХИ-КВАДРАТ»: ПОИСК ЗАКОНОМЕРНОСТЕЙ ДЛЯ КАЧЕСТВЕННЫХ ДАННЫХ ..................................................................................... 125 5.1. Комбинация нынешние и прошлые события (критерий «хи-квадрат» соответствия) .............................................................................................................. 125 5.2. О коэффициентах взаимной сопряженности ................................................... 136 5.3. Проверка взаимосвязи между двумя качественными переменными критерий «хи-квадрат» независимости) ................................................................. 137 6. СТАТИСТИЧЕСКИЕ МЕТОДЫ ИЗУЧЕНИЯ ДИНАМИЧЕСКИХ ПРОЦЕССОВ ......................................................................................................................................... 146 6.1. Понятие о статистических рядах динамики ..................................................... 146 6.2. Изучение основной тенденции развития .......................................................... 148 6.3. Общее описание динамического процесса ....................................................... 153 6.4. Вычисление скользящего среднего ................................................................... 156 6.5. Анализ сезонных колебаний .............................................................................. 161 6.6. Поправка на сезонный фактор ........................................................................... 164 6.7. Долгосрочный тренд и прогноз с поправкой на сезонность .......................... 168 6.8. Прогноз тренд с учетом сезонности ................................................................ 170
БИБЛИОГРАФИЧЕСКИЙ СПИСОК .......................................................................... 173
ПРИЛОЖЕНИЯ Статико-математические таблицы ................................................. 174

5
ВВЕДЕНИЕ
Статистика – в высшей мере логичный и точный метод, позволяющий весьма уклончиво формулировать полуправду
Из постулатов НАСА Если мой сосед бьет жену каждый день, а я никогда, то сточки зрения статистики мы оба бьем своих жен через день
Бернард Шоу

Статистика
(немец. Statistik, от латинского status

состояние) рассматривается как наука о методах изучения массовых явлений. Некоторые процессы, наблюдаемые в массовом количестве, обнаруживают определенные закономерности, которые, однако невозможно заметить в отдельном случае или же при небольшом числе наблюдений. Можно дать и иную формулировку статистика

это наука, занимающаяся сбором и анализом данных о событиях, носящих массовый характер. При этом подданными принято понимать любой вид зарегистрированной
информации. Явления, которые в случае событий массового характера отличаются определенной закономерностью, однако не обнаруживаются на основе единичного наблюдения, называются массовыми явлениями. Сама такая закономерность называется статистической закономерностью.
Статистическая закономерность возникает в тех случаях, когда а) вис- следуемом процессе действует один общий комплекс причини когда б) наряду с этим в каждом отдельном случае действуют особые дополнительные причины, всякий раз иные. При этом сами причины, которые определяют массовые процессы, принято делить на две категории

основные причины, которые действуют во всех случаях

побочные (вторичные) причины, которые проявляются только в от-
дельных случаях. Скажем, возрастное старение человека определяется его биологической конституцией, социальными условиями. Все это, конечно, отражается на продолжительности жизни. Понятно, что названные факторы создают комплекс основных причин. Однако мы понимаем, что в жизни конкретного

6 человека добавляется множество дополнительных частных причин (неожиданная болезнь, стрессы, несчастный случай и проч, которые порой самым прискорбным образом могут повлиять на его фактическую продолжительность жизни. Если бы имели место только основные причины, то закономерность была бы абсолютной (те. для каждого элемента статистического массива одинаковой. Тогда еѐ можно было бы уловить в каждом отдельном случае. Так, все люди жили бы одинаковое число лет. Вместе стем если бы действовали только второстепенные причины, отличные для каждого случая, тони- какой закономерности не было бы и воцарился бы полный хаос.
Таким образом, статистическая закономерность имеет место тогда, когда существует сочетание основных и побочных причин. При этом можно добавить, что основные причины обусловливают само
существование такой закономерности, а побочные причины определяют еѐ
приблизительность. Иначе говоря, закономерность проявляется только в массе случаев, а отдельный случай может отклоняться от общей картины. Можно полагать, что закономерность, вытекающая из постоянного действия основных причин, пробивается сквозь действие разнородных побочных факторов. Из сказанного становится понятным, что статистика оказывается полезной в тех случаях, когда приходится анализировать процессы, которые при массовом наблюдении способны проявлять очевидную закономерность. Если бы действовали только главные причины, те. без наложения второстепенных, то все отдельные случаи происходили бы совершенно одинаково и не было бы нужды анализировать всю их массу. Достаточно было бы взять один из случаев и на его основе сделать выводы, относящиеся уже ко всей исследуемой совокупности. Так, кстати сказать, поступают во многих науках. Например, в химии полагают, что капля воды похожа на другую. Проводят анализ одной пробы воды и на его основе делают обобщение относительно химического состава воды. Похожим образом примерно действуют в биологии или анатомии. Например, исследуется анатомическое строение одной собаки, и делаются выводы об анатомическом строении всех собак. Там же, где закономерность пробивается через результаты воздействия побочных причин, приходится изучать уже целую массу случаев, чтобы иметь возможность выявить закономерность. В такой ситуации исследование единичного примера может привести к ложным заключениям. В массовых процессах обычно различают два элемента систематиче-
ский (постоянный) и случайный (побочный). Систематический элемент явля-

7 ется результатом действия основных причин, случайный элемент

это следствие действия побочных причин (действуют по-разному в каждом отдельном случае. Статистическая закономерность проявляться более отчетливо в случае действия закона больших чисел. Этот закон отражает закономерности, присущие случайным событиям массового характера. При большом количестве наблюдений влияние случайных факторов взаимно уравновешивается и вступают в действие главные причины, которые отражаются в некотором по-
стоянстве средних чисел.
Например, каждый покупатель в магазине выбирает именно тот товар, который в данный момент ему нужен. Нов целом по магазину можно сравнительно точно предвидеть общий объем спроса, его структуру за год, отдельные сезоны и даже дни недели. Для выявления конкретных закономерностей покупательского спроса и нужна статистическая информация, отображающая специфику спроса по дням недели, времени года ив целом за год. Для выполнения закона больших чисел важно соблюсти определенные условия.
1. Исследуемый массив должен быть однородным, те. быть одинакового качества. Это означает, что все элементы массива подпадают под действие одних и тех же основных причин. В противном случае могут возникнуть иные основные факторы и тогда общую картину выявить окажется невозможным. Однородна ли данная статистическая масса

этого нельзя установить на основе статистического исследования. Для этого нужен качественный анализ, который проводится методами, применяемыми в соответствующих областях науки (физические, экономические и т.д.).
2. Побочные причины, воздействующие на разные элементы массива, должны быть независимыми друг от друга или же мало зависимыми. Таким образом, не может быть хорошей статистики там, где нет достаточно а) многочисленных, б) однородных ив) независимых данных. Если это условие не соблюдено, то отсутствует и подлинная статистика. В курсе общей теории статистики принято условно различать описательную статистику и аналитическую. Описательная статистика преимущественно связана с планированием исследования, сбором информации и представлением полученных результатов в виде статистических показателей. Удобная форма представления статистической информации

таблицы, графики. Задача аналитической статистики

выявить причинные связи, оценить влияние исследуемых факторов и сделать надлежащие выводы, на ос-

8 новании которых могут быть приняты ответственные решения. Часто исследуемый процесс представляется в аналитической форме, те. в виде уравнения (эмпирической формулы. Знание статистики помогает нам принять хорошие, лучше сказать, оптимальные решения. При этом статистика отнюдь не отвергает опыт и интуицию. Еѐ можно рассматривать как один из компонентов процесса принятия решения, но отнюдь не весь процесс. Поэтому оправданно полагать, что статистика дополняет, ноне заменяет деловой опыт, здравый смысли интуицию. И, наконец, не следует забывать

использование статистики становится все более важным преимуществом в конкуренции. Мощным инструментальным средством при выполнении статистических исследований является использование компьютерной техники. В этой связи широкое распространение в деловой сфере (понимай – в коммерческой деятельности) получили специальные пакеты прикладных программ. Они позволяют обеспечить весьма впечатляющую быстроту статистических расчетов, высокую надежность и достоверность результатов, возможность легко представлять данные в аналитической, графической или табличной формах. Среди подобных программ большой известностью пользуется приложение, которое включает в себя программную надстройку "Пакет анализа" и богатую библиотеку из большого числа статистических функций.
Основное назначение данного учебного пособия – познакомить студентов с поразительными возможностями этого весьма полезного приложения и показать, как его удобно применять для выполнения достаточно стандартных статистических расчетов применительно к работе в деловой сфере. Таким образом, оно адресовано, прежде всего, студентам, обучающимся по специальностям "Коммерция (торговое дело, "Мировой рынок сырья и металлов. Вместе стем методический способ изложения материала, приводимые практические примеры носят достаточно общий характер. Поэтому данное пособие может оказаться пригодным для студентов и других специальностей, изучающих в соответствующих учебных дисциплинах методы статистического анализа данных. Первое издание учебного пособия "Использование MS Excel для анализа статистических данных" было выпущено в свет в 2007 году. Стой поры накоплен определенный опыт, позволяющий на основании мнения студенческой аудитории, а также университетских коллег судить о пригодности и полезности данного пособия в качестве учебного материала при изучении приемов статистического исследования. При этом авторы сочли возможным вве-

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

Основное содержание данного пособия состоит не только в развернутом изложении известных методов статистического исследования, но ив описании приемов применения в практике коммерческой деятельности выборочного метода, корреляционно-регрессионного анализа, а также динамических рядов и способов перспективного прогнозирования. Каждая глава пособия условно поделена на две части. Первая часть содержит изложение основных положений, касающихся рассмотрения соответствующего раздела теории статистики. Вторая часть главы – это практикум, где мы, что называется, засучив рукава, уже наделе применяем усвоенные теоретические положения, используя впечатляющие возможности компьютерной программы Excel. Следует сделать одно замечание. Данное пособие вовсе не претендует на подробное и последовательное изложение всех положений общей теории статистики. Предполагается, что студент знаком с содержательной стороной этой дисциплины и вполне владеет каноническими сведениями из теории статистики (методы группировок, абсолютные и относительные величины, средние величины, показатели вариации, ряды распределения и ряды динамики, измерение связи и т.д.). Поэтому если в пособии и приводятся подробные описания некоторых теоретических положений, то они излагаются для того, чтобы дать более понятное объяснение использования компьютерной технологии при статистическом анализе сугубо практических ситуаций. Предложенные для рассмотрения примеры по своему содержанию намеренно носят довольно иронично-шутливый характер. Поэтому избыточно серьезный читатель, а тем более достаточно въедливый, легко найдет в этом очевидные изъяны. Однако использование такого методического подхода преследовало вполне понятную цель – в легкой и по возможности непринужденной манере попытаться рассказать о вещах, в общем-то, довольно скучных, если не сказать просто занудных, однако не теряющих от этого свою несомненную важность и очевидную полезность.

1. ВЫБОРОЧНЫЙ МЕТОД СТАТИСТИЧЕСКОГО АНАЛИЗА
Работая над решением задачи, всегда полезно знать ответ.
Закон Мэрфи

Коля ловил девчонок, окунал их в лужу и старательно измерял глубину погружения каждой девчонки, а Толя стоял рядышком и смотрел, как девчонки барахтаются. Чем отличаются Колины действия от Толиных и как такие действия называют физики И физики и химики назовут Колины и То- лины действия хулиганством и надают им по шее. Однако сточки зрения бесстрастной науки Толя производил наблюдение, а Коля ставил опыты.
Григорий Остер. "Сборник задач по физике"

Как было отмечено, цель статистического исследования состоит в отыскании определенных закономерностей в событиях массового характера, каждое из которых по отдельности имеет достаточно случайное проявление. Для достижения этой цели используются специальные статистические приемы, основанные на реализации так называемого выборочного изучения. Познакомимся с основными его положениями.
1.1. Измерение

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

11 выражении, вторая присуща специально созданному объекту – эталону или мере. Сравнение этих объектов сводится к сопоставлению их размеров, следовательно, основывается на выявлении их количественного соотношения. При этом сравниваемые величины должны быть однородными, те. имеющими сходную физическую природу, одинаковую размерность. В качестве эталона могут применяться самые различные меры, порой весьма своеобразные. Так, в старину в качестве меры для определения расстояния служил локоть (это примерном, те. длина локтевой кости человека. Можно привести и просто забавный пример. В известном детском мультфильме "38 попугаев" Мартышка, Слоненок и Попугай измеряли длину Удава. А в качестве эталона использовали самих себя, что позволило потом Удаву горделиво заключить "А в попугаях-то я длиннее" Словом, говоря философски, можно измерять в чем угодно, было бы только что измерять. При экспериментальном определении какой-либо величины приходится сталкиваться стем, что параллельные измерения не дают одинаковых результатов даже при самой тщательной подготовке опыта. Это обстоятельство является следствием того, что на процесс измерения и, стало быть, на его результат оказывает влияние огромное число факторов (начиная от погоды, температуры, степени изношенности оборудования, измерительного инструмента и кончая эмоциональным состоянием экспериментатора в момент измерения. Влияние каждого фактора в отдельности может быть в целом совершенно ничтожным, нов совокупности они способны вызывать случайные (и потому непредсказуемые) отклонения измеряемой величины от ее истинного значения. Это означает, что при проведении повторных измерений одной и той же физической величины мы получим в итоге несколько отличающиеся друг от друга результаты.

Таким образом, измеренное значение определяется, с одной стороны, влиянием основных факторов, ас другой, параметрами, обусловленными случайными причинами. Измерения принято делить на прямые и косвенные. Основным признаком является вид уравнения измерения, связывающее измеряемую (искомую) величину и непосредственно наблюдаемую (эталон.
Прямые измерения – измеряемая величина А пропорциональна непосредственно наблюдаемой В, те. получается непосредственно с помощью измерительного прибора (используется непосредственный счет единиц наблюдения. Аналитически такое измерение можно представить в виде линейного соотношения А = кВ, где к – заданный коэффициент.

12 В качестве примера можно указать измерение массы на циферблат- ных или равноплечных весах или измерение температуры термометром. Получаемые данные – это абсолютные значения.
Косвенные измерения – в этом случае измеряемая величина А является известной функцией непосредственно наблюдаемого аргумента В и определяется в результате математических действий над результатами прямых измерений. Это значит, что на основании результатов изучения одного процесса с использованием известной аналитической зависимости (уравнения) получаются сведения о другом. Типичный пример измерение плотности твердого тела по его массе и геометрическим размерам. Указанное соотношение имеет следующий вид А=f(B); получаемые данные являются относительными величинами. Обычно удается провести ограниченное число параллельных измерений или получить случайную выборку (те. конкретный набор экспериментальных данных) из генеральной совокупности (всѐ мыслимое количество повторных измерений. В этом случае задача исследователя состоит в том, чтобы по такой выборке (те. на основе знания части целого) получить истинное значение (или так называемое математическое ожидание) самого целого (генеральной совокупности. В связи с этим задача статистической обработки сводится к следующему. Отыскать истинное значение измеряемой величины х , однако в большинстве случаев оно оказывается неизвестным. Поэтому его заменяют некоторым приближенным значением, которое наиболее вероятно соответствует истинному значению. В статистике показано, что этому условию наиболее полно отвечает среднее арифметическое

х выборочной совокупности. Оценить погрешность (ошибку)

х, с которой найдена эта истинная величина иными словами, нужно определить ту величину, на которую отличается приближенное значение

х от истинного х .