perbandingan data excel. Bagaimana membandingkan dua kolom di Excel untuk pertandingan

Artikel ini akan membawa Anda sekitar 10 menit untuk membaca. Dalam 5 menit berikutnya, Anda dapat dengan mudah membandingkan dua kolom di Excel dan mencari tahu apakah ada duplikat di dalamnya, menghapusnya atau menyorotnya dengan warna. Jadi waktunya telah tiba!

Excel adalah aplikasi yang sangat kuat dan sangat keren untuk membuat dan memanipulasi data dalam jumlah besar. Jika Anda memiliki beberapa buku kerja dengan data (atau hanya satu tabel besar), maka Anda mungkin ingin membandingkan 2 kolom, menemukan nilai duplikat, dan kemudian mengambil beberapa tindakan dengannya, misalnya, menghapus, menyorot, atau menghapus konten. ... Kolom dapat berada pada tabel yang sama, bersebelahan atau tidak bersebelahan, dapat terletak pada 2 lembar yang berbeda atau bahkan pada buku yang berbeda.

Bayangkan kita memiliki 2 kolom nama orang - 5 nama dalam satu kolom SEBUAH dan 3 nama dalam satu kolom B... Penting untuk membandingkan nama-nama di dua kolom ini dan menemukan duplikatnya. Seperti yang Anda pahami, ini adalah data fiktif, yang diambil semata-mata sebagai contoh. Dalam tabel nyata, kita berurusan dengan ribuan, jika tidak puluhan ribu catatan.

Opsi A: kedua kolom berada pada lembar yang sama. Misalnya kolom SEBUAH dan kolom B.

Opsi B: Kolom terletak di lembar yang berbeda. Misalnya kolom SEBUAH di atas lembaran Lembar2 dan kolom SEBUAH di atas lembaran Lembar3.

Excel 2013, 2010 dan 2007 memiliki alat bawaan Hapus Duplikat(Hapus duplikat), tetapi tidak berdaya dalam situasi seperti itu karena tidak dapat membandingkan data dalam 2 kolom. Selain itu, itu hanya dapat menghapus duplikat. Tidak ada pilihan lain seperti menyorot atau mengubah warna. Dan intinya!

Bandingkan 2 kolom di Excel dan temukan catatan duplikat menggunakan rumus

Opsi A: kedua kolom berada di lembar yang sama

Cepat: Dalam spreadsheet besar, menyalin rumus akan lebih cepat jika Anda menggunakan pintasan keyboard. Pilih sel C1 dan tekan Ctrl + C(untuk menyalin rumus ke clipboard), lalu tekan Ctrl + Shift + Akhir(untuk memilih semua sel yang tidak kosong di kolom C) dan terakhir tekan Ctrl + V(untuk menyisipkan rumus ke semua sel yang dipilih).

Opsi B: dua kolom berada di lembar yang berbeda (dalam buku yang berbeda)

Memproses duplikat yang ditemukan

Bagus, kami menemukan entri di kolom pertama yang juga ada di kolom kedua. Sekarang kita perlu melakukan sesuatu dengan mereka. Melihat semua catatan duplikat dalam tabel secara manual agak tidak efisien dan memakan waktu. Ada cara yang lebih baik.

Tampilkan hanya baris duplikat di kolom A

Jika kolom Anda tidak memiliki header, Anda perlu menambahkannya. Untuk melakukan ini, letakkan kursor pada nomor yang mewakili baris pertama, dan itu akan berubah menjadi panah hitam, seperti yang ditunjukkan pada gambar di bawah ini:

Klik kanan dan pilih Memasukkan(Memasukkan):

Beri nama kolom, misalnya, “ Nama"Dan" Duplikat?”Lalu buka tabnya Data(Data) dan tekan Saring(Saring):

Kemudian klik panah abu-abu kecil di sebelah “ Duplikat?“Untuk memperluas menu filter; hapus centang semua item dalam daftar ini kecuali Duplikat, dan tekan baik.

Itu saja, sekarang Anda hanya melihat elemen-elemen kolom itu TAPI yang digandakan di kolom DI... Hanya ada dua sel seperti itu di tabel pelatihan kami, tetapi, seperti yang Anda pahami, dalam praktiknya ada lebih banyak lagi.

Untuk menampilkan semua baris kolom lagi TAPI, klik simbol filter di kolom DI yang sekarang terlihat seperti corong dengan panah kecil dan pilih Pilih Semua(Pilih Semua). Atau, Anda dapat melakukan hal yang sama melalui Umpan dengan mengklik Data(Data)> Pilih & Filter(Urut & Saring)> Bersih(Hapus) seperti yang ditunjukkan pada tangkapan layar di bawah ini:

Ubah warna atau sorot duplikat yang ditemukan

Jika tanda “ Duplikat”Tidak cukup untuk tujuan Anda, dan Anda ingin menandai sel berulang dengan warna font yang berbeda, warna isian, atau dengan cara lain ...

Dalam hal ini, saring duplikat seperti yang ditunjukkan di atas, pilih semua sel yang difilter dan klik Ctrl + 1 untuk membuka dialog Format Sel Cell(Format sel). Sebagai contoh, mari kita ubah warna isian sel di garis duplikat menjadi kuning cerah. Tentu saja, Anda dapat mengubah warna isian dengan alat ini Mengisi(Isi warna) pada tab Rumah(Home) tetapi keuntungan dari kotak dialog dialog Format Sel Cell(Format sel) adalah Anda dapat mengonfigurasi semua opsi pemformatan secara bersamaan.

Sekarang Anda pasti tidak akan melewatkan satu sel pun dengan duplikat:

Menghapus nilai duplikat dari kolom pertama

Filter tabel sehingga hanya sel dengan nilai duplikat yang ditampilkan, dan pilih sel tersebut.

Jika 2 kolom yang Anda bandingkan berada di lembar yang berbeda, yaitu, di tabel yang berbeda, klik kanan rentang yang dipilih dan pilih Hapus baris(Hapus baris):

Klik baik ketika Excel meminta Anda untuk mengonfirmasi bahwa Anda benar-benar ingin menghapus seluruh baris lembar kerja dan kemudian menghapus filter. Seperti yang Anda lihat, hanya ada baris dengan nilai unik yang tersisa:

Jika 2 kolom berada pada lembar yang sama, berdekatan satu sama lain (adjacent) atau tidak saling berdekatan (not berdekatan), maka proses menghilangkan duplikat akan sedikit lebih sulit. Kami tidak dapat menghapus seluruh baris dengan nilai duplikat, karena ini juga akan menghapus sel dari kolom kedua. Jadi hanya menyisakan entri unik di kolom TAPI, melakukan hal ini:

Seperti yang Anda lihat, menghapus duplikat dari dua kolom di Excel menggunakan rumus tidaklah sulit.

Cukup sering, pengguna Excel dihadapkan dengan tugas membandingkan dua tabel atau daftar untuk mengidentifikasi perbedaan atau item yang hilang. Setiap pengguna mengatasi tugas ini dengan caranya sendiri, tetapi paling sering menghabiskan banyak waktu untuk menyelesaikan masalah ini, karena tidak semua pendekatan untuk masalah ini rasional. Pada saat yang sama, ada beberapa algoritme yang telah terbukti untuk tindakan yang memungkinkan Anda membandingkan daftar atau susunan tabel dalam waktu yang cukup singkat dengan sedikit usaha. Mari kita lihat lebih dekat opsi-opsi ini.

Ada beberapa cara untuk membandingkan area tabel di Excel, tetapi semuanya dapat dibagi menjadi tiga kelompok besar:

perbandingan daftar pada lembar yang sama;

perbandingan tabel yang terletak di lembar yang berbeda;

perbandingan rentang tabel dalam file yang berbeda.

Berdasarkan klasifikasi inilah, pertama-tama, metode perbandingan dipilih, dan tindakan dan algoritma spesifik untuk melakukan tugas ditentukan. Misalnya, saat membuat perbandingan di buku kerja yang berbeda, Anda perlu membuka dua file Excel secara bersamaan.

Selain itu, harus dikatakan bahwa masuk akal untuk membandingkan tablespace hanya ketika mereka memiliki struktur yang serupa.

Metode 1: rumus sederhana

Cara termudah untuk membandingkan data dalam dua tabel adalah dengan menggunakan rumus persamaan sederhana. Jika data cocok, maka akan mengembalikan indikator TRUE, dan jika tidak, maka - FALSE. Anda dapat membandingkan data numerik dan teks. Kerugian dari metode ini adalah hanya dapat digunakan jika data dalam tabel diurutkan atau diurutkan sama, sinkron dan memiliki jumlah baris yang sama. Mari kita lihat bagaimana cara menggunakan metode ini dalam praktik menggunakan contoh dua tabel yang ditempatkan pada satu lembar.

Jadi, kami memiliki dua tabel sederhana dengan daftar karyawan perusahaan dan gaji mereka. Penting untuk membandingkan daftar karyawan dan mengidentifikasi inkonsistensi antara kolom tempat nama ditempatkan.

Untuk ini kita membutuhkan kolom tambahan pada lembar. Kami memasukkan tanda di sana «=» ... Kemudian kita klik pada item pertama yang akan dibandingkan dalam daftar pertama. Pasang simbol lagi «=» dari papan ketik. Selanjutnya, klik pada sel pertama dari kolom yang kita bandingkan di tabel kedua. Hasilnya adalah ekspresi dari tipe berikut:

Meskipun tentu saja dalam setiap kasus koordinatnya akan berbeda, tetapi esensinya akan tetap sama.
Klik pada tombol Memasukkan untuk mendapatkan hasil perbandingan. Seperti yang Anda lihat, saat membandingkan sel pertama dari kedua daftar, program menunjukkan indikator "BENAR", yang berarti kecocokan data.

Sekarang kita perlu melakukan operasi serupa dengan sisa sel dari kedua tabel di kolom yang kita bandingkan. Tetapi Anda cukup menyalin formula, yang secara signifikan akan menghemat waktu. Faktor ini sangat penting ketika membandingkan daftar dengan sejumlah besar string.
Prosedur penyalinan paling mudah dilakukan dengan gagang isian. Arahkan kursor ke sudut kanan bawah sel tempat kami mendapatkan indikator "BENAR"... Dalam hal ini, itu harus diubah menjadi salib hitam. Ini adalah penanda isian. Tekan tombol kiri mouse dan seret kursor ke bawah ke jumlah baris dalam susunan tabel yang dibandingkan.

Seperti yang Anda lihat, sekarang kolom tambahan menampilkan semua hasil membandingkan data dalam dua kolom array tabel. Dalam kasus kami, data tidak cocok hanya dalam satu baris. Saat membandingkannya, rumus memberikan hasil "SALAH"... Untuk semua baris lainnya, seperti yang Anda lihat, rumus perbandingan memberikan indikator "BENAR".

Selain itu, dimungkinkan untuk menghitung jumlah ketidakcocokan menggunakan rumus khusus. Untuk melakukan ini, pilih elemen lembar yang akan ditampilkan. Kemudian klik pada ikon "Sisipkan fungsi".

Di jendela Fungsi Wizards di grup operator "Matematis" sorot namanya SUMPRODUCT... Klik pada tombol "BAIK".

Jendela argumen fungsi diaktifkan SUMPRODUCT, yang tugas utamanya adalah menghitung jumlah produk dari rentang yang dipilih. Tetapi fungsi ini dapat digunakan untuk tujuan kita juga. Sintaksnya cukup sederhana:
SUMPRODUCT (array1; array2; ...)

Secara total, alamat hingga 255 array dapat digunakan sebagai argumen. Tetapi dalam kasus kami, kami hanya akan menggunakan dua array, apalagi, sebagai satu argumen.

Kami menempatkan kursor di lapangan "Array1" dan pilih pada lembar rentang data yang dibandingkan di area pertama. Setelah itu di lapangan kita pasang tanda "tidak sama" (<> ) dan pilih rentang yang dibandingkan dari wilayah kedua. Selanjutnya, kami membungkus ekspresi yang dihasilkan dalam tanda kurung, di depannya kami meletakkan dua tanda «-» ... Dalam kasus kami, kami mendapatkan ekspresi berikut:

- (A2: A7<>D2: D7)

Klik pada tombol "BAIK".

Operator menghitung dan menampilkan hasilnya. Seperti yang Anda lihat, dalam kasus kami, hasilnya sama dengan angka "satu" artinya, ada satu ketidakcocokan ditemukan dalam daftar yang dibandingkan. Jika daftarnya benar-benar identik, maka hasilnya akan sama dengan angkanya «0» .

Dengan cara yang sama, Anda dapat membandingkan data dalam tabel yang terletak di lembar yang berbeda. Tetapi dalam hal ini, diinginkan agar garis-garis di dalamnya diberi nomor. Jika tidak, prosedur perbandingannya hampir sama persis seperti yang dijelaskan di atas, kecuali fakta bahwa saat memasukkan formula, Anda harus beralih antar lembar. Dalam kasus kami, ekspresinya akan terlihat seperti ini:

B2 = Lembar2! B2

Artinya, seperti yang Anda lihat, sebelum koordinat data yang terletak di lembar lain, berbeda dari tempat hasil perbandingan ditampilkan, nomor lembar dan tanda seru ditunjukkan.

Metode 2: pilih grup sel

Perbandingan dapat dilakukan dengan menggunakan alat pemilihan kelompok sel. Ini juga memungkinkan Anda untuk membandingkan hanya daftar yang disinkronkan dan diurutkan. Selain itu, dalam hal ini, daftar harus ditempatkan bersebelahan pada lembar yang sama.

Metode 3: pemformatan bersyarat

Perbandingan dapat dilakukan dengan menggunakan metode pemformatan bersyarat. Seperti pada metode sebelumnya, area yang dibandingkan harus berada di lembar kerja Excel yang sama dan disinkronkan satu sama lain.

Ada cara lain untuk menggunakan pemformatan bersyarat untuk menyelesaikan tugas yang ada. Seperti opsi sebelumnya, ini memerlukan lokasi kedua area yang dibandingkan pada lembar yang sama, tetapi tidak seperti metode yang dijelaskan sebelumnya, kondisi sinkronisasi atau penyortiran data tidak diperlukan, yang membedakan opsi ini dari yang dijelaskan sebelumnya.

Jika diinginkan, Anda dapat, sebaliknya, mewarnai elemen yang tidak cocok, dan membiarkan indikator yang cocok dengan isian dengan warna yang sama. Dalam hal ini, algoritme tindakan praktis sama, tetapi di jendela pengaturan untuk menyorot nilai duplikat di bidang pertama alih-alih parameter "Berulang" parameter harus dipilih "Unik"... Setelah itu, klik tombol "BAIK".

Dengan demikian, indikator yang tidak bertepatan akan disorot.

Metode 4: rumus kompleks

Anda juga dapat membandingkan data menggunakan rumus kompleks berdasarkan fungsi COUNTIF... Dengan alat ini, Anda dapat menghitung berapa banyak setiap elemen dari kolom yang dipilih dari tabel kedua diulang di yang pertama.

Operator COUNTIF termasuk dalam kelompok fungsi statistik. Tugasnya adalah menghitung jumlah sel di mana nilainya memenuhi kondisi tertentu. Sintaks untuk operator ini adalah sebagai berikut:

COUNTIF (rentang, kriteria)

Argumen "Jarak" adalah alamat larik tempat nilai yang cocok dihitung.

Argumen "Kriteria" menentukan kondisi pertandingan. Dalam kasus kami, ini akan mewakili koordinat sel tertentu di area tabel pertama.

Tentu saja, untuk membandingkan indikator tabel, ekspresi ini dapat diterapkan dalam bentuk yang ada, tetapi ada kemungkinan untuk memperbaikinya.

Mari kita buat agar nilai yang ada di tabel kedua, tetapi tidak di tabel pertama, ditampilkan dalam daftar terpisah.

Pertama-tama, mari kita sedikit merevisi formula kita COUNTIF, yaitu, kami akan menjadikannya salah satu argumen dari operator JIKA SEBUAH... Untuk melakukan ini, pilih sel pertama di mana operator berada COUNTIF... Di bilah rumus, sebelum itu, tambahkan ekspresi "JIKA SEBUAH" tanpa tanda kutip dan buka tanda kurung. Selanjutnya, untuk memudahkan kita bekerja, pilih nilai di bilah rumus "JIKA SEBUAH" dan klik pada ikon "Sisipkan fungsi".

Membuka jendela argumen fungsi JIKA SEBUAH... Seperti yang Anda lihat, bidang pertama jendela sudah diisi dengan nilai operator COUNTIF... Tapi kita perlu menambahkan sesuatu yang lain ke bidang ini. Kami mengatur kursor di sana dan menambahkan ekspresi yang sudah ada existing «=0» tanpa tanda kutip.
Setelah itu, pergi ke lapangan "Nilai jika benar"... Di sini kita akan menggunakan satu lagi fungsi bersarang - GARIS... Kami memasukkan kata "GARIS" tanpa tanda kutip, lalu buka tanda kurung dan tunjukkan koordinat sel pertama dengan nama belakang di tabel kedua, lalu tutup tanda kurung. Secara khusus, dalam kasus kami, di lapangan "Nilai jika benar" ekspresi berikut ternyata:

BARIS (D2)

Sekarang operatornya GARIS akan mengkomunikasikan fungsi JIKA SEBUAH jumlah baris di mana nama keluarga tertentu berada, dan dalam kasus ketika kondisi yang ditentukan di bidang pertama akan terpenuhi, fungsi JIKA SEBUAH akan menampilkan nomor ini ke sel. Klik pada tombol "BAIK".

Seperti yang Anda lihat, hasil pertama ditampilkan sebagai "SALAH"... Artinya nilai tersebut tidak memenuhi syarat dari operator JIKA SEBUAH... Artinya, nama keluarga pertama ada di kedua daftar.

Menggunakan penanda isian, salin ekspresi operator dengan cara biasa JIKA SEBUAH seluruh kolom. Seperti yang Anda lihat, untuk dua posisi yang ada di tabel kedua, tetapi tidak di tabel pertama, rumusnya memberikan nomor baris.

Kami mundur dari area tabel ke kanan dan mengisi kolom dengan angka secara berurutan, mulai dari 1 ... Jumlah angka harus sesuai dengan jumlah baris pada tabel perbandingan kedua. Anda juga dapat menggunakan penanda isian untuk mempercepat proses penomoran.

Setelah itu, pilih sel pertama di sebelah kanan kolom dengan angka dan klik ikon "Sisipkan fungsi".

Buka Penyihir fungsi... Masuk ke kategori "Statistik" dan pilih namanya "PALING SEDIKIT"... Klik pada tombol "BAIK".

Fungsi PALING SEDIKIT, yang jendela argumennya telah dibuka, dimaksudkan untuk menampilkan nilai terendah yang ditentukan oleh hitungan.
di lapangan "Himpunan" anda harus menentukan koordinat rentang kolom tambahan "Jumlah kebetulan" yang sebelumnya kita ubah menggunakan fungsi JIKA SEBUAH... Kami membuat semua tautan menjadi mutlak.

di lapangan "K" menunjukkan nilai terkecil yang akan ditampilkan. Di sini kami menunjukkan koordinat sel pertama kolom dengan penomoran, yang baru saja kami tambahkan. Kami meninggalkan alamat relatif. Klik pada tombol "BAIK".

Operator mengeluarkan hasilnya - sebuah angka 3 ... Ini adalah penomoran terkecil dari baris yang tidak cocok dalam array tabel. Menggunakan gagang isian, salin rumus ke bagian paling bawah.

Sekarang, mengetahui nomor baris elemen yang tidak cocok, kita dapat memasukkan ke dalam sel dan nilainya menggunakan fungsi INDEKS... Pilih elemen lembar pertama yang berisi rumus PALING SEDIKIT... Setelah itu, buka bilah rumus dan sebelum namanya "PALING SEDIKIT" tambahkan nama "INDEKS" tanpa tanda kutip, langsung buka tanda kurung dan beri tanda titik koma ( ; ). Kemudian pilih nama di bilah rumus "INDEKS" dan klik pada ikon "Sisipkan fungsi".

Setelah itu, sebuah jendela kecil terbuka di mana Anda perlu menentukan apakah fungsi tersebut harus memiliki formulir referensi INDEKS atau dirancang untuk bekerja dengan array. Kami membutuhkan opsi kedua. Itu diinstal secara default, jadi di jendela ini, cukup klik tombol "BAIK".

Jendela argumen fungsi dimulai INDEKS... Operator ini dimaksudkan untuk menampilkan nilai yang terletak di larik tertentu di baris yang ditentukan.
Seperti yang Anda lihat, bidang "Nomor baris" sudah diisi dengan nilai fungsi PALING SEDIKIT... Perbedaan antara penomoran lembar Excel dan penomoran internal area tabel harus dikurangi dari nilai yang sudah ada di sana. Seperti yang Anda lihat, kami hanya memiliki header di atas nilai tabel. Artinya selisihnya hanya satu garis. Oleh karena itu, kami menambahkan di bidang "Nomor baris" nilai "-satu" tanpa tanda kutip.

di lapangan "Himpunan" tentukan alamat rentang nilai tabel kedua. Dalam hal ini, kami membuat semua koordinat menjadi absolut, yaitu, kami meletakkan tanda dolar di depannya seperti yang telah kami jelaskan.

Klik pada tombol "BAIK".

Setelah menampilkan hasilnya di layar, perluas fungsi menggunakan penanda isian ke ujung kolom ke bawah. Seperti yang Anda lihat, kedua nama keluarga yang ada di tabel kedua, tetapi tidak ada di tabel pertama, ditampilkan dalam rentang terpisah.

Metode 5: membandingkan array di buku yang berbeda

Saat membandingkan rentang di buku kerja yang berbeda, Anda bisa menggunakan metode yang tercantum di atas, kecuali untuk opsi yang mengharuskan kedua ruang tabel ditempatkan pada lembar yang sama. Syarat utama untuk melakukan prosedur perbandingan dalam hal ini adalah membuka jendela kedua file secara bersamaan. Untuk versi Excel 2013 dan yang lebih baru, serta untuk versi sebelum Excel 2007, tidak ada masalah dengan kondisi ini. Namun di Excel 2007 dan Excel 2010, untuk membuka kedua jendela secara bersamaan, diperlukan manipulasi tambahan. Cara melakukan ini dijelaskan dalam pelajaran terpisah.

Seperti yang Anda lihat, ada sejumlah kemungkinan untuk membandingkan tabel satu sama lain. Opsi mana yang akan digunakan bergantung pada di mana tepatnya data tabel berada relatif satu sama lain (pada lembar yang sama, dalam buku yang berbeda, pada lembar yang berbeda), serta pada bagaimana pengguna ingin perbandingan ini ditampilkan di layar.

Katakanlah Anda ingin membandingkan versi buku kerja, menganalisis buku kerja untuk masalah atau inkonsistensi, atau melihat tautan antara buku kerja atau lembar kerja. Jika Microsoft Office 365 atau Office Professional Plus 2013 diinstal di komputer Anda, add-in Tanyakan Lembar Bentang tersedia di Excel.

Anda dapat menggunakan perintah di tab Inquire untuk melakukan semua tugas ini, dan banyak lagi. Tab Inquire pada pita Excel memiliki tombol untuk perintah yang dijelaskan di bawah ini.

Jika Anda tidak melihat Menanyakan di pita Excel, lihat Mengaktifkan add-in Tanyakan Lembar Bentang.

Bandingkan dua buku kerja

Itu Bandingkan File perintah memungkinkan Anda melihat perbedaan, sel demi sel, antara dua buku kerja. Anda harus membuka dua buku kerja di Excel untuk menjalankan perintah ini.

Hasil diberi kode warna berdasarkan jenis konten, seperti nilai yang dimasukkan, rumus, rentang bernama, dan format. Bahkan ada jendela yang dapat menampilkan perubahan kode VBA baris demi baris. Perbedaan antar sel ditampilkan dalam tata letak kotak yang mudah dibaca, seperti ini:

Itu Bandingkan File perintah menggunakan Microsoft Spreadsheet Bandingkan untuk membandingkan dua file. Di Windows 8, Anda dapat memulai Perbandingan Spreadsheet di luar Excel dengan mengklik Perbandingan Spreadsheet pada Aplikasi layar. Di Windows 7, klik tombol Windows Mulailah tombol dan kemudian> Semua program > Microsoft Office 2013 > Alat Office 2013 > Perbandingan Spreadsheet 2013.

Untuk mempelajari selengkapnya tentang perbandingan lembar bentang dan membandingkan file, baca membandingkan dua versi buku kerja.

Menganalisis buku kerja

Itu Analisis Buku Kerja perintah membuat laporan interaktif yang memperlihatkan informasi mendetail tentang buku kerja dan strukturnya, rumus, sel, rentang, dan peringatannya. Gambar di sini memperlihatkan buku kerja yang sangat sederhana yang berisi dua rumus dan koneksi data ke database Access dan file teks.

Tampilkan tautan buku kerja

Buku kerja yang terhubung ke buku kerja lain melalui referensi sel bisa membingungkan. Gunakan untuk membuat peta grafis interaktif dari dependensi buku kerja yang dibuat oleh koneksi (tautan) antar file. Tipe tautan dalam diagram dapat menyertakan buku kerja lain, database Access, file teks, halaman HTML, database SQL Server, dan sumber data lainnya. Dalam diagram hubungan, Anda dapat memilih elemen dan menemukan lebih banyak informasi tentangnya, dan menyeret garis koneksi untuk mengubah bentuk diagram.

Diagram ini memperlihatkan buku kerja saat ini di sebelah kiri dan koneksi antara buku kerja dan sumber data lainnya. Ini juga memperlihatkan tingkat koneksi buku kerja tambahan, memberi Anda gambaran tentang asal data untuk buku kerja.

Tampilkan tautan lembar kerja

Punya banyak lembar kerja yang saling bergantung? Gunakan untuk membuat peta koneksi (tautan) grafis interaktif antara lembar kerja baik di buku kerja yang sama maupun di buku kerja lainnya. Ini membantu memberi Anda gambaran yang lebih jelas tentang bagaimana data Anda mungkin bergantung pada sel di tempat lain.

Diagram ini memperlihatkan hubungan antara lembar kerja dalam empat buku kerja yang berbeda, dengan ketergantungan antar lembar kerja dalam buku kerja yang sama serta tautan antar lembar kerja dalam buku kerja yang berbeda. Saat Anda menempatkan penunjuk Anda di atas simpul dalam diagram, seperti lembar kerja bernama "Barat" dalam diagram, balon berisi informasi akan muncul.

Tampilkan hubungan sel

Untuk mendapatkan diagram interaktif dan terperinci dari semua tautan dari sel yang dipilih ke sel di lembar kerja lain atau bahkan buku kerja lainnya, gunakan Hubungan sel alat. Hubungan ini dengan sel lain bisa ada dalam rumus, atau referensi ke rentang bernama. Diagram dapat melintasi lembar kerja dan buku kerja.

Diagram ini menunjukkan dua tingkat hubungan sel untuk sel A10 pada Sheet5 di Book1.xlsx. Sel ini bergantung pada sel C6 pada Lembar 1 di buku kerja lain, Book2.xlsx. Sel ini adalah preseden untuk beberapa sel pada lembar kerja lain dalam file yang sama.

Untuk mempelajari selengkapnya tentang menampilkan hubungan sel, baca melihat tautan antar sel.

Bersihkan pemformatan sel berlebih

Pernah membuka buku kerja dan menemukannya dimuat dengan lambat, atau menjadi besar? Ini mungkin memiliki pemformatan yang diterapkan ke baris atau kolom yang "tidak Anda sadari. Gunakan Bersihkan Pemformatan Sel Kelebihan perintah untuk menghapus pemformatan berlebih dan sangat mengurangi ukuran file. Ini membantu Anda menghindari "spreadsheet bloat", yang meningkatkan kecepatan Excel.

Kelola kata sandi

Jika Anda "menggunakan fitur Inquire untuk menganalisis atau membandingkan buku kerja yang dilindungi kata sandi, Anda" harus menambahkan kata sandi buku kerja ke daftar kata sandi Anda sehingga Inquire bisa membuka salinan buku kerja Anda yang disimpan. Menggunakan Kata Sandi Buku Kerja perintah pada Menanyakan tab untuk menambahkan kata sandi, yang akan disimpan di komputer Anda. Kata sandi ini dienkripsi dan hanya dapat diakses oleh Anda.

Artikel ini memberikan jawaban atas pertanyaan-pertanyaan berikut:

Bagaimana membandingkan dua tabel di Excel?
Bagaimana membandingkan spreadsheet kompleks di Excel?
Bagaimana cara membandingkan tabel di Excel menggunakan fungsi VLOOKUP ()?
Bagaimana cara menghasilkan pengidentifikasi baris unik jika keunikannya awalnya ditentukan oleh serangkaian nilai di beberapa kolom?
Bagaimana cara membekukan nilai sel dalam rumus saat menyalin rumus?

Saat bekerja dengan sejumlah besar informasi, pengguna mungkin dihadapkan dengan tugas seperti membandingkan dua sumber data tabel. Saat menyimpan data dalam satu sistem akuntansi (misalnya, sistem berdasarkan 1C Enterprise, sistem yang menggunakan database SQL), kemampuan yang dibangun ke dalam sistem atau DBMS dapat digunakan untuk membandingkan data. Sebagai aturan, untuk ini cukup melibatkan programmer yang akan menulis kueri ke database, atau mekanisme laporan perangkat lunak. Pengguna berpengalaman yang memiliki keterampilan menulis kueri 1C atau SQL juga dapat menangani kueri.

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

Untuk mengatasi masalah yang ditunjukkan, teknik yang ideal adalah menggunakan editor spreadsheet Microsoft Excel untuk membandingkan data. Sebagian besar sistem akuntansi manajemen dan peraturan umum mendukung ekspor ke format Excel. Tugas ini hanya akan memerlukan kualifikasi pengguna tertentu untuk bekerja dengan office suite ini dan tidak memerlukan keterampilan pemrograman.

