Data untuk membandingkan dua kolom di Excel
Data kita terdiri dari tiga kolom. Daftar 1 [kolom B], Daftar 2 [kolom C] dan Perbandingan [kolom D]. Kami ingin membandingkan kode produk dalam dua kolom B dan C. Hasil perbandingan akan dicatat pada kolom D.
Bandingkan dua kolom di Excel menggunakan VLOOKUP
VLOOKUP digunakan saat kita ingin mencari nilai di satu kolom dan menentukan apakah ada di kolom lain. Ada dua jenis pencocokan. pencocokan perkiraan atau pencocokan tepat. Saat membandingkan daftar, pencocokan persis paling sering digunakan.
Sintaks fungsi VLOOKUP
=VLOOKUP[lookup_value, table_array, col_index_num, [range_lookup]]
- lookup_value – nilai yang ingin kita temukan di table_array
- table_array – rentang sel berisi data yang ingin kita temukan atau ambil
- col_index_num – nomor kolom dalam table_array yang sesuai dengan informasi yang ingin kita ambil, relatif terhadap nilai_pencarian
- [ range_lookup ] – opsional;
- jika BENAR atau dihilangkan, VLOOKUP mengembalikan kecocokan persis atau perkiraan
- jika FALSE, VLOOKUP hanya akan menemukan kecocokan yang tepat
Untuk menentukan apakah nilai di kolom pertama [Daftar 1] ada di kolom lain [Daftar 2] dan mengembalikan nilai itu sendiri, kami mengikuti langkah-langkah berikut.
Langkah 1 . Masukkan rumus di sel D3. =VLOOKUP[B3,$C$3:$C$9,1,FALSE]_
Langkah 2 . Tekan ENTER
Langkah 3 . Salin rumus di sel D3 ke sel D4. D9 dengan mengeklik ikon “+” di pojok kanan bawah sel D3 dan menariknya ke bawah
lookup_value kita adalah nilai di kolom B [B3], sedangkan table_array kita adalah kumpulan nilai di kolom C, $C$3. $C$9. Col_index_num adalah 1, yang mengembalikan nilai di kolom pertama relatif terhadap table_array jika nilai di kolom B ditemukan di kolom C. Jika tidak, fungsi akan mengembalikan nilai “ #N/A ”. range_lookup adalah FALSE, yang berarti fungsi akan mencari kecocokan yang tepat.
Akibatnya, nilai dalam sel D3 adalah “ 19001 ”, because the value in column B “19001” is found in column C.
Untuk nilai berikutnya di kolom B, "19002", fungsi VLOOKUP mengembalikan "#N/A" di sel D4 karena "19002" tidak ditemukan di kolom C.
Tabel di bawah menunjukkan hasil akhir setelah membandingkan kedua daftar menggunakan VLOOKUP. Nilai 19001, 19003, 19005 dan 19006 hadir dalam dua daftar.
Bandingkan dua kolom di Google sheets menggunakan VLOOKUP
Dengan cara yang sama, kita dapat membandingkan dua kolom di Google sheets menggunakan VLOOKUP dengan mengikuti prosedur yang sama di atas. Kami memasukkan rumus berikut ke dalam D3 dan menyeretnya ke bawah ke D9.
=VLOOKUP[B3,$C$3:$C$9,1,FALSE]
Hasil perbandingan di Excel dan Google sheets adalah sama, seperti yang ditunjukkan pada tabel di bawah ini
Bandingkan dua kolom menggunakan Excel COUNTIF
Saat bekerja dengan Excel atau Google spreadsheet dan kami .
Sintaks Fungsi COUNTIF
=COUNTIF[range,criteria]
- rentang – rentang data yang akan dievaluasi menggunakan kriteria
- kriteria – kriteria atau ketentuan yang menentukan sel mana yang akan dihitung
Untuk menentukan berapa kali nilai di kolom B ada di kolom C, kami mengikuti langkah-langkah ini.
Langkah 1 . Masukkan rumus di sel D3. =COUNTIF[$C$3:$C$9,B3]_
Langkah 2 . Tekan ENTER
Langkah 3 . Salin rumus di sel D3 ke sel D4. D9 dengan mengeklik ikon “+” di pojok kanan bawah sel D3 dan menariknya ke bawah
Jangkauan kita adalah kumpulan nilai di kolom C, $C$3. $C$9. Kriterianya adalah B3, atau nilai pada kolom B. Fungsi mengembalikan frekuensi B3 dalam rentang di kolom C. Akibatnya, nilai dalam sel D3 adalah “ 1 ”, .
Sedangkan untuk B4, atau “19002”, fungsi COUNTIF mengembalikan “0”, karena “19002” tidak ditemukan di kolom C.
Nilai di D5 adalah “2” karena “19003” ditemukan dua kali di kolom C, khususnya di C3 dan C9.
Sesuaikan hasilnya menggunakan IF dan COUNTIF
Kita dapat menyesuaikan hasilnya saat kita membandingkan dua kolom di Excel dengan menggabungkan fungsi IF dan COUNTIF. Kami mengikuti prosedur yang sama seperti pada contoh sebelumnya tetapi kami akan menggunakan rumus ini.
=IF[COUNTIF[$C$3:$C$9,B3]>0,"Match found","Not found"]
Alih-alih mengembalikan hitungan cocok nilai dalam dua daftar, the formula returns “Match found” when the value in column B exists in column C, and returns “Not found” if otherwise.
Bandingkan dua kolom menggunakan MATCH
Saat kita membandingkan dua kolom di Excel, kita mungkin ingin mengembalikan posisi nilai dalam kolom tertentu, bukan nilai . Fungsi MATCH adalah solusi sempurna untuk ini, karena mengembalikan posisi nilai dalam rentang tertentu. Perhatikan, bagaimanapun, bahwa itu hanya mempertimbangkan pertandingan pertama.
Sintaks fungsi MATCH
=MATCH[lookup_value, lookup_array, [match_type]]_
- lookup_value – nilai yang ingin kita temukan di lookup_array
- lookup_array – rentang sel berisi nilai yang ingin kita cocokkan
- [jenis_kecocokan] – opsional;
Untuk menentukan apakah suatu nilai ada di dua kolom B dan C dan mengembalikan posisinya alih-alih nilai itu sendiri, kami mengikuti langkah-langkah ini.
Langkah 1 . Masukkan rumus di sel D3. =MATCH[B3,$C$3:$C$9,0]_
Langkah 2 . Tekan ENTER
Langkah 3 . Salin rumus di sel D3 ke sel D4. D9 dengan mengeklik ikon “+” di pojok kanan bawah sel D3 dan menariknya ke bawah
Lookup_value kita adalah nilai di kolom B, sedangkan lookup_array kita adalah kumpulan nilai di kolom C, $C$3. $C$9. Match_type adalah "0" untuk menemukan kecocokan yang tepat.
Akibatnya, nilai dalam sel D3 adalah “ 4 ”, because the value in column B “19001” is found in the fourth position [C6] in the lookup_array C3:C9. On the other hand, when a value is not found, the function returns “#N/A”, which is the case for D4, D6 and D9.
Untuk “19003”, ada dua kecocokan di kolom C, khususnya C3 dan C9. Namun, fungsi MATCH hanya memperhitungkan kecocokan pertama dan mengabaikan kecocokan lainnya. Di D5 hasilnya “1”, karena pertandingan pertama untuk “19003” berada di posisi 1 di range C3. C9.
Bandingkan dua kolom menggunakan INDEX dan MATCH
Kombinasi fungsi INDEX dan MATCH digunakan saat kita ingin mencocokkan dua kolom di Google sheets or in Microsoft Excel. The MATCH function, as discussed above, returns the position of a value in a specified column while INDEX function returns the value of a cell in a range based on a column and row number.
Sintaks fungsi INDEX
=INDEX[array, row_num, column_num]_
- array – rentang sel tempat kita ingin mengambil beberapa data
- row_num – baris dalam larik tempat kita ingin mengambil data
- column_num – kolom dalam array tempat kita ingin mengambil data;
Untuk membandingkan dua kolom B dan C dan mengembalikan nilai yang cocok dengan menggunakan INDEX dan MATCH, kami mengikuti langkah-langkah berikut.
Langkah 1 . Masukkan rumus di sel D3. =INDEX[$C$3:$C$9,MATCH[B3,$C$3:$C$9,0]]_
Langkah 2 . Tekan ENTER
Langkah 3 . Salin rumus di sel D3 ke sel D4. D9 dengan mengeklik ikon “+” di pojok kanan bawah sel D3 dan menariknya ke bawah
Larik kita adalah kumpulan nilai di kolom C, $C$3. $C$9, dan nomor baris disediakan oleh fungsi MATCH
Pada contoh pertama, fungsi MATCH kita mencari B3 atau “19001” dalam rentang C3. C9, dan mengembalikan “4” yang merupakan posisi “19001” di rentang C3. C9. Fungsi INDEX kemudian mengembalikan nilai keempat dalam larik C3. C9, yaitu C6 atau “19001”. Nomor kolom diabaikan karena kami hanya mempertimbangkan satu kolom dalam larik kami, kolom C.
Terakhir, nilai dalam sel D3 adalah “ 19001 ”. The combination of INDEX and MATCH when used to membandingkan dua kolom memberikan hasil yang sama seperti saat kita menggunakan VLOOKUP.
Bandingkan dua kolom menggunakan Pemformatan Bersyarat
Pemformatan bersyarat adalah fungsi bawaan lainnya di Excel atau Google sheets yang membantu kita membandingkan dua kolom berdasarkan seperangkat aturan.
Sorot nilai duplikat dalam dua kolom
Misalkan kita ingin membandingkan dua kolom di Excel dan menyorot kecocokan atau menggandakan nilai di B dan C. Kami mengikuti langkah-langkah ini.
Langkah 1 . Pilih sel yang ingin kita sorot, B3. C9.
Langkah 2 . Klik tab Beranda, Menu Pemformatan Bersyarat, dan pilih “ Sorot Aturan Sel ”, lalu “ Nilai Duplikat ”. The Duplicate Values dialog box will pop up.
Langkah 3 . Di kotak dialog Nilai Duplikat, pilih “ Duplikat ” dan “ Isi Merah Terang dengan Teks Merah Tua ” in the drop-down lists
Langkah 4 . Tekan OK untuk menerapkan aturan pemformatan.
Akibatnya, nilai duplikat 19001, 19003, 19005 dan 19006 di dua kolom B dan C disorot
Sorot nilai unik dalam dua kolom
Kita juga dapat membandingkan dua kolom di Excel dan menyorot perbedaannya dan menggunakan pemformatan bersyarat untuk menyorot nilai unik saja. Kami mengikuti prosedur yang sama tetapi kali ini, kami memilih “ Unik ” dari daftar drop-down dan memilih format apa pun yang kami inginkan. Dalam hal ini, kami memilih gaya pemformatan “ Isi Kuning dengan Teks Kuning Tua ”.
Akibatnya, nilai unik 19002, 19004 dan 19007 di dua kolom B dan C disorot
Sering kali, masalah yang perlu Anda selesaikan akan lebih rumit daripada aplikasi sederhana dari rumus atau fungsi. Jika Anda ingin menghemat berjam-jam riset dan frustrasi, cobalah layanan live Excelchat kami. Pakar Excel kami tersedia 24/7 untuk menjawab pertanyaan Excel apa pun yang mungkin Anda miliki. Kami menjamin koneksi dalam 30 detik dan solusi khusus dalam 20 menit.
Apakah Anda masih mencari bantuan dengan fungsi IF?