При работе с данными часто бывает необходимо выяснить, какое место в агрегированном списке занимает тот или иной показатель по значению. В статистике это называется рейтингом. Excel предоставляет инструменты, которые позволяют пользователям выполнять эту процедуру быстро и легко. Давайте узнаем, как их использовать.
Функции ранжирования
Для выполнения классификации в Excel предусмотрены специальные функции. В предыдущих версиях приложения для решения этой проблемы был оператор RANK. Из соображений совместимости в современных версиях программы он оставлен в отдельной категории формул, но в них все же желательно по возможности работать с более свежими аналогами. К ним относятся статистические операторы RANK.RV и RANK.SR. Мы поговорим об отличиях и алгоритме работы с ними дальше.
Способ 1: функция РАНГ.РВ
Оператор RANK.RV обрабатывает данные и возвращает порядковый номер аргумента, заданного агрегированным списком указанной ячейке. Если несколько значений имеют один и тот же уровень, оператор выводит самое высокое из списка значений. Например, если два значения имеют одинаковую величину, обоим будет присвоено второе число, а следующему большему значению будет четвертое. Кстати, оператор RANK делает то же самое в более ранних версиях Excel, поэтому эти функции можно считать идентичными.
Синтаксис этого оператора записывается следующим образом:
= RANGO.RV (номер; ссылка; [заказ])
Ссылочный номер и аргументы являются обязательными, а порядок указывать необязательно. В качестве аргумента «число» необходимо ввести ссылку на ячейку, содержащую значение, чей порядковый номер вы хотите узнать. Аргумент ссылки содержит адрес всего классифицируемого диапазона. Аргумент порядка может иметь два значения: «0» и «1». В первом случае порядок считается по убыванию, а во втором — по возрастанию. Если этот аргумент не указан, программа автоматически считает его равным нулю.
Эту формулу можно записать вручную в ячейку, в которой вы хотите отобразить результат обработки, но для многих пользователей удобнее задать ввод через окно мастера.
- Выделите на листе ячейку, в которой будет отображаться результат обработки данных. Нажмите кнопку «Вставить функцию». Он расположен слева от строки формул.
- Эти действия запускают окно мастера. В нем представлены все (за редким исключением) операторы, которые можно использовать для составления формул в Excel. В категории «Статистика» или «Полный алфавитный список» находим имя «RANK.RV», выбираем его и нажимаем на кнопку «ОК».
- После выполнения вышеуказанных шагов будет активировано окно аргументов функции. В поле «Число» введите адрес ячейки, данные, которые вы хотите классифицировать. Это можно сделать вручную, но удобнее сделать так, как будет описано ниже. Установите курсор в поле «Число», затем просто выберите нужную ячейку на листе.
После этого ваш адрес будет введен в поле. Таким же образом мы вставляем данные в поле «Ссылка», только в этом случае мы выбираем весь диапазон, в котором происходит ранжирование.
Если вы хотите, чтобы рейтинг шел от низшего к высшему, то в поле «Порядок» установите цифру «1». Если вы хотите, чтобы порядок распределялся от наибольшего к наименьшему (а в подавляющем большинстве случаев это именно то, что требуется), это поле остается пустым.
После ввода всех вышеперечисленных данных нажмите кнопку «ОК».
- После выполнения этих действий в заданной ячейке отобразится серийный номер, который имеет выбранное значение из всего списка данных.
Если вы хотите ранжировать всю указанную область, вам не нужно вводить отдельную формулу для каждой меры. Прежде всего, сделаем адрес абсолютным в поле «Ссылка». Добавьте знак доллара ($) к каждому значению координат. В этом случае не следует изменять значения в поле «Число» на абсолютные значения, иначе формула будет рассчитана некорректно.
Далее необходимо поместить курсор в правый нижний угол ячейки и дождаться появления индикатора заполнения в виде небольшого креста. Затем, удерживая левую кнопку мыши, перетащите маркер параллельно рассчитанной области.
Как видите, таким образом формула будет скопирована, и ранжирование будет выполнено по всему диапазону данных.
Способ 2: функция РАНГ.СР
Вторая функция, выполняющая операцию ранжирования в Excel, — это RANK.SR. В отличие от функций RANK и RANK.RV, если значения нескольких элементов совпадают, этот оператор возвращает средний уровень. То есть, если два значения равны по величине и следуют за пронумерованным значением 1, им обоим будет присвоен номер 2,5.
Синтаксис RANK.CP очень похож на предыдущий оператор. Похоже на то:
= RANK.SR (число; ссылка; [порядок])
Формулу можно ввести вручную или с помощью мастера. На последнем варианте остановимся подробнее.
- Выбираем ячейку на листе для просмотра результата. Аналогично, как и в предыдущий раз, перейдите в мастер через кнопку «Вставить функцию».
- После открытия окна мастера функций выберите имя RANK.SR в списке категорий «Статистика» и нажмите кнопку «ОК».
- Окно темы активировано. Аргументы для этого оператора точно такие же, как для функции RANK.RV:
- Число (адрес ячейки, в которой находится элемент, уровень которого необходимо определить);
- Ссылка (координаты диапазона, рейтинг в пределах которого выполняется);
- Заказ (необязательно).
Вставка данных в поля выполняется точно так же, как и в предыдущем операторе. После выполнения всех настроек нажмите кнопку «ОК».
- Как видите, после выполненных действий результат расчета отображался в ячейке, отмеченной в первом абзаце данной инструкции. Сама сумма представляет собой место, которое занимает конкретное значение среди других значений в диапазоне. В отличие от результата RANK.RV, итог оператора RANK.SR может иметь дробное значение.
- Как и в случае с предыдущей формулой, изменив ссылки с относительных на абсолютные и указав индикатор выбора, вы можете ранжировать весь диапазон данных с помощью автозаполнения. Алгоритм действий точно такой же.
Как видите, в Excel есть две функции для определения ранжирования конкретного значения в диапазоне данных: RANK.RV и RANK.SR. Для более ранних версий программы используется оператор RANK, который, по сути, является полным аналогом функции RANK.RV. Основное различие между формулами RANK.RV и RANK.SR состоит в том, что первая указывает наивысший уровень, когда значения совпадают, а вторая отображает среднее значение в виде десятичной дроби. Это единственное различие между этими операторами, но это необходимо учитывать при выборе функции, которая лучше всего подходит для использования пользователем.
