Setelah menginstal add-on, Anda akan memiliki Tab baru dengan perintah pemanggilan fungsi. Saat Anda mengklik sebuah perintah Perbandingan Rentang kotak dialog muncul untuk memasukkan parameter.

Makro ini memungkinkan Anda untuk membandingkan tabel dengan ukuran berapa pun dan dengan jumlah kolom berapa pun. Tabel dapat dibandingkan pada satu, dua atau tiga kolom sekaligus.

Kotak dialog dibagi menjadi dua bagian: kiri untuk tabel pertama dan kanan untuk tabel kedua.

Untuk membandingkan tabel, lakukan hal berikut:

• Tentukan rentang tabel.
• Setel kotak centang (kotak centang/tanda centang) di bawah rentang tabel yang dipilih jika tabel menyertakan tajuk (baris tajuk).
• Pilih kolom tabel kiri dan kanan yang akan dibandingkan (jika rentang tabel tidak menyertakan header, kolom akan diberi nomor).
• Tentukan jenis perbandingan.
• Pilih opsi keluaran.

Jenis perbandingan tabel

Program ini memungkinkan Anda untuk memilih beberapa jenis perbandingan tabel:

Temukan baris dalam satu tabel yang hilang di tabel lain

Saat memilih jenis ini Program perbandingan mencari baris dalam satu tabel yang hilang di tabel lain. Jika Anda membandingkan tabel dengan beberapa kolom, maka hasil pekerjaan akan menjadi baris di mana setidaknya ada perbedaan di salah satu kolom.

Temukan garis yang cocok

Ketika jenis perbandingan ini dipilih, program menemukan baris yang cocok di tabel pertama dan kedua. Baris yang cocok adalah nilai di kolom perbandingan yang dipilih (1, 2, 3) dari satu tabel benar-benar cocok dengan nilai kolom tabel kedua.

Contoh program dalam mode ini ditunjukkan di sebelah kanan pada gambar.

Cocokkan tabel berdasarkan yang dipilih

Dalam mode perbandingan ini, di depan setiap baris tabel pertama (dipilih sebagai yang utama), data dari baris yang cocok dari tabel kedua disalin. Jika tidak ada baris yang cocok, baris di seberang tabel utama tetap kosong.

Membandingkan tabel dengan empat atau lebih kolom

Jika Anda tidak memiliki fungsionalitas program dan perlu mencocokkan tabel pada empat kolom atau lebih, maka Anda dapat menyiasatinya sebagai berikut:

• Buat kolom kosong di tabel.
• Di kolom baru menggunakan rumus = MENGHUBUNG menggabungkan kolom yang ingin Anda bandingkan.

Jadi Anda akan berakhir dengan 1 kolom yang berisi nilai beberapa kolom. Nah, Anda sudah tahu cara mencocokkan satu kolom.

Mungkin setiap orang yang bekerja dengan data di Excel dihadapkan dengan pertanyaan tentang bagaimana membandingkan dua kolom di Excel untuk kecocokan dan perbedaan. Ada beberapa cara untuk melakukan ini. Mari kita lihat lebih dekat satu per satu.

Bagaimana membandingkan dua kolom di Excel baris demi baris

Saat membandingkan dua kolom data, sering kali perlu membandingkan data di setiap baris individu untuk kecocokan atau perbedaan. Kita dapat membuat analisis seperti itu dengan menggunakan fungsi . Mari kita lihat cara kerjanya dalam contoh di bawah ini.

Contoh 1: Bagaimana membandingkan dua kolom untuk kecocokan dan perbedaan di baris yang sama

Untuk membandingkan data di setiap baris dua kolom di Excel, kami menulis rumus sederhana. Rumus harus dimasukkan ke dalam setiap baris di kolom yang berdekatan, di sebelah tabel tempat data utama berada. Setelah membuat rumus untuk baris pertama tabel, kita dapat meregangkan / menyalinnya ke baris lainnya.

Untuk memeriksa apakah dua kolom dari satu baris berisi data yang sama, kita memerlukan rumus:

=IF(A2=B2, “Cocok”, “”)

Rumus yang menentukan perbedaan antara data dua kolom dalam satu baris akan terlihat seperti ini:

=JIKA(A2<>B2; "Tidak cocok"; “”)

Kami dapat mencocokkan pemeriksaan kecocokan dan perbedaan antara dua kolom pada satu baris dalam satu rumus:

=IF(A2=B2, “cocok”, “tidak cocok”)

=JIKA(A2<>B2; "Tidak cocok"; "Cocok")

Contoh hasil perhitungan mungkin terlihat seperti ini:

Untuk membandingkan data dalam dua kolom dari baris yang sama, peka huruf besar/kecil, gunakan rumus:

=IF(TEPAT(A2,B2), “Cocok”, “Unik”)

Bagaimana membandingkan beberapa kolom untuk kecocokan dalam satu baris di Excel

Excel memiliki kemampuan untuk membandingkan data dalam beberapa kolom dari baris yang sama menggunakan kriteria berikut:

• Temukan baris dengan nilai yang sama di semua kolom tabel;
• Temukan baris dengan nilai yang sama di dua kolom tabel mana pun;

Contoh 1. Bagaimana menemukan kecocokan dalam satu baris di beberapa kolom tabel

Bayangkan tabel kita terdiri dari beberapa kolom dengan data. Tugas kita adalah menemukan baris yang nilainya cocok di semua kolom. Fungsi Excel dan . Rumus untuk menentukan kecocokan adalah:

=IF(DAN(A2=B2,A2=C2), “cocok”, ” “)

Jika tabel kita memiliki banyak kolom, maka akan lebih mudah untuk menggunakan fungsi yang dikombinasikan dengan:

=IF(COUNTIF($A2:$C2,$A2)=3;”cocok”;” “)

Dalam rumus, "5" menunjukkan jumlah kolom tabel yang rumusnya kita buat. Jika tabel Anda memiliki lebih banyak atau lebih sedikit kolom, maka nilai ini harus sama dengan jumlah kolom.

Contoh 2: Bagaimana menemukan kecocokan dalam satu baris di dua kolom tabel?

Bayangkan bahwa tugas kita adalah mengidentifikasi dari tabel dengan data dalam beberapa kolom, baris-baris di mana data cocok atau berulang dalam setidaknya dua kolom. Fungsi dan akan membantu kami dalam hal ini. Mari kita menulis rumus untuk tabel yang terdiri dari tiga kolom dengan data:

=IF(ATAU(A2=B2;B2=C2;A2=C2);”cocok”;” “)

Dalam kasus di mana ada terlalu banyak kolom di tabel kami, rumus kami dengan fungsi akan sangat besar, karena dalam parameternya kami perlu menentukan kriteria kecocokan antara setiap kolom tabel. Cara yang lebih mudah, dalam hal ini, adalah dengan menggunakan .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “String unik”; “Bukan string unik”)

=IF(COUNTIF($B:$B,$A5)=0, “Tidak ada kecocokan di kolom B”, “Ada kecocokan di kolom B”)

Rumus ini memeriksa nilai di kolom B terhadap data sel di kolom A.

Jika tabel Anda memiliki jumlah baris yang tetap, Anda dapat menentukan rentang yang jelas dalam rumus (misalnya, $B2:$B10). Ini akan mempercepat formula.

Bagaimana membandingkan dua kolom di Excel untuk kecocokan dan sorot dengan warna

Saat mencari kecocokan antara dua kolom di Excel, kita mungkin perlu memvisualisasikan kecocokan atau perbedaan yang ditemukan dalam data, misalnya dengan menggunakan penyorotan warna. Cara termudah untuk menyorot kecocokan dan perbedaan adalah dengan menggunakan Pemformatan Bersyarat di Excel. Mari kita lihat bagaimana melakukannya dalam contoh di bawah ini.

Menemukan dan menyorot kecocokan berdasarkan warna di beberapa kolom di Excel

Dalam kasus di mana kita perlu menemukan kecocokan di beberapa kolom, maka untuk ini kita perlu:

• Pilih kolom dengan data yang ingin Anda hitung kecocokannya;
• Pada tab "Beranda" di Bilah Alat, klik item menu "Pemformatan Bersyarat" -> "Aturan Pemilihan Sel" -> "Nilai Duplikat";
• Di kotak dialog pop-up, pilih item "Berulang" di daftar drop-down kiri, di daftar drop-down kanan, pilih warna mana yang akan disorot nilai berulang. Klik tombol "Oke":
• Setelah itu, kecocokan akan disorot di kolom yang dipilih:

Temukan dan sorot baris yang cocok di Excel

Mencari sel yang cocok dengan data dalam dua kolom atau lebih dan mencari kecocokan seluruh baris dengan data adalah konsep yang berbeda. Perhatikan dua tabel di bawah ini:

Tabel di atas berisi data yang sama. Perbedaannya adalah pada contoh di sebelah kiri, kami mencari sel yang cocok, dan di sebelah kanan, kami menemukan seluruh baris berulang dengan data.

Pertimbangkan cara menemukan baris yang cocok dalam tabel:

• Di sebelah kanan tabel dengan data, kami akan membuat kolom tambahan, di mana, di seberang setiap baris dengan data, kami akan meletakkan rumus yang menggabungkan semua nilai baris tabel menjadi satu sel:

=A2&B2&C2&D2

Di kolom bantu, Anda akan melihat data gabungan dari tabel:

Sekarang, untuk menentukan baris yang cocok dalam tabel, lakukan langkah-langkah berikut:

• Pilih area dengan data di kolom bantu (dalam contoh kami, ini adalah rentang sel E2:E15 );
• Pada tab "Beranda" di Bilah Alat, klik item menu "Pemformatan Bersyarat" -> "Aturan Pemilihan Sel" -> "Nilai Duplikat";
• Di kotak dialog pop-up, pilih "Duplikat" di daftar drop-down kiri, di daftar drop-down kanan, pilih warna mana nilai duplikat akan disorot. Klik tombol "Oke":
• Setelah itu, baris duplikat akan disorot di kolom yang dipilih:

Kadang-kadang menjadi perlu untuk membandingkan dua file MS Excel. Ini mungkin menemukan perbedaan harga untuk posisi tertentu atau mengubah pembacaan apa pun, tidak masalah, yang utama adalah menemukan perbedaan tertentu.

Tidak berlebihan untuk menyebutkan bahwa jika ada beberapa catatan dalam file MS Excel, maka tidak ada gunanya menggunakan otomatisasi. Jika file memiliki beberapa ratus, atau bahkan ribuan catatan, maka Anda tidak dapat melakukannya tanpa bantuan daya komputasi komputer.

Mari kita simulasikan situasi di mana dua file memiliki jumlah baris yang sama, dan perbedaan harus dicari di kolom tertentu atau di beberapa kolom. Situasi seperti itu dimungkinkan, misalnya, jika perlu membandingkan harga barang menurut dua daftar harga, atau membandingkan pengukuran atlet sebelum dan sesudah musim pelatihan, meskipun untuk otomatisasi seperti itu harus ada banyak. .

Sebagai contoh kerja, mari kita ambil file dengan kinerja peserta fiktif: lari 100 meter, lari 3000 meter, dan pull-up. File pertama adalah pengukuran di awal musim, dan yang kedua adalah akhir musim.

Cara pertama untuk menyelesaikan masalah. Solusinya hanya dengan menggunakan rumus MS Excel.

Karena catatan disusun secara vertikal (konstruksi paling logis), maka perlu menggunakan . Dalam hal menggunakan penempatan catatan horizontal, Anda harus menggunakan fungsi.

Untuk membandingkan performa lari 100m, rumusnya adalah sebagai berikut:
=IF(VLOOKUP($B2;Sheet2!$B$2:$F$13;3;TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13;3;TRUE);"Tidak ada perbedaan")
Jika tidak ada perbedaan akan muncul pesan tidak ada perbedaan, jika ada maka indikator awal musim dikurangi dengan nilai akhir musim.

Rumus lari 3000 meter adalah sebagai berikut:
=IF(VLOOKUP($B2;Sheet2!$B$2:$F$13;4;TRUE)<>E2;"Ada perbedaan";"Tidak ada perbedaan")
Jika akhir dan nilai awal tidak sama, pesan yang sesuai akan ditampilkan. Rumus untuk pull-up bisa mirip dengan yang sebelumnya, tidak masuk akal untuk memberikannya tambahan. File terakhir dengan perbedaan yang ditemukan ditunjukkan di bawah ini.

Sedikit penjelasan. Untuk kenyamanan pembacaan rumus, data dari dua file dipindahkan ke satu (ke lembar yang berbeda), tetapi ini tidak dapat dilakukan.

Video membandingkan dua file MS Excel menggunakan fungsi dan.

Cara kedua untuk menyelesaikan masalah. Solusi menggunakan MS Access.

Tugas dapat diselesaikan dengan terlebih dahulu mengimpor file MS Excel ke Access. Adapun metode mengimpor data eksternal itu sendiri, tidak ada perbedaan untuk menemukan bidang yang berbeda (salah satu opsi yang disajikan akan berfungsi).

Yang terakhir adalah koneksi file excel dan Access, sehingga saat Anda mengubah data di file Excel, perbedaan akan ditemukan secara otomatis saat Anda menjalankan kueri di MS Access.

Langkah selanjutnya setelah import adalah membuat relasi antar tabel. Sebagai bidang penjilidan, pilih bidang unik "No. p / p".
Langkah ketiga adalah membuat kueri pemilihan sederhana menggunakan pembuat kueri.

Di kolom pertama, kami menunjukkan catatan mana yang perlu ditampilkan, dan di kolom kedua - dalam kondisi apa catatan akan ditampilkan. Secara alami, untuk bidang kedua dan ketiga, tindakannya akan serupa.

Video membandingkan file MS ke Excel menggunakan MS Access.

Sebagai hasil dari manipulasi yang dilakukan, semua catatan ditampilkan, dengan data yang berbeda di lapangan: "Berlari 100 meter." File MS Access disajikan di bawah ini (sayangnya, SkyDrive tidak mengizinkan Anda untuk menyematkan sebagai file Excel)

Berikut adalah dua cara untuk menemukan perbedaan dalam tabel MS Excel. Masing-masing memiliki kelebihan dan kekurangan. Jelas bahwa ini bukan daftar lengkap perbandingan antara dua file Excel. Kami menunggu saran Anda di komentar.

Artikel ini memberikan jawaban atas pertanyaan-pertanyaan berikut:

• Bagaimana cara membandingkan dua tabel di Excel?
• Bagaimana membandingkan tabel kompleks di Excel?
• Bagaimana membandingkan tabel di Excel menggunakan Fungsi VLOOKUP()?
• Bagaimana cara membentuk pengidentifikasi baris unik jika keunikannya awalnya ditentukan oleh sekumpulan nilai dalam beberapa kolom?
• Bagaimana cara memperbaiki nilai sel dalam rumus saat menyalin rumus?

Saat bekerja dengan sejumlah besar informasi, pengguna mungkin dihadapkan pada tugas seperti membandingkan dua sumber data tabel. Saat menyimpan data di sistem terpadu akuntansi (misalnya, sistem berdasarkan 1C Enterprise, sistem yang menggunakan database SQL data), untuk membandingkan data, kemampuan yang dibangun ke dalam sistem atau DBMS dapat digunakan. Sebagai aturan, untuk ini cukup melibatkan programmer yang akan menulis kueri ke database, atau mesin laporan terprogram. Dapat menangani permintaan pengguna berpengalaman Kemahiran dalam menulis kueri 1C atau SQL.

Masalah dimulai ketika perlu untuk melakukan tugas membandingkan data secara mendesak, dan melibatkan programmer dan menulis permintaan atau laporan program tepat waktu dapat melebihi tenggat waktu yang ditetapkan untuk menyelesaikan tugas. Masalah lain yang sama umum adalah kebutuhan untuk membandingkan informasi dari berbagai sumber. Dalam hal ini, pernyataan tugas untuk programmer akan terdengar seperti integrasi dua sistem. Solusi dari masalah seperti itu akan membutuhkan kualifikasi programmer yang lebih tinggi dan juga akan membutuhkan lebih banyak waktu daripada pengembangan dalam satu sistem.

Untuk memecahkan masalah yang ditunjukkan, teknik yang ideal adalah menggunakan tabel tabel untuk membandingkan data. editor Microsoft Unggul. Sistem akuntansi manajerial dan teregulasi yang paling umum mendukung pengunggahan ke format excel. Tugas ini hanya memerlukan kualifikasi tertentu dari pengguna untuk bekerja dengan office suite ini dan tidak memerlukan keterampilan pemrograman.

Pertimbangkan memecahkan masalah membandingkan tabel di Excel menggunakan contoh. Kami memiliki dua tabel yang berisi daftar apartemen. Bongkar sumber - 1C Enterprise (akuntansi untuk konstruksi) dan tabel di Excel (akuntansi untuk penjualan). Meja terletak di buku kerja Excel pada lembar pertama dan kedua, masing-masing.

Kita dihadapkan pada tugas untuk membandingkan daftar ini berdasarkan alamat. Di tabel pertama - semua apartemen di rumah. Di tabel kedua - hanya dijual apartemen dan nama pembeli. Tujuan utamanya adalah untuk menampilkan di tabel pertama untuk setiap apartemen nama pembeli (untuk apartemen yang telah terjual). Tugas diperumit oleh fakta bahwa alamat apartemen di setiap tabel adalah alamat gedung dan terdiri dari beberapa bidang: 1) alamat gedung (rumah), 2) bagian (pintu masuk), 3) lantai, 4) nomor di lantai (misalnya, dari 1 hingga 4) .

Untuk membandingkan dua tabel excel kita perlu memastikan bahwa di kedua tabel setiap baris akan diidentifikasi oleh satu bidang, bukan empat. Anda bisa mendapatkan bidang seperti itu dengan menggabungkan nilai dari empat bidang alamat dengan fungsi Concatenate(). Tujuan dari fungsi Concatenate() adalah untuk menggabungkan beberapa nilai teks menjadi satu string. Nilai dalam fungsi dicantumkan melalui simbol ";". Alamat sel dan teks arbitrer yang ditentukan dalam tanda kutip dapat bertindak sebagai nilai.

Langkah 1. Masukkan kolom kosong "A" di awal tabel pertama dan tulis di sel kolom ini di seberang baris pertama dengan data rumus:
=CONCATENATE(B3,"-";C3;"-";D3;"-";E3)
Untuk kenyamanan persepsi visual antara nilai-nilai sel yang digabungkan, kami telah menetapkan simbol "-".

Langkah 2 Salin rumus ke sel berikut di kolom A.

Langkah 4 Untuk membandingkan tabel Excel berdasarkan nilai, gunakan fungsi VLOOKUP(). Tujuan dari fungsi VLOOKUP() adalah untuk mencari nilai di kolom paling kiri dari tabel dan mengembalikan nilai sel di kolom tertentu dari baris yang sama. Parameter pertama adalah nilai yang diinginkan. Parameter kedua adalah tabel di mana nilai akan dicari. Parameter ketiga adalah jumlah kolom, dari sel yang nilainya akan dikembalikan di baris yang ditemukan. Parameter keempat adalah jenis pencarian: false - pencocokan tepat, benar - pencocokan perkiraan. Karena informasi keluaran harus ditempatkan di tabel pertama (di dalamnya harus ditambahkan nama pembeli), kami akan menulis rumus di dalamnya. Mari kita bentuk rumus di kolom bebas di sebelah kanan tabel di seberang baris pertama data:
=VLOOKUP(A3;Lembar2!$A$3:$F$10;6;FALSE)
Saat menyalin rumus, Excel "pintar" secara otomatis mengubah pengalamatan sel. Dalam kasus kami, nilai yang diinginkan untuk setiap baris akan berubah: A3, A4, dll., dan alamat tabel tempat pencarian dilakukan harus tetap tidak berubah. Untuk melakukan ini, kami memperbaiki sel di parameter alamat tabel dengan simbol "$". Alih-alih "Sheet2!A3:F10" kami melakukan "Sheet2!$A$3:$F$10".