Mencari Percanggahan Dalam Excel – 5 Teknik Mudah Diterangkan

Apabila bekerja dengan set data yang besar dalam Microsoft Excel, menghadapi percanggahan dalam data tidak dapat dielakkan. Mengenal pasti dan membetulkan ketidakkonsistenan ini adalah penting untuk memastikan pelaporan dan analisis yang tepat.

Berita baik? Excel datang dengan pelbagai alat dan teknik untuk membantu anda mencari dan menyelesaikan isu ini. Dalam artikel ini, anda akan belajar tentang beberapa kaedah yang paling berkesan. Ini termasuk penapisan, pemformatan bersyarat dan menggunakan formula lanjutan.

Dengan memasukkan teknik ini ke dalam aliran kerja harian anda, anda akan menjadi lebih cekap dalam mengurus dan menganalisis data anda. Akhirnya membawa kepada membuat keputusan yang lebih baik dan hasil yang lebih baik untuk projek anda.

Isi kandungan

• Memahami Percanggahan dalam Excel
• 5 Cara Mencari Percanggahan dalam Excel
• #1 – Mencari Percanggahan melalui Semakan Manual dalam Excel
• #2 – Menggunakan Penapis dalam Excel untuk Mengesan & Membetulkan Data Tidak Konsisten
• 3# – Menyerlahkan Percanggahan dengan Pemformatan Bersyarat
• #4 – Mencari Percanggahan dengan Fungsi Excel Lanjutan
  • Fungsi IF dan IS
  • VLOOKUP, HLOOKUP & XLOOKUP
  • PERLAWANAN
• #5 – Menggunakan Alat Tambah Excel untuk Pengesanan Percanggahan
• Bagaimanakah Anda Boleh Mencipta Peraturan Pengesahan Data untuk Mencegah Percanggahan?
• Kata Akhir Kami

Memahami Percanggahan dalam Excel

Percanggahan dalam baris dan lajur boleh disebabkan oleh ralat input manusia, entri pendua atau penyelewengan dalam pemformatan data.

Perhatikan data berikut. Anda akan perasan bahawa Jantina Walter White dan Wayne Cena salah eja – Mael dan bukannya Lelaki. Sesungguhnya, adalah mudah untuk melihat dan membetulkan ralat dalam senario ini. Tetapi itu tidak berlaku dengan hamparan yang mengandungi beribu-ribu baris & kol.

Untuk mencari dan mengurus percanggahan ini, anda boleh menggunakan ciri Excel berikut :

• Penapis: Gunakan penapis pada lajur tertentu untuk melihat nilai unik dan cepat mengesan data yang tidak konsisten.
• Pemformatan Bersyarat: Gunakan peraturan pemformatan untuk menyerlahkan sel yang memenuhi kriteria tertentu atau data tidak sepadan antara lajur.
• Fungsi Excel Lanjutan: Gunakan fungsi seperti MATCH dan VLOOKUP untuk membandingkan set data dan mengenal pasti percanggahan antara mereka.

Dengan memahami cara menggunakan ciri ini dalam Excel, anda boleh mengesan dan membetulkan percanggahan dalam set data anda dengan cekap.

5 Cara Mencari Percanggahan dalam Excel

Terdapat 5 cara utama kami ingin menangani percanggahan dalam Excel:

1. Semakan Manual
2. Penapisan
3. Pemformatan Bersyarat
4. Fungsi Excel Lanjutan
5. Tambahan Excel

Mari kita jalani setiap satu daripada mereka supaya anda boleh menggunakan cara terbaik yang sesuai dengan anda.

#1 – Mencari Percanggahan melalui Semakan Manual dalam Excel

Apabila bekerja dengan Excel , semakan manual boleh menjadi kaedah yang berguna untuk mengenal pasti percanggahan dalam data anda. Proses ini memerlukan pemeriksaan yang teliti terhadap set data untuk mengesan sebarang ketidakkonsistenan atau ralat.

Berikut ialah beberapa langkah yang boleh anda ikuti untuk melakukan semakan manual:

• Mulakan dengan memahami dengan jelas set data yang anda gunakan. Biasakan diri anda dengan baris, lajur dan pemformatan.
• Tatal melalui data dan perhatikan maklumat yang dibentangkan dalam setiap sel. Anda boleh menggunakan alatan seperti penyerlahan sel dengan warna isian, pemformatan teks atau pemformatan bersyarat untuk menjadikan data lebih mudah dibaca.
• Bandingkan satu baris dengan yang lain atau satu lajur dengan yang lain. Cari maklumat yang tidak sepadan, entri pendua, dsb. yang mungkin menyebabkan ketidaktepatan dalam helaian Excel anda.
• Semasa anda mengenal pasti percanggahan, buat senarai atau gunakan ulasan untuk mendokumentasikannya. Kemudian, ini boleh menjadi rujukan penting untuk membetulkan isu.

Apabila menyemak data secara manual untuk percanggahan dalam hamparan Excel, kesabaran adalah kuncinya. Proses ini memakan masa. Tetapi ini adalah titik permulaan yang baik untuk mengenal pasti kemungkinan ralat.

#2 – Menggunakan Penapis dalam Excel untuk Mengesan & Membetulkan Data Tidak Konsisten

Salah satu cara paling mudah untuk mengesan data yang tidak konsisten ialah dengan menggunakan Penapis Excel .

Dengan Penapis, kami akan mengasingkan data yang salah dalam lajur Jantina dalam set data berikut:

1. Pilih Isih & Tapis dalam tab Laman Utama .

2. Pilih Penapis – pastikan kursor anda berada dalam julat data.

3. Klik anak panah senarai juntai bawah pada lajur dengan data yang tidak konsisten.

4. Sekarang, nyahpilih semua data padanan yang betul. Dalam kes kami, ia adalah Lelaki . Kemudian, klik OK untuk menyimpan perubahan.

5. Lembaran kerja Excel anda kini hanya akan menunjukkan baris dengan nilai yang salah. Untuk membetulkannya, klik Cari & Pilih dalam tab Laman Utama .

6. Klik Ganti apabila senarai juntai bawah muncul.

7. Taip nilai yang salah dalam Cari apa dan yang betul dalam Ganti dengan . Kemudian, klik Ganti Semua .

8. Kotak dialog akan muncul mengesahkan perubahan. Klik OK untuk meneruskan.

3# – Menyerlahkan Percanggahan dengan Pemformatan Bersyarat

Satu lagi cara untuk mengenal pasti perbezaan lajur dan baris adalah dengan menggunakan pemformatan bersyarat.

Anda boleh mengakses peraturan seperti Sel Serlahkan, Atas/Bawah, Bar Data, Skala Warna & Set Besi. Selain itu, anda boleh membuat peraturan baharu dan mengurus atau mengosongkan peraturan sebelumnya.

Kami akan menggunakan jadual sampel untuk menunjukkan kepada anda kuasa pemformatan bersyarat:

1. Pilih jadual atau baris dan lajur pilihan anda. Kami memilih Lajur A (Syarikat) dan Lajur B (Negara). Kemudian, pilih Pemformatan Bersyarat pada tab Laman Utama .

2. Pilih jenis peraturan dan kemudian peraturan. Kami memilih Peraturan Sel Serlahkan dan Nilai Pendua…

3. Sekarang, kita perlu memilih sama ada untuk menyerlahkan nilai Duplikat atau Unik dan Warna Isi & Fon untuk memformat sel. Setelah selesai, klik OK .

4. Semua nilai pendua kini diserlahkan dengan Isi Merah Muda dengan Teks Merah Gelap.

#4 – Mencari Percanggahan dengan Fungsi Excel Lanjutan

Fungsi IF dan IS

Untuk membandingkan sel dan mengenal pasti perbezaan, anda boleh menggunakan fungsi IF dan IS.

Sebagai contoh, kami telah menggunakan formula =IF(A2=B2,"MATCH","NOT MATCHED")untuk membandingkan sel A2 dan B2. Dan kami telah menyeret formula untuk menggunakannya pada sel lain. Jadi, sel-sel yang sama akan mengembalikan MATCH sebaliknya TIDAK MATCHED .

Anda boleh menggabungkan IF dengan fungsi IS seperti ISNUMBER atau ISTEXT untuk menyemak jenis data tertentu. Sebagai contoh, formula =IF(ISNUMBER(B2), "Number", "NaN")akan mengembalikan Nombor dalam lajur c jika B2 ialah angka dan NaN sebaliknya.

VLOOKUP, HLOOKUP & XLOOKUP

Fungsi VLOOKUP (Menegak) dan HLOOKUP (Mendatar) berguna apabila membandingkan nilai dalam lajur atau baris yang berbeza bagi dua jadual. Untuk melakukannya, gunakan formula berikut: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) atau =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

Gantikan lookup_value dengan nilai yang anda ingin cari dalam jadual lain, table_array dengan julat sel dalam jadual kedua, col_index_num atau row_index_num dengan nombor indeks lajur atau baris yang anda ingin pulangkan nilai daripadanya dan [range_lookup] dengan FALSE untuk padanan tepat atau TRUE untuk padanan anggaran.

Dalam tutorial berikut, kami menggunakan fungsi VLOOKUP pada Lajur E untuk membandingkan Nama Pekerja dalam Lajur A dan Lajur D untuk mengeluarkan Gaji mereka daripada Lajur B jika padanan ditemui.

XLOOKUP ialah versi VLOOKUP dan HLOOKUP yang diperbaharui yang tersedia pada Excel 2021 dan Excel 365. Formula XLOOKUP ialah: =XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]).

Gantikan lookup_value dengan nilai yang anda ingin cari dalam jadual lain, lookup_array dengan julat sel untuk mencari dalam jadual kedua, return_array dengan tatasusunan yang anda mahu pulangkan nilai daripadanya dan [if_nout_found] dengan nilai teks jika tiada padanan nilai ditemui.

Berikut ialah contoh pantas XLOOKUP dengan jadual yang sama:

PERLAWANAN

Fungsi MATCH juga boleh digunakan untuk membandingkan dua senarai untuk percanggahan.

Fungsi MATCH, =MATCH(lookup_value, lookup_array, [match_type]), mencari item yang ditentukan dalam julat dan mengembalikan kedudukan relatif item dalam julat tersebut.

Di sini, kami mencari kedudukan Tembikai dalam tatasusunan A2:A8 menggunakan MATCH. Kami menggunakan match_type sebagai 0 untuk mencari nilai pertama yang betul-betul sama dengan lookup_value .

#5 – Menggunakan Alat Tambah Excel untuk Pengesanan Percanggahan

Excel menawarkan pelbagai alat tambah dan terbina dalam yang boleh membantu anda mengesan dan menganalisis percanggahan dengan lebih cekap. Dalam bahagian ini, kita akan membincangkan beberapa tambahan berguna tersebut.

Mula-mula, pertimbangkan untuk menggunakan tambahan Spreadsheet Inquire . Ini boleh membantu anda membandingkan dua buku kerja dan menyerlahkan perbezaan sel demi sel. Untuk mendayakan tambahan Spreadsheet Inquire:

1. Klik tab Fail .

2. Pilih Pilihan , dan kemudian klik kategori Tambah-Masuk .

3. Dalam kotak Urus , pilih Tambah COM dan klik Pergi .

4. Tandai kotak Spreadsheet Inquire dan klik OK .

5. Setelah didayakan, navigasi ke tab Inquire untuk menggunakan arahan Compare Files .

Satu lagi alat berguna untuk pengesanan percanggahan ialah Analysis ToolPak . Alat tambah ini menyediakan fungsi statistik lanjutan dan alat analisis data, yang boleh berguna apabila menganalisis percanggahan.

Untuk mendayakan Analysis ToolPak, ikuti langkah yang sama seperti mendayakan tambahan Spreadsheet Inquire. Tetapi pilih Add-in Excel dalam kotak Urus dan tandai kotak Analysis ToolPak .

Secara ringkasnya, Excel menawarkan pelbagai tambahan untuk membantu anda mengesan dan mengendalikan percanggahan dalam data anda. Anda boleh meningkatkan ketepatan data anda dengan membiasakan diri anda dengan alatan dan formula ini.

Bagaimanakah Anda Boleh Mencipta Peraturan Pengesahan Data untuk Mencegah Percanggahan?

Untuk membuat peraturan pengesahan data dalam Excel yang boleh membantu mencegah percanggahan, ikut langkah berikut:

1. Pilih sel yang anda ingin hadkan. Ini boleh menjadi satu sel, julat sel atau keseluruhan lajur.

2. Pergi ke tab Data dalam bar alat dan klik butang Pengesahan Data (ditandai dengan dua kotak mendatar, tanda semak hijau dan bulatan berpalang merah).

3. Dalam tetingkap Pengesahan Data, pastikan anda berada pada tab Tetapan . Di sini, anda akan mempunyai peluang untuk menentukan kriteria pengesahan anda berdasarkan keperluan anda.

Terdapat pelbagai jenis kriteria pengesahan yang tersedia dalam Excel, beberapa daripadanya termasuk:

• Nombor bulat – membenarkan hanya nombor bulat dalam julat yang ditentukan.
• Perpuluhan – membenarkan hanya nombor perpuluhan dalam julat yang ditentukan.
• Senarai – mengehadkan input kepada senarai nilai yang boleh diterima yang dipratentukan.
• Tarikh – memerlukan tarikh dalam julat tarikh tertentu.
• Masa – hanya menerima masa dalam julat masa tertentu.
• Panjang teks – menguatkuasakan kekangan pada panjang teks yang dimasukkan.
• Tersuai – membolehkan anda membuat peraturan pengesahan tersuai menggunakan formula Excel.

Selepas memilih kriteria pengesahan yang sesuai, nyatakan parameter seperti yang diperlukan. Sebagai contoh, jika anda memilih Nombor Penuh, anda perlu menetapkan Minimum dan Maksimum dengan pengesah julat Data seperti antara, sama dengan, kurang daripada, dsb.

Selain kriteria pengesahan, anda juga boleh menyediakan mesej ralat tersuai dan pembayang input untuk membantu pengguna memahami dan mematuhi peraturan pengesahan anda. Untuk melakukan ini, tukar ke tab Mesej Input dan Makluman Ralat untuk memasukkan mesej yang anda inginkan.

Dengan melaksanakan peraturan pengesahan data, anda mengurangkan dengan ketara kemungkinan ketidakkonsistenan data dan meningkatkan ketepatan keseluruhan lembaran kerja Excel anda.

Kata Akhir Kami

Dalam artikel ini, anda telah mempelajari pelbagai kaedah untuk mencari dan menangani percanggahan dalam data Excel anda. Mari kita imbas semula strategi utama tersebut:

• Menggunakan lungsur turun Penapis dalam lajur untuk mengenal pasti nilai unik dan data tidak konsisten.
• Menggunakan Pemformatan Bersyarat dalam sel terpilih untuk menyerlahkan percanggahan menggunakan sekumpulan templat peraturan.
• Menggunakan fungsi Excel lanjutan seperti MATCH , VLOOKUP , XLOOKUP , dsb. berfungsi untuk menyemak silang data dalam dua senarai atau jadual.
• Menggunakan alat tambah Excel untuk mengesan percanggahan.
• Menyediakan peraturan pengesahan Data untuk mengelakkan ralat dalam data

Dengan teknik ini dalam set kemahiran Excel anda, data anda akan menjadi lebih tepat dan boleh dipercayai.

Ingatlah untuk mengamalkan kaedah ini dengan kerap untuk menjadi lebih cekap dalam mengesan percanggahan dalam hamparan anda.

Teruskan memperhalusi kemahiran anda dengan mendaftar untuk kami , dan teruskan meneroka ciri dan fungsi baharu untuk menjadi lebih mahir dalam Excel.