Formula generik
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Ringkasan
Untuk menyusun data secara dinamis yang hanya mengandungi nilai angka, anda boleh menggunakan kolum pembantu dan formula yang dibuat dengan fungsi RANK dan COUNTIF. Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
di mana "penjualan" adalah julat bernama C5: C11.
Penjelasan
Catatan: formula ini adalah persediaan untuk formula yang dapat mengekstrak dan memaparkan data menggunakan urutan urutan yang telah ditentukan dalam kolom penolong. Satu contoh di sini.
Inti formula ini adalah fungsi RANK, yang digunakan untuk menghasilkan peringkat nilai penjualan, di mana jumlah tertinggi berada di kedudukan # 1:
=RANK(C5,sales)
Di sini, RANK menggunakan julat bernama "penjualan" (C5: C11) untuk kemudahan. Secara lalai, RANK akan menetapkan 1 ke nilai tertinggi, 2 ke nilai tertinggi kedua, dan seterusnya. Ini berfungsi dengan sempurna selagi nilai berangka unik. Namun, untuk menangani nilai berangka yang mengandungi pendua, kita perlu menggunakan fungsi COUNTIF untuk memutuskan hubungan. Ini dilakukan dengan menambahkan hasil coretan ini ke nilai yang dikembalikan oleh RANK:
COUNTIF($C$5:C5,C5)-1
Perhatikan julat dimasukkan sebagai rujukan campuran yang akan berkembang ketika formula disalin di bawah jadual. Seperti yang ditulis, rujukan ini akan merangkumi baris semasa, jadi kami tolak 1 hingga "nol keluar" kejadian pertama. Ini bermaksud ungkapan akan mengembalikan sifar untuk setiap nilai angka sehingga pendua ditemui. Pada contoh kedua, ungkapan akan kembali 1, pada contoh ketiga, ia akan mengembalikan 2, dan seterusnya. Ini secara efektif memutuskan hubungan, dan membolehkan formula menghasilkan senarai nombor yang berurutan tanpa jurang.
Setelah formula di tempat, data dapat disusun mengikut kolom penolong. Ia juga boleh diambil dengan INDEX menggunakan nilai-nilai di ruang penolong.
Catatan: Formula ini diadaptasi dari contoh dalam buku yang sangat baik Control + Shift + Enter, oleh Mike Girvin.
