Generička formula
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Sažetak
Za dinamičko sortiranje podataka s brojevima i tekstom po abecednom redu možete upotrijebiti formulu za generiranje numeričkog ranga u pomoćnom stupcu, a zatim upotrijebiti INDEX i MATCH za prikaz vrijednosti na temelju ranga. U prikazanom primjeru formula u C5 je:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
gdje je "podatak" imenovani raspon B5: B13.
Obrazloženje
Ova formula prvo generira vrijednost ranga pomoću izraza temeljenog na COUNTIF:
=COUNTIF(data,"<="&B5)
što je ovdje detaljnije objašnjeno. Ako podaci sadrže sve tekstualne vrijednosti ili sve numeričke vrijednosti, rang će biti točan. Međutim, ako podaci uključuju i tekst i brojeve, moramo "pomaknuti" rang svih tekstualnih vrijednosti kako bi se uzele u obzir numeričke vrijednosti. To je učinjeno s drugim dijelom formule ovdje:
+(COUNT(data)*ISTEXT(B7))
Ovdje koristimo funkciju COUNT za dobivanje broja numeričkih vrijednosti u podacima, a zatim pomnožimo rezultat s logičkim rezultatom ISTEXT, koji testira je li vrijednost tekst i vraća li TRUE ili FALSE. Ovo učinkovito poništava rezultat COUNT kada radimo s brojem u trenutnom retku.
Rukovanje duplikatima
Ako podaci sadrže duplikate, formula se može izmijeniti kako je prikazano u nastavku kako bi se dodijelio sekvencijalni rang vrijednostima koje se pojavljuju više puta:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Ova verzija prilagođava logiku početne funkcije COUNTIF i dodaje još COUNTIF s proširivanjem reference za povećanje duplikata.
Prikaži sortirane vrijednosti
Da bi dohvatio i prikazao sortirane vrijednosti po abecednom redu pomoću izračunate vrijednosti ranga, E5 sadrži sljedeću formulu INDEX i MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
gdje je "podatak" imenovani raspon B5: B13, a "rang" imenovani raspon C5: C13.
Za više informacija o načinu funkcioniranja ove formule pogledajte primjer ovdje.
Bavljenje prazninama
Prazne ćelije generirat će rang nula. Pod pretpostavkom da želite zanemariti prazne stanice, ovo dobro funkcionira jer gornja formula INDEX i MATCH započinje na 1. Međutim, vidjet ćete # N / A pogreške na kraju sortiranih vrijednosti, po jednu za svaku praznu ćeliju. Jednostavan način da se to riješi je umotavanje formule INDEX i MATCH u IFERROR ovako:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