Mari kita pertimbangkan solusi untuk masalah membandingkan tabel di Excel menggunakan contoh. Kami memiliki dua tabel yang berisi daftar apartemen. Sumber pembongkaran - 1C Enterprise (akuntansi konstruksi) dan tabel di Excel (akuntansi penjualan). Tabel terletak di buku kerja Excel pada lembar pertama dan kedua, masing-masing.

Tugas kita adalah membandingkan daftar ini berdasarkan alamat. Tabel pertama berisi semua apartemen di rumah. Tabel kedua hanya berisi apartemen yang dijual dan nama pembeli. Tujuan utamanya adalah untuk menampilkan nama pembeli di tabel pertama untuk setiap apartemen (untuk apartemen yang telah terjual). Tugas diperumit oleh fakta bahwa alamat apartemen di setiap meja bersifat konstruksi dan terdiri dari beberapa bidang: 1) alamat bangunan (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 setiap baris di kedua tabel diidentifikasi oleh satu bidang, bukan empat. Anda bisa mendapatkan bidang seperti itu dengan menggabungkan nilai dari empat bidang alamat dengan fungsi Gabungkan (). Tujuan dari fungsi Concatenate() adalah untuk menggabungkan beberapa nilai teks menjadi satu baris. Nilai dalam fungsi dihitung melalui simbol ";". Nilai dapat berupa alamat sel atau teks arbitrer yang ditentukan dalam tanda kutip.

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

Langkah 2. Salin rumus ke dalam sel berikut di kolom A.

Langkah 4. Untuk membandingkan tabel Excel berdasarkan nilai, gunakan fungsi VLOOKUP (). Tujuan dari fungsi VLOOKUP () adalah untuk menemukan nilai di kolom paling kiri dari tabel dan mengembalikan nilai sel yang terletak 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 mana nilai akan dikembalikan di baris yang ditemukan. Parameter keempat adalah jenis pencarian: false adalah pencocokan tepat, true adalah pencocokan perkiraan. Karena informasi keluaran harus ditempatkan di tabel pertama (di tabel inilah nama pembeli harus ditambahkan), maka formula akan ditulis di dalamnya. Mari kita bentuk rumus di kolom bebas di sebelah kanan tabel di seberang baris data pertama:
= VLOOKUP (A3; Sheet2! $A $3: $F $10; 6; FALSE)
Saat menyalin rumus, Smart Excel 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 melakukannya, perbaiki sel di parameter alamat tabel dengan simbol "$". Alih-alih "Sheet2! A3: F10" kami membuat "Sheet2! $ A $ 3: $ F $ 10".

Setelah menginstal add-in, Anda akan memiliki tab baru dengan perintah untuk memanggil fungsi tersebut. Saat Anda mengklik perintah Membandingkan rentang kotak dialog untuk memasukkan parameter muncul.

Makro ini memungkinkan Anda untuk membandingkan tabel dengan ukuran apa pun dan dengan jumlah kolom berapa pun. Perbandingan tabel dapat dilakukan satu, dua atau tiga kolom sekaligus.

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

Untuk membandingkan tabel, ikuti langkah-langkah berikut:

Tentukan rentang tabel.
Setel kotak centang (kotak centang / kotak centang) di bawah rentang tabel yang dipilih jika tabel menyertakan tajuk (baris tajuk).
Pilih kolom tabel kiri dan kanan yang akan digunakan untuk membandingkan (jika rentang tabel tidak menyertakan header, kolom akan diberi nomor).
Tentukan jenis perbandingan.
Pilih opsi untuk menampilkan hasil.

Jenis perbandingan tabel

Program ini memungkinkan Anda untuk memilih beberapa jenis perbandingan tabel:

Temukan baris dari satu tabel yang tidak ada di tabel lain

Ketika jenis perbandingan ini dipilih, program akan mencari baris dari satu tabel yang tidak ada 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

Saat memilih jenis perbandingan ini, program menemukan baris yang cocok di tabel pertama dan kedua. Baris dianggap bertepatan jika nilai dalam kolom perbandingan yang dipilih (1, 2, 3) dari satu tabel benar-benar bertepatan dengan nilai kolom dari tabel kedua.

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

Cocokkan tabel berdasarkan yang dipilih

Dalam mode perbandingan ini, di seberang 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 pada empat kolom atau lebih

Jika Anda tidak memiliki fungsionalitas program dan perlu memetakan tabel dengan empat kolom atau lebih, maka Anda dapat keluar dari situasi sebagai berikut: