Ранг и Персентиль в надстройке Пакет анализа EXCEL

Рассмотрим инструмент Ранг и Персентиль надстройки Пакет анализа.

Надстройка Пакет анализа MS EXCEL позволяет быстро вычислить ранги для значений массива.

Пусть в диапазоне В7:В32 имеется числовой массив (см. файл примера ).

Вызовем диалоговое окно надстройки Пакет анализа (Вкладка Данные/ Анализ/ Анализ данных ) и выберем инструмент Ранг и Персентиль .

СОВЕТ : О том как подключить и работать с этой надстройкой см. статью Пакет анализа MS EXCEL .

Появится диалоговое окно:

Если ссылку на массив указать вместе с заголовком (см. рисунок), то нужно установить галочку в поле Метки в первой строке . Указывать заголовки удобно, когда анализируется сразу несколько массивов данных. В нашем случае это не принципиально, т.к. у нас только 1 массив. Можно указать ссылку на диапазон только с числами и не ставить галочку.

Ссылку на выходной интервал укажем на текущем листе, например, в ячейке D 6 (это будет левый верхний угол таблицы результатов).

После нажатия кнопки ОК будет выведена таблица результатов, содержащая следующие столбцы (см. рисунок выше):

• Столбец Массив . Это просто наш входной массив сортированный по убыванию . Его можно сформировать формулой =НАИБОЛЬШИЙ($B$7:$B$32;A7) (см. файл примера )
• Столбец со странным заголовком Точка . В нем выведены первоначальные позиции значений из столбца Массив исх . Например, до сортировки значение 50, которое сейчас стоит первым, находилось в 18-й строке исходного списка. Если во входном массиве есть повторы , то каждому повтору будет соответствовать своя позиция. Этот столбец можно сформировать с помощью формулы массива (см. файл примера столбец L).
• Столбец Ранг . Это столбец рангов значений . Максимальному значению будет присвоен ранг=1. Подробнее см. статью про функцию РАНГ() . Этот столбец можно сформировать с помощью формулы =РАНГ.РВ(I7;$B$7:$B$32) .
• Столбец Процент . Столбец содержит процентиль-ранг (percentile rank) , который служит оценкой относительного положения значения в массиве. Для заданного значения вычисляется сколько значений в массиве меньше или равно ему. Точнее - какой процент значений массива меньше или равен ему. Понятно, что для максимального значения процентиль-ранг равен 0,00%, а для минимального - 100% (все значения массива меньше или равны ему). Повторы в массиве имеют один и тот же ранг и процентиль-ранг . Подробнее про процентиль-ранг см. статью про Процентили (раздел про Функцию ПРОЦЕНТРАНГ.ВКЛ() ). Этот столбец можно сформировать с помощью формулы =ПРОЦЕНТРАНГ.ВКЛ($B$7:$B$32;I7) .

Не смотря на свое название Ранг и Персентиль, надстройка Пакет анализа не возвращает Персентили. По крайней мере в том понимании, которое заложено в функции ПРОЦЕНТИЛЬ.ВКЛ() . Подробнее про эту функцию также см. статью про Процентили .

Примечание : Существует некоторая путаница в терминологии: MS EXCEL использует понятие процентиль (функция ПРОЦЕНТИЛЬ.ВКЛ() и Условное форматирование ) и понятие персентиль ( надстройка Пакет анализа ) . В MS WORD правильным считается термин перцентиль.