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

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

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

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

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

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

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

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

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

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

  • Столбец Массив. Это просто наш входной массив сортированный по убыванию. Его можно сформировать формулой =НАИБОЛЬШИЙ($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 правильным считается термин перцентиль.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)