Как рассчитать медиану и квартили в excel

Функция КВАРТИЛЬ

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

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

Дополнительные сведения о новых функциях см. в разделах Функция КВАРТИЛЬ.ИСКЛ и Функция КВАРТИЛЬ.ВКЛ.

Синтаксис

Аргументы функции КВАРТИЛЬ описаны ниже.

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

Часть Обязательный. Значение, которое требуется вернуть.

Пояснения к таблицам

Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.

Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.

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

Квартили используются для распределения диапазона чисел на четыре равные части:

  1. Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
  2. Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
  3. Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 75% чисел диапазона, которые меньше полученного значения, а во второй (25%) – больше.

Функция КВАРТИЛЬ.ВКЛ может быть использована не только для определения медианы выборки (второго квартиля), а и нахождения минимального и максимального значений соответственно. При работе с большими диапазонами чисел для подобных расчетов рекомендуется использовать функции МИН и МАКС соответственно.

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

  • Qp – p-й квантиль (является частным случаем квантиля);
  • x – индекс квантиля;
  • i – индекс элемента из выборки;
  • A1,A2…Ai – элементы выборки, отсортированной по возрастанию значений.

Для расчета индекса квантиля (x) функция КВАРТИЛЬ.ВКЛ используют формулу:

x=(n-1)p, где n – количество элементов в диапазоне.

Функция КВАРТИЛЬ.ИСКЛ использует формулу x=(n+1)p.

В Excel принято так, что первые выше указанные 2 функции используют метод N-1-интерполяцию, а третья функция – N+1-интерполяцию.

Примеры использования функций КВАРТИЛЬ в Excel

Пример 1. В столбце таблицы содержится числовая последовательность. Определить число, которое делит последовательность на 2 части, 25% первой – числа меньше полученного значения, а 75% — больше. Использовать N+1-интерполяцию.

Вид таблицы данных:

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

  • A2:A15 – диапазон ячеек с исследуемыми числами;
  • 1 – номер вычисляемого квартиля.

Проверим утверждение о том, что второй квартиль соответствует медиане выборке. Определим 2-й по формуле:

Полученные значения совпадают:

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

Статистический анализ роста доли дохода в Excel за период

Пример 2. В таблице приведены данные о доходах предпринимателя за год. Доказать, что примерно 75% значений меньше, чем третий квартиль доходов.

Вид исходной таблицы:

Определим 3-й по формуле:

Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:

=СЧЁТЕСЛИ(B2:B13;» Анализ статистики случайно сгенерированных чисел в Excel

Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.

Чтобы сгенерировать случайное число в Excel воспользуемся функцией:

После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:

Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):

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

Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.

Особенности использования функций расчета квартиля в Excel

Все рассматриваемые функции имеют одни и те же аргументы:

  • массив – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек с числовыми значениями, для которых будет рассчитан требуемый квартиль;
  • часть – обязательный аргумент, принимающий числовые значения, указывающие номер возвращаемого квартиля. В зависимости от используемой функции, может принимать числа из диапазонов:
  1. От 0 до 4 (КВАРТИЛЬ.ВКЛ), при этом числа 0 и 1 характеризуют минимальное и максимальное значения из исследуемого диапазона соответственно. Число 1 соответствует 1-й квартили, 2 – медиане, 3 – 3-й квартили.
  2. От 1 до 3 (функция КВАРТИЛЬ.ИСКЛ), соответствующие 1-й, 2-й и 3-й квартилям.
  1. Все рассматриваемые функции не учитывают имена и текстовые строки, которые не могут быть преобразованы к числам, логические значения и пустые ячейки. Ячейки, содержащие значение 0 (нуль), в расчет включаются.
  2. Если в качестве первого аргумента функций передан пустой массив или ссылка на диапазон пустых значений, все функции вернут код ошибки #ЧИСЛО!.
  3. Если в качестве второго аргумента функций было передано нецелое число из диапазона допустимых значений, дробная часть будет усечена.
  4. Если второй аргумент задан числом, взятым из вне диапазона допустимых значений, в результате вычислений будет возвращен код ошибки #ЧИСЛО!.

