Bu eğitimde Excel’de tekrar eden verilerin nasıl aranacağı açıklanmaktadır. Tekrar eden verileri tanımlamak veya ilk oluşumu olan ya da olmayan tekrar eden verileri bulmak için birkaç formül öğreneceksiniz. Ayrıca, her yinelenen kaydın örneklerini ayrı ayrı saymayı ve bir sütundaki toplam tekrar eden sayısını bulmayı, tekrar eden verileri nasıl filtreleyeceğinizi ve daha fazlasını öğreneceksiniz.
Büyük bir Excel çalışma sayfasıyla çalışırken veya birkaç küçük elektronik tabloyu daha büyük bir tabloda birleştirirken, içinde çok sayıda tekrar eden veri bulabilirsiniz.
Bu makalede tekrar eden verileri tespit etmekle ilgili birkaç çözüm okuyacaksınız. Bu çözümler Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013 ve daha düşük sürümlerin tümünde çalışır.
Örnek çalışma dosyasını indirmek için buraya tıklayın.
Excel’de tekrar eden verileri bulma, vurgulama, silme yolları
1 – Tekrar eden verileri Yinelenenleri kaldır komutuyla silme
Tekrar eden verileri kaldırma çok sık kullanılan ve çok kişinin kullandığı bir fonksiyon. Belki de bu yüzden excel üst menüsünde pratik komutların içinde kocaman bir ikonla bulunuyor.
Verinizi seçip “yinelenenleri kaldır” a tıklayınca bu komut seçtiğiniz veriyi olduğu yerde azaltıyor.
Size kaç tane sildiğinin de bilgisini vererek listenizi kısaltıyor. Ama hangi tekrar eden verileri sildiğini söylemiyor.
2 – Tekrar eden verileri başka bir konuma kopyalayarak kopyalardan kurtulun
Excel’de yinelemelerden kurtulmanın bir başka yolu da benzersiz değerleri ayırmak ve bunları başka bir sayfaya veya farklı bir çalışma kitabına kopyalamaktır. Ayrıntılı adımlar aşağıda verilmiştir.
1 – Tekilleştirmek istediğiniz aralığı veya tablonun tamamını seçin.
2 – Veri sekmesi > Sırala ve Filtrele grubuna gidin ve Gelişmiş düğmesine tıklayın.
3 – Gelişmiş Filtre iletişim penceresinde aşağıdakileri yapın:
- Başka bir konuma kopyala düğmesini seçin.
- Liste Aralığı’nda doğru aralığın görünüp görünmediğini doğrulayın Bu, 1. adımda seçtiğiniz aralık olmalıdır.
- Şuraya kopyala kutusuna benzersiz değerleri kopyalamak istediğiniz aralığı girin (aslında hedef aralığın sol üst hücresini seçmek yeterlidir).
- Yalnızca benzersiz kayıtlar kutusunu seçin.
4 – Son olarak, Tamam’a tıklayın ve benzersiz değerler yeni bir konuma kopyalanacaktır:
3 – Özet tablo ile tekrar eden veriyi özetleme
Bir çoğu zaman iş ortamında tekrar eden verilerin olduğu dosyalar önünüze geliyor olabilir. Amacınız tekrar eden verileri sadece silmek değil toplayıp tek satırda göstermekse özet tablo kullanabilirsiniz. Tüm veriyi seçin. Ekle -> özet tablo menüsünden pivot tablo seçin ve varolan çalışma sayfasında bir hücre seçin. Tamam’a tıklayın.
Alanları da yerine koyduğunuzda özet tabloda tekrar eden verileri toplayarak tek satıra indirirsiniz.
Excel’de tekrar eden veriler nasıl belirlenir
Excel’de tekrar eden verileri saptamanın en kolay yolu EĞERSAY işlevini kullanmaktır. Yinelenen değerleri ilk oluşlarıyla mı yoksa ilk oluşları olmadan mı bulmak istediğinize bağlı olarak, aşağıdaki örneklerde gösterildiği gibi formülde küçük bir değişiklik olacaktır.
4 – İlk tekrar dahil olmak üzere tekrar eden verileri bulma
A sütununda yinelenenleri kontrol etmek istediğiniz bir öğe listeniz olduğunu varsayalım. Bunlar faturalar, ürün kimlikleri, adlar veya başka herhangi bir veri olabilir.
İşte Excel’de ilk oluşumlar dahil olmak üzere tekrar eden verileri bulmak için bir formül (burada A2 en üstteki hücredir):
=EĞERSAY(A:A;A2)>1
Yukarıdaki formülü B2’ye girin, ardından B2’yi seçin ve formülü diğer hücrelere kopyalamak için doldurma tutamacını sürükleyin:
Yukarıdaki ekran görüntüsünde görebileceğiniz gibi, formül tekrar eden veriler için DOĞRU, benzersiz değerler için YANLIŞ döndürür.
Not. Tekrar eden verileri bir sütunun tamamında değil de bir hücre aralığında bulmanız gerekiyorsa, bu aralığı $ işaretiyle kilitlemeyi unutmayın. Örneğin, A2:A8 hücrelerindeki yinelemeleri aramak için şu formülü kullanın:
=EĞERSAY($A$2:$A$8;A2)>1
5 – Sonuçlara özel metin yazmak
Yinelenen bir formülün DOĞRU ve YANLIŞ mantık değerlerinden daha anlamlı bir şey döndürmesi için, bunu EĞER işlevinin içine alın ve yinelenen ve benzersiz değerler için istediğiniz etiketleri yazın:
=EĞER(EĞERSAY($A$2:$A$8;$A2)>1;”Tekrar eden veri”;”Benzersiz”)
Excel formülünün yalnızca yinelenenleri bulmasını istiyorsanız, “Benzersiz” ifadesini aşağıdaki gibi boş bir dizeyle (“”) değiştirin:
=EĞER(EĞERSAY($A$2:$A$8;$A2)>1;”Tekrar eden veri”;””)
Formül, yinelenen kayıtlar için “Yinelenenler” ve Benzersiz kayıtlar için boş bir hücre döndürür:
6 – Excel’de sadece 2. ve diğer tekrarları göstermek (1.tekrarlar olmadan)
Tekrar eden verileri bulduktan sonra filtrelemeyi veya kaldırmayı planlıyorsanız, yukarıdaki formülü kullanmak güvenli değildir çünkü tüm aynı kayıtları tekrar eden veri olarak işaretler. Benzersiz değerleri listenizde tutmak istiyorsanız, tüm tekrar eden verileri silemezsiniz, yalnızca 2. ve sonraki tüm örnekleri silmeniz gerekir.
Öyleyse, uygun yerlerde mutlak ve göreli hücre referanslarını kullanarak Excel yinelenen formülümüzü değiştirelim:
=EĞER(EĞERSAY($A$2:$A2;$A2)>1;”Tekrar eden veri”;””)
Aşağıdaki ekran görüntüsünde görebileceğiniz gibi, bu formül “Elma” sözcüğünün ilk geçtiği yeri yinelenen olarak tanımlamaz:
Excel’de yinelemeler nasıl sayılır
Excel sayfanızdaki aynı kayıtların tam sayısını bilmek istiyorsanız, tekrar eden verileri saymak için aşağıdaki formüllerden birini kullanın.
7 – Her bir tekrar eden verileri sayın
Tekrar eden veriler içeren bir sütununuz olduğunda, genellikle bu değerlerin her biri için kaç tane yineleme olduğunu bilmeniz gerekebilir.
Excel çalışma sayfanızda şu veya bu girdinin kaç kez oluştuğunu bulmak için, A2’nin listenin ilk ve A8’in son öğesi olduğu basit bir EĞERSAY formülü kullanın:
=EĞERSAY($A$2:$A$8;$A2)
Aşağıdaki ekran görüntüsünde gösterildiği gibi, formül her bir öğenin kaç tekrar olduğunu sayar: “Elmalar” 3 kez, “Yeşil muzlar” – 2 kez, “Muzlar” ve “Portakallar” yalnızca bir kez.
8 – Her bir tekrar eden verinin kaçıncı tekrarı olduğunu bulmak
Her bir öğenin 1., 2., 3., vb. yani kaçıncı tekrarı olduğunu tanımlamak istiyorsanız, aşağıdaki formülü kullanın:
=EĞERSAY($A$2:$A2;$A2)
Benzer bir şekilde, yinelenen satırların oluşumlarını sayabilirsiniz. Tek fark, EĞERSAY yerine ÇOKEĞERSAY işlevini kullanmanız gerekecek olmasıdır. Örneğin:
=ÇOKEĞERSAY($A$2:$A$8;$A2;$B$2:$B$8;$B2)
Tekrar eden veriler sayıldıktan sonra, Benzersiz değerleri gizleyebilir ve yalnızca tekrar eden verileri görüntüleyebilir veya tam tersini yapabilirsiniz. Bunu yapmak için, aşağıdaki örnekte gösterildiği gibi Excel’in otomatik filtresini uygulayın: Excel’de kopyalar nasıl filtrelenir.
9 – Excel’de tekrar eden verileri gösterme ve gizleme
Tüm kopyaları bir bakışta görmek istiyorsanız, Excel’de kopyaları bulmak için ihtiyaçlarınıza daha uygun olan formüllerden birini kullanın. Ardından tablonuzu seçin, Veri sekmesine geçin ve Filtre düğmesine tıklayın. Alternatif olarak, Düzenleme grubundaki Giriş sekmesinde Sırala ve Filtrele > Filtre’ye tıklayabilirsiniz.
İpucu. Filtrelemenin otomatik olarak etkinleştirilmesi için verilerinizi tam işlevli bir Excel tablosuna dönüştürün. Tüm verileri seçin ve Ctrl + L kısayoluna basın.
Bundan sonra, Yinelenen sütununun başlığındaki oka tıklayın ve yinelenenleri göstermek için “Yinelenen satır” kutusunu işaretleyin. Filtrelemek, yani kopyaları gizlemek istiyorsanız, yalnızca Benzersiz kayıtlarını görüntülemek için “Benzersiz” seçeneğini seçin.
Ve şimdi, daha kolay analiz için gruplandırmak üzere yinelenenleri anahtar sütuna göre sıralayabilirsiniz. Bu örnekte, yinelenen satırları Sipariş numarası sütununa göre sıralayabiliriz:
10 – Tekrar eden verileri oluşumlarına göre filtreleme
Tekrar eden verilerin 2., 3. veya N. oluşumlarını göstermek istiyorsanız, daha önce tartıştığımız yinelenen örnekleri saymak için formülü kullanın:
=EĞERSAY($A$2:$A2;$A2)
Ardından tablonuza filtreleme uygulayın ve yalnızca görüntülemek istediğiniz oluşumu/oluşumları seçin. Örneğin, aşağıdaki ekran görüntüsünde olduğu gibi 2. oluşumları filtreleyebilirsiniz:
Tüm tekrar eden verileri, yani 1’den büyük oluşumları görüntülemek için, Oluşumlar sütununun (formülün bulunduğu sütun) başlığındaki filtre okuna tıklayın ve ardından Sayı Filtreleri > Büyüktür’e tıklayın.
İlk kutuda “büyüktür” seçeneğini seçin, yanındaki kutuya 1 yazın ve Tamam düğmesine tıklayın:
Benzer şekilde, 2., 3. ve sonraki tüm yinelenen oluşumları gösterebilirsiniz. Sadece “daha büyüktür” ifadesinin yanındaki kutuya gerekli sayıyı yazın.
Kopyaları vurgulayın, seçin, temizleyin, silin, kopyalayın veya taşıyın
Yukarıda gösterildiği gibi kopyaları filtreledikten sonra, bunlarla başa çıkmak için çeşitli seçenekleriniz vardır.
11 – Excel’de yinelemeler nasıl vurgulanır
Yinelenen değerleri vurgulamak için, filtrelenen yinelemeleri seçin, Giriş sekmesinde Yazı Tipi grubundaki Dolgu rengi düğmesine tıklayın ve ardından istediğiniz rengi seçin.
Filtreyi serbest bıraktığınızda renklendirilmemişler de gözükecektir.
12 – Koşullu biçimlendirme ile tekrar eden veriyi vurgulama
Excel’de yinelenen değerleri vurgulamanın bir başka yolu da yinelenen değerler için yerleşik bir koşullu biçimlendirme kuralı kullanmak ya da sayfanıza özel bir kural oluşturmaktır.
Fakat tekrar eden veriyi bulmak sıklıkla kullanılan bir özellik olduğunda excel bunu hazır şablonların içine koymuştur.
Önce vurgulamak istediğiniz tekrar eden veri içeren ürün sütununu seçin. Koşullu biçimlendirme komutlarından aşağıdaki gibi yinelenen değerleri seçin.
Yinelenen değerlere tıkladığınızda önünüze küçük bir kutucuk çıkacak.
Yinelenen veya benzersiz menülerinden birini seçin.
Vurgu biçimini de hazır şablon veya özel biçim menüsünden seçebilirsiniz.
Tamam dediğinizde koşullu biçimlendirme uygulanacaktır.
Koşullu biçimlendirmenin diğer güzel tarafı ise verileri değiştirdiğinizde tekrar eden verileri kendisi otomatik olarak tabloda tekrar vurgular.
13 – Yinelenen satırları nasıl vurgularız?
Bazen bir tablodaki tüm satırların birbirinin kopyası olup olmadığını kontrol etmeniz gerekir. İşte her hücreyi tek tek kontrol etmeden bunu yapmanın kolay bir yolu:
- Yardımcı bir sütun açın
– İlk olarak, her satırdaki tüm bilgileri tek bir hücrede birleştiren yeni bir sütun oluşturacağız. Bu, tüm satırları karşılaştırmayı kolaylaştırır.
– Her satırdaki verileri tek bir hücrede birleştirmek için Excel BİRLEŞTİR işlevini kullanacağız. Yeni sütununuzun ilk hücresine şunu yazın:
=BİRLEŞTİR(A4:C4)
Formulü aşağıya kadar sürükleyin
- Yinelenen satırları vurgulayın
– Yeni doldurduğunuz sütunu seçin. ‘Giriş’ sekmesine gidin, ‘Koşullu Biçimlendirme’ye, ardından ‘Hücreleri Vurgulama Kuralları’na tıklayın ve ‘Yinelenen Değerler’i seçin. Bu, birden fazla kez görünen veriye sahip herhangi bir hücreyi renklendirecektir.
- Yardımcı sütunu gizleyin:
– Sayfanızı düzenli tutmak için, yardımcı sütundaki birleştirilmiş verileri gizlemek isteyebilirsiniz. Seçmek için sütun başlığına tıklayın, hücreleri biçimlendir penceresini açmak için Ctrl + 1 tuşlarına basın ve ‘Özel’ altına ;;; (üç noktalı virgül) yazın. Bu, hücrelerdeki metni görünmez hale getirir.
– Daha az yer kaplaması için sütunu daha dar da yapabilirsiniz.
Artık tüm yinelenen satırlar vurgulanacak ve tablonuz görünür yardımcı veriler olmadan daha temiz görünecektir.
14 – Excel’de yinelemeler nasıl temizlenir veya kaldırılır
Excel’de kopyaları temizlemek için bunları seçin, sağ tıklayın ve ardından İçeriği Temizle’ye tıklayın (veya Giriş sekmesinde, Düzenleme grubunda Temizle düğmesi > İçeriği Temizle’ye tıklayın). Bu işlem yalnızca hücre içeriğini siler ve sonuç olarak boş hücrelere sahip olursunuz. Filtrelenmiş yinelenen hücreleri seçip Sil tuşuna basmak da aynı etkiyi yaratacaktır.
Yinelenen satırların tamamını kaldırmak için, yinelenenleri filtreleyin, fareyi satır başlıkları boyunca sürükleyerek satırları seçin, seçime sağ tıklayın ve ardından bağlam menüsünden Satırı Sil’i seçin.
15 – Kopyaları başka bir sayfaya kopyalama veya taşıma
Kopyaları kopyalamak için, kopyaları seçin, Ctrl + C tuşlarına basın, ardından başka bir sayfa açın (yeni veya mevcut bir sayfa), kopyaları kopyalamak istediğiniz aralığın sol üst hücresini seçin ve yapıştırmak için Ctrl + V tuşlarına basın.
Kopyaları başka bir sayfaya taşımak için aynı adımları uygulayın, tek fark Ctrl + C (kopyala) yerine Ctrl + X (kes) tuşlarına basmanızdır.
Kaynaklar
https://www.ablebits.com/office-addins-blog/identify-duplicates-excel/
https://www.xelplus.com/excel-find-and-highlight-duplicates/
Bu yazılar da ilgini çekebilir;
Excel Arama Kutusu Hazırlama [1 şablon içerir]
Excel'de iki sütun karşılaştırma nasıl yapılır [1 şablon içerir]
Excel'de sütun ve satır sabitleme nasıl yapılır (2 basit adım)
Excel'de bir hücre değerine göre satır renklendirme [1 şablon içerir]
2008’den beri pazarlama dalında çalışıyorum. 2014’ten beri markamuduru.com’da yazıyorum. İnanıyorum ki markalaşma adına ülkemizde inanılmaz bir potansiyel var ve markalaşmak ülkemizi fersah fersah ileri götürecek. Kendini yetiştirmiş marka müdürlerine de bu yüzden çokça ihtiyaç var. Ben de öğrendiklerimi, araştırdıklarımı, bildiklerimi burada paylaşıyorum. Daha fazla bilgi için Hakkımda sayfasını inceleyebilirsiniz.