Генеричка формула
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Резиме
Да бисте динамички сортирали податке са бројевима и текстом по абецедном реду, можете користити формулу за генерисање нумеричког ранга у помоћној колони, а затим помоћу ИНДЕКС и МАТЦХ приказати вредности на основу ранга. У приказаном примеру формула у Ц5 је:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
где је „податак“ именовани опсег Б5: Б13.
Објашњење
Ова формула прво генерише вредност ранга користећи израз заснован на ЦОУНТИФ:
=COUNTIF(data,"<="&B5)
што је овде детаљније објашњено. Ако подаци садрже све текстуалне вредности или све нумеричке вредности, ранг ће бити тачан. Међутим, ако подаци укључују и текст и бројеве, морамо да „померимо“ ранг свих текстуалних вредности како би се узеле у обзир нумеричке вредности. Ово се ради са другим делом формуле овде:
+(COUNT(data)*ISTEXT(B7))
Овде користимо функцију ЦОУНТ да бисмо добили број нумеричких вредности у подацима, а затим помножимо резултат са логичким резултатом ИСТЕКСТ, који тестира да ли је вредност текст и враћа ТРУЕ или ФАЛСЕ. Ово ефективно поништава резултат ЦОУНТ када радимо са бројем у тренутном реду.
Руковање дупликатима
Ако подаци садрже дупликате, формула се може изменити како је приказано у наставку како би се вредностима које се појављују више пута доделио секвенцијални ранг:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Ова верзија прилагођава логику почетне функције ЦОУНТИФ и додаје још ЦОУНТИФ са проширујућом референцом за повећање дупликата.
Прикажи сортиране вредности
Да би дохватио и приказао сортиране вредности по абецедном реду помоћу израчунате вредности ранга, Е5 садржи следећу формулу ИНДЕКС и МАТЦХ:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
где је „податак“ именовани опсег Б5: Б13, а „ранк“ именовани опсег Ц5: Ц13.
За више информација о томе како функционише ова формула, погледајте пример овде.
Бављење празнинама
Празне ћелије ће генерисати ранг нула. Под претпоставком да желите занемарити празне ћелије, ово добро функционише, јер формула ИНДЕКС и МАТЦХ горе почиње на 1. Међутим, видећете # Н / А грешке на крају сортираних вредности, по једну за сваку празну ћелију. Једноставан начин да се ово реши је умотавање формуле ИНДЕКС и МАТЦХ у ИФЕРРОР овако:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
