Note: The other languages of the website are Google-translated. Back to English

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.
tab kte 201905

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!
officetab bawah
Urutkan komentar berdasarkan
komentar (40)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
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))
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!
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!
Komentar ini diminimalkan oleh moderator di situs
Terima kasih, Ini luar biasa!
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?
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?
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?
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...
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)
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)
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
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?
Komentar ini diminimalkan oleh moderator di situs
Halo, saya mendapatkan kesalahan "#N/A" di "Fungsi pencocokan", dapatkah Anda memandu?
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
Komentar ini diminimalkan oleh moderator di situs
sebenarnya saya ingin sel mencerminkan "YA" jika (AL2="AP" dan AK2="AD" dan Z2>500000)
Komentar ini diminimalkan oleh moderator di situs
Saya mendapatkan kesalahan #N/A pada fungsi Match dengan rumus ini. Bisakah Anda membantu?
Komentar ini diminimalkan oleh moderator di situs
Halo, saya mendapatkan kesalahan "#N/A" di "Fungsi pencocokan", dapatkah Anda memandu?
Komentar ini diminimalkan oleh moderator di situs
Jika Anda mendapatkan kesalahan #N/A, buka rumus Anda dan gunakan Control + Shift + Enter alih-alih Enter.
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)
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!
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.
Komentar ini diminimalkan oleh moderator di situs
Terima Kasih!
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.
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
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!
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!
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?
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?
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
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.
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
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL