Как ранжировать числовые данные в Excel?
Научимся ранжировать числовые данные в Excel с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Задача ранжирования числовых данных постоянно возникает в работе с целью поиска наибольших или наименьших значений в списке.
В Excel с этой задачей можно справиться 2 способами: стандартным инструментом сортировки и с помощью функций.
Для примера возьмем простую таблицу со списком числовых значений, в которой в дальнейшем и будем ранжировать данные:
Сортировка данных
Начнем с самого простого и доступного варианта — сортировки.
Мы уже частично разбирали как можно структурировать данные с помощью фильтра и сортировки.
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная -> Редактирование -> Сортировка и фильтр, а далее указать по какому критерию нужно произвести сортировку.
В данном случае выберем Сортировка по убыванию, где значения будут расположены от большего к меньшему:
Минусом данного способа является изменение структуры исходных данных, так как в процессе сортирования данных строки и столбцы могут меняться местами, что в некоторых случаях неудобно или невозможно сделать.
Также к важным недостаткам этого варианта можно отнести отсутствие возможности автоматизировать сортировку. Поэтому каждый раз при изменении данных сортировку придется делать еще раз.
В качестве решения данной проблемы рассмотрим другой способ ранжирования, который впрочем можно рассматривать и отдельно от решения этой задачи.
Ранжирование данных
При отсутствии возможности изменения структуры документа мы можем создать дополнительный ряд данных, где будут содержаться порядковые номера исходных данных.
Получить эти порядковые номера нам поможет функция РАНГ (а также РАНГ.РВ и РАНГ.СР).
Функция РАНГ в Excel
Синтаксис и описание функции:
РАНГ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке.
- Число(обязательный аргумент) — число для которого вычисляется ранг;
- Ссылка(обязательный аргумент) — массив или ссылка на массив чисел;
- Порядок(необязательный аргумент) — способ упорядочения. Если аргумент равен 0 или не указан, то значение 1 присваивается максимальному элементу в списке (условно говоря, сортируем по убыванию), в ином случае значение 1 присваивается минимальному элементу (сортируем по возрастанию).
Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР, а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.
Функции РАНГ.РВ и РАНГ.СР в Excel
Синтаксис и описание функций:
РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.
РАНГ.СР(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается средний ранг.
Аргументы у всех трех функций одинаковые, т.е. кардинально они почти не отличаются, есть небольшие различие в деталях.
На примере исходной таблицы посмотрим как работает с данными каждая из функций:
Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.
В случае с РАНГ.РВ равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки и Мультиварки соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.
На этом различия между ними заканчиваются, поэтому в зависимости от ваших задач можно использовать ту или иную функцию.
Если нужно отсортировать значения по возрастанию, то в качестве аргумента Порядок нужно указать значение 1:
Автоматическая сортировка
Немного усложним задачу и представим, что нам в дальнейшем нужно составить отсортированную таблицу, которая бы автоматически обновлялась при изменении данных в исходной таблице.
Например, это можно сделать с помощью функции ВПР, или комбинации ИНДЕКС и ПОИСКПОЗ, однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:
В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции СТРОКА или СТОЛБЕЦ, поделенные на заведомо большую величину.
Этот шаг позволит нам получить различные числа в исходных данных, избежать совпадения рангов и ошибки при подтягивании данных:
Теперь для всех элементов таблицы (даже изначально совпадающих) определен свой индивидуальный ранг отличный от остальных, поэтому ошибок при автоматическом ранжировании данных удастся избежать.
Спасибо за внимание!
Если у вас остались вопросы — пишите в комментариях.
Функция РАНГ() в EXCEL
history 27 мая 2014 г.
- Группы статей
- Последовательности Чисел
- стандартные функции
- Сортировка Числовых Значений
Число — число, для которого определяется ранг.
Ссылка — ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;<10:50:30:40:50>) работать не будет. Но, если ввести формулу =РАНГ(B7;$A$7:$A$11) , то она будет работать (хотя ячейка B7 — вне списка с числами). Если в B7 содержится число вне списка с числами, то формула вернет ошибку #Н/Д.
Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.
Порядок — число, определяющее способ упорядочения.
- Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются б о льшие ранги.
- Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, б о льшим значениям присваиваются б о льшие ранги.
Примечание : Начиная с MS EXCEL 2010 для вычисления ранга также используются функции РАНГ.СР() и РАНГ.РВ() . Последняя функция аналогична РАНГ() .
Определяем ранг в списке без повторов
Если список чисел находится в диапазоне A7:A11 , то формула =РАНГ(A7;$A$7:$A$11) определит ранг числа из ячейки А7 (см. файл примера ).
Т.к. аргумент порядок опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) — минимальному (10).
Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;">"&A7)+1
В столбце С приведена формула =РАНГ(A7;$A$7:$A$11;1) с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант: =СЧЁТЕСЛИ($A$7:$A$11;"
Если исходный список отсортировать , то ранг числа будет его позицией в списке.
Ранг по условию
Если список состоит из значений, относящихся к разным группам (например, к разным маркам машин), то ранг можно вычислить не только относительно всей совокупности данных, но и относительно данных каждой отдельной группы.
В файле примера ранг по условию (условием является принадлежность значения к групп) вычислен с помощью формулы:
В столбце А содержатся названия группы, в столбце В — значения.
Связь функций НАИБОЛЬШИЙ() / НАИМЕНЬШИЙ() и РАНГ()
Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11)) мы получим тот же исходный массив A7:A11 .
Определяем ранг в списке с повторами
Если список содержит повторы , то повторяющимся значениям (выделено цветом) будет присвоен одинаковый ранг (максимальный, если использована функция РАНГ() или РАНГ.РВ() ) или среднее значение, если РАНГ.СР() ). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).
Иногда это не удобно и требуется, чтобы ранги не повторялись (например, при определении призовых мест, когда нельзя занимать нескольким людям одно место).
В этом нам поможет формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1
Предполагается, что исходный список с числами находится в диапазоне А37:А44 .
Примечание . В MS EXCEL 2010 добавилась функция РАНГ.РВ(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В файле примера дается пояснение работы этой функции. Также добавилась функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается среднее.
Массив рангов
Для построения некоторых сложных формул массива может потребоваться массив рангов, т.е. тот же набор рангов, но в одной ячейке.
Как видно из картинки выше, значения из диапазона В60:В67 и в ячейке D60 совпадают. Такой массив можно получить с помощью формулы =РАНГ(A60:A67;A60:A67) или с помощью формулы =СЧЁТЕСЛИ(A60:A67;">"&A60:A67)+1
Ранги по возрастанию можно получить с помощью формулы =РАНГ(A60:A67;A60:A67;1) или =СЧЁТЕСЛИ(A60:A67;" .