Расчет ундециля

Ундециль (11-квантиль, англ. undecile) — это значение, которое делит отсортированную выборку на 11 равных по доле частей.
В отличие от привычных квартилей (4 части) или процентилей (100 частей), ундецили дают 10 «разрезов» между 11 долями данных.
Чаще всего такие квантили применяются в финансовом анализе, демографии и маркетинговом сегментировании клиентов.

Зачем нужны ундецили?

• быстро «нарезать» аудиторию на 11 равных сегментов;
• находить аномалии в распределении доходов, расходов, выручки и т. д.;
• строить специализированные рейтинги (например, Top-9 % клиентов соответствует 9-му и 10-му ундецилям).

Формула расчёта ундециля вручную

Для упорядоченной выборки x₍₁₎ ≤ x₍₂₎ ≤ … ≤ x_(n)
позиция k-го ундециля (k = 1…10) определяется как:

P_k = (n − 1)·k/11 + 1, где n — объём выборки.

Затем берутся соседние элементы и выполняется линейная интерполяция между ними.

Как посчитать ундецили в Excel без VBA

Шаг 1. Подготовьте данные

1. Введите числовой ряд в столбец A, начиная с A2 вниз.
2. В B1 напишите формулу =СЧЁТ(A:A) — получите объём выборки n.

Шаг 2. Создайте сетку ундецилей

Скопируйте строки C2:G2 вниз на 10 строк — получите 10 значений ундецилей от Q_1/11 до Q_10/11.

Шаг 3. Проверка корректности

• Отсортируйте исходные данные (Вкладка «Данные → Сортировка»).
• Сравните ручной расчёт с функцией =КВАНТИЛЬ.ВКЛ(A:A; k/11).
  Результаты могут слегка отличаться, потому что Excel использует другой метод (тип 7 по ISO), но для больших n различие пренебрежимо мало.

Расширение: автоматизация через Power Query

В Power Query (Вкладка «Данные → Получить данные») можно добавить пользовательскую функцию:

let
 Исходник = Excel.CurrentWorkbook{[Name="Таблица1"]}[Content],
 Сортировка = Table.Sort(Исходник,{{"Значение", Order.Ascending}}),
 ДобавитьИндекс = Table.AddIndexColumn(Сортировка, "Индекс", 0, 1, Int64.Type),
 ДобавитьУндецили = Table.AddColumn(ДобавитьИндекс, "Ундециль",
 each List.Percentile(ДобавитьИндекс[Значение], [Индекс] / (Table.RowCount(ДобавитьИндекс) ― 1) * 11/11))
in
 ДобавитьУндецили

Как использовать полученные ундецили

1. Сегментация клиентов: распределите клиентов по 11 группам по доходу и назначьте разные маркетинговые стратегии.
2. Анализ зарплат: выявите «дно» 1-го ундециля и «потолок» 10-го, чтобы оценить социальное неравенство.
3. Финансовые рейтинги: выделите верхние 2–3 доли (9-й и 10-й ундецили) как VIP-группу с повышенным лимитом кредитов.

Отличие от других квантилей

Несмотря на то, что Excel не содержит встроенной функции УНДЕЦИЛЬ,
с помощью простых формул и, при необходимости, Power Query или Office Script (JavaScript)
расчёт 11-квантилей занимает не более пары минут.
Главное — помнить про интерполяцию и всегда сортировать исходные данные перед вычислениями.