Загальна формула
=COUNTIF(range,"<="&A1)
Резюме
Щоб динамічно сортувати текстові значення в алфавітному порядку, ви можете використовувати формулу на основі функції COUNTIF. У наведеному прикладі формула в С5 має вигляд:
=COUNTIF(countries,"<="&B5)
де "країни" - це названий діапазон B4: B13
Пояснення
Ця формула використовує оператор "більше або дорівнює" з текстом, чого ви раніше не пробували. Коли Excel порівнює текст, він вирішує, яке значення "більше", ніж інші засновані правила, які відповідають схемі нумерації ASCII.
Усередині COUNTIF аргумент діапазону подається як названий діапазон "країни" (B4: B13), а критерії подаються як "менше або дорівнює" значенню в C5. У кожному рядку функція COUNTIFS повертає кількість значень, менших або рівних поточному значенню, що створює послідовний список чисел (тобто ранг) у допоміжному стовпці.
Перелік відсортованих значень
Допоміжний стовпець можна використовувати для отримання відсортованих значень за рангом. У E5 формула, яка використовується для отримання значень, така:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
Це формула INDEX і MATCH, яка використовує посилання, що розширюється, для генерації послідовних чисел, які подаються в MATCH як значення пошуку. MATCH з’ясовує, де в даних існує кожне число, а INDEX отримує значення в цій позиції. Дивіться цю сторінку для більш детального пояснення.
Обробка дублікатів
Якщо дані містять повторювані текстові значення, послідовність номерів сортування також міститиме дублікати, що спричинить проблеми, якщо ви намагаєтесь отримати значення за допомогою функції INDEX. Щоб вирішити цю проблему, ви можете використовувати варіант формули, який збільшує кількість повторень з другим COUNTIF:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Зверніть увагу, що логічний оператор у першій функції COUNTIF було налаштовано, а діапазон у другій функції COUNTIF є розширюваним посиланням.
