Общая формула
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Резюме
Для динамической сортировки данных по числам и тексту в алфавитном порядке вы можете использовать формулу для создания числового ранга во вспомогательном столбце, а затем использовать ИНДЕКС и ПОИСКПОЗ для отображения значений на основе ранга. В показанном примере формула в C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
где «данные» - именованный диапазон B5: B13.
Объяснение
Эта формула сначала генерирует значение ранга, используя выражение, основанное на СЧЁТЕСЛИ:
=COUNTIF(data,"<="&B5)
что более подробно объясняется здесь. Если данные содержат все текстовые значения или все числовые значения, ранг будет правильным. Однако, если данные включают и текст, и числа, нам нужно «сместить» ранг всех текстовых значений, чтобы учесть числовые значения. Это делается с помощью второй части формулы здесь:
+(COUNT(data)*ISTEXT(B7))
Здесь мы используем функцию COUNT, чтобы получить количество числовых значений в данных, затем умножаем результат на логический результат ISTEXT, который проверяет, является ли значение текстом, и возвращает TRUE или FALSE. Это эффективно отменяет результат COUNT, когда мы работаем с числом в текущей строке.
Обработка дубликатов
Если данные содержат дубликаты, формулу можно изменить, как показано ниже, чтобы присвоить последовательный ранг значениям, которые появляются более одного раза:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Эта версия регулирует логику начальной функции СЧЁТЕСЛИ и добавляет еще СЧЁТЕСЛИ с расширяющейся ссылкой для увеличения дубликатов.
Отображение отсортированных значений
Для извлечения и отображения значений отсортированных значений в алфавитном порядке с использованием вычисленного значения ранга E5 содержит следующие формулы ИНДЕКС и ПОИСКПОЗ:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
где «данные» - это именованный диапазон B5: B13, а «ранг» - именованный диапазон C5: C13.
Для получения дополнительной информации о том, как работает эта формула, см. Пример здесь.
Работа с пробелами
Пустые ячейки будут генерировать нулевой ранг. Предполагая, что вы хотите игнорировать пустые ячейки, это работает нормально, потому что приведенная выше формула ИНДЕКС и ПОИСКПОЗ начинается с 1. Однако вы увидите ошибки # Н / Д в конце отсортированных значений, по одной для каждой пустой ячейки. Простой способ справиться с этим - заключить формулы ИНДЕКС и ПОИСКПОЗ в ЕСЛИОШИБКА следующим образом:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
