Cara Membandingkan Dua Helaian Excel Untuk Pendua: 5 Cara Pantas

Apabila bekerja dengan set data yang besar dalam Excel, adalah perkara biasa untuk mempunyai berbilang lembaran kerja yang mengandungi data yang serupa. Membandingkan helaian ini untuk rekod pendua boleh menjadi agak mencabar, terutamanya apabila data bertaburan merentasi pelbagai lajur dan baris.

Kaedah yang paling biasa untuk mencari pendua dalam dua helaian Excel ialah menggunakan:

1. Fungsi VLOOKUP, COUNTIF atau EXACT

2. Pemformatan bersyarat

3. Power Query

4. Alat luaran dan tambahan

5. Pemeriksaan visual untuk pendua

Artikel ini berjalan langkah demi langkah melalui lima kaedah ini untuk menentukan dan mengendalikan pendua merentas berbilang lembaran kerja. Pada akhirnya, anda akan melihat betapa mudahnya mengurus nilai pendua.

Mari kita mulakan!

Isi kandungan

• Contoh Lembaran Kerja Dan Data
• 1. Menggunakan Fungsi VLOOKUP, COUNTIF atau EXACT untuk Mencari Pendua
  • A. Bagaimanakah Anda Menggunakan Fungsi VLOOKUP untuk Mencari Pendua dalam Dua Helaian?
    • Bagaimana jika Anda Mengendalikan Buku Kerja Berbeza?
  • B. Bagaimanakah Anda Menggunakan Fungsi COUNTIF untuk Mencari Pendua Merentasi Lembaran Kerja?
  • C. Bagaimanakah Anda Menggunakan Fungsi EXACT untuk Mencari Pendua Merentas Lembaran Kerja?
• 2. Cara Menggunakan Pemformatan Bersyarat untuk Baris Pendua
  • Cara Menggunakan Pengurus Peraturan Pemformatan Bersyarat
• 3. Cara Menggunakan Power Query untuk Mencari Pendua Merentas Lembaran Kerja
• 4. Alat dan Tambahan untuk Mengenalpasti Pendua Merentas Lembaran Kerja
• 5. Cara Menyemak Pendua Secara Visual dalam Dua Helaian
• 3 Petua untuk Menyediakan Lembaran Kerja Excel Anda
• Cara Mengendalikan Ralat dan Ketidakkonsistenan
• Fikiran Akhir

Contoh Lembaran Kerja Dan Data

Untuk mengikuti contoh dalam artikel ini, buat buku kerja Excel baharu dengan data ini dalam lajur A helaian pertama:

• epal

• Jingga

• buah pir

• Strawberi

Letakkan data ini ke dalam lajur A helaian kedua:

• buah pir

• Strawberi

• epal

• Nenas

Lembaran kerja anda kelihatan seperti ini:

Setelah anda menyediakan lembaran kerja anda, kini kami boleh menyemak cara yang berbeza untuk mencari pendua dalam dua helaian, bermula dengan fungsi VLOOKUP, COUNTIF dan EXACT.

Mari pergi!

1. Menggunakan Fungsi VLOOKUP, COUNTIF atau EXACT untuk Mencari Pendua

Excel mempunyai tiga fungsi terbina dalam yang boleh memudahkan pencarian pendua: VLOOKUP , COUNTIF dan EXACT .

Fungsi ini direka bentuk untuk membantu anda mencari, mengira dan membandingkan data dalam hamparan anda, menjadikannya alat yang sesuai untuk mencari entri pendua.

Setiap fungsi mempunyai tujuan yang unik dan memahami cara menggunakannya boleh menyelaraskan proses analisis data anda dengan ketara.

Dalam bahagian berikut, kami akan meneroka cara menggunakan fungsi VLOOKUP, COUNTIF dan EXACT untuk mengenal pasti pendua dalam lembaran kerja Excel anda dengan berkesan.

A. Bagaimanakah Anda Menggunakan Fungsi VLOOKUP untuk Mencari Pendua dalam Dua Helaian?

VLOOKUP bermaksud Vertical Lookup. Anda menggunakan fungsi ini untuk mencari nilai pendua antara dua lajur. Ini adalah sintaksnya:

=VLOOKUP(nilai_carian, tatasusunan_jadual, bilangan_indeks_kol, [julat_carian])

• lookup_value : Nilai yang anda ingin cari dalam lajur pertama table_array.

• table_array : Julat sel yang mengandungi data yang anda ingin cari.

• col_index_num : Nombor lajur dalam table_array yang anda mahu pulangkan nilainya.

• range_lookup : Pilihan. Ia sama ada BENAR (anggaran padanan) atau SALAH (padanan tepat). Lalai adalah BENAR.

Untuk menggunakan fungsi merentas dua lembaran kerja dalam fail Excel, anda perlu tahu cara merujuk helaian berasingan dalam formula. Untuk berbuat demikian, taip nama helaian diikuti dengan tanda seru (!) dan julat sel atau sel anda.

Sebagai contoh, ini merujuk sel A2 kepada A5 dalam helaian 2 buku kerja yang sama: Helaian 2!$A$2:$A$5.

Untuk menggunakan fungsi VLOOKUP dalam hamparan sampel anda, ikut langkah berikut:

1. Pilih sel B2 untuk memaparkan hasil perbandingan pertama.

2. Taip formula ini: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE).

3. Tekan Enter untuk memaparkan hasil perbandingan.

4. Isikan formula untuk membandingkan nilai untuk baris yang lain dalam helaian pertama.

Hasilnya akan kelihatan seperti gambar di bawah:

Anda boleh mengemaskan paparan dengan menunjukkan mesej mesra pengguna dan bukannya ralat apabila pendua tidak ditemui.

Sebagai contoh, formula ini akan memaparkan "Ya" atau "Tidak" masing-masing untuk nilai yang ditemui dan tidak dijumpai:

=IF(ISNA(VLOOKUP(A2, Helaian 2!$A$2:$A$5, 1, SALAH)), “Tidak”, “Ya”)

Terdapat lebih banyak contoh fungsi ini dalam artikel kami tentang menggunakan VLOOKUP untuk membandingkan dua lajur .

Bagaimana jika Anda Mengendalikan Buku Kerja Berbeza?

Jika lembaran kerja anda berada dalam buku kerja yang berasingan, penggunaan fungsi adalah sama dengan dua fail Excel. Walau bagaimanapun, merujuk lembaran kerja kedua adalah sedikit lebih kompleks.

Anda perlu:

• Sertakan nama buku kerja Excel dalam kurungan

• Ikuti dengan nama lembaran kerja

• Sertakan buku kerja dan lembaran kerja dalam tanda petikan.

Contohnya, jika sel berada dalam helaian bernama Sheet2 dalam buku kerja bernama "WB 2.xlsx", formatnya akan kelihatan seperti ini:

'[WB 2.xlsx]Helaian2'!$A$2:$A$5

Sebelum anda memasukkan formula, tutup buku kerja kedua. Jika tidak, anda akan mendapat ralat.

B. Bagaimanakah Anda Menggunakan Fungsi COUNTIF untuk Mencari Pendua Merentasi Lembaran Kerja?

Fungsi COUNTIF dalam Excel digunakan untuk mengira bilangan sel dalam julat tertentu yang memenuhi kriteria tertentu.

Untuk membandingkan berbilang helaian, anda boleh mengira bilangan sel dalam lembaran kerja kedua yang sepadan dengan sel dalam lembaran kerja pertama.

Ini ialah sintaks fungsi:

=COUNTIF(julat, kriteria)

• Julat : julat sel yang anda ingin kira berdasarkan kriteria yang ditentukan.

• Kriteria : syarat yang mesti dipenuhi untuk sel dikira.

Untuk menggunakan fungsi dengan data sampel, ikuti langkah berikut:

1. Pilih sel B2 untuk memaparkan hasil perbandingan pertama.

2. Taip formula ini: =COUNTIF(Helaian2!$A$2:$A$5, A2)

3. Tekan Enter untuk memaparkan hasil perbandingan.

4. Isikan formula untuk membandingkan nilai untuk baris yang lain dalam helaian pertama.

