Formula generik
=COUNTIF(range,"<="&A1)
Ringkasan
Untuk menyusun nilai teks secara dinamik mengikut urutan abjad, anda boleh menggunakan formula berdasarkan fungsi COUNTIF. Dalam contoh yang ditunjukkan, formula dalam C5 adalah:
=COUNTIF(countries,"<="&B5)
di mana "negara" adalah julat bernama B4: B13
Penjelasan
Formula ini menggunakan operator "lebih besar daripada atau sama dengan" dengan teks, sesuatu yang mungkin belum pernah anda cuba sebelumnya. Apabila Excel membandingkan teks, ia memutuskan nilai mana yang "lebih besar" daripada peraturan berdasarkan lain yang mengikuti skema penomboran ASCII.
Di dalam COUNTIF, argumen julat diberikan sebagai julat bernama "negara" (B4: B13), dan kriteria diberikan sebagai "kurang dari atau sama dengan" nilai di C5. Dalam setiap baris, COUNTIFS mengembalikan jumlah nilai yang kurang dari atau sama dengan nilai semasa, yang membuat senarai nombor berurutan (iaitu peringkat) di lajur penolong.
Menyenaraikan nilai yang disusun
Lajur pembantu boleh digunakan untuk mendapatkan nilai yang disusun mengikut pangkat. Dalam E5, formula yang digunakan untuk mendapatkan nilai adalah:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
Ini adalah formula INDEX dan MATCH yang menggunakan rujukan yang berkembang untuk menghasilkan nombor berurutan, yang dimasukkan ke dalam MATCH sebagai nilai pencarian. PERTANDINGAN mengetahui di mana setiap nombor ada dalam data, dan INDEX mengambil nilai pada kedudukan tersebut. Lihat halaman ini untuk penjelasan yang lebih terperinci.
Mengendalikan pendua
Sekiranya data mengandungi nilai teks pendua, urutan nombor urutan juga akan mengandungi pendua, yang akan menimbulkan masalah jika anda berusaha mendapatkan nilai dengan fungsi INDEX. Untuk mengatasi masalah ini, anda boleh menggunakan variasi formula yang menambah pendua dengan COUNTIF kedua:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Perhatikan pengendali logik pada fungsi COUNTIF pertama telah disesuaikan, dan rentang fungsi COUNTIF kedua adalah rujukan yang berkembang.
