Cara membandingkan dua lajur dalam Excel dan mengalih keluar pendua (serlahkan, warnakan, alihkan)

Artikel ini akan membawa anda kira-kira 10 minit untuk membaca. Dalam 5 minit seterusnya, anda boleh membandingkan dua lajur dalam Excel dengan mudah dan mengetahui sama ada terdapat pendua di dalamnya, padamkannya atau serlahkannya dalam warna. Jadi, masanya telah tiba!

Excel ialah aplikasi yang sangat berkuasa dan hebat untuk mencipta dan memproses sejumlah besar data. Jika anda mempunyai beberapa buku kerja dengan data (atau hanya satu jadual besar), maka anda mungkin ingin membandingkan 2 lajur, mencari nilai pendua dan kemudian melakukan sesuatu dengannya, contohnya, padam, serlahkan atau kosongkan kandungan . Lajur boleh berada dalam jadual yang sama, bersebelahan atau tidak bersebelahan, mungkin terletak pada 2 helaian yang berbeza atau bahkan dalam buku yang berbeza.

Bayangkan kita mempunyai 2 lajur dengan nama orang – 5 nama setiap lajur A dan 3 nama dalam lajur B. Anda perlu membandingkan nama dalam dua lajur ini dan mencari pendua. Seperti yang anda faham, ini adalah data rekaan, diambil semata-mata sebagai contoh. Dalam jadual sebenar, kami berurusan dengan beribu-ribu atau bahkan berpuluh-puluh ribu rekod.

Pilihan A: kedua-dua lajur berada pada helaian yang sama. Sebagai contoh, lajur A dan lajur B.

Pilihan B: Lajur berada pada helaian yang berbeza. Sebagai contoh, lajur A pada helaian Sheet2 dan lajur A pada helaian Sheet3.

Excel 2013, 2010 dan 2007 mempunyai alat terbina dalam Keluarkan Duplikat (Alih Keluar Pendua) tetapi ia tidak berkuasa dalam keadaan ini kerana ia tidak dapat membandingkan data dalam 2 lajur. Selain itu, ia hanya boleh mengalih keluar pendua. Tiada pilihan lain seperti menyerlahkan atau menukar warna. Dan titik!

Seterusnya, saya akan menunjukkan kepada anda cara yang mungkin untuk membandingkan dua lajur dalam Excel, yang akan membolehkan anda mencari dan mengalih keluar rekod pendua.

• Bandingkan 2 lajur dan cari pendua menggunakan formula
  
  • Pilihan A: kedua-dua lajur berada pada helaian yang sama
  • Pilihan B: Lajur berada pada helaian yang berbeza atau dalam buku kerja yang berbeza
• Memproses pendua yang ditemui
  
  • Tunjukkan baris pendua sahaja dalam lajur A
  • Tukar warna atau serlahkan pendua yang ditemui
  • Alih keluar pendua daripada lajur pertama

Bandingkan 2 lajur dalam Excel dan cari entri pendua menggunakan formula

Pilihan A: kedua-dua lajur berada pada helaian yang sama

1. Dalam sel kosong pertama (dalam contoh kami, ini adalah sel C1), kami menulis formula berikut:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Dalam formula kami A1 ini ialah sel pertama lajur pertama yang akan kita bandingkan. $B$1 и $B$10000 ini ialah alamat sel pertama dan terakhir lajur kedua, yang dengannya kami akan melakukan perbandingan. Perhatikan rujukan mutlak – huruf lajur dan nombor baris didahului dengan tanda dolar ($). Saya menggunakan rujukan mutlak supaya alamat sel kekal sama semasa menyalin formula.

   Jika anda ingin mencari pendua dalam lajur B, tukar rujukan supaya formula kelihatan seperti ini:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Sebaliknya "Hanya"Dan"Pendua» Anda boleh menulis label anda sendiri, sebagai contoh, «Tidak dijumpai"Dan"Mendapati“, atau biarkan sahaja “Pendua' dan masukkan aksara ruang dan bukannya nilai kedua. Dalam kes kedua, sel yang tiada pendua ditemui akan kekal kosong, dan, saya percaya, perwakilan data ini paling mudah untuk analisis selanjutnya.

2. Sekarang mari salin formula kami ke semua sel dalam lajur C, sehingga ke baris bawah, yang mengandungi data dalam lajur A. Untuk melakukan ini, gerakkan penuding tetikus ke sudut kanan bawah sel C1, penunjuk akan mengambil bentuk silang hitam, seperti yang ditunjukkan dalam gambar di bawah:Klik dan tahan butang kiri tetikus dan seret sempadan bingkai ke bawah, menyerlahkan semua sel di mana anda ingin memasukkan formula. Apabila semua sel yang diperlukan dipilih, lepaskan butang tetikus:

   

Petua: Dalam jadual besar, penyalinan formula akan menjadi lebih pantas jika anda menggunakan pintasan papan kekunci. Serlahkan sel C1 dan tekan Ctrl + C (untuk menyalin formula ke papan keratan), kemudian klik Ctrl + Shift + Tamat (untuk memilih semua sel bukan kosong dalam lajur C) dan akhirnya tekan Ctrl + V (untuk memasukkan formula ke dalam semua sel yang dipilih).

3. Hebat, kini semua nilai pendua ditandakan sebagai “Pendua":

Pilihan B: dua lajur berada pada helaian berbeza (dalam buku kerja berbeza)

