Loncat ke daftar isi utama

Bagaimana cara mengecualikan nilai dalam satu daftar dari yang lain di Excel?

Misalkan Anda memiliki dua daftar data seperti screenshot kiri yang ditampilkan. Sekarang Anda perlu menghapus atau mengecualikan nama di kolom A jika nama tersebut sudah ada di kolom D. Bagaimana cara mencapainya? Dan bagaimana jika kedua daftar ditempatkan di dua lembar kerja yang berbeda? Artikel ini memberikan dua metode untuk Anda.

Kecualikan nilai dalam satu daftar dari yang lain dengan rumus

Cepat kecualikan nilai dalam satu daftar dari yang lain dengan Kutools for Excel

Kecualikan nilai dalam satu daftar dari yang lain dengan rumus

Anda dapat menerapkan rumus berikut untuk mencapainya. Silakan lakukan sebagai berikut.

1. Pilih sel kosong yang berdekatan dengan sel pertama dari daftar yang ingin Anda hapus, lalu masukkan rumus = COUNTIF ($ D $ 2: $ D $ 6, A2) ke dalam Formula Bar, lalu tekan Enter kunci. Lihat tangkapan layar:

Note: Dalam rumusnya, $ D $ 2: $ D $ 6 adalah daftar yang nilainya akan dihapus, A2 adalah sel pertama dari daftar yang akan Anda hapus. Harap ubah sesuai kebutuhan Anda.

2. Tetap memilih sel hasil, seret Fill Handle ke bawah hingga mencapai sel terakhir dari daftar. Lihat tangkapan layar:

3. Tetap pilih daftar hasil, lalu klik Data > Urutkan A sampai Z.

Kemudian Anda dapat melihat daftar diurutkan seperti gambar di bawah ini.

4. Sekarang pilih seluruh baris nama dengan hasil 1, klik kanan rentang yang dipilih dan klik Delete untuk menghapusnya.

Sekarang Anda telah mengecualikan nilai dalam satu daftar berdasarkan daftar lainnya.

Note: Jika "daftar-untuk-hapus" terletak di kisaran A2: A6 dari lembar kerja lain seperti Sheet2, silakan gunakan rumus ini = IF (ISERROR (VLOOKUP (A2, Sheet2! $ A $ 2: $ A $ 6,1, FALSE)), "Simpan", "Hapus") untuk mendapatkan semuanya Menjaga dan Delete hasil, lanjutkan untuk mengurutkan daftar hasil dari Ato Z, dan kemudian secara manual menghapus semua baris nama yang berisi hasil Hapus di lembar kerja saat ini.

Cepat kecualikan nilai dalam satu daftar dari yang lain dengan Kutools for Excel

Bagian ini akan merekomendasikan Pilih Sel yang Sama & Berbeda kegunaan Kutools untuk Excel untuk mengatasi masalah ini. Silakan lakukan sebagai berikut.

Sebelum melamar Kutools untuk Excel, Mohon unduh dan instal terlebih dahulu.

1. klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda. Lihat tangkapan layar:

2. Dalam Pilih Sel yang Sama & Berbeda kotak dialog, Anda perlu:

  • 2.1 Pilih daftar yang akan Anda hapus nilainya di Temukan nilai dalam kotak;
  • 2.2 Pilih daftar yang akan Anda hapus nilai-nilainya berdasarkan di Menurut kotak;
  • 2.3 pilih Sel tunggal pilihan dalam Berdasarkan bagian;
  • 2.4 Klik tombol OK tombol. Lihat tangkapan layar:

3. Kemudian kotak dialog muncul untuk memberi tahu Anda berapa banyak sel yang telah dipilih, klik OK .

4. Sekarang nilai di kolom A dipilih jika sudah ada di kolom D. Anda bisa menekan Delete kunci untuk menghapusnya secara manual.

  Jika Anda ingin memiliki uji coba gratis (30 hari) dari utilitas ini, silahkan klik untuk mendownloadnya, lalu lanjutkan untuk menerapkan operasi sesuai langkah di atas.

Cepat kecualikan nilai dalam satu daftar dari yang lain dengan Kutools for Excel

Terkait artikel:

Alat Produktivitas Kantor Terbaik

🤖 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

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!
Comments (16)
No ratings yet. Be the first to rate!
Claudio
Claudio
This comment was minimized by the moderator on the site
"foruma contains an error!"
Wondeful help...
Reply
0
0
Elisa
Elisa     Claudio
This comment was minimized by the moderator on the site
the function is correct: maybe you have Excel in a different language than english. Change the function names in that way. :)
Reply
0
0
Sarah
Sarah
This comment was minimized by the moderator on the site
Thank you for this. It solved my problem of wanting to include only companies from a list
Reply
0
0
Keith
Keith
This comment was minimized by the moderator on the site
This formula subtracts list in column B from list in columnA:=FILTER(IFNA(MATCH(A2:A12,B2:B6,0),A2:A12),ISNUMBER(IFNA(MATCH(A2:A12,B2:B6,0),A2:A12))=FALSE)
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
But this one's simpler (Subtracts list in A2:A20 from list in D2:D6):=FILTER(A2:A20,ISERROR(MATCH(A2:A20,D2:D6,0))=TRUE)
Reply
0
0
Bruno
Bruno     Keith
This comment was minimized by the moderator on the site
Keith,

This formula was incredibly helpful (even more helpful than the actual blog post!) You're a life saver! This formula gets really powerful if you use it in dynamic arrays.
Reply
0
0
Penny D Gaddy
Penny D Gaddy     Keith
This comment was minimized by the moderator on the site
what if I have multiple columns?
Reply
0
0
Keith
Keith     Penny D Gaddy
This comment was minimized by the moderator on the site
Depends on the structure of your lists, but if you want to exclude several lists Y and Z from a single big list X, try something like this:
=FILTER(X3:X21,ISERROR(MATCH(X3:X21,Y3:Y6,0))*ISERROR(MATCH(X3:X21,Z3:Z5,0))=1)
this returns listX minus listY and listZ.
The "ISERROR(MATCH(X3:X21,Y3:Y6,0))" returns "TRUE" if an item in listX is missing from listY, likewise ISERROR(MATCH(X3:X21,Z3:Z5,0)) for listX and listZ.
A quirk of Excel is that "=TRUE*TRUE" will return "1". In fact, any number of TRUEs multiplied will return "1", but include a single FALSE and it will return "0".
So the FILTER will return only those items from the list where all the "ISERROR(MATCH....." formulae are TRUE.
For each additional list of items to exclude, add an extra *ISERROR(MATCH(listX,list_exclude,0)) before the "=1)"
Reply
0
0
Keith
Keith     Penny D Gaddy
This comment was minimized by the moderator on the site
You could try a filter of a filter:=FILTER(A2:A20,ISERROR(MATCH(A2:A20,FILTER(Array2,Include_criteria2)))=TRUE)
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
Apologies, that should read "Subtracts list in D2:D6 from list in A2:A20."
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
Apologies, that should read "Subtracts list in D2:D6 from list in A2:A20."
Reply
0
0
Keith
Keith     Keith
This comment was minimized by the moderator on the site
Apologies, that should read "Subtracts list in D2:D6 from list in A2:A20"
Reply
0
0
Geo
Geo
This comment was minimized by the moderator on the site
Good find. Thank you for this clever workaround. I thought was only possible via macros.
Possible to do the same using method 1, for a range consisting in two columns. i.e City, State in col D2:E20 while my A2:C1000 (B:C has city,state) has the set I need to get marked?
tried this did not work: =COUNTIF($D$2:$E$20,B2:C2). Unless you have another wonderful guide.
Reply
0
0
Стас
Стас
This comment was minimized by the moderator on the site
Как безграмотный человек писал эту статью? Тот, кто русский язык не учил, ни одной книги не прочел и не общался в социуме никогда? Уже с самого начала статьи обороты почти в каждом предложении такие, что на голову не натянешь. Зачем писать, если не умеешь писать?
Reply
0
0
Zuvay
Zuvay
This comment was minimized by the moderator on the site
I receive other values in the COUNTIF column like 2 but also higher numbers other than 0 and 1. What does that mean?
Reply
0
0
LX
LX     Zuvay
This comment was minimized by the moderator on the site
The values you get in the COUNTIF column are equal to the number of occurrences of the element in the right column. For example, if you have 5 displayed against a cell in the Name column, it means that this name was found in the To-remove-list not one, but five times.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations