Obecný vzorec
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
souhrn
Chcete-li dynamicky řadit data s čísly i textem v abecedním pořadí, můžete pomocí vzorce vygenerovat číselnou hodnost ve sloupci pomocníka, poté použít INDEX a MATCH k zobrazení hodnot založených na hodnosti. V zobrazeném příkladu je vzorec v C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
kde „data“ je pojmenovaný rozsah B5: B13.
Vysvětlení
Tento vzorec nejprve vygeneruje hodnostní hodnotu pomocí výrazu založeného na COUNTIF:
=COUNTIF(data,"<="&B5)
který je zde vysvětlen podrobněji. Pokud data obsahují všechny textové hodnoty nebo všechny číselné hodnoty, bude pořadí správné. Pokud však data obsahují text i čísla, musíme „posunout“ pořadí všech textových hodnot tak, aby odpovídaly číselným hodnotám. To se děje s druhou částí vzorce zde:
+(COUNT(data)*ISTEXT(B7))
Tady použijeme funkci COUNT k získání počtu číselných hodnot v datech, poté výsledek vynásobíme logickým výsledkem ISTEXT, který testuje, zda je hodnota text a vrátí hodnotu TRUE nebo FALSE. Tím efektivně zrušíte výsledek COUNT, když pracujeme s číslem v aktuálním řádku.
Zpracování duplikátů
Pokud data obsahují duplikáty, lze vzorec změnit, jak je znázorněno níže, a přiřadit sekvenční pořadí hodnotám, které se objeví více než jednou:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Tato verze upravuje logiku počáteční funkce COUNTIF a přidává další COUNTIF s rozšiřujícím se odkazem na přírůstek duplikátů.
Zobrazit seřazené hodnoty
Chcete-li načíst a zobrazit hodnoty seřazené hodnoty v abecedním pořadí pomocí vypočítané hodnoty pořadí, obsahuje E5 následující vzorec INDEX a MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
kde „data“ je pojmenovaný rozsah B5: B13 a „rank“ je pojmenovaný rozsah C5: C13.
Další informace o tom, jak tento vzorec funguje, najdete v příkladu zde.
Nakládání s mezerami
Prázdné buňky vygenerují hodnost nula. Za předpokladu, že chcete ignorovat prázdné buňky, to funguje dobře, protože výše uvedený vzorec INDEX a MATCH začíná na 1. Na konci seřazených hodnot se však zobrazí chyby # N / A, pro každou prázdnou buňku jedna. Snadný způsob, jak to zvládnout, je zabalit vzorec INDEX a MATCH do IFERROR takto:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
