Veriyle ustalık: Excel'de tekrarları ve tutarsızlıkları bulma

Tek bir sütundaki yinelenenleri hızlıca bulmak için aralığı seçin ve Giriş → Koşullu Biçimlendirme → Hücre Vurgulama Kuralları → Yinelenen Değerler yolunu izleyin. İki liste arasındaki eşleşmeleri bulmak için =COUNTIF(Aralık_2; Hücre_1)>0 formülünü, benzersiz kayıtları (farkı) ortaya çıkarmak için ise =COUNTIF(Aralık_2; Hücre_1)=0 koşulunu kullanın. Bu yöntemler, karmaşık yazılımlar kullanmadan verileri anında temizlemenize veya denetim yapmanıza olanak tanır.

Aşağıda, basit kontrollerden karmaşık tabloların analizine kadar veri dizileriyle çalışmanın çeşitli senaryolarına ilişkin ayrıntılı talimatlar yer almaktadır.

Tek listede yinelenenleri arama

En sık karşılaşılan görev, tek bir sütun içinde tekrarlanan değerleri bulmaktır (örneğin, e-posta adresleri veya vergi kimlik numaraları listesi).

Yöntem 1: Görsel vurgulama (Koşullu Biçimlendirme)

Küçük ve orta büyüklükteki dizilerin hızlı kontrolü için idealdir.

1. Sütunu veya veri aralığını seçin.
2. Giriş sekmesine gidin → Koşullu Biçimlendirme.
3. Hücre Vurgulama Kuralları → Yinelenen Değerler seçeneğini belirleyin.
4. İletişim kutusunda dolgu rengini seçin ve Tamam'a tıklayın.

Birden fazla kez geçen değerleri içeren tüm hücreler seçilen renkle boyanacaktır.

Yöntem 2: Formülle tekrar sayısını hesaplama

Yinelenenleri sadece görmek değil, filtrelemek veya saymak istiyorsanız.

COUNTIF işlevini kullanın:

=COUNTIF($A:$A; A2)

• Sonuç 1: değer benzersizdir.
• Sonuç >1: değer tekrar eder (sayı, toplam oluşum sayısını gösterir).

Yinelenenleri silme

Amacınız listeyi hemen temizlemekse:

1. Aralığı seçin.
2. Veri sekmesine gidin → Yinelenenleri Kaldır.
3. İşlemi onaylayın. Excel, bulunan ilk oluşumu korur ve diğerlerini siler.

Eşleşmeler için iki sütunu karşılaştırma

Genellikle A Listesindeki değerlerin B Listesinde olup olmadığını kontrol etmek gerekir (örneğin, tedarikçilerin mutabakatı).

Eşleşmeleri arama formülü

A listesinin yanındaki boş bir sütuna (örneğin, C) şu formülü girin:

=IF(COUNTIF($B:$B; A2)>0; "Bulundu"; "Yok")

Burada $B:$B arama aralığı, A2 ise kontrol edilen değerdir.

MATCH işlevi ile alternatif bir seçenek (konumu veya hatayı döndürür):

=IFERROR(MATCH(A2; $B:$B; 0); "Eşleşme yok")

Eşleşmeleri renkle vurgulama

B sütununda bulunan A sütunundaki hücreleri vurgulamak için:

1. A sütununu seçin.
2. Koşullu Biçimlendirme → Yeni Kural → Formül kullanarak...
3. Şunu girin: =COUNTIF($B:$B; A1)>0
4. Dolgu formatını ayarlayın.

Veri kümeleri arasındaki farklılıkları belirleme

Eşleşmeleri aramanın tersi bir görev: Bir listede olan ancak diğerinde olmayanı bulmak (simetrik fark).

A Listesi için benzersiz değerleri arama

Formül, B sütununda olmayan ancak A sütununda olan değerleri gösterecektir:

=IF(COUNTIF($B:$B; A2)=0; "A için benzersiz"; "")

İki aralığın tam karşılaştırması

İki sütun arasındaki tüm farklılıkları bulmanız gerekiyorsa (A'da olan ama B'de olmayan ve tam tersi), yukarıdaki formülü bitişik hücrelerde her iki sütuna da uygulayın. "Benzersiz" olarak işaretlenen değerler, aranan fark olacaktır.

Tam satır yinelenenlerini arama

Bazen yinelenen tek bir hücre değil, birkaç sütunun kombinasyonudur (örneğin, aynı Ad ve Soyad ancak farklı doğum tarihleri – bu yinelenen değildir).

Karşılaştırma anahtarı oluşturma

1. Yardımcı bir sütun ekleyin.
2. Değerleri ampersand & ile birleştirin:

   =A2 & "|" & B2 & "|" & C2

Ayraç (örneğin, |), birleştirme sırasında yanlış eşleşmeleri önlemek için gereklidir (Ivan+Ov = Ivanov). 3. İlk bölümde açıklanan yinelenen arama yöntemini (koşullu biçimlendirme veya COUNTIF) bu yeni sütuna uygulayın.

COUNTIFS kullanma

Yardımcı sütun olmadan, çoklu koşul işlevini kullanarak devam edebilirsiniz:

=COUNTIFS($A:$A; A2; $B:$B; B2; $C:$C; C2)

Sonuç 1'den büyükse, satır tamamen yinelenmiştir.

Gelişmiş araçlar: Power Query

Büyük veri hacimleri (on binlerce satır) için formüller Excel'in çalışma hızını yavaşlatabilir. Bu durumlarda Power Query eklentisini kullanın (Veri sekmesi → Veri Al).

Power Query'nin avantajları:

• Performans: Dizi formüllerinden daha hızlı çalışır.
• Otomasyon: Kaynak veriler güncellendiğinde, tüm karşılaştırma süreci tek tıklamayla tekrarlanır.
• Esneklik: SQL prensibine göre tabloların karmaşık birleştirmelerini (Merge) gerçekleştirmeye olanak tanır (Left Join, Inner Join vb.).

Power Query'de algoritma:

1. Her iki listeyi de Power Query düzenleyicisine yükleyin.
2. Sorguları Birleştir (Merge Queries) işlevini kullanın.
3. Birleştirme türünü seçin:
   • Inner Join: Sadece eşleşmeleri bırakır.
   • Left Anti: İlk tablodaki ikinci tabloda olmayan satırları bırakır (farkları arama).

Karşılaştırmada sık yapılan hatalar

Doğru formüller bile veri girişindeki özellikler nedeniyle başarısız olabilir. Sonuçlar yanlış görünüyorsa şu noktaları kontrol edin:

1. Fazladan boşluklar: En sık karşılaşılan neden. Temizlemek için =TRIM(A2) kullanın.
2. Farklı veri formatları: Sayı 105 (sayısal format) ve 105 (metin formatı, genellikle yeşil köşeli) birbirine eşit değildir. Veri → Metni Sütunlara Dönüştür → Son yoluyla sütunları aynı formata getirin.
3. Görünmez karakterler: Web'den veya 1C'den kopyalanan veriler, bölünemez boşluklar (kod 160) içerebilir. Normal boşluk değiştirme işlemi çalışmayabilir. Yazdırılamayan karakterleri kaldırmak için =CLEAN() işlevini deneyin.
4. Harf büyüklüğü: Standart işlevler (COUNTIF, VLOOKUP) harf büyüklüğünü ayırt etmez ("moskova" = "Moskova"). Harf büyüklüğü önemliyse, EXACT işlevini veya kesin karşılaştırma ile SUMPRODUCT işlevini kullanın.

SSS

Birden fazla sayfada aynı anda yinelenenler nasıl bulunur? Bunu standart araçlarla yapmak zordur. En iyi yöntem, tüm sayfalardaki verileri tek bir özet tabloya kopyalamak veya tüm tabloları tek bir sorguda birleştirmek (Append) için Power Query kullanmak ve ardından yinelenenleri genel listede aramaktır.

Yinelenenler yalnızca görünen hücrelerde (filtrelemeden sonra) vurgulanabilir mi? Standart koşullu biçimlendirme, gizli satırlar dahil olmak üzere tüm aralıkla çalışır. Yalnızca görünenlerle çalışmak için makrolar (VBA) veya görünürlüğü dikkate alan bir formülle ara sütun kullanılması gerekir (SUBTOTAL işlevi aracılığıyla).

Tarihe göre en güncel kaydı bırakarak yinelenenler nasıl silinir? "Yinelenenleri Kaldır" aracı, karşılaştığı ilk satırı bırakır. En son tarihe sahip kaydı bırakmak için:

1. Tabloyu tarih sütununa göre sıralayın (azalan).
2. Yinelenenleri kaldırma aracını çalıştırın. Tüm tekrarları silecek ve ilk satırı (şimdi en güncel olan) bırakacaktır.