Bagaimana untuk membandingkan dua senarai dalam Excel

Excel ialah program pemprosesan data yang cekap. Dan salah satu kaedah analisis maklumat ialah perbandingan dua senarai. Jika anda membandingkan dua senarai dengan betul dalam Excel, mengatur proses ini akan menjadi sangat mudah. Cukuplah sekadar mengikuti beberapa perkara yang akan dibincangkan hari ini. Pelaksanaan praktikal kaedah ini bergantung sepenuhnya kepada keperluan orang atau organisasi pada masa tertentu. Oleh itu, beberapa kes yang mungkin perlu dipertimbangkan.

Membandingkan dua senarai dalam Excel

Sudah tentu, anda boleh membandingkan dua senarai secara manual. Tetapi ia akan mengambil masa yang lama. Excel mempunyai kit alat pintar sendiri yang akan membolehkan anda membandingkan data bukan sahaja dengan cepat, tetapi juga untuk mendapatkan maklumat yang tidak begitu mudah diperoleh dengan mata anda. Katakan kita mempunyai dua lajur dengan koordinat A dan B. Beberapa nilai diulang di dalamnya.

Perumusan masalah

Jadi kita perlu membandingkan lajur ini. Prosedur untuk membandingkan dua dokumen adalah seperti berikut:

1. Jika sel unik setiap senarai ini adalah sama, dan jumlah bilangan sel unik adalah sama, dan sel adalah sama, maka senarai ini boleh dianggap sama. Susunan nilai dalam senarai ini disusun tidak begitu penting.
2. Kita boleh bercakap tentang kebetulan separa senarai jika nilai unik itu sendiri adalah sama, tetapi bilangan ulangan adalah berbeza. Oleh itu, senarai sedemikian mungkin mengandungi bilangan elemen yang berbeza.
3. Fakta bahawa kedua-dua senarai tidak sepadan ditunjukkan oleh set nilai unik yang berbeza.

Ketiga-tiga syarat ini secara serentak adalah syarat masalah kita.

Penyelesaian masalah

Mari kita hasilkan dua julat dinamik untuk memudahkan anda membandingkan senarai. Setiap daripada mereka akan sepadan dengan setiap senarai.

Untuk membandingkan dua senarai, lakukan perkara berikut:

1. Dalam lajur yang berasingan, kami membuat senarai nilai unik yang khusus untuk kedua-dua senarai. Untuk ini kami menggunakan formula: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Formula itu sendiri mesti ditulis sebagai formula tatasusunan.
2. Mari tentukan berapa kali setiap nilai unik berlaku dalam tatasusunan data. Berikut adalah formula untuk melakukan ini: =COUNTIF(Senarai1,D5) dan =COUNTI(Senarai2,D5).
3. Jika kedua-dua bilangan ulangan dan bilangan nilai unik adalah sama dalam semua senarai yang disertakan dalam julat ini, maka fungsi mengembalikan nilai 0. Ini menunjukkan bahawa padanan ialah XNUMX%. Dalam kes ini, tajuk senarai ini akan memperoleh latar belakang hijau.
4. Jika semua kandungan unik terdapat dalam kedua-dua senarai, kemudian dikembalikan oleh formula =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) nilainya akan menjadi sifar. Jika E1 tidak mengandungi sifar, tetapi nilai sedemikian terkandung dalam sel E2 dan F2, maka dalam kes ini julat akan diiktiraf sebagai sepadan, tetapi hanya sebahagiannya. Dalam kes ini, tajuk senarai yang sepadan akan bertukar menjadi jingga.
5. Dan jika salah satu daripada formula yang diterangkan di atas mengembalikan nilai bukan sifar, senarai itu akan menjadi tidak sepadan sepenuhnya.

Ini adalah jawapan kepada soalan tentang cara menganalisis lajur untuk padanan menggunakan formula. Seperti yang anda lihat, dengan penggunaan fungsi, anda boleh melaksanakan hampir semua tugas yang, pada pandangan pertama, tidak berkaitan dengan matematik.

Contoh Pengujian

Dalam versi jadual kami, terdapat tiga jenis senarai bagi setiap jenis yang diterangkan di atas. Ia mempunyai padanan sebahagian dan sepenuhnya, serta tidak sepadan.

Untuk membandingkan data, kami menggunakan julat A5:B19, di mana kami memasukkan pasangan senarai ini secara bergilir-gilir. Mengenai apa yang akan menjadi hasil perbandingan, kami akan faham dengan warna senarai asal. Jika mereka berbeza sama sekali, maka ia akan menjadi latar belakang merah. Jika sebahagian daripada data adalah sama, maka kuning. Dalam kes identiti lengkap, tajuk yang sepadan akan berwarna hijau. Bagaimana untuk membuat warna bergantung kepada apa hasilnya? Ini memerlukan pemformatan bersyarat.

Mencari perbezaan dalam dua senarai dalam dua cara

Mari kita terangkan dua lagi kaedah untuk mencari perbezaan, bergantung pada sama ada senarai adalah segerak atau tidak.

Pilihan 1. Senarai Segerak

Ini adalah pilihan yang mudah. Katakan kita mempunyai senarai sedemikian.

Untuk menentukan berapa kali nilai tidak menumpu, anda boleh menggunakan formula: =SUMPRODUCT(—(A2:A20<>B2:B20)). Jika kita mendapat 0 sebagai hasilnya, ini bermakna kedua-dua senarai adalah sama.

Pilihan 2: Senarai Kocok

Jika senarai tidak sama dalam susunan objek yang terkandung di dalamnya, anda perlu menggunakan ciri seperti pemformatan bersyarat dan mewarnakan nilai pendua. Atau gunakan fungsi tersebut COUNTIF, menggunakan yang kita tentukan berapa kali elemen daripada satu senarai berlaku dalam yang kedua.

Cara membandingkan 2 lajur baris demi baris

Apabila kita membandingkan dua lajur, kita selalunya perlu membandingkan maklumat yang berada dalam baris yang berbeza. Untuk melakukan ini, pengendali akan membantu kami JIKA. Mari kita lihat bagaimana ia berfungsi dalam amalan. Untuk melakukan ini, kami membentangkan beberapa situasi ilustrasi.

Contoh. Cara membandingkan 2 lajur untuk padanan dan perbezaan dalam satu baris

Untuk menganalisis sama ada nilai yang berada dalam baris yang sama tetapi lajur yang berbeza adalah sama, kami menulis fungsi tersebut IF. Formula dimasukkan ke dalam setiap baris yang diletakkan dalam lajur tambahan di mana hasil pemprosesan data akan dipaparkan. Tetapi ia sama sekali tidak perlu untuk menetapkannya dalam setiap baris, hanya salin ke dalam sel yang tinggal dalam lajur ini atau gunakan penanda autolengkap.

Kita harus menulis formula sedemikian untuk memahami sama ada nilai dalam kedua-dua lajur adalah sama atau tidak: =IF(A2=B2, “Padan”, “”). Logik fungsi ini sangat mudah: ia membandingkan nilai dalam sel A2 dan B2, dan jika ia sama, ia memaparkan nilai "Bertepatan". Jika data berbeza, ia tidak mengembalikan sebarang nilai. Anda juga boleh menyemak sel untuk melihat sama ada terdapat padanan antara mereka. Dalam kes ini, formula yang digunakan ialah: =IF(A2<>B2, “Jangan padankan”, “”). Prinsipnya adalah sama, pertama pemeriksaan dijalankan. Jika ternyata sel memenuhi kriteria, maka nilai "Tidak sepadan" dipaparkan.

Anda juga boleh menggunakan formula berikut dalam medan formula untuk memaparkan kedua-dua "Padan" jika nilai adalah sama dan "Jangan padan" jika ia berbeza: =IF(A2=B2; “Padan”, “Jangan padankan”). Anda juga boleh menggunakan pengendali ketidaksamaan dan bukannya pengendali kesamarataan. Hanya susunan nilai yang akan dipaparkan dalam kes ini akan sedikit berbeza: =IF(A2<>B2, “Jangan padankan”, “Bertepatan”). Selepas menggunakan versi pertama formula, hasilnya adalah seperti berikut.

Variasi formula ini tidak peka huruf besar-besaran. Oleh itu, jika nilai dalam satu lajur berbeza daripada yang lain hanya kerana ia ditulis dalam huruf besar, maka program tidak akan melihat perbezaan ini. Untuk menjadikan perbandingan sensitif huruf besar-besaran, anda perlu menggunakan fungsi dalam kriteria TEPAT. Selebihnya hujah dibiarkan tidak berubah: =IF(EXACT(A2,B2), “Padan”, “Unik”).

Cara membandingkan berbilang lajur untuk padanan dalam satu baris

Adalah mungkin untuk menganalisis nilai dalam senarai mengikut keseluruhan set kriteria:

1. Cari baris yang mempunyai nilai yang sama di mana-mana.
2. Cari baris yang terdapat padanan dalam dua senarai sahaja.

Mari lihat beberapa contoh bagaimana untuk meneruskan dalam setiap kes ini.

Contoh. Cara mencari padanan dalam satu baris dalam berbilang lajur jadual

Katakan kita mempunyai satu siri lajur yang mengandungi maklumat yang kita perlukan. Kami berhadapan dengan tugas untuk menentukan baris-baris di mana nilainya adalah sama. Untuk melakukan ini, anda perlu menggunakan formula berikut: =IF(AND(A2=B2,A2=C2), “padanan”, ” “).

Jika terdapat terlalu banyak lajur dalam jadual, maka anda hanya perlu menggunakannya bersama-sama dengan fungsi IF operator COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”padanan”;” “). Nombor yang digunakan dalam formula ini menunjukkan bilangan lajur untuk diperiksa. Jika ia berbeza, maka anda perlu menulis seberapa banyak yang benar untuk situasi anda.

Contoh. Cara mencari padanan dalam satu baris dalam mana-mana 2 lajur jadual

Katakan kita perlu menyemak sama ada nilai dalam satu baris sepadan dalam dua lajur daripada nilai dalam jadual. Untuk melakukan ini, anda perlu menggunakan fungsi sebagai syarat OR, di mana secara bergilir-gilir tulis kesamaan setiap lajur dengan yang lain. Berikut adalah contoh.

Kami menggunakan formula ini: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Mungkin terdapat situasi apabila terdapat banyak lajur dalam jadual. Dalam kes ini, formula akan menjadi besar, dan mungkin mengambil banyak masa untuk memilih semua kombinasi yang diperlukan. Untuk menyelesaikan masalah ini, anda perlu menggunakan fungsi tersebut COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Rentetan unik”; “Rentetan bukan unik”)

Kami melihat bahawa secara keseluruhan kami mempunyai dua fungsi COUNTIF. Dengan yang pertama, kami secara bergilir-gilir menentukan bilangan lajur yang mempunyai persamaan dengan A2, dan dengan yang kedua, kami menyemak bilangan persamaan dengan nilai B2. Jika, hasil pengiraan dengan formula ini, kita mendapat nilai sifar, ini menunjukkan bahawa semua baris dalam lajur ini adalah unik, jika lebih banyak, terdapat persamaan. Oleh itu, jika hasil pengiraan dengan dua formula dan menambah hasil akhir kita mendapat nilai sifar, maka nilai teks "Rentetan unik" dikembalikan, jika nombor ini lebih besar, ditulis bahawa rentetan ini tidak unik.

Cara membandingkan 2 lajur dalam Excel untuk padanan

Sekarang mari kita ambil contoh. Katakan kita mempunyai jadual dengan dua lajur. Anda perlu menyemak sama ada ia sepadan. Untuk melakukan ini, anda perlu menggunakan formula, di mana fungsi itu juga akan digunakan IF, dan pengendali COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Tiada padanan dalam lajur B”, “Ada padanan dalam lajur B”)

Tiada tindakan lanjut diperlukan. Selepas mengira keputusan dengan formula ini, kita mendapat jika nilai hujah ketiga fungsi IF perlawanan. Jika tiada, maka isi hujah kedua.

Cara membandingkan 2 lajur dalam Excel untuk padanan dan serlahkan dengan warna

Untuk memudahkan anda mengenal pasti secara visual lajur yang sepadan, anda boleh menyerlahkannya dengan warna. Untuk melakukan ini, anda perlu menggunakan fungsi "Pemformatan Bersyarat". Mari lihat dalam amalan.

Mencari dan menyerlahkan padanan mengikut warna dalam berbilang lajur

Untuk menentukan padanan dan menyerlahkannya, anda mesti terlebih dahulu memilih julat data di mana semakan akan dijalankan, dan kemudian buka item "Pemformatan Bersyarat" pada tab "Rumah". Di sana, pilih "Nilai Pendua" sebagai peraturan pemilihan sel.

Selepas itu, kotak dialog baharu akan muncul, di mana dalam senarai pop timbul kiri kita dapati pilihan "Mengulang", dan dalam senarai kanan kita memilih warna yang akan digunakan untuk pemilihan. Selepas kami mengklik butang "OK", latar belakang semua sel yang mempunyai persamaan akan dipilih. Kemudian hanya bandingkan lajur dengan mata.

Mencari dan menyerlahkan baris yang sepadan

Teknik untuk menyemak sama ada rentetan sepadan sedikit berbeza. Pertama, kita perlu membuat lajur tambahan, dan di sana kita akan menggunakan nilai gabungan menggunakan operator &. Untuk melakukan ini, anda perlu menulis formula borang: =A2&B2&C2&D2.

Kami memilih lajur yang telah dibuat dan mengandungi nilai gabungan. Seterusnya, kami melakukan urutan tindakan yang sama yang diterangkan di atas untuk lajur. Garis pendua akan diserlahkan dalam warna yang anda tentukan.

Kami melihat bahawa tidak ada yang sukar dalam mencari pengulangan. Excel mengandungi semua alat yang diperlukan untuk ini. Adalah penting untuk hanya berlatih sebelum mempraktikkan semua pengetahuan ini.