Загальна формула
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Резюме
Для динамічного сортування даних, що містять лише числові значення, ви можете використовувати допоміжний стовпець та формулу, створену за допомогою функцій RANK та COUNTIF. У наведеному прикладі формула в D5 має вигляд:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
де "продаж" - названий діапазон C5: C11.
Пояснення
Примітка: ця формула призначена для формули, яка може витягувати та відображати дані за допомогою заздалегідь визначеного порядку сортування у допоміжному стовпці. Один приклад тут.
Ядром цієї формули є функція RANK, яка використовується для формування рангу цінності продажів, де найбільше число посідає 1 місце:
=RANK(C5,sales)
Тут RANK для зручності використовує названий асортимент "продаж" (C5: C11). За замовчуванням RANK призначає 1 найвищому значенню, 2 другому найвищому значенню тощо. Це чудово працює, якщо числові значення є унікальними. Однак для обробки числових значень, що містять дублікати, нам потрібно використовувати функцію COUNTIF для розриву зв’язків. Це робиться шляхом додавання результату цього фрагмента до значення, яке повертає RANK:
COUNTIF($C$5:C5,C5)-1
Зверніть увагу, що діапазон вводиться як змішане посилання, яке буде розширюватися під час копіювання формули в таблицю. Як написано, це посилання буде включати поточний рядок, тому ми віднімаємо 1, щоб "обнулити" перший вступ. Це означає, що вираз повертатиме нуль для кожного числового значення, поки не буде виявлено дублікат. У другому випадку вираз поверне 1, у третьому - 2, і так далі. Це ефективно розриває зв'язки і дозволяє формулі формувати послідовний список чисел без пропусків.
Коли формула встановлена, дані можна сортувати за допоміжним стовпцем. Його також можна отримати за допомогою INDEX, використовуючи значення в допоміжному стовпці.
Примітка: Ця формула адаптована до прикладу у чудовій книзі "Control + Shift + Enter" Майка Гірвіна.
