Malam tadi di rancangan radio Craig Crossman's Computer America, Joe dari Boston mempunyai soalan:
Saya mempunyai lajur nombor invois. Bagaimana saya boleh menggunakan Excel untuk menandakan pendua?
Saya mencadangkan penggunaan format bersyarat dan formula COUNTIF. Berikut adalah perincian mengenai cara membuatnya.
Kami ingin mengatur pemformatan bersyarat untuk keseluruhan rentang, tetapi lebih mudah untuk menyediakan format bersyarat untuk sel pertama dalam julat dan kemudian menyalin format bersyarat itu. Dalam kes kami, sel A1 mempunyai judul nombor invois, jadi saya akan memilih sel A2 dan dari menu, pilih Format> Pemformatan Bersyarat. Dialog Pemformatan Bersyarat dimulakan dengan dropdown awal yang mengatakan "Nilai Sel Adalah". Sekiranya anda menyentuh anak panah di sebelahnya, anda boleh memilih "Formula Is".
Setelah memilih "Formula Is", kotak dialog berubah penampilan. Daripada kotak untuk "Antara x dan y", kini terdapat kotak formula tunggal. Kotak formula ini sangat hebat. Anda boleh memasukkan formula yang boleh anda impikan, selagi formula itu akan dinilai menjadi BENAR atau SALAH.
Dalam kes kita, kita perlu menggunakan formula COUNTIF. Formula untuk menaip di dalam kotak adalah
=COUNTIF(A:A,A2)>1
Dalam bahasa Inggeris, ini mengatakan, "lihat keseluruhan julat lajur A. Hitung berapa banyak sel dalam julat itu nilai yang sama dengan yang ada di A2. (Sangat penting bahawa" A2 "dalam formula menunjukkan ke sel semasa - sel yang anda tetapkan pemformatan bersyaratnya. Jadi - jika data anda berada di lajur E dan anda menetapkan pemformatan bersyarat pertama di E5, rumusnya adalah =COUNTIF(E:E,E5)>0). Kemudian, kita membandingkan untuk melihat apakah jumlahnya adalah> 1. Sebaik-baiknya, tanpa pendua, kiraan akan selalu 1 - kerana sel A2 berada dalam julat - kita harus mencari tepat satu sel di lajur A yang mengandungi nilai yang sama dengan A2.
Klik butang Format…
Sekarang masanya untuk memilih format yang menjengkelkan. Terdapat tiga tab di bahagian atas dialog Format Sel ini. Tab Font biasanya pertama, jadi anda boleh memilih font yang tebal dan merah, tetapi saya suka sesuatu yang lebih menjengkelkan. Saya biasanya mengklik tab Corak dan memilih sama ada merah terang atau kuning terang. Pilih warna, kemudian klik OK untuk menutup dialog Format Sel.
Anda akan melihat format yang dipilih dalam kotak "Pratonton format yang akan digunakan". Klik OK untuk menutup dialog Pemformatan Bersyarat…
… dan tidak ada yang berlaku. Wah. Sekiranya ini adalah kali pertama anda membuat pemformatan bersyarat, sangat senang mendapat maklum balas di sini bahawa ia berfungsi. Tetapi, melainkan jika anda cukup bernasib baik kerana 1098 dalam sel A2 adalah pendua dari beberapa sel lain, keadaannya tidak benar, dan sepertinya tidak ada yang berlaku.
Anda perlu menyalin format bersyarat dari A2 ke sel lain dalam julat anda. Dengan kursor kursor di A2, lakukan Edit> Salin. Tekan Ctrl + Spacebar untuk memilih keseluruhan lajur. Lakukan Edit> Tampal Istimewa. Dalam dialog Tampal Khas, klik Format. Klik OK.
Ini akan menyalin pemformatan bersyarat ke semua sel di lajur. Sekarang - akhirnya - anda melihat beberapa sel dengan format merah, yang menunjukkan bahawa anda mempunyai pendua.
Adalah bermaklumat untuk pergi ke sel A3 dan melihat format bersyarat selepas salinannya. Pilih A3, tekan od untuk memaparkan format bersyarat. Rumus dalam kotak Formula Is diubah untuk mengira berapa kali A3 muncul di lajur A: A.
Catatan
Dalam pertanyaan Joe, dia hanya memiliki 1700 invois dalam jangkauan. Saya telah menyediakan 65536 sel dengan pemformatan bersyarat dan setiap sel membandingkan sel semasa dengan 65536 sel lain. Di Excel 2005 - dengan lebih banyak baris - masalahnya akan menjadi lebih teruk lagi. Secara teknikal, formula pada langkah pertama adalah:=COUNTIF($A$2:$A$1751,A2)>1
Juga, semasa menyalin format bersyarat ke seluruh lajur, anda boleh memilih hanya baris dengan data sebelum melakukan Paste Special Format.
Lebih banyak lagi
Masalah lain yang saya jelaskan selepas soalan ini adalah bahawa anda benar-benar tidak dapat menyusun lajur berdasarkan format bersyarat. Sekiranya anda perlu menyusun data ini agar pendua berada dalam satu kawasan, ikuti langkah-langkah ini. Pertama, Tambahkan tajuk ke B1 yang disebut "Pendua?". Taipkan formula ini dalam B2: =COUNTIF(A:A,A2)>1.
Dengan penunjuk sel di B2, klik pemegang isian automatik (kotak kecil di sudut kanan bawah sel) untuk menyalin formula sepanjang julat.
Anda kini boleh mengisih mengikut lajur B menurun dan A menaik untuk mempunyai faktur masalah di bahagian atas julat.
Penyelesaian ini mengandaikan bahawa anda ingin menonjolkan KEDUA dari invois pendua sehingga anda dapat mengetahui secara manual mana yang hendak dihapus atau dibetulkan. Jika anda tidak mahu untuk menandakan kejadian pertama salinan, anda boleh melaraskan formula untuk menjadi: =COUNTIF($A$2:$A2,A2)>1. Penting untuk memasukkan tanda dolar seperti yang ditunjukkan. Ini akan melihat semua sel dari sel semasa sahaja, mencari entri pendua.
Terima kasih kepada Joe dari Boston untuk soalannya!
