Общая формула
=COUNTIF(range,"<="&A1)
Резюме
Для динамической сортировки текстовых значений в алфавитном порядке можно использовать формулу, основанную на функции СЧЁТЕСЛИ. В показанном примере формула C5 имеет следующий вид:
=COUNTIF(countries,"<="&B5)
где "страны" - именованный диапазон B4: B13.
Объяснение
Эта формула использует оператор «больше или равно» с текстом, чего вы, возможно, не пробовали раньше. Когда Excel сравнивает текст, он решает, какое значение «больше», чем другие правила, соответствующие схеме нумерации ASCII.
Внутри COUNTIF аргумент диапазона предоставляется как именованный диапазон «стран» (B4: B13), а критерий - как «меньше или равно» значению в C5. В каждой строке СЧЁТЕСЛИМН возвращает количество значений, которые меньше или равны текущему значению, что создает последовательный список чисел (т. Е. Ранг) во вспомогательном столбце.
Вывод отсортированных значений
Вспомогательный столбец можно использовать для извлечения отсортированных значений по рангу. В E5 для получения значений используется следующая формула:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
Это формула ИНДЕКС и ПОИСКПОЗ, которая использует расширяющуюся ссылку для генерации последовательных чисел, которые передаются в ПОИСКПОЗ в качестве значений поиска. MATCH определяет, где каждое число существует в данных, а INDEX извлекает значение в этой позиции. См. Эту страницу для более подробного объяснения.
Обработка дубликатов
Если данные содержат повторяющиеся текстовые значения, последовательность номеров сортировки также будет содержать дубликаты, что вызовет проблемы, если вы попытаетесь получить значения с помощью функции ИНДЕКС. Чтобы обойти эту проблему, вы можете использовать вариант формулы, которая увеличивает дубликаты со вторым СЧЁТЕСЛИ:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Обратите внимание, что логический оператор в первой функции СЧЁТЕСЛИ был скорректирован, а диапазон во второй функции СЧЁТЕСЛИ является расширяющейся ссылкой.
