Примеры функции КВАРТИЛЬ в Excel для расчета медианы квартиля
Функция КВАРТИЛЬ в Excel используется для расчета квартиля диапазона числовых данных и возвращает соответствующее числовое значение.
Функция КВАРТИЛЬ.ВКЛ вычисляет на основе указанной процентили в качестве второго аргумента функции. Полностью соответствует первой функции. Последняя используется в Excel 2007 и более ранних версиях и оставлена для совместимости.
Функция КВАРТИЛЬ.ИСКЛ используется для расчета квартили диапазона числовых значений на основе известной процентили, за исключением граничных значений (минимального и максимального значения в диапазоне).
Квартили используются для распределения диапазона чисел на четыре равные части:
- Первый квартиль является числом из диапазона исследуемых значений, которое делит данный диапазон на две части так, что около 25% данного диапазона являются числами, которые меньше первого квартиля, а остальные (75%) – больше. Рассматриваемые функции могут возвращать результат интерполяции двух соседних значений из диапазона.
- Второй квартиль эквивалентен медиане выборки (исследуемого числового диапазона), то есть числовому значению, которое делит диапазон на две части: 50% чисел меньше медианы, остальные 50% чисел больше медианы. Так, запись =КВАРТИЛЬ.ВКЛ(A1:A10;2) возвращает значение, эквивалентное результату вычисления функции =МЕДИАНА(A1:A10), при условии, что ячейки из диапазона A1:A10 содержат числовые значения.
- Третий квартиль – числовое значение, делящее диапазон на две части, в первой из которой содержатся 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-й по формуле:
Определим соотношение чисел, меньше полученного числа, к общему количеству значений по формуле:
Анализ статистики случайно сгенерированных чисел в Excel
Пример 3. Имеется диапазон случайных чисел, отсортированный в порядке возрастания. Определить соотношение суммы чисел, которые меньше 1-го квартиля, к сумме чисел, которые превышают значение 1-го квартиля.
Чтобы сгенерировать случайное число в Excel воспользуемся функцией:
После генерации отсортируем случайно сгенерированные числа по возрастанию. Вид исходной таблицы данных со случайными числами:
Формула для расчета имеет следующий вид (формула массива CTRL+SHIFT+ENTER):
Функции СУММ с вложенными функциями ЕСЛИ выполняют расчет суммы только тех чисел, которые меньше и больше соответственно значения, возвращаемого функцией для исследуемого диапазона. Из полученных значений вычисляется частное. Результат расчетов:
Общая сумма чисел исследуемого диапазона, которые меньше 1-го квартиля, составляет всего 8,57% от общей суммы чисел, которые больше 1-го квартиля.
Функции Excel QUARTILE — Расчет значения квартиля
Функция КВАРТИЛЬ Вычисляет указанное значение квартиля.
Чтобы использовать функцию листа Excel QUARTILE, выберите ячейку и введите:
(Обратите внимание, как появляются входные данные формулы)
Функция КВАРТИЛЬ Синтаксис и входные данные:
| 1 | = КВАРТИЛЬ (массив; кварта) |
множество — Массив чисел.
кварта — Число, представляющее квартиль, который вы хотите вернуть.
Что такое функция КВАРТИЛЬ?
КВАРТИЛЬ — одна из статистических функций Excel. Он возвращает квартиль диапазона данных. Вы можете указать, хотите ли вы: первый, второй или третий квартиль. QUARTILE также может возвращать минимальные и максимальные значения.
КВАРТИЛЬ — это функция «совместимости»
Начиная с Excel 2010, Microsoft заменила QUARTILE двумя вариантами: QUARTILE.INC и QUARTILE. ИСКЛ.
КВАРТИЛЬ по-прежнему работает, поэтому, если у вас есть старые электронные таблицы, которые его используют, они будут продолжать работать в обычном режиме. Однако вы должны использовать QUARTILE.INC или QUARTILE.EXC везде, где это возможно, на тот случай, если Microsoft исключит QUARTILE из будущих версий Excel.
Что такое квартиль?
Квартили делят диапазон данных на четыре приблизительно равные группы по размеру. Excel рассчитывает квартили как процентили:
- Первый квартиль также известен как 25 th процентиль — поскольку 25% данных ниже этого значения.
- Второй квартиль — это медиана набора данных, также известная как 50 th процентиль, поскольку 50% данных ниже этого значения.
- Третий квартиль также называют 75 th процентиль, поскольку 75% данных ниже этого значения.
Квартили вместе с наименьшими и наибольшими числами в наборе создают «пятизначную сводку». Это может помочь вам сразу увидеть, где находится середина данных и насколько они распределены.
Как использовать функцию КВАРТИЛЬ
Используйте QUARTILE так:
| 1 | = КВАРТИЛЬ (C4: C14,2) |
Группа учеников эксклюзивной школы сдала важный экзамен. Их оценки хранятся в C4: C13, а диапазон данных — это первый аргумент, который мы передаем в QUARTILE.
Следующий аргумент в функции, который мы называем «кварта”, Определяет значение, которое мы хотим вернуть. Здесь мы выбрали 2, второй квартиль / медиана. Медиана — это среднее число в диапазоне, а поскольку у нас 11 студентов, медиана — это 6 th один — 74 балла Жана, которые я выделил в таблице.
Несколько полезных вещей, которые нужно знать о QUARTILE:
- Если кварт не является числом, вы получите # ЗНАЧ! ошибка
- Если в кварте меньше 0 или больше 4, вы получите # ЧИСЛО! ошибка
- Если кварт не является целым числом, КВАРТИЛЬ проигнорирует все, что находится после десятичной точки.
- Пустые или нечисловые ячейки в диапазоне данных игнорируются.
- Если диапазон данных пуст, вы получите # ЧИСЛО! ошибка
Создание пятизначной сводки
Когда вы определяете кварта у вас есть пять вариантов:
- 0 — наименьшее число (эквивалент функции MIN в Excel <>)
- 1 — первый квартиль
- 2 — второй квартиль (эквивалент функции МЕДИАНА Excel <>)
- 3 — третий квартиль
- 4 — наибольшее значение (эквивалент функции Excel MAX <>)
Поэтому, если вы хотите создать сводку из пяти цифр, вы просто создаете пять функций QUARTILE и измените кварта ценность в каждом. Это показано ниже:
QUARTILE.INC
Как я упоминал ранее, Microsoft заменила квартиль двумя функциями — QUARTILE.INC и QUARTILE.EXC. QUARTILE.INC — это то же самое, что QUARTILE. Поэтому, если мы переключим эти функции в нашей сводной таблице из пяти чисел, мы получим точно такие же результаты:
| 1 | = КВАРТИЛЬ.INC (C4: C14; E4) |
Часть функции «INC» является сокращением от «inclusive». Это означает, что QUARTILE.INC включает наименьшие и наибольшие значения в диапазоне данных при вычислении квартилей.
Excel вычисляет квартили как процентили: это может дать несколько отличные результаты от способа вычисления квартилей в других аналитических пакетах (обычно путем разделения данных на две половины и вычисления медианы каждой половины).
Вот как Excel рассчитывает инклюзивные квартили:
| 1 | Расположение квартиля = (n — 1) * (целевой квартиль / 100) + 1 |
Здесь n — количество точек данных в наборе. Итак, давайте заполним уравнение на примере первого квартиля:
| 1 | Расположение Q1 = (11-1) * (25/100) + 1 |
| 1 | Наш результат = 3,5 |
Это означает, что значение Q1 равно 3,5. th значение, другими словами, посередине между третьим и четвертым значением.
Чтобы получить фактическое значение, мы используем следующее:
| 1 | Q1 = 3-е значение + (4-е значение — 3-е значение) * .5 |
Заполняем уравнение нашими значениями:
| 1 | Q1 = 65 + (67-65) * 0,5 |
| 1 | Наш результат: 66 |
Более подробное обсуждение того, как рассчитываются процентили в Excel, можно найти на главной странице функции Excel PERCENTILE <>.
QUARTILE.EXC
QUARTILE.EXC очень похож на QUARTILE.INC, за исключением того, что он исключает наименьшие и наибольшие значения в наборе данных при вычислении квартилей.
Вы используете это так:
| 1 | = КВАРТИЛЬ.EXC (C4: C14,2) |
Вот та же сводка из пяти цифр, рассчитанная с помощью QUARTILE.EXC:
Ну, первое, что выскакивает, — это # ЧИСЛО! ошибки, когда мы устанавливаем кварта на 0 или 4. Это связано с тем, что, как упоминалось ранее, QUARTILE.EXC исключает наименьшие и наибольшие значения при создании своих диапазонов. Таким образом, вы не можете создать пятизначную сводку с помощью QUARTILE.EXC.
Вот как Excel рассчитывает эксклюзивные квартили:
| 1 | Расположение квартиля = (n + 1) * (целевой квартиль / 100) |
Давайте заполним это поле, снова используя в качестве примера первый квартиль:
| 1 | Расположение Q1 = (11 + 1) * (25/100) |
| 1 | Наш результат = 3 |
Это означает, что значение Q1 является третьим значением: оценка Логана 65.
Если бы в результате мы получили десятичное число, это означало бы, что квартиль находится между двумя значениями, и мы получили бы значение так же, как и с QUARTILE.INC:
| 1 | Квартиль = наименьшее значение + (наибольшее значение — наименьшее значение) * [десятичная дробь из первого уравнения] |
Опять же, для более подробного обсуждения того, как рассчитываются процентили в Excel, см. Главную страницу функции Excel PERCENTILE <>.
Как найти квартили в Excel (плюс определение и часто задаваемые вопросы)
Компании часто организуют статистику в Excel, чтобы лучше понять свои данные. Одна из функций, которую некоторые люди могут использовать в своих электронных таблицах, — это функция квартилей, которая разделяет данные на четыре категории в диапазоне. Узнав больше о квартилях, вы сможете решить, может ли этот расчет дать новое представление о ваших числовых данных.
В этой статье мы обсудим, что такое квартили и как их найти в Excel, а также советы и часто задаваемые вопросы об этой функции.
Что такое квартили?
Квартили — это значения в Excel, которые разделяют числовые значения на четыре части. Люди находят квартили, чтобы понять процентили, например, 25% клиентов с наибольшими расходами. Четыре квартиля включают:
Первый квартиль: Первый квартиль включает самые низкие 25% диапазона данных.
Второй квартиль: Второй квартиль включает следующую наименьшую группу чисел. В эту группу входят числа через медиану набора данных.
Третий квартиль: Третий квартиль — это вторая по величине группа чисел, превышающая медиану.
Четвертый квартиль: Четвертый квартиль включает самые высокие 25% чисел в диапазоне данных.
Например, если данные варьируются от одного до восьми, то каждое из них находится в следующих квартилях:
Первый квартиль: 1 и 2
Второй квартиль: 3 и 4
Третий квартиль: 5 и 6
Четвертый квартиль: 7 и 8
Как найти квартили в Excel
При нахождении квартилей в Excel можно предпринять несколько шагов:
1. Упорядочьте ваши числа
Чтобы получить квартили, вы можете упорядочить числа в диапазоне данных от наименьшего к наибольшему. В электронной таблице их можно упорядочить в столбце. Например, ваши числа могут быть следующими:
Для формулы квартиля необходимы две основные величины: квартили и массивы. Квартили — это значения, представленные каждым квартилем. Они варьируются от нуля до четырех:
0: Это минимальное значение в диапазоне чисел.
1: Это первый квартиль или 25-й процентиль.
2: Это второй, или медианный, квартиль, или 50-й процентиль.
3: Это представляет собой третий квартиль или 75-й процентиль.
4: Представляет собой максимальное значение в диапазоне.
3. Выполните функцию
Использование функции квартиля в Excel позволяет получить квартиль для любого набора данных. Для расчета квартилей в Excel используется формула:
Массив — это полный диапазон значений, для которых вы хотите найти квартили
Кварта — это то, какой квартиль вы хотите найти
Советы по использованию функции квартилей в Excel
Вот несколько советов по использованию функции квартилей в Excel:
Обзор значений
Перед выполнением функции квартиля можно убедиться, что ваши числа расположены в порядке возрастания в одном столбце и являются правильными. Если ячейки пустые или содержат текст или специальные символы, функция выдает сообщение об ошибке. Вы также можете получить сообщение об ошибке, если значение quart в вашей команде меньше нуля или больше четырех.
Рассчитать различные кварты
Каждый квартиль может предоставить вам различную информацию, которую вы можете использовать. Например, вы можете захотеть узнать, какую максимальную сумму тратит клиент в самом низком процентиле покупок, а также в более высоких квартилях. Это может помочь вам определить, как вы можете создать конкретные бизнес-цели для отдельных групп. Для широкого диапазона данных вычисление каждого квартиля может показать больше, чем среднее значение или медиана, так как оно показывает вариации внутри набора данных.
Проверьте точность вручную
Вы можете вручную рассчитать квартили с помощью нескольких формул для проверки информации:
Нижний квартиль = (N+1) x 14
Средний квартиль = (N+1) x 24
Верхний квартиль = (N+1) x 34
N — это количество целых чисел в вашем наборе данных. Результат говорит вам о позиции, которая представляет каждый квартиль. Например, если для формулы нижнего квартиля результат равен шести, шестое число в вашей последовательности будет нижним квартилем. Поскольку четвертый квартиль является максимальным значением в диапазоне, формулы для его расчета не существует.
Часто задаваемые вопросы
Что такое процентиль?
Процентиль — это мера, используемая в статистике, чтобы показать процентное соотношение в наборе данных. Процентили могут указывать процент отклонения от нормы ниже и выше установленного процентиля. Например, оценка в пределах 25-го процентиля ключевого показателя эффективности поставщика показывает, что 25% оценок были ниже. Процентные показатели могут показать предприятиям, как они сравниваются с другими в своей отрасли.
В чем разница между кварталом и квартилем?
Квартал включает все числа в пределах 25% набора данных. Вместо этих отдельных чисел, квартиль — это число, на котором заканчивается один квартал и начинается другой. Вычисление квартилей может помочь вам понять, насколько велика разница между кварталами.
Для чего используются квартили?
Квартили существуют в статистике для измерения величин, таких как процент доходов в определенной демографической группе или развитие ребенка в определенном возрасте. Квартили могут быть полезны компаниям для оценки и установления эталона на основе конкуренции. Например, организация может собирать данные об общем объеме продаж своих ведущих конкурентов. Вычисление квартилей может помочь установить цели, которые помогут удержаться в верхних квартилях.
Что такое аналогичные функции в Excel?
Существует несколько аналогичных функций, которые можно рассчитать в Excel:
MIN: Функция MIN вычисляет наименьшее потенциальное значение в наборе данных.
MAX: Функция MAX вычисляет наименьшее возможное значение в наборе данных.
MEDIAN: Функция MEDIAN вычисляет среднее значение в наборе данных.
СРЕДНЕЕ: Функция MEAN вычисляет среднее значение чисел в наборе данных.
Для каждой из них вы можете выбрать пустой квадрат и ввести формулу с массивом данных, которые вы хотите проанализировать. Например, функция MEDIAN может быть такой:
Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с Indeed.
Как (и зачем) использовать функцию выбросов в Excel
Выброс — это значение, которое значительно выше или ниже большинства значений в ваших данных. При использовании Excel для анализа данных выбросы могут исказить результаты. Например, среднее значение набора данных может действительно отражать ваши значения. Excel предоставляет несколько полезных функций, помогающих управлять выбросами, поэтому давайте посмотрим.
Быстрый пример
На изображении ниже выбросы довольно легко обнаружить — значение два присвоено Эрику, а значение 173 — Райану. В таком наборе данных достаточно легко обнаружить и обработать эти выбросы вручную.
В большем наборе данных этого не будет. Возможность идентифицировать выбросы и удалять их из статистических расчетов важна — и это то, что мы рассмотрим, как это сделать в этой статье.
Как найти выбросы в ваших данных
Чтобы найти выбросы в наборе данных, мы используем следующие шаги:
Вычислите 1-й и 3-й квартили (мы немного поговорим о том, что это такое).
Оцените межквартильный размах (мы также объясним это немного ниже).
Верните верхнюю и нижнюю границы нашего диапазона данных.
Используйте эти границы для определения отдаленных точек данных.
Диапазон ячеек справа от набора данных, показанного на изображении ниже, будет использоваться для хранения этих значений.
Шаг 1. Рассчитайте квартили
Если вы разделите данные на кварталы, каждый из этих наборов называется квартилем. Самые низкие 25% чисел в диапазоне составляют 1-й квартиль, следующие 25% — 2-й квартиль и т. Д. Мы делаем этот шаг в первую очередь, потому что наиболее широко используемое определение выброса — это точка данных, которая более чем на 1,5 интерквартильных диапазонов (IQR) ниже 1-го квартиля и на 1,5 межквартильных диапазонов выше 3-го квартиля. Чтобы определить эти значения, мы сначала должны выяснить, каковы квартили.
Excel предоставляет функцию КВАРТИЛЬ для расчета квартилей. Для этого требуются две части информации: массив и кварта.
Массив — это диапазон значений, которые вы оцениваете. Кварта — это число, которое представляет квартиль, который вы хотите вернуть (например, 1 для 1-го квартиля, 2 для 2-го квартиля и т. Д.).
Примечание. В Excel 2010 Microsoft выпустила функции QUARTILE.INC и QUARTILE.EXC как усовершенствования функции QUARTILE. QUARTILE более обратно совместима при работе с несколькими версиями Excel.
Вернемся к нашему примеру таблицы.
Для вычисления 1-го квартиля мы можем использовать следующую формулу в ячейке F2.
Когда вы вводите формулу, Excel предоставляет список параметров для аргумента кварты.
Чтобы вычислить 3-й квартиль, мы можем ввести формулу, аналогичную предыдущей, в ячейку F3, но используя тройку вместо единицы.
Теперь у нас есть точки данных квартилей, отображаемые в ячейках.
Шаг второй: оцените межквартильный размах
Межквартильный диапазон (или IQR) — это средние 50% значений в ваших данных. Он рассчитывается как разница между значением 1-го квартиля и значением 3-го квартиля.
Мы собираемся использовать простую формулу в ячейке F4, которая вычитает 1-й квартиль из 3-го квартиля:
Теперь мы можем видеть наш межквартильный размах.
Шаг третий: верните нижнюю и верхнюю границы
Нижняя и верхняя границы — это наименьшее и наибольшее значение диапазона данных, который мы хотим использовать. Любые значения, меньшие или большие, чем эти связанные значения, являются выбросами.
Мы рассчитаем нижний предел в ячейке F5, умножив значение IQR на 1,5, а затем вычтя его из точки данных Q1:
Примечание. Скобки в этой формуле не нужны, потому что часть умножения будет вычисляться перед частью вычитания, но они облегчают чтение формулы.
Чтобы вычислить верхнюю границу в ячейке F6, мы снова умножим IQR на 1,5, но на этот раз добавим его к точке данных Q3:
Шаг четвертый: выявление выбросов
Теперь, когда мы настроили все наши базовые данные, пришло время определить наши отдаленные точки данных — те, которые ниже значения нижней границы или выше значения верхней границы.
Мы будем использовать Функция ИЛИ для выполнения этого логического теста и отображения значений, соответствующих этим критериям, введите следующую формулу в ячейку C2:
Затем мы скопируем это значение в наши ячейки C3-C14. Значение ИСТИНА указывает на выброс, и, как видите, в наших данных их два.
Игнорирование выбросов при вычислении среднего среднего
Использование функции КВАРТИЛЬ позволяет нам рассчитать IQR и работать с наиболее широко используемым определением выброса. Однако при вычислении среднего среднего для диапазона значений и игнорировании выбросов существует более быстрая и простая функция. Этот метод не будет определять выбросы, как раньше, но он позволит нам быть гибкими в выборе того, что мы можем считать своей частью выбросов.
Нужная нам функция называется TRIMMEAN, синтаксис для нее вы можете увидеть ниже:
Массив — это диапазон значений, которые вы хотите усреднить. Процент — это процент точек данных, которые необходимо исключить из верхней и нижней части набора данных (вы можете ввести его как процентное или десятичное значение).
В нашем примере мы ввели приведенную ниже формулу в ячейку D3, чтобы вычислить среднее значение и исключить 20% выбросов.
Здесь у вас есть две разные функции для обработки выбросов. Независимо от того, хотите ли вы идентифицировать их для каких-либо потребностей в отчетности или исключить их из вычислений, таких как средние значения, в Excel есть функция, соответствующая вашим потребностям.