Formula generik
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Ringkasan
Untuk menyusun dan mengekstrak nilai unik dari senarai data secara dinamik, anda boleh menggunakan formula larik untuk menentukan kedudukan dalam kolom penolong, kemudian menggunakan formula INDEX dan MATCH yang dibina khas untuk mengekstrak nilai unik. Dalam contoh yang ditunjukkan, formula untuk menentukan kedudukan dalam C5: C13 adalah:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
di mana "data" adalah julat bernama B5: B13.
Catatan: ini adalah formula pelbagai sel, dimasukkan dengan kawalan + shift + enter.
Penjelasan
Catatan: idea teras formula ini diadaptasi dari contoh dalam buku Mike Girvin yang sangat baik iaitu Control + Shift + Enter.
Contoh yang ditunjukkan menggunakan beberapa formula, yang dijelaskan di bawah. Pada tahap tinggi, fungsi MMULT digunakan untuk menghitung peringkat angka dalam kolom penolong (lajur C), dan peringkat ini kemudian digunakan oleh formula INDEX dan MATCH di lajur G untuk mengekstrak nilai unik.
Nilai data kedudukan
Fungsi MMULT melakukan pendaraban matriks dan digunakan untuk menetapkan peringkat angka untuk setiap nilai. Susunan pertama dibuat dengan ungkapan berikut:
--(data>TRANSPOSE(data))
Di sini, kami menggunakan fungsi TRANSPOSE untuk membuat susunan data mendatar , dan semua nilai dibandingkan antara satu sama lain. Pada hakikatnya, setiap nilai dibandingkan dengan setiap nilai lain untuk menjawab soalan "adakah nilai ini lebih besar daripada setiap nilai lain". Ini menghasilkan susunan dua dimensi, 9 lajur x 9 baris, diisi dengan nilai BENAR dan SALAH. Negatif berganda (-) digunakan untuk memaksakan nilai BENAR PALSU kepada 1 dan sifar. Anda dapat menggambarkan susunan yang dihasilkan seperti ini:
Matriks 1s dan sifar di atas menjadi array1 di dalam fungsi MMULT. Array2 dibuat dengan ungkapan ini:
ROW(data)^0
Di sini, setiap nombor baris dalam "data" dinaikkan menjadi kekuatan sifar untuk membuat array satu dimensi, 1 baris x 9 baris, diisi dengan nombor 1. MMULT kemudian mengembalikan produk matriks dari dua tatasusunan, yang menjadi nilai yang dilihat pada lajur peringkat.
Kami memperoleh kembali semua 9 kedudukan pada masa yang sama dalam satu array, jadi kami perlu memasukkan hasilnya ke dalam sel yang berbeza sekaligus. Jika tidak, setiap sel hanya akan menunjukkan nilai peringkat pertama dalam array yang dikembalikan.
Catatan: ini adalah formula array pelbagai sel, dimasukkan dengan kawalan + shift + enter, dalam julat C5: C13.
Mengendalikan sel kosong
Sel kosong dikendalikan dengan bahagian formula pemeringkatan ini:
=IF(data="",ROWS(data)
Di sini, sebelum kita menjalankan MMULT, kita periksa sama ada sel dalam "data" kosong. Sekiranya demikian, kami memberikan nilai peringkat yang sama dengan jumlah baris dalam data. Ini dilakukan untuk memaksa sel kosong ke bahagian bawah senarai, di mana sel itu dapat dikecualikan dengan mudah kemudian kerana nilai unik diekstrak (dijelaskan di bawah).
Mengira nilai unik
Untuk menghitung nilai unik dalam data, rumus dalam E5 adalah:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Oleh kerana formula peringkat di atas memberikan peringkat angka untuk setiap nilai, kita dapat menggunakan fungsi FREQUENCY dengan SUM untuk menghitung nilai unik. Formula ini dijelaskan secara terperinci di sini. Kami kemudian mengurangkan 1 dari hasilnya jika terdapat sel kosong dalam data:
-(blank>0)
di mana "kosong" adalah julat bernama E8, dan mengandungi formula ini:
=COUNTBLANK(data)
Pada dasarnya, kami mengurangkan jumlah unik satu demi satu jika terdapat sel kosong dalam data, kerana kami tidak memasukkannya dalam hasil. Kiraan unik dalam sel E5 dinamakan "unik" (untuk kiraan unik), dan digunakan oleh formula INDEX dan MATCH untuk menyaring sel kosong (dijelaskan di bawah).
Mengekstrak nilai unik
Untuk mengekstrak nilai unik, G5 mengandungi formula berikut, disalin ke bawah:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Sebelum kita menjalankan formula INDEX dan MATCH, kita terlebih dahulu memeriksa sama ada jumlah baris semasa di kawasan pengekstrakan lebih besar daripada kiraan unik julat bernama "unik" (E5)
=IF(ROWS($G$5:G5)>unique,"",
Jika demikian, kami selesai mengekstrak nilai unik dan kami mengembalikan rentetan kosong (""). Sekiranya tidak, kami menjalankan formula pengekstrakan:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Perhatikan terdapat dua fungsi PERTANDINGAN di sini, satu di antara yang lain. MATCH dalaman menggunakan julat pengembangan untuk array dan julat bernama "data" untuk nilai pencarian:
MATCH(data,$G$4:G4,0)
Perhatikan julat pengembangan bermula pada "baris di atas", baris 4 dalam contoh. Hasil dari MATCH dalaman adalah array yang, untuk setiap nilai dalam data, mengandungi kedudukan angka (nilai sudah diekstrak) atau kesalahan # N / A (nilai belum diekstrak). Kami kemudian menggunakan IF dan ISNA untuk menyaring hasil ini, dan mengembalikan nilai peringkat untuk semua nilai dalam "data" yang belum diekstrak:
IF(ISNA(results),rank))
Operasi ini menghasilkan array, yang dimasukkan ke dalam fungsi MIN untuk mendapatkan "nilai peringkat minimum" untuk nilai data yang belum diekstrak. Fungsi MIN mengembalikan nilai ini ke MATCH luar sebagai nilai pencarian dan julat bernama "rank" sebagai array:
MATCH(min_not_extracted,rank)),rank,0)
Akhirnya, MATCH mengembalikan kedudukan nilai peringkat terendah ke INDEX sebagai nombor baris, dan INDEX mengembalikan nilai data dalam baris semasa julat pengekstrakan.
