Как ранжировать данные в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Функции РАНГ, РАНГ.РВ и РАНГ.СР для ранжирования данных в Excel

Для ранжирования данных в Excel применяются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в ранжированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.

Пример функции РАНГ в Excel

Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел число 2 будет иметь ранг 1; 26 –2; 30 –3 (как наибольшее значение в списке).

Число . Для, которого необходимо определить номер в ранжировании.
Ссылка . На массив чисел или диапазон ячеек с числовыми значениями. Если задать в качестве аргумента просто числа, то функция вернет ошибку. Нечисловым значениям номер не присваивается.
Порядок . Способ упорядочения чисел в списке. Варианты: аргумент равен «0» или опущен – значение 1 присваивается максимальному числу в списке (как будто список отсортирован в порядке убывания); аргумент равен любому неравному нулю числу – номер ранжирования 1 присваивается минимальному числу в списке (как будто список отсортирован в порядке возрастания).

Определим ранжирование чисел в списке без повторов:

РАНГ.

Аргумент, определяющий способ упорядочения чисел, равен «0». Следовательно, в данной функции номера присваивались значениям от большего к меньшему. Максимальному числу 87 присвоен номер 1.

По возрастанию.

В третьем столбце приведена формула с рангом по возрастанию.

Определим номера значений в списке, где присутствуют повторяющиеся значения.

Повторяющиеся значения.

Желтым цветом выделены повторяющиеся числа. Для них определяется один и тот же номер. Например, числу 7 во втором столбце присвоен номер 9 (и во второй строке, и в девятой); в третьем столбце – 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем – 4.

Чтобы ранги не повторялись (иногда это мешает пользователю решить поставленную задачу), используется следующая формула:

Формула.

Для работы функции можно установить пределы. Например, необходимо ранжировать только значения от 0 до 30. Чтобы решить задачу, применим функцию ЕСЛИ (=ЕСЛИ(A2 ↑

Функция РАНГ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции РАНГ в 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: представление связей с помощью RANK

В этой статье описывается, как использовать RANK для присвоения среднего значения ранга привязанным наблюдениям.

Дополнительная информация

При преобразовании числовых данных в ранг может потребоваться представить связи, чтобы связанные наблюдения получили средний ранг по всем таким наблюдениям, а не наименьший возможный ранг. Это показано в числовом примере в этой статье.

Хотя текущая версия RANK возвращает соответствующие результаты в большинстве ситуаций, в этой статье рассматривается ситуация, в которой возникает связь. Например, ранжирование может потребовать обработки связей при использовании непараметрического статистического теста гипотезы, включающего ранги.

Метод, описывающийся в этой статье, также описан в файле справки RANK для Microsoft Office Excel 2003 и более поздних версий Excel (но не в более ранних файлах справки по ранжированию). Эта процедура одинаково хорошо работает для всех версий Excel. Сама функция RANK не изменилась.

Синтаксис

RANK(number, ref, order)

Число должно иметь числовое значение; ссылка должна быть массивом или диапазоном ячеек, содержащего числовые значения данных; порядок является необязательным. Если опустить порядок или присвоить ему значение 0 (ноль), ранг числа будет позицией числа в ref, если ref ранжирован в порядке убывания. Если порядок присваивается любому ненулевому значению, предполагается, что ref ранжирован в порядке возрастания.

Пример использования

Чтобы проиллюстрировать это использование RANK, создайте пустой лист Excel, скопируйте следующую таблицу, выберите ячейку A1 на пустом листе Excel, а затем щелкните «Вставить » в меню «Правка», чтобы записи в следующей таблице заполняли ячейки A1:F12 на листе.

В Microsoft Office Excel 2007 команда «Вставить » находится в группе буфера обмена на вкладке «Главная «.

A	Б	В	D	E
10	=RANK(A1,$A$1:$A$12;1)	=B1 + (COUNT($A$1:$A$12) + 1 — RANK($A 1,$A$1:$A$12,0) — RANK($A 1,$A$1:$A$12;1))/2	=RANK(A1,$A$1:$A$12,0)	=E1 + (COUNT($A$1:$A$12) + 1 — RANK($A 1,$A$1:$A$12,0) — RANK($A 1,$A$1:$A$12;1))/2
21	=RANK(A2,$A$1:$A$12;1)	=B2 + (COUNT($A$1:$A$12) + 1 — RANK($A 2;$A$1:$A$12,0) — RANK($A 2,$A$1:$A$12;1))/2	=RANK(A2,$A$1:$A$12,0)	=E2 + (COUNT($A$1:$A$12) + 1 — RANK($A 2,$A$1:$A$12,0) — RANK($A 2,$A$1:$A$12,1))/2
21	=RANK(A3,$A$1:$A$12;1)	=B3 + (COUNT($A$1:$A$12) + 1 — RANK($A 3,$A$1:$A$12,0) — RANK($A 3,$A$1:$A$12,1))/2	=RANK(A3,$A$1:$A$12,0)	=E3 + (COUNT($A$1:$A$12) + 1 — RANK($A 3,$A$1:$A$12,0) — RANK($A 3,$A$1:$A$12;1))/2
21	=RANK(A4,$A$1:$A$12;1)	=B4 + (COUNT($A$1:$A$12) + 1 — RANK($A 4,$A$1:$A$12,0) — RANK($A 4,$A$1:$A$12,1))/2	=RANK(A4,$A$1:$A$12,0)	=E4 + (COUNT($A$1:$A$12) + 1 — RANK($A 4,$A$1:$A$12,0) — RANK($A 4,$A$1:$A$12;1))/2
21	=RANK(A5,$A$1:$A$12;1)	=B5 + (COUNT($A$1:$A$12) + 1 — RANK($A 5,$A$1:$A$12,0) — RANK($A 5,$A$1:$A$12,1))/2	=RANK(A5,$A$1:$A$12,0)	=E5 + (COUNT($A$1:$A$12) + 1 — RANK($A 5,$A$1:$A$12,0) — RANK($A 5,$A$1:$A$12,1))/2
33	=RANK(A6,$A$1:$A$12;1)	=B6 + (COUNT($A$1:$A$12) + 1 — RANK($A 6,$A$1:$A$12,0) — RANK($A 6,$A$1:$A$12;1))/2	=RANK(A6,$A$1:$A$12,0)	=E6 + (COUNT($A$1:$A$12) + 1 — RANK($A 6,$A$1:$A$12,0) — RANK($A 6,$A$1:$A$12,1))/2
33	=RANK(A7,$A$1:$A$12;1)	=B7 + (COUNT($A$1:$A$12) + 1 — RANK($A 7,$A$1:$A$12,0) — RANK($A 7,$A$1:$A$12,1))/2	=RANK(A7,$A$1:$A$12,0)	=E7 + (COUNT($A$1:$A$12) + 1 — RANK($A 7,$A$1:$A$12,0) — RANK($A 7,$A$1:$A$12,1))/2
52	=RANK(A8,$A$1:$A$12;1)	=B8 + (COUNT($A$1:$A$12) + 1 — RANK($A 8,$A$1:$A$12,0) — RANK($A 8,$A$1:$A$12,1))/2	=RANK(A8,$A$1:$A$12,0)	=E8 + (COUNT($A$1:$A$12) + 1 — RANK($A 8,$A$1:$A$12,0) — RANK($A 8,$A$1:$A$12,1))/2
52	=RANK(A9,$A$1:$A$12;1)	=B9 + (COUNT($A$1:$A$12) + 1 — RANK($A 9,$A$1:$A$12,0) — RANK($A 9,$A$1:$A$12,1))/2	=RANK(A9,$A$1:$A$12,0)	=E9 + (COUNT($A$1:$A$12) + 1 — RANK($A 9,$A$1:$A$12,0) — RANK($A 9,$A$1:$A$12;1))/2
52	=RANK(A10,$A$1:$A$12;1)	=B10 + (COUNT($A$1:$A$12) + 1 — RANK($A 10,$A$1:$A$12,0) — RANK($A 10,$A$1:$A$12,1))/2	=RANK(A10,$A$1:$A$12,0)	=E10 + (COUNT($A$1:$A$12) + 1 — RANK($A 10,$A$1:$A$12,0) — RANK($A 10,$A$1:$A$12;1)))/2
61	=RANK(A11,$A$1:$A$12;1)	=B11 + (COUNT($A$1:$A$12) + 1 — RANK($A 11;$A$1:$A$12,0) — RANK($A 11,$A$1:$A$12,1))/2	=RANK(A11,$A$1:$A$12,0)	=E11 + (COUNT($A$1:$A$12) + 1 — RANK($A 11,$A$1:$A$12,0) — RANK($A 11,$A$1:$A$12,1)))/2
73	=RANK(A12,$A$1:$A$12;1)	=B12 + (COUNT($A$1:$A$12) + 1 — RANK($A 12;$A$1:$A$12,0) — RANK($A 12,$A$1:$A$12;1)))/2	=RANK(A12,$A$1:$A$12,0)	=E12 + (COUNT($A$1:$A$12) + 1 — RANK($A 12,$A$1:$A$12,0) — RANK($A 12,$A$1:$A$12,1))/2

