Vispārēja formula
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Kopsavilkums
Lai dinamiski kārtotu datus gan ar cipariem, gan tekstu alfabētiskā secībā, varat izmantot formulu, lai palīgkolonnā ģenerētu skaitlisko rangu, pēc tam izmantojiet INDEX un MATCH, lai parādītu vērtības, pamatojoties uz rangu. Parādītajā piemērā formula C5 ir:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
kur "dati" ir nosauktais diapazons B5: B13.
Paskaidrojums
Šī formula vispirms ģenerē ranga vērtību, izmantojot izteiksmi, kuras pamatā ir COUNTIF:
=COUNTIF(data,"<="&B5)
kas šeit sīkāk izskaidrots. Ja datos ir visas teksta vērtības vai visas skaitliskās vērtības, rangs būs pareizs. Tomēr, ja datos ir gan teksts, gan skaitļi, mums ir jāpārceļ visu tekstu vērtību rangs, lai ņemtu vērā skaitliskās vērtības. Tas tiek darīts ar formulas otro daļu šeit:
+(COUNT(data)*ISTEXT(B7))
Šeit mēs izmantojam funkciju COUNT, lai iegūtu skaitlisko vērtību skaitu, pēc tam rezultātu reizinot ar ISTEXT loģisko rezultātu, kas pārbauda, vai vērtība ir teksts un atgriež vai nu TRUE, vai FALSE. Tas faktiski atceļ COUNT rezultātu, kad mēs strādājam ar skaitli pašreizējā rindā.
Dublikātu apstrāde
Ja datos ir dublikāti, formulu var mainīt, kā parādīts zemāk, lai piešķirtu secīgu rangu vērtībām, kas parādās vairāk nekā vienu reizi:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Šī versija pielāgo sākotnējās COUNTIF funkcijas loģiku un pievieno vēl vienu COUNTIF ar paplašinošu atsauci uz pieauguma dublikātiem.
Parādīt sakārtotās vērtības
Lai izgūtu un parādītu vērtības sakārtotas vērtības alfabētiskā secībā, izmantojot aprēķināto rangu vērtību, E5 satur šādu INDEX un MATCH formulu:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
kur "dati" ir nosauktais diapazons B5: B13 un "rangs" ir nosauktais diapazons C5: C13.
Lai iegūtu vairāk informācijas par šīs formulas darbību, skatiet piemēru šeit.
Darbība ar sagatavēm
Tukšas šūnas radīs nulles pakāpi. Pieņemot, ka vēlaties ignorēt tukšās šūnas, tas darbojas labi, jo iepriekš minētās formulas INDEX un MATCH sākas ar 1. Tomēr sakārtoto vērtību beigās redzēsiet # N / A kļūdas, pa vienai katrai tukšai šūnai. Vienkāršs veids, kā to izdarīt, ir INDEX un MATCH formulas ietīšana IFERROR šādā veidā:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
