Функція рангування в Excel із прикладами формул

У цьому посібнику пояснюється специфіка функції Rank в Excel та показано, як сортувати дані в Excel за різними критеріями, групувати дані, обчислювати процентильні ранги тощо. Коли вам потрібно визначити відносне положення числа у списку чисел , найпростіший спосіб – відсортувати числа у порядку зростання або спадання. Якщо з якоїсь причини сортування неможливе, формула рангу в Excel – ідеальний інструмент для виконання цієї роботи.

Функція Excel RANGE

Функція RANK в Excel повертає порядок (або ранг) числового значення порівняно з іншими значеннями в тому ж списку. Іншими словами, вона показує, яке значення є найвищим, другим за величиною тощо.

Вас також може зацікавити: Функція CHOOSE в Excel. Формули та приклади

У впорядкованому списку діапазон заданого числа буде його позицією. Функція RANK в Excel може визначити діапазон, починаючи з найбільшого значення (якби відсортовано у порядку спадання) або найменшого значення (якби відсортовано у порядку зростання). Синтаксис функції RANK в Excel такий:

RANK (номер, посилання, [порядок])

Dónde:

• Номер (обов'язково): значення, рейтинг якого ви хотіли б знайти.
• Ref (обов'язково): список числових значень для сортування. Його можна надати як масив чисел або як посилання на список чисел.
• Порядок (необов'язково): число, яке вказує, як сортувати значення:
  • Якщо 0 або опущено, значення сортуються в порядку спадання, тобто від найвищого до найменшого.
  • Якщо це 1 або будь-яке інше ненульове значення, значення сортуються в порядку зростання, тобто від найменшого до найвищого.

Функція RANK.EQ в Excel

RANK.EQ – це покращена версія функції RANK , представленої в Excel 2010. Вона має той самий синтаксис, що й RANK, і працює за тією ж логікою: якщо кілька значень мають однаковий ранг, найвищий ранг присвоюється всім цим значенням. (EQ означає «дорівнює»).

RANK.EQ (число, посилання, [порядок])

У Excel 2007 і старіших версіях завжди слід використовувати функцію RANK. В Excel 2010, Excel 2013 і Excel 2016 ви можете вибрати RANK або RANK.EQ. Однак було б доцільно використовувати RANK.EQ, оскільки RANK можна призупинити в будь-який час.

Функція Excel RANGE.AVG

RANK.AVG – це ще одна функція для знаходження діапазону в Excel , доступна лише в Excel 2010, Excel 2013, Excel 2016 та пізніших версіях. Вона має той самий синтаксис, що й дві інші функції:

RANK.AVG (номер, посилання, [порядок])

Різниця полягає в тому, що якщо кілька чисел мають однаковий ранг, повертається середній ранг (AVG означає «середній»).

4 речі, які ви повинні знати про RANK в Excel

Ось кілька речей, які слід пам’ятати про функцію Rank у Excel:

• Будь-яка формула діапазону в Excel працює лише для числових значень: додатних і від’ємних чисел, нулів, значень дати та часу. Нечислові значення в аргументі ref ігноруються.
• Усі функції RANGE повертають однаковий діапазон для повторюваних значень і пропускають подальше сортування, як показано в наступному прикладі.
• У Excel 2010 і новіших версіях функцію RANK було замінено на RANK.EQ і RANK.AVG. Для зворотної сумісності RANK все ще працює в усіх версіях Excel, але може бути недоступним у майбутньому.
• Якщо число не знайдено в посиланні, будь-яка функція діапазону Excel поверне помилку #N/A.

Основна формула діапазону Excel (від найвищого до найнижчого)

Щоб дізнатися більше про ранжирування за допомогою функції Rank в Excel, подивіться на цей знімок екрана:

Усі три формули сортують числа у стовпці B у порядку спадання (аргумент порядку опущено):

У всіх версіях Excel 2003 – 2016:

=РАНГ($B2,$B$2:$B$7)

У Excel 2010 – 2016:

=RANK.EQ($B2,$B$2:$B$7)

=RANK.AVG($B2,$B$2:$B$7)

Різниця полягає в тому, як ці формули обробляють повторювані значення. Як бачите, одна і та сама оцінка з’являється двічі, у клітинках B5 та B6, що впливає на подальше сортування:

• Формули RANK і RANK.EQ дають ранг 2 обом дубльованим балам. Наступний найвищий бал (Даніела) посідає четверте місце. Нікому не присвоюється 3 ранг.
• Формула RANK.AVG призначає різний ранг кожному дублікату за кадром (2 і 3 у цьому прикладі) і повертає середнє значення цих рангів (2.5). Знову ж таки, третій ранг нікому не присвоюється.

Як використовувати RANK в Excel – приклади формул

Дорога до досконалості, кажуть, прокладена практикою. Отже, щоб краще навчитися використовувати функцію RANK в Excel, окремо чи в поєднанні з іншими функціями, давайте розглянемо рішення для деяких реальних завдань.

Як сортувати в Excel від найменшого до найбільшого

Як показано в наведеному вище прикладі, щоб відсортувати числа від найбільшого до найменшого, скористайтеся однією з формул діапазону Excel із значенням аргументу порядку 0 або пропущеним (за замовчуванням).

Щоб число було відсортовано з іншими числами в порядку зростання, додайте 1 або будь-яке інше ненульове значення в необов’язковий третій аргумент. Наприклад, щоб оцінити час спринту учнів на дистанції 100 метрів, ви можете використати будь-яку з наступних формул:

= РЕЙТИНГ (B2, $ B $ 2: $ B $ 7,1)

=RANK.EQ(B2;$B$2:$B$7,1)

Зверніть увагу, що ми блокуємо діапазон в аргументі ref за допомогою абсолютних посилань на клітинки, щоб він не змінювався під час копіювання формули в стовпець.

У результаті найнижче значення (найшвидший час) займає перше місце, а найбільше значення (найповільніший час) отримує найнижчий ранг 6. Рівні часи (B2 і B7) отримують однаковий ранг.

Як унікально класифікувати дані в Excel

Як зазначалося вище, усі функції діапазону Excel повертають однаковий діапазон для елементів однакового значення. Якщо це не те, що ви хочете, скористайтеся однією з наведених нижче формул, щоб розв’язати ситуації на тай-брейку та надати кожному номеру унікальний ранг.

Унікальний рейтинг від найвищого до найнижчого

Щоб відсортувати результати студентів з математики лише в порядку спадання, використовуйте цю формулу:

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Унікальний рейтинг від найнижчого до найвищого

Щоб відсортувати результати забігу на 100 метрів у порядку зростання без дублікатів, використовуйте цю формулу:

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

Як працюють ці формули

Як ви могли помітити, єдина різниця між цими двома формулами полягає в аргументі порядку сортування функції RANK.EQ: він пропускається для сортування значень у порядку спадання та 1 для сортування їх у порядку зростання. В обох формулах саме функція COUNTIF з її розумним використанням відносних та абсолютних посилань на клітинки виконує свою роботу.

Коротше кажучи, використовуйте функцію COUNTIF , щоб дізнатися, скільки разів число, яке ранжується, зустрічається у клітинках вище, включаючи клітинку, що містить це число. У верхньому рядку, де ви вводите формулу, діапазон складається з однієї клітинки ($B$2:B2). Але оскільки блокується лише перше посилання ($B$2), останнє відносне посилання (B2) змінюється залежно від рядка, куди копіюється формула.

Тому для рядка 7 діапазон розширюється до $B$2:B7, а значення в B7 порівнюється з кожною з попередніх клітинок. Отже, для всіх перших входжень COUNTIF повертає 1; і відніміть 1 від кінця формули, щоб відновити вихідний діапазон.

Для другого входження COUNTIF повертає 2. Віднімаючи 1, ви збільшуєте діапазон на 1 пункт, таким чином уникаючи дублікатів. Якщо є 3 випадки однакового значення, COUNTIF() – 1 додасть 2 до вашого сортування тощо.

Обхідний шлях для розриву зв’язків Excel RANK

Інший спосіб однозначної класифікації чисел в Excel – це об'єднання двох функцій COUNTIF:

• Перша функція визначає, скільки значень більше або менше числа, яке потрібно відсортувати, залежно від того, сортуєте ви за спаданням чи зростанням відповідно.
• Друга функція (з «діапазоном розширення» $B $2:B2, як у попередньому прикладі) ви отримуєте кількість значень, що дорівнює числу.

Наприклад, щоб відсортувати числа лише від найвищого до найменшого, ви повинні використати цю формулу:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)

Як показано на знімку екрана нижче, тай-брейк успішно вирішено, і кожному студенту присвоюється унікальний ранг:

Класифікація в Excel на основі кількох критеріїв

У попередньому прикладі було продемонстровано два робочих рішення для ситуації з визначенням значення RANGE у Excel . Однак може здатися несправедливим, коли однакові числа ранжуються по-різному виключно на основі їхньої позиції у списку.

Щоб покращити свій рейтинг, ви можете додати ще один критерій, який слід враховувати у випадку рівності. У наш вибірковий набір даних давайте додамо загальні бали в стовпці C і обчислимо діапазон таким чином:

• Спочатку класифікуйте за математичним балом (основний критерій).
• Якщо є нічия, розірвати її за загальним балом (другорядний критерій)

Для цього ми використаємо звичайну формулу RANK/RANK.EQ, щоб знайти рейтинг, і функцію COUNTIF, щоб розірвати нічию:

=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)

Порівняно з попереднім прикладом ця формула рангу більш об’єктивна: Тімоті посідає 2-е місце, оскільки його загальний бал вищий, ніж у Юлії:

Як працює ця формула

Частина формули RANK очевидна, а функція COUNTIFS виконує наступне:

• Перша пара критерій_діапазон/критерій ($B$2:$B$7,$B2) підраховує випадки значення, яке ви ранжируєте. Зауважте, що ми фіксуємо діапазон за допомогою абсолютних посилань, але ми не блокуємо рядок умов ($B2), щоб формула перевіряла значення в кожному рядку окремо.
• Друга пара діапазон_критерію/критерій ($C$2:$C$7, ">" і $C2) визначає, скільки загальних балів перевищує загальний бал значення, яке ранжується.

Оскільки COUNTIFS працює з логікою І, тобто підраховує лише клітинки, які відповідають усім заданим умовам, він повертає 0 для Тімоті, оскільки жоден інший студент із таким же балом з математики не має вищого загального балу. Таким чином, ранг Тімоті, повернутий RANK.EQ, не змінюється.

Для Юлії функція COUNTIF повертає 1, оскільки учень з таким самим балом з математики має вищий загальний бал, тому його ранг збільшується на 1. Якби інший учень мав такий самий бал з математики та нижчий загальний бал, ніж Тімоті та Юлія, його ранг збільшувався б на 2 тощо.

Альтернативні рішення для сортування чисел за кількома критеріями

Замість функції RANK або RANK.EQ ви можете використовувати COUNTIF, щоб перевірити основні критерії, і COUNTIFS або SUMPRODUCT, щоб розв’язати тай-брейк:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1

=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1

Результат цих формул точно такий же, як показано вище.

Як розрахувати процентиль рангу в Excel

У статистиці процентиль - це значення, нижче якого опускається певний відсоток значень у даному наборі даних. Наприклад, якщо 70% студентів мають ваш іспитовий бал або нижчий, ваш процентиль дорівнює 70.

Щоб отримати процентний ранг у Excel, скористайтеся функцією RANK або RANK.EQ з аргументом ненульового порядку, щоб ранжувати числа від найнижчого до найвищого, а потім розділити ранг на число. Отже, загальна формула процентильного рангу Excel виглядає так:

RANK.EQ(верхня_комірка, ранг, 1) / COUNT(ранг)

Для розрахунку процентильного рангу студентів формула приймає такий вигляд:

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

Щоб результати відображалися правильно, переконайтеся, що в комірках формули встановлено відсотковий формат:

Ви також можете дізнатися: Як підсумовувати за категоріями в Excel

Як сортувати числа в несуміжних клітинках

У ситуаціях, коли вам потрібно сортувати несуміжні клітинки, вкажіть ці клітинки безпосередньо в аргументі ref вашої формули діапазону Excel як об'єднання посилань, обмежуючи посилання знаком долара ($). Наприклад:

=РАНГ(B2,($B$2,$B$4,$B$6))

Щоб уникнути помилок у некласифікованих клітинках, налаштуйте RANK у функції IFERROR ось так:

=ЯКЩОПОМИЛКА(РАНГ(B2,($B$2,$B$4,$B$6)),»»)

Зверніть увагу, що повторюваному номеру також призначається діапазон, хоча клітинка B5 не включена у формулу:

Якщо вам потрібно відсортувати кілька несуміжних клітинок, наведена вище формула може бути задовгою. У цьому випадку більш елегантним рішенням було б визначити іменований діапазон і посилатися на це ім’я у формулі:

=ЯКЩОПОМИЛКА(РАНГ(B2,діапазон),»»)

Як сортувати в Excel по групах

Під час роботи із записами, організованими в структуру даних певного типу, дані можуть належати до кількох груп, і ви можете відсортувати числа в кожній групі окремо. Функція RANK Excel не може вирішити цю проблему, тому скористаємося більш складною формулою SUMPRODUCT:

Сортувати за групою в порядку спадання:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Сортувати за групою в порядку зростання:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

Dónde:

• A2: A7 — це групи, призначені номерам.
• C2: C7 - номери для класифікації.

У цьому прикладі ми використовуємо першу формулу для сортування чисел у кожній групі від найбільшого до найменшого:

Як працює ця формула

В основному формула оцінює 2 умови:

• Спочатку перевірте групу (A2=$A$2:$A$7). Ця частина повертає масив TRUE і FALSE залежно від того, чи належить елемент діапазону до тієї ж групи, що й A2.
• По-друге, перевірте рахунок. Щоб відсортувати значення від найвищого до найменшого (за спаданням), використовуйте умову (C2 < $C$2:$C$11), яка повертає значення TRUE для клітинок, що більше або дорівнює C2, і FALSE в іншому випадку.

Оскільки в термінах Microsoft Excel TRUE = 1 та FALSE = 0, множення двох масивів призводить до масиву одиниць та нулів, де 1 повертається лише для рядків, які відповідають обом умовам. Функція SUMPRODUCT потім підсумовує елементи масиву одиниць та нулів, повертаючи 0 для найбільшого числа в кожній групі. І додає 1 до результату, щоб почати сортування з 1.

Формула, яка сортує числа за групами від найменшого до найбільшого (у порядку зростання), працює за тією ж логікою. Різниця полягає в тому, що SUMPRODUCT повертає 0 для найменшого числа в певній групі, оскільки жодне число в цій групі не відповідає другій умові (C2 > C2:C7). Знову ж таки, замініть нульовий діапазон першим діапазоном, додавши 1 до результату формули.

Замість SUMPRODUCT можна використовувати функцію SUM для підсумовування елементів масиву. Однак для цього знадобиться формула масиву, яку потрібно ввести за допомогою Ctrl+Shift+Enter . Наприклад:

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Як класифікувати додатні та від’ємні числа окремо

Якщо ваш список чисел містить як додатні, так і від’ємні значення, функція RANK у Excel миттєво ранжує їх усі. Але що, якщо ви хочете, щоб додатні та від’ємні числа класифікувалися окремо? З числами в клітинках від A2 до A10 використовуйте одну з наведених нижче формул, щоб отримати індивідуальний рейтинг для додатних і від’ємних значень:

Відсортуйте додатні числа за спаданням:

=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Відсортуйте додатні числа в порядку зростання:

=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)

Сортування від’ємних чисел за спаданням:

=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)

Відсортуйте від’ємні числа в порядку зростання:

=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)

Результати виглядатимуть приблизно так:

Як працюють ці формули

Для початку розглянемо формулу, яка сортує додатні числа в порядку спадання:

• У логічній перевірці функції IF, перевіряє, чи число більше нуля.
• Якщо число більше 0, функція COUNTIF повертає кількість значень, які перевищують число, яке сортується.

У цьому прикладі A2 містить друге за величиною додатне число, для якого COUNTIF повертає 1, тобто існує лише одне число, більше за нього. Щоб почати наше сортування з 1, а не з 0, ми додаємо 1 до результату формули, тож вона повертає сортування 2 для A2.

Якщо число більше за 0, формула повертає порожній рядок (""). Формула, яка сортує додатні числа у порядку зростання, працює дещо інакше: якщо число більше за 0, перша функція COUNTIF отримує загальну кількість додатних чисел у наборі даних, а друга функція COUNTIF знаходить, на скільки значень більше за це число.

Потім ви віднімаєте останнє від першого і отримуєте потрібний діапазон. У цьому прикладі є 5 позитивних значень, 1 з яких більше за A2. Потім відніміть 1 від 5, отримуючи таким чином ранг 4 для A2. Формули для класифікації від’ємних чисел базуються на схожій логіці.

Примітка: Усі наведені вище формули ігнорують нульові значення, оскільки 0 не належить ні до множини додатних чисел, ні до множини від’ємних чисел. Щоб включити нулі до ранжування, замініть >0 та <0 на >=0 та <=0 відповідно, де цього вимагає логіка формули. Наприклад, щоб ранжувати додатні числа та нулі від найбільшого до найменшого, використовуйте цю формулу:

=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Як сортувати дані в Excel, ігноруючи нульові значення

Як відомо, формула RANK в Excel обробляє всі числа: додатні, від’ємні та нулі. Але в деяких випадках нам потрібно сортувати лише комірки з даними, які ігнорують нульові значення. Для цього завдання можна знайти кілька можливих рішень, але формула RANK IF в Excel є найбільш універсальною.

Спадання чисел діапазону без урахування нуля:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))

Числа в діапазоні зростання без урахування нуля:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))

Де B2:B10 – це діапазон чисел, які потрібно відсортувати. Найкраще в цій формулі те, що вона чудово працює як для додатних, так і для від’ємних чисел, залишаючи нульові значення поза сортуванням:

Як працює ця формула

На перший погляд формула може здатися трохи складною. Якщо придивитися ближче, то логіка дуже проста. Ось як формула Excel RANK IF ранжирує числа від найбільшого до найменшого, ігноруючи нулі:

• Перший IF перевіряє, чи число дорівнює 0, і якщо так, повертає порожній рядок:

ЯКЩО ($B2 = 0, «»,…)

• Якщо число не дорівнює нулю, другий IF перевіряє, чи воно більше за 0, і якщо це так, звичайна функція RANK/RANK.EQ обчислює ранг:

IF ($B2 > 0, RANK ($B2, $B$2: $B$10),…)

• Якщо число менше 0, відрегулюйте сортування за нульовою кількістю. У цьому прикладі 4 додатних числа і 2 нулі. Отже, для найбільшого від’ємного числа в B10 функція діапазону в Excel поверне 7. Але ми опустили нулі, тому потрібно скоригувати діапазон на 2 пункти. Для цього з діапазону віднімаємо кількість нулів:

RANGE($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

Так, це так просто! Формула для сортування чисел від найменшого до найбільшого без урахування нулів працює подібним чином і може бути хорошою розумовою вправою для виведення її логіки.

Як розрахувати діапазон в Excel за абсолютною величиною

Під час роботи зі списком додатних і від’ємних значень може знадобитися відсортувати числа за їхніми абсолютними значеннями, ігноруючи знак. Це завдання можна виконати за допомогою однієї з наведених нижче формул, в основі якої лежить функція ABS, що повертає абсолютне значення числа:

Нижній діапазон ABS:

=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7) )) * (A$2:A$7<>»»))+1

Верхній діапазон ABS:

=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7) )) * (A$2:A$7<>»»))+1

У результаті від’ємні числа класифікуються як додатні:

Як отримати N більших або менших значень

Якщо ви хочете отримати дійсне число N найбільших або найменших значень замість їх ранжування, використовуйте функцію LARGE або SMALL відповідно. Наприклад, ми можемо отримати 3 кращі результати студентів за цією формулою:

=ВЕЛИКИЙ($B$2:$B$7, $D3)

Де B2:B7 – це список балів, а D3 – потрібний діапазон. Крім того, ви можете отримати імена студентів за допомогою формули INDEX STARTING (за умови, що в перших 3 балах немає дублікатів):

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

Подібним чином ви можете використовувати функцію SMALL, щоб отримати 3 нижні значення:

=МАЛИЙ($B$2:$B$7, $D3)

Дивіться також: Як поєднати Excel з Word: Імпорт даних з Excel до Word

Pensamientos finales

Ось як ранжувати за допомогою функції рангування в Excel. Дякуємо за читання та сподіваємося побачити вас у нашому блозі наступного тижня! Існує велика кількість підручників, щоб роз’яснити сумніви, пов’язані з комп’ютерною сферою. Повертаючись до теми, існує багато альтернативних способів обчислення діапазону в Excel, кожен зі своїми особливостями. Ви вирішуєте, як ви хочете працювати.