После вставки этой таблицы на новый лист Excel нажмите кнопку «Параметры вставки» и выберите «Сопоставление форматирования назначения».

В Excel 2003 с выбранным диапазоном вставки наведите указатель мыши на пункт «Столбец» в меню «Формат» и выберите пункт «Автоподбор выделения».

В Excel 2007 с выбранным диапазоном вставки щелкните «Формат» в группе » Ячейка» на вкладке «Главная» и выберите пункт «Автоподбор ширины столбца«.

Для некоторых целей может потребоваться использовать определение ранга, которое учитывает связи. Для этого добавьте следующий коэффициент коррекции к значению, возвращаемом RANK. Этот коэффициент коррекции подходит, если ранг вычисляется в порядке убывания (порядок = 0 или пропущен) или в порядке возрастания (порядок = ненулевое значение).

(COUNT(ref) + 1 – RANK(number, ref, 0) – RANK(number, ref, 1))/2.

На листе показано это определение ранга. Данные в ячейках A1:A12. Ранги, возвращаемые rank в порядке возрастания, находятся в ячейках B1:B12. Наблюдения в ячейках A2:A5 связаны с общим значением 21. Это дает общий ранг 2. Имеется одно наблюдение с более низким рейтингом— 10. Эти четыре значения из 21 принимают ранжированные позиции 2, 3, 4 и 5 и имеют средний ранг (2 + 3 + 4 + 5)/4 = 3,5. Аналогичным образом, два наблюдения в ячейках A6:A7 — это каждое 33. Существует пять наблюдений с более низким рангом. Таким образом, эти два наблюдения принимают ранжированные позиции 6 и 7 и имеют средний ранг (6 + 7)/2 = 6,5. Наконец, три наблюдения в ячейках A8:A10 имеют общее значение 52. Существует семь наблюдений с более низким рангом. Таким образом, эти три наблюдения принимают ранжированные позиции 8, 9 и 10 и имеют средний ранг (8 + 9 + 10)/3 = 9.

Записи в столбце C содержат коэффициент коррекции для привязанных рангов и показывают эти средние ранги, которые принимают во внимание связи. Значения в столбцах B и C точно такие же, где наблюдения не связаны с другими наблюдениями, такими как строки 1, 11 и 12.

Ячейки E1:E12 содержат ранги, возвращаемые RANK в порядке убывания. Есть две записи с более низкими рангами, чем три записи в ячейках A8:A10. Ячейки A8:A10 имеют общее значение 54. Таким образом, эти три записи принимают ранжированные позиции 3, 4 и 5 и имеют средний ранг (3 + 4 + 5)/3 = 4. Существует пять записей с более низким рангом, чем две записи в ячейках A6:A7. Ячейки A6:A7 имеют общее значение 33. Таким образом, эти две записи имеют ранжированные позиции 6 и 7 и имеют средний ранг (6 + 7)/2 = 6,5. В ячейках A2:A5 имеется семь записей с более низкими рангами, чем четыре записи. Ячейки A2:A5 имеют общее значение 21. Таким образом, эти четыре записи имеют ранжированные позиции 8, 9, 10, 11 и имеют среднее значение (8 + 9 + 10 + 11)/4 = 9,5.

Записи в столбце F содержат коэффициент коррекции для привязанных рангов и показывают эти средние ранги, которые принимают во внимание связи. Значения в столбцах E и F точно такие же, где наблюдения не связаны с другими наблюдениями, такими как строки 1, 11 и 12.

Выводы

В этой статье описывается и иллюстрируется коэффициент исправления, который можно использовать для учета связанных рангов при ранжировании данных. Вы можете использовать коэффициент коррекции вместе с функцией RANK. Коэффициент коррекции работает одинаково хорошо, если ранги находятся в порядке возрастания или убывания.

Как ранжировать данные в excel