Craig Crossman'ın Computer America radyo programında dün gece Boston'dan Joe'nun bir sorusu vardı:
Fatura numaralarından oluşan bir sütunum var. Yinelenenleri işaretlemek için Excel'i nasıl kullanabilirim?
Koşullu biçimleri ve EĞERSAY formülünü kullanmayı önerdim. İşte bunun nasıl yapılacağına dair ayrıntılar.
Tüm aralık için koşullu biçimlendirme ayarlamak istiyoruz, ancak aralıktaki ilk hücre için bir koşullu biçim ayarlamak ve ardından bu koşullu biçimi kopyalamak daha kolaydır. Bizim durumumuzda, A1 hücresinde bir fatura numarası başlığı vardır, bu yüzden A2 hücresini seçeceğim ve menüden Biçim> Koşullu Biçimlendirme'yi seçeceğim. Koşullu Biçimlendirme iletişim kutusu, "Hücre Değeri" yazan ilk açılır menü ile başlar. Bunun yanındaki oka dokunursanız, "Formula Is" seçeneğini seçebilirsiniz.
"Formula Is" seçildikten sonra, iletişim kutusunun görünümü değişir. "X ve y arasında" kutuları yerine artık tek bir formül kutusu var. Bu formül kutusu inanılmaz derecede güçlü. Bu formül DOĞRU veya YANLIŞ olarak değerlendirildiği sürece, hayal edebileceğiniz herhangi bir formülü yazabilirsiniz.
Bizim durumumuzda, bir EĞERSAY formülü kullanmamız gerekiyor. Kutuya yazılacak formül
=COUNTIF(A:A,A2)>1
İngilizce'de bu, "A sütununun tüm aralığına bakın. Bu aralıktaki kaç hücrenin A2 hücresiyle aynı değere sahip olduğunu sayın. (Formüldeki" A2 "ifadesinin, geçerli hücre - içinde koşullu biçimlendirmeyi ayarladığınız hücre. Yani - verileriniz E sütunundaysa ve E5'te ilk koşullu biçimlendirmeyi ayarlıyorsanız, formül şöyle olacaktır =COUNTIF(E:E,E5)>0). Ardından, bu sayının geçerli olup olmadığını görmek için karşılaştırırız İdeal olarak, yineleme olmadan sayı her zaman 1 olacaktır - çünkü A2 hücresi aralıktadır - A sütununda A2 ile aynı değeri içeren tam olarak bir hücre bulmalıyız.
Biçimlendir… düğmesini tıklayın
Şimdi iğrenç bir format seçme zamanı. Bu Hücreleri Biçimlendir iletişim kutusunun üst kısmında üç sekme vardır. Yazı Tipi sekmesi genellikle önce gelir, bu nedenle Kalın, kırmızı bir yazı tipi seçebilirsiniz, ancak daha iğrenç bir şey seviyorum. Genellikle Desenler sekmesini tıklıyorum ve parlak kırmızı veya parlak sarıyı seçiyorum. Rengi seçin, ardından Hücreleri Biçimlendir iletişim kutusunu kapatmak için Tamam'ı tıklayın.
Seçilen biçimi "Kullanılacak biçimin önizlemesi" kutusunda göreceksiniz. Koşullu Biçimlendirme iletişim kutusunu kapatmak için Tamam'ı tıklayın…
… Ve hiçbir şey olmuyor. Vay. Koşullu biçimlendirmeyi ilk kez kuruyorsanız, burada işe yaradığına dair bazı geri bildirimler almak gerçekten güzel olurdu. Ancak, A2 hücresindeki 1098'in başka bir hücrenin kopyası olduğu konusunda yeterince şanslı değilseniz, koşul doğru değildir ve hiçbir şey olmamış gibi görünür.
Koşullu biçimlendirmeyi A2'den aralığınızdaki diğer hücrelere kopyalamanız gerekir. A2'de imleç eşiği ile Düzenle> Kopyala'yı yapın. Tüm sütunu seçmek için Ctrl + Boşluk Çubuğu'na basın. Düzen> Özel Yapıştır yapın. Özel Yapıştır iletişim kutusunda Biçimler'i tıklayın. Tamam'ı tıklayın.
Bu, koşullu biçimlendirmeyi sütundaki tüm hücrelere kopyalayacaktır. Şimdi - nihayet - bir kopyasına sahip olduğunuzu belirten kırmızı biçimlendirmeli bazı hücreler görüyorsunuz.
A3 hücresine gidip kopyadan sonra koşullu biçime bakmak bilgilendiricidir. Koşullu biçimlendirmeyi getirmek için A3'ü seçin, od tuşuna basın. Formüldür kutusundaki formül, A3'ün A: A sütununda kaç kez göründüğünü saymak için değiştirildi.
Notlar
Joe'nun sorusuna göre, aralıkta yalnızca 1700 faturası vardı. Koşullu biçimlendirmeye sahip 65536 hücre kurdum ve her hücre mevcut hücreyi 65536 diğer hücreyle karşılaştırıyor. Excel 2005'te - daha fazla satırla - sorun daha da kötü olacaktır. Teknik olarak, ilk adımdaki formül şöyle olabilirdi:=COUNTIF($A$2:$A$1751,A2)>1
Ayrıca, koşullu biçimi tüm sütuna kopyalarken, Özel Biçimleri Yapıştır'ı yapmadan önce bunun yerine yalnızca verileri içeren satırları seçebilirdiniz.
Daha
Sorudan sonra anlattığım bir diğer konu ise, bir sütunu gerçekten koşullu bir biçime göre sıralayamamanızdır. Yinelenenlerin tek bir alanda olması için bu verileri sıralamanız gerekiyorsa, aşağıdaki adımları izleyin. İlk olarak, B1'e "Yineleniyor mu?" Adlı bir başlık ekleyin. Bu formülü B2: yazın =COUNTIF(A:A,A2)>1.
B2 hücresinde hücre işaretçisiyle, formülü aralığın sonuna kadar kopyalamak için otomatik doldurma tutamacına (hücrenin sağ alt köşesindeki küçük kare) tıklayın.
Artık, sorunlu faturaların aralığın en üstünde olması için B sütununa göre azalan ve A artan şekilde sıralayabilirsiniz.
Bu çözüm, hangisinin silineceğini veya düzeltileceğini manuel olarak bulabilmeniz için yinelenen faturaların İKİSİNİ vurgulamak istediğinizi varsayar. Eğer yinelenen ilk oluşumunu işaretlemek istemiyorsanız, söz konusu yöntemi formülü ayarlayabilirsiniz: =COUNTIF($A$2:$A2,A2)>1. Dolar işaretlerini tam olarak gösterildiği gibi girmek önemlidir. Bu, yalnızca geçerli hücreden üstteki tüm hücrelere bakacak ve yinelenen girişleri arayacaktır.
Boston'dan Joe'ya soru için teşekkürler!
