Tadi malam di acara radio Komputer Amerika Craig Crossman, Joe dari Boston memiliki pertanyaan:
Saya memiliki kolom nomor faktur. Bagaimana saya bisa menggunakan Excel untuk menandai duplikat?
Saya menyarankan menggunakan format bersyarat dan rumus COUNTIF. Berikut adalah detail tentang cara membuatnya berfungsi.
Kami ingin menyiapkan pemformatan bersyarat untuk seluruh rentang, tetapi lebih mudah untuk menyiapkan format bersyarat untuk sel pertama dalam rentang lalu menyalin format bersyarat tersebut. Dalam kasus kami, sel A1 memiliki judul nomor faktur, jadi saya akan memilih sel A2 dan dari menu, pilih Format> Format Bersyarat. Dialog Conditional Formatting dimulai dengan dropdown awal yang bertuliskan "Cell Value Is". Jika Anda menyentuh panah di samping ini, Anda dapat memilih "Formula Is".
Setelah memilih "Formula Is", tampilan kotak dialog berubah. Alih-alih kotak untuk "Antara x dan y", sekarang ada satu kotak rumus. Kotak formula ini sangat kuat. Anda dapat mengetikkan rumus apa pun yang Anda impikan, selama rumus tersebut akan mengevaluasi BENAR atau SALAH.
Dalam kasus kami, kami perlu menggunakan rumus COUNTIF. Rumus untuk mengetik di kotak adalah
=COUNTIF(A:A,A2)>1
Dalam bahasa Inggris, ini mengatakan, "lihat seluruh rentang kolom A. Hitung berapa banyak sel dalam rentang itu yang memiliki nilai yang sama dengan yang ada di A2. (Sangat penting bahwa" A2 "dalam rumus mengarah ke sel saat ini - sel tempat Anda menyetel pemformatan bersyarat. Jadi - jika data Anda ada di kolom E dan Anda menyetel pemformatan bersyarat pertama di E5, rumusnya adalah =COUNTIF(E:E,E5)>0). Kemudian, kita bandingkan untuk melihat apakah itu dihitung adalah> 1. Idealnya, tanpa duplikat, hitungannya akan selalu 1 - karena sel A2 berada dalam kisaran - kita harus menemukan tepat satu sel di kolom A yang berisi nilai yang sama dengan A2.
Klik tombol Format…
Sekarang saatnya memilih format yang menjengkelkan. Ada tiga tab di bagian atas dialog Format Sel ini. Tab Font biasanya lebih dulu, jadi Anda bisa memilih yang Tebal, font merah, tapi saya suka sesuatu yang lebih menjengkelkan. Saya biasanya mengklik tab Patterns dan memilih antara merah terang atau kuning cerah. Pilih warnanya, lalu klik OK untuk menutup dialog Format Cells.
Anda akan melihat format yang dipilih di kotak "Pratinjau format yang akan digunakan". Klik OK untuk menutup dialog Conditional Formatting…
… Dan tidak ada yang terjadi. Wow. Jika ini adalah pertama kalinya Anda menyiapkan pemformatan bersyarat, akan sangat menyenangkan mendapatkan umpan balik di sini bahwa ini berfungsi. Tapi, kecuali Anda cukup beruntung bahwa 1098 di sel A2 adalah duplikat dari beberapa sel lain, kondisinya tidak benar, dan sepertinya tidak terjadi apa-apa.
Anda perlu menyalin pemformatan bersyarat dari A2 ke sel lain dalam rentang Anda. Dengan ambang kursor di A2, lakukan Edit> Salin. Tekan Ctrl + Spacebar untuk memilih seluruh kolom. Lakukan Edit> Tempel Spesial. Dalam dialog Tempel Spesial, klik Format. Klik OK.
Ini akan menyalin pemformatan bersyarat ke semua sel di kolom. Sekarang - akhirnya - Anda melihat beberapa sel dengan format merah, menunjukkan bahwa Anda memiliki duplikat.
Adalah informatif untuk pergi ke sel A3 dan melihat format bersyarat setelah penyalinan. Pilih A3, tekan od untuk menampilkan pemformatan bersyarat. Rumus di kotak Formula Is berubah untuk menghitung berapa kali A3 muncul di kolom A: A.
Catatan
Dalam pertanyaan Joe, dia hanya memiliki 1.700 faktur dalam kisaran tersebut. Saya telah menyiapkan 65536 sel dengan pemformatan bersyarat dan setiap sel membandingkan sel saat ini dengan 65536 sel lainnya. Di Excel 2005 - dengan lebih banyak baris - masalahnya akan menjadi lebih buruk. Secara teknis, rumus pada langkah pertama bisa jadi adalah:=COUNTIF($A$2:$A$1751,A2)>1
Selain itu, saat menyalin format bersyarat ke seluruh kolom, Anda bisa memilih hanya baris dengan data sebelum melakukan Tempel Format Khusus.
Lebih
Masalah lain yang saya jelaskan setelah pertanyaan adalah bahwa Anda benar-benar tidak dapat mengurutkan kolom berdasarkan format bersyarat. Jika Anda perlu mengurutkan data ini sehingga duplikat berada di satu area, ikuti langkah-langkah berikut. Pertama, Tambahkan heading ke B1 yang disebut "Duplicate?". Ketik rumus ini di B2: =COUNTIF(A:A,A2)>1.
Dengan penunjuk sel di B2, klik gagang IsiOtomatis (kotak kecil di sudut kanan bawah sel) untuk menyalin rumus sepenuhnya ke bawah rentang.
Anda sekarang dapat mengurutkan menurut kolom B turun dan A naik untuk memiliki faktur bermasalah di bagian atas rentang.
Solusi ini mengasumsikan bahwa Anda ingin menyorot KEDUA faktur duplikat sehingga Anda dapat secara manual mencari tahu mana yang akan dihapus atau diperbaiki. Jika Anda tidak ingin menandai kejadian pertama duplikat, Anda dapat menyesuaikan rumus menjadi: =COUNTIF($A$2:$A2,A2)>1. Penting untuk memasukkan tanda dolar persis seperti yang ditunjukkan. Ini akan melihat semua sel dari sel saat ini ke atas saja, mencari entri duplikat.
Terima kasih kepada Joe dari Boston atas pertanyaannya!
