Общая формула
=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 - второму значению и так далее. Это отлично работает, пока числовые значения уникальны. Однако для обработки числовых значений, содержащих дубликаты, нам нужно использовать функцию СЧЁТЕСЛИ, чтобы разорвать связи. Это делается путем добавления результата этого фрагмента к значению, возвращаемому RANK:
COUNTIF($C$5:C5,C5)-1
Обратите внимание, что диапазон вводится как смешанная ссылка, которая будет расширяться по мере копирования формулы вниз по таблице. Как написано, эта ссылка будет включать текущую строку, поэтому мы вычитаем 1, чтобы «обнулить» первое вхождение. Это означает, что выражение будет возвращать ноль для каждого числового значения, пока не встретится дубликат. Во втором экземпляре выражение вернет 1, в третьем экземпляре вернет 2 и так далее. Это эффективно разрывает связи и позволяет формуле генерировать последовательный список чисел без пробелов.
После того, как формула введена, данные можно отсортировать по вспомогательному столбцу. Его также можно получить с помощью INDEX, используя значения в вспомогательном столбце.
Примечание. Эта формула адаптирована из примера из замечательной книги Майка Гирвина «Control + Shift + Enter».
