Formula generik
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Ringkasan
Untuk mengekstrak nilai unik dari senarai atau lajur, anda boleh menggunakan formula larik berdasarkan INDEX, MATCH, dan COUNTIF. Dalam contoh yang ditunjukkan, formula di D5, disalin, adalah:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
di mana "senarai" adalah julat bernama B5: B11.
Catatan: ini adalah formula larik dan mesti dimasukkan menggunakan kawalan + shift + enter.
Penjelasan
Inti formula ini adalah carian asas dengan INDEX:
=INDEX(list,row)
Dengan kata lain, berikan senarai dan nombor baris kepada INDEX, dan INDEX akan mengambil nilai untuk ditambahkan ke senarai unik.
Kerja keras adalah mencari nombor ROW untuk memberi INDEX, sehingga kita hanya mendapat nilai unik. Ini dilakukan dengan MATCH dan COUNTIF, dan helah utamanya ada di sini:
COUNTIF($D$4:D4,list)
Di sini, COUNTIF mengira berapa kali item yang sudah ada dalam senarai unik muncul dalam senarai induk, menggunakan rujukan yang berkembang untuk julat, $ D $ 4: D4.
Rujukan yang berkembang adalah mutlak di satu pihak, relatif di sisi lain. Dalam kes ini, kerana formula disalin ke bawah, rujukan akan diperluas untuk memasukkan lebih banyak baris dalam senarai unik.
Perhatikan rujukan bermula di D4, satu baris di atas entri unik pertama, dalam senarai unik. Ini disengajakan - kami ingin mengira item * sudah * dalam senarai unik, dan kami tidak dapat memasukkan sel semasa tanpa membuat rujukan bulat. Jadi, kita mulakan pada baris di atas.
Penting: pastikan tajuk untuk senarai unik tidak muncul dalam senarai induk.
Untuk kriteria di COUNTIF, kami menggunakan senarai induk itu sendiri. Apabila diberi pelbagai kriteria, COUNTIF akan mengembalikan banyak hasil dalam satu array. Pada setiap baris baru, kami mempunyai susunan yang berbeza 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 pelbagai kriteria dengan hubungan "ATAU" (iaitu COUNTIF (julat, ("merah", "biru", "hijau")) dikira merah, biru, atau hijau.
Sekarang kita mempunyai tatasusunan yang kita perlukan untuk mencari kedudukan (nombor baris). Untuk ini, kami menggunakan MATCH, disiapkan untuk pencocokan tepat, untuk mencari nilai sifar. Sekiranya kita memasukkan susunan yang dibuat oleh COUNTIF di atas ke dalam MATCH, inilah yang kita dapat:
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 mencari item dengan mencari kiraan sifar (iaitu mencari item yang belum muncul dalam senarai unik). Ini berfungsi, kerana PERTANDINGAN selalu mengembalikan perlawanan pertama apabila terdapat pendua.
Akhirnya, kedudukan dimasukkan ke dalam INDEX sebagai nombor baris, dan INDEX mengembalikan nama pada kedudukan itu.
Versi bukan array dengan LOOKUP
Anda boleh membina formula bukan larik untuk mengekstrak item unik menggunakan fungsi LOOKUP fleksibel:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Pembentukan formula serupa dengan formula INDEX MATCH di atas, tetapi LOOKUP dapat menangani operasi array secara semula jadi.
- COUNTIF mengembalikan jumlah setiap nilai dari "senarai" dalam julat yang berkembang $ D $ 4: D4
- Membandingkan dengan sifar menghasilkan susunan nilai BENAR dan SALAH
- Nombor 1 dibahagi dengan tatasusunan, membuat susunan kesalahan 1s dan # DIV / 0
- Susunan ini menjadi lookup_vector di dalam LOOKUP
- Nilai carian 2 lebih besar daripada nilai dalam lookup_vector
- LOOKUP akan sepadan dengan nilai bukan ralat terakhir dalam tatasusunan carian
- LOOKUP mengembalikan nilai yang sepadan dalam result_vector, julat bernama "senarai"
Ekstrak item yang muncul sekali sahaja
Rumusan LOOKUP di atas senang diperluas dengan logik boolean. Untuk mengekstrak senarai item unik yang muncul sekali dalam data sumber, anda boleh menggunakan formula seperti ini:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Satu-satunya penambahan ialah ungkapan COUNTIF kedua:
COUNTIF(list,list)=1
Di sini, COUNTIF mengembalikan pelbagai jumlah item seperti ini:
(2;2;2;2;2;1;2)
yang dibandingkan dengan 1, menghasilkan susunan nilai BENAR / SALAH:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
yang bertindak sebagai "penapis" untuk menyekat output pada item yang berlaku sekali sahaja dalam data sumber.
