Обща формула
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Обобщение
За да сортирате динамично данните както с числа, така и с текст в азбучен ред, можете да използвате формула, за да генерирате числов ранг в помощна колона, след което да използвате INDEX и MATCH, за да покажете стойности въз основа на ранга. В показания пример формулата в C5 е:
=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))
където "data" е наименованият диапазон B5: B13, а "rank" е наименуваният диапазон C5: C13.
За повече информация как работи тази формула вижте примера тук.
Справяне със заготовки
Празните клетки ще генерират нулев ранг. Ако приемем, че искате да игнорирате празни клетки, това работи добре, защото формулата INDEX и MATCH по-горе започва от 1. Въпреки това, ще видите # N / A грешки в края на сортираните стойности, по една за всяка празна клетка. Лесен начин да се справите с това е да увиете формулата INDEX и MATCH в IFERROR така:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
