Microsoft Excel'in birçok yeteneğinden biri, iki veri listesini karşılaştırma, listeler arasındaki eşleşmeleri belirleme ve hangi öğelerin yalnızca bir listede bulunduğunu belirleme yeteneğidir. Bu, mali kayıtları karşılaştırırken veya belirli bir adın bir veritabanında olup olmadığını kontrol ederken kullanışlıdır. Eşleşen veya eşleşmeyen kayıtları belirlemek ve işaretlemek için KAÇINCI işlevini kullanabilir veya EĞERSAY işleviyle koşullandırma biçimlendirmesini kullanabilirsiniz. Aşağıdaki adımlar, verilerinizi eşleştirmek için her birini nasıl kullanacağınızı anlatır.
adımlar
Yöntem 1/2: MATCH İşleviyle Kayıtları Tanımlama
Adım 1. Veri listelerini tek bir çalışma sayfasına kopyalayın
Excel, tek bir çalışma kitabında birden çok çalışma sayfasıyla veya birden çok çalışma kitabıyla çalışabilir, ancak bilgilerini tek bir çalışma sayfasına kopyalarsanız listeleri karşılaştırmayı daha kolay bulacaksınız.
Adım 2. Her liste öğesine benzersiz bir tanımlayıcı verin
İki listeniz onları tanımlamanın ortak bir yolunu paylaşmıyorsa, belirli bir listedeki bir öğenin bir öğeyle ilgili olup olmadığını görebilmesi için her bir veri listesine o öğeyi tanımlayan ek bir sütun eklemeniz gerekebilir. diğer listede. Bu tanımlayıcının doğası, eşleştirmeye çalıştığınız veri türüne bağlı olacaktır. Her sütun listesi için bir tanımlayıcıya ihtiyacınız olacak.
- Vergi kayıtları gibi belirli bir dönemle ilişkili finansal veriler için bu, bir varlığın tanımı, varlığın edinildiği tarih veya her ikisi olabilir. Bazı durumlarda, bir giriş bir kod numarasıyla tanımlanabilir; ancak, her iki liste için de aynı sistem kullanılmıyorsa, bu tanımlayıcı, yapılması gereken eşleşmelerin olmadığı veya yoksayıldığı eşleşmelerin olmadığı yerlerde eşleşmeler oluşturabilir.
- Bazı durumlarda, bir listeden öğeleri alabilir ve fiziksel varlık açıklaması ve satın alındığı yıl gibi bir tanımlayıcı oluşturmak için bunları başka bir listedeki öğelerle birleştirebilirsiniz. Böyle bir tanımlayıcı oluşturmak için ve işareti (&) kullanarak iki veya daha fazla hücreden verileri birleştirirsiniz (eklersiniz, birleştirirsiniz). F3 hücresindeki bir öğe açıklamasını G3 hücresindeki bir tarihle bir boşlukla ayırarak birleştirmek için, o satırdaki E3 gibi başka bir hücreye '=F3&" "&G3" formülünü girersiniz. Tanımlayıcıya yalnızca yılı dahil etmek istiyorsanız (bir liste tam tarihleri ve diğeri yalnızca yılları kullandığından), bunun yerine E3 hücresine '=F3&" "&YEAR(G3)' girerek YIL işlevini dahil edersiniz. (Tek tırnakları dahil etmeyin; bunlar yalnızca örneği belirtmek için oradalar.)
- Formülü oluşturduktan sonra, formülü içeren hücreyi seçip doldurma tutamacını formülü kopyalamak istediğiniz sütunun diğer hücrelerinin üzerine sürükleyerek tanımlayıcı sütununun diğer tüm hücrelerine kopyalayabilirsiniz. Fare düğmesini bıraktığınızda, sürüklediğiniz her hücre, hücre referansları aynı satırdaki uygun hücrelere ayarlanmış olarak formülle doldurulur.
Adım 3. Mümkünse verileri standartlaştırın
Zihin "Inc." ve "Incorporated" aynı anlama gelir, Excel bir kelimeyi veya diğerini yeniden biçimlendirmediğiniz sürece bunu yapmaz. Benzer şekilde, 11, 950 ABD Doları ve 11, 999,95 ABD Doları gibi değerleri eşleşecek kadar yakın olarak düşünebilirsiniz, ancak siz söylemedikçe Excel yapmaz.
- "Şirket" için "Co" ve "Incorporated" için "Inc", ek karakterleri kısaltmak için SOL dize işlevini kullanarak bazı kısaltmalarla ilgilenebilirsiniz. "Assn" gibi diğer kısaltmalar en iyisi olabilir. bir veri girişi stil kılavuzu oluşturarak ve ardından uygunsuz biçimleri aramak ve düzeltmek için bir program yazarak ele alınır.
- Bazı girişlerin ZIP+4 son ekini içerdiği ve diğerlerinin içermediği ZIP kodları gibi sayı dizeleri için, yalnızca birincil ZIP kodlarını tanımak ve eşleştirmek için SOL dize işlevini tekrar kullanabilirsiniz. Excel'in birbirine yakın ancak aynı olmayan sayısal değerleri tanımasını sağlamak için, yakın değerleri aynı sayıya yuvarlamak ve eşleştirmek için YUVARLA işlevini kullanabilirsiniz.
- Sözcükler arasına bir yerine iki boşluk yazmak gibi fazladan boşluklar, TRIM işlevi kullanılarak kaldırılabilir.
Adım 4. Karşılaştırma formülü için sütunlar oluşturun
Liste tanımlayıcıları için sütunlar oluşturmanız gerektiği gibi, karşılaştırmayı sizin için yapan formül için de sütunlar oluşturmanız gerekir. Her liste için bir sütuna ihtiyacınız olacak.
Bu sütunları "Eksik" gibi bir şeyle etiketlemek isteyeceksiniz
Adım 5. Her hücreye karşılaştırma formülünü girin
Karşılaştırma formülü için, başka bir Excel işlevi olan ISNA'nın içine yerleştirilmiş KAÇINCI işlevini kullanacaksınız.
- Formül, "=ISNA(KAÇINCI(G3, $L$3:$L$14, YANLIŞ))" biçimini alır; burada birinci listenin tanımlayıcı sütununun bir hücresi, ikinci listedeki tanımlayıcıların her biriyle karşılaştırılır. bunlardan biriyle eşleşip eşleşmediğine bakın. Eşleşmiyorsa, bir kayıt eksiktir ve o hücrede "DOĞRU" kelimesi görüntülenecektir. Eşleşirse, kayıt mevcuttur ve "YANLIŞ" kelimesi görüntülenecektir. (Formülü girerken, ekteki tırnak işaretlerini eklemeyin.)
- Formülü, hücre tanımlayıcı formülünü kopyaladığınız gibi sütunun kalan hücrelerine kopyalayabilirsiniz. Bu durumda, ikinci hücre tanımlayıcıları listesindeki ilk ve son hücreler için satır ve sütun referanslarının önüne dolar işaretleri koyarak onları mutlak referanslar haline getirdiğinden, yalnızca tanımlayıcı hücrenin hücre referansı değişir.
- Birinci listenin karşılaştırma formülünü ikinci listenin sütununun ilk hücresine kopyalayabilirsiniz. Daha sonra hücre referanslarını düzenlemeniz gerekir, böylece "G3" ikinci listenin ilk tanımlayıcı hücresinin referansıyla değiştirilir ve "$L$3:$L$14" hücrenin ilk ve son tanımlayıcı hücresiyle değiştirilir. ikinci liste. (Dolar işaretlerini ve iki nokta üst üste işaretlerini bırakın.) Daha sonra bu düzenlenmiş formülü ikinci listenin karşılaştırma satırında kalan hücrelere kopyalayabilirsiniz.
Adım 6. Gerekirse eşleşmeyen değerleri daha kolay görmek için listeleri sıralayın
Listeleriniz büyükse, eşleşmeyen tüm değerleri bir araya getirmek için bunları sıralamanız gerekebilir. Aşağıdaki alt adımlardaki talimatlar, yeniden hesaplama hatalarını önlemek için formülleri değerlere dönüştürecek ve listeleriniz büyükse, uzun bir yeniden hesaplama süresinden kaçınacaktır.
- Seçmek için farenizi bir listedeki tüm hücrelerin üzerine sürükleyin.
- Excel 2003'te Düzen menüsünden veya Excel 2007 veya 2010'da Giriş şeridinin Pano grubundan Kopyala'yı seçin.
- Excel 2003'teki Düzen menüsünden veya Excel 2007 veya 2010'ların Giriş şeridinin Pano grubundaki Yapıştır açılır düğmesinden Özel Yapıştır'ı seçin.
- Özel Yapıştır iletişim kutusundaki Farklı Yapıştır listesinden "Değerler"i seçin. İletişim kutusunu kapatmak için Tamam'a tıklayın.
- Excel 2003'te Veri menüsünden Sırala'yı veya Excel 2007 veya 2010'da Veri şeridinin Sırala ve Filtrele grubunu seçin.
- Sıralama Ölçütü iletişim kutusundaki "Veri aralığım var" listesinden "Başlık satırı"nı seçin, "Eksik mi?" (veya karşılaştırma sütunu başlığına gerçekten verdiğiniz ad) ve Tamam'ı tıklayın.
- Diğer liste için bu adımları tekrarlayın.
Adım 7. Neden eşleşmediklerini görmek için eşleşmeyen öğeleri görsel olarak karşılaştırın
Daha önce belirtildiği gibi, Excel, yaklaşık eşleşmeleri aramak üzere ayarlamadığınız sürece tam veri eşleşmelerini aramak üzere tasarlanmıştır. Eşleşmemeniz, harflerin veya rakamların yanlışlıkla yer değiştirmesi kadar basit olabilir. Ayrıca, listelenen varlıkların ilk etapta raporlanması gerekip gerekmediğini kontrol etmek gibi bağımsız doğrulama gerektiren bir şey de olabilir.
Yöntem 2/2: COUNTIF ile Koşullu Biçimlendirme
Adım 1. Veri listelerini tek bir çalışma sayfasına kopyalayın
Adım 2. Hangi listede eşleşen veya eşleşmeyen kayıtları vurgulamak istediğinize karar verin
Yalnızca bir listedeki kayıtları vurgulamak istiyorsanız, muhtemelen o listeye özgü kayıtları vurgulamak isteyeceksiniz; yani, diğer listedeki kayıtlarla eşleşmeyen kayıtlar. Her iki listedeki kayıtları da vurgulamak istiyorsanız, birbiriyle eşleşen kayıtları vurgulamak isteyeceksiniz. Bu örneğin amaçları için, ilk listenin G3 ila G14 arasındaki hücreleri ve ikinci listenin L3 ila L14 arasındaki hücreleri aldığını varsayacağız.
Adım 3. Benzersiz veya eşleşen öğeleri vurgulamak istediğiniz listedeki öğeleri seçin
Her iki listede de eşleşen öğeleri vurgulamak istiyorsanız, listeleri birer birer seçmeniz ve karşılaştırma formülünü (bir sonraki adımda açıklanan) her listeye uygulamanız gerekir.
Adım 4. Uygun karşılaştırma formülünü uygulayın
Bunu yapmak için Excel sürümünüzde Koşullu Biçimlendirme iletişim kutusuna erişmeniz gerekir. Excel 2003'te bunu Biçim menüsünden Koşullu Biçimlendirme'yi seçerek yaparsınız, Excel 2007 ve 2010'da ise Giriş şeridinin Stiller grubunda Koşullu Biçimlendirme düğmesini tıklatırsınız. Kural türünü "Formül" olarak seçin ve Kural Açıklamasını Düzenle alanına formülünüzü girin.
- İlk listeye özgü kayıtları vurgulamak istiyorsanız, formül "=EĞERSAY($L$3:$L$14, G3=0)" olur, ikinci listenin hücre aralığı mutlak değerler ve referans olarak gösterilir. göreli bir değer olarak ilk listenin ilk hücresine. (Yakın tırnakları girmeyin.)
- İkinci listeye özgü kayıtları vurgulamak istiyorsanız, formül "=EĞERSAY($G$3:$G$14, L3=0)" olur, birinci listenin hücre aralığı mutlak değerler olarak gösterilir ve referans göreli bir değer olarak ikinci listenin ilk hücresine. (Yakın tırnakları girmeyin.)
- Her listedeki diğer listede bulunan kayıtları vurgulamak istiyorsanız, biri birinci liste için diğeri ikinci liste için olmak üzere iki formüle ihtiyacınız olacaktır. İlk listenin formülü "=EĞERSAY($L$3:$L$14, G3>0)", ikinci listenin formülü ise EĞERSAY($G$3:$G$14, L3>0)" şeklindedir. daha önce belirtildiği gibi, formülünü uygulamak için ilk listeyi ve ardından formülünü uygulamak için ikinci listeyi seçersiniz.
- İşaretlenen kayıtları vurgulamak için istediğiniz biçimlendirmeyi uygulayın. İletişim kutusunu kapatmak için Tamam'a tıklayın.
Video - Bu hizmet kullanılarak YouTube ile bazı bilgiler paylaşılabilir
İpuçları
- EĞERSAY koşullu biçimlendirme yöntemiyle bir hücre başvurusu kullanmak yerine, aranacak bir değer girebilir ve bu değerin örnekleri için bir veya daha fazla listeyi işaretleyebilirsiniz.
- Karşılaştırma formlarını basitleştirmek için listeniz için "Liste1" ve "Liste2" gibi adlar oluşturabilirsiniz. Daha sonra formüller yazılırken bu liste adları yukarıdaki örneklerde kullanılan mutlak hücre aralıklarının yerini alabilir.
