Formula umum
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Ringkasan
Untuk mengurutkan data secara dinamis dengan angka dan teks dalam urutan alfabet, Anda dapat menggunakan rumus untuk menghasilkan peringkat numerik di kolom pembantu, lalu gunakan INDEX dan MATCH untuk menampilkan nilai berdasarkan peringkat. Pada contoh yang diperlihatkan rumus di C5 adalah:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
di mana "data" adalah rentang bernama B5: B13.
Penjelasan
Rumus ini pertama kali menghasilkan nilai peringkat menggunakan ekspresi berdasarkan COUNTIF:
=COUNTIF(data,"<="&B5)
yang dijelaskan lebih detail di sini. Jika data berisi semua nilai teks, atau semua nilai numerik, peringkatnya akan benar. Namun, jika data menyertakan teks dan angka, kita perlu "menggeser" peringkat semua nilai teks untuk memperhitungkan nilai numerik. Ini dilakukan dengan bagian kedua dari rumus di sini:
+(COUNT(data)*ISTEXT(B7))
Di sini, kami menggunakan fungsi COUNT untuk mendapatkan hitungan nilai numerik dalam data, lalu mengalikan hasilnya dengan hasil logika ISTEXT, yang menguji apakah nilainya adalah teks dan mengembalikan TRUE atau FALSE. Ini secara efektif membatalkan hasil JUMLAH ketika kita bekerja dengan angka di baris saat ini.
Menangani duplikat
Jika data berisi duplikat, rumus dapat diubah seperti yang diperlihatkan 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 logika fungsi COUNTIF awal, dan menambahkan COUNTIF lainnya dengan referensi yang meluas ke duplikat kenaikan.
Menampilkan nilai yang diurutkan
Untuk mengambil dan menampilkan nilai yang diurutkan nilai dalam urutan abjad menggunakan nilai peringkat terhitung, E5 berisi rumus INDEX dan MATCH berikut:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
di mana "data" adalah rentang bernama B5: B13, dan "peringkat" adalah rentang bernama C5: C13.
Untuk informasi selengkapnya tentang cara kerja rumus ini, lihat contohnya di sini.
Berurusan dengan kekosongan
Sel kosong akan menghasilkan peringkat nol. Dengan asumsi Anda ingin mengabaikan sel kosong, ini berfungsi dengan baik karena rumus INDEX dan MATCH di atas dimulai pada 1. Namun, Anda akan melihat kesalahan # N / A di akhir nilai yang diurutkan, satu untuk setiap sel kosong. Cara mudah untuk menangani ini adalah dengan membungkus rumus INDEX dan MATCH dalam IFERROR seperti ini:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
