Загальна формула
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Резюме
Для динамічного сортування даних як з числами, так і з текстом в алфавітному порядку, ви можете використовувати формулу для створення числового рангу в допоміжному стовпці, а потім за допомогою INDEX та MATCH відображати значення на основі рангу. У наведеному прикладі формула в С5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
де "дані" - названий діапазон B5: B13.
Пояснення
Ця формула спочатку генерує значення рангу за допомогою виразу на основі COUNTIF:
=COUNTIF(data,"<="&B5)
що пояснюється тут більш докладно. Якщо дані містять усі текстові значення або всі числові значення, рейтинг буде правильним. Однак, якщо дані включають як текст, так і цифри, нам потрібно "перенести" ранг усіх текстових значень для врахування числових значень. Це робиться з другою частиною формули тут:
+(COUNT(data)*ISTEXT(B7))
Тут ми використовуємо функцію COUNT, щоб отримати підрахунок числових значень у даних, а потім помножимо результат на логічний результат ISTEXT, який перевіряє, чи є значення текстовим, і повертає або TRUE, або FALSE. Це фактично скасовує результат COUNT, коли ми працюємо з числом у поточному рядку.
Обробка дублікатів
Якщо дані містять дублікати, формулу можна змінити, як показано нижче, щоб присвоїти послідовний ранг значенням, які з’являються більше одного разу:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Ця версія коригує логіку початкової функції COUNTIF і додає ще COUNTIF із розширеним посиланням для збільшення дублікатів.
Відображення відсортованих значень
Для отримання та відображення значень, відсортованих в алфавітному порядку за допомогою обчисленого значення рангу, E5 містить наступну формулу INDEX та MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
де "дані" - іменований діапазон B5: B13, а "ранг" - іменований діапазон C5: C13.
Для отримання додаткової інформації про те, як працює ця формула, див. Приклад тут.
Робота з заготовками
Порожні комірки генерують нульовий ранг. Якщо припустити, що ви хочете ігнорувати порожні комірки, це працює нормально, оскільки формула INDEX і MATCH вище починається з 1. Однак у кінці відсортованих значень ви побачите # N / A помилок, по одній для кожної порожньої комірки. Найпростіший спосіб вирішити це - обернути формулу INDEX і MATCH у IFERROR так:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
