Perbandingan jadual dalam Excel

Sering kali, pengguna Excel mempunyai tugas membandingkan dua jadual atau senarai untuk mengenal pasti perbezaan dalamnya atau item yang hilang. Setiap pengguna mengatasi tugas ini dengan cara tersendiri, tetapi paling sering sejumlah masa yang agak besar dibelanjakan untuk menyelesaikan soalan yang ditentukan, kerana tidak semua pendekatan untuk masalah ini adalah rasional. Pada masa yang sama, terdapat beberapa algoritma tindakan yang terbukti yang akan membolehkan bandingkan senarai atau jadual jadual dalam masa yang agak singkat dengan usaha yang minimum. Mari kita pertimbangkan butiran pilihan ini.

Dengan cara yang sama, anda boleh membandingkan data dalam jadual yang terletak pada helaian yang berbeza. Tetapi dalam kes ini, adalah wajar bahawa garis-garis di dalamnya adalah bernombor. Selebihnya prosedur perbandingan hampir sama seperti yang dinyatakan di atas, selain fakta bahawa apabila membuat formula, adalah perlu untuk beralih antara kepingan. Dalam kes kami, ungkapan akan mempunyai bentuk berikut:

= B2 = Senarai2! B2

Iaitu, seperti yang kita lihat, di hadapan koordinat data, yang terletak pada lembaran lain selain dari mana hasil perbandingan dipaparkan, nombor lembaran dan tanda seru ditunjukkan.

Kaedah 2: Pemilihan kumpulan sel

Perbandingan boleh dibuat menggunakan alat untuk pemisahan kumpulan sel. Dengan itu, anda juga boleh membandingkan hanya senarai yang disegerakkan dan dipesan. Di samping itu, dalam kes ini, senarai mesti terletak di sebelah satu sama lain pada satu helaian.

1. Pilih Bandingkan Array. Pergi ke tab "Rumah". Seterusnya, klik pada ikon "Cari dan Pilih", yang terletak di pita dalam bar alat pengeditan. Terdapat senarai di mana anda perlu memilih kedudukan "pemilihan kumpulan sel ...".

   

   Di samping itu, pemilihan kumpulan sel boleh dicapai dengan cara yang lain. Pilihan ini terutama berguna untuk pengguna yang mempunyai versi program lebih awal daripada Excel 2007, kerana kaedah melalui butang "Cari dan Pilih" tidak menyokong aplikasi ini. Pilih Array yang ingin membandingkan, dan klik pada kekunci F5.

2. Tetingkap peralihan kecil diaktifkan. Klik pada butang "Sorotan ..." di sudut kiri bawahnya.



3. Selepas itu, apa sahaja dua pilihan di atas yang telah anda pilih, tetingkap pemilihan kumpulan sel dilancarkan. Pasang suis ke kedudukan "Sorotan pada talian". Klik pada butang "OK".



4. Seperti yang dapat kita lihat, selepas itu, nilai-nilai yang tidak konsisten akan diserlahkan dengan warna yang berbeza. Di samping itu, bagaimana boleh dinilai dari kandungan garis formula, program ini akan menjadikan satu sel yang aktif dalam garis yang tidak bertepatan yang ditunjukkan.

Kaedah 3: Pemformatan Bersyarat

Anda boleh membuat perbandingan dengan menggunakan kaedah pemformatan bersyarat. Seperti dalam kaedah sebelumnya, berbanding kawasan harus berada di atas kertas kerja Excel dan disegerakkan antara satu sama lain.

1. Pertama sekali, kita memilih apa rantau tabular akan dianggap sebagai utama, dan dalam apa yang perlu dicari perbezaannya. Last mari kita lakukan dalam jadual kedua. Oleh itu, kami memperuntukkan senarai pekerja di dalamnya. Bergerak ke tab "Rumah", klik pada butang "Pemformatan Bersyarat", yang mempunyai lokasi pada pita dalam blok "gaya". Dari senarai drop-down, pergi melalui item "Pengurusan Peraturan".



2. Tetingkap Pengurus Pengurus Peraturan diaktifkan. Kami klik padanya ke butang "Buat Peraturan".



3. Dalam tetingkap berjalan, kami memilih kedudukan "Gunakan Formula". Dalam bidang "Format Cell", tulis formula yang mengandungi alamat sel-sel pertama dari rang-lajur yang dibandingkan, dipisahkan oleh tanda "tidak sama" (). Sebelum ungkapan ini kali ini kali akan berdiri tanda "=". Di samping itu, untuk semua koordinat lajur dalam formula ini, anda perlu memohon alamat mutlak. Untuk melakukan ini, kami memperuntukkan formula dengan kursor dan klik pada kekunci F4 tiga kali. Seperti yang dapat kita lihat, tanda dolar muncul berhampiran semua alamat lajur, yang bermaksud menghidupkan pautan untuk mutlak. Untuk kes tertentu kami, formula akan mengambil bentuk berikut:

   = $ A2 $ D2

   Kami merakam ungkapan ini dalam bidang di atas. Selepas itu, kami klik pada butang "Format ...".



4. Tetingkap "Format Sel" diaktifkan. Kami pergi ke tab "Isi". Di sini dalam senarai warna Hentikan pilihan pada warna, yang kami mahu cat unsur-unsur di mana data tidak sepadan. Klik pada butang "OK".



5. Kembali ke tetingkap Peraturan Pemformatan, klik pada butang "OK".



6. Selepas automatik bergerak ke tetingkap "Pengurus Peraturan", klik pada butang "OK" dan di dalamnya.



7. Sekarang dalam jadual kedua, unsur-unsur yang mempunyai data yang tidak konsisten dengan nilai-nilai yang sama di kawasan jadual pertama akan diserlahkan dalam warna yang dipilih.

Terdapat satu lagi cara untuk memohon pemformatan bersyarat untuk melaksanakan tugas itu. Seperti pilihan terdahulu, ia memerlukan lokasi kedua-duanya membandingkan kawasan-kawasan pada satu helaian, tetapi berbeza dengan kaedah yang telah dijelaskan sebelum ini, keadaan penyegerakan atau menyusun data tidak akan diwajibkan, yang membezakan pilihan ini dari sebelumnya yang diterangkan.

1. Kami menghasilkan pemilihan kawasan yang anda perlukan untuk membandingkan.



2. Kami menjalankan peralihan ke tab yang dipanggil "Rumah". Kami mengklik pada butang "Pemformatan Bersyarat". Dalam senarai diaktifkan, pilih kedudukan "peraturan untuk peruntukan sel". Dalam menu seterusnya, buat kedudukan "nilai berulang".



3. Nilai pendua dilancarkan. Sekiranya anda semua dilakukan dengan betul, maka dalam tetingkap ini, ia tetap hanya untuk mengklik pada butang "OK". Walaupun, jika anda mahu, dalam bidang yang sesuai tetingkap ini, anda boleh memilih satu lagi warna pemilihan.



4. Selepas kami menghasilkan tindakan yang ditentukan, semua elemen berulang akan diserlahkan dalam warna yang dipilih. Unsur-unsur yang tidak bertepatan akan tetap dicat dalam warna asal mereka (secara lalai putih). Oleh itu, anda boleh segera melihat secara visual untuk melihat apa perbezaan antara array.

Jika anda mahu, anda boleh, sebaliknya, cat unsur-unsur yang tidak konsisten, dan penunjuk yang sepadan, meninggalkan dengan mengisi warna sebelumnya. Pada masa yang sama, algoritma tindakan hampir sama, tetapi dalam tetapan nilai pendua dalam bidang pertama, bukannya parameter "berulang", pilih parameter "unik". Selepas itu klik pada butang "OK".

Oleh itu, ia adalah tepat petunjuk yang tidak bertepatan.

Pelajaran: Pemformatan Bersyarat dalam Excel

Kaedah 4: Formula Komprehensif

Juga bandingkan data dengan bantuan formula yang kompleks, asasnya adalah fungsi meter. Menggunakan alat ini, anda boleh mengira berapa banyak elemen dari lajur yang dipilih dari jadual kedua diulangi pada yang pertama.

Majlis pengendali merujuk kepada kumpulan statistik fungsi. Tugasnya adalah untuk mengira bilangan sel, nilai-nilai yang memenuhi syarat yang ditetapkan. Sintaks pengendali ini mempunyai jenis ini:

= Jadual (julat; kriteria)

Argumen "Range" adalah alamat array, yang mengira nilai-nilai yang bertepatan.

Hujah "kriteria" menentukan keadaan kebetulan. Dalam kes kita, ia akan menjadi koordinat sel-sel tertentu di kawasan jadual pertama.

1. Kami memperuntukkan elemen pertama dari lajur tambahan di mana bilangan perlawanan akan dikira. Seterusnya, klik pada ikon "Masukkan Fungsi".



2. Wizard Fungsi bermula. Pergi ke kategori "statistik". Cari dalam senarai nama "COUNTECLES". Selepas pemilihannya, klik pada butang "OK".



3. Tingkap hujah pengendali mula berjalan. Seperti yang kita lihat, nama-nama bidang dalam tetingkap ini sesuai dengan nama-nama hujah-hujah.

   Pasang kursor dalam medan "julat". Selepas itu, dengan memegang butang kiri tetikus, peruntukkan semua nilai lajur dengan nama jadual kedua. Seperti yang anda lihat, koordinat segera jatuh ke dalam medan yang ditentukan. Tetapi untuk tujuan kami, alamat ini harus dibuat dengan mutlak. Untuk melakukan ini, sorot koordinat dalam bidang dan klik pada kekunci F4.

   Seperti yang anda lihat, rujukan mengambil bentuk mutlak, yang dicirikan oleh kehadiran tanda dolar.

   Kemudian pergi ke medan "Kriteria" dengan memasang kursor. Klik pada elemen pertama dengan nama terakhir dalam julat jadual pertama. Dalam kes ini, biarkan pautan relatif. Selepas ia dipaparkan di lapangan, anda boleh mengklik pada butang "OK".



4. Hasilnya adalah output kepada elemen lembaran. Ia sama dengan nombor "1". Ini bermakna dalam senarai nama jadual kedua, nama "Grinev V. P.", yang merupakan yang pertama dalam senarai array jadual pertama, berlaku sekali.



5. Sekarang kita perlu membuat ungkapan yang sama dan untuk semua elemen lain dari jadual pertama. Untuk melakukan ini, saya akan melakukan penyalinan, menggunakan penanda pengisian, seperti yang telah kami lakukan sebelum ini. Kami meletakkan kursor ke bahagian bawah bahagian bawah elemen daun, yang mengandungi fungsi meter, dan selepas menukarnya ke penanda pengisian, mengepam butang tetikus kiri dan tarik kursor ke bawah.



6. Seperti yang dapat kita lihat, program ini telah membuat pengiraan kebetulan, membandingkan setiap sel jadual pertama dengan data, yang terletak di julat jadual kedua. Dalam empat kes, hasilnya adalah "1", dan dalam dua kes - "0". Iaitu, program ini tidak dapat mencari kedua-dua nilai dalam jadual kedua yang tersedia dalam pelbagai jadual pertama.

Sudah tentu, ungkapan ini untuk membandingkan penunjuk jadual, adalah mungkin untuk memohon dalam bentuk yang sedia ada, tetapi adalah mungkin untuk memperbaikinya.

Kami akan membuat nilai-nilai yang wujud dalam jadual kedua, tetapi tidak tersedia pada yang pertama, dipaparkan sebagai senarai yang berasingan.

1. Pertama sekali, kita mengitar semula formula kita, tetapi kita akan menjadikannya salah satu hujah pengendali jika. Untuk melakukan ini, kami menyerlahkan sel pertama di mana pengendali adalah pengendali. Dalam rentetan formula di hadapannya, kami menambah ungkapan "jika" tanpa petikan dan membuka kurungan. Selanjutnya, supaya lebih mudah bagi kami untuk bekerja, memperuntukkan formula dalam bentuk formula dan klik pada ikon "Masukkan Fungsi".



2. Tingkap argumen fungsi dibuka jika. Seperti yang anda lihat, tetingkap pertama tingkap telah dipenuhi dengan penilaian Majlis Pengendali. Tetapi kita perlu menambah sesuatu yang lain dalam bidang ini. Kami menetapkan di sana kursor dan ungkapan yang sudah ada tambah "= 0" tanpa petikan.

   Selepas itu, pergi ke medan "makna jika kebenaran". Di sini kita akan menggunakan satu lagi fungsi bersarang. Masukkan perkataan "string" tanpa petikan, kemudian buka kurungan dan nyatakan koordinat sel pertama dengan nama keluarga dalam jadual kedua, selepas itu kami menutup kurungan. Khususnya, dalam kes kami dalam bidang "Maksud jika benar", ungkapan berikut berlaku:

   Baris (D2)

   Sekarang rentetan pengendali akan melaporkan fungsi jika nombor baris di mana nama keluarga tertentu terletak, dan dalam kes apabila keadaan yang dinyatakan dalam medan pertama akan dilakukan, fungsi jika nombor ini akan dipaparkan. Klik pada butang "OK".



3. Seperti yang anda lihat, hasil pertama dipaparkan sebagai "Lies". Ini bermakna bahawa nilai tidak memenuhi syarat pengendali jika. Iaitu, nama keluarga pertama hadir dalam kedua-dua senarai.



4. Menggunakan penanda pengisian, yang sudah biasa menyalin ungkapan pengendali jika di seluruh lajur. Seperti yang kita lihat, dalam dua kedudukan yang hadir dalam jadual kedua, tetapi tidak pada mulanya, formula mengeluarkan nombor baris.



5. Kami berundur dari kawasan meja ke kanan dan mengisi ruang nombor yang teratur, bermula dari 1. Bilangan nombor mesti sepadan dengan bilangan baris dalam jadual yang boleh dipertimbangkan kedua. Untuk mempercepatkan prosedur penomboran, anda juga boleh menggunakan penanda mengisi.



6. Selepas itu, kami menyerlahkan sel pertama di sebelah kanan pembesar suara dengan nombor dan klik ikon "Masukkan Fungsi".



7. Wizard dibuka. Pergi ke kategori "statistik" dan menghasilkan nama nama "terkecil". Klik pada butang "OK".



8. Fungsi ini adalah yang terkecil, hujah-hujah hujah-hujah yang didedahkan, bertujuan untuk menarik balik nilai terkecil yang ditentukan.

   Dalam bidang "array", anda harus menentukan koordinat julat lajur pilihan "Bilangan perlawanan", yang sebelum ini telah diubah menggunakan fungsi jika. Kami membuat semua pautan mutlak.

   Dalam bidang "K", ia ditunjukkan yang dalam akaun nilai terkecil perlu dipaparkan. Di sini kita menunjukkan koordinat sel pertama lajur dengan penomboran, yang baru-baru ini kami tambah. Alamat Tinggalkan Relatif. Klik pada butang "OK".



9. Pengendali memaparkan hasil - nombor 3. Ia adalah tepat yang terkecil daripada penomboran garis-garis yang tidak konsisten dari array meja. Menggunakan penanda pengisian, salin formula ke hidung itu sendiri.



10. Sekarang, mengetahui bilangan baris unsur yang tidak berpengalaman, kita boleh memasukkan ke dalam sel dan nilai-nilai mereka menggunakan fungsi indeks. Pilih elemen pertama lembaran yang mengandungi formula adalah yang terkecil. Selepas itu, pergi ke formula garisan dan sebelum nama "terkecil" Tambah nama "indeks" tanpa petikan, segera buka pendakap dan letakkan satu titik dengan koma (;). Kemudian kami memperuntukkan nama Formula "Indeks" dalam talian dan klik pada ikon "Fungsi Tampal".



11. Selepas itu, tetingkap kecil dibuka, di mana ia perlu untuk menentukan sama ada pandangan rujukan harus mempunyai fungsi indeks atau direka untuk bekerja dengan array. Kami memerlukan pilihan kedua. Ia dipasang secara lalai, supaya dalam tetingkap ini hanya klik pada butang "OK".



12. Tetingkap hujah menjalankan fungsi indeks. Pengendali ini direka untuk mengeluarkan nilai yang terletak dalam array tertentu dalam baris yang ditentukan.

    Seperti yang anda lihat, medan "nombor baris" sudah dipenuhi dengan nilai-nilai makna fungsi terkecil. Dari nilai yang ada di sana, perbezaan antara penomboran lembaran Excel dan penomboran dalaman rantau tabular. Seperti yang kita lihat, kita hanya mempunyai topi pada nilai meja. Ini bermakna perbezaannya adalah satu baris. Oleh itu, tambah "-1" tanpa petikan dalam medan "nombor baris".

    Dalam bidang "Array", nyatakan alamat nilai nilai jadual kedua. Pada masa yang sama, semua koordinat membuat mutlak, iaitu, kami meletakkan tanda dolar sebelum kaedah yang diterangkan oleh kami.

    Klik pada butang "OK".



13. Selepas menarik balik, hasil pada skrin meregangkan fungsi menggunakan penanda isi ke akhir lajur ke bawah. Seperti yang kita lihat, kedua-dua nama keluarga yang hadir dalam jadual kedua, tetapi tidak terdapat pada yang pertama, dikeluarkan dalam julat yang berasingan.

Kaedah 5: Perbandingan array dalam buku yang berbeza

Apabila membandingkan julat dalam buku yang berbeza, anda boleh menggunakan kaedah di atas, tidak termasuk pilihan di mana penempatan kedua-dua kawasan jadual pada satu helaian diperlukan. Keadaan utama untuk prosedur perbandingan dalam kes ini adalah pembukaan tingkap kedua-dua fail pada masa yang sama. Untuk versi Excel 2013 dan kemudian, serta untuk versi untuk Excel 2007, tidak ada masalah dengan pelaksanaan keadaan ini. Tetapi dalam Excel 2007 dan Excel 2010 untuk membuka kedua-dua Windows pada masa yang sama, manipulasi tambahan diperlukan. Bagaimana untuk melakukannya dalam pelajaran yang berasingan.

Pelajaran: Bagaimana untuk membuka Excel dalam tingkap yang berbeza

Seperti yang anda lihat, terdapat beberapa kemungkinan untuk membandingkan jadual antara satu sama lain. Apa jenis pilihan untuk digunakan bergantung kepada di mana data jadual relatif antara satu sama lain terletak (pada satu helai, dalam buku yang berbeza, pada helaian yang berbeza), serta dari pengguna mahu perbandingan ini dipaparkan pada skrin.