Bagaimana membandingkan dua kolom di Excel - metode perbandingan data Excel
Ini adalah bab dari buku: Michael Girwin. Ctrl + Shift + Enter. Menguasai rumus array di Excel.
Sampel berdasarkan satu atau lebih kondisi.Baris fungsi Excel gunakan operator perbandingan. Misalnya, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, dan AVERAGEIFS. Fungsi ini membuat pilihan berdasarkan satu atau beberapa kondisi (kriteria). Masalahnya adalah fungsi-fungsi ini hanya dapat menambah, menghitung, dan rata-rata. Bagaimana jika Anda ingin memaksakan ketentuan pada pencarian Anda, seperti nilai maksimum atau deviasi standar? Dalam kasus ini, karena tidak ada fungsi bawaan, Anda harus membuat rumus array. Hal ini sering kali disebabkan oleh penggunaan operator perbandingan larik. Contoh pertama dalam bab ini menunjukkan bagaimana menghitung nilai minimum dalam satu kondisi.
Mari gunakan fungsi IF untuk memilih elemen array yang memenuhi syarat. Dalam gambar. 4.1 Di tabel kiri ada kolom dengan nama kota dan kolom dengan waktu. Diperlukan untuk menemukan waktu minimum untuk setiap kota dan menempatkan nilai ini di sel yang sesuai di tabel kanan. Syarat pemilihannya adalah nama kota. Jika Anda menggunakan fungsi MIN, Anda dapat menemukan nilai minimum untuk kolom B. Tetapi bagaimana Anda memilih hanya angka-angka yang spesifik untuk Oakland? Dan bagaimana Anda menyalin rumus ke kolom? Karena Excel tidak memiliki fungsi MINESLI bawaan, Anda perlu menulis rumus asli yang menggabungkan fungsi IF dan MIN.
Angka: 4.1. Tujuan rumus: memilih waktu minimum untuk setiap kota
Unduh catatan dalam format atau format
Seperti yang ditunjukkan pada gambar. 4.2, Anda harus mulai memasukkan rumus di sel E3 dengan fungsi MIN. Tapi Anda tidak bisa berdebat nomor 1 semua nilai di kolom B!? Anda hanya ingin memilih nilai-nilai yang khusus untuk Auckland.
Seperti yang ditunjukkan pada gambar. 4.3, di langkah berikutnya, masukkan fungsi IF sebagai argumen nomor 1 untuk MIN. Anda meletakkan IF di dalam MIN.
Dengan menempatkan kursor di tempat argumen dimasukkan log_expression function IF (Gambar 4.4), Anda memilih rentang dengan nama kota A3: A8, lalu tekan F4 untuk membuat referensi sel menjadi absolut (lihat, misalnya, untuk lebih jelasnya). Kemudian Anda mengetik operator pembanding, tanda sama dengan. Terakhir, Anda akan memilih sel di sebelah kiri rumus - D3, dengan menjaga referensi relatif. Kondisi yang diformulasikan akan memungkinkan Anda memilih hanya Aucklands saat melihat kisaran A3: A8.
Angka: 4.4. Buat operator array pada argumen log_expression fungsi IF
Jadi, Anda telah membuat operator larik menggunakan operator perbandingan. Setiap saat selama pemrosesan larik, operator larik adalah operator pembanding, sehingga hasil operasinya adalah larik nilai TRUE dan FALSE. Untuk memverifikasi ini, pilih array (untuk melakukan ini, klik di tooltip pada argumen log_expression) dan tekan F9 (gbr 4.5). Anda biasanya menggunakan satu argumen log_expression, mengembalikan TRUE atau FALSE; di sini, larik yang dihasilkan akan mengembalikan beberapa nilai TRUE dan FALSE, jadi fungsi MIN hanya akan memilih jumlah minimum untuk kota yang cocok dengan nilai TRUE.
Angka: 4.5. Untuk melihat larik nilai TRUE dan FALSE, klik argumen di tooltip log_expression dan tekan F9
Kami memiliki dua tabel pesanan yang disalin ke dalam satu lembar kerja. Anda perlu membandingkan data dari dua tabel di Excel dan memeriksa posisi mana yang ada di tabel pertama, tetapi tidak di tabel kedua. Tidak ada gunanya membandingkan nilai setiap sel secara manual.
Bandingkan dua kolom untuk pertandingan di Excel
Bagaimana cara membandingkan nilai di Excel dari dua kolom? Untuk mengatasi masalah ini, kami merekomendasikan penggunaan pemformatan bersyaratuntuk menyorot dengan cepat item yang hanya ada di satu kolom. Lembar kerja dengan tabel:
Langkah pertama adalah menetapkan nama ke kedua tabel. Ini membuatnya lebih mudah untuk memahami rentang sel mana yang dibandingkan:
- Pilih alat FORMULAS - Nama yang Ditentukan - Tetapkan Nama.
- Di jendela yang muncul, di bidang "Nama:", masukkan nilai - Tabel_1.
- Klik kiri pada kolom entri "Range:" dan pilih kisaran: A2: A15. Dan klik OK.
Untuk daftar kedua, lakukan hal yang sama, tetapi tetapkan nama - Table_2. Dan tunjukkan kisaran C2: C15 - masing-masing.
Saran yang berguna! Nama rentang dapat ditetapkan lebih cepat dengan menggunakan kolom nama. Letaknya di sebelah kiri bilah rumus. Cukup pilih rentang sel, dan di bidang nama, ketikkan nama yang sesuai untuk rentang tersebut dan tekan Enter.
Sekarang mari gunakan pemformatan bersyarat untuk membandingkan dua daftar di Excel. Kita perlu mendapatkan hasil sebagai berikut:
Posisi yang ada di Table_1, tapi tidak di Table_2 akan ditampilkan dengan warna hijau. Pada saat yang sama, posisi yang terletak di Table_2, tetapi tidak ada di Table_1, akan disorot dengan warna biru.
Prinsip membandingkan data dua kolom di Excel
Kami menggunakan fungsi COUNTIF untuk menentukan kondisi untuk memformat sel kolom. Dalam contoh ini, fungsi ini memeriksa berapa kali nilai argumen kedua (misalnya, A2) muncul dalam daftar argumen pertama (misalnya, Tabel_2). Jika jumlah kali \u003d 0 maka rumus mengembalikan TRUE. Dalam kasus ini, sel diberi format kustom yang ditentukan dalam opsi pemformatan bersyarat. Referensi dalam argumen kedua bersifat relatif, yang berarti bahwa semua sel dari rentang yang dipilih akan diperiksa secara bergantian (misalnya, A2: A15). Rumus kedua bekerja dengan cara yang serupa. Prinsip yang sama dapat diterapkan pada berbagai tugas serupa. Misalnya, untuk membandingkan dua harga di Excel, genap
Saat bekerja di Excel, tugas yang cukup umum adalah membandingkan berbagai jenis daftar nilai. Alat Excel standar seperti pemformatan bersyarat dan fungsi dapat digunakan untuk membandingkan rentang nilai secara umum dan kolom nilai secara khusus. Selain itu, Anda dapat menyelesaikan tugas tersebut menggunakan makro VBA dan add-in untuk Excel berdasarkan tugas tersebut.
Kebutuhan untuk membandingkan rentang secara umum dan kolom secara khusus muncul saat memperbarui berbagai laporan, daftar, dan database yang dihasilkan di Excel dan memerlukan perubahan seiring waktu.DI jaringan internet cukup banyak contoh yang telah dijelaskan yang menggunakan berbagai fungsi (rumus) dan pemformatan bersyarat untuk membandingkan nilai dalam kolom. Bagi mereka yang metode tersebut tidak cocok karena alasan apa pun, Anda dapat menggunakan alat lain yang memungkinkan Anda membandingkan kedua kolom dan rentang nilai apa pun yang terdiri dari sejumlah baris dan kolom. Dengan bantuan add-in untuk Excel, Anda dapat membandingkan dua rentang, atau, lebih tepatnya, membandingkan setiap elemen dari satu rentang dengan setiap elemen yang lain dan menemukan kecocokan dan perbedaan di antara mereka pilihan Anda.
Add-in perbandingan Excel untuk nilai dalam dua rentang
Add-on, yang akan dibahas di bawah, dirancang untuk membandingkan nilai dalam dua rentang, yang terdiri dari sejumlah baris dan kolom. Add-in ini menggunakan prosedur untuk membandingkan setiap item dalam satu rentang dengan setiap item dalam rentang lain, menentukan item mana dalam rentang ini yang sama dan mana yang berbeda, dan mengisi sel dengan warna yang ditentukan pengguna.
Seperti yang Anda lihat dari kotak dialog, semuanya cukup sederhana, pengguna memilih dua rentang yang dibandingkan, menentukan apa yang harus dicari, nilai yang berbeda atau bertepatan, memilih warna dan meluncurkan program. Hasil akhir penggunaan add-in adalah sel yang diisi dengan warna tertentu, yang menunjukkan perbedaan atau tumpang tindih dalam nilai rentang yang dipilih.
Ada satu nuansa saat membandingkan nilai di Excel. Angka dapat diformat sebagai teks, yang tidak selalu dapat diidentifikasi secara visual (). Artinya, angka di Excel bisa berupa nilai numerik atau nilai teks, dan kedua nilai ini tidak sama satu sama lain. Sangat sering fenomena ini menimbulkan berbagai macam kesalahan. Untuk mengecualikan kesalahan tersebut, opsi "Bandingkan angka sebagai teks" digunakan, yang diaktifkan secara default. Menggunakan opsi ini memungkinkan Anda untuk membandingkan bukan nilai numerik, tetapi teks yang dikonversi darinya.
Add-on memungkinkan:
1. Dengan satu klik mouse, buka kotak dialog makro langsung dari toolbar Excel;
2. temukan elemen rentang # 1 yang tidak berada dalam rentang # 2;
3. temukan elemen rentang # 2 yang tidak berada dalam rentang # 1;
4. temukan elemen rentang # 1 yang berada dalam rentang # 2;
5. temukan elemen rentang # 2 yang berada dalam rentang # 1;
6. pilih salah satu dari sembilan warna isian untuk sel dengan nilai yang diinginkan;
7. memilih rentang dengan cepat menggunakan opsi "Batasi rentang", sementara Anda dapat memilih seluruh baris dan kolom, mengurangi rentang yang dipilih ke rentang yang digunakan dilakukan secara otomatis;
8. Alih-alih membandingkan nilai numerik, gunakan membandingkan nilai teks menggunakan opsi "Bandingkan angka sebagai teks";
9. membandingkan nilai dalam sel rentang, tanpa memperhitungkan spasi ekstra;
10. Bandingkan nilai dalam sel rentang dengan cara yang tidak membedakan huruf besar / kecil.
Bagaimana cara membandingkan dua kolom menggunakan makro (add-in) untuk Excel?
Perbandingan kolom adalah kasus khusus untuk membandingkan rentang arbitrer. Dalam rentang # 1 dan # 2, pilih dua kolom, dan Anda dapat memilih kolom dengan tepat, dan tidak menyeret bingkai pilihan melalui rentang dengan sel dengan mouse (untuk kenyamanan, opsi "Batasi Rentang" diaktifkan secara default, pemilihan menggunakan rentang), pilih tindakan yang diperlukan untuk mencari perbedaan atau kecocokan, pilih warna isian sel dan jalankan program. Di bawah ini Anda dapat melihat hasil pencarian nilai yang cocok dalam dua kolom.
Kemampuan untuk membandingkan dua kumpulan data di Excel sering kali berguna untuk orang yang memproses data dalam jumlah besar dan bekerja dengan tabel yang besar. Misalnya, perbandingan dapat digunakan dalam, ketepatan memasukkan data atau memasukkan data ke dalam tabel tepat waktu. Artikel di bawah ini menjelaskan beberapa teknik untuk membandingkan dua kolom dengan data di Excel.
Menggunakan operator bersyarat IF
Metode penggunaan operator bersyarat IF berbeda karena hanya bagian yang diperlukan untuk perbandingan yang digunakan untuk membandingkan dua kolom, dan bukan seluruh larik. Berikut adalah langkah-langkah penerapannya metode ini:
Tempatkan kedua kolom untuk perbandingan di kolom A dan B pada lembar kerja.
Di sel C2, masukkan rumus berikut \u003d IF (ISERROR (SEARCH (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) dan perluas ke sel C11. Rumus ini secara berurutan mencari setiap item dari kolom A di kolom B dan mengembalikan nilai item jika ditemukan di kolom B.
Menggunakan rumus substitusi VLOOKUP
Prinsip rumusnya mirip dengan metode sebelumnya, perbedaannya terletak pada, bukan CARI. Ciri khas dari metode ini juga kemampuan untuk membandingkan dua larik horizontal menggunakan rumus HLP.
Untuk membandingkan dua kolom dengan data di kolom A dan B (mirip dengan metode sebelumnya), masukkan rumus berikut \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) ke dalam sel C2 dan seret ke sel C11.
Rumus ini melihat setiap elemen dari array utama dalam array yang dibandingkan dan mengembalikan nilainya jika ditemukan di kolom B. Jika tidak, Excel akan mengembalikan kesalahan # N / A.
Menggunakan VBA Macro
Menggunakan makro untuk membandingkan dua kolom dapat menyatukan proses dan mengurangi waktu persiapan data. Keputusan tentang hasil perbandingan mana yang harus ditampilkan sepenuhnya bergantung pada imajinasi dan keterampilan Anda dalam menggunakan makro. Di bawah ini adalah teknik yang dipublikasikan di situs web resmi Microsoft.
1 | Sub Find_Matches () |
Dalam kode ini, variabel CompareRange diberi rentang dengan array yang dibandingkan. Kemudian menjalankan loop yang mengulang setiap item dalam rentang yang dipilih dan membandingkannya dengan setiap item dalam rentang yang dibandingkan. Jika elemen dengan nilai yang sama ditemukan, makro memasukkan nilai elemen di kolom C.
Untuk menggunakan makro, kembali ke lembar kerja, pilih rentang utama (dalam kasus kami, ini adalah sel A1: A11), tekan Alt + F8. Pada dialog yang muncul, pilih makro Temukan_Cocokdan klik tombol jalankan.
Setelah menjalankan makro, hasilnya akan seperti berikut:
Menggunakan add-in Enquire
Hasil
Jadi, kami melihat beberapa cara untuk membandingkan data di Excel, yang akan membantu Anda memecahkan beberapa masalah analitis dan mempermudah untuk menemukan nilai duplikat (atau unik).
Saat membandingkan beberapa item pembanding di Lembar kerja Excel, data sering disusun dalam kolom sehingga mudah untuk membandingkan karakteristik objek ini baris demi baris. Misalnya, model mobil, telepon, grup eksperimen dan kontrol, sejumlah toko rantai ritel, dll. Dengan jumlah jalur yang banyak, analisis visual tidak dapat diandalkan. Fungsi VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) mudah digunakan untuk membandingkan data di seluruh sel dan tidak memberikan gambaran keseluruhan. Bagaimana Anda mengetahui seberapa mirip kolom secara keseluruhan? Apakah kolomnya identik?
Add-in Kolom Peta memungkinkan Anda memetakan kolom dan melihat gambaran besarnya:
- Bandingkan dua kolom atau lebih satu sama lain
- Bandingkan Kolom dengan Nilai Referensi
- Hitung persentase pencocokan tepat
- Sajikan hasilnya dalam tabel pivot visual
Bahasa video: Inggris. Teks: Rusia, Inggris. (Perhatian: video mungkin tidak mencerminkan pembaruan terkini... Gunakan instruksi di bawah ini.)
Tambahkan "Kolom Cocok" ke Excel 2016, 2013, 2010, 2007
Cocok untuk: Microsoft Excel 2016 - 2007, desktop Office 365 (32-bit dan 64-bit).
Cara bekerja dengan add-in:
Bagaimana membandingkan dua kolom atau lebih satu sama lain dan menghitung persentase kecocokan
Pertimbangkan contoh pengembangan produk. Misalkan Anda perlu membandingkan beberapa prototipe yang sudah jadi satu sama lain dan mencari tahu seberapa mirip, berbeda, dan bahkan mungkin identik.
- Klik tombol Map Columns di toolbar XLTools\u003e Pilih Map Columns to Each.
- Klik OK\u003e
Nasihat:
Silahkan pilih tabel ringkasan hasil\u003e Klik pada ikon Analisis Ekspres\u003e Terapkan "Skala Warna".
Membaca hasilnya: prototipe Tipe 1 dan Tipe 3 hampir identik, tingkat kecocokan 99% berarti 99% parameter mereka di baris sama. Tipe 2 dan Tipe 4 adalah yang paling tidak mirip - parameternya hanya sama dengan 30%.
Bagaimana membandingkan kolom dengan nilai referensi dan menghitung tingkat kecocokan
Pertimbangkan contoh pengembangan produk. Misalkan Anda perlu membandingkan beberapa prototipe jadi dengan standar target tertentu, dan juga menghitung sejauh mana prototipe tersebut memenuhi standar ini.
- Pilih kolom untuk dibandingkan.
Misalnya kolom dengan data prototipe. - Klik tombol Map Columns di toolbar XLTools.
- Pilih Peta ke Rentang Kolom Referensi\u003e Pilih Kolom Referensi.
Misalnya kolom dengan standar. - Centang "Kolom berisi judul" jika demikian.
- Centang "Tampilkan persentase pertandingan" untuk menampilkan persentase pertandingan sebagai persentase.
Jika tidak, hasil akan ditampilkan sebagai 1 (pertandingan penuh) atau 0 (tidak ada pertandingan). - Tunjukkan di mana hasil harus ditempatkan: pada lembar baru atau pada lembar yang sudah ada.
- Klik OK\u003e Selesai, hasilnya ditampilkan di tabel pivot.
Nasihat: Untuk mempermudah menafsirkan hasil, terapkan pemformatan bersyarat padanya:
Pilih tabel pivot dari hasil\u003e Klik ikon Analisis Cepat\u003e Terapkan "Skala Warna".
Membaca hasilnya: Prototipe tipe 2 99% memenuhi Standar 2, yaitu. 99% parameternya dalam string adalah sama. Produk 5 paling dekat dengan Standar 3 - 96% parameternya identik. Pada saat yang sama, Produk 4 masih jauh dari memenuhi salah satu dari tiga standar tersebut. Sekarang kita dapat menyimpulkan seberapa besar setiap prototipe menyimpang dari nilai referensi target.
Tugas apa yang akan dibantu oleh Add-in Kolom Pencocokan?
Add-in memindai sel baris demi baris dan menghitung persentase nilai yang identik di kolom. XLTools Match Columns tidak cocok untuk perbandingan normal nilai dalam sel - ini tidak dirancang untuk menemukan duplikat atau nilai unik.
Add-in Map Columns memiliki tujuan berbeda. Nya tugas utama - cari tahu bagaimana secara umum dataset (kolom) mirip atau berbeda. Add-on membantu analisis data dalam jumlah besar saat Anda perlu melihat tingkat makro yang lebih luas, misalnya. jawab pertanyaan berikut:
- Seberapa mirip kinerja kelompok eksperimental
- Seberapa mirip hasil dari kelompok eksperimen dan kontrol
- Seberapa mirip / berbeda beberapa produk dari kategori yang sama
- Seberapa dekat KPI karyawan dengan indikator yang direncanakan?
- Seberapa mirip indikator dari beberapa toko ritel, dll.