Salah satu daripada banyak kemampuan Microsoft Excel adalah kemampuan membandingkan dua senarai data, mengenal pasti padanan antara senarai dan mengenal pasti item mana yang terdapat dalam satu senarai sahaja. Ini berguna semasa membandingkan rekod kewangan atau memeriksa untuk melihat apakah nama tertentu ada dalam pangkalan data. Anda boleh menggunakan fungsi MATCH untuk mengenal pasti dan menandakan rekod yang sepadan atau tidak sepadan, atau anda boleh menggunakan format penyesuaian dengan fungsi COUNTIF. Langkah-langkah berikut memberitahu anda bagaimana menggunakan masing-masing untuk memadankan data anda.
Langkah-langkah
Kaedah 1 dari 2: Mengenal pasti Rekod Dengan Fungsi MATCH
Langkah 1. Salin senarai data ke lembaran kerja tunggal
Excel boleh berfungsi dengan pelbagai lembaran kerja dalam satu buku kerja, atau dengan banyak buku kerja, tetapi anda akan membandingkan senarai dengan lebih mudah jika anda menyalin maklumat mereka ke satu lembaran kerja.
Langkah 2. Berikan setiap item senarai pengecam unik
Sekiranya kedua-dua senarai anda tidak berkongsi cara yang biasa untuk mengenalinya, anda mungkin perlu menambahkan lajur tambahan ke setiap senarai data yang mengenal pasti item tersebut ke Excel sehingga dapat melihat apakah item dalam daftar tertentu berkaitan dengan item dalam senarai yang lain. Sifat pengecam ini akan bergantung pada jenis data yang ingin anda padankan. Anda memerlukan pengecam untuk setiap senarai lajur.
- Untuk data kewangan yang berkaitan dengan jangka waktu tertentu, seperti dengan catatan cukai, ini mungkin merupakan keterangan aset, tarikh aset itu diperoleh, atau keduanya. Dalam beberapa kes, entri boleh dikenali dengan nombor kod; namun, jika sistem yang sama tidak digunakan untuk kedua-dua senarai, pengecam ini dapat membuat pertandingan yang tidak ada atau mengabaikan perlawanan yang harus dibuat.
- Dalam beberapa kes, anda boleh mengambil item dari satu senarai dan menggabungkannya dengan item dari senarai lain untuk membuat pengecam, seperti keterangan aset fizikal dan tahun ia dibeli. Untuk membuat pengecam seperti itu, anda menggabungkan (menambah, menggabungkan) data dari dua atau lebih sel menggunakan ampersand (&). Untuk menggabungkan keterangan item dalam sel F3 dengan tarikh di sel G3, dipisahkan dengan spasi, anda akan memasukkan formula '= F3 & "" & G3' di sel lain dalam baris itu, seperti E3. Sekiranya anda hanya mahu memasukkan tahun dalam pengecam (kerana satu senarai menggunakan tarikh penuh dan yang lain hanya menggunakan tahun), anda akan memasukkan fungsi TAHUN dengan memasukkan '= F3 & "" & TAHUN (G3)' di sel E3 sebagai gantinya. (Jangan sertakan petikan tunggal; hanya ada tanda petikan.)
- Sebaik sahaja anda membuat formula, anda boleh menyalinnya ke semua sel lain dari lajur pengecam dengan memilih sel dengan formula dan menyeret pemegang pengisi ke sel lain lajur di mana anda ingin menyalin formula. Apabila anda melepaskan butang tetikus, setiap sel yang anda seret akan diisi dengan rumus, dengan rujukan sel disesuaikan dengan sel yang sesuai di baris yang sama.
Langkah 3. Menyeragamkan data jika boleh
Sementara akal menyedari bahawa "Inc." dan "Diperbadankan" bermaksud perkara yang sama, Excel tidak melainkan anda memilikinya memformat semula satu perkataan atau yang lain. Begitu juga, anda mungkin menganggap nilai seperti $ 11, 950 dan $ 11, 999.95 cukup dekat untuk dipadankan, tetapi Excel tidak akan melainkan anda memaklumkannya.
- Anda boleh menangani beberapa singkatan, seperti "Co" untuk "Company" dan "Inc" untuk "Incorporated dengan menggunakan fungsi rentetan LEFT untuk memotong watak tambahan. Singkatan lain, seperti" Assn "untuk" Association, "mungkin paling baik ditangani dengan mewujudkan panduan gaya kemasukan data dan kemudian menulis program untuk mencari dan membetulkan format yang tidak betul.
- Untuk rentetan nombor, seperti kod ZIP di mana beberapa entri merangkumi akhiran ZIP +4 dan yang lain tidak, anda sekali lagi boleh menggunakan fungsi rentetan KIRI untuk mengenali dan memadankan hanya kod ZIP utama. Untuk membolehkan Excel mengenali nilai angka yang hampir tetapi tidak sama, anda boleh menggunakan fungsi ROUND untuk membundarkan nilai dekat ke nombor yang sama dan memadankannya.
- Ruang tambahan, seperti menaip dua ruang antara kata dan bukannya satu, dapat dihapus dengan menggunakan fungsi TRIM.
Langkah 4. Buat lajur untuk formula perbandingan
Sama seperti anda harus membuat lajur untuk pengecam senarai, anda perlu membuat lajur untuk formula yang membuat perbandingan untuk anda. Anda memerlukan satu lajur untuk setiap senarai.
Anda mahu melabelkan lajur ini dengan sesuatu seperti "Hilang?"
Langkah 5. Masukkan formula perbandingan di setiap sel
Untuk formula perbandingan, anda akan menggunakan fungsi MATCH yang bersarang di dalam fungsi Excel yang lain, ISNA.
- Rumus tersebut berupa "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", di mana sel lajur pengenal senarai pertama dibandingkan dengan setiap pengecam dalam senarai kedua untuk lihat sama ada ia sesuai dengan salah satu daripadanya. Sekiranya tidak sepadan, catatan tidak ada, dan kata "BENAR" akan dipaparkan di sel itu. Sekiranya ia sepadan, rekod ada, dan perkataan "PALSU" akan dipaparkan. (Semasa memasukkan formula, jangan sertakan petikan yang disertakan.)
- Anda boleh menyalin formula ke sel lajur yang tersisa dengan cara yang sama dengan menyalin formula pengenal sel. Dalam kes ini, hanya rujukan sel untuk sel pengenal yang berubah, kerana meletakkan tanda dolar di hadapan baris dan rujukan lajur untuk sel pertama dan terakhir dalam senarai pengenal sel kedua menjadikannya sebagai rujukan mutlak.
- Anda boleh menyalin formula perbandingan untuk senarai pertama ke sel pertama lajur untuk senarai kedua. Anda kemudian perlu mengedit rujukan sel supaya "G3" diganti dengan rujukan untuk sel pengenal pertama dari senarai kedua dan "$ L $ 3: $ L $ 14" diganti dengan sel pengenal pertama dan terakhir dari senarai kedua. (Biarkan tanda dolar dan titik dua sahaja.) Anda kemudian boleh menyalin formula yang diedit ini ke sel yang selebihnya dalam baris perbandingan senarai kedua.
Langkah 6. Susun senarai untuk melihat nilai yang tidak sepadan dengan lebih mudah, jika perlu
Sekiranya senarai anda besar, anda mungkin perlu menyusunnya untuk mengumpulkan semua nilai yang tidak sepadan. Arahan di bawah ini akan mengubah formula menjadi nilai untuk mengelakkan kesalahan pengiraan semula, dan jika senarai anda besar, akan mengelakkan masa pengiraan semula yang panjang.
- Seret tetikus anda ke semua sel dalam senarai untuk memilihnya.
- Pilih Salin dari menu Edit di Excel 2003 atau dari kumpulan Papan Keratan dari pita Rumah di Excel 2007 atau 2010.
- Pilih Tampal Khas dari menu Edit di Excel 2003 atau dari butang lungsur Tampal di kumpulan Papan klip pita Beranda Excel 2007 atau 2010.
- Pilih "Nilai" dari senarai Tampal Sebagai di kotak dialog Tampal Khas. Klik OK untuk menutup dialog.
- Pilih Urutkan dari menu Data di Excel 2003 atau kumpulan Susun dan Tapis pita Data di Excel 2007 atau 2010.
- Pilih "Baris tajuk" dari senarai "Julat data saya mempunyai" dalam dialog Sort By, pilih "Hilang?" (atau nama yang sebenarnya anda berikan tajuk lajur perbandingan) dan klik OK.
- Ulangi langkah-langkah ini untuk senarai yang lain.
Langkah 7. Bandingkan item yang tidak sepadan secara visual untuk melihat mengapa ia tidak sesuai
Seperti yang dinyatakan sebelum ini, Excel direka untuk mencari padanan data yang tepat kecuali anda menyiapkannya untuk mencari anggaran yang hampir sama. Ketidaksesuaian anda semudah pemindahan huruf atau digit secara tidak sengaja. Ini juga boleh menjadi sesuatu yang memerlukan pengesahan bebas, seperti memeriksa untuk melihat apakah aset yang disenaraikan perlu dilaporkan sejak awal.
Kaedah 2 dari 2: Pemformatan Bersyarat Dengan COUNTIF
Langkah 1. Salin senarai data ke lembaran kerja tunggal
Langkah 2. Tentukan senarai mana yang ingin anda sorot catatan yang sepadan atau tidak sepadan
Sekiranya anda ingin menyerlahkan rekod hanya dalam satu senarai, anda mungkin ingin menonjolkan rekod yang unik untuk senarai itu; iaitu, rekod yang tidak sepadan dengan rekod dalam senarai lain. Sekiranya anda ingin menyerlahkan rekod dalam kedua-dua senarai, anda mesti menonjolkan rekod yang saling sesuai. Untuk tujuan contoh ini, kita akan menganggap senarai pertama mengambil sel G3 hingga G14 dan senarai kedua mengambil sel L3 hingga L14.
Langkah 3. Pilih item dalam senarai yang ingin anda sorot item unik atau sepadan
Sekiranya anda ingin menonjolkan item yang sepadan di kedua-dua senarai, anda harus memilih senarai satu demi satu dan menerapkan formula perbandingan (dijelaskan pada langkah seterusnya) ke setiap senarai.
Langkah 4. Terapkan formula perbandingan yang sesuai
Untuk melakukan ini, anda harus mengakses dialog Pemformatan Bersyarat dalam versi Excel anda. Di Excel 2003, anda melakukannya dengan memilih Pemformatan Bersyarat dari menu Format, sementara di Excel 2007 dan 2010, anda mengklik butang Pemformatan Bersyarat dalam kumpulan Gaya pada pita Rumah. Pilih jenis peraturan sebagai "Formula" dan masukkan formula anda di ruang Edit Perihalan Peraturan.
- Sekiranya anda ingin menonjolkan rekod yang unik untuk senarai pertama, rumusnya adalah "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", dengan julat sel dari senarai kedua yang diberikan sebagai nilai mutlak dan rujukan ke sel pertama senarai pertama sebagai nilai relatif. (Jangan masukkan tanda petikan.)
- Sekiranya anda ingin menonjolkan rekod yang unik untuk senarai kedua, rumusnya adalah "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", dengan julat sel dari senarai pertama yang diberikan sebagai nilai mutlak dan rujukan ke sel pertama senarai kedua sebagai nilai relatif. (Jangan masukkan tanda petikan.)
- Sekiranya anda ingin menyerlahkan catatan dalam setiap senarai yang terdapat di senarai yang lain, anda memerlukan dua formula, satu untuk senarai pertama dan satu untuk yang kedua. Rumus untuk senarai pertama adalah "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", sementara formula untuk senarai kedua adalah COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Seperti dinyatakan sebelumnya, anda memilih senarai pertama untuk menerapkan formula dan kemudian memilih senarai kedua untuk menerapkan formula itu.
- Terapkan format apa sahaja yang anda mahu untuk menyerlahkan rekod yang dibenderakan. Klik OK untuk menutup dialog.
Video - Dengan menggunakan perkhidmatan ini, beberapa maklumat dapat dikongsi dengan YouTube
Petua
- Daripada menggunakan rujukan sel dengan kaedah pemformatan bersyarat COUNTIF, anda boleh memasukkan nilai yang akan dicari dan menandakan satu atau lebih senarai untuk nilai tersebut.
- Untuk mempermudah borang perbandingan, anda boleh membuat nama untuk senarai anda, seperti "List1" dan "List2." Kemudian, semasa menulis formula, nama senarai ini dapat menggantikan julat sel mutlak yang digunakan dalam contoh di atas.
