Formula generik
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Ringkasan
Untuk menyusun data secara dinamik dengan kedua-dua nombor dan teks mengikut urutan abjad, anda boleh menggunakan formula untuk menghasilkan kedudukan angka dalam lajur pembantu, kemudian gunakan INDEX dan MATCH untuk memaparkan nilai berdasarkan peringkat. Dalam contoh yang ditunjukkan formula di C5 adalah:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
di mana "data" adalah julat bernama B5: B13.
Penjelasan
Formula ini mula-mula menghasilkan nilai peringkat menggunakan ungkapan berdasarkan COUNTIF:
=COUNTIF(data,"<="&B5)
yang dijelaskan dengan lebih terperinci di sini. Sekiranya data tersebut mengandungi semua nilai teks, atau semua nilai angka, peringkatnya akan betul. Namun, jika data tersebut merangkumi teks dan angka, kita perlu "mengalihkan" peringkat semua nilai teks untuk memperhitungkan nilai angka. Ini dilakukan dengan bahagian kedua formula di sini:
+(COUNT(data)*ISTEXT(B7))
Di sini, kami menggunakan fungsi COUNT untuk mendapatkan jumlah nilai numerik dalam data, kemudian mengalikan hasilnya dengan hasil logik ISTEXT, yang menguji apakah nilai itu teks dan mengembalikan sama ada BENAR atau SALAH. Ini dengan berkesan membatalkan COUNT hasil ketika kita bekerja dengan nombor di baris sekarang.
Mengendalikan pendua
Sekiranya data mengandungi pendua, rumus dapat diubah seperti yang ditunjukkan di bawah ini untuk menetapkan peringkat berurutan ke nilai yang muncul lebih dari sekali:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Versi ini menyesuaikan logik fungsi COUNTIF awal, dan menambahkan COUNTIF lain dengan rujukan yang berkembang untuk peningkatan pendua.
Paparkan nilai yang disusun
Untuk mendapatkan dan memaparkan nilai yang disusun mengikut urutan abjad menggunakan nilai peringkat yang dikira, E5 mengandungi formula INDEX dan MATCH berikut:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
di mana "data" adalah julat bernama B5: B13, dan "peringkat" adalah julat bernama C5: C13.
Untuk maklumat lebih lanjut mengenai bagaimana formula ini berfungsi, lihat contohnya di sini.
Berurusan dengan kosong
Sel kosong akan menghasilkan kedudukan sifar. Dengan andaian anda mahu mengabaikan sel kosong, ini berfungsi dengan baik kerana formula INDEX dan MATCH di atas bermula pada 1. Walau bagaimanapun, anda akan melihat ralat # N / A pada akhir nilai yang disusun, satu untuk setiap sel kosong. Cara mudah untuk mengatasi ini adalah dengan membungkus formula INDEX dan MATCH di IFERROR seperti ini:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
