Formula umum
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Ringkasan
Untuk secara dinamis mengurutkan data yang berisi hanya nilai numerik, Anda bisa menggunakan kolom pembantu dan rumus yang dibuat dengan fungsi RANK dan COUNTIF. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
di mana "penjualan" adalah rentang bernama C5: C11.
Penjelasan
Catatan: rumus ini adalah penyetelan untuk rumus yang bisa mengekstrak dan menampilkan data menggunakan urutan yang sudah ditentukan sebelumnya di kolom pembantu. Salah satu contohnya di sini.
Inti dari rumus ini adalah fungsi RANK, yang digunakan untuk menghasilkan peringkat nilai penjualan, di mana angka tertinggi diberi peringkat # 1:
=RANK(C5,sales)
Di sini, RANK menggunakan rentang bernama "penjualan" (C5: C11) untuk kenyamanan. Secara default, RANK akan menetapkan 1 ke nilai tertinggi, 2 ke nilai tertinggi kedua, dan seterusnya. Ini berfungsi dengan sempurna selama nilai numerik unik. Namun, untuk menangani nilai numerik yang berisi duplikat, kita perlu menggunakan fungsi COUNTIF untuk memutuskan hubungan. Ini dilakukan dengan menambahkan hasil cuplikan ini ke nilai yang dikembalikan oleh RANK:
COUNTIF($C$5:C5,C5)-1
Perhatikan kisaran dimasukkan sebagai referensi campuran yang akan meluas saat rumus disalin ke bawah tabel. Seperti yang tertulis, referensi ini akan menyertakan baris saat ini, jadi kita kurangi 1 untuk "membolos" kemunculan pertama. Ini berarti ekspresi akan mengembalikan nol untuk setiap nilai numerik hingga ditemukan duplikat. Pada contoh kedua, ekspresi akan mengembalikan 1, pada contoh ketiga, itu akan mengembalikan 2, dan seterusnya. Ini secara efektif memutuskan hubungan, dan memungkinkan rumus untuk menghasilkan daftar nomor berurutan tanpa celah.
Setelah rumus tersedia, data dapat diurutkan berdasarkan kolom pembantu. Itu juga bisa diambil dengan INDEX menggunakan nilai di kolom helper.
Catatan: Rumus ini diadaptasi dari contoh di buku luar biasa Control + Shift + Enter, oleh Mike Girvin.
