Formula umum
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Ringkasan
Untuk secara dinamis mengurutkan dan mengekstrak nilai unik dari daftar data, Anda bisa menggunakan rumus array untuk menetapkan peringkat di kolom pembantu, lalu gunakan rumus INDEX dan MATCH yang dibuat khusus untuk mengekstrak nilai unik. Pada contoh yang ditampilkan, rumus untuk menetapkan rank di C5: C13 adalah:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
di mana "data" adalah rentang bernama B5: B13.
Catatan: ini adalah rumus array multisel, dimasukkan dengan control + shift + enter.
Penjelasan
Catatan: ide inti rumus ini diadaptasi dari contoh dalam buku bagus Mike Girvin, Control + Shift + Enter.
Contoh yang diperlihatkan menggunakan beberapa rumus, yang dijelaskan di bawah ini. Pada tingkat tinggi, fungsi MMULT digunakan untuk menghitung peringkat numerik di kolom pembantu (kolom C), dan peringkat ini kemudian digunakan oleh rumus INDEX dan MATCH di kolom G untuk mengekstrak nilai unik.
Memberi peringkat nilai data
Fungsi MMULT melakukan perkalian matriks dan digunakan untuk menetapkan peringkat numerik ke setiap nilai. Array pertama dibuat dengan ekspresi berikut:
--(data>TRANSPOSE(data))
Di sini, kami menggunakan fungsi TRANSPOSE untuk membuat larik data horizontal , dan semua nilai dibandingkan satu sama lain. Intinya, setiap nilai dibandingkan dengan setiap nilai lainnya untuk menjawab pertanyaan "apakah nilai ini lebih besar dari setiap nilai lainnya". Ini menghasilkan larik dua dimensi, 9 kolom x 9 baris, diisi dengan nilai TRUE dan FALSE. Negatif ganda (-) digunakan untuk memaksa nilai TRUE FALSE menjadi 1 dan nol. Anda dapat memvisualisasikan larik yang dihasilkan seperti ini:
Matriks 1 dan nol di atas menjadi larik1 di dalam fungsi MMULT. Array2 dibuat dengan ekspresi ini:
ROW(data)^0
Di sini, setiap nomor baris dalam "data" dinaikkan ke pangkat nol untuk membuat larik satu dimensi, 1 kolom x 9 baris, diisi dengan angka 1. MMULT kemudian mengembalikan produk matriks dari dua larik, yang menjadi nilai-nilai yang terlihat di kolom peringkat.
Kami mendapatkan kembali semua 9 peringkat pada saat yang sama dalam sebuah larik, jadi kami perlu memasukkan hasilnya ke dalam sel yang berbeda sekaligus. Jika tidak, setiap sel hanya akan menampilkan nilai peringkat pertama dalam larik yang dikembalikan.
Catatan: ini adalah rumus larik multisel, dimasukkan dengan control + shift + enter, dalam rentang C5: C13.
Menangani sel kosong
Sel kosong ditangani dengan bagian rumus peringkat ini:
=IF(data="",ROWS(data)
Di sini, sebelum kami menjalankan MMULT, kami memeriksa apakah sel saat ini di "data" kosong. Jika demikian, kami menetapkan nilai peringkat yang sama dengan jumlah baris dalam data. Ini dilakukan untuk memaksa sel kosong ke bagian bawah daftar, di mana mereka dapat dengan mudah dikecualikan nanti saat nilai unik diekstraksi (dijelaskan di bawah).
Menghitung nilai unik
Untuk menghitung nilai unik dalam data, rumus dalam E5 adalah:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Karena rumus peringkat di atas memberikan peringkat numerik untuk setiap nilai, kita dapat menggunakan fungsi FREQUENCY dengan SUM untuk menghitung nilai unik. Rumus ini dijelaskan secara rinci di sini. Kami kemudian mengurangi 1 dari hasil jika ada sel kosong dalam data:
-(blank>0)
di mana "kosong" adalah rentang bernama E8, dan berisi rumus ini:
=COUNTBLANK(data)
Pada dasarnya, kami mengurangi hitungan unik satu per satu jika ada sel kosong dalam data, karena kami tidak menyertakannya dalam hasil. Hitungan unik di sel E5 diberi nama "unik" (untuk hitungan unik), dan digunakan oleh rumus INDEX dan MATCH untuk memfilter sel kosong (dijelaskan di bawah).
Mengekstrak nilai unik
Untuk mengekstrak nilai unik, G5 berisi rumus berikut, disalin:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Sebelum kita menjalankan rumus INDEX dan MATCH, pertama-tama kita periksa apakah jumlah baris saat ini di area ekstraksi lebih besar dari jumlah unik kisaran bernama "unik" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Jika demikian, kita telah selesai mengekstrak nilai unik dan mengembalikan string kosong (""). Jika tidak, kami menjalankan rumus ekstraksi:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Perhatikan ada dua fungsi MATCH di sini, satu di dalam yang lain. MATCH bagian dalam menggunakan rentang yang diperluas untuk larik dan rentang bernama "data" untuk nilai pencarian:
MATCH(data,$G$4:G4,0)
Perhatikan rentang perluasan dimulai dari "baris di atas", baris 4 dalam contoh. Hasil dari MATCH bagian dalam adalah larik yang, untuk setiap nilai dalam data, berisi posisi numerik (nilai telah diekstraksi) atau kesalahan # N / A (nilai belum diekstraksi). Kami kemudian menggunakan IF dan ISNA untuk memfilter hasil ini, dan mengembalikan nilai peringkat untuk semua nilai di "data" yang belum diekstrak:
IF(ISNA(results),rank))
Operasi ini menghasilkan sebuah larik, yang dimasukkan ke dalam fungsi MIN untuk mendapatkan "nilai peringkat minimum" untuk nilai data yang belum diekstraksi. Fungsi MIN mengembalikan nilai ini ke MATCH luar sebagai nilai pencarian dan rentang bernama "rank" sebagai array:
MATCH(min_not_extracted,rank)),rank,0)
Akhirnya, MATCH mengembalikan posisi nilai peringkat terendah ke INDEX sebagai nomor baris, dan INDEX mengembalikan nilai data di baris saat ini dari rentang ekstraksi.