1. Dalam sel pertama lajur kosong pertama pada lembaran kerja Sheet2 (dalam kes kami ia adalah lajur B) masukkan formula berikut:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Di sini Sheet3 ialah nama helaian di mana lajur ke-2 terletak, dan $A$1:$A$10000 ialah alamat sel dari 1 hingga yang terakhir dalam lajur ke-2 ini.

2. Salin formula ke semua sel dalam lajur B (sama seperti pilihan A).
3. Kami mendapat keputusan ini:

Memproses pendua yang ditemui

Hebat, kami telah menemui entri dalam lajur pertama yang turut terdapat dalam lajur kedua. Sekarang kita perlu melakukan sesuatu dengan mereka. Meneliti semua rekod pendua dalam jadual secara manual agak tidak cekap dan mengambil terlalu banyak masa. Terdapat cara yang lebih baik.

Tunjukkan baris pendua sahaja dalam lajur A

Jika lajur anda tidak mempunyai pengepala, maka anda perlu menambahkannya. Untuk melakukan ini, letakkan kursor pada nombor yang mewakili baris pertama, dan ia akan bertukar menjadi anak panah hitam, seperti yang ditunjukkan dalam rajah di bawah:

Klik kanan dan pilih daripada menu konteks Memasukkan (Sisipkan):

Berikan nama pada lajur, sebagai contoh, “Nama"Dan"Gandakan?» Kemudian buka tab Tarikh (Data) dan tekan Saring (Penapis):

Selepas itu klik pada anak panah kelabu kecil di sebelah “Gandakan?« untuk membuka menu penapis; nyahtanda semua item dalam senarai ini kecuali Pendua, dan tekan OK.

Itu sahaja, kini anda hanya melihat unsur-unsur lajur tersebut А, yang diduakan dalam lajur В. Terdapat hanya dua sel sedemikian dalam jadual latihan kami, tetapi, seperti yang anda faham, dalam praktiknya akan ada lebih banyak daripada mereka.

Untuk memaparkan semua baris lajur sekali lagi А, klik simbol penapis dalam lajur В, yang kini kelihatan seperti corong dengan anak panah kecil, dan pilih Pilih semua (Pilih semua). Atau anda boleh melakukan perkara yang sama melalui Reben dengan mengklik Tarikh (Data) > Pilih & Tapis (Isih & Tapis) > Clear (Jelas) seperti yang ditunjukkan dalam tangkapan skrin di bawah:

Tukar warna atau serlahkan pendua yang ditemui

Jika nota "Pendua” tidak mencukupi untuk tujuan anda dan anda ingin menandakan sel pendua dengan warna fon, warna isian atau kaedah lain yang berbeza…

Dalam kes ini, tapis pendua seperti yang ditunjukkan di atas, pilih semua sel yang ditapis dan klik Ctrl + 1untuk membuka dialog Format Sel (format sel). Sebagai contoh, mari kita tukar warna isian sel dalam baris dengan pendua kepada kuning terang. Sudah tentu, anda boleh menukar warna isian dengan alat tersebut Mengisi tab (Warna Isi). Laman Utama (Rumah) tetapi kelebihan kotak dialog Format Sel (Format Sel) kerana anda boleh mengkonfigurasi semua pilihan pemformatan pada masa yang sama.

Kini anda pasti tidak akan terlepas sebarang sel dengan pendua:

Mengalih keluar nilai pendua dari lajur pertama

Tapis jadual supaya hanya sel dengan nilai pendua ditunjukkan dan pilih sel tersebut.

Jika 2 lajur yang anda bandingkan adalah pada helaian yang berbeza, iaitu, dalam jadual yang berbeza, klik kanan julat yang dipilih dan pilih Padamkan Baris (Alih keluar baris):

Keratan Akhbar OKapabila Excel meminta anda mengesahkan bahawa anda benar-benar mahu memadamkan keseluruhan baris helaian dan kemudian mengosongkan penapis. Seperti yang anda lihat, hanya baris dengan nilai unik yang kekal:

Jika 2 lajur berada pada helaian yang sama, rapat antara satu sama lain (bersebelahan) atau tidak rapat antara satu sama lain (bukan bersebelahan), maka proses membuang pendua akan menjadi lebih rumit sedikit. Kami tidak boleh mengalih keluar keseluruhan baris dengan nilai pendua, kerana ini akan mengalih keluar sel dari lajur kedua juga. Jadi untuk meninggalkan hanya entri unik dalam lajur А, melakukan ini:

1. Tapis jadual untuk menunjukkan nilai pendua sahaja dan pilih sel tersebut. Klik kanan pada mereka dan pilih daripada menu konteks Kandungan yang jelas (isi kandungan yang jelas).
2. Bersihkan penapis.
3. Pilih semua sel dalam lajur А, bermula dari sel A1 sehingga ke bawah yang mengandungi data.
4. Klik Tarikh (Data) dan tekan Isih A hingga Z (Isih dari A hingga Z). Dalam kotak dialog yang terbuka, pilih Teruskan dengan pilihan semasa (Isih dalam pilihan yang ditentukan) dan klik butang Hitam (Menyusun):
5. Padamkan lajur dengan formula, anda tidak lagi memerlukannya, mulai sekarang anda hanya mempunyai nilai unik.
6. Itu sahaja, kini lajur А mengandungi hanya data unik yang tiada dalam lajur В:

Seperti yang anda lihat, mengalih keluar pendua daripada dua lajur dalam Excel menggunakan formula tidaklah begitu sukar.