Как посчитать размах в excel
Обновлено: 04.07.2024
Размах варьирования. Наибольшее и наименьшее значения
Лабораторная работа № 1
Статистический анализ данных
Цель работы: научиться обрабатывать статистические данные с помощью встроенных функций.
Порядок выполнения работы:
1. Основные статистические характеристики:
2. Самостоятельная работа
1. Основные статистические характеристики.
Электронные таблицы Excel имеют огромный набор средств для анализа статистических данных. Наиболее часто используемые статистические функции встроены в основное ядро программы, то есть эти функции доступны с момента запуска программы.
Среднее значение.
Функция СРЗНАЧ (или AVERAGE) вычисляет выборочное (или генеральное) среднее, то есть среднее арифметическое значение признака выборочной (или генеральной) совокупности. Аргументом функции СРЗНАЧ является набор чисел, как правило, задаваемый в виде интервала ячеек, например, =СРЗНАЧ (А3:А201).
Дисперсия и среднее квадратическое отклонение.
Для оценки разброса данных используются такие статистические характеристики, как дисперсия D и среднее квадратическое (или стандартное) отклонение . Стандартное отклонение есть квадратный корень из дисперсии: . Большое стандартное отклонение указывает на то, что значения измерения сильно разбросаны относительно среднего, а малое – на то, что значения сосредоточены около среднего.
В Excel имеются функции, отдельно вычисляющие выборочную дисперсию Dв и стандартное отклонение в и генеральные дисперсию Dг и стандартное отклонение г. Поэтому, прежде чем вычислять дисперсию и стандартное отклонение, следует четко определиться, являются ли ваши данные генеральной совокупностью или выборочной. В зависимости от этого нужно использовать для расчета Dг и г , Dв и в.
Для вычисления выборочной дисперсии Dв и выборочного стандартного отклонения в имеются функции ДИСП (или VAR) и СТАНДОТКЛОН (или STDEV). Аргументом этих функций является набор чисел, как правило, заданный диапазоном ячеек, например, =ДИСП (В1:В48).
Для вычисления генеральной дисперсии Dг и генерального стандартного отклонения г имеются функции ДИСПР (или VARP) и СТАНДОТКЛОНП (или STDEVP), соответственно.
Аргументы этих функций такие же как и для выборочной дисперсии.
Объем совокупности.
Объем совокупности выборочной или генеральной – это число элементов совокупности. Функция СЧЕТ (или COUNT) определяет количество ячеек в заданном диапазоне, которые содержат числовые данные. Пустые ячейки или ячейки, содержащие текст, функция СЧЕТ пропускает. Аргументом функции СЧЕТ является интервал ячеек, например: =СЧЕТ (С2:С16).
Для определения количества непустых ячеек, независимо от их содержимого, используется функция СЧЕТ3. Ее аргументом является интервал ячеек.
Мода и медиана.
Мода – это значение признака, которое чаще других встречается в совокупности данных. Она вычисляется функцией МОДА (или MODE). Ее аргументом является интервал ячеек с данными.
Медиана – это значение признака, которое разделяет совокупность на две равные по числу элементов части. Она вычисляется функцией МЕДИАНА (или MEDIAN). Ее аргументом является интервал ячеек.
Размах варьирования. Наибольшее и наименьшее значения.
Размах варьирования R – это разность между наибольшим xmax и наименьшим xmin значениями признака совокупности (генеральной или выборочной): R=xmax–xmin. Для нахождения наибольшего значения xmax имеется функция МАКС (или MAX), а для наименьшего xmin – функция МИН (или MIN). Их аргументом является интервал ячеек. Для того, чтобы вычислить размах варьирования данных в интервале ячеек, например, от А1 до А100, следует ввести формулу: =МАКС (А1:А100)-МИН (А1:А100).
Задание 1
Имеются данные о размерах располагаемого дохода DPI и расходов на личное потребление С для n семей в условных единицах, так что DPIi и Сi, соответственно, представляют располагаемый доход и расходы на личное потребление i-й семьи.
| Доходы_расходы | |||||
| I | DPI | C | I | DPI | C |
2. Выполнить настройку формата оси Х и оси Y в соответствии с образцом диаграммы.
Задание 2
Имеются данные об уровне безработицы (в %) среди «белого» (коренное) и «цветного» (эмигранты) населения страны с марта 2000г. по июль 2001г. (месячные данные), так что BELi и ZVETi, соответственно, представляют уровни безработицы в i-м месяце.
1. Построить графики изменения уровней безработицы в обеих группах в течение указанного периода времени.
2. Вычислить средние значения уровней безработицы для BELi и ZVETi населения страны.
| Уровень безработицы | ||
| Исходные данные | ||
| I | BEL(%) | ZVET(%) |
| 3,2 | 6,9 | |
| 3,1 | 6,7 | |
| 3,2 | 6,5 | |
| 3,3 | 7,1 | |
| 3,3 | 6,8 | |
| 3,2 | 6,4 | |
| 3,2 | 6,6 | |
| 3,1 | 7,3 | |
| 3,0 | 6,5 | |
| 3,0 | 6,5 | |
| 3,0 | 6,0 | |
| 2,9 | 5,7 | |
| 3,1 | 6,0 | |
| 3,1 | 6,9 | |
| 3,1 | 6,5 | |
| 3,0 | 7,0 | |
| 3,2 | 6,4 |
3. Вычислить выборочные дисперсии, характеризующие степень разброса значений BELi и ZVETi вокруг своего среднего значения.
4. Вычислить стандартные отклоненияBELi и ZVETi относительно среднего значения.
5. Вычислить наибольшее и наименьшее значения для BELi и ZVETi.
6. Вычислить размах варьирования дляBELi и ZVETi.
7. Вычислить Моду и Медиану дляBELi и ZVETi.
Использование Excel для расчета статистических характеристик случайной величины
Разделы: Математика
- Совершенствование умений и навыков нахождения статистических характеристик случайной величины, работа с расчетами в Excel;
- применение информационно коммутативных технологий для анализа данных; работа с различными информационными носителями.
- Сегодня на уроке мы научимся рассчитывать статистические характеристики для больших по объему выборок, используя возможности современных компьютерных технологий.
- Для начала вспомним:
– что называется случайной величиной? (Случайной величиной называют переменную величину, которая в зависимости от исхода испытания принимает одно значение из множества возможных значений.)
– Какие виды случайных величин мы знаем? (Дискретные, непрерывные.)
– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).
– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).
– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).
- Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.
Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):
1. Занести данные в EXCEL, каждое число в отдельную ячейку.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.
Используя тот же путь вычисляем медиану.
Вставка – Функция – Статистические – Медиана.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.
Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.
Вставка – Функция – Статистические – МАКС.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наибольшее значение = 36.
Вставка – Функция – Статистические – МИН.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наименьшее значение = 22.
36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.
Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni |
Чтобы сосчитать частоту каждого значения воспользуемся
Вставка – Функция – Статистические – СЧЕТЕСЛИ.
В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22
Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni | 1 | 3 | 4 | 5 | 11 | 9 | 13 | 18 | 16 | 6 | 4 | 6 | 3 | 0 | 1 |
Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)
Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.
Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).
Диаграмма – Стандартные – Круговая.
Диаграмма – Стандартные – Гистограмма.
4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.
– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).
– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).
– Какие приемы используются для наглядного представления статистических характеристик случайной величины (полигон частот, круговые и столбчатые диаграммы, гистограммы).
- Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.
Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):
- моду
- медиану
- размах ряда
- построить полигон частот
- построить столбчатую и круговую диаграммы
- раскрыть смысловую сторону каждой характеристики
1. Занести данные в EXCEL, каждое число в отдельную ячейку.
| 23 | 25 | 24 | 25 | 30 | 24 | 30 | 26 | 28 | 26 |
| 32 | 33 | 31 | 31 | 25 | 33 | 25 | 29 | 30 | 28 |
| 23 | 30 | 29 | 24 | 33 | 30 | 30 | 28 | 26 | 25 |
| 26 | 29 | 27 | 29 | 26 | 28 | 27 | 26 | 29 | 28 |
| 29 | 30 | 27 | 30 | 28 | 32 | 28 | 26 | 30 | 26 |
| 31 | 27 | 30 | 27 | 33 | 28 | 26 | 30 | 31 | 29 |
| 27 | 30 | 30 | 29 | 27 | 26 | 28 | 31 | 29 | 28 |
| 33 | 27 | 30 | 33 | 26 | 31 | 34 | 28 | 32 | 22 |
| 29 | 30 | 27 | 29 | 34 | 29 | 32 | 29 | 29 | 30 |
| 29 | 29 | 36 | 29 | 29 | 34 | 23 | 28 | 24 | 28 |
2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем - статистические, в списке: МОДА
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.
Используя тот же путь вычисляем медиану.
Вставка – Функция – Статистические – Медиана.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.
Размах ряда чисел – разница между наименьшим и наибольшим возможным значением случайной величины. Для вычисления размаха ряда нужно найти наибольшее и наименьшее значения нашей выборки и вычислить их разность.
Вставка – Функция – Статистические – МАКС.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наибольшее значение = 36.
Вставка – Функция – Статистические – МИН.
В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:
Нажимаем клавишу ОК. Получили наименьшее значение = 22.
36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.
Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni |
Чтобы сосчитать частоту каждого значения воспользуемся
Вставка – Функция – Статистические – СЧЕТЕСЛИ.
В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22
Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.
| xi | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 |
| ni | 1 | 3 | 4 | 5 | 11 | 9 | 13 | 18 | 16 | 6 | 4 | 6 | 3 | 0 | 1 |
Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические - СУММА). Должно получиться 100 (количество всех фирм).
Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)
Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.
Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).
Диаграмма – Стандартные – Круговая.
Диаграмма – Стандартные – Гистограмма.
4. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.
Для вычисления квартилей в MS EXCEL существует специальная функция КВАРТИЛЬ() . В этой статье дадим определение квартилей и научимся их вычислять для выборки и для непрерывного распределения. Также вычислим интерквартильный интервал.
Квартили (Quartiles) — значения, которые делят выборку (набор значений) на четыре части, содержащие приблизительно равное количество наблюдений (по 25%).
Поясним определение квартиля на примере. Пусть имеется выборка , состоящая из 50 значений в ячейках А7:А56 (см. файл примера , лист Квартиль-выборка). Для наглядности отсортируем значения по возрастанию и построим гистограмму .
Чтобы разделить выборку на 4 части достаточно 3-х квартилей .
Первый квартиль (или нижний квартиль , Q1) делит выборку , на 2 части: примерно 25% значений в выборке меньше Q1, остальные 75% - больше. Для вычисления 1-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;1) . Для нашей выборки формула вернет значение 224. Значения 224 нет в выборке , формула произвела интерполяцию на основе 2-х соседних значений 223 и 227.
Примечание : Функция КВАРТИЛЬ.ВКЛ() появилась в MS EXCEL 2010. В более ранних версиях использовалась аналогичная ей функция КВАРТИЛЬ() .
Чтобы убедиться, что примерно 25% значений меньше, чем 224, используем формулу =СЧЁТЕСЛИ(A7:A56;" . В результате получим, что 26% меньше, чем 1-й квартиль .
Чем в выборке больше значений и меньше повторов , тем точнее деление выборки квартилями на четверти.
Примечание : Первый квартиль - это то же самое, что и 25-я процентиль . Подробнее см. статью про процентили .
Второй квартиль (или медиана , Q2) также делит выборку , на 2 равные части: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана . Для вычисления 2-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;2) или =МЕДИАНА(A7:A56)
Третий квартиль (или верхний квартиль , Q3) делит выборку , на 2 части: примерно 75% значений в выборке меньше Q3, остальные 25% - больше. Для вычисления 3-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;3) или =ПРОЦЕНТИЛЬ.ВКЛ(A7:A56;0,75)
Примечание : Третий квартиль - это то же самое, что и 75-я процентиль .
Второй аргумент функции КВАРТИЛЬ.ВКЛ() может также принимать значения 0 и 4. В первом случае функция вернет минимальное значение , во втором – максимальное .
Интерквартильный размах
Интерквартильным размахом или интерквартильным интервалом (InterQuartile range, IQR) называется разность между третьим и первым квартилями (Q3 - Q1). Интерквартильный размах является характеристикой разброса значений в выборке .
Примечание : Характеристикой разброса значений в выборке является также дисперсия и стандартное отклонение .
Интерквартильный размах , а также квартили используются при построении Блочной диаграммы , которая полезна для оценки разброса значений (variation) в небольших выборках или для сравнения нескольких выборок имеющих сходные распределения.
Подробнее о построении Блочной диаграммы см. статью Блочная диаграмма в MS EXCEL .
Квартили непрерывного распределения
Если функция распределения F (х) случайной величины х непрерывна, то 1-й квартиль является решением уравнения F(х) =0,25, второй - F(х) =0,5, а третий F(х) =0,75.
Примечание : Подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .
Если известна функция плотности вероятности p (х) , то 1-й квартиль можно найти из уравнения:
Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана (2-й квартиль ) вычисляется по формуле e μ или в MS EXCEL =EXP(μ). При μ=1, медиана равна 2,718.
Обратите внимание на точку Функции распределения , для которой F(х)=0,5 (см. картинку выше или файл примера , лист Квартиль-распределение) . Абсцисса этой точки равна 2,718. Это и есть значение 2-го квартиля ( медианы ), что естественно совпадает с ранее вычисленным значением по формуле e μ .
Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице:
Поэтому, линии квартилей ( х=квартиль ) делят площадь под графиком функции плотности вероятности на 4 равные части.
Квартили в MS EXCEL
Чтобы вычислить в MS EXCEL квартили заданного распределения необходимо использовать соответствующую обратную функцию распределения .
При вычислении квартилей в MS EXCEL используются обратные функции распределения : НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР() , ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .
Например, в MS EXCEL 1-й квартиль для логнормального распределения LnN(1;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,25;1;1) , а 3-й квартиль для стандартного нормального распределения по формуле =НОРМ.СТ.ОБР(0,75) .
Скользящее среднее используется для сглаживания краткосрочных колебаний с целью определения долгосрочного тренда. Вычислим скользящее среднее с помощью надстройки MS EXCEL Пакет анализа, формулами и с помощью линии тренда на диаграмме.
Метод скользящего среднего состоит в вычислении средних значений на основе предшествующих значений исследуемого числового ряда.
В случае усреднения за 3 периода скользящее среднее равно:
Y скол.i =(Y i + Y i-1 + Y i-2 )/3
На картинке ниже показано как вычислить в MS EXCEL скользящее среднее путем усреднения значений за три периода (за два предыдущих и один текущий).
Примечание : В англоязычной литературе для скользящего среднего используется термин Moving Average (MA) или Simple Moving Average (SMA) , а также rolling average, running average, moving mean.
Получить ряд, сглаженный методом скользящего среднего, можно с помощью надстройки MS EXCEL Пакет анализа (Analysis ToolPak) . Надстройка доступна из вкладки Данные, группа Анализ (EXCEL 2010).
В поле Интервал установим значение 3 – будем усреднять значения ряда за 3 периода. В поле Выходной интервал достаточно ввести ссылку на левую верхнюю ячейку диапазона с результатами (укажем ячейку D7).
Также поставим галочки в поле Вывод графика и Стандартные погрешности (будет выведен столбец с расчетами погрешностей, англ. Standard Errors).
Нажмем ОК.
Диаграмма
Вычисление погрешности
В столбце E, начиная с ячейки Е11, MS EXCEL разместил формулы для вычисления погрешностей (англ. Standard Errors):
=КОРЕНЬ(СУММКВРАЗН(B9:B11;D9:D11)/3)
Т.е. данная погрешность вычисляется по формуле:
Значения y i – это значения исходного ряда в период i. Значения «yi с крышечкой» - значения ряда, полученного методом скользящего среднего, в тот же в период i. Значение n равно 3, т.к. в нашем случае усреднение производится 3 периода.
Формула погрешности совпадает с выражением среднеквадратичной ошибки (англ. RMSE – Root Mean Squared Errors, квадратный корень из среднего значения квадратов ошибок), но вычисленной не для всей выборки (ряда), а только на интервале сглаживания (в нашем случае за 3 периода).
Обычно рассчитывается 2 типа ошибок: ошибка сглаживания (ошибка подгонки модели; англ. fitting errors или residuals) и ошибка прогнозирования (forecast errors).
Погрешности, вычисленные надстройкой Пакет анализа, являются ошибками прогнозирования. Эту погрешность можно использовать, чтобы рассчитать интервал предсказания (prediction interval). Про вычисление прогнозного значения и его интервала предсказания см. статью Прогнозирование методом скользящего среднего .
Отметим, что MS EXCEL вычисляет целый массив погрешностей (столбец Е), но для построения интервала предсказания необходимо только последнее значение.
Недостатком формул, получаемых с помощью Пакета анализа, является то, что при изменении количества периодов усреднения приходится перезапускать расчет, вызывая Надстройку заново.
В файле примера на листе Формулы создана форма для автоматического пересчета скользящего среднего в зависимости от количества периодов.
Значения сглаженного ряда вычисляются с помощью формулы:
= ЕСЛИ(A11
в ячейке D8 содержится количество периодов усреднения. Про функцию СМЕЩ() можно прочитать в этой статье .
Погрешности вычисляются по формуле:
= КОРЕНЬ(СУММКВРАЗН(СМЕЩ(B11;-$D$8+1;;$D$8);СМЕЩ(C11;-$D$8+1;;$D$8))/$D$8)
Выбор количества периодов усреднения для удобства осуществляется с помощью элемента управления Счетчик .
На диаграмме с помощью линии тренда можно построить график Скользящего среднего с заданным количеством периодов усреднения.
Используем данные файла примера на листе Формулы . Сначала построим ряд скользящего среднего с 5-ю периодами усреднения с помощью формул.
Теперь построим линию тренда, которая совпадет с красным графиком «Сглаженный ряд». Для этого:
• Щелкните диаграмму, чтобы выделить ее.
• Выберите ряд данных, к которому нужно добавить график скользящего среднего (синий график).
• На вкладке Макет в группе Анализ нажмите кнопку Линия тренда и выберите пункт Дополнительные параметры линии тренда.
• В открывшемся окне выберите Линейная фильтрация и установите в поле Точки значение 5.
После закрытия окна будет выведен график скользящего среднего, полностью совпадающий с красным графиком, ранее построенным с помощью формул.
Примечание : У метода Скользящее среднее есть несколько модификаций, которые рассмотрены в одноименной статье.
Момент степени k:
Центральный момент степени k:
Среднее значение
| Кол-во посетителей | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| Количество наблюдений | 114 | 115 | 52 | 52 | 24 | 13 | 30 |
| Таблица 1. Количество посетителей в час | |||||||
Чтобы найти среднее значение всех результатов необходимо сложить всё вместе и разделить на количество результатов:
μ = (114 • 0 + 115 • 1 + 52 • 2 + 52 • 3 + 24 • 4 + 13 • 5 + 30 • 6) / 400 = 716/400 = 1.79
То же самое мы можем проделать используя формулу 2:
μ = M(X) = Σ(Xi•pi) = 0 • 0.29 + 1 • 0.29 + 2 • 0.13 + 3 • 0.13 + 4 • 0.06 + 5 • 0.03 + 6 • 0.08 = 1.79 Момент первой степени, формула (5)
Собственно, формула 2 представляет собой среднее арифметическое всех значений
Итог: в среднем, 1.79 посетителя в час
| Количество посетителей | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
| Вероятность (%) | 28.5 | 28.8 | 13 | 13 | 6 | 3.3 | 7.5 |
| Таблица 2. Закон распределения количества посетителей | |||||||
Отклонение от среднего
Посмотрите на это распределение, можно предположить, что в среднем случайная величина равна 100±5, поскольку кажется, что таких значений несравнимо больше чем тех, что меньше 95 или больше 105:
График 2. График функции вероятности. Распределение ≈ 100±5
но сумма таких расстояний, а следовательно и любое производное от этого числа, будет равно нулю, поэтому в качестве меры выбрали квадрат разниц между величинами и средним значением:
σ возведена в квадрат, поскольку вместо расстояний мы взяли квадрат расстояний. σ 2 называется дисперсией. Корень из дисперсии называется средним квадратическим отклонением, или среднеквадратическим отклоненим, и его используют в качестве меры разброса:
Возвращаясь к примеру, посчитаем среднеквадратическое отклонение для графика 2:
Квантиль
График 3. Функция распределения. Медиана
График 4. Функция распределения. 4-квантиль или квартиль
График 5. Функция распределения. 0.34-квантиль
То есть, если мы говорим о дециле (10-квантиле), то это означает, что мы разбили график на 10 частей, что соответствует девяти линяям, и для каждого дециля нашли значение случайной величины.
Для дискретного распределения квантиль необходимо выбирать следующим образом: квантиль гарантирует вероятность, поэтому, если рассчитанный квантиль не совпадает с одним и значений, необходимо выбирать меньшее значение.
Построение интервалов
Двусторонний доверительный интервал
Первый квартиль
Значение квартиля Q1 находится в интервале 68,98 – 71,70, соответствующего частоте fQ1 = 150:4 = 37,5
Третий квартиль
Значение квартиля находится в интервале 68,98 – 71,70, соответствующего частоте fQ3 = (3*150):4 = 112,5
Квартили непрерывного распределения
Примечание : Подробнее о Функции распределения см. статью Функция распределения и плотность вероятности в MS EXCEL .
Если известна функция плотности вероятности p (х) , то 1-й квартиль можно найти из уравнения:
Например, решив аналитическим способом это уравнение для Логнормального распределения lnN(μ; σ 2 ), получим, что медиана (2-й квартиль ) вычисляется по формуле e μ или в MS EXCEL =EXP(μ). При μ=1, медиана равна 2,718.
Обратите внимание на точку Функции распределения , для которой F(х)=0,5 (см. картинку выше или файл примера , лист Квартиль-распределение) . Абсцисса этой точки равна 2,718. Это и есть значение 2-го квартиля ( медианы ), что естественно совпадает с ранее вычисленным значением по формуле e μ .
Примечание : Напомним, что интеграл от функции плотности вероятности по всей области задания случайной величины равен единице:
Поэтому, линии квартилей ( х=квартиль ) делят площадь под графиком функции плотности вероятности на 4 равные части.
Квартили в MS EXCEL
Чтобы вычислить в MS EXCEL квартили заданного распределения необходимо использовать соответствующую обратную функцию распределения .
При вычислении квартилей в MS EXCEL используются обратные функции распределения : НОРМ.СТ.ОБР() , ЛОГНОРМ.ОБР() , ХИ2.ОБР() , ГАММА.ОБР() и т.д. Подробнее о распределениях, представленных в MS EXCEL, можно прочитать в статье Распределения случайной величины в MS EXCEL .
Например, в MS EXCEL 1-й квартиль для логнормального распределения LnN(1;1) можно вычислить по формуле =ЛОГНОРМ.ОБР(0,25;1;1) , а 3-й квартиль для стандартного нормального распределения по формуле =НОРМ.СТ.ОБР(0,75) .
Моменты случайной величины
Моменты случайно величины описывают различные аспекты характера и формы нашего распределения.
Как видно на графике, чем выше значение пики, тем выше коэффициент эксцесса, т.е. у верхней кривой коэффициент эксцесса выше, чем у нижней.
Статистический анализ роста доли дохода в Excel за период
Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.
Вид исходной таблицы:
Определим 3-й по формуле:
Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:
Анализ статистики случайно сгенерированных чисел в Excel
Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.
Чтобы сгенерировать случайное число в Excel воспользуемся функцией:
После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:
Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):
Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:
Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.
Расчет квартилей в R и SAS
Функция quantile в R использует все девять алгоритмов расчета квантилей, в соответствии с нумерацией, предложенной Hyndman and Fan в работе 1996 г. (рис. 15; если вы не знакомы с R, рекомендую начать с Алексей Шипунов. Наглядная статистика. Используем R! ). Квантиль при i-м методе расчета:
где i – номер метода, 1 ≤ i ≤ 9, (j–m)/n ≤ p < (j–m+1)/n, хj – j-ый порядковый элемент упорядоченного ряда, n – размер выборки, γ является функцией двух параметров: j = floor(np + m) и g = np + m – j, где floor – функция возвращающая наибольшее целое, но всё еще меньшее, чем аргумент функции (аналог в Excel – ОКРВНИЗ.МАТ), m – константа, определяемая типом алгоритма расчета квантиля. Если вас интересуют подробности, обратитесь к справочной системе R.
SAS предлгает 5 методов расчета квантилей.
Расчет децилей для дискретного ряда
Определяем номер дециля по формуле: ,
Если номер дециля – целое число, то значение дециля будет равно величине элемента ряда, которое обладает накопленной частотой равной номеру дециля. Например, если номер дециля равен 20, его значение будет равно значению признака с S =20 (накопленной частотой равной 20).
Если номер дециля – нецелое число, то дециль попадает между двумя наблюдениями. Значением дециля будет сумма, состоящая из значения элемента, для которого накопленная частота равна целому значению номера дециля, и указанной части (нецелая часть номера дециля) разности между значением этого элемента и значением следующего элемента.
Например, если номер дециля равна 20,25, дециль попадает между 20-м и 21-м наблюдениями, и его значение будет равно значению 20-го наблюдения плюс 1/4 разности между значением 20-го и 21-го наблюдений.
Квантили специальных видов
Часто используются Квантили специальных видов:
В качестве примера вычислим медиану (0,5-квантиль) логнормального распределения LnN(0;1) (см. файл примера лист Медиана ).
Это можно сделать с помощью формулы =ЛОГНОРМ.ОБР(0,5; 0; 1)
Квантили стандартного нормального распределения
Необходимость в вычислении квантилей стандартного нормального распределения возникает при проверке статистических гипотез и при построении доверительных интервалов.
Примечание : Про проверку статистических гипотез см. статью Проверка статистических гипотез в MS EXCEL . Про построение доверительных интервалов см. статью Доверительные интервалы в MS EXCEL .
В данных задачах часто используется специальная терминология:
-
Нижний квантиль уровняальфа ( α percentage point) файл примера лист Квантили ).
Для α=0,05, нижний 0,05-квантиль стандартного нормального распределения равен -1,645. Вычисления в MS EXCEL можно сделать по формуле:
Действительно, для α=0,05, верхний 0,05-квантиль стандартного нормального распределения равен 1,645. Т.к. функция плотности вероятности стандартного нормального распределения является четной функцией, то вычисления в MS EXCEL верхнего квантиля можно сделать по двум формулам:
Чтобы пояснить название « верхний» квантиль , построим график плотности вероятности и функцию вероятности стандартного нормального распределения для α=0,05.
Выделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение больше верхнего 0,05-квантиля , т.е. больше значения 1,645. Эта вероятность равна 0,05.
Невыделенная площадь на рисунке соответствует вероятности, что случайная величина примет значение между нижним квантилем уровня α /2 и верхним квантилем уровня α /2, т.е. будет между значениями -1,960 и 1,960 при α=0,05. Эта вероятность равна в нашем случае 1-(0,05/2+0,05/2)=0,95. Если Z 0 попадает в одну из выделенных областей, то нулевая гипотеза отклоняется.
Другими словами, двусторонние α-квантили задают интервал, в который рассматриваемая случайная величина попадает с заданной вероятностью α.
Квантили распределения Стьюдента
Аналогичным образом квантили вычисляются и для распределения Стьюдента . Например, вычислять верхний α/2- квантиль распределения Стьюдента с n -1 степенью свободы требуется, если проводится проверка двухсторонней гипотезы о среднем значении распределения при неизвестной дисперсии ( см. эту статью ).
Для верхних квантилей распределения Стьюдента часто используется запись t α/2,n-1 . Если такая запись встретилась в статье про проверку гипотез или про построение доверительного интервала , то это именно верхний квантиль .
Примечание : Функция плотности вероятности распределения Стьюдента , как и стандартного нормального распределения , является четной функцией.
.2X означает 2 хвоста, т.е. двусторонний квантиль .
Квантили распределения ХИ-квадрат
Вычислять квантили распределения ХИ-квадрат с n -1 степенью свободы требуется, если проводится проверка гипотезы о дисперсии нормального распределения (см. статью Проверка статистических гипотез в MS EXCEL о дисперсии нормального распределения ).
При проверке таких гипотез также используются верхние квантили. Например, при двухсторонней гипотезе требуется вычислить 2 верхних квантиля распределения ХИ 2 : χ 2 α/2,n-1 и χ 2 1- α/2,n-1 . Почему требуется вычислить два квантиля , не один, как при проверке гипотез о среднем , где используется стандартное нормальное распределение или t-распределение ?
Дело в том, что в отличие от стандартного нормального распределения и распределения Стьюдента , плотность распределения ХИ 2 не является четной (симметричной относительно оси х). У него все квантили больше 0, поэтому верхний альфа-квантиль не равен нижнему (1-альфа)-квантилю или по-другому: верхний альфа-квантиль не равен нижнему альфа-квантилю со знаком минус.
Результат равен 20,48. .ПХ означает правый хвост распределения, т.е. тот который расположен вверху на графике функции распределения .
Чтобы вычислить верхний (1-0,05/2)- квантиль при том же числе степеней свободы , т.е. χ 2 1-0,05/2,n-1 и необходимо записать формулу =ХИ2.ОБР.ПХ(1-0,05/2; 10) или =ХИ2.ОБР(0,05/2; 10)
Читайте также: