Обща формула
=COUNTIF(range,"<="&A1)
Обобщение
За да сортирате динамично текстовите стойности по азбучен ред, можете да използвате формула, базирана на функцията COUNTIF. В показания пример формулата в C5 е:
=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 е разширяваща се справка.
