Рейтинг по числу упоминаний в эксель от большего к меньшему
Обновлено: 07.07.2024
В этой статье описаны синтаксис формулы и использование функции РАНГ в Microsoft Excel.
Описание
Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если отсортировать список, то ранг числа будет его позицией.)
Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Дополнительные сведения о новых функциях см. в разделах Функция РАНГ.СР и Функция РАНГ.РВ.
Синтаксис
Аргументы функции РАНГ описаны ниже.
Число Обязательный. Число, для которого определяется ранг.
Ссылка Обязательный. Массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются.
Порядок Необязательный. Число, определяющее способ упорядочения.
Если значение аргумента "порядок" равно 0 или опущено, ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке убывания.
Если значение аргумента "порядок" — любое число, кроме нуля, то ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке возрастания.
Замечания
Функция РАНГ присваивает повторяющимся числам одинаковые значения ранга. Однако наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).
В некоторых случаях может потребоваться определить ранг, учитывая при этом связь. В предыдущем примере можно было бы получить измененный ранг числа 5,5 для числа 10. Для этого добавьте следующий коэффициент исправления к значению, возвращаемом функцией РАНГ. Этот коэффициент исправления подходит как для случая, когда ранг вычисляется в порядке убывания (порядок = 0 или опущен) или в порядке возрастания (порядок = нелиценз).
Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ(число, ссылка, 0) – РАНГ(число, ссылка, 1)]/2.
В следующем примере РАНГ(A2,A1:A5,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей в учетной записи, равен 3 + 0,5 = 3,5. Если то или иное число появляется в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ для связи не будет изменяться.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
При работе с данными часто возникает потребность выяснить, какое место занимает в совокупном перечне по величине тот или иной показатель. В статистике это называется ранжированием. В Excel имеются инструменты, которые позволяют пользователям быстро и легко произвести данную процедуру. Давайте выясним, как ими пользоваться.
Функции ранжирования
Для выполнения ранжирования в Экселе предусмотрены специальные функции. В старых версиях приложения был один оператор, предназначенный для решения этой задачи – РАНГ. В целях совместимости он оставлен в отдельной категории формул и в современных версиях программы, но в них все-таки желательно работать с более новыми аналогами, если есть такая возможность. К ним относятся статистические операторы РАНГ.РВ и РАНГ.СР. О различиях и алгоритме работы с ними мы поговорим далее.
Способ 1: функция РАНГ.РВ
Оператор РАНГ.РВ производит обработку данных и выводит в указанную ячейку порядковый номер заданного аргумента из совокупного списка. Если несколько значений имеют одинаковый уровень, то оператор выводит высший из перечня значений. Если, например, два значения будут иметь одинаковую величину, то им обоим будет присвоен второй номер, а уже следующее по величине значение будет иметь четвертый. Кстати, полностью аналогично поступает и оператор РАНГ в более старых версиях Эксель, так что данные функции можно считать идентичными.
Синтаксис этого оператора записывается следующим образом:
Данную формулу можно записывать вручную, в ту ячейку, где вы хотите, чтобы выводился результат обработки, но для многих пользователей удобнее задавать вводные через окно Мастера функций.
-
Выделяем на листе ячейку, в которую будет выводиться результат обработки данных. Щелкаем по кнопке «Вставить функцию». Она локализуется слева от строки формул.
После этого её адрес будет занесен в поле. Точно таким же образом заносим данные и в поле «Ссылка», только в этом случае выделяем весь диапазон, в границах которого происходит ранжирование.
Если вы хотите, чтобы ранжирование происходило от меньшего к большему, то в поле «Порядок» следует установить цифру «1». Если же нужно, чтобы порядок распределялся от большего к меньшему (а в подавляющем числе случаев именно это и требуется), то данное поле оставляем пустым.
Если вы желаете проранжировать всю указанную область, то совсем не нужно для каждого показателя вводить отдельную формулу. Прежде всего, делаем адрес в поле «Ссылка» абсолютным. Перед каждым значением координат добавляем знак доллара ($). При этом изменять значения в поле «Число» на абсолютные ни в коем случае не следует, иначе формула будет рассчитываться некорректно.
После этого нужно установить курсор в нижний правый угол ячейки, и дождаться появления маркера заполнения в виде небольшого крестика. Затем зажать левую кнопку мыши и протянуть маркер параллельно вычисляемой области.
Способ 2: функция РАНГ.СР
Второй функцией, которая производит операцию ранжирования в Экселе, является РАНГ.СР. В отличие от функций РАНГ и РАНГ.РВ, при совпадении значений нескольких элементов данный оператор выдает средний уровень. То есть, если два значения имеют равную величину и следуют после значения под номером 1, то им обоим будет присвоен номер 2,5.
Синтаксис РАНГ.СР очень похож на схему предыдущего оператора. Выглядит он так:
Формулу можно вводить вручную или через Мастер функций. На последнем варианте мы подробнее и остановимся.
-
Производим выделение ячейки на листе для вывода результата. Таким же образом, как и в предыдущий раз, переходим в Мастер функций через кнопку «Вставить функцию».
- Число (адрес ячейки содержащей элемент, уровень которого следует определить);
- Ссылка (координаты диапазона, ранжирование внутри которого выполняется);
- Порядок (необязательный аргумент).
Как видим, в Экселе существует две функции для определения ранжирования конкретного значения в диапазоне данных: РАНГ.РВ и РАНГ.СР. Для более старых версий программы используется оператор РАНГ, который, по сути, является полным аналогом функции РАНГ.РВ. Главное отличие формул РАНГ.РВ и РАНГ.СР состоит в том, что первая из них указывает наивысший уровень при совпадении значений, а вторая выводит средний показатель в виде десятичной дроби. Это единственное различие между этими операторами, но его нужно учитывать при выборе того, какой именно функцией пользователю лучше воспользоваться.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.
Пример функции РАНГ в Excel
Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).
- Число . Для, которого необходимо определить номер в ранжировании.
- Ссылка . На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
- Порядок . Способ упорядочения чисел в списке. Варианты: аргумент равен «0» или опущен – значение 1 присваивается максимальному числу в списке (как будто список отсортирован в порядке убывания); аргумент равен любому неравному нулю числу – номер ранжирования 1 присваивается минимальному числу в списке (как будто список отсортирован в порядке возрастания).
Определим ранжирование чисел в списке без повторов:
Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.
В третьем столбце приведена формула с рангом по возрастанию.
Определим номера значений в списке, где присутствуют повторяющиеся значения.
Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.
Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:
Действительно, наши проблемы решил бы простой фильтр, но любая сортировка по возрастанию или убыванию изменит положение данных в таблице согласно их величине. Рассмотрим вот такой пример. Имеется таблица:
Если мы включаем фильтр от большего к меньшему, то порядок магазинов изменится, так как необходимо увидеть лучший и худший результаты. Выделяем заголовок таблицы, на вкладке "Главная" нажимаем кнопку "Сортировка и фильтр" далее выбираем строчку "Фильтр". Появится возможность выбирать критерии сортировки. Согласно условию выбираем сортировку "От максимального к минимальному".
Порядок магазинов изменился, но, согласно условию задачи, этого допускать нельзя. В таком случае идеально подойдёт функция РАНГ. Начиная с версии Excel 2010 были введены новые функции РАНГ.РВ и РАНГ.СР. Функция РАНГ осталась для совместимости с предыдущими версиями программы поэтому будет использоваться схожая функция РАНГ.РВ.
Главное отличие функций - РАНГ.РВ учитывает равные значения и присваивает им одинаковый ранг.
РАНГ.СР - возвращает среднее между двумя одинаковыми рангами (их порядковыми номерами).
Синтаксис формулы будет следующий:
=РАНГ.РВ(число;ссылка;порядок)
число - числовое значение для которого вычисляется ранг (обязательный аргумент);
ссылка - адрес ячейки или диапазона ячеек среди которых будет произведено ранжирование (обязательный аргумент);
порядок - порядок выставления ранга (если стоит 0 или поле останется пустым значит присвоение ранга будет по убыванию, если стоит 1, то будет производиться ранжирование по возрастанию, необязательный аргумент).
Совершим следующие действия:
- добавим в нашу таблицу дополнительный столбец "Место в прибыли";
- вызываем мастер функций;
- вводим в поле поиска РАНГ.РВ;
- нажимаем "ОК";
- заполняем аргументы функции.
В этой формуле специально закреплён диапазон от B2 до B10, чтобы можно было использовать маркер автозаполнения. Протянем за него до конца таблицы. Результат не заставит себя долго ждать.
В аргументе функции "Порядок" был выставлен 0, поэтому ранг 1 назначен самому большому значению, 6 - самому маленькому.
Дальнейшую сортировку можно усложнять с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ, о которых мы говорили в предыдущих статьях.
Если появились вопросы - пишите комментарии! Всем удачи!
Читайте также: