Как ранжировать числовые данные в Excel?
Научимся ранжировать числовые данные в Excel с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Задача ранжирования числовых данных постоянно возникает в работе с целью поиска наибольших или наименьших значений в списке.
В Excel с этой задачей можно справиться 2 способами: стандартным инструментом сортировки и с помощью функций.
Для примера возьмем простую таблицу со списком числовых значений, в которой в дальнейшем и будем ранжировать данные:
Сортировка данных
Начнем с самого простого и доступного варианта — сортировки.
Мы уже частично разбирали как можно структурировать данные с помощью фильтра и сортировки.
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная -> Редактирование -> Сортировка и фильтр, а далее указать по какому критерию нужно произвести сортировку.
В данном случае выберем Сортировка по убыванию, где значения будут расположены от большего к меньшему:
Минусом данного способа является изменение структуры исходных данных, так как в процессе сортирования данных строки и столбцы могут меняться местами, что в некоторых случаях неудобно или невозможно сделать.
Также к важным недостаткам этого варианта можно отнести отсутствие возможности автоматизировать сортировку. Поэтому каждый раз при изменении данных сортировку придется делать еще раз.
В качестве решения данной проблемы рассмотрим другой способ ранжирования, который впрочем можно рассматривать и отдельно от решения этой задачи.
Ранжирование данных
При отсутствии возможности изменения структуры документа мы можем создать дополнительный ряд данных, где будут содержаться порядковые номера исходных данных.
Получить эти порядковые номера нам поможет функция РАНГ (а также РАНГ.РВ и РАНГ.СР).
Функция РАНГ в Excel
Синтаксис и описание функции:
РАНГ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке.
- Число(обязательный аргумент) — число для которого вычисляется ранг;
- Ссылка(обязательный аргумент) — массив или ссылка на массив чисел;
- Порядок(необязательный аргумент) — способ упорядочения. Если аргумент равен 0 или не указан, то значение 1 присваивается максимальному элементу в списке (условно говоря, сортируем по убыванию), в ином случае значение 1 присваивается минимальному элементу (сортируем по возрастанию).
Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР, а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.
Функции РАНГ.РВ и РАНГ.СР в Excel
Синтаксис и описание функций:
РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.
РАНГ.СР(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается средний ранг.
Аргументы у всех трех функций одинаковые, т.е. кардинально они почти не отличаются, есть небольшие различие в деталях.
На примере исходной таблицы посмотрим как работает с данными каждая из функций:
Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.
В случае с РАНГ.РВ равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки и Мультиварки соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.
На этом различия между ними заканчиваются, поэтому в зависимости от ваших задач можно использовать ту или иную функцию.
Если нужно отсортировать значения по возрастанию, то в качестве аргумента Порядок нужно указать значение 1:
Автоматическая сортировка
Немного усложним задачу и представим, что нам в дальнейшем нужно составить отсортированную таблицу, которая бы автоматически обновлялась при изменении данных в исходной таблице.
Например, это можно сделать с помощью функции ВПР, или комбинации ИНДЕКС и ПОИСКПОЗ, однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:
В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции СТРОКА или СТОЛБЕЦ, поделенные на заведомо большую величину.
Этот шаг позволит нам получить различные числа в исходных данных, избежать совпадения рангов и ошибки при подтягивании данных:
Теперь для всех элементов таблицы (даже изначально совпадающих) определен свой индивидуальный ранг отличный от остальных, поэтому ошибок при автоматическом ранжировании данных удастся избежать.
Спасибо за внимание!
Если у вас остались вопросы — пишите в комментариях.
Выполнение ранжирования в Microsoft Excel
При работе с данными часто возникает потребность выяснить, какое место занимает в совокупном перечне по величине тот или иной показатель. В статистике это называется ранжированием. В Excel имеются инструменты, которые позволяют пользователям быстро и легко произвести данную процедуру. Давайте выясним, как ими пользоваться.
Функции ранжирования
Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.
Способ 1: функция РАНГ.РВ
Оператор РАНГ.РВ производит обработку данных и выводит в указанную ячейку порядковый номер заданного аргумента из совокупного списка. Если несколько значений имеют одинаковый уровень, то оператор выводит высший из перечня значений. Если, например, два значения будут иметь одинаковую величину, то им обоим будет присвоен второй номер, а уже следующее по величине значение будет иметь четвертый. Кстати, полностью аналогично поступает и оператор РАНГ в более старых версиях Эксель, так что данные функции можно считать идентичными.
Синтаксис этого оператора записывается следующим образом:
Аргументы «число» и «ссылка» являются обязательными, а «порядок» — необязательным. В качестве аргумента «число» нужно ввести ссылку на ту ячейку, где содержится значение, порядковый номер которого нужно узнать. Аргумент «ссылка» содержит адрес всего диапазона, который ранжируется. Аргумент «порядок» может иметь два значения – «0» и «1». В первом случае отсчет порядка идет по убывающей, а во втором – по возрастающей. Если данный аргумент не указан, то он автоматически считается программой равным нулю.
Данную формулу можно записывать вручную, в ту ячейку, где вы хотите, чтобы выводился результат обработки, но для многих пользователей удобнее задавать вводные через окно Мастера функций.
- Выделяем на листе ячейку, в которую будет выводиться результат обработки данных. Щелкаем по кнопке «Вставить функцию». Она локализуется слева от строки формул.
Эти действия приводят к тому, что запускается окно Мастера функций. В нем представлены все (за редким исключениям) операторы, которые можно использовать для составления формул в Excel. В категории «Статистические» или «Полный алфавитный перечень» находим наименование «РАНГ.РВ», выделяем его и щелкаем по кнопке «OK».
После указанных выше действий будет активировано окно аргументов функции. В поле «Число» следует ввести адрес той ячейки, данные в которой нужно ранжировать. Это можно сделать вручную, но удобнее выполнить тем способом, о котором пойдет речь ниже. Устанавливаем курсор в поле «Число», а затем просто выделяем нужную ячейку на листе.
После этого её адрес будет занесен в поле. Точно таким же образом заносим данные и в поле «Ссылка», только в этом случае выделяем весь диапазон, в границах которого происходит ранжирование.
Если вы хотите, чтобы ранжирование происходило от меньшего к большему, то в поле «Порядок» следует установить цифру «1». Если же нужно, чтобы порядок распределялся от большего к меньшему (а в подавляющем числе случаев именно это и требуется), то данное поле оставляем пустым.
После того, как все вышеуказанные данные внесены, жмем на кнопку «OK».
После выполнения данных действий в предварительно указанной ячейке будет выведен порядковый номер, который имеет выбранное вами значение среди всего перечня данных.
Если вы желаете проранжировать всю указанную область, то совсем не нужно для каждого показателя вводить отдельную формулу. Прежде всего, делаем адрес в поле «Ссылка» абсолютным. Перед каждым значением координат добавляем знак доллара ($). При этом изменять значения в поле «Число» на абсолютные ни в коем случае не следует, иначе формула будет рассчитываться некорректно.
После этого нужно установить курсор в нижний правый угол ячейки, и дождаться появления маркера заполнения в виде небольшого крестика. Затем зажать левую кнопку мыши и протянуть маркер параллельно вычисляемой области.
Как видим, таким образом, формула скопируется, а ранжирование будет произведено на всем диапазоне данных.
Способ 2: функция РАНГ.СР
Второй функцией, которая производит операцию ранжирования в Экселе, является РАНГ.СР. В отличие от функций РАНГ и РАНГ.РВ, при совпадении значений нескольких элементов данный оператор выдает средний уровень. То есть, если два значения имеют равную величину и следуют после значения под номером 1, то им обоим будет присвоен номер 2,5.
Синтаксис РАНГ.СР очень похож на схему предыдущего оператора. Выглядит он так:
Формулу можно вводить вручную или через Мастер функций. На последнем варианте мы подробнее и остановимся.
- Производим выделение ячейки на листе для вывода результата. Таким же образом, как и в предыдущий раз, переходим в Мастер функций через кнопку «Вставить функцию».
После открытия окна Мастера функций выделяем в перечне категории «Статистические» наименование РАНГ.СР и жмем на кнопку «OK».
Внесение данных в поля происходит точно таким же способом, как и у предыдущего оператора. После того, когда все настройки выполнены, жмем на кнопку «OK».
Как видим, после выполненных действий результат расчета был выведен в ячейку, отмеченную в первом пункте данной инструкции. Сам итог представляет собой место, которое занимает конкретное значение среди других величин диапазона. В отличие от результата РАНГ.РВ, итог оператора РАНГ.СР может иметь дробное значение.
Как видим, в Экселе существует две функции для определения ранжирования конкретного значения в диапазоне данных: РАНГ.РВ и РАНГ.СР. Для более старых версий программы используется оператор РАНГ, который, по сути, является полным аналогом функции РАНГ.РВ. Главное отличие формул РАНГ.РВ и РАНГ.СР состоит в том, что первая из них указывает наивысший уровень при совпадении значений, а вторая выводит средний показатель в виде десятичной дроби. Это единственное различие между этими операторами, но его нужно учитывать при выборе того, какой именно функцией пользователю лучше воспользоваться.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 11907 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.