Formula umum
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Ringkasan
Untuk mengekstrak daftar nilai unik dari sekumpulan data, difilter menurut hitungan atau kemunculan, Anda bisa menggunakan UNIQUE dengan FILTER, dan menerapkan kriteria dengan fungsi COUNTIF. Dalam contoh yang ditampilkan, rumus di D5 adalah:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
yang mengeluarkan 3 nilai unik yang muncul lebih dari sekali dalam rentang bernama "data" (B5: B16).
Catatan: Dalam contoh ini, kami mengekstrak daftar nilai unik yang muncul lebih dari sekali. Dengan kata lain, kami membuat daftar duplikat :) Bahasanya agak membingungkan.
Penjelasan
Contoh ini menggunakan fungsi UNIQUE bersama dengan fungsi FILTER. Anda dapat melihat contoh yang lebih mendasar di sini.
Trik dalam kasus ini adalah menerapkan kriteria ke fungsi FILTER untuk hanya mengizinkan nilai berdasarkan jumlah kemunculan. Bekerja dari dalam ke luar, ini dilakukan dengan COUNTIF dan fungsi FILTER di sini:
FILTER(data,COUNTIF(data,data)>1)
Hasil dari COUNTIF adalah larik hitungan seperti ini:
(3;1;3;3;2;1;1;3;1;2;3;3)
yang diperiksa dengan perbandingan logis> 1 untuk menghasilkan larik atau nilai TRUE / FALSE:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Perhatikan TRUE sesuai dengan nilai dalam data yang muncul lebih dari sekali. Array ini dikembalikan ke FILTER sebagai termasuk argumen, digunakan untuk menyaring data. FILTER mengembalikan larik lain sebagai hasil:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Larik ini dikembalikan langsung ke fungsi UNIQUE sebagai argumen larik . Perhatikan dari 12 nilai aslinya, hanya 8 yang bertahan.
UNIQUE lalu menghapus duplikat, dan mengembalikan array terakhir:
("red";"green";"blue")
Jika nilai dalam B5: B16 berubah, output akan segera diperbarui.
Hitung> 2
Rumus di F5, yang mencantumkan warna yang muncul setidaknya 2 kali di data sumber, adalah:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Rentang sumber dinamis
Karena data (B5: B15) adalah rentang bernama normal, ukurannya tidak akan diubah jika data ditambahkan atau dihapus. Untuk menggunakan rentang dinamis yang akan secara otomatis mengubah ukuran saat diperlukan, Anda dapat menggunakan Tabel Excel, atau membuat rentang bernama dinamis dengan rumus.
