Каждый раз, выполняя в Excel статистический анализ, нам приходится сталкиваться с расчётом таких значений, как дисперсия, среднеквадратичное отклонение и, разумеется, коэффициент вариации. Именно расчёту последнего стоит уделить особое внимание. Очень важно, чтобы каждый новичок, который только приступает к работе с табличным редактором, мог быстро подсчитать относительную границу разброса значений.
В этой статье мы расскажем, как автоматизировать расчеты при прогнозировании данных
Что такое коэффициент вариации и для чего он нужен?
Итак, как мне кажется, нелишним будет провести небольшой теоретический экскурс и разобраться в природе коэффициента вариации. Этот показатель необходим для отражения диапазона данных относительно среднего значения. Иными словами, он показывает отношение стандартного отклонения к среднему значению. Коэффициент вариации принято измерять в процентном выражении и отображать с его помощью однородность временного ряда.
Коэффициент вариации станет незаменимым помощником в том случае, когда вам необходимо будет сделать прогноз по данным из заданной выборки. Этот индикатор выделит главные ряды значений, которые будут наиболее полезными для последующего прогнозирования, а также очистит выборку от малозначительных факторов. Так, если вы видите, что значение коэффициента равно 0%, то с уверенностью заявляйте о том, что ряд является однородным, а значит, все значения в нём равны один с другим. В случае, если коэффициент вариации принимает значение, превышающее отметку в 33%, то это говорит о том, что вы имеете дело с неоднородным рядом, в котором отдельные значения существенно отличаются от среднего показателя выборки.
Как найти среднее квадратичное отклонение?
Поскольку для расчёта показателя вариации в Excel нам необходимо использовать среднее квадратичное отклонение, то вполне уместно будет выяснить, как нам посчитать этот параметр.
Из школьного курса алгебры мы знаем, что среднее квадратичное отклонение — это извлечённый из дисперсии квадратный корень, то есть этот показатель определяет степень отклонения конкретного показателя общей выборки от её среднего значения. С его помощью мы можем измерить абсолютную меру колебания изучаемого признака и чётко её интерпретировать.
Рассчитываем коэффициент в Экселе
К сожалению, в Excel не заложена стандартная формула, которая бы позволила рассчитать показатель вариации автоматически. Но это не значит, что вам придётся производить расчёты в уме. Отсутствие шаблона в «Строке формул» никоим образом не умаляет способностей Excel, потому вы вполне сможете заставить программу выполнить необходимый вам расчёт, прописав соответствующую команду вручную.
Вставьте формулу и укажите диапазон данных
Для того чтобы рассчитать показатель вариации в Excel, необходимо вспомнить школьный курс математики и разделить стандартное отклонение на среднее значение выборки. То есть на деле формула выглядит следующим образом — СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных). Ввести эту формулу необходимо в ту ячейку Excel, в которой вы хотите получить нужный вам расчёт.
Не забывайте и о том, что поскольку коэффициент выражается в процентах, то ячейке с формулой нужно будет задать соответствующий формат. Сделать это можно следующим образом:
- Откройте вкладку «Главная».
- Найдите в ней категорию «Формат ячеек» и выберите необходимый параметр.
Как вариант, можно задать процентный формат ячейке при помощи клика по правой кнопке мыши на активированной клеточке таблицы. В появившемся контекстном меню, аналогично вышеуказанному алгоритму нужно выбрать категорию «Формат ячейки» и задать необходимое значение.
Выберите «Процентный», а при необходимости укажите число десятичных знаков
Возможно, кому-то вышеописанный алгоритм покажется сложным. На самом же деле расчёт коэффициента так же прост, как сложение двух натуральных чисел. Единожды выполнив эту задачу в Экселе, вы больше никогда не вернётесь к утомительным многосложным решениям в тетрадке.
Всё ещё не можете сделать качественное сравнение степени разброса данных? Теряетесь в масштабах выборки? Тогда прямо сейчас принимайтесь за дело и осваивайте на практике весь теоретический материал, который был изложен выше! Пусть статистический анализ и разработка прогноза больше не вызывают у вас страха и негатива. Экономьте свои силы и время вместе с табличным редактором Excel.
Содержание
- Вычисление коэффициента вариации
- Шаг 1: расчет стандартного отклонения
- Шаг 2: расчет среднего арифметического
- Шаг 3: нахождение коэффициента вариации
- Вопросы и ответы
Одним из основных статистических показателей последовательности чисел является коэффициент вариации. Для его нахождения производятся довольно сложные расчеты. Инструменты Microsoft Excel позволяют значительно облегчить их для пользователя.
Вычисление коэффициента вариации
Этот показатель представляет собой отношение стандартного отклонения к среднему арифметическому. Полученный результат выражается в процентах.
В Экселе не существует отдельно функции для вычисления этого показателя, но имеются формулы для расчета стандартного отклонения и среднего арифметического ряда чисел, а именно они используются для нахождения коэффициента вариации.
Шаг 1: расчет стандартного отклонения
Стандартное отклонение, или, как его называют по-другому, среднеквадратичное отклонение, представляет собой квадратный корень из дисперсии. Для расчета стандартного отклонения используется функция СТАНДОТКЛОН. Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.
Синтаксис данных функций выглядит соответствующим образом:
= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)
- Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
- Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».
- Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»
- В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.
Урок: Формула среднего квадратичного отклонения в Excel
Шаг 2: расчет среднего арифметического
Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.
- Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».
- В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».
- Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».
- Результат вычисления среднего арифметического выводится в ту ячейку, которая была выделена перед открытием Мастера функций.
Урок: Как посчитать среднее значение в Excel
Шаг 3: нахождение коэффициента вариации
Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.
- Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «Главная». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.
- Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.
- Как видим, результат расчета выведен на экран.
Таким образом мы произвели вычисление коэффициента вариации, ссылаясь на ячейки, в которых уже были рассчитаны стандартное отклонение и среднее арифметическое. Но можно поступить и несколько по-иному, не рассчитывая отдельно данные значения.
- Выделяем предварительно отформатированную под процентный формат ячейку, в которой будет выведен результат. Прописываем в ней формулу по типу:
= СТАНДОТКЛОН.В(диапазон_значений)/СРЗНАЧ(диапазон_значений)Вместо наименования «Диапазон значений» вставляем реальные координаты области, в которой размещен исследуемый числовой ряд. Это можно сделать простым выделением данного диапазона. Вместо оператора СТАНДОТКЛОН.В, если пользователь считает нужным, можно применять функцию СТАНДОТКЛОН.Г.
- После этого, чтобы рассчитать значение и показать результат на экране монитора, щелкаем по кнопке Enter.
Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.
Как видим, программа Эксель позволяет значительно упростить расчет такого сложного статистического вычисления, как поиск коэффициента вариации. К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается. Таким образом, в Excel её может выполнить даже человек, который не имеет высокого уровня знаний связанных со статистическими закономерностями.
Еще статьи по данной теме:
Помогла ли Вам статья?
ТАБЛИЧНЫЙ
ПРОЦЕССОР MICROSOFT EXCEL
Практическая работа №4
Выполнение
расчетов и оптимизация изображения таблицы
ПРАКТИЧЕСКАЯ ЧАСТЬ
Порядок выполнения:
1.
Для выполнения Задания создайте документ MS Excel под именем названием
Фамилия_Имя_Excel_4.xlsx. и переименуйте в рабочей книге Лист 1 на Задание_Ваша
фамилия.
2.
Введите данные на рабочий лист (см. рис. 1):
Рис. 1. Текстовые и
числовые данные для выполнения задания
3.
Вставьте формулы для вычислений в столбцах Районный
коэффициент, Начислено, Подоходный налог, Сумма к выдаче
(в квадратных скобках указаны номера столбцов):
[4] = [2] ∙ k
[5] = [2] + [4]
[6] = ([5] – [3]) ∙
n
[7] = [5] – [6]
4.
В последней строке вставьте формулы для вычисления итоговых сумм
по столбцам Подоходный налог и Сумма к выдаче. Примечание:
сумма выполняется через функцию СУММ().
5.
Ниже таблицы вставьте формулы для вычисления: а)
максимальной суммы к выдаче; б) среднего оклада; в) минимального
налога; г) количества рабочих, оклады которых превышают 16 000 руб.
(функция СЧЁТЕСЛИ()); д) суммарный подоходный налог рабочих, имеющих
налоговые вычеты (функция СУММЕСЛИ()); е) суммарный подоходный налог
рабочих, оклады которых превышают 16 000 руб. и не имеющих налоговые вычеты
(функция СУММЕСЛИМН()).
6.
Отобразите значения во всей таблице в денежном формате с двумя
знаками после десятичной запятой.
7.
Установите в итоговой строке заливку ячеек черным цветом, белый
цвет шрифта, полужирное начертание.
8.
В итоге у Вас должна получиться следующая работа (см. рис. 2):
Рис.
2. Окончательный вид расчетов
9.
Создайте Лист 2 и переименуйте его на Надбавка_Ваша Фамилия.
10.
С Листа 1 скопируйте столбцы Ф.И.О. и Сумма к выдаче,
и переместите их на Лист 2.
11.
Добавьте к таблице поля Сообщение о надбавке, Величина
надбавки, Итоговая сумма. Введите заголовок таблицы Расчет
надбавки.
Введите нумерацию столбцов (см. рис. 3).
Рис. 3. Добавление
столбцов
17 авг. 2022 г.
читать 2 мин
Соотношение используется для сравнения двух чисел. Соотношения полезны для понимания того, насколько велико одно значение по сравнению с другим.
Например, предположим, что A = 40 и B = 10.
Чтобы рассчитать отношение A к B, мы можем использовать следующий двухэтапный процесс:
Шаг 1 : Найдите наибольший общий делитель (наибольшее целое число, на которое будет делиться каждое значение)
- Наибольшее значение, которое будет делиться как на 40, так и на 10, равно 10 .
Шаг 2 : Разделите каждое значение на наибольший общий делитель и запишите результат в виде A:B.
- Отношение 40 к 10 будет записано как 4:1 .
Чтобы вычислить соотношение между любыми двумя числами в Excel, мы можем использовать следующую формулу:
= A2 /GCD( A2 , B2 )&":"& B2 /GCD( A2 , B2 )
Эта конкретная формула вычисляет отношение между значением в ячейке A2 и значением в ячейке B2 , используя функцию НОД в Excel для автоматического поиска наибольшего общего делителя между двумя значениями.
В следующем примере показано, как использовать эту функцию на практике.
Пример: расчет коэффициентов в Excel
Предположим, у нас есть следующие два списка значений в Excel:
Предположим, мы хотели бы рассчитать отношение значения 1 к значению 2 в каждой строке.
Мы можем ввести следующую формулу в ячейку C2 :
= A2 /GCD( A2 , B2 )&":"& B2 /GCD( A2 , B2 )
На следующем снимке экрана показано, как использовать эту формулу на практике:
Оказывается, наибольший общий делитель 12 и 5 равен 1. Таким образом, когда мы делим каждое значение на 1, у нас просто остается отношение 12:5 .
Мы можем скопировать и вставить эту формулу в ячейку C2 до каждой оставшейся ячейки в столбце C, чтобы вычислить соотношение для двух значений в каждой строке:
Вот как интерпретировать результаты:
- Соотношение между 12 и 5 составляет 12:5 .
- Соотношение между 40 и 10 составляет 4:1 .
- Соотношение между 20 и 8 составляет 5:2 .
И так далее.
Примечание.Полную документацию по функции НОД в Excel можно найти здесь .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Excel: как найти уникальные значения из нескольких столбцов
Excel: как сопоставить два столбца и вернуть третий