İki listeyi karşılaştırıp eşleşen ve eşleşmeyen kayıtları bulmak

Liste karşılaştırması hangi işlerde gerekir?

İş hayatında iki listeyi karşılaştırmak çok sık gereken bir iştir. Muhasebe, banka hareketleri ile defter kayıtlarını eşleştirir. İnsan kaynakları, aktif çalışan listesi ile sistem kullanıcı listesini karşılaştırır. Satın alma, sipariş edilen kalemler ile teslim alınan kalemleri kontrol eder. Pazarlama, mevcut müşteri listesi ile yeni kampanya listesinde örtüşenleri bulur.

Her durumda soru benzerdir: A listesinde olup B listesinde olmayanlar hangileri, B'de olup A'da olmayanlar hangileri, ikisinde de bulunanlar hangileri? Doğru yöntem, listelerin büyüklüğüne, eşleştirme anahtarının niteliğine ve sonucun ne için kullanılacağına göre seçilir.

Önce hazırlık gerekir: karşılaştırma anahtarı belirlenir, örneğin sicil numarası, fatura numarası ya da e-posta adresi. Anahtar sütunda boşluk, büyük küçük harf farkı ya da metin-sayı uyuşmazlığı varsa eşleşmeler kaçırılır.

Görsel yöntem: koşullu biçimlendirme ile görsel karşılaştırma

En hızlı yöntem, bir listedeki kayıtların diğerinde bulunup bulunmadığını renkle göstermektir. Koşullu biçimlendirmede formüle dayalı bir kural tanımlanır: A listesindeki her hücre için B listesinde kaç kez geçtiği sayılır ve sonuç sıfırsa hücre boyanır.

Bu yaklaşım küçük ve orta listeler için pratiktir. Sonucu yalnızca göze gösterir; ayrı bir sütun üretmediği için eşleşmeyenleri başka yere kopyalamak ya da raporlamak istiyorsanız yetersiz kalır.

Görsel yöntemde dikkat edilecek nokta, kuralın hücre başvurularını doğru sabitlemesidir. Karşılaştırılan aralık mutlak, kontrol edilen hücre göreli başvuruyla yazılmazsa, kural aşağı kaydıkça aralık da kayar ve yanlış sonuçlar çıkar. Bu, bu yöntemde en sık yapılan hatadır.

Küçük bir örnek: bir eğitim kurumunun katılımcı listesi ile sertifika basım listesi karşılaştırılıyor olsun. Katılımcı listesinin yanına eklenen kontrol sütunu, sertifika listesinde karşılığı bulunmayan kayıtları sıfır olarak gösterir. Bu kayıtlar süzülür ve sorumluya gönderilir; eksik sertifikalar basımdan önce yakalanır.

Sayım yöntemi: EĞERSAY ile yardımcı sütun

Kalıcı ve filtrelenebilir sonuç istiyorsanız, A listesinin yanına yardımcı bir sütun eklenir. Bu sütun, her kaydın B listesinde kaç kez geçtiğini EĞERSAY işleviyle sayar. Sonuç sıfırsa kayıt B'de yoktur; sıfırdan büyükse vardır, birden büyükse B listesinde yineleniyor demektir.

Bu sütun filtrelenerek yalnızca eşleşmeyen kayıtlar ayrı bir sayfaya kopyalanabilir. Sonuç sayısal olduğu için çıktıyı özetlemek de kolaydır: kaç kayıt eşleşti, kaç kayıt eşleşmedi, kaç kayıt yinelendi.

Aynı mantık ters yönde de uygulanır: B listesinin yanına, her kaydın A'da geçip geçmediğini sayan bir sütun eklenir. Böylece her yöndeki eşleşmeyenler elde edilir. Yinelenen kayıtlar karşılaştırma sonucunu çarpıtabileceği için, önce her listedeki yinelenenleri temizlemek ya da ayrıca raporlamak sağlıklıdır.

Arama yöntemi: DÜŞEYARA ve XLOOKUP ile bilgi getirerek karşılaştırma

Yalnızca var-yok bilgisi değil, eşleşen kaydın diğer bilgilerini de görmek istiyorsanız arama işlevleri kullanılır. DÜŞEYARA ya da daha yeni XLOOKUP, A listesindeki anahtara karşılık B listesinden bir değer getirir. Eşleşme yoksa #YOK hatası döner; bu hata, eşleşmeyen kaydın işaretidir.

Örneğin banka hareket listesindeki her işlem için defterdeki tutar getirilir ve tutarlar yan yana konur. Tutar farkı olan satırlar filtrelenir. Böylece hem hiç eşleşmeyen kayıtlar hem de eşleşip tutarı farklı olan kayıtlar tek tabloda görülür.

XLOOKUP, eşleşme bulunamadığında gösterilecek değeri doğrudan tanımlamaya izin verdiği için #YOK hatalarını ayrıca yakalamak gerekmez. DÜŞEYARA ise anahtarın aralığın ilk sütununda olmasını ister; bu kısıt, sütun sırası uygun olmayan listelerde zorluk çıkarır. Arama işlevleri tam eşleşme modunda kullanılmalıdır; yaklaşık eşleşme, karşılaştırmada sessizce yanlış sonuç verir.

Mutabakat işinde tutar karşılaştırması da yapılıyorsa, fark sütunu eklenir: A tutarı ile B tutarı arasındaki fark sıfır değilse satır incelemeye alınır. Küçük yuvarlama farkları için kabul edilebilir bir tolerans belirlemek, gereksiz uyarıları azaltır.

Sık hatalar ve veri hazırlığı

Karşılaştırma sonuçlarının güvenilir olması için şu noktalara dikkat edilir:

• Boşluklar: Sonunda ya da başında görünmeyen boşluk bulunan kayıtlar eşleşmez. Karşılaştırmadan önce boşlukları temizleyen bir yardımcı sütun kurulabilir.
• Metin ve sayı farkı: Biri listede sayı, diğerinde metin olarak saklanan numaralar eşleşmez. Biçimler eşitlenmelidir.
• Büyük küçük harf ve Türkçe karakter: İ ve ı farkı, farklı kaynaktan gelen adlarda eşleşmeyi bozabilir.
• Yinelenen anahtarlar: Aynı anahtarın birden çok kez geçmesi, hangi kaydın eşleştiğini belirsizleştirir.
• Yaklaşık eşleşme: Arama işlevlerinde yanlış mod seçilmesi, var olmayan eşleşmeyi bulmuş gibi gösterir.

Örneğin bir dağıtım şirketinde araç plakaları farklı sistemlerde farklı yazılıyor olabilir: biri boşluklu, diğeri bitişik. Doğrudan karşılaştırma bu kayıtları eşleşmemiş gösterir. Önce plakaları tek biçime getirmek sonucu düzeltir.

Başlangıçtan ileri düzeye öğrenme yolu

Başlangıç aşamasında küçük listeler üzerinde koşullu biçimlendirme ve EĞERSAY ile var-yok kontrolü yapılır. Anahtar seçimi ve veri temizliği alışkanlığı bu aşamada kazanılır.

Orta aşamada DÜŞEYARA ve XLOOKUP ile bilgi getirme, tutar farkı kontrolü ve eşleşmeyenleri ayrı çıktıya ayırma öğrenilir. Dinamik dizi işlevleriyle (FILTER, UNIQUE) eşleşmeyen kayıtları otomatik listelemek de bu aşamada mümkündür.

İleri aşamada Power Query ile tabloların birleştirilmesi (merge) kullanılır. Birleştirme türü seçilerek yalnızca soldaki ya da yalnızca sağdaki kayıtlar bulunabilir; kaynak güncellendiğinde sorgu yenilenir. Büyük listelerde ve tekrarlayan aylık mutabakatlarda bu yöntem en sağlıklısıdır.

Bu beceriyi Mindset programlarında nerede çalışırsınız?

Mindset kataloğundaki Excel Orta Düzey programı, İleri Arama ve Basit Referans Formülleri, Koşullu Fonksiyonlar ve Çok Katmanlı Hesaplamalar ile Veri Doğrulama ve Koşullu Biçimlendirme başlıklarını içerir; liste karşılaştırmasının dayandığı araçlar bunlardır. Muhasebe, satın alma ve operasyon ekipleri için uygundur.

Microsoft Excel ile Veri Analizi Eğitimi - İleri Excel (Uygulamalı), Veri Analizi ve İleri Formüller modülünü kapsar. Mutabakat ve kayıt eşleştirme işi yoğun olan kurumsal ekipler için ilgili olabilir.