При работе с данными часто возникает потребность выяснить, какое место занимает в совокупном перечне по величине тот или иной показатель. В статистике это называется ранжированием. В Excel имеются инструменты, которые позволяют пользователям быстро и легко произвести данную процедуру. Давайте выясним, как ими пользоваться.
Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.
Оператор РАНГ.РВ производит обработку данных и выводит в указанную ячейку порядковый номер заданного аргумента из совокупного списка. Если несколько значений имеют одинаковый уровень, то оператор выводит высший из перечня значений. Если, например, два значения будут иметь одинаковую величину, то им обоим будет присвоен второй номер, а уже следующее по величине значение будет иметь четвертый. Кстати, полностью аналогично поступает и оператор РАНГ в более старых версиях Эксель, так что данные функции можно считать идентичными.
Синтаксис этого оператора записывается следующим образом:
Аргументы «число» и «ссылка» являются обязательными, а «порядок» - необязательным. В качестве аргумента «число» нужно ввести ссылку на ту ячейку, где содержится значение, порядковый номер которого нужно узнать. Аргумент «ссылка» содержит адрес всего диапазона, который ранжируется. Аргумент «порядок» может иметь два значения – «0» и «1». В первом случае отсчет порядка идет по убывающей, а во втором – по возрастающей. Если данный аргумент не указан, то он автоматически считается программой равным нулю.
Данную формулу можно записывать вручную, в ту ячейку, где вы хотите, чтобы выводился результат обработки, но для многих пользователей удобнее задавать вводные через окно Мастера функций.
Урок: Мастер функций в Excel
Второй функцией, которая производит операцию ранжирования в Экселе, является РАНГ.СР. В отличие от функций РАНГ и РАНГ.РВ, при совпадении значений нескольких элементов данный оператор выдает средний уровень. То есть, если два значения имеют равную величину и следуют после значения под номером 1, то им обоим будет присвоен номер 2,5.
Синтаксис РАНГ.СР очень похож на схему предыдущего оператора. Выглядит он так:
Формулу можно вводить вручную или через Мастер функций. На последнем варианте мы подробнее и остановимся.
Внесение данных в поля происходит точно таким же способом, как и у предыдущего оператора. После того, когда все настройки выполнены, жмем на кнопку «OK».
Урок: Другие статистические функции в Microsoft Excel
Урок: Как сделать автозаполнение в Эксель
Как видим, в Экселе существует две функции для определения ранжирования конкретного значения в диапазоне данных: РАНГ.РВ и РАНГ.СР. Для более старых версий программы используется оператор РАНГ, который, по сути, является полным аналогом функции РАНГ.РВ. Главное отличие формул РАНГ.РВ и РАНГ.СР состоит в том, что первая из них указывает наивысший уровень при совпадении значений, а вторая выводит средний показатель в виде десятичной дроби. Это единственное различие между этими операторами, но его нужно учитывать при выборе того, какой именно функцией пользователю лучше воспользоваться.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Научимся ранжировать числовые данные в Excel с помощью стандартной сортировки, а также функции РАНГ и ее частных случаях (РАНГ.РВ и РАНГ.СР), которые помогут в автоматизации сортировки.
Приветствую всех, дорогие читатели блога TutorExcel.Ru.
Задача ранжирования числовых данных постоянно возникает в работе с целью поиска наибольших или наименьших значений в списке.
В Excel с этой задачей можно справиться 2 способами: стандартным инструментом сортировки
и с помощью функций
.
Для примера возьмем простую таблицу со списком числовых значений, в которой в дальнейшем и будем ранжировать данные:
Начнем с самого простого и доступного варианта - сортировки.
Мы уже частично разбирали как можно структурировать данные с помощью фильтра и сортировки.
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная
-> Редактирование
-> Сортировка и фильтр
, а далее указать по какому критерию нужно произвести сортировку.
В данном случае выберем Сортировка по убыванию , где значения будут расположены от большего к меньшему:
Минусом данного способа является изменение структуры исходных данных, так как в процессе сортирования данных строки и столбцы могут меняться местами, что в некоторых случаях неудобно или невозможно сделать.
Также к важным недостаткам этого варианта можно отнести отсутствие возможности автоматизировать сортировку. Поэтому каждый раз при изменении данных сортировку придется делать еще раз.
В качестве решения данной проблемы рассмотрим другой способ ранжирования, который впрочем можно рассматривать и отдельно от решения этой задачи.
При отсутствии возможности изменения структуры документа мы можем создать дополнительный ряд данных, где будут содержаться порядковые номера исходных данных.
Получить эти порядковые номера нам поможет функция РАНГ
(а также РАНГ.РВ
и РАНГ.СР
).
Синтаксис и описание функции:
Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР , а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.
Синтаксис и описание функций:
РАНГ.РВ(число; ссылка;)
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.
Аргументы у всех трех функций одинаковые, т.е. кардинально они почти не отличаются, есть небольшие различие в деталях.
На примере исходной таблицы посмотрим как работает с данными каждая из функций:
Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.
В случае с РАНГ.РВ
равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки
и Мультиварки
соответствует одинаковое значение элемента - 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг - 3.
Для РАНГ.СР
для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами - 3,5.
На этом различия между ними заканчиваются, поэтому в зависимости от ваших задач можно использовать ту или иную функцию.
Если нужно отсортировать значения по возрастанию, то в качестве аргумента Порядок
нужно указать значение 1:
Немного усложним задачу и представим, что нам в дальнейшем нужно составить отсортированную таблицу, которая бы автоматически обновлялась при изменении данных в исходной таблице.
Например, это можно сделать с помощью функции ВПР, или комбинации ИНДЕКС и ПОИСКПОЗ, однако в случае наличия одинаковых значений в списке мы не сможем корректно подтянуть данные и получим ошибку:
В этом случае можно воспользоваться простым приемом в виде небольшой хитрости.
Добавим к каждому значению исходной таблицы не совпадающие случайные числа близкие к нулю, к примеру, я для этих целей использую функции СТРОКА или СТОЛБЕЦ, поделенные на заведомо большую величину.
Этот шаг позволит нам получить различные числа в исходных данных, избежать совпадения рангов и ошибки при подтягивании данных:
Теперь для всех элементов таблицы (даже изначально совпадающих) определен свой индивидуальный ранг отличный от остальных, поэтому ошибок при автоматическом ранжировании данных удастся избежать.
Скачать файл с примером .
Спасибо за внимание!
Если у вас остались вопросы - пишите в комментариях.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.
Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел {30;2;26} число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).
Синтаксис функции:
Определим ранжирование чисел в списке без повторов:
Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.
В третьем столбце приведена формула с рангом по возрастанию.
Определим номера значений в списке, где присутствуют повторяющиеся значения.
Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.
Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:
Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2
Серым цветом выделены значения, которые соответствуют заданному условию. Для чисел, которые больше 30, выводится пустая строка.
В версиях Excel, начиная с 2010 года, появилась функция РАНГ.РВ. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «РВ» в названии указывают на то, что при обнаружении формулой одинаковых значений функция вернет высший номер ранжирования (то есть первого обнаруженного элемента в перечне равных).
Как видно из примера, данная функция обрабатывает повторяющиеся числа в списке точно так же, как и в обычной формуле. Если необходимо избежать повторений рангов, используем другую формулу (см. выше).
Возвращает номера числового значения в списке (порядковый номер относительно других значений). То есть выполняет ту же задачу. Только при обнаружении идентичных значений возвращает средний показатель.
Вот результат работы функции:
Формула в столбце «по убыванию»: =РАНГ.СР(A2;$A$2:$A$9;0). Так, функция значению 87 присвоила средний номер 1,5.
Допустим, в списке чисел три повторяющихся значения (выделены оранжевым цветом).
Функция присвоила каждому из них ранг 5, что является средним для 4, 5 и 6.
Сравним работу двух функций:
Напомним, что эти две функции работают только в Excel 2010 и выше. В более ранних версиях для этих целей можно задействовать формулу массива.
Скачать примеры функции ранжирования РАНГ в Excel.
Таким образом, все выше описанные примеры позволяют автоматизировать работу по ранжированию данных и составлению рейтинга значений без применения сортировки.
1. В дополнительный столбец, в котором будем указывать рейтинг, вставляем функцию РАНГ (пишем в ячейке =РАНГ и выбираем из списка предложенную EXCEL функцию, жмем в строке формул fx)
2. Заполняем аргументы в открывшемся окне: «Число» - указываем первое значение в нашей таблице в той же строке, где находится формула.
3. «Ссылка» - указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).
4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.
5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.
При пользовании данной функцией, расчет рейтинга производится автоматически, и если вы изменили какое-либо значение, то по рейтингу произойдет автоматический пересчет.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA
и далее "перевести")
Функция РАНГ() , английский вариант RANK(), возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 будет иметь ранг 1, т.к. это наименьшее число, число 10 - ранг 2, а 20 - ранг 3 (это ранг по возрастанию, когда наименьшему значению присваивается ранг 1). Если список отсортировать, то ранг числа будет его позицией (если нет повторов).
Синтаксис
Число - число, для которого определяется ранг.
Ссылка - ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;{10:50:30:40:50}) работать не будет. Но, если ввести формулу =РАНГ(B7;$A$7:$A$11) , то она будет работать (хотя ячейка B7 - вне списка с числами). Если в B7 содержится число вне списка с числами, то формула вернет ошибку #Н/Д.
Порядок - число, определяющее способ упорядочения.
Если список чисел находится в диапазоне 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;"<"&A7)+1
Если исходный список , то ранг числа будет его позицией в списке.
Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($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 .
Примечание . В добавилась функция РАНГ.РВ(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга). В файле примера дается пояснение работы этой функции. Также добавилась функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается среднее.
Ранжирование – это определение порядка согласно рангу. В Microsoft Excel для этих целей есть формула РАНГ. В версии 2010 добавлены еще две полезные формулы РАНГ.РВ и РАНГ.СР. Результатом работы данных функций является номер значения в определенном списке значений. В данной статье рассмотрим, упомянутые выше функции на конкретных примерах автоматизации составления рангов.
Использование функции РАНГ
С помощью данной функции можем узнать ранг значения по отношению к другим в числовом ряду. Если числа выстроены от меньшего к большему, то функция возвращает позицию значения в ряду чисел. Допустим, что имеется массив значений . Значение 20 имеет ранг один; 38 ранг два, а 45 соответствует ранг три, т.к. 45 самое большое значение в массиве значений.
У функции три параметра:
1-ый: Число, по отношению к которому определяется ранг (обязательный).
2-ой: Массив значений или ссылка на него. Если массив содержит значения, отличные от числовых, то они игнорируются (обязательный).
3-ий: Способ сортировки чисел (необязательный). «0» означает сортировку по убыванию. «1» означает сортировку по возрастанию.
Определение ранга чисел в числовом ряду без повторов:
Третий параметр равен нулю, поэтому значения присваивались от большего к меньшему, поэтому наибольшее число получило ранг единицу.
Использование функция РАНГ.РВ
Как уже упоминалось выше, с версии 2010, добавлены дополнительные способы для ранжирования. Эта функция отличается от РАНГ тем, что в случае выявления идентичных значений возвращает наивысший номер ранжирования, т.е. первого выявленного элемента из равных.
Использование функция РАНГ.СР
Эта функция тоже доступна только с версии 2010. Отличается от предыдущих тем, что при выявлении идентичных значений возвращает среднее значение.
Ниже представлен итог применения РАНГ.СР:
Функция РАНГ (RANK) в Excel используется для присвоения ранга значению из списка относительно других чисел.
Возвращает число, обозначающее ранг числа относительно других значений из списка.
=RANK(number, ref, ) – английская версия
Если аргумент Order (порядок) :
Команда Microsoft представила новую функцию RANK.EQ . Она работает точно так же, как функция RANK (РАНГ) . Команда MS рекомендует использовать эту новую функцию для повышения точности. Функция RANK (РАНГ) была сохранена для совместимости.
