Как найти размах вариации в excel

Вариация, размах, межквартильный размах, среднее линейное отклонение

В этой статье мы приступим к изучению показателей вариации: размах вариации, межквартильный размах, среднее линейное отклонение.

В математической статистике вариация занимает одно из центральных мест. Что же такое вариация? Это изменчивость. Вариация показателя – изменчивость показателя.

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

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

Размах вариации

Размах вариации – разница между максимальным и минимальным значением:

Ниже приведена графическая интерпретация размаха вариации.

Видно максимальное и минимальное значение, а также расстояние между ними, которое и соответствует размаху вариации.

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

Межквартильный размах

В статистике для анализа выборки часто прибегают к другому показателю вариации – межквартильному размаху. Квартиль – это то значение, которые делит ранжированные (отсортированные) данные на части, кратные одной четверти, или 25%. Так, 1-й квартиль – это значение, ниже которого находится 25% совокупности. 2-й квартиль делит совокупность данных пополам (то бишь медиана), ну и 3-й квартиль отделяет 25% наибольших значений. Так вот межквартильный размах – это разница между 3-м и 1-м квартилями. У данного показателя есть одно неоспоримое преимущество: он является робастным, т.е. не зависит от аномальных отклонений.

Наглядное отображение размаха вариации и межкварительного расстояния производят с помощью диаграммы «ящик с усами».

Среднее линейное отклонение

Есть показатели вариации, которые учитывают сразу все значения, а не только отдельные наблюдения (типа максимума или минимума). Одним из таких является среднее линейное отклонение. Этот показатель характеризует меру разброса значений вокруг их среднего. В чем суть? Для того, чтобы показать меру разброса данных, нужно вначале определиться, относительно чего этот самый разброс будет считаться. Обычно это среднее арифметическое. Далее нужно посчитать, насколько каждое значение отклоняется от средней. Нас интересует среднее из таких отклонений. Однако напрямую складывать положительные и отрицательные отклонения нельзя, т.к. они взаимоуничтожатся и их сумма будет равна нулю. Поэтому все отклонения берутся по модулю. Средне линейное отклонение рассчитывается по формуле:

a – среднее линейное отклонение,

X – анализируемый показатель,

X̅ – среднее значение показателя,

n – количество значений в анализируемой совокупности данных.

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

Отклонения каждого наблюдения от среднего указаны маленькими стрелочками. Именно они берутся по модулю и суммируются. Потом все делится на количество значений.

Для полноты картины нужно привести еще и пример. Допустим, имеется фирма по производству черенков для лопат. Каждый черенок должен быть 1,5 метра длиной, но, что еще важней, все должны быть одинаковыми или, по крайней мере, плюс-минус 5 см. Однако нерадивые работники то 1,2 м отпилят, то 1,8 м. Дачники недовольны. Решил директор провести статистический анализ длины черенков. Отобрал 10 штук и замерил их длину, нашел среднюю и рассчитал среднее линейное отклонение. Средняя получилась как раз, что надо – 1,5 м. А вот среднее линейное отклонение вышло 0,16 м. Вот и получается, что каждый черенок длиннее или короче, чем нужно, в среднем на 16 см. Есть, о чем поговорить с работниками.

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

Как рассчитать коэффициент вариации и другие статистические величины в Excel

Коэффициент вариации – это сравнение рассеивания двух случайно взятых величин. Величины имеют единицы измерения, что приводит к получению сопоставимого результата. Этот коэффициент нужен для подготовки статистического анализа.

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

Расчет стандартного отклонения

Стандартное отклонение является статистической величиной. С помощью расчета этой величины пользователь получит информацию о том, насколько отклоняются данные в ту или иную сторону относительно среднего значения. Стандартное отклонение в Excel рассчитывается в несколько шагов.

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

Вводите данные в любой табличный редактор (в нашем случае Excel), заполняя ячейки слева направо. Начинать следует с колонки «А». Заголовки вводите в строке сверху, а названия в тех же столбцах, которые относятся к заголовкам, только ниже. Затем дату и данные, которые подлежат расчету, справа от даты.

Этот документ сохраняете.

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

Вписываете знак «=» и прописываете далее формулу. Знак равенства обязателен. Иначе программа не посчитает предложенные данные. Формула вводится без пробелов.

Утилита выдаст названия нескольких формул. Выбираете «СТАНДОТКЛОН». Это формула вычисления стандартного отклонения. Существует два вида расчета:

• с вычислением по выборке;
• с вычислением по генеральной совокупности.

Выбрав одну из них, указываете диапазон данных. Вся введенная формула будет выглядеть так: «=СТАНДОТКЛОН (В2: В5)».

Затем кликаете по кнопке «Enter». Полученные данные появятся в отмеченном пункте.

Расчет среднего арифметического

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

• открываете утилиту. В верхней строке набираете ряд нужных цифр;

• под первой цифрой ставите курсор. В верхней строке программы выбираете вкладку «Редактирование», затем кнопку «Сумма». В выпавшем окне выбираете значение «Среднее»;

• после того, как кликните в том пункте на котором стоит курсор, появится формула;

• останется только выделить диапазон и кликнуть по кнопке «Ввод». А в ячейке теперь отобразится результат из взятых данных выше.

Расчет коэффициента вариации

Формула расчета коэффициента вариации:

V= S/X, где S – это стандартное отклонение, а X – среднее значение.

Для того, чтобы посчитать коэффициент вариации в Excel, необходимо найти стандартное отклонение и среднее арифметическое. То есть проделав первые два расчета, которые были показаны выше, можно перейти к работе над коэффициентом вариации.

Для этого открываете Excel, заполняем два поля, куда следует вписать полученные числа стандартного отклонения и среднего значения.

Теперь выделяете ячейку, которую отвели под число для вычисления вариации. Открываете вкладку «Главная», если она не открыта. Кликаете по инструменту «Число». Выбираете процентный формат.

Переходите к отмеченной ячейке и кликаете по ней дважды. Затем вводите знак равенства и выделяете пункт, куда вписан итог стандартного отклонения. Затем кликаете на клавиатуре по кнопке «слэш» или «разделить» (выглядит так: «/»). Выделяете пункт, куда вписано среднее арифметическое, и кликаете по кнопке «Enter». Должно получиться так:

А вот и результат после нажатия «Enter»:

Также для расчета коэффициента вариации можно использовать онлайн калькуляторы, например planetcalc.ru и allcalc.ru. Достаточно внести необходимые цифры и запустить расчет, после чего получить необходимые сведения.

Среднеквадратическое отклонение

Среднеквадратичное отклонение в Excel решается с помощью двух формул:

Простыми словами, извлекается корень из дисперсии. Как вычислить дисперсию рассмотрено ниже.

Среднее квадратичное отклонение является синонимом стандартного и вычисляется точное также. Выделяется ячейка для результата под числами, которые нужно рассчитать. Вставляется одна из функций, указанных на рисунке выше. Кликается кнопка «Enter». Результат получен.

Коэффициент осциляции

Соотношением размаха вариации к среднему – называется коэффициентом осциляции. Готовых формул в Экселе нет, поэтому нужно компоновать несколько функций в одну.

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

Дисперсия

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

Переменные принимают такие значения:

В Excel есть две функции, которые определяют дисперсию:

• Дисп.Г – используется относительно небольших выборок.
• Дисп.В – вычисление несмещенной дисперсии.

Чтобы произвести расчет, под числами, которые необходимо посчитать, выделяется ячейка. Заходите во вкладку вставки функции. Выбираете категорию «Статистические». В выпавшем списке выбираете одну из функций и кликаете по кнопке «Enter».

Максимум и минимум

Максимум и минимум нужны для того, чтобы не искать вручную среди большого количества чисел минимальное или максимальное число.

Чтобы вычислить максимум, выделяете весь диапазон необходимых чисел в таблице и отдельную ячейку, затем кликаете по значку «Σ» или «Автосумма». В выпавшем окне выбираете «Максимум» и, нажав кнопку «Enter» получаете нужное значение.

Тоже самое делаете, чтобы получить минимум. Только выбираете функцию «Минимум».

Как найти размах в excel

Размах варьирования. Наибольшее и наименьшее значения

Лабораторная работа № 1

Статистический анализ данных

Цель работы: научиться обрабатывать статистические данные с помощью встроенных функций.

Порядок выполнения работы:

1. Основные статистические характеристики:

— Выборочная дисперсия (вариабельность)

2. Самостоятельная работа

— Диаграмма рассеяния (задание 1)

— Основные статистические показатели (задание 2)

— Отклонение случайного распределения от нормального (задание 3)

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 – это разность между наибольшим x_max и наименьшим x_min значениями признака совокупности (генеральной или выборочной): R=x_max–x_min. Для нахождения наибольшего значения x_max имеется функция МАКС (или MAX), а для наименьшего x_min – функция МИН (или MIN). Их аргументом является интервал ячеек. Для того, чтобы вычислить размах варьирования данных в интервале ячеек, например, от А1 до А100, следует ввести формулу: =МАКС (А1:А100)-МИН (А1:А100).

Задание 1

Имеются данные о размерах располагаемого дохода DPI и расходов на личное потребление С для n семей в условных единицах, так что DPI_i и С_i, соответственно, представляют располагаемый доход и расходы на личное потребление i-й семьи.

1. Построить диаграмму рассеяния, принимая за ось абсцисс — DPI_i, а за ось ординатС_i

Доходы_расходы
I	DPI	C	I	DPI	C

2. Выполнить настройку формата оси Х и оси Y в соответствии с образцом диаграммы.

Задание 2

Имеются данные об уровне безработицы (в %) среди «белого» (коренное) и «цветного» (эмигранты) населения страны с марта 2000г. по июль 2001г. (месячные данные), так что BEL_i и ZVET_i, соответственно, представляют уровни безработицы в i-м месяце.

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

2. Вычислить средние значения уровней безработицы для BEL_i и ZVET_i населения страны.

Уровень безработицы
Исходные данные
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. Вычислить выборочные дисперсии, характеризующие степень разброса значений BEL_i и ZVET_i вокруг своего среднего значения.

4. Вычислить стандартные отклоненияBEL_i и ZVET_i относительно среднего значения.

5. Вычислить наибольшее и наименьшее значения для BEL_i и ZVET_i.

6. Вычислить размах варьирования дляBEL_i и ZVET_i.

7. Вычислить Моду и Медиану дляBEL_i и ZVET_i.

Использование Excel для расчета статистических характеристик случайной величины

Разделы: Математика

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

1. Сегодня на уроке мы научимся рассчитывать статистические характеристики для больших по объему выборок, используя возможности современных компьютерных технологий.
2. Для начала вспомним:

– что называется случайной величиной? (Случайной величиной называют переменную величину, которая в зависимости от исхода испытания принимает одно значение из множества возможных значений.)

– Какие виды случайных величин мы знаем? (Дискретные, непрерывные.)

– Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).

– Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).

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

1. Рассмотрим, применение инструментов 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. Составим таблицу, в которой значения x_i случайной величины меняются от 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. Сегодня на уроке мы научились применять компьютерные технологии для анализа и обработки статистической информации.

Как посчитать размах вариации в excel

Чтобы посчитать размах вариации (range) в Excel, выполните следующие действия:

1. Откройте программу Microsoft Excel и создайте новую таблицу.

2. Введите данные для анализа в ячейки таблицы.

3. Выберите ячейку, в которой хотите вывести результат размаха вариации.

4. Введите формулу «=MAX(выборка)-MIN(выборка)» в выбранную ячейку и нажмите клавишу Enter.

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

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

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

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

Для расчета стандартного отклонения в Excel можно использовать функцию STDEV. P для выборок с известной генеральной совокупностью или STDEV. S для выборок без известной генеральной совокупности. Эти функции вычисляют стандартное отклонение по формуле, которая учитывает все значения выборки.

Кроме того, существует также межквартильный размах, который является более устойчивым к выбросам, чем размах вариации. Межквартильный размах определяется как разница между 75-м и 25-м процентилем выборки. Этот показатель также можно рассчитать в Excel, используя функцию QUARTILE. EXC.

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