Что такое «квартиль»

Квартал — это статистический термин, описывающий разделение наблюдений на четыре определенных интервала, основанные на значениях данных и их сравнении со всем набором наблюдений ,

Старайтесь не путать четверть с квартикой.

BREAKING DOWN ‘Quartile’

Чтобы понять квартиль, важно понять медиану как меру центральной тенденции. Медиана в статистике — это среднее значение набора чисел. Это точка, в которой ровно половина данных лежит ниже и выше центрального значения. Итак, учитывая набор из 13 чисел, медиана будет седьмым числом. Шесть чисел, предшествующих этому значению, являются наименьшими числами в данных, а шесть чисел после медианы являются наивысшими числами в приведенном наборе данных. Поскольку медиана не зависит от экстремальных значений или выбросов в распределении, иногда это бывает предпочтительнее среднего.

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

Квартал делит данные на три точки — более низкий квартиль, медианный и верхний квартиль — для формирования четырех групп набора данных. Нижняя квартиль или первый квартиль обозначаются как Q1, а среднее число находится между наименьшим значением набора данных и медианной. Второй квартиль Q2 также является медианным. Верхний или третий квартиль, обозначенный как Q3, является центральной точкой, которая находится между медианным и наибольшим числом распределения. Теперь мы можем отобразить четыре группы, сформированные из квартилей. Первая группа значений содержит наименьшее число до Q1; вторая группа включает Q1 в медиану; третий набор является медианным для Q3; и четвертая категория включает Q3 в самую высокую точку данных всего набора.

Каждый квартиль содержит 25% от общего количества наблюдений. Как правило, данные распределяются от самых маленьких до крупнейших, причем эти наблюдения падают ниже 25% от всех проанализированных данных, выделенных в 1-м квартиле, наблюдения падают между 25. 1% и 50% и распределяются во 2-м квартиле, тогда наблюдения падают между 51% и 75% выделены в 3-м квартиле и, наконец, остальные наблюдения, выделенные в 4-м квартиле.

Пример квартили

Давайте работать с примером.Предположим, что распределение математических баллов в классе из 19 учеников в порядке возрастания:

59, 60, 65, 65, 68, 69, 70, 72, 75, 75, 76, 77, 81, 82, 84 , 87, 90, 95, 98

Сначала отметьте медианную Q2, которая в этом случае будет десятым значением — 75.

Q1 — это центральная точка между наименьшим и средним. В этом случае Q1 падает между первым и девятым баллами — 68. [Обратите внимание, что медиана также может быть включена при вычислении Q1 или Q3 для нечетного набора значений. Если бы мы включили медиану по обе стороны от средней точки, то Q1 будет средним значением между первым и десятым баллами, что является средним значением пятого и шестого балла — (пятый + шестой) / 2 = (68 + 69) / 2 = 68. 5].

Q3 — среднее значение между Q2 и самым высоким счетом — 84. [Или если вы включаете медиану, Q3 = (82 + 84) / 2 = 83].

Теперь, когда у нас есть наши квартили, давайте интерпретировать их числа. Оценка 68 (Q1) представляет первый квартиль и является 25 th процентилем. 68 — медиана нижней половины балла, установленного в доступных данных i. е. медиана баллов от 59 до 75. Q1 говорит нам, что 25% баллов меньше 68 и 75% баллов в классе больше. Q2 (медиана) — это 50 th процентиль и показывает, что 50% баллов меньше 75, а 50% баллов выше 75. Наконец, Q3, 75 th Если точка данных для Q1 находится дальше от медианной, чем Q3, от медианной, то можно сказать, что существует большая дисперсия среди меньших значений набора данных, чем среди больших значений. Такая же логика применяется, если Q3 находится дальше от Q2, чем Q1 от медианы.

Если существует четное количество точек данных, медиана будет средним числом средних двух чисел. В нашем примере выше, если бы у нас было 20 учеников вместо 19, медиана их баллов будет средним арифметическим для десятого и одиннадцатого чисел.

Квартилы используются для расчета межквартильного диапазона, который является мерой изменчивости вокруг медианы. Межквартильный диапазон просто вычисляется как разница между первым и третьим квартилями: Q3 — Q1. По сути, это диапазон средней половины данных, который показывает, насколько распространены данные.

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

Минимальный уровень: наименьшее значение заработной платы в изучаемом массиве.
Максимальный уровень: наибольшее значение заработной платы в изучаемом массиве.
Мода: наиболее часто встречающееся значение заработной платы в изучаемом массиве.
Медиана, или Средний квартиль: значение заработной платы, расположенное в середине изучаемого массива, упорядоченного по возрастанию.
Нижний квартиль: значение заработной платы, ниже которого в упорядоченном по возрастанию массиве находится четверть (25%) данных о заработных платах.
Верхний квартиль: значение заработной платы, выше которого в упорядоченном по возрастанию массиве находится четверть (25%) данных о заработных платах.
Среднее арифметическое: сумма всех значений заработных плат исследуемого массива, деленная на количество заработных плат, содержащихся в массиве.

Показатели, на которые следует ориентироваться при определении среднерыночной стоимости специалиста Наиболее распространенным видом среднего значения заработной платы является среднее арифметическое. Однако среднее арифметическое довольно чувствительно к влиянию экстремальных значений (слишком малые или слишком большие зарплаты) и его значение смещается в сторону этих экстремальных значений, поэтому ориентироваться на данный показатель в случае большого разброса данных особого смысла не имеет. При решении вопроса о соответствии зарплаты специалиста среднему рыночному значению, следует руководствоваться значением медианы, которая в отличие от среднего арифметического не чувствительна к влиянию экстремальных значений. Для понимания того, находится ли интересующее Вас значение заработной платы в рыночном коридоре, следует ориентироваться на диапазон зарплат между нижним и верхним квартилями, в котором сосредоточены 50% исследуемых данных. Мода это наиболее часто встречающееся значение. В то время как любой исследуемый массив данных по заработным платам всегда обладает одним средним арифметическим и одной медианой, у набора данных может быть одна мода, ни одной моды или несколько мод. Мода полезна в том случае, когда нужно узнать какие значения зарплат по исследуемой позиции наиболее популярны. Приводимые значения, характеризующие исследуемый массив, а также таблица соответствия диапазонов зарплат и требований к специалисту, позволяют устанавливать и корректировать зарплату специалиста с учетом его профессиональных навыков и опыта работы, а также разрабатывать и применять схемы материального поощрения персонала компании.Пример расчета приводимых показателей Для наглядности демонстрации расчета показателей для абстрактной позиции ограничимся, например, размером исследуемого массива заработных плат n = 25, где 13 значений зарплат соответствуют предложениям работодателей, а 12 значений − ожиданиям Соискателей. Пусть указаны следующие значения заработных плат (в долларах): в предложениях работодателей: 1300; 1000; 800; 700; 800; 500; 1100; 700; 600; 800; 1200; 900; 600; в ожиданиях Соискателей: 900; 600; 1000; 800; 1100; 800; 700; 1100; 900; 500; 900; 800. Таким образом, изучаемый массив зарплат: 1300; 1000; 800; 700; 800; 500; 1100; 700; 600; 800; 1200; 900; 600; 900; 600; 1000; 800; 1100; 800; 700; 1100; 900; 500; 900; 800. Для удобства восприятия расположим зарплаты в порядке возрастания: 500; 500; 600; 600; 600; 700; 700; 700; 800; 800; 800; 800; 800; 800; 900; 900; 900; 900; 1000; 1000; 1100; 1100; 1100; 1200; 1300. Определение позиций квартилей в массиве:нижний квартиль: (n+1)/4= (25+1)/4= 6.5, т.е. значение квартиля находится между 6 и 7 элементами массива;средний квартиль: (n+1)/2= (25+1)/2= 13, т.е. значение квартиля соответствует 13-ому элементу массива;верхний квартиль: 3*(n+1)/4=3*(25+1)/4=19.5, т.е. значение квартиля находится между 19 и 20 элементами массива.

Ссылка на основную публикацию