Perkongsian kemahiran Excel Praktikal: perbandingan data dalam beberapa situasi berbeza

Kerja harian memerlukan membandingkan data, mencari perbezaan, mencari nilai pendua, dsb. dari semasa ke semasa. Ada yang membandingkan data dalam lembaran kerja yang sama, dan ada yang membandingkan data antara lembaran kerja yang berbeza. Di sini kami meringkaskan perbandingan data dalam pelbagai situasi berbeza, dan menyediakan kaedah pantas supaya semua orang boleh melengkapkan perbandingan data dengan cepat dalam situasi berbeza.

Bahagian 1: Perbandingan data dalam jadual yang sama

---

1 . Tegas Bandingkan dua lajur data untuk melihat sama ada ia adalah sama

Perbandingan yang dipanggil ketat bermaksud data itu dibandingkan mengikut kedudukan.

1) Perbandingan kekunci pintasan Ctrl+

Seperti yang ditunjukkan dalam rajah di bawah, pilih dua lajur data untuk dibandingkan, lajur A dan lajur B, dan kemudian tekan kekunci pintasan Ctrl+, berbeza Data B5, B9, B10 dan B15 akan dipilih.

2) Perbandingan kaedah penentududukan (kekunci pintasan F5 atau Ctrl+G)

Jadual berikut ialah contoh, pilih lajur A dan pengepala lajur lajur B untuk memilih dua lajur data dengan cepat, kemudian tekan kekunci pintasan F5 (atau Ctrl+G) untuk memaparkan tetingkap kedudukan, pilih keadaan kedudukan sebagai "sel perbezaan kandungan baris", klik Butang "OK", perbezaan Data akan dipilih.

Nota:

Kedua-dua kaedah di atas boleh membandingkan dengan cepat perbezaan antara dua lajur data, tetapi mereka tidak akan membezakan antara huruf besar dan huruf kecil.

3) Perbandingan fungsi IF

(1) Perbandingan fungsi if yang tidak memerlukan huruf sensitif huruf kecil

Kedua-dua lajur A dan B dalam jadual di bawah ialah Ia adalah nombor, tiada huruf, dan ia tidak perlu sensitif huruf besar-besaran.

Anda boleh memasukkan formula =IF(A2=B2, "Same", "Not the same") dalam sel C2 Selepas memasukkan, tarik pemegang dan seretnya ke bawah sehingga Data dalam lajur ini dipotong, dan keputusan yang sama dan berbeza jelas sepintas lalu, seperti yang ditunjukkan dalam jadual di bawah.

(2) Perbandingan fungsi if yang membezakan antara huruf besar dan huruf kecil

Jika data perbandingan mengandungi huruf dan perlu sensitif huruf, formula di atas tidak boleh menjadi kontras yang tepat. Pada masa ini, anda boleh menukar formula C2 kepada =IF(EXACT(A2,B2)=TRUE,"Sama","Tidak sama"), dan kemudian jatuh ke bawah untuk mengisi formula, seperti yang ditunjukkan dalam rajah di bawah .

2 Cari nilai pendua dalam dua lajur data

1) Fungsi IF + MATCH mencari nilai pendua

Sekarang, apakah kaedah untuk mengetahui senarai pemenang untuk dua suku berturut-turut dalam jadual berikut?

Sebenarnya, adalah perlu untuk mencari nilai pendua dengan membandingkan lajur A dan lajur B. Kita boleh menggunakan fungsi IF+MATCH untuk menggabungkan formula, masukkan formula =IF(ISERROR(MATCH(A2,$B:$B,0)),"",A2) dalam sel C2, dan kemudian tarik ke bawah untuk menyalin formula untuk menyelesaikan tugas carian. Lihat jadual di bawah untuk perbandingan hasil carian:

Analisis formula:

MATCH digunakan untuk mengembalikan kedudukan data A2 yang terdapat dalam kawasan $B$2:$B$25. Jika ia dijumpai, nombor baris akan dikembalikan (menunjukkan bahawa terdapat pertindihan), jika tidak dijumpai, ralat #N/A akan dikembalikan (menunjukkan bahawa tiada pertindihan).

Tambahkan fungsi ISERROR pada formula untuk menentukan sama ada nilai yang dikembalikan oleh MATCH ialah ralat #N/A Jika ia ralat #N/A, ia akan mengembalikan TRUE #T/A, ia akan berjulat daripada PALSU.

Fungsi IF paling luar mengembalikan nilai berbeza bergantung pada sama ada ISERROR (MATCH ()) adalah TRUE atau FALSE. Jika BENAR (iaitu, tiada pengulangan), ia kembali kosong; jika ia SALAH, ia mengembalikan A2. ​

Jika kita ingin mengetahui senarai pemenang pada suku pertama tetapi bukan pada suku kedua, kita boleh menukar formula fungsi di atas kepada: =IF(ISERROR(MATCH(A2,$B:$B,0)), A2, "").

2) IF+COUNTIF berfungsi untuk mencari nilai pendua

Dua lajur A dan B dalam jadual di bawah ialah nama pelanggan yang perlu anda cari nama pelanggan pendua dalam dua lajur dan Tandakannya dalam lajur C.

Kaedah operasi ialah memasukkan formula =IF(COUNTIF(A:A,B2)=0,"",B2) dalam sel C2, dan kemudian tarik ke bawah untuk melengkapkan dua lajur perbandingan Data excel. Sila lihat demo di bawah!

Fungsi COUNTIF ialah fungsi yang mengira sel dalam julat tertentu yang memenuhi syarat tertentu.

Uji anda:

Jika nilai berangka yang dibandingkan di atas melebihi 15 digit, contohnya, perbandingan adalah nombor ID, adakah formula di atas masih boleh digunakan? Jika formula di atas tidak boleh digunakan, bagaimana pula dengan menukarnya kepada formula berikut?

=IF(COUNTIF(A: A,B2&"*")=0," ",B2)

atau

=IF(SUMPRODUCT(1*(A:A=B2)),B2,"")

Jika anda tidak tahu jawapannya, sila tonton tutorial "Kad bilangannya berkurangan secara pelik, dan sepupu saya dipenjarakan secara tidak adil Penalti - Excel, ternyata anda mempunyai pendua benar dan salah! 》.

3) IF+VLOOKUP berfungsi untuk mencari nilai pendua

Seperti yang ditunjukkan dalam jadual di bawah, terdapat dua set nombor pekerja. Saya tidak tahu yang mana yang biasa bagi kedua-dua kumpulan A dan B. Kita juga boleh menggunakan formula fungsi if+VLOOKUP untuk melengkapkan perbandingan.

Masukkan formula dalam sel C2: =IF(ISNA(VLOOKUP(A2,$B:$B,1,))," ",A2), kemudian tarik ke bawah dan salin formula, anda boleh mencari nilai pendua dalam dua lajur data dalam Excel .

Analisis formula:

Fungsi ISNA digunakan untuk menentukan sama ada nilai itu nilai ralat #N/A (iaitu , nilai tidak wujud ), jika ya, kembalikan BENAR jika tidak, kembalikan FALSE.

Dalam formula, anda perlu menambah tanda $ sebelum data dalam kawasan carian untuk membetulkan kawasan carian. Jika tidak, kawasan carian juga akan berubah apabila drop-down diisi, yang akan menjejaskan hasil carian dan perbandingan.

Pelanjutan apl: Gunakan Vlookup untuk mencari perbezaan

Formula ini boleh dilaraskan sedikit untuk mencari nilai yang berbeza, atau nilai yang hilang, atau nilai yang salah (perbandingan tidak ketat, tidak memberi perhatian kepada kedudukan atau susunan). Sebagai contoh, Kumpulan B di atas adalah data standard Untuk mengetahui nilai dalam Kumpulan A yang berbeza daripada Kumpulan B, formula boleh ditulis sebagai:

=IF(ISNA(VLOOKUP(A2,$B:$B,1,)), A2, " ")

Bahagian. 2: Perbandingan data jadual silang

---

1 Bandingkan dengan ketat sama ada data kedua-dua jadual adalah sama

Apabila dua jadual dengan format yang betul-betul sama dibandingkan Apabila membandingkan dan mencari perbezaan, anda boleh menggunakan kaedah di bawah.

1) Kaedah pemformatan bersyarat untuk membandingkan perbezaan antara kedua-dua jadual

Sekarang ambil dua jadual berikut sebagai contoh untuk membandingkan dan menyerlahkan nilai yang berbeza .

Mula-mula, pilih jadual, cipta peraturan baharu dan pilih "Gunakan formula untuk menentukan sel yang akan diformat", kemudian masukkan =A9A1, untuk sel yang sepadan Buat penghakiman untuk menentukan sama ada mereka sama. Sila lihat demo di bawah!

Peringatan hangat:

Jika anda ingin mengosongkan pemformatan bersyarat, mula-mula pilih julat sel untuk mengosongkan format , dan kemudian Laksanakan "Mula" - "Pemformatan Bersyarat" - "Kosongkan Peraturan" - "Kosongkan Peraturan untuk Sel Terpilih" (atau kosongkan peraturan untuk keseluruhan lembaran kerja).

2) Bandingkan perbezaan antara kedua-dua jadual menggunakan kaedah tampal khas (kaedah ini hanya sesuai untuk perbandingan nombor)

Seperti yang ditunjukkan dalam rajah di bawah, kedua-dua jadual mempunyai format yang sama dan susunan nama yang sama Ia diperlukan untuk mencari perbezaan data dengan cepat antara dua jadual.

Salin salah satu kawasan berangka, kemudian tekan kekunci pintasan Ctrl+Alt+V untuk menampal secara selektif, tetapkannya kepada operasi "tolak", klik "OK", ia bukan 0 Bahagian adalah tempat perbezaannya. Sila lihat demo di bawah!

Kaedah ini hanya sesuai untuk mencari data pembezaan dengan cepat, hanya dengan sepintas lalu, kerana ia akan memusnahkan jadual data asal.

3) Fungsi IF membandingkan perbezaan antara dua jadual

Seperti yang ditunjukkan dalam rajah di bawah, jadual a dan jadual b ialah jadual dengan format yang sama ia dikehendaki menyemak dua jadual Sama ada nilai benar-benar konsisten, dan perbezaan mesti dipaparkan secara visual.

Kaedah operasi adalah untuk mencipta lembaran kerja kosong baharu dan masukkan formula =IF(Jadual a!A1 Jadual b!A1, "Jadual a:"& Jadual a! A1&" lwn jadual b:"& jadual b!A1,""), kemudian salin formula isian dalam julat. Sila lihat demo di bawah!

2 Cari perbezaan antara dua data jadual mengikut syarat

1. ) Cari perbezaan antara data dalam dua jadual menggunakan syarat tunggal

Sebagai contoh, berikut ialah jadual skor yang disusun oleh dua orang Format jadual adalah sama, tetapi susunan nama adalah berbeza. Kini anda perlu membandingkan kedua-dua jadual untuk mengesahkan sama ada keputusan ringkasan adalah betul.

Pengesahan data jenis ini ialah pengesahan syarat tunggal. Kerana ia disusun oleh orang yang berbeza, selain menyemak markah mengikut nama, ia juga perlu untuk menandakan mereka yang namanya tidak sepadan. Kami melakukan ini menggunakan pemformatan bersyarat.

Anda perlu mencipta dua format bersyarat.

Format pertama: Cari perbezaan nama

(1) Pilih data lajur nama bagi jadual kedua, pilih "Peraturan Baharu" dalam "Pemformatan Bersyarat", dan dalam pop timbul Dalam kotak dialog, pilih "Gunakan formula untuk menentukan sel yang akan diformat", dan kemudian masukkan formula =COUNTIF($A$2:$A$10,A14)=0

(2 ) Klik butang format dan pilih Warna isian.

Selepas pengesahan, kami akan melengkapkan tetapan format pertama.

Format kedua: Cari perbezaan markah untuk nama yang sama.

(1) Pilih semua sel skor dalam jadual kedua, cipta peraturan baharu dan gunakan formula untuk menentukan peraturan yang dimasukkan ialah = =VLOOKUP($A14,$A$1:$I$10,COLUMN(B1),0)-B14

(2) Klik butang format dan pilih warna isian.

Lengkapkan semakan skor selepas pengesahan. Keputusan pengesahan keseluruhan adalah seperti berikut:

Jingga menunjukkan bahawa nama "Liu Xiaoguang" tidak sepadan dengan jadual lain, dan nama itu mungkin ditulis dengan salah biru-hijau; Markah Bahasa Cina Yang Wenwen , markah Bahasa Inggeris He Congliang dan markah Bahasa Cina Hou Manman tidak sepadan, dan mungkin terdapat ralat.

2) Pelbagai syarat untuk mencari perbezaan antara data dalam dua jadual

Seperti yang ditunjukkan dalam rajah di bawah, ia dikehendaki menyemak perbezaan kuantiti bagi produk yang sama dalam gudang yang sama dalam dua jadual Keputusan menunjukkan Dalam lajur D. Dengan cara apakah ini boleh dicapai? Sakit kepala!

Masukkan formula berikut dalam sel D15:

=SUMPRODUCT(($A:$A=A15)*($B:$B=B15)*$C:$C)-C15

Kemudian tarik ke bawah untuk melengkapkan perbandingan nilai. Sila lihat sila lihat! !

Di atas adalah perkongsian hari ini, mari kita amalkan bersama

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: perbandingan data dalam beberapa situasi berbeza. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!