Všeobecný vzorec
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Zhrnutie
Ak chcete dynamicky zoradiť údaje s číslami aj textom v abecednom poradí, môžete pomocou vzorca vygenerovať číselné poradie v pomocnom stĺpci a potom pomocou INDEXU a ZHODY zobraziť hodnoty na základe poradia. V zobrazenom príklade je vzorec v C5:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
kde „údaj“ je pomenovaný rozsah B5: B13.
Vysvetlenie
Tento vzorec najskôr vygeneruje hodnotu poradia pomocou výrazu založeného na COUNTIF:
=COUNTIF(data,"<="&B5)
ktorý je tu podrobnejšie vysvetlený. Ak údaje obsahujú všetky textové hodnoty alebo všetky číselné hodnoty, poradie bude správne. Ak však údaje zahŕňajú text aj čísla, musíme „posunúť“ poradie všetkých textových hodnôt tak, aby zodpovedali číselným hodnotám. To sa deje s druhou časťou vzorca tu:
+(COUNT(data)*ISTEXT(B7))
Tu použijeme funkciu COUNT na získanie počtu číselných hodnôt v dátach, potom výsledok vynásobíme logickým výsledkom ISTEXTu, ktorý otestuje, či je hodnota textová a vráti buď TRUE, alebo FALSE. Týmto efektívne zrušíte výsledok COUNT, keď pracujeme s číslom v aktuálnom riadku.
Spracovanie duplikátov
Ak údaje obsahujú duplikáty, vzorec je možné zmeniť, ako je uvedené nižšie, aby ste hodnotám, ktoré sa objavia viackrát, priradili postupné poradie:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Táto verzia upravuje logiku počiatočnej funkcie COUNTIF a pridáva ďalší COUNTIF s rozširujúcim sa odkazom na prírastok duplikátov.
Zobraziť zoradené hodnoty
Na získanie a zobrazenie hodnôt zoradených hodnôt v abecednom poradí pomocou vypočítanej hodnoty poradia obsahuje E5 nasledujúci index INDEX a MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
kde „údaj“ je pomenovaný rozsah B5: B13 a „poradie“ je pomenovaný rozsah C5: C13.
Ďalšie informácie o fungovaní tohto vzorca nájdete v príklade tu.
Zaoberanie sa polotovarmi
Prázdne bunky vygenerujú poradie nula. Za predpokladu, že chcete ignorovať prázdne bunky, funguje to dobre, pretože vyššie uvedený vzorec INDEX a MATCH začína číslom 1. Na konci zoradených hodnôt však uvidíte chyby # N / A, pre každú prázdnu bunku jednu. Jednoduchým spôsobom, ako to vyriešiť, je zabaliť vzorec INDEX a MATCH do programu IFERROR takto:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
