Bagaimana cara mengekstrak nilai unik berdasarkan kriteria di Excel?

Misalkan, Anda memiliki rentang data kiri yang ingin Anda daftar hanya nama unik kolom B berdasarkan kriteria tertentu dari kolom A untuk mendapatkan hasil seperti gambar di bawah ini. Bagaimana Anda bisa menangani tugas ini di Excel dengan cepat dan mudah?

Ekstrak nilai unik berdasarkan kriteria dengan rumus array

Ekstrak nilai unik berdasarkan beberapa kriteria dengan rumus array

Ekstrak nilai unik dari daftar sel dengan fitur yang berguna

Ekstrak nilai unik berdasarkan kriteria dengan rumus array

Untuk menyelesaikan pekerjaan ini, Anda dapat menerapkan rumus array yang kompleks, lakukan hal berikut:

1. Masukkan rumus di bawah ini ke dalam sel kosong tempat Anda ingin mencantumkan hasil ekstraksi, dalam contoh ini, saya akan meletakkannya ke sel E2, lalu tekan Shift + Ctrl + Enter kunci untuk mendapatkan nilai unik pertama.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. Kemudian, seret gagang isian ke sel sampai sel kosong ditampilkan, dan sekarang semua nilai unik berdasarkan kriteria tertentu telah terdaftar, lihat tangkapan layar:

Catatan: Dalam rumus di atas: B2: B15 adalah rentang kolom berisi nilai unik yang ingin Anda ekstrak, A2: A15 adalah kolom berisi kriteria Anda berdasarkan, D2 menunjukkan kriteria yang Anda inginkan untuk mencantumkan nilai unik berdasarkan, dan E1 adalah sel di atas rumus yang Anda masukkan.

Ekstrak nilai unik berdasarkan beberapa kriteria dengan rumus array

Jika Anda ingin mengekstrak nilai unik berdasarkan dua kondisi, berikut adalah rumus array lain yang dapat membantu Anda, lakukan seperti ini:

1. Masukkan rumus di bawah ini ke dalam sel kosong di mana Anda ingin mencantumkan nilai unik, dalam contoh ini, saya akan meletakkannya ke sel G2, lalu tekan Shift + Ctrl + Enter kunci untuk mendapatkan nilai unik pertama.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. Kemudian, seret gagang isian ke sel sampai sel kosong ditampilkan, dan sekarang semua nilai unik berdasarkan dua kondisi tertentu telah terdaftar, lihat tangkapan layar:

Catatan: Dalam rumus di atas: C2: C15 adalah rentang kolom berisi nilai unik yang ingin Anda ekstrak, A2: A15 serta E2 adalah rentang pertama dengan kriteria yang ingin Anda ekstrak berdasarkan nilai unik, B2: B15 serta F2 adalah rentang kedua dengan kriteria yang ingin Anda ekstrak berdasarkan nilai unik, dan G1 adalah sel di atas rumus yang Anda masukkan.

Ekstrak nilai unik dari daftar sel dengan fitur yang berguna

Terkadang, Anda hanya ingin mengekstrak nilai unik dari daftar sel, di sini, saya akan merekomendasikan alat yang berguna-Kutools untuk Excel, Dengan yang Ekstrak sel dengan nilai unik (termasuk duplikat pertama) utilitas, Anda dapat dengan cepat mengekstrak nilai-nilai unik.

Catatan:Untuk menerapkan ini Ekstrak sel dengan nilai unik (termasuk duplikat pertama), pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Klik sel tempat Anda ingin mengeluarkan hasilnya. (Note: Jangan mengklik sel di baris pertama.)

2. Lalu klik Kutools > Pembantu Formula > Pembantu Formula, lihat tangkapan layar:

3. di Rumus Pembantu kotak dialog, lakukan operasi berikut:

Pilih Teks pilihan dari Rumus Tipe daftar drop-down;
Lalu pilih Ekstrak sel dengan nilai unik (termasuk duplikat pertama) dari Pilih fromula kotak daftar;
Di kanan Masukan argumen bagian, pilih daftar sel yang ingin Anda ekstrak nilai uniknya.

4. Lalu klik Ok tombol, hasil pertama ditampilkan ke dalam sel, lalu pilih sel dan seret pegangan isian ke sel yang ingin Anda daftarkan semua nilai unik sampai sel kosong ditampilkan, lihat tangkapan layar:

Unduh Gratis Kutools untuk Excel Sekarang!

Artikel yang lebih relatif:

Hitung Jumlah Nilai Unik Dan Berbeda Dari Sebuah Daftar
Misalkan, Anda memiliki daftar panjang nilai dengan beberapa item duplikat, sekarang, Anda ingin menghitung jumlah nilai unik (nilai yang muncul dalam daftar hanya sekali) atau nilai yang berbeda (semua nilai berbeda dalam daftar, artinya unik nilai + nilai duplikat pertama) di kolom seperti gambar kiri yang ditampilkan. Artikel ini, saya akan berbicara tentang cara menangani pekerjaan ini di Excel.

Jumlahkan Nilai Unik Berdasarkan Kriteria Di Excel
Misalnya, saya memiliki berbagai data yang berisi kolom Nama dan Urutan, sekarang, untuk menjumlahkan hanya nilai unik di kolom Urutan berdasarkan kolom Nama seperti gambar berikut yang ditampilkan. Bagaimana mengatasi tugas ini dengan cepat dan mudah di Excel?

Ubah Urutan Sel Dalam Satu Kolom Berdasarkan Nilai Unik Di Kolom Lain
Misalkan, Anda memiliki rentang data yang berisi dua kolom, sekarang, Anda ingin mengubah urutan sel dalam satu kolom menjadi baris horizontal berdasarkan nilai unik di kolom lain untuk mendapatkan hasil berikut. Apakah Anda punya ide bagus untuk mengatasi masalah ini di Excel?

Gabungkan Nilai Unik Di Excel
Jika saya memiliki daftar panjang nilai yang diisi dengan beberapa data duplikat, sekarang, saya hanya ingin menemukan nilai unik dan kemudian menggabungkannya menjadi satu sel. Bagaimana saya bisa mengatasi masalah ini dengan cepat dan mudah di Excel?

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2021 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap Uji coba gratis 30 hari. Jaminan uang kembali 60 hari.

Read More ... Download Gratis... Membeli...

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!

Read More ... Download Gratis... Membeli...

komentar (40)

Belum ada peringkat. Jadilah yang pertama memberi peringkat!

Jake

tentang 6 tahun lalu

#17974

Komentar ini diminimalkan oleh moderator di situs

Hai, terima kasih untuk tutorial ini, ini berfungsi dengan baik. Saya mencoba memodifikasinya agar berfungsi dengan kondisi ATAU, tetapi sepertinya tidak berfungsi - apakah ini mungkin? misalnya =INDEX($B$2:$B$17, MATCH(0, IF(OR($D$2=$A$2:$A$17,$D$2=$B$2:$B$17), COUNTIF($E$1 :$E1, $B$2:$B$17), ""), 0))

membalas

Aileen

tentang 6 tahun lalu

#18085

Komentar ini diminimalkan oleh moderator di situs

Terima kasih untuk panduan ini! Saya juga mencoba untuk mengubah rumus, seperti komentator di atas, tetapi dengan kondisi AND sehingga memenuhi kriteria bersyarat lain (misalnya untuk contoh ini, saya hanya ingin melihat hal-hal di atas ambang batas tertentu). Bisakah Anda memberi saran? Terima kasih!

membalas

konfis Aileen

tentang 5 tahun lalu

#18086

Komentar ini diminimalkan oleh moderator di situs

Hei, Salah satu cara untuk melakukannya: Ganti rumus if dengan sumproduct((condition1=rng1)+(condition2=rng2))*countif(... Ini berhasil untuk saya. Semoga berhasil! Dengan mengganti + dengan * Anda dapat buat itu menjadi kondisi ATAU, tetapi perhatikan baik-baik tanda kurung!

membalas

JeteMc

tentang 5 tahun lalu

#18699

Komentar ini diminimalkan oleh moderator di situs

Terima kasih, Ini luar biasa!

membalas

Claire

tentang 5 tahun lalu

#19402

Komentar ini diminimalkan oleh moderator di situs

Terima kasih untuk ini, saya telah mencoba ini dan tampaknya berfungsi dengan baik sebentar-sebentar. Masalah yang terus berulang adalah terkadang hanya nilai pertama yang cocok yang akan kembali dan kemudian diduplikasi ketika saya menyeret ke bawah untuk mengembalikan semua nilai yang cocok. Bagaimana cara mencegah hal ini? Ada saran?

membalas

Barrett

tentang 5 tahun lalu

#19653

Komentar ini diminimalkan oleh moderator di situs

Ini bekerja dengan sangat baik, tetapi setiap kali nilai yang dimasukkannya digandakan, itu hanya menempatkan nilainya sekali. Misalnya, jika daftar Anda memiliki dua Lucy di dalamnya, itu hanya akan membawa satu Lucy ke tabel baru. Apakah ada cara untuk memperbaikinya?

membalas

Amanda

tentang 5 tahun lalu

#19983

Komentar ini diminimalkan oleh moderator di situs

Saat menggunakan rumus ini terus mengulangi nilai pertama, bagaimana Anda menghentikannya dan memberikan daftar nilai yang sama dengan produk di D2?

membalas

Ryan

tentang 5 tahun lalu

#20219

Komentar ini diminimalkan oleh moderator di situs

Hai, untuk menghentikan pengulangan nilai pertama saat Anda menyeret ke bawah, Anda harus MENGHITUNG sel DI ATAS sel tempat Anda memasukkan rumus. Misalnya jika rumus masuk E2 Anda harus mengetik countif($E$1:$E1...

membalas

Andre Ryan

tentang 5 tahun lalu

#20220

Komentar ini diminimalkan oleh moderator di situs

Hai Ryan. Rumus berfungsi dengan baik, namun saat menyeret nilai pertama terus berulang. Saya telah memastikan bahwa COUNTIF mereferensikan sel DI ATAS sel dengan rumus, tetapi masih mengulangi nilai pertama saat menyeret ke bawah? (mis. jika rumus array dalam C2 maka COUNTIF menunjuk ke sel $C$1:$C$1)

membalas

Andre

tentang 5 tahun lalu

#22012

Komentar ini diminimalkan oleh moderator di situs

membalas

Camilla Andre

tentang 5 tahun lalu

#22013

Komentar ini diminimalkan oleh moderator di situs

Mungkin tidak berfungsi karena Anda telah mengunci sel - Coba ganti $C$1:$C$1 dengan $C$1:$C1

membalas

Michael

tentang 5 tahun lalu

#23073

Komentar ini diminimalkan oleh moderator di situs

ini sangat membantu, tetapi saya terus mendapatkan dua kali lipat dari semua nama seperti ini:
Baik, Jane
Baik, Jane
Hoover, Tom
Hoover, Tom

Bagaimana saya bisa menghentikan ini?

membalas

aditya dhavale Michael

tentang 5 tahun lalu

#23074

Komentar ini diminimalkan oleh moderator di situs

Halo, saya mendapatkan kesalahan "#N/A" di "Fungsi pencocokan", dapatkah Anda memandu?

membalas

Laporan

Sundari

tentang 5 tahun lalu

#23642

Komentar ini diminimalkan oleh moderator di situs

=IF(AL2="AP","AP",IF(AK2="AD","AD",IF(Z2>500000,"Ya",,"Tidak"))) Saya ingin "semua kondisi" dipenuhi untuk mengatakan ya ... excel mencerminkan kesalahan dalam rumus ini.. mohon sarannya

membalas

Sundari

tentang 5 tahun lalu

#23643

Komentar ini diminimalkan oleh moderator di situs

sebenarnya saya ingin sel mencerminkan "YA" jika (AL2="AP" dan AK2="AD" dan Z2>500000)

membalas

Aditya

tentang 5 tahun lalu

#23653

Komentar ini diminimalkan oleh moderator di situs

Saya mendapatkan kesalahan #N/A pada fungsi Match dengan rumus ini. Bisakah Anda membantu?

membalas

aditya dhavale

tentang 5 tahun lalu

#23657

Komentar ini diminimalkan oleh moderator di situs

Halo, saya mendapatkan kesalahan "#N/A" di "Fungsi pencocokan", dapatkah Anda memandu?

membalas

Laporan

mujardin

tentang 4 tahun lalu

#23751

Komentar ini diminimalkan oleh moderator di situs

Jika Anda mendapatkan kesalahan #N/A, buka rumus Anda dan gunakan Control + Shift + Enter alih-alih Enter.

membalas

gon

tentang 4 tahun lalu

#23912

Komentar ini diminimalkan oleh moderator di situs

Saya mendapatkan 0 alih-alih hasil yang diharapkan, rumusnya bagus untuk data di lembar yang sama, apakah Anda punya solusi untuk data di lembar berbeda?

ini rumus saya

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)

membalas

skyyang gon

tentang 4 tahun lalu

#23913

Komentar ini diminimalkan oleh moderator di situs

Hai, Gon,
Setelah memasukkan rumus, Anda harus menekan tombol Ctrl + Shift + Enter secara bersamaan, bukan hanya tombol Enter.
Silakan dicoba, terima kasih!

membalas

Laporan

LUCICHE ANIBAL gon

tentang 3 tahun lalu

#23914

Komentar ini diminimalkan oleh moderator di situs

Halo Gan, semoga baik-baik saja. Saya ingin tahu apakah Anda bisa menyelesaikan masalah ini. Saya mendapatkan kesalahan yang sama ketika formula berasal dari lembar yang berbeda. Saya akan menghargai berbagi solusi jika Anda mendapatkannya.

membalas

Laporan

tentang 4 tahun lalu

#23972

Komentar ini diminimalkan oleh moderator di situs

Terima Kasih!

membalas

Joe Jerzo

tentang 4 tahun lalu

#24139

Komentar ini diminimalkan oleh moderator di situs

Bagaimana saya mendapatkan formula ini untuk mengembalikan setiap duplikat alih-alih salah satu dari masing-masing nama? Misalnya, pada contoh di atas, bagaimana saya mendapatkan kolom hasil (B:B) untuk mengembalikan Lucy, Ruby, Anny, Jose, Lucy, Anny, Tom? Saya menggunakan ini sebagai alat anggaran yang menarik ringkasan akun tertentu dari buku besar. Namun, beberapa jumlah dan deskripsi transaksi adalah duplikat dalam buku besar. Setelah nilai duplikat pertama ditarik, tidak ada lagi yang ditarik.

membalas

Laporan

skyyang Joe Jerzo

tentang 4 tahun lalu

#24140

Komentar ini diminimalkan oleh moderator di situs

Hai, Jo,
Untuk mengekstrak semua nilai yang sesuai berdasarkan kriteria sel tertentu, rumus array berikut dapat membantu Anda, lihat tangkapan layar:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Setelah memasukkan rumus, tekan Shift + Ctrl + Enter secara bersamaan untuk mendapatkan hasil yang benar, lalu seret gagang isian ke bawah untuk mendapatkan semua nilai.
Semoga ini bisa membantu Anda, terima kasih

Lihat Lampiran (1 / 5)

membalas

Laporan

Joe Jerzo skyyang

tentang 4 tahun lalu

#24141

Komentar ini diminimalkan oleh moderator di situs

Sejauh ini baik. Saya dapat menduplikasi hasil di lembar pengujian, membuat perubahan pada larik, dan kemudian mengoreksi rumus untuk memperhitungkan perubahan yang telah saya buat. Saya berencana untuk memindahkan ini ke lembar master hari ini dan melihat cara kerjanya. Terima kasih untuk bantuannya!

membalas

Laporan

Joe Jerzo skyyang

tentang 4 tahun lalu

#24142

Komentar ini diminimalkan oleh moderator di situs

Ok, jadi ini berfungsi di buku kerja master. Ada satu pengecualian yang saya belum dapat menentukan penyebabnya: Jika array (dalam kasus saya, buku besar yang saya mulai di baris 3) tidak dimulai di Baris 1, nilai yang dikembalikan salah. Apa yang menyebabkan masalah ini, dan istilah mana dalam rumus yang memperbaikinya? Terima kasih sekali lagi atas bantuan Anda dalam hal ini!

membalas

Laporan

Joe Jerzo skyyang

tentang 4 tahun lalu

#24143

Komentar ini diminimalkan oleh moderator di situs

Pertanyaan Terakhir: Jika saya ingin kolom hasil mengembalikan semua nilai yang tidak terkait dengan KTE atau KTO (jadi, D:D akan menjadi Tom, Nocol, Lily, Angelina, Genna), bagaimana saya melakukannya?

membalas

Laporan

ewik

tentang 4 tahun lalu

#26207

Komentar ini diminimalkan oleh moderator di situs

Bagi saya rumus tidak bekerja. Saya menekan ctrl shift enter dan saya masih mendapatkan kesalahan N/A. Saya ingin menambahkan bahwa saya menyiapkan data yang sama persis seperti di tutorial. Apa alasannya tidak berhasil?

membalas

tentang 4 tahun lalu

#26301

Komentar ini diminimalkan oleh moderator di situs

Ini telah bekerja sangat baik untuk saya dengan nilai pencarian tertentu. Namun, jika saya ingin menggunakan wildcard untuk mencari nilai parsial, bagaimana saya melakukannya? Misalnya, jika saya ingin mencari semua nama yang terkait dengan KT?

Saya menggunakan fungsi ini untuk mencari sel yang berisi banyak teks. Misalnya jika setiap produk juga memiliki sub-produk dalam sel yang sama tetapi saya hanya mencari nama yang terkait dengan sub-produk "elf".

KTE - peri
KTE- bola
KTE - piano
KTO - peri
KTO- bola
KTO - piano

membalas

Konstantin

tentang 3 tahun lalu

#29208

Komentar ini diminimalkan oleh moderator di situs

Apakah ada cara untuk membuat ini berfungsi sambil MENGIZINKAN untuk nilai duplikat? Misalnya, saya ingin semua contoh Lucy dicantumkan dalam hasil.

membalas

skyyang Konstantin

tentang 3 tahun lalu

#29209

Komentar ini diminimalkan oleh moderator di situs

Halo, Konstantin,
Untuk mengekstrak semua nilai terkait termasuk duplikat berdasarkan kriteria sel tertentu, rumus array berikut dapat membantu Anda, lihat tangkapan layar:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

Setelah memasukkan rumus, tekan Shift + Ctrl + Enter secara bersamaan untuk mendapatkan hasil yang benar, lalu seret gagang isian ke bawah untuk mendapatkan semua nilai.
Semoga ini bisa membantu Anda, terima kasih

Lihat Lampiran (1 / 5)

membalas

Laporan

Belum ada komentar yang diposting di sini

Muat Lebih

Tinggalkan komentar anda

Posting sebagai Tamu

Nama (Wajib)

Email (Diperlukan)

Beri peringkat pos ini:

ulang

0 Karakter

Saya setuju dengan syarat dan ketentuan