Excel'de iki sütun nasıl karşılaştırılır - Excel veri karşılaştırma yöntemleri
Bu kitaptan bir bölüm: Michael Girwin. Ctrl + Üst Karakter + Enter. Excel'de dizi formüllerine hakim olma.
Bir veya daha fazla koşula dayalı numuneler.Kürek çekmek excel işlevleri karşılaştırma operatörlerini kullanın. Örneğin, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF ve AVERAGEIFS. Bu işlevler, bir veya daha fazla koşula (ölçüt) göre seçim yapar. Sorun, bu işlevlerin yalnızca toplama, sayma ve ortalama yapabilmesidir. Aramanıza maksimum değer veya standart sapma gibi koşullar empoze etmek isterseniz ne olur? Bu durumlarda, yerleşik bir işlev olmadığından, bir dizi formülü icat etmelisiniz. Bunun nedeni genellikle dizi karşılaştırma operatörünün kullanılmasıdır. Bu bölümdeki ilk örnek, bir koşul altında minimum değerin nasıl hesaplanacağını gösterir.
Bir koşulu karşılayan dizi öğelerini seçmek için IF işlevini kullanalım. İncirde. 4.1 Soldaki tabloda şehir adlarının bulunduğu bir sütun ve zamanı gösteren bir sütun vardır. Her şehir için minimum zamanı bulmak ve bu değeri doğru tablonun ilgili hücresine yerleştirmek gerekir. Seçimin koşulu şehrin adıdır. MIN işlevini kullanırsanız, sütun B için minimum değeri bulabilirsiniz. Ancak yalnızca Oakland'a özgü sayıları nasıl seçersiniz? Ve formülleri sütunun altına nasıl kopyalarsınız? Excel'de yerleşik bir MINESLI işlevi olmadığından, IF ve MIN işlevlerini birleştiren orijinal bir formül yazmanız gerekir.
Şekil: 4.1. Formülün amacı: her şehir için minimum süreyi seçin
Bir notu format veya formatta indirin
Şek. 4.2, E3 hücresindeki formülü MIN işleviyle girmeye başlamalısınız. Ama tartışamazsın 1 numara B sütunundaki tüm değerler!? Yalnızca Auckland'a özgü değerleri seçmek istiyorsunuz.
Şek. 4.3, sonraki adımda, EĞER işlevini bağımsız değişken olarak girin 1 numara MIN için. MIN içine IF koyarsınız.
İmleci argümanın girildiği yere yerleştirerek log_expression IF işlevi (Şekil 4.4), A3: A8 şehir adlarına sahip bir aralık seçersiniz ve ardından hücre referanslarını mutlak hale getirmek için F4 tuşuna basarsınız (örneğin, daha fazla ayrıntı için bkz.). Ardından karşılaştırma işlecini, eşittir işaretini yazarsınız. Son olarak, başvuruyu göreli olarak koruyarak formülün solundaki hücreyi seçeceksiniz - D3. Formüle edilmiş durum, A3: A8 aralığını görüntülerken yalnızca Aucklands'ı seçmenize izin verecektir.
Şekil: 4.4. Bağımsız değişken üzerinde dizi operatörü oluştur log_expression işlevler EĞER
Dolayısıyla, karşılaştırma operatörünü kullanarak bir dizi operatörü oluşturdunuz. Dizi işlendiği herhangi bir zamanda, dizi operatörü bir karşılaştırma operatörüdür, dolayısıyla işleminin sonucu DOĞRU ve YANLIŞ değerlerden oluşan bir dizi olur. Bunu doğrulamak için diziyi seçin (bunu yapmak için bağımsız değişkendeki araç ipucuna tıklayın log_expression) ve F9'a basın (şek. 4.5). Genellikle bir argüman kullanırsınız log_expression, DOĞRU veya YANLIŞ döndüren; burada, ortaya çıkan dizi birden çok DOĞRU ve YANLIŞ değeri döndürecektir, bu nedenle MİN işlevi yalnızca DOĞRU değerle eşleşen şehirler için minimum sayıyı seçecektir.
Şekil: 4.5. DOĞRU ve YANLIŞ değer dizisini görmek için araç ipucundaki bağımsız değişkeni tıklayın log_expression ve F9'a basın
Bir çalışma sayfasına kopyalanmış iki sipariş tablomuz var. Excel'de iki tablonun verilerini karşılaştırmak ve hangi pozisyonların ilk tabloda olduğunu, ancak ikinci tabloda olmadığını kontrol etmek gerekir. Her hücrenin değerini manuel olarak karşılaştırmanın bir anlamı yoktur.
Excel'deki eşleşmeler için iki sütunu karşılaştırın
Excel'de iki sütunun değerlerinin karşılaştırması nasıl yapılır? Bu sorunu çözmek için kullanmanızı öneririz. koşullu biçimlendirmeyalnızca bir sütunda bulunan öğeleri hızlıca vurgulamak için. Tablolarla çalışma sayfası:
İlk adım, her iki tabloya da adlar atamaktır. Bu, hangi hücre aralıklarının karşılaştırıldığını anlamayı kolaylaştırır:
- FORMÜLLER aracını seçin - Tanımlı Adlar - Ad Ata.
- Görünen pencerede, "Ad:" alanına, değeri girin - Tablo_1.
- "Aralık:" giriş alanına sol tıklayın ve aralığı seçin: A2: A15. Ve Tamam'ı tıklayın.
İkinci liste için de aynısını yapın, ancak bir ad atayın - Tablo_2. Ve sırasıyla C2: C15 aralığını belirtin.
Faydalı tavsiyeler! Ad alanı kullanılarak aralık adları daha hızlı atanabilir. Formül çubuğunun solunda yer alır. Yalnızca hücre aralıklarını seçin ve ad alanına aralık için uygun adı yazın ve Enter tuşuna basın.
Şimdi, Excel'deki iki listeyi karşılaştırmak için koşullu biçimlendirmeyi kullanalım. Aşağıdaki sonucu almamız gerekiyor:
Tablo_1'de bulunan ancak Tablo_2'de olmayan pozisyonlar yeşil renkte görüntülenecektir. Aynı zamanda, Tablo_2'de bulunan ancak Tablo_1'de bulunmayan pozisyonlar mavi renkte vurgulanacaktır.
Excel'de iki sütunun verilerini karşılaştırma ilkesi
Sütun hücrelerini biçimlendirme koşullarını tanımlamak için EĞERSAY işlevini kullandık. Bu örnekte, bu işlev, birinci bağımsız değişken (örneğin, Tablo_2) listesinde ikinci bağımsız değişkenin (örneğin, A2) değerinin kaç kez oluştuğunu kontrol eder. Çarpma sayısı \u003d 0 ise formül DOĞRU verir. Bu durumda, hücreye koşullu biçimlendirme seçeneklerinde belirtilen özel biçim atanır. İkinci bağımsız değişkendeki referans görecelidir; bu, seçilen aralığın tüm hücrelerinin sırayla kontrol edileceği anlamına gelir (örneğin, A2: A15). İkinci formül de benzer şekilde çalışır. Aynı prensip, çeşitli benzer görevlere de uygulanabilir. Örneğin, Excel'deki iki fiyatı karşılaştırmak için
Excel'de çalışırken, oldukça yaygın bir görev, farklı türdeki değer listelerini karşılaştırmaktır. Koşullu biçimlendirme ve işlevler gibi standart Excel araçları, genel olarak değer aralıklarını ve özel olarak değer sütunlarını karşılaştırmak için kullanılabilir. Ek olarak, bu tür görevleri VBA makrolarını ve bunlara dayalı olarak Excel için eklentileri kullanarak çözebilirsiniz.
Genel olarak aralıkları ve özellikle sütunları karşılaştırma ihtiyacı, Excel'de oluşturulan çeşitli raporlar, listeler ve veritabanları güncellenirken ve zaman içinde değişiklikler gerektirirken ortaya çıkar.İÇİNDE İnternet ağları Sütunlardaki değerleri karşılaştırmak için çeşitli işlevler (formüller) ve koşullu biçimlendirme kullanan pek çok örnek açıklanmıştır. Bu tür yöntemlerin herhangi bir nedenle uygun olmadığı kişiler için, hem sütunları hem de herhangi bir sayıda satır ve sütundan oluşan herhangi bir değer aralığını karşılaştırmanıza izin veren başka bir araç kullanabilirsiniz. Excel için eklentiyi kullanarak, herhangi iki aralığı karşılaştırabilir veya daha doğrusu, bir aralığın her bir öğesini diğerinin her bir öğesi ile karşılaştırabilir ve hem eşleşmeleri hem de seçtiğiniz aralarındaki farklılıkları bulabilirsiniz.
İki aralıktaki değerler için Excel karşılaştırma eklentisi
Aşağıda tartışılacak olan eklenti, herhangi bir sayıda satır ve sütundan oluşan iki aralıktaki değerleri karşılaştırmak için tasarlanmıştır. Bu eklenti, bir aralıktaki her öğeyi başka bir aralıktaki her öğeyle karşılaştırmak için bir yordam kullanır, bu aralıklardaki hangi öğelerin aynı ve hangilerinin farklı olduğunu belirler ve hücreleri kullanıcının belirlediği bir renkle doldurur.
İletişim kutusundan görebileceğiniz gibi, her şey oldukça basit, kullanıcı karşılaştırılacak iki aralığı seçiyor, neyi arayacağını, farklı değerleri veya aynı şeyi belirliyor, bir renk seçip programı başlatıyor. Eklentiyi kullanmanın nihai sonucu, seçilen aralıkların değerlerindeki farklılıkları veya çakışmaları gösteren, belirli bir renkle doldurulmuş hücrelerdir.
Excel'deki değerleri karşılaştırırken bir uyarı var. Sayılar, her zaman görsel olarak tanımlanamayan () metin olarak biçimlendirilebilir. Yani, Excel'deki bir sayı sayısal bir değer veya metin değeri olabilir ve bu iki değer birbirine eşit değildir. Çoğu zaman bu fenomen çeşitli hatalara neden olur. Bu tür hataları dışlamak için, varsayılan olarak etkin olan "Sayıları metin olarak karşılaştır" seçeneği kullanılır. Bu seçeneği kullanmak, sayısal değerleri değil, onlardan dönüştürülen metni karşılaştırmanıza olanak tanır.
Eklenti şunları sağlar:
1. Tek bir fare tıklamasıyla, makro iletişim kutusunu doğrudan Excel araç çubuğundan açın;
2. aralık # 2 içinde olmayan aralık # 1 öğelerini bulun;
3. Aralık # 1'de olmayan 2. aralıktaki öğeleri bulun;
4. Aralık # 2'deki aralık # 1'in öğelerini bulun;
5. Aralık # 1'de bulunan 2 aralığındaki öğeleri bulun;
6. İstenen değerlere sahip hücreler için dokuz dolgu renginden birini seçin;
7. "Aralıkları sınırla" seçeneğini kullanarak aralıkları hızlı bir şekilde seçerken, tüm satırları ve sütunları seçebilirsiniz, seçilen aralığı kullanılan aralığa düşürmek otomatik olarak yapılır;
8. Sayısal değerleri karşılaştırmak yerine, "Sayıları metin olarak karşılaştır" seçeneğini kullanarak metin değerlerini karşılaştırmayı kullanın;
9. Fazladan boşlukları hesaba katmadan aralığın hücrelerindeki değerleri karşılaştırın;
10. Bir aralığın hücrelerindeki değerleri büyük / küçük harfe duyarlı olmayacak şekilde karşılaştırın.
Excel için bir makro (eklenti) kullanarak iki sütun nasıl karşılaştırılır?
Sütun karşılaştırması, rastgele aralıkları karşılaştırmanın özel bir durumudur. 1 ve 2 numaralı aralıklarda, iki sütun seçin ve tam olarak sütunları seçebilir ve seçim çerçevesini fareyle aralıkların üzerine sürükleyemezsiniz (kolaylık sağlamak için, "Aralıkları sınırla" seçeneği varsayılan olarak etkindir. , tüm sütunların veya satırların seçilmesi durumunda, bu seçimi aralığı kullanarak sınırlar), farkları veya eşleşmeleri aramak için gerekli eylemi seçin, hücrelerin dolgu rengini seçin ve programı çalıştırın. Eşleşen değerlerin aranmasının sonucunu aşağıda iki sütunda görebilirsiniz.
Excel'de iki veri kümesini karşılaştırma yeteneği, büyük miktarda veriyi işleyen ve büyük tablolarla çalışan kişiler için genellikle yararlıdır. Örneğin, karşılaştırma, verilerin doğru girilmesi veya bir tabloya zamanında veri girilmesinde kullanılabilir. Aşağıdaki makale, iki sütunu Excel'deki verilerle karşılaştırmak için çeşitli teknikleri açıklar.
IF koşullu işlecini kullanma
IF koşullu operatörünü kullanma yöntemi, tüm diziyi değil, yalnızca karşılaştırma için gerekli olan parçanın iki sütunu karşılaştırmak için kullanılması bakımından farklılık gösterir. Uygulamanın adımları aşağıdadır bu method:
Karşılaştırma için her iki sütunu çalışma sayfasının A ve B sütunlarına yerleştirin.
C2 hücresine şu formülü girin \u003d EĞER (ISERROR (SEARCH (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) ve C11 hücresine genişletin. Bu formül, B sütunundaki A sütunundaki her öğeyi sırayla arar ve B sütununda bulunursa öğenin değerini döndürür.
DÜŞEYARA ikame formülünü kullanma
Formülün ilkesi önceki yönteme benzer, aradaki fark ARAMA yerine yatıyor. Bu yöntemin ayırt edici bir özelliği, HLP formülünü kullanarak iki yatay diziyi karşılaştırma yeteneğidir.
İki sütunu A ve B sütunlarındaki verilerle karşılaştırmak için (önceki yönteme benzer şekilde), aşağıdaki formülü girin \u003d DÜŞEYARA (A2; $ B $ 2: $ B $ 11; 1; 0) C2 hücresine ve hücre C11.
Bu formül, karşılaştırılan dizideki temel dizideki her öğeye bakar ve B sütununda bulunursa değerini döndürür. Aksi takdirde, Excel bir #YOK hatası verir.
VBA Makrosunu Kullanma
İki sütunu karşılaştırmak için makroların kullanılması süreci birleştirir ve veri hazırlama süresini azaltır. Hangi karşılaştırma sonucunun görüntülenmesi gerektiğine dair karar tamamen sizin hayal gücünüze ve makro kullanma becerinize bağlıdır. Resmi Microsoft web sitesinde yayınlanan teknik aşağıdadır.
1 | Alt Bul_Eşleşmeleri () |
Bu kodda, CompareRange değişkenine, karşılaştırılan diziyle bir aralık atanır. Daha sonra, seçilen aralıktaki her öğe arasında döngü yapan ve onu karşılaştırılan aralıktaki her öğe ile karşılaştıran bir döngü çalıştırır. Aynı değerlere sahip öğeler bulunursa, makro öğenin değerini C sütununa girer.
Makroyu kullanmak için çalışma sayfasına dönün, ana aralığı seçin (bizim durumumuzda bunlar A1: A11 hücreleridir), Alt + F8 tuşlarına basın. Görünen iletişim kutusunda makroyu seçin Bul_Maçlarve yürüt düğmesini tıklayın.
Makroyu çalıştırdıktan sonra sonuç aşağıdaki gibi olmalıdır:
Inquire eklentisini kullanma
Sonuç
Bu nedenle, Excel'de verileri karşılaştırmanın birkaç yolunu aradık; bu, bazı analitik sorunları çözmenize ve yinelenen (veya benzersiz) değerleri bulmanızı kolaylaştıracak.
Karşılaştırılabilir birkaç öğeyi karşılaştırırken Excel elektronik tabloları, veriler genellikle sütunlar halinde düzenlenir, böylece bu nesnelerin özelliklerini satır satır karşılaştırmaya elverişlidir. Örneğin, araba modelleri, telefonlar, deney ve kontrol grupları, bir dizi perakende mağaza zinciri vb. Çok sayıda hat ile görsel analiz güvenilir olamaz. DÜŞEYARA, İNDİS, ARAMA (DÜŞEYARA, DİZİN, KAÇINCI) işlevleri, hücrelerdeki verileri karşılaştırmak için uygundur ve genel bir resim vermez. Sütunların genel olarak ne kadar benzer olduğunu nasıl anlarsınız? Sütunlar aynı mı?
Harita Sütunları eklentisi, sütunları eşlemenizi ve büyük resmi görmenizi sağlar:
- İki veya daha fazla sütunu birbiriyle karşılaştırın
- Sütunları Referans Değerlerle Karşılaştırın
- Tam eşleşme yüzdesini hesaplayın
- Sonucu görsel bir pivot tabloda sunun
Video dili: İngilizce. Altyazılar: Rusça, İngilizce. (Dikkat: video yansıtmayabilir en son güncellemeler... Aşağıdaki talimatları kullanın.)
Excel 2016, 2013, 2010, 2007'ye "Eşleştirme Sütunları" ekleyin
İçin uygun: Microsoft Excel 2016 - 2007, masaüstü Office 365 (32 bit ve 64 bit).
Eklenti ile nasıl çalışılır:
İki veya daha fazla sütun birbiriyle nasıl karşılaştırılır ve eşleşme yüzdesi nasıl hesaplanır?
Bir ürün geliştirme örneği düşünün. Birkaç bitmiş prototipi birbiriyle karşılaştırmanız ve ne kadar benzer, farklı ve hatta muhtemelen aynı olduklarını bulmanız gerektiğini varsayalım.
- XLTools araç çubuğundaki Sütunları Eşle düğmesini\u003e Sütunları Birbirine Eşle Seç düğmesini tıklayın.
- Tamam'ı tıklayın\u003e
Tavsiye:
Lütfen seçin özet tablosu sonuç\u003e Ekspres Analiz simgesi\u003e "Renk Ölçeği" Uygula'ya tıklayın.
Sonucu okumak: Tip 1 ve Tip 3 prototipleri hemen hemen aynıdır,% 99 eşleşme oranı, satırlardaki parametrelerinin% 99'unun aynı olduğu anlamına gelir. Tip 2 ve Tip 4 en az benzerdir - parametreleri yalnızca% 30 oranında çakışır.
Sütunlar referans değerlerle nasıl karşılaştırılır ve eşleşme derecesi nasıl hesaplanır?
Bir ürün geliştirme örneği düşünün. Birkaç bitmiş prototipi belirli bir hedef standartla karşılaştırmanız ve ayrıca prototiplerin bu standartları karşılama derecesini hesaplamanız gerektiğini varsayalım.
- Karşılaştırılacak sütunları seçin.
Örneğin, prototip verileri içeren sütunlar. - XLTools araç çubuğundaki Harita Sütunları düğmesini tıklayın.
- Referans Sütun Aralığına Eşle\u003e Referans Sütunları Seç'i seçin.
Örneğin, standartlara sahip sütunlar. - Varsa, "Sütunlar başlık içeriyor" u işaretleyin.
- Eşleşme yüzdesini yüzde olarak görüntülemek için "Eşleşme yüzdesini göster" seçeneğini işaretleyin.
Aksi takdirde, sonuç 1 (tam eşleşme) veya 0 (eşleşme yok) olarak görüntülenir. - Sonucun nereye yerleştirilmesi gerektiğini belirtin: yeni bir sayfaya veya mevcut bir sayfaya.
- Tamam\u003e Bitir'e tıklayın, sonuç pivot tabloda gösterilir.
Tavsiye: Sonucu yorumlamayı kolaylaştırmak için ona koşullu biçimlendirme uygulayın:
Sonucun pivot tablosunu seçin\u003e Hızlı Analiz simgesini\u003e "Renk Ölçeği" Uygula'yı tıklayın.
Sonucu okumak: Tip 2 prototip, Standart 2 ile% 99 uyumludur, yani Dizelerdeki parametrelerinin% 99'u aynıdır. Ürün 5, Standart 3'e en yakın olan - parametrelerinin% 96'sı aynıdır. Aynı zamanda, Ürün 4, üç standarttan herhangi birini karşılamaktan uzaktır. Şimdi, prototiplerin her birinin hedef referans değerlerinden ne kadar saptığı sonucuna varabiliriz.
Eşleştirme Sütunları eklentisi hangi görevlerde yardımcı olur?
Eklenti, hücreleri satır satır tarar ve sütunlardaki aynı değerlerin yüzdesini hesaplar. XLTools Match Columns, hücrelerdeki değerlerin normal karşılaştırması için uygun değildir - kopyaları veya benzersiz değerleri bulmak için tasarlanmamıştır.
Harita Sütunları eklentisinin farklı bir amacı vardır. Ona ana görev - genel olarak veri kümelerinin (sütunların) nasıl benzer veya farklı olduğunu öğrenin. Eklenti, örneğin daha geniş bir makro düzeyine bakmanız gerektiğinde büyük miktarda verinin analizine yardımcı olur. aşağıdaki soruları cevaplayın:
- Deney gruplarının performansı ne kadar benzer?
- Deney ve kontrol gruplarının sonuçları ne kadar benzer
- Aynı kategorideki birkaç ürün ne kadar benzer / farklıdır?
- Çalışanların temel performans göstergeleri planlanan göstergelere ne kadar yakın?
- Birkaç perakende mağazasının vb. Göstergeleri ne kadar benzer?