Formula umum
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Ringkasan
Untuk mengekstrak hanya nilai unik dari daftar atau kolom, Anda bisa menggunakan rumus array yang didasarkan pada INDEX, MATCH, dan COUNTIF. Dalam contoh yang diperlihatkan, rumus di D5, disalin ke bawah, adalah:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
di mana "daftar" adalah rentang bernama B5: B11.
Catatan: ini adalah rumus array dan harus dimasukkan menggunakan control + shift + enter.
Penjelasan
Inti dari rumus ini adalah pencarian dasar dengan INDEX:
=INDEX(list,row)
Dengan kata lain, berikan INDEX daftar dan nomor baris, dan INDEX akan mengambil nilai untuk ditambahkan ke daftar unik.
Kerja kerasnya adalah mencari angka ROW untuk memberikan INDEX, sehingga kita mendapatkan nilai yang unik saja. Ini dilakukan dengan MATCH dan COUNTIF, dan trik utamanya ada di sini:
COUNTIF($D$4:D4,list)
Di sini, COUNTIF menghitung berapa kali item yang sudah ada dalam daftar unik muncul di daftar master, menggunakan referensi yang diperluas untuk kisaran tersebut, $ D $ 4: D4.
Referensi yang meluas adalah mutlak di satu sisi, relatif di sisi lain. Dalam kasus ini, saat rumus disalin, referensi akan diperluas untuk menyertakan lebih banyak baris dalam daftar unik.
Perhatikan referensi dimulai di D4, satu baris di atas entri unik pertama, dalam daftar unik. Ini disengaja - kami ingin menghitung item * sudah * dalam daftar unik, dan kami tidak dapat menyertakan sel saat ini tanpa membuat referensi melingkar. Jadi, kita mulai dari baris di atas.
Penting: pastikan judul untuk daftar unik tidak muncul di daftar master.
Untuk kriteria di COUNTIF, kami menggunakan daftar induk itu sendiri. Saat diberi beberapa kriteria, COUNTIF akan mengembalikan beberapa hasil dalam satu larik. Di setiap baris baru, kami memiliki array yang berbeda seperti ini:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Catatan: COUNTIF menangani beberapa kriteria dengan hubungan "ATAU" (yaitu, COUNTIF (rentang, ("merah", "biru", "hijau")) menghitung merah, biru, atau hijau.
Sekarang kita memiliki array yang kita butuhkan untuk mencari posisi (nomor baris). Untuk ini, kami menggunakan MATCH, disiapkan untuk pencocokan tepat, untuk menemukan nilai nol. Jika kita meletakkan array yang dibuat oleh COUNTIF di atas ke dalam MATCH, inilah yang kita dapatkan:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH menempatkan item dengan mencari hitungan nol (yaitu mencari item yang belum muncul dalam daftar unik). Ini berfungsi, karena MATCH selalu mengembalikan kecocokan pertama saat ada duplikat.
Akhirnya, posisi dimasukkan ke dalam INDEX sebagai nomor baris, dan INDEX mengembalikan nama pada posisi itu.
Versi non-array dengan LOOKUP
Anda bisa menyusun rumus non-larik untuk mengekstrak item unik menggunakan fungsi LOOKUP yang fleksibel:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Konstruksi rumus mirip dengan rumus PENCOCOKAN INDEKS di atas, tetapi LOOKUP dapat menangani operasi array secara native.
- COUNTIF mengembalikan hitungan setiap nilai dari "daftar" dalam rentang meluas $ D $ 4: D4
- Membandingkan dengan nol membuat larik nilai TRUE dan FALSE
- Angka 1 dibagi dengan array, membuat array 1s dan kesalahan # DIV / 0
- Larik ini menjadi lookup_vector di dalam LOOKUP
- Nilai pencarian 2 lebih besar daripada nilai apa pun di lookup_vector
- LOOKUP akan cocok dengan nilai bukan kesalahan terakhir dalam larik pencarian
- LOOKUP mengembalikan nilai yang sesuai di result_vector, rentang bernama "daftar"
Ekstrak item yang muncul sekali
Rumus LOOKUP di atas mudah diperluas dengan logika boolean. Untuk mengekstrak daftar item unik yang muncul sekali dalam data sumber, Anda bisa menggunakan rumus seperti ini:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Satu-satunya tambahan adalah ekspresi COUNTIF kedua:
COUNTIF(list,list)=1
Di sini, COUNTIF mengembalikan larik jumlah item seperti ini:
(2;2;2;2;2;1;2)
yang dibandingkan dengan 1, menghasilkan larik nilai TRUE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
yang bertindak sebagai "filter 'untuk membatasi keluaran ke item yang terjadi hanya sekali dalam data sumber.