Fungsi ini akan mencari satu padanan untuk beberapa sel dan tiada untuk yang lain. Sel perbandingan memaparkan kiraan. Berikut adalah hasil daripada data sampel:

Fungsi COUNTIF boleh digunakan untuk tugas berguna lain seperti mengira sel bukan kosong dalam Excel .

C. Bagaimanakah Anda Menggunakan Fungsi EXACT untuk Mencari Pendua Merentas Lembaran Kerja?

Fungsi EXACT dalam Excel juga boleh digunakan untuk mencari pendua dalam sel yang sama dalam dua lembaran kerja Excel yang berbeza. Sintaksnya ialah:

=EXACT(teks1, teks2)

• text1 ialah rentetan teks pertama yang anda mahu bandingkan.

• text2 ialah rentetan teks kedua yang anda ingin bandingkan.

Ikut langkah-langkah ini:

1. Pilih sel B2.

2. Taip formula =EXACT(A2, Sheet2!A2)

3. Tekan Enter untuk memaparkan hasil perbandingan. Formula akan mengembalikan TRUE jika kedua-dua nilai adalah sama atau FALSE sebaliknya.

4. Isikan formula untuk membandingkan nilai untuk baris yang lain dalam helaian pertama.

Ambil perhatian bahawa kaedah ini tidak mencari pendua merentas julat sel. Sebaliknya, anda hanya mencari padanan berdasarkan sel yang sama dalam helaian yang berbeza.

Ini boleh berguna dengan data tersusun di mana anda hanya mengharapkan beberapa pengecualian.

Fungsi VLOOKUP, COUNTIF dan EXACT berguna untuk mencari pendua, tetapi Excel ialah program serba boleh dan terdapat pilihan lain. Dalam bahagian seterusnya, kami melihat cara anda boleh menggunakan pemformatan bersyarat untuk mengenal pasti pendua dalam dua helaian.

2. Cara Menggunakan Pemformatan Bersyarat untuk Baris Pendua

Dalam bahagian ini, anda akan belajar cara menggunakan pemformatan bersyarat untuk mencari dan menyerlahkan baris pendua dalam dua lembaran kerja Excel.

Untuk membuat peraturan pemformatan bersyarat, ikut langkah berikut:

1. Pilih julat sel yang mengandungi data (A2:A5 dalam kes ini).

2. Klik pada tab "Rumah" dalam reben Excel.

3. Klik pada "Pemformatan Bersyarat" dalam kumpulan "Gaya".

4. Pilih "Peraturan Baharu" daripada menu lungsur.

Tugas seterusnya ialah menyediakan formula untuk digunakan oleh peraturan anda. Ikut langkah-langkah ini:

1. Pilih "Gunakan formula untuk menentukan sel yang hendak diformat" dalam kotak dialog

2. Masukkan formula berikut: =COUNTIF(Helaian2!$A$2:$A$5, A2) > 0

Akhir sekali, anda menggunakan pemformatan yang anda suka untuk sel pendua.

1. Klik pada butang Format untuk membuka kotak dialog Format Sel.

2. Pilih format cth isi pendua dengan warna latar belakang kuning.

3. Klik OK.

Data pendua anda kini diserlahkan dengan warna kuning.

Cara Menggunakan Pengurus Peraturan Pemformatan Bersyarat

Setelah anda membuat peraturan pemformatan bersyarat, anda boleh mengurusnya menggunakan Pengurus Peraturan Pemformatan Bersyarat.

Untuk mengakses pengurus:

1. Pergi ke tab Laman Utama.

2. Klik pada Pemformatan Bersyarat.

3. Pilih "Urus Peraturan".

Anda akan melihat senarai semua peraturan pemformatan bersyarat yang digunakan pada helaian yang dipilih. Anda boleh mengedit, memadam atau menukar susunan peraturan dengan memilih peraturan dan mengklik butang yang sesuai.

Untuk menggunakan peraturan yang sama pada helaian lain, ikut langkah berikut:

1. Pilih julat yang ingin anda bandingkan dalam helaian kedua.

2. Pergi ke Pengurus Peraturan Pemformatan Bersyarat.

3. Pilih peraturan, klik pada Peraturan Pendua dan kemudian tekan Edit Peraturan.

4. Gantikan "Sheet2" dengan nama helaian pertama untuk dibandingkan.

Memandangkan anda telah menggunakan peraturan pemformatan bersyarat pada kedua-dua helaian, pendua akan diserlahkan mengikut pemformatan yang telah anda pilih.

Pastikan anda melaraskan julat dan rujukan sel dalam formula seperti yang diperlukan untuk merangkumi semua data yang ingin anda bandingkan.

Pemformatan bersyarat mungkin kelihatan sedikit primitif. Jika anda mahukan kawalan yang lebih halus, maka Power Query mungkin jawapannya! Dalam bahagian seterusnya, kami membincangkan cara anda boleh menggunakan Power Query untuk mencari pendua.

3. Cara Menggunakan Power Query untuk Mencari Pendua Merentas Lembaran Kerja

Power Query ialah alat transformasi data dan penyediaan data dalam Microsoft Excel. Mengenal pasti nilai yang sama hanyalah satu daripada banyak tugas analisis yang boleh anda lakukan dengan alat tersebut.

Untuk berbuat demikian, anda harus terlebih dahulu mengimport data dalam dua lembaran kerja ke dalam jadual berasingan. Ikuti langkah-langkah ini dalam setiap helaian:

1. Klik kanan julat sel.

2. Pilih "Dapatkan Data daripada Jadual/Julat".

3. Pindaan nama jadual kepada sesuatu yang sesuai.

Selepas mengimport kedua-dua helaian, tugas pertama adalah untuk menggabungkan data:

1. Pergi ke tab Data.

2. Klik "Dapatkan Data".

3. Pilih "Gabungkan Pertanyaan".

4. Pilih "Gabung" dan pilih dua jadual.

5. Klik pada dua lajur utama.

6. Pilih "Inner" sebagai "Join Jenis" dan klik OK.

Editor Power Query akan dibuka dengan data gabungan daripada kedua-dua jadual dalam helaian Excel anda. Anda akan melihat dua lajur, satu daripada setiap jadual. Memandangkan anda hanya berminat dengan nilai pendua, anda boleh mengalih keluar lajur kedua.

Anda boleh mengklik "Tutup & Muatkan" dalam Editor Pertanyaan Kuasa untuk memuatkan pendua ke lembaran kerja baharu.

Untuk meneroka lebih banyak aspek ciri berkuasa ini, ikuti contoh dalam artikel kami tentang cara menggunakan Power Query dalam Excel .

Excel juga mempunyai alat dan alat tambah pihak ketiga yang menambahkan keupayaan untuk mencari pendua dengan lancar, jadi mari kita lihat beberapa alat tersebut dalam bahagian seterusnya.

4. Alat dan Tambahan untuk Mengenalpasti Pendua Merentas Lembaran Kerja

Alat luaran dan tambahan boleh menawarkan fungsi lanjutan yang mungkin tidak tersedia dalam ciri Excel asli. Alat ini boleh menyelaraskan lagi proses membandingkan helaian untuk pendua.

Spreadsheet Compare ialah alat Microsoft yang membolehkan anda membandingkan dua buku kerja bersebelahan, menyerlahkan perbezaan dan mengenal pasti pendua dengan mudah. Anda boleh memuat turunnya dari laman web Microsoft.

Terdapat beberapa tambahan yang boleh anda pasang untuk mengautomasikan proses mencari pendua. Satu contoh ialah " Penyingkiran Pendua ". Untuk memasang tambahan:

1. Pergi ke tab Sisipkan.

2. Klik pada “Get Add-In”.

3. Cari "Pendua".

4. Klik "Tambah" pada alat pilihan anda.

5. Cara Menyemak Pendua Secara Visual dalam Dua Helaian

Jika semuanya gagal, gunakan mata anda! Kotak dialog Susun Windows dalam Excel membolehkan anda melihat berbilang lembaran kerja atau buku kerja sebelah menyebelah.

Walaupun ia tidak mencari pendua secara langsung, ia boleh membantu anda membandingkan data secara visual merentas lembaran kerja atau buku kerja untuk mengesan pendua. Ikut langkah-langkah ini:

1. Klik pada tab "Lihat" dalam reben Excel.

2. Klik pada "Susun Semua" dalam kumpulan "Tetingkap".

3. Pilih pilihan susunan cth "Menegak" atau "Mendatar".

Ini akan memaparkan kedua-dua helaian sama ada bersebelahan atau satu di atas yang lain. Kini anda boleh membandingkan data dalam setiap helaian secara manual untuk mengenal pasti pendua.

Anda perlu menatal melalui data dan memeriksa secara visual setiap nilai untuk mencari padanan.

Ambil perhatian bahawa kaedah ini tidak cekap untuk set data yang besar, kerana ia memerlukan perbandingan manual. Menggunakan kaedah lain dalam artikel ini akan menjadi lebih berkesan untuk mencari pendua dalam set data yang lebih besar.

Dan itulah kaedah terakhir kami yang biasa untuk mencari nilai pendua dalam helaian Excel! Dalam bahagian seterusnya, kami akan memberikan anda beberapa petua untuk menyediakan lembaran kerja anda.

3 Petua untuk Menyediakan Lembaran Kerja Excel Anda

Sebelum anda mula membandingkan berbilang helaian, pastikan anda mempunyai lajur dan baris set data anda berbaris dengan betul.

Pastikan kedua-dua helaian Excel mempunyai struktur yang sama dan nama pengepala yang sama. Jika perlu, anda boleh menyusun semula lajur dalam kedua-dua helaian agar sepadan antara satu sama lain.

Berikut ialah tiga cadangan untuk memastikan perbandingan yang tepat:

1. Susun data anda dalam susunan yang sama dalam kedua-dua helaian. Ini memudahkan fungsi Excel berfungsi dengan berkesan.

2. Normalkan data anda dengan menggunakan pemformatan, penggunaan huruf besar dan jenis data yang konsisten. Ini akan mengelakkan entri yang tidak sepadan disebabkan oleh perbezaan kecil.

3. Alih keluar baris atau lajur kosong yang tidak perlu, kerana ia mungkin mengganggu proses perbandingan.

Anda boleh menilai jumlah pertindihan yang anda ada dalam set data dengan mengira nilai unik. Video ini berjalan melalui beberapa kaedah:

Cara Mengendalikan Ralat dan Ketidakkonsistenan

Ketidakkonsistenan dalam data anda boleh memberi kesan kepada proses perbandingan. Berikut ialah empat petua untuk menyelesaikan ketidakkonsistenan:

1. Semak percanggahan dalam jenis data, seperti mencampurkan teks dan nilai berangka dalam lajur yang sama.

2. Pastikan pemformatan yang konsisten digunakan untuk tarikh, nombor dan jenis data lain.

3. Periksa data anda untuk entri yang hilang atau salah, dan kemas kini jika perlu.

4. Seragamkan singkatan atau konvensyen penamaan yang tidak konsisten dalam set data anda.

Fikiran Akhir

Mencari pendua merentas dua lembaran kerja Excel ialah tugas penting untuk pengurusan dan analisis data, memastikan integriti dan ketepatan data. Excel menawarkan pelbagai teknik untuk mengenal pasti pendua, masing-masing mempunyai kelebihan dan batasannya sendiri.

Pilihan kaedah bergantung pada keperluan pengguna, saiz dan kerumitan set data dan hasil yang diinginkan. Untuk set data yang lebih kecil dan perbandingan mudah, menggunakan VLOOKUP , COUNTIF atau pemformatan bersyarat mungkin mencukupi.

Untuk set data yang lebih besar atau transformasi data yang lebih kompleks, Power Query ialah alat yang berkuasa dan fleksibel yang boleh mengendalikan pelbagai tugas penyediaan data, termasuk mencari pendua.

Untuk menyelesaikannya, membandingkan helaian Excel untuk pendua ialah kemahiran yang sangat berguna untuk dimiliki dalam kotak alat anda. Dengan helah dalam artikel ini, anda boleh melihat pendua yang menjengkelkan itu dan memastikan data anda bersih.

Percayalah kepada kami, apabila anda semakin mahir dalam hal ini, anda akan memudahkan tugas data anda dan menarik perhatian semua orang di sekeliling anda!