ДомойСтатьиExcelЯк проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

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

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Додайте стовпець з рангом за допомогою RANK.EQ

  1. Створіть поруч зі значеннями новий стовпець «Ранг».
  2. Клацніть клітинку для першого рангу (наприклад, C2).
  3. Введіть формулу: =RANK.EQ(B2, $B$2:$B$25, 0).
  4. Натисніть Enter, щоб отримати ранг для B2 відносно діапазону $B$2:$B$25.
  5. Протягніть маркер заповнення вниз, щоб застосувати формулу до всього стовпця.

Пояснення: number — це значення для ранжування, ref — діапазон порівняння, [order] визначає напрям (0 або пропущено — більше значення має вищий ранг; ненульове, напр. 1 — менші значення вищі). Знакова прив’язка $ фіксує діапазон під час копіювання формули.

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Розв’яжіть «нічиї»: унікальні місця через COUNTIFS

  1. У клітинці рангу (наприклад, C2) замініть формулу на: =RANK.EQ(B2, $B$2:$B$25, 0) + COUNTIFS($B$2:B2, B2) — 1.
  2. Натисніть Enter і протягніть формулу вниз по стовпцю.

Як це працює: розширюваний діапазон $B$2:B2 рахує повтори зверху вниз. Перша поява дубля зберігає свій ранг, друга отримує +1, третя — +2 тощо, тож усі місця стають унікальними без переміщення рядків.

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Тонше розрізнення однакових балів: другий показник

  1. Якщо маєте другий критерій (наприклад, середні дні до закриття угоди в стовпці D), введіть: =RANK.EQ(B2, $B$2:$B$25, 0) + (0.001 * RANK.EQ(D2, $D$2:$D$25, 1)).
  2. Натисніть Enter і скопіюйте формулу вниз.

Пояснення: основний ранг за B — у спадному порядку. Доданок — це ранг за D у зростанні (1 означає, що менше — краще). Множник 0.001 робить другорядний внесок малим, аби він не перекрив основний на масиві такого розміру; дробова частина розділяє учасників із однаковими основними балами.

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Ігноруйте пропуски й непридатні записи

  1. Щоб відловити помилки ранжування (порожньо, текст тощо), у клітинці рангу використайте: =IFERROR(RANK.EQ(B2,$B$2:$B$25,0), COUNTA($B$2:$B$25)).
  2. Підтвердіть Enter і протягніть вниз.

Числові значення отримають свої звичні ранги, а проблемні клітинки повернуть запасний номер, що дорівнює кількості непорожніх клітин у діапазоні — такий підхід утримує «сміття» поза осмисленою частиною рейтингу.

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Поради та типові ситуації

  • Класична RANK.EQ залишає «дірку» після нічиї: якщо двоє на другому місці, наступний отримає ранг 4. Якщо це незручно, можна використати RANK.AVG — вона присвоїть обом середній ранг (наприклад, по 2.5).
  • У старих книгах можете зустріти RANK — вона працює схоже до RANK.EQ, включно з обробкою нічиїх, але це суміснісна функція; для нових аркушів краще використовувати RANK.EQ.
  • Щоб не зрушувати групування або хронологію, завжди додавайте окремий стовпець «Ранг» замість сортування вихідних даних.

Підказка до Copilot (за бажанням)

Спробуйте звернутися до Copilot в Excel українською, щоб швидше скласти формулу: «Створи формулу, яка проставить ранги за діапазоном B2:B25 у спадному порядку, а однакові значення розрізнить за порядком зверху вниз».

Фото: MakeUseOf: Productivity

Джерела

- Advertisment -

Excel RANDARRAY: як швидко створювати випадкові списки з гнучкими параметрами

Інструкція допоможе зробити у Excel випадкові числа списком — від простого прикладу до гнучкої формули з керуванням довжиною та діапазоном значень. Працює в Excel для Microsoft 365 та інших версіях із підтримкою Spill-функцій.

VLOOKUP (ВПР) в англомовному Excel: покроковий гід, XLOOKUP і альтернативи

Як перекласти ВПР у VLOOKUP і навпаки, чим XLOOKUP краща, як уникати #N/A та що робити у старих версіях Excel — з прикладами і формулами.

Як підготувати дані в Excel, щоб Copilot давав кращі відповіді

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

Еще записей в тему?
Если честно, некоторые могут быть не свежие:)