Uzun bir listede aynı değerlerden birden fazla var ise biz bunları nasıl bulabiliriz, nasıl süzebiliriz ve tabii ki sadece eşsiz değerlerden oluşan bir listeyi nasıl elde ederiz? Öncelikle eğer formül ile listedeki eşsiz değerleri çekmek istiyorsanız buradan ulaşacağınız sayfadaki yazıları okuyun.
Bu yazıda kullanacağımız formüller için buradan ve buradan ulaşabileceğiniz sayfalardaki yazıları okumuş olduğunuzu varsayıyorum.
Uygulamada kullandığım Excel dosyasına da buraya tıklayarak indirebilirsiniz.
1. Koşullu Biçimlendirme İle Yinelenen Değerleri Gösterme
Elimizde aşağıdaki gibi değerler listesi olduğunu varsayalım:
| Meyveler | ||
|---|---|---|
| Erik | Ahududu | Armut |
| Frenküzümü | Alıç | Böğürtlen |
| İğde | Armut | Çilek |
| Karadut | Ayva | Dut |
| Karayemiş | Çilek | Erik |
| Kayısı | Elma | Karayemiş |
| Kızılcık | Erik | Kayısı |
| Kiraz | İğde | Kızılcık |
| Kuşburnu | İğde | Kocayemiş |
| Şeftali | Kayısı | Muşmula |
| Vişne | Kuşburnu | Üzüm |
| Yemişen | İnnap | Vişne |
Bu listede yer alan değerlerin birden fazla kopyalarını hızlıca görebilmek için önce listemizi seçiyoruz ve ardından Koşullu Biçimlendirme kullanabiliriz. Bunun için “Giriş” sekmesinde iken Stiller kısmında yer alan “Koşullu Biçimlendirme” simgesine tıklıyoruz ve gelen menüden “Hücre Kurallarını Vurgula” kısmından “Yinelenen Değerler” seçeneğini seçiyoruz.
Ardından gelen pencerede ne tür bir biçimlendirme istediğimizi seçiyoruz. Bu pencerede isterseniz benzersiz değerler için de biçimlendirme seçebilirsiniz.
İşlem sonucunda elinizde seçtiğiniz biçimlendirme şekline göre farklı renklerden oluşmuş aşağıdaki gibi bir tablo görüntüsü elde edeceksiniz.
Bu sayede tabloda hangi değerlerden birden fazla bulunduğunu hızlıca görebilirsiniz. Ancak fazladan yer alan bilgileri elemek için elinizde çok fazla imkan da yoktur. Yani hangi değer nerede ilk ortaya çıktı, kopyaları nerede yer alıyor görme ve müdahale etme imkanınız kısıtlıdır.
Ek olarak bu tarz birden fazla sütundan oluşan tablolarda genel olarak hataya müdahale etmek tek sütunda alta alta dizilmiş değerlere müdahale etmekten daha zordur. Bu yüzden tabloyu önce liste haline getirmek çok daha faydalı olacaktır.
2.EĞERSAY İle Yinelenen Değerleri Bulma
Bu örnek için listeyi dikey hale getirdim. Tüm değerler tek bir sütunda, B sütunu 3-38 arası, yer alıyor.Ben C sütununa aşağıdaki formülü yazıyorum:
=EĞERSAY($B$3:$B$38;$B3)
Bu formülü aşağıya doğru çoğaltırsam tüm değerlerin toplamda kaç adet yer aldığını bulabilirim. Ancak bu formül bana 1-2-3 gibi rakam verecektir Ben hangi değerlerin birden fazla yer aldığını daha net görmek için bu formülü bir de “EĞER” formülü ile birleştirebilirim.
=EĞER(EĞERSAY($B$3:$B$38;$B3)<=1;”TEK”;”ÇOK”)
Eğer bu formülü aşağıya doğru uzatırsam aşağıdaki bir gibi bir liste oluşur.
Eğer sadece birden fazla yer alan değerleri görmek istiyorsak EĞER formülünü aşağıdaki gibi güncellememiz gerekecektir.
=EĞER(EĞERSAY($B$3:$B$38;$B3)<=1;””;”ÇOK”)
Bu durumda tablomuzun görüntüsü aşağıdaki gibi oluşacaktır.
Tablomuzu seçerek “Veri” sekmesinden tabloya filtre eklediğimizde ve formülün olduğu sütunda “Boş” olan satırları elediğimizde birden fazla kopyası olan değerleri rahatlıkla süzebiliriz.
3.Aynı Değerin İlk Oluşumunu Hariç Tutma
Çoğu zaman listeden sadece ilk değeri atlayarak fazladan eklenmiş değerleri listelememiz gerekir. Bunun için de yine EĞERSAY formülünü biraz değiştirerek kullanacağız.
=EĞERSAY($B$3:$B3;$B3)
Bu formülde dikkat ederseniz aralığımız $B$3:$B3 ile başlıyor. Formülü aşağıya doğru çoğalttığımızda aralığımız da $B$3:$B4, $B$3:$B5 gibi her satırda değişen bir yapıya sahip olacak. Ve sayma işlemi B3 hücresinden başlayarak o satırdaki B hücresine kadar olacaktır. Bu sayede formül, B hücresindeki değerin ilk karşılaşıldığı anda 1 değerine sahip olacak ve aşağıya doğru uzattığımızda aynı değerden birden fazla var ise 2, 3 gibi değer döndürecektir.
Şimdi bu formülü EĞER formülü ile birleştirerek değerin ilk ortaya çıktığı satırı es geçerek tekrar ettiği diğer satırları bulalım:
=EĞER(EĞERSAY($B$3:$B3;$B3)>1;”ÇOK”;””)
Bu formülü yazarak aşağıya doğru uzattığımızda aşağıdaki gibi bir görüntü elde ederiz. Tabii ki bu tabloyu da filtre uygulayarak süzebiliriz.
4. Büyük Küçük Harf Duyarlı Yinelenen Değerleri Bulma
Anlattığım formüllerin hepsi büytük küçük harf dikkate almadan, hücre içindeki değeri kontrol eder. Yani Erik ile erik hatta ERİK aynı değerdir ve formül tüm bu değerleri aynıymış gibi dikkate alarak sonuç döndürür. Eğer biz bu tarz bir ayrım yapmak istiyorsak daha farklı bir formül kullanmak zorundayız.
Excel’de metin değerinin bire bir aynısını bulmamıza yarayan formül ÖZDEŞ formülü.
=ÖZDEŞ($B$3:$B$23;$B3)
Özdeş normalde 2 değeri kıyaslar, burada bir aralık ile kıyaslama yaptığımız için bu formülü CTRL+SHIFT+ENTER tuşlarına basarak dizin formülü haline getirmemiz gerekecek. Sonrasında elimizde (DOĞRU; YANLIŞ;YANLIŞ;…} şeklinde bir dizin oluşacak demektir. Bunu TOPLA formülü ile birleştirmemiz gerek ama bunu yaparken DOĞRU değerini 1, YANLIŞ değerini 0 olarak çevirmemiz de lazım. Bunu için kullanacağımız formül
=TOPLA(–ÖZDEŞ($B$3:$B$23;$B3))
ÖZDEŞ formülünden önceki – ifadesi DOĞRU yerine 1, Yanlış yerine 0 almamızı sağlıyor. Formülü aşağıya doğru uzattığımızda aşağıdaki gibi bir tablo elde edeceğiz:
Tabloyu daha anlaşılabilir yapmak için bu formülü aşağıdaki EĞER formülünü ile de kullanabiliriz:
=EĞER(TOPLA(–ÖZDEŞ($B$3:$B$23;$B3))>1;”ÇOK”;””)
Tüm formülleri CTRL+SHIFT+ENTER ile dizin formülü haline getirmeyi unutmayın lütfen. Bu işlemler doğru yaptıysanız aşağıdaki gibi bir tablonuz olur:
Gördüğünüz gibi tabloda büyük küçük harfe göre bir karşılaştırma yapıldı, 3 satırdaki Erik ve 12. Satırdaki ERİK değerleri artık eşsiz değer olarak gözüküyor. Eğer formülü ilk oluşumları dikkate almayacak şekilde değiştirmek itersek yine bir önceki adımda yaptığımız gibi ÖZDEŞ formülünü sadece o satıra kadar olan değerleri dikkate alacak şekilde güncellememiz gerek:
= ÖZDEŞ($B$3:$B$3;$B3))
Ve nihai formülümüzde aşağıdaki gibi oluşmalı:
=EĞER(TOPLA(–ÖZDEŞ($B$3:$B$3;$B3))>1;”ÇOK”;””)
Formülü CTRL+SHIFT+ENTER tuşlarına basarak dizin formülü olarka girip aşağıya doğru çoğaltın. Bu durumda karşılaştırma işlemi bir önceki adımda olduğu gibi her satırda, o satıra göre artan bir aralıkta yapılacağı için aynı değerin ilk ortaya çıktığı satırdaki formül sonucu boş değer olacaktır.
5. Birden Çok Sütundaki Verilerde Yinelenenleri Bulma
Bazı durumlarda yinelenen veriler birden fazla sütundaki değerlere göre oluşmuş olabilir. Örnek olarak aşağıdaki tablodaki verilere bir bakalım:
Tabloda görebileceğiniz üzere sipariş numarası altında birden fazla ürün yer almakta ancak aynı siparişte aynı üründen 2 tane olması istenmemekte. Bu durumda kontrol edeceğimiz şart birden fazla olduğundan kullanacağımız formül de ÇOKESAY olacak. Ben burada ilk oluşumu görmek istemediğimden diğer uygulamalardaki gibi aralığı satıra göre değişecek şekilde ayarlayarak H sütununa aşağıdaki formülü yazıyorum:
=ÇOKEĞERSAY($B$3:$B3;$B3;$C$3:$C3;$C3)
Bu formülü yazıp aşağıya doğru çoğalttığımızda aşağıdaki gibi bir tablo elde ederiz.
Gördüğünüz gibi aynı sipariş numarasına sahip aynı ürün koduna göre yapılan kıyaslamaya göre satırlarda 1-2-3 gibi rakamlar görüntülendi. Burada 1 olan satırlar ilk karşılaşılan satırlar. diğerleri ise kopyalar. Ben kopyaları daha rahat görmek için bu formülü EĞER ile birleştireceğim:
=EĞER(ÇOKEĞERSAY($B$3:$B3;$B3;$C$3:$C3;$C3)=1;””;”ÇOK”)
Artık tablomuz aşağıdaki haleg gibi oluşacak ve hangi satırların birden fazla olduğunu daha rahat görme imkanımız olacaktır.
6. Yinelenenleri Kaldırma
Yinelenen değerleri kaldırmak için kullanabileceğiniz ilk yöntem Veri sekmesindeki gelişmiş filtredir. Bunun için önce aralığımızı seçiyoruz sonra veri sekmesinde yer alan gelişmiş seçneğine tıklıyoruz. Sonrasında gelen pencerede “Başka bir yere kopyala” seçeneğini seçiyoruz, Hedef kısmına veriyi kopyalamak istediğimiz sütununun ilk hücresini seçiyoruz ve en altta yer alan “Yalnız benzersiz kayıtlar” kutusuna tıklıyoruz. Tamam düğmesine bastığımızda Excel tüm listede yer alan tüm benzersiz değerleri seçtiğiniz hücreden başlayarak aşağıya doğru bir liste oluşturacak şekilde kopyalayacaktır.
Bu yöntem oldukça hızlı olsa da bir kaç kısıtlaması mevcut. İlk kısıtlama birden fazla satıra dağılmış verilerde bu seçeneği kullanmak sıkıntılıdır. Excel bu tarz verilerde eşsiz değerleri bulamaz. İkincisi ise seçtiğiniz hücrenin altına doğru, geri döndürülen değerler adedince boş hücre olması zorunluluğudur. Eğer yeterli boş hücre yok ise Excel hata vermeden bu hücrelerin içeriğini değiştirecektir ve sizin girdiğiniz değerler kaybolacaktır.
İkinci yöntem ise süzme kullanma. Bunun için yukarıda anlattığım formüller ile satırların hangisinde aynı değerden yer aldığını bulmanız gerekmektedir. Bunun için de ilk oluşumları ihmal etmeniz daha doğru olacaktır. Sonra tabloya Veri sekmesinden Filtre uygulayara çok olan satırları süzün. Sonrasında bu satırları silin.
İlginizi Çekebilecek Diğer Yazılar
Grafiklerde Değişken Aralık Kullanma
Mantıksal Karşılatırmalar
Tablolarda Yapısal Başvuru
Metinleri Formülle Sıralama
Belirsiz, Belirli Kesişim ve @ İşleci
DüşeyAra ve Kaçıncı Kullanımı
Excel Hücre Temizleme
Excel Özet Tablo
Yinelenen Değerleri Gösterme ve Kaldırma
You may also like this
merhaba,
sayfalar arası aynı veriyi renklendirmek istiyorum yani tek bir dosyam var ama onun içinde her güne ait ayrı ayrı veri tabloları(barkod listesi) var.günler arası aynı barkod olursa renklenmesini istiyorum.
Merhaba,
Tek bir koşullu biçimlendirme kullanarak birden fazla sayfaya uygulanabilecek bir biçimlendirme yapamazsınız. Her bir sayfada aynı koşula sahip bir biçimlendirme tanımlamanız gerek. Burada koşullu biçimelndirmeye girdiğinizde “Yeni Kural” oradan da “Biçimlenmdirilecek hücreleri belirlemek için formül kullan” seçeneğini seçmeniz gerek. SOnra misal A2=değer gibi bir formül girerek uygulama aralığını verilerinizin olduığu aralık olarak seçmelisiniz.
http://excelileharikalar.com/index.php/2019/10/20/kosullu-bicimlendirme/
Saygılarımla,