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

Использование описательной статистики

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

В Экселе существует отдельный инструмент, входящий в «Пакет анализа», с помощью которого можно провести данный вид обработки данных. Он так и называется «Описательная статистика». Среди критериев, которые высчитывает данный инструмент следующие показатели:

• Медиана;
• Мода;
• Дисперсия;
• Среднее;
• Стандартное отклонение;
• Стандартная ошибка;
• Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

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

1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».



2. В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».



3. Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».

После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

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

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

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

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

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

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)

1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.

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

Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.

Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».

В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».

Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».

Шаг 3: нахождение коэффициента вариации

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

Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.

Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.

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

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

Вместо наименования «Диапазон значений» вставляем реальные координаты области, в которой размещен исследуемый числовой ряд. Это можно сделать простым выделением данного диапазона. Вместо оператора СТАНДОТКЛОН.В, если пользователь считает нужным, можно применять функцию СТАНДОТКЛОН.Г.

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.

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

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

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

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

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

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

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

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

1. Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.

Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):

1. Занести данные в EXCEL, каждое число в отдельную ячейку.

2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем – статистические, в списке: МОДА

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили М_о = 29 (чел) – Фирм у которых в штате 29 человек больше всего.

Используя тот же путь вычисляем медиану.

Вставка – Функция – Статистические – Медиана.

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили М_е = 29 (чел) – среднее значение сотрудников в фирме.

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

Вставка – Функция – Статистические – МАКС.

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили наибольшее значение = 36.

Вставка – Функция – Статистические – МИН.

В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

Нажимаем клавишу ОК. Получили наименьшее значение = 22.

36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.

Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения x_i случайной величины меняются от 22 до 36 включительно шагом 1.

Чтобы сосчитать частоту каждого значения воспользуемся

Вставка – Функция – Статистические – СЧЕТЕСЛИ.

В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22

Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.

Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические – СУММА). Должно получиться 100 (количество всех фирм).

Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)

Нажимаем клавишу Далее, в Мастере диаграмм указываем название диаграммы (Полигон частот), удаляем легенду, редактируем шкалу и характеристики диаграммы для наибольшей наглядности.

Для построения столбчатой и круговой диаграмм используем тот же путь (выбирая нужный нам тип диаграммы).

Диаграмма – Стандартные – Круговая.

Диаграмма – Стандартные – Гистограмма.

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

Простая формула для расчета объема выборки

где: n – объем выборки;

z – нормированное отклонение, определяемое исходя из выбранного уровня доверительности. Этот показатель характеризует возможность, вероятность попадания ответов в специальный – доверительный интервал. На практике уровень доверительности часто принимают за 95% или 99%. Тогда значения z будут соответственно 1,96 и 2,58;

p – вариация для выборки, в долях. По сути, p – это вероятность того, что респонденты выберут той или иной вариант ответа. Допустим, если мы считаем, что четверть опрашиваемых выберут ответ «Да», то p будет равно 25%, то есть p = 0,25;

q = (1 – p);

e – допустимая ошибка, в долях.

Пример расчета объема выборки

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

Объем выборки в этом случае рассчитывается следующим образом. Уровень доверительности принимается за 95%, тогда нормированное отклонение z = 1,96. Вариацию принимаем за 50%, то есть условно считаем, что половина респондентов может ответить на вопрос о том, курят ли они – «Да». Тогда p = 0,5. Отсюда находим q = 1 – p = 1 – 0,5 = 0,5. Допустимую ошибку выборки принимаем за 10%, то есть e = 0,1.

Подставляем эти данные в формулу и считаем:

Получаем объем выборки n = 96 человек.

Задачи о генеральной доле

На вопрос «Накрывает ли доверительный интервал заданное значение p₀?» — можно ответить, проверив статистическую гипотезу H₀:p=p₀. При этом предполагается, что опыты проводятся по схеме испытаний Бернулли (независимы, вероятность p появления события А постоянна). По выборке объема n определяют относительную частоту p^* появления события A: где m — количество появлений события А в серии из n испытаний. Для проверки гипотезы H₀ используется статистика, имеющая при достаточно большом объеме выборки стандартное нормальное распределение (табл. 1).
Таблица 1 – Гипотезы о генеральной доле

Гипотеза	H0:p=p0	H0:p1=p2
Предположения	Схема испытаний Бернулли	Схема испытаний Бернулли
Оценки по выборке
Статистика K
Распределение статистики K	Стандартное нормальное N(0,1)	Стандартное нормальное N(0,1)

Пример №1. С помощью случайного повторного отбора руководство фирмы провело выборочный опрос 900 своих служащих. Среди опрошенных оказалось 270 женщин. Постройте доверительный интервал, с вероятностью 0.95 накрывающий истинную долю женщин во всем коллективе фирмы.
Решение. По условию выборочная доля женщин составляет (относительная частота женщин среди всех опрошенных). Так как отбор является повторным, и объем выборки велик (n=900) предельная ошибка выборки определяется по формуле
(относительная частота женщин среди всех опрошенных). Так как отбор является повторным, и объем выборки велик (n=900) предельная ошибка выборки определяется по формуле

Значение u_кр находим по таблице функции Лапласа из соотношения 2Ф(u_кр)=γ, т.е. Функция Лапласа (приложение 1) принимает значение 0.475 при u_кр=1.96. Следовательно, предельная ошибка Функция Лапласа (приложение 1) принимает значение 0.475 при u_кр=1.96. Следовательно, предельная ошибка и искомый доверительный интервал
(p – ε, p + ε) = (0.3 – 0.18; 0.3 + 0.18) = (0.12; 0.48)
Итак, с вероятностью 0.95 можно гарантировать, что доля женщин во всем коллективе фирмы находится в интервале от 0.12 до 0.48.

Пример №2. Владелец автостоянки считает день «удачным», если автостоянка заполнена более, чем на 80 %. В течение года было проведено 40 проверок автостоянки, из которых 24 оказались «удачными». С вероятностью 0.98 найдите доверительный интервал для оценки истинной доли «удачных» дней в течение года.
Решение. Выборочная доля «удачных» дней составляет
По таблице функции Лапласа найдем значение u_кр при заданной
доверительной вероятности
По таблице функции Лапласа найдем значение u_кр при заданной
доверительной вероятности
Ф(2.23) = 0.49, u_кр = 2.33.
Считая отбор бесповторным (т.е. две проверки в один день не проводилось), найдем предельную ошибку:
где n=40, N = 365 (дней). Отсюда
где n=40, N = 365 (дней). Отсюда
и доверительный интервал для генеральной доли: (p – ε, p + ε) = (0.6 – 0.17; 0.6 + 0.17) = (0.43; 0.77)
С вероятностью 0.98 можно ожидать, что доля «удачных» дней в течение года находится в интервале от 0.43 до 0.77.

Пример №3. Проверив 2500 изделий в партии, обнаружили, что 400 изделий высшего сорта, а n–m – нет. Сколько надо проверить изделий, чтобы с уверенностью 95% определить долю высшего сорта с точностью до 0.01?
Решение ищем по формуле определения численности выборки для повторного отбора.

Ф(t) = γ/2 = 0.95/2 = 0.475 и этому значению по таблице Лапласа соответствует t=1.96
Выборочная доля w = 0.16; ошибка выборки ε = 0.01

Пример №4. Партия изделий принимается, если вероятность того, что изделие окажется соответствующим стандарту, составляет не менее 0.97. Среди случайно отобранных 200 изделий проверяемой партии оказалось 193 соответствующих стандарту. Можно ли на уровне значимости α=0,02 принять партию?
Решение. Сформулируем основную и альтернативную гипотезы.
H₀:p=p₀=0,97 — неизвестная генеральная доля p равна заданному значению p₀=0,97. Применительно к условию — вероятность того, что деталь из проверяемой партии окажется соответствующей стандарту, равна 0.97; т.е. партию изделий можно принять.
H₁:p<0,97 – вероятность того, что деталь из проверяемой партии окажется соответствующей стандарту, меньше 0.97; т.е. партию изделий нельзя принять. При такой альтернативной гипотезе критическая область будет левосторонней.
Наблюдаемое значение статистики K (таблица) вычислим при заданных значениях p₀=0,97, n=200, m=193

Критическое значение находим по таблице функции Лапласа из равенства

По условию α=0,02 отсюда Ф(Ккр)=0,48 и Ккр=2,05. Критическая область левосторонняя, т.е. является интервалом (-∞;-K_kp)= (-∞;-2,05). Наблюдаемое значение К_набл=-0,415 не принадлежит критической области, следовательно, на данном уровне значимости нет оснований отклонять основную гипотезу. Партию изделий принять можно.

Пример №5. Два завода изготавливают однотипные детали. Для оценки их качества сделаны выборки из продукции этих заводов и получены следующие результаты. Среди 200 отобранных изделий первого завода оказалось 20 бракованных, среди 300 изделий второго завода — 15 бракованных.
На уровне значимости 0.025 выяснить, имеется ли существенное различие в качестве изготавливаемых этими заводами деталей.
Решение. Это задача о сравнении генеральных долей двух совокупностей. Сформулируем основную и альтернативную гипотезы.
H₀:p₁=p₂ — генеральные доли равны. Применительно к условию — вероятность появления бракованного изделия в продукции первого завода равна вероятности появления бракованного изделия в продукции второго завода (качество продукции одинаково).
H₀:p₁≠p₂ — заводы изготавливают детали разного качества.
Для вычисления наблюдаемого значения статистики K (таблица) рассчитаем оценки по выборке.

Наблюдаемое значение равно

Так как альтернативная гипотеза двусторонняя, то критическое значение статистики K≈ N(0,1) находим по таблице функции Лапласа из равенства
Так как альтернативная гипотеза двусторонняя, то критическое значение статистики K≈ N(0,1) находим по таблице функции Лапласа из равенства
По условию α=0,025 отсюда Ф(Ккр)=0,4875 и Ккр=2,24. При двусторонней альтернативе область допустимых значений имеет вид (-2,24;2,24). Наблюдаемое значение K_набл=2,15 попадает в этот интервал, т.е. на данном уровне значимости нет оснований отвергать основную гипотезу. Заводы изготавливают изделия одинакового качества.

По части судить о целом

Как рассчитать объем выборки

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

Первым шагом в поиске значений выбросов статистики является определение статистического центра диапазона данных. С этой целью необходимо сначала определить границы первого и третьего квартала. Определение границ квартала – значит разделение данных на 4 равные группы, которые содержат по 25% данных каждая. Группа, содержащая 25% наибольших значений, называется первым квартилем.

Границы квартилей в Excel можно легко определить с помощью простой функции КВАРТИЛЬ. Данная функция имеет 2 аргумента: диапазон данных и номер для получения желаемого квартиля.

В примере показанному на рисунке ниже значения в ячейках E1 и E2 содержат показатели первого и третьего квартиля данных в диапазоне ячеек B2:B19:

Вычитая от значения первого квартиля третьего, можно определить набор 50% статистических данных, который называется межквартильным диапазоном. В ячейке E3 определен размер межквартильного диапазона.

В этом месте возникает вопрос, как сильно данное значение может отличаться от среднего значения 50% данных и оставаться все еще в пределах нормы? Статистические аналитики соглашаются с тем, что для определения нижней и верхней границы диапазона данных можно смело использовать коэффициент расширения 1,5 умножив на значение межквартильного диапазона. То есть:

1. Нижняя граница диапазона данных равна: значение первого квартиля – межкваритльный диапазон * 1,5.
2. Верхняя граница диапазона данных равна: значение третьего квартиля + расширенных диапазон * 1,5.

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

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

Способ 1: применение расширенного автофильтра

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

1. Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».

   

   Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».



2. После этого действия в шапке таблицы появляются пиктограммы для запуска фильтрования в виде перевернутых острием вниз небольших треугольников на правом краю ячеек. Кликаем по данному значку в заглавии того столбца, по которому желаем произвести выборку. В запустившемся меню переходим по пункту «Текстовые фильтры». Далее выбираем позицию «Настраиваемый фильтр…».



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

   Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».



4. Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.



5. Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».

   Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».



6. Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.



7. Аналогично можно настраивать фильтры и в других столбцах. При этом имеется возможность сохранять также фильтрацию и по предыдущим условиям, которые были заданы в колонках. Итак, посмотрим, как производится отбор с помощью фильтра для ячеек в формате даты. Кликаем по значку фильтрации в соответствующем столбце. Последовательно кликаем по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».



8. Снова запускается окно пользовательского автофильтра. Выполним отбор результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условий, как видим, ещё больше вариантов, чем для числового формата. Выбираем позицию «После или равно». В поле справа устанавливаем значение «04.05.2016». В нижнем блоке устанавливаем переключатель в позицию «До или равно». В правом поле вписываем значение «06.05.2016». Переключатель совместимости условий оставляем в положении по умолчанию – «И». Для того, чтобы применить фильтрацию в действии, жмем на кнопку «OK».



9. Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.



10. Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр».



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



12. В данной таблице имеется ещё одна колонка – «Наименование». В ней содержатся данные в текстовом формате. Посмотрим, как сформировать выборку с помощью фильтрации по этим значениям.

    Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».



13. Опять открывается окно пользовательского автофильтра. Давайте сделаем выборку по наименованиям «Картофель» и «Мясо». В первом блоке переключатель условий устанавливаем в позицию «Равно». В поле справа от него вписываем слово «Картофель». Переключатель нижнего блока так же ставим в позицию «Равно». В поле напротив него делаем запись – «Мясо». И вот далее мы выполняем то, чего ранее не делали: устанавливаем переключатель совместимости условий в позицию «ИЛИ». Теперь строчка, содержащая любое из указанных условий, будет выводиться на экран. Щелкаем по кнопке «OK».



14. Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.



15. Полностью удалить фильтр можно теми же способами, которые использовались для его установки. Причем неважно, какой именно способ применялся. Для сброса фильтрации, находясь во вкладке «Данные» щелкаем по кнопке «Фильтр», которая размещена в группе «Сортировка и фильтр».

    

    Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

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

Способ 2: применение формулы массива

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

1. На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.



2. Выделяем все пустые ячейки первой колонки новой таблицы. Устанавливаем курсор в строку формул. Как раз сюда будет заноситься формула, производящая выборку по указанным критериям. Отберем строчки, сумма выручки в которых превышает 15000 рублей. В нашем конкретном примере, вводимая формула будет выглядеть следующим образом:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Естественно, в каждом конкретном случае адрес ячеек и диапазонов будет свой. На данном примере можно сопоставить формулу с координатами на иллюстрации и приспособить её для своих нужд.



3. Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.



4. Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:

   =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Жмем сочетание клавиш Ctrl+Shift+Enter.



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

   =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Опять набираем сочетание клавиш Ctrl+Shift+Enter.

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



6. Как видим, таблица заполнена данными, но внешний вид её не совсем привлекателен, к тому же, значения даты заполнены в ней некорректно. Нужно исправить эти недостатки. Некорректность даты связана с тем, что формат ячеек соответствующего столбца общий, а нам нужно установить формат даты. Выделяем весь столбец, включая ячейки с ошибками, и кликаем по выделению правой кнопкой мыши. В появившемся списке переходим по пункту «Формат ячейки…».



7. В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».



8. Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!». По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная» кликаем по кнопке «Условное форматирование», которая находится в блоке инструментов «Стили». В появившемся списке выбираем пункт «Создать правило…».



9. В открывшемся окне выбираем тип правила «Форматировать только ячейки, которые содержат». В первом поле под надписью «Форматировать только ячейки, для которых выполняется следующее условие» выбираем позицию «Ошибки». Далее жмем по кнопке «Формат…».



10. В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».



11. На кнопку с точно таким же названием жмем после возвращения в окно создания условий.

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

СРЗНАЧ()

Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

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

Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

СРЗНАЧЕСЛИ()

Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:

В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.

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

Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.

МАКС()

Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

МИН()

Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

Расчет показателей вариации в Excel

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

Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных. Кстати, на этом указанная тема не закончилась, к выпуску планируются новые статьи – следите за рекламой! Однако сухая теория без инструментов реализации – вещь не сильно полезная. Поэтому по мере появления теоретических выкладок, я стараюсь не отставать с заметками о соответствующих расчетах в программе Excel.

Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:

— максимальное и минимальное значение

— среднее линейное отклонение

— дисперсия (по генеральной совокупности и по выборке)

— среднее квадратическое отклонение (по генеральной совокупности и по выборке)

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

Максимальное и минимальное значение

Начнем с формул максимума и минимума. Что такое максимальное и минимальное значение, уверен, знают почти все. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое (может быть и отрицательным числом). Это крайние значения в совокупности данных, обозначающие границы их вариации. Примеры реального использования каждый может придумать сам – их полно. Это и минимальные/максимальные цены на что-нибудь, и выбор наилучшего или наихудшего решения задачи, и всего, чего угодно. Минимум и максимум – весьма информативные показатели. Давайте теперь их рассчитаем в Excel.

Как нетрудно догадаться, делается сие элементарно – как два клика об асфальт. В Мастере функций следует выбрать: МАКС – для расчета максимального значения, МИН – для расчета минимального значения. Для облегчения поиска перечень всех функций можно отфильтровать по категории «Статистические».

Выбираем нужную формулу, в следующем окошке указываем диапазон данных (в котором ищется максимальное или минимальное значение) и жмем «ОК».

Функции МАКС и МИН достаточно часто используются, поэтому разработчики Экселя предусмотрительно добавили соответствующие кнопки в ленту. Они находятся там же, где суммаи среднее значение – в разворачивающемся списке.

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

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

Среднее линейное отклонение, напоминаю, представляет собой среднее из абсолютных (по модулю) отклонений от средней арифметической в анализируемой совокупности данных. Математическая формула имеет вид:

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

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

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

В Excel эта функция называется СРОТКЛ.

После выбора функции СРОТКЛ указываем диапазон данных, по которому должен произойти расчет. Нажимаем «ОК». Наслаждаемся результатом.

Дисперсия

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

x – анализируемый показатель, с черточкой сверху – среднее значение показателя,

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

Excel также предлагает готовую функцию для расчета генеральной дисперсии ДИСП.Г.

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

Математическая формула выборочной дисперсии имеет вид:

в Excel выборочная дисперсия рассчитывает через функцию ДИСП.В.

Выбираем в Мастере функций нужную дисперсию (генеральную или выборочную), указываем диапазон, жмем кнопку «ОК». Полученное значение может оказаться очень большим из-за предварительного возведения отклонений в квадрат, поэтому дисперсия сама по себе мало о чем говорит. Ее обычно используют для дальнейших расчетов.

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

Среднеквадратическое отклонение по генеральной совокупности – это корень из генеральной дисперсии.

Выборочное среднеквадратическое отклонение – это корень из выборочной дисперсии.

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

— Среднеквадратическое отклонение по генеральной совокупности СТАНДОТКЛОН.Г

— Среднеквадратическое отклонение по выборке СТАНДОТКЛОН.В.

С названием этого показателя может возникнуть путаница, т.к. часто можно встретить синоним «стандартное отклонение». Пугаться не нужно – смысл тот же.

Далее, как обычно, указываем нужный диапазон и нажимаем на «ОК». Среднее квадратическое отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными. Об этом ниже.

Коэффициент вариации

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

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

В скобках должен быть указан диапазон данных. При необходимости используется среднее квадратическое отклонение по выборке (СТАНДОТКЛОН.В).

Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на ленте на закладке «Главная»:

Изменить формат также можно, выбрав «Формат ячеек» из выпадающего списка после выделения нужной ячейки правой кнопкой мышки.

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

В целом, с помощью Excel все, или почти все, статистические показатели рассчитываются очень просто. Если что-то непонятно, всегда можно воспользоваться окошком для поиска в Мастере функций. Ну, и Гугл в помощь.

Показатели вариации и способы их расчета

Показатели вариации делятся на две группы: абсолютные и относительные.

К абсолютным показателям относятся:

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

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

К относительным показателям вариации относятся:

─ относительное линейное отклонение и др.

Размах вариации (R) вычисляется как разность между наибольшим и наименьшим значениями варьирующего признака

.

Он показывает, насколько велико различие между единицами совокупности, имеющими самое маленькое (х_min) и самое большое значение признака (х_max).

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

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

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

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

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

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

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

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

Если каждый вариант в ряду распределения повторяется один раз, то среднее линейное отклонение равно

где — абсолютные значения отклонений отдельных вариантов от их средней величины;

n — объем совокупности.

Для вариационного ряда с неравными частотами формула имеет следующий вид:

, (6.1)

где — сумма частот вариационного ряда.

На основе данных дискретного ряда распределения табл. 6.1. рассчитаем размах вариации и среднее линейное отклонение:

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

Лабораторная работа № 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

Лекция 3. Описательная статистика. Показатели разброса или вариации

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

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

Размах вариации – это разность между максимальным и минимальным значениями X из имеющихся в изучаемой статистической совокупности:

Недостатком показателя H является то, что он показывает только максимальное различие значений X и не может измерять силу вариации во всей совокупности.

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

Cреднее линейное отклонение — это средний модуль отклонений значений X от среднего арифметического значения. Его можно рассчитывать по формуле средней арифметической простой — получим среднее линейное отклонение простое:

Например, студент сдал 4 экзамена и получил следующие оценки: 3, 4, 4 и 5.Ранее уже была рассчитана средняя арифметическая= 4. Рассчитаем среднее линейное отклонение простое: Л = (|3-4|+|4-4|+|4-4|+|5-4|)/4 = 0,5.

Если исходные данные X сгруппированы (имеются частоты f), то расчет среднего линейного отклонения выполняется по формуле средней арифметической взвешенной — получим среднее линейное отклонение взвешенное:

Вернемся к примеру про студента, который сдал 4 экзамена и получил следующие оценки: 3, 4, 4 и 5. Ранее уже была рассчитана средняя арифметическая = 4 и среднее линейное отклонение простое = 0,5. Рассчитаем среднее линейное отклонение взвешенное: Л = (|3-4|*1+|4-4|*2+|5-4|*1)/4 = 0,5.

Функция СРОТКЛ

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

Общий вид функции

СРОТКЛ (число1; число2; . )

Число1, число2, . — это от 1 до 30 аргументов, для которых определяется среднее абсолютных отклонений. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. При использовании функции надо учитывать следующие условия:

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

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

Уравнение для среднего отклонения следующее:

На результат СРОТКЛ влияют единицы измерения входных данных.

Линейный коэффициент вариации

Линейный коэффициент вариации — это отношение среднего линейного отклонения к средней арифметической:

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

В рассматриваемом примере про студента, который сдал 4 экзамена и получил следующие оценки: 3, 4, 4 и 5, линейный коэффициент вариации составит 0,5/4 = 0,125 или 12,5%.

Дисперсия

Дисперсия — это средний квадрат отклонений значений X от среднего арифметического значения. Дисперсию можно рассчитывать по формуле средней арифметической простой — получим дисперсию простую:

В уже знакомом нам примере про студента, который сдал 4 экзамена и получил оценки: 3, 4, 4 и 5, ранее уже была рассчитана средняя арифметическая = 4. Тогда дисперсия простая Д = ((3-4) 2 +(4-4) 2 +(4-4) 2 +(5-4) 2 )/4 = 0,5.

Если исходные данные X сгруппированы (имеются частоты f), то расчет дисперсии выполняется по формуле средней арифметической взвешенной — получим дисперсию взвешенную:

В рассматриваемом примере про студента, который сдал 4 экзамена и получил следующие оценки: 3, 4, 4 и 5, рассчитаем дисперсию взвешенную:
Д = ((3-4) 2 *1+(4-4) 2 *2+(5-4) 2 *1)/4 = 0,5.

Если преобразовать формулу дисперсии (раскрыть скобки в числителе, почленно разделить на знаменатель и привести подобные), то можно получить еще одну формулу для ее расчета как разность средней квадратов и квадрата средней:

В уже знакомом нам примере про студента, который сдал 4 экзамена и получил следующие оценки: 3, 4, 4 и 5, рассчитаем дисперсию методом разности средней квадратов и квадрата средней:
Д = (3 2 *1+4 2 *2+5 2 *1)/4-4 2 = 16,5-16 = 0,5.

Если значения X — это доли совокупности, то для расчета дисперсии используют частную формулу дисперсии доли :

.

Функция ДИСПР

Функция вычисляет дисперсию для генеральной совокупности. (Для дисперсии по выборке используется функция ДИСП). Дисперсией ( s 2 ) называют среднюю арифметическую квадратов отклонений результатов наблюдений от их средней арифметической.

Число1, число2, . — это от 1 до 30 числовых аргументов, соответствующих генеральной совокупности. Логические значения, например ИСТИНА и ЛОЖЬ, а также текст игнорируются

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

Уравнение для дисперсии имеет следующий вид:

Для функции ДИСП используется формула

Функция ДИСПРА

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

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

ДИСПРА предполагает, что аргументы представляют всю генеральную совокупность. Если данные представляют только выборку из генеральной совокупности, то дисперсию следует вычислять, используя функцию ДИСПА. Аргументы, содержащие значение ИСТИНА интерпретируются как 1, аргументы, содержащие текст или значение ЛОЖЬ интерпретируются как 0 (ноль).

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

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

Еще проще можно найти среднее квадратическое отклонение, если предварительно рассчитана дисперсия, как корень квадратный из нее:

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

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

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

Общий вид функции

Число1, число2, . — это от 1 до 30 аргументов, для которых вычисляется сумма квадратов отклонений. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

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

Уравнение для суммы квадратов отклонений имеет следующий вид:

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

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

Число1, число2, . — это от 1 до 30 числовых аргументов, соответствующих генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются.

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

СТАНДОТКЛОНП использует следующую формулу:

,

а СТАНДОТКЛОН —

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

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

Значение1,значение2. это от 1 до 30 значений, соответствующих генеральной совокупности. Можно использовать массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.

СТАНДОТКЛОНПА предполагает, что аргументы образуют всю генеральную совокупность. Если данные являются только выборкой из генеральной совокупности, то стандартное отклонение следует вычислять с использованием функции СТАНДОТКЛОНА. Аргументы, содержащие значение ИСТИНА интерпретируются как 1, аргументы, содержащие значение ЛОЖЬ интерпретируются как 0 (ноль). Для больших выборок СТАНДОТКЛОНА и СТАНДОТКЛОНПА возвращают примерно равные значения.

Квадратический коэффициент вариации

Квадратический коэффициент вариации — это самый популярный относительный показатель вариации:

Критериальным значением квадратического коэффициента вариации V служит 0,333 или 33,3%, то есть если V меньше или равен 0,333 — вариация считает слабой, а если больше 0,333 — сильной. В случае сильной вариации изучаемая статистическая совокупность считается неоднородной, а средняя величина — нетипичной и ее нельзя использовать как обобщающий показатель этой совокупности.

В примере про студента, в котором выше рассчитали среднее квадратическое отклонение, найдем квадратический коэффициент вариации V = 0,707/4 = 0,177, что меньше критериального значения 0,333, значит вариация слабая и равна 17,7%.

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

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

Copyright © 2009-2015
Ющик Е.В. All Rights Reserved

Показатели вариации и способы их расчета

Показатели вариации делятся на две группы: абсолютные и относительные.

К абсолютным показателям относятся:

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

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

К относительным показателям вариации относятся:

─ относительное линейное отклонение и др.

Размах вариации (R) вычисляется как разность между наибольшим и наименьшим значениями варьирующего признака

.

Он показывает, насколько велико различие между единицами совокупности, имеющими самое маленькое (х_min) и самое большое значение признака (х_max).

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

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

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

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

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

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

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

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

Если каждый вариант в ряду распределения повторяется один раз, то среднее линейное отклонение равно

где — абсолютные значения отклонений отдельных вариантов от их средней величины;

n — объем совокупности.

Для вариационного ряда с неравными частотами формула имеет следующий вид:

, (6.1)

где — сумма частот вариационного ряда.

На основе данных дискретного ряда распределения табл. 6.1. рассчитаем размах вариации и среднее линейное отклонение:

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

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

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

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

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

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

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

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

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

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

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

С одной стороны, показатель размаха может быть вполне информативным и полезным.

К примеру, максимальная и минимальная стоимость квартиры в городе 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 см.

Есть, о чем поговорить с работниками.

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

в социальных сетях:

Источник: https://statanaliz.info/statistica/opisanie-dannyx/variatsiya-razmakh-srednee-linejnoe-otklonenie/

Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Предмет:

Файл:

6. показатели вариации.doc

Скачиваний:

199

Добавлен:

Размер:

246.78 Кб

Скачать

Калькулятор

Сервис бесплатной оценки стоимости работы

1. Заполните заявку. Специалисты рассчитают стоимость вашей работы
2. Расчет стоимости придет на почту и по СМС

Номер вашей заявки

Прямо сейчас на почту придет автоматическое письмо-подтверждение с информацией о заявке.

Оформить еще одну заявку

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

Для расчета стандартного отклонения используется функция СТАНДОТКЛОН.

Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.

Синтаксис данных функций выглядит соответствующим образом:

1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
2. Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».
3. Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»
4. В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.

Урок: Формула среднего квадратичного отклонения в Excel

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.

1. Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».
2. В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».
3. Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».
4. Результат вычисления среднего арифметического выводится в ту ячейку, которая была выделена перед открытием Мастера функций.

Урок: Как посчитать среднее значение в Excel

Шаг 3: нахождение коэффициента вариации

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

1. Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.
2. Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.
3. Как видим, результат расчета выведен на экран.

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

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

   Вместо наименования «Диапазон значений» вставляем реальные координаты области, в которой размещен исследуемый числовой ряд. Это можно сделать простым выделением данного диапазона. Вместо оператора СТАНДОТКЛОН.В, если пользователь считает нужным, можно применять функцию СТАНДОТКЛОН.Г.

2. После этого, чтобы рассчитать значение и показать результат на экране монитора, щелкаем по кнопке Enter.

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

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

К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Источник: https://alfa-service42.com/tehnologii/raschet-pokazateley-variacii-v-excel.html

Коэффициент вариации (CV)

Коэффициент вариации (coefficient of variation, CV) – это статистическая мера дисперсии (разброса) данных вокруг некоторого среднего значения.

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

СОДЕРЖАНИЕ

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

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

Коэффициент вариации может быть весьма полезен при использовании соотношения риск/прибыль для выбора объекта инвестиций.

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

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

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

Обычно данный коэффициентиспользуют в таких целях как:

• Для сравнениянескольких различных рядов данных илипоказателей;
• Для оценки потенциальныхобъектов инвестирования;
• Для проведенияXYZ-анализа.

КЛЮЧЕВЫЕ МОМЕНТЫ

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

Формула CV

Ниже приведена формула для расчета коэффициента вариации:

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

Коэффициент вариации в Excel и Open Office

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

Несмотря на то, что в нём нет стандартной функции для расчёта CV, но зато есть функции позволяющие рассчитать стандартное отклонение (СТАНДОТКЛОН) и среднее значение (СРЗНАЧ).

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

В OpenOffice данный показательрассчитывается аналогично. Функциястандартного отклонения здесь — STDEV,а функция среднего значения— AVERAGE.

Давайтерассмотрим пример расчёта коэффициентавариации в Open Office.Предположим, что у насесть три потенциальных объекта дляинвестиций — объект А, объект Б и объектВ. Прибыль по каждому из этих проектовза последние 6 лет занесена в таблицупредставленную ниже:

Давайтерассчитаем значение CVдля каждого из этихобъектов. Начнём с расчёта стандартныхотклонений. Для этого применим к рядузначений прибыли отдельно по каждомуобъекту функцию STDEV:

Аналогичнымобразом рассчитаем среднее значениедля каждого ряда данных:

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

Кликните по картинке для увеличения

Очевидно, что из всех представленных объектов инвестиций предпочтительным будет объект Б имеющий наименьшее значение коэффициента CV.

Пример использования коэффициента вариации для выбора объекта инвестиций

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

Инвестор выбирает SPDR S&P 500 ETF, Invesco QQQ ETF и iShares Russell 2000 ETF.

Затем он анализирует доходность и волатильность выбранных ETF за последние 15 лет и предполагает, что в будущем они могут иметь аналогичную доходность в отношении к своим долгосрочным средним значениям.

Для принятия решения инвестором используется следующая 15-летняя историческая информация:

• SPDR S&P 500 ETF имеет среднюю годовую доходность 5,47% и стандартное отклонение 14,68%. Коэффициент вариации SPDR S&P 500 ETF составляет 2,68;
• Средняя годовая доходность Invesco QQQ ETF составляет 6,88%, а стандартное отклонение-21,31%. Коэффициент вариации QQQ равен 3,09;
• iShares Russell 2000 ETF имеет среднюю годовую доходность 7,16% и стандартное отклонение 19,46%. Коэффициент вариации IWM равен 2,72.

Исходя из этих данных, инвестор может инвестировать либо в SPDR S&P 500 ETF, либо в iShares Russell 2000 ETF, так как соотношение риска и вознаграждения для них является сравнительно одинаковым. А для Invesco QQQ ETF соотношение риск-доходность, как видите, будет несколько хуже.

Источник: https://www.AzbukaTreydera.ru/cv.html

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

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

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

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

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

В последних версиях Excel она разделена на две части, в зависимости от того, как происходит вычисление: СТАНДОТКЛОН.Г(по генеральной совокупности), СТАНДОТКЛОН.В(по выборке).

Записываются функции следующим образом:

= СТАНДОТКЛОН(Число1;Число2;…) — Для старой версии

= СТАНДОТКЛОН.В(Число1;Число2;…) — Для новой версии соответственно.

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

2. Откроется окно мастера функций. Перейдите в категорию “Статистические”, затем выберите строку с названием “СТАНДОТКЛОН”(СТАНДОТКЛОН .В или .Г соответственно). Нажмите “ОК”.

3. В открывшемся окне аргументов необходимо указать диапазон ячеек, с которыми будет производиться расчет. Также можно ввести конкретные числа. После указания параметров нажмите кнопку “ОК”.

4. В ранее выделенной ячейке отобразится итоговый расчет стандартного отклонения.

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

Среднее арифметическое отражает общую сумму значений числового ряда, поделенных на их количество. Для этого используем функцию СРЗНАЧ.

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

2. Перейдите в категорию “Статистические” и выберите поле с наименованием “СРЗНАЧ”, после этого нажмите “ОК”.

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

Шаг 3. Нахождение коэффициента вариации

Мы получили все предварительные данных для конечных вычислений, поэтому приступаем к последнему шагу, а именно к расчету коэффициента вариации.

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

2. Снова вернитесь к ранее выбранной ячейке и выделите ее двойным щелчком левой кнопки мыши. Поставьте в ней знак “=”, затем выделите ячейку с результатом вычислений стандартного отклонения. Теперь нажмите кнопку “/”(разделить) на клавиатуре и выберите ячейку со средним арифметическим. После ввода данных нажмите клавишу Enter.

3. Результат будет автоматически выведен на экран.

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

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

“Диапазон значений” указывает с исходными данными. Можете указать его вручную, либо просто выделив нужный диапазон ячеек. Вместо оператора СТАНДОТКЛОН также можно ввести СТАНДОТКЛОН .В или СТАНДОТКЛОН .Г соответственно(для новых версий Excel).

2. После занесения всех параметров нажмите клавишу Enter, чтобы получить конечный результат.

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

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

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

Cреднее линейное отклонение — это средний модуль отклонений значений X от среднего арифметического значения. Его можно рассчитывать по формуле средней арифметической простой — получим среднее линейное отклонение простое:

Например, студент сдал 4 экзамена и получил следующие оценки: 3, 4, 4 и 5.Ранее уже была рассчитана средняя арифметическая= 4. Рассчитаем среднее линейное отклонение простое: Л = (|3-4|+|4-4|+|4-4|+|5-4|)/4 = 0,5.

Источник: https://browhennashop.ru/razmah-variacii-v-excel/

Коэффициент вариации – что это, что показывает, формула расчета в Excel, среднеквадратичное и стандартное отклонение

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

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

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

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

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

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

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

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

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

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

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

  Как провести регрессионный анализ в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Способы удаления пустых строк и столбцов в Excel

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

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

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

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

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

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

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

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

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

Дисперсия

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

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

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

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

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

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

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

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

  Где находится «Поиск решения» в Excel и как им пользоваться

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

Источник: http://composs.ru/koefficient-variacii-v-excel/

Прогнозируем с Excel: как посчитать коэффициент вариации

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

Именно расчёту последнего стоит уделить особое внимание.

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

В этой статье мы расскажем, как автоматизировать расчеты при прогнозировании данных

Что такое коэффициент вариации и для чего он нужен?

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

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

Коэффициент вариации принято измерять в процентном выражении и отображать с его помощью однородность временного ряда.

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

Так, если вы видите, что значение коэффициента равно 0%, то с уверенностью заявляйте о том, что ряд является однородным, а значит, все значения в нём равны один с другим.

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

Как найти среднее квадратичное отклонение?

Поскольку для расчёта показателя вариации в Excel нам необходимо использовать среднее квадратичное отклонение, то вполне уместно будет выяснить, как нам посчитать этот параметр.

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

Рассчитываем коэффициент в Экселе

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

Вставьте формулу и укажите диапазон данных

Для того чтобы рассчитать показатель вариации в Excel, необходимо вспомнить школьный курс математики и разделить стандартное отклонение на среднее значение выборки. То есть на деле формула выглядит следующим образом — СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных). Ввести эту формулу необходимо в ту ячейку Excel, в которой вы хотите получить нужный вам расчёт.

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

1. Откройте вкладку «».
2. Найдите в ней категорию «Формат ячеек» и выберите необходимый параметр.

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

Выберите «Процентный», а при необходимости укажите число десятичных знаков

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

Всё ещё не можете сделать качественное сравнение степени разброса данных? Теряетесь в масштабах выборки? Тогда прямо сейчас принимайтесь за дело и осваивайте на практике весь теоретический материал, который был изложен выше! Пусть статистический анализ и разработка прогноза больше не вызывают у вас страха и негатива. Экономьте свои силы и время вместе с табличным редактором Excel.

Источник: https://nastroyvse.ru/programs/review/koefficient-variacii-v-exsel.html

Расчет показателей вариации в Excel

Оригинал http://statanaliz.info/index.php/excel/formuly/37-raschet-pokazatelej-variatsii-v-excel

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

Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных.

Кстати, на этом указанная тема не закончилась, к выпуску планируются новые статьи – следите за рекламой! Однако сухая теория без инструментов реализации – вещь не сильно полезная.

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

Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:

— максимальное и минимальное значение

— среднее линейное отклонение

— дисперсия (по генеральной совокупности и по выборке)

— среднее квадратическое отклонение (по генеральной совокупности и по выборке)

— коэффициент вариации

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

Максимальное и минимальное значение

Начнем с формул максимума и минимума. Что такое максимальное и минимальное значение, уверен, знают почти все. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое (может быть и отрицательным числом).

Это крайние значения в совокупности данных, обозначающие границы их вариации. Примеры реального использования каждый может придумать сам – их полно. Это и минимальные/максимальные цены на что-нибудь, и выбор наилучшего или наихудшего решения задачи, и всего, чего угодно.

Минимум и максимум – весьма информативные показатели. Давайте теперь их рассчитаем в Excel.

Как нетрудно догадаться, делается сие элементарно – как два клика об асфальт. В Мастере функций следует выбрать: МАКС – для расчета максимального значения, МИН – для расчета минимального значения. Для облегчения поиска перечень всех функций можно отфильтровать по категории «Статистические».

Выбираем нужную формулу, в следующем окошке указываем диапазон данных (в котором ищется максимальное или минимальное значение) и жмем «ОК».

Функции МАКС и МИН достаточно часто используются, поэтому разработчики Экселя предусмотрительно добавили соответствующие кнопки в ленту. Они находятся там же, где суммаи среднее значение – в разворачивающемся списке.

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

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

Среднеквадратическое отклонение по генеральной совокупности – это корень из генеральной дисперсии.

https://www.youtube.com/watch?v=ZNHPq4W6hRs

Выборочное среднеквадратическое отклонение – это корень из выборочной дисперсии.

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

— Среднеквадратическое отклонение по генеральной совокупности СТАНДОТКЛОН.Г

— Среднеквадратическое отклонение по выборке СТАНДОТКЛОН.В.

С названием этого показателя может возникнуть путаница, т.к. часто можно встретить синоним «стандартное отклонение». Пугаться не нужно – смысл тот же.

Далее, как обычно, указываем нужный диапазон и нажимаем на «ОК». Среднее квадратическое отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными. Об этом ниже.

Коэффициент вариации

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

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

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

=СТАНДОТКЛОН.Г(диапазон)/СРЗНАЧ(диапазон)

В скобках должен быть указан диапазон данных. При необходимости используется среднее квадратическое отклонение по выборке (СТАНДОТКЛОН.В).

Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на ленте на закладке «»:

Изменить формат также можно, выбрав «Формат ячеек» из выпадающего списка после выделения нужной ячейки правой кнопкой мышки.

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

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

В целом, с помощью Excel все, или почти все, статистические показатели рассчитываются очень просто. Если что-то непонятно, всегда можно воспользоваться окошком для поиска в Мастере функций. Ну, и Гугл в помощь.

Легкой работы в Excel и до встречи на блоге statanaliz.info.

Оригинал и другие статьи http://statanaliz.info/index.php/excel/formuly/37-raschet-pokazatelej-variatsii-v-excel

Источник: http://vniioh.ru/raschet-pokazatelej-variacii-v-excel/