Коэффициент вариации в Excel: формула и оценка однородности

Отклонение 4,32 балла — это много или мало? Само по себе число ни о чём не говорит: всё зависит от того, вокруг какого среднего эти баллы разбросаны.

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

🧮Онлайн-калькулятор коэффициента вариацииПосчитайте свои данные за пару минут — нажмите, чтобы открыть→

Что показывает коэффициент вариации

Стандартное отклонение измеряется в тех же единицах, что и сам показатель. Разброс в 4,32 балла при среднем 47,40 — это девятая часть среднего, мелочь. Тот же разброс при среднем 5 баллов означал бы, что группа разваливается надвое.

Коэффициент вариации — это отклонение, выраженное в процентах от среднего. Обозначают его V или CV, считают по формуле V = σ / M × 100 %.

Он безразмерный: не баллы, не сантиметры, а проценты. Отсюда два его применения в дипломе — оценка однородности выборки и сравнение разброса показателей, измеренных в разных единицах.

Формула в Excel: готовой функции нет

В Excel нет ни КОЭФВАРИАЦИИ, ни CV — искать бесполезно. Коэффициент собирают из двух обычных функций: делят стандартное отклонение на среднее и умножают на 100.

Пусть данные лежат в столбце B со второй по двадцать первую строку:

=СТАНДОТКЛОН(B2:B21)/СРЗНАЧ(B2:B21)*100

В английском Excel — =STDEV(B2:B21)/AVERAGE(B2:B21)*100.

Умножение на 100 нужно, только если ячейка в обычном числовом формате. Если задать ей процентный формат, *100 убирают — иначе получится 910 % вместо 9,1 %.

Пример на реальных данных

Ситуативная тревожность 20 студентов: 48, 52, 45, 41, 54, 47, 50, 43, 46, 51, 44, 49, 53, 42, 47, 55, 40, 46, 50, 45.

Что получается на этих двадцати значениях

Проверьте себя устно: 4,32 — это чуть меньше десятой части от 47,40, значит и процент должен выйти чуть меньше десяти. Получилось 30 % или 0,09 — ищите ошибку в формате ячейки или в диапазоне.

Считать σ и M отдельными ячейками, а потом делить одну на другую — то же самое. Так даже нагляднее: все три числа видны рядом, и в таблицу диплома их переносить удобнее.

Как оценить однородность выборки

Это главное, ради чего коэффициент вариации попадает в диплом. Вывод строится по трём порогам.

• До 10 % — выборка однородная. Люди в группе похожи, среднее хорошо описывает всех.
• 10–20 % — средняя однородность. Работать с данными можно, но разброс уже заметный.
• Свыше 20 % — выборка разнородная. Среднее становится «средней температурой»: за ним прячутся очень разные испытуемые.

Наш пример: 4,32 / 47,40 × 100 = 9,1 %. Меньше десяти — группа однородная, среднее 47,40 балла представительно.

Порог, за которым среднее считают уже непригодным для выводов, — 33 %. Такое значение говорит, что в выборке смешались минимум две разные подгруппы.

Что делать с высоким V: проверить крайние значения, при необходимости разбить выборку на подгруппы и описывать их отдельно, а сравнивать группы — ранговыми критериями, а не по средним.

Когда коэффициент вариации незаменим: сравнение разных единиц

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

Пример. В дипломе по физической культуре обследовали 18 легкоатлетов: рост, вес и результат в беге на 100 м. По «сырым» отклонениям самым разбросанным кажется рост — 6,2 см против 0,58 с у бега. В процентах картина переворачивается.

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

Тот же приём работает для сравнения групп и для оценки стабильности техники: у кого V ниже, тот выполняет упражнение ровнее от попытки к попытке.

Когда его считать нельзя

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

Среднее близко к нулю. Деление на почти-ноль раздувает результат до сотен процентов. Классический случай — прирост показателя: разности «после минус до» в среднем дают 0,4 кг при отклонении 3,8 кг, и V выходит 950 %.

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

Шкала без абсолютного нуля. Стены, T-баллы, станайны, IQ-подобные шкалы: точка отсчёта в них выбрана условно, поэтому «процент от среднего» зависит от того, как шкалу отцентровали. Формально V применим только к шкале отношений — росту, весу, времени, количеству повторений, сырым баллам теста.

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

Готовый файл считает сам

Если считать нужно не одно число, а всю статистику для третьей главы — есть готовая книга Excel. Вставляете свои данные в жёлтые ячейки, и всё считается само.

Коэффициент вариации считается на листе «6. Ошибка среднего и вариация»: там же ошибка среднего, готовая строка «M ± m» и трактовка однородности по трём зонам — её остаётся перенести в текст работы.

Три способа посчитать — что выбрать

← листайте вправо, чтобы сравнить →

Число везде получится одно и то же — 9,1 %. Разница в том, сколько работы остаётся на вас после того, как оно появилось.

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

Что писать в дипломе

Коэффициент вариации приводят одной фразой после описания среднего:

Средний уровень ситуативной тревожности в группе составил 47,40 ± 4,32 балла (M ± σ). Коэффициент вариации V = 9,1 % не превышает 10 %, что свидетельствует об однородности выборки по данному показателю.

Если группа оказалась разнородной:

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

При сравнении нескольких показателей:

Наиболее стабильным показателем оказался рост (V = 3,5 %), наиболее изменчивым — масса тела (V = 7,9 %).

Округляйте до одного знака после запятой и всегда ставьте знак процента: «9,1 %», а не «0,091» и не «9,1».

Частые ошибки

• Ищут в Excel функцию «коэффициент вариации». Её там нет, и полчаса уходит на перебор списка функций.Соберите формулу сами: =СТАНДОТКЛОН(диапазон)/СРЗНАЧ(диапазон)*100.
• Умножают на 100 в ячейке с процентным форматом. Excel умножит ещё раз, и вместо 9,1 % получится 910 %.Либо обычный формат и *100, либо процентный формат без умножения.
• Считают V для приростов и разностей. Среднее около нуля раздувает результат до сотен процентов.Разброс приростов описывайте отклонением, а коэффициент вариации считайте по исходным значениям.
• Берут СТАНДОТКЛОНП вместо СТАНДОТКЛОН. Разброс занижается, вслед за ним занижается и V.В дипломе нужен вариант «по выборке» — СТАНДОТКЛОН.
• Сравнивают V разных методик и делают вывод о людях. Разброс зависит и от самой шкалы: у теста из 10 пунктов он всегда крупнее, чем у теста из 100.Сравнивайте V одного показателя между группами, а не разные методики между собой.

Частые вопросы

Есть ли в Excel готовая функция коэффициента вариации

Нет, ни в русской, ни в английской версии. Считают делением: =СТАНДОТКЛОН(B2:B21)/СРЗНАЧ(B2:B21)*100. Пакет анализа тоже отдельной строки «коэффициент вариации» не выдаёт — только среднее и отклонение, из которых его собирают.

Какой порог брать — 10 % или 33 %

Оба, но для разного. Шкала 10 / 20 % — это оценка однородности выборки, её и приводят в третьей главе. Порог 33 % — граница, за которой среднее арифметическое считают непредставительным: описывать такую группу средним уже нельзя.

В процентах или в долях

В дипломе — в процентах, с одним знаком после запятой. Доля (0,091) — промежуточный результат, в текст работы она не идёт.

Коэффициент вариации получился больше 100 %. Это ошибка

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

Можно ли считать V по баллам психологического теста

По сырым баллам — да, у них есть естественный ноль. По стенам, T-баллам и другим стандартизированным шкалам — нет: точка отсчёта там условная, и процент от среднего теряет смысл.

Короткий алгоритм

1. Соберите значения в один столбец — без заголовков и пустых строк внутри.
2. Посчитайте =СРЗНАЧ(диапазон) и =СТАНДОТКЛОН(диапазон).
3. Введите =СТАНДОТКЛОН(диапазон)/СРЗНАЧ(диапазон)*100 — это V в процентах.
4. Убедитесь, что среднее заметно больше нуля и все значения положительные, иначе показатель не применим.
5. Сравните результат со шкалой: до 10 % — однородная, 10–20 % — средняя, свыше 20 % — разнородная.
6. Впишите вывод в третью главу одной фразой после «M ± σ».

Или пропустите все шаги: скачайте файл выше либо вставьте данные в калькулятор.

Что ещё почитать

• Стандартное отклонение в Excel — числитель нашей формулы и развилка «выборка или совокупность».
• Среднее значение в Excel — знаменатель: СРЗНАЧ, СРЗНАЧЕСЛИ и подсчёт по группам.
• Дисперсия в Excel — откуда берётся σ и почему её не путают с дисперсионным анализом.
• Как посчитать статистику в Excel: формулы — остальные показатели для третьей главы.
• Описательная статистика в дипломе — как собрать итоговую таблицу с M, σ и V.

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