Excel'de İki Listeyi Karşılaştırma: Aynı ve Farklı Olanları Bulma

İki liste karşılaştırılırken önce ne sorulmalı?

İlk soru, karşılaştırmanın satır satır mı yoksa liste genelinde mi yapılacağıdır. Satır satır karşılaştırmada A2 ile B2, A3 ile B3 eşleşir. Bu durum için aşağıda ayrı bir bölüm var.

Ocak ve şubat ayında sipariş veren müşterileri karşılaştırmak ise farklı bir iştir. Bir isim ilk listenin 2. satırında, ikinci listenin 5. satırında olabilir. Sıra tutmaz, yalnız varlık önemlidir.

Örneklerde A sütununda ocak, B sütununda şubat müşterileri var. İki listede de altışar firma bulunuyor. Üçü ortak.

EĞERSAY ile ortak kayıtları işaretleme

Her ismin öbür listede geçip geçmediğini bulmanın en kısa yolu EĞERSAY'dır. Fonksiyon, A2'deki ismin B sütununda kaç kez geçtiğini sayar. Sonuç sıfırdan büyükse isim iki listede de vardır.

=EĞER(EĞERSAY($B$2:$B$7;A2)>0;"Var";"Yok")

=IF(COUNTIF($B$2:$B$7,A2)>0,"Var","Yok")

Formülü C2'ye yazıp aşağı doğru çektiğinizde her satırda "Var" ya da "Yok" görürsünüz. B aralığını $B$2:$B$7 biçiminde mutlak yazmak burada şarttır. Dolar işareti olmazsa aralık her satırda bir aşağı kayar ve son satırlarda yanlış "Yok" sonuçları çıkar.

Aynı kontrolü KAÇINCI ile de yapabilirsiniz: =ESAYIYSA(KAÇINCI(A2;$B$2:$B$7;0)) isim bulunursa DOĞRU döndürür. DÜŞEYARA alışkanlığınız varsa =EĞERHATA(DÜŞEYARA(A2;$B$2:$B$7;1;YANLIŞ);"Yok") de çalışır, ama EĞERSAY'lı formül okunması en kolay olanıdır.

Kaç kayıt iki listede de var?

Ortak kayıt sayısını tek hücrede görmek için yardımcı sütun açmanıza gerek yoktur. TOPLA.ÇARPIM, EĞERSAY'ın her isim için verdiği sonuçları tek seferde değerlendirir. Başındaki iki eksi işareti DOĞRU ve YANLIŞ değerlerini 1 ve 0'a çevirir.

=TOPLA.ÇARPIM(--(EĞERSAY(B2:B7;A2:A7)>0))

=SUMPRODUCT(--(COUNTIF(B2:B7,A2:A7)>0))

Bu sayı, iki dönem arasında kaç müşterinin tekrar sipariş verdiğini raporlamak için kullanışlıdır. Ocakta 6 müşteriden 3'ü şubatta da geldiyse tekrar oranı yüzde 50'dir. Hepsi bu.

Oranı formülle almak isterseniz aynı ifadeyi ocak listesinin dolu hücre sayısına bölersiniz: =TOPLA.ÇARPIM(--(EĞERSAY(B2:B7;A2:A7)>0))/BAĞ_DEĞ_DOLU_SAY(A2:A7). Hücreyi yüzde biçimine aldığınızda %50 görünür. Listeler her ay uzuyorsa aralıkları baştan geniş tutun, ama boş hücreleri bölene katmamak için BAĞ_DEĞ_DOLU_SAY kullanmaya devam edin.

Yalnız bir listede olanları FİLTRE ile çıkarma

Farklı olanları ayrı bir liste hâlinde görmek istiyorsanız Microsoft 365 ve Excel 2021'deki FİLTRE fonksiyonu işinizi görür. Koşul olarak EĞERSAY sonucunun sıfır olmasını verirsiniz. Sonuç, formülü yazdığınız hücreden aşağı doğru kendiliğinden yayılır.

=FİLTRE(A2:A7;EĞERSAY(B2:B7;A2:A7)=0)

=FILTER(A2:A7,COUNTIF(B2:B7,A2:A7)=0)

Şubatta olup ocakta olmayan yeni müşterileri bulmak için sütunları yer değiştirmeniz yeter: =FİLTRE(B2:B7;EĞERSAY(A2:A7;B2:B7)=0). Altındaki hücreler doluysa formül #YAYILMA! hatası verir; sonuç alanını boş bırakın.

Satır satır karşılaştırma: aynı satırdaki değerler

Sipariş ile fatura tutarlarını ya da sayım ile sistem stokunu karşılaştırırken değerler aynı satırdadır. Bu durumda en kısa formül =A2=B2 olur ve DOĞRU ya da YANLIŞ döndürür. Daha okunur bir çıktı için =EĞER(A2=B2;"Aynı";"Farklı") yazabilirsiniz.

Eşittir işareti büyük ve küçük harfi ayırt etmez. "ABC-10" ile "abc-10" kodlarının farklı sayılması gerekiyorsa ÖZDEŞ kullanılır. =ÖZDEŞ(A2;B2) yalnız harfler birebir aynıysa DOĞRU döndürür.

Sütunlar sayı içeriyorsa farkın kendisi de gerekir. Sistemde 120, sayımda 114 adet görünen bir ürün için =B2-A2 sonucu -6'dır. Yüzde fark için =(B2-A2)/A2 yazıp hücreyi yüzde biçimine alırsınız; sonuç %-5 olur.

Formülsüz yol: yinelenenleri renklendirme

Yalnızca göz atmak istiyorsanız formül yazmadan da karşılaştırma yapılabilir. İki listeyi birlikte seçin, Giriş sekmesinde Koşullu Biçimlendirme, Hücre Kurallarını Vurgula ve Yinelenen Değerler adımlarını izleyin. İki listede de geçen isimler renklenir.

Bu yöntemin bir sınırı vardır: aynı listede iki kez yazılmış bir isim de renklenir. Liste içi tekrarlar varsa EĞERSAY'lı sütun daha güvenilir sonuç verir.

Eşleşmeyi bozan görünmez farklar

İki liste aynı görünüp eşleşme çıkmıyorsa sorun çoğu zaman verinin kendisindedir. İsmin sonunda kalan bir boşluk, "Akın Gıda " ile "Akın Gıda" değerlerini farklı kılar. KIRP fonksiyonuyla temizlenmiş bir yardımcı sütun bu sorunu çözer.

Büyük ve küçük harf ise EĞERSAY ve KAÇINCI için fark etmez. Harf duyarlı karşılaştırma gerekirse =ÖZDEŞ(A2;B2) kullanılır. Sayı gibi görünen ama metin olarak kaydedilmiş kodlar da eşleşmeyi bozar.

Sıradaki adım

Kendi dosyanızda önce iki listedeki boşlukları KIRP ile temizleyin, sonra EĞERSAY'lı sütunu ekleyin. "Yok" çıkan satırlara filtre uygulayarak eksik kayıtları tek bakışta görebilirsiniz.