Как посчитать коэффициент вариации в Excel
Содержание
В программе Microsoft Excel предусмотрен набор инструментов для анализа статистических данных и интерпретации результатов. На основе встроенных функций и специальных формул можно выполнять довольно сложные расчеты. В данном материале рассмотрим, как рассчитать коэффициент вариации в Excel, предварительно определив промежуточные показатели из выборки данных.
Что такое коэффициент вариации
Данный показатель отражает степень разброса значений в определенной последовательности или в наборе данных по отношению к среднему значению. Коэффициент вариации также называется относительным стандартным отклонением. Показатель выражается, как правило, в процентах. Стандартное обозначение данного коэффициента при записи — cv.
Коэффициент вариации чаще всего используется для сравнения разброса значений между двумя (или более) наборами данных. На практике этот показатель используют в аналитических направлениях химии, физики, биологии, инженерии. Также относительное стандартное отклонение является одним из показателей в экономических моделях.
Отметим, что коэффициент вариации вычисляют только для данных, которые могут подвергаться сравнению. Если речь идет об интервальных шкалах, в которых значения носят вариативный характер, определять относительное стандартное отклонение нецелесообразно. В качестве примера можно привести сравнение данных из температурных шкал по Цельсию и Фаренгейту. Несмотря на то, что обе величины определяют температуру, они иначе интерпретируются и соотносятся друг с другом.
В целом, коэффициент вариации является показателем, который отображает отношение стандартного отклонения и средних значений в выборке. Целесообразно использовать данный параметр при сравнении двух или более наборов данных.
Расчет коэффициента вариации в Эксель
Отдельная формула в Excel для определения относительного стандартного отклонения не предусмотрена. Тем не менее в Экселе можно выполнить расчеты, необходимые для определения этого показателя. Данную процедуру можно условно разделить на 3 этапа, каждый из которых мы рассмотрим детально.
Определяем среднее арифметическое в наборе данных
Чтобы определить коэффициент вариации в таблице Excel, предварительно необходимо вычислить среднее значение в наборе данных. Для этого в программе есть специальная формула, которая позволит найти данный параметр максимально быстро. В дальнейшем он поможет при вычислении коэффициента вариации в Excel.
Как пример, рассмотрим таблицу, в которой отображено количество продаж определенных товаров. В данном случае вычисление коэффициента вариации позволит определить изменчивость спроса на ту или иную позицию. Такой вариант расчета нередко используют при оценке прибыльности в предпринимательстве и трейдинге.
Начнем расчет коэффициента вариации с поиска среднего значения для каждой позиции. Для этого применяем формулу СРЗНАЧ. Она возвращает среднее арифметическое из набора чисел в последовательности. В нашем случае последовательность прописана в ячейках, поэтому в качестве аргумента для функции указываем диапазон.
Далее используем маркер автозаполнения, чтобы применить формулу также и к другим строкам в таблице. Таким образом мы получим средние значения, которые потребуются для расчета относительного стандартного отклонения. Отметим, что в качестве аргумента для СРЗНАЧ выступает минимум 2 числа. Если указать только одно значение, то формула для определения среднего арифметического вернет это же самое число. Однако ошибка в ячейке фиксироваться не будет, поэтому в определенных случаях такой способ применения СРЗНАЧ может быть оправдан.
Формат данных для этой ячейки рекомендуется сменить с общего на числовой. Это позволит исключить вероятность ошибок, связанных с форматированием значений во время дальнейшего расчета показателей.
Определяем стандартное отклонение
Это еще один параметр, который требуется для определения коэффициента вариации в Excel. Стандартное отклонение определяет разницу между значениями в выборке данных и их средним арифметическим. Чем выше стандартное отклонение, тем больше разброс между значениями и последовательности. Сам по себе этот показатель также используется в анализе и статистике.
Расчет стандартного отклонения осуществляется с помощью довольно сложной формулы. Этот показатель можно выразить, как корень из суммы квадратов разниц между значениями выборки и средним. Намного проще найти стандартное отклонение, используя функцию СТАНДОТКЛОН в Эксель. Она находит разброс данных в выборке относительно среднего значения, используя числа либо диапазон ячеек в качестве аргумента.
Отметим, что в Excel предусмотрено 2 вида формул — СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В. В первом случае вычисление отклонения осуществляется по генеральной совокупности, а во втором — по выборке из нее. Они возвращают примерно равные значения, и работают не только с числами, но и с массивами данными, ссылками. Однако нужно учитывать, что формула не находит среднеквадратичное значение в том случае, если в последовательности есть логические значения, текст или вывод ошибки. Для этих целей может использоваться формула СТАНДОТКЛОНПА.
Вычисляем коэффициент вариации
После того как нам удалось разобраться в основных показателях, можно приступить к определению коэффициента вариации. В данном случае все довольно просто. Показатель выражается в виде результата деления стандартного отклонения и среднего арифметического из выборки.
В нашем примере стандартное отклонение записано в столбце J, а среднее из выборки — в колонке I. Чтобы получить результат, достаточно применить арифметический оператор деления.
Таким же образом рассчитываем коэффициент и для других позиций в таблице. Отметим, что данный показатель выражается в процентах. Если планируется дальнейшее вычисление с показателем CV, нужно изменить формат данных, чтобы значения отображались корректно.
Можно выполнить расчет и альтернативным способом. В нашем примере мы узнали показатели для вычисления коэффициента заранее, и записали их в определенные ячейки. В свою очередь, не исключается возможность выполнить вычисление напрямую, не выполняя вспомогательные расчеты заранее. В данном случае формула для определения коэффициента будет выглядеть следующим образом.
Этот способ будет удобным при условии, если нет возможности добавлять в таблицу ячейки со вспомогательными вычислениями. Во многих случаях они могут оказаться неуместными и повлияют на качество визуализации. Поэтому можно воспользоваться таким способом, не осуществляя предварительный расчет промежуточных показателей, если они не потребуются в дальнейшем.
Выяснив, как посчитать коэффициент вариации, необходимо обратить внимание на еще один важный момент. В Excel предусмотрена функция КОВАРИАЦИЯ. Некоторые пользователи ошибочно предполагают, что она возвращает коэффициент вариации. На самом же деле она действует иначе и определяет отношения между значениями в двух наборах данных. Этот показатель также может использоваться в статистике, однако не имеет никакого отношения к коэффициенту вариации, рассчитанному выше в данном материале.
Подводим итоги
Коэффициент вариации — статистический показатель, который отражает отношение стандартного отклонения и среднего арифметического из выборки числовых данных. Как правило, этот показатель выражается в процентах. Он используется в различных направлениях анализа и на практике часто задействуется при оценке определенных активов.
Для определения коэффициента вариации в Excel используются формулы СТАНДОТКЛОН и СРЗНАЧ. Можно выполнить предварительный расчет и использовать промежуточные результаты для подстановки в функцию, либо прописать конструкцию из двух формул напрямую в ячейку. Оба варианта действуют одинаково и выводят идентичный результат, а отличаются только способом записи.
Загрузка...
