Excel'deki eşleşmeler ve farklılıklar için iki sütunu karşılaştırın

Bu öğretici, günlük çalışmalarımızda normal bir Excel işi olan Excel'de iki sütunun nasıl karşılaştırılacağından bahsediyor. Excel'de, iki sütunu satır satır veya hücre bazında karşılaştırma, eşleşmeleri veya farklılıkları vurgulamak için iki sütunu karşılaştırma vb. Gibi Excel'de farklı şekillerde karşılaştırmak mümkündür. Burada bu makale, iki sütunu karşılaştırmanın en olası senaryolarını kapsar. tanışın ve size yardımcı olabileceğini umuyoruz.

not

Bu eğiticide, çeşitli durumlarda iki sütunu karşılaştırma yöntemlerini daha iyi açıklamak için bazı örnek veriler sağlar. Veri kümenize bağlı olarak, bazı içerikleri (referansları) değiştirmeniz veya ayarlamanız gerekebilir, ancak temel ilkeler aynı kalacaktır. Veya yalnızca yöntemlerin işe yarayıp yaramadığını doğrulamak istiyorsanız, her vakanın örneklerini doğrudan indirin.

Aşağıda, A sütunundaki adların B sütunundakilerle aynı olup olmadığını aynı satırda kontrol etmem gereken bir veri kümesi var.

Örnek dosyayı indirmek için tıklayın

1.1 Tam eşleşme için aynı satırdaki hücreleri karşılaştırın

Genel olarak, iki sütunu tam olarak eşleşmeleri için satır satır karşılaştırmak isterseniz aşağıdaki formülü kullanabilirsiniz:

Basın Keşfet tuşuna basın ve dolgu tutamacını D8 hücresine sürükleyin. Formül DOĞRU döndürürse, iki sütunun değerleri tamamen aynıdır, YANLIŞ döndürürse farklıdırlar.

1.2 Aynı satırdaki hücreleri tam olarak veya büyük / küçük harfe duyarlı olmayan eşleşme için karşılaştırın (EĞER formülünü kullanarak)

Büyük / küçük harfe duyarlı olmamak için iki sütunu satır satır karşılaştırmak veya Match, Mismatch gibi daha fazla açıklama almak istiyorsanız, IF işlevini kullanabilirsiniz.

Tam eşleşme için aynı satırdaki hücreleri karşılaştırma

Karşılaştırma sonuçlarını açıklamak için "Eşleşme" ve "Uyumsuzluk" metinlerini kullanmak isterseniz, lütfen aşağıdaki formülü kullanın:

Basın Keşfet İlk sonucu almak için tuşuna basın, ardından otomatik doldurma tutamacını D8 hücresine sürükleyin.

Büyük / küçük harfe duyarlı olmayan eşleşme için aynı satırdaki hücreleri karşılaştırma

Hücreleri büyük / küçük harfe duyarlı olmamak için karşılaştırmak istiyorsanız aşağıdaki formülü kullanabilirsiniz:

Basın Keşfet İlk sonucu almak için tuşuna basın, ardından otomatik doldurma tutamacını E8 hücresine sürükleyin.

Açıklama

Yukarıdaki formüllerde, "Eşleşiyor" ve "Uyumsuzluk" metinlerini kendi açıklamanıza göre değiştirebilirsiniz.

1.3 Aynı satırdaki hücreleri karşılaştırın ve eşleşen veya uyumsuz verileri vurgulayın (Koşullu Biçimlendirme kullanarak)

Eşleşen veya farklı değerleri vurgulamak isterseniz, Koşullu biçimlendirme özelliği size yardımcı olabilir.

1. Karşılaştırmak için kullanılan iki sütunu seçin (B2: C8, sütun başlıkları hariç) ve ardından Anasayfa > Koşullu biçimlendirme > Yeni kural.

2. patlamada Yeni Formatlama Kuralı iletişim, seçmek için tıklayın Biçimlendirilecek hücreleri belirlemek için bir formül kullanın içinde Bir Kural Türü seçin bölüm, ardından yazın = $ B2 = $ C2 metin kutusuna Bu formülün geçerli olduğu değerleri biçimlendir.

3. Şimdi tıklayın oluşturulan görüntülemek için biçim Hücreler iletişim kutusu, ardından altında Doldurmak sekmesinde, eşleşmeleri vurgulamak için ihtiyacınız olan bir rengi seçin.

Ya da diğer sekmelerde ihtiyaç duyduğunuz eşleşmeleri geride bırakmak için yazı tipi boyutunu, yazı tipi boyutunu, hücre kenarlıklarını veya sayı biçimini değiştirebilirsiniz.

4. tık OK > OK diyalogları kapatmak için, aynı satırdaki hücreler aynıysa vurgulanacaktır.

Uyumsuzluk değerlerini vurgulamak istiyorsanız, bunu şurada kullanabilirsiniz: = $ B2 <> $ C2 içine Bu formülün geçerli olduğu değerleri biçimlendir içindeki metin kutusu Biçimlendirme Kuralını Düzenle iletişim.

Daha sonra aynı satırdaki iki sütunun farklılıkları belirli bir renkle vurgulanacaktır.

1.4 İki sütunu satır satır karşılaştırın ve uyumsuz değerleri vurgulayın (VBA kullanarak)

İki sütunu VBA kodu ile satır satır karşılaştırmak istiyorsanız, bu eğitim sizi tatmin eder.

1. Karşılaştırmak için kullanılan iki sütunu içeren çalışma sayfasını etkinleştirin, Alt + F11 etkinleştirmek için anahtarlar Uygulamalar için Microsoft Visual Basic pencere.

2. Açılan iletişim kutusunda, Ekle > modül.

3. Ardından yeni modül komut dosyasında makronun altına kopyalayıp yapıştırın.

VBA: İki sütunu satır satır karşılaştırın ve farklılıkları vurgulayın

Sub ExtendOffice_HighlightColumnDifferences()
'UpdatebyKutools20201016
Dim xRg As Range

Dim xWs As Worksheet

Dim xFI As Integer

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

 

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns"

    GoTo SRg

End If

 

Set xWs = xRg.Worksheet

For xFI = 1 To xRg.Rows.Count

    If Not StrComp(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2), vbBinaryCompare) = 0 Then

        xWs.Range(xRg.Cells(xFI, 1), xRg.Cells(xFI, 2)).Interior.ColorIndex = 7 'you can change the color index as you need.

End If

Next xFI

End Sub

4. basın F5 kodu çalıştırmak için tuşuna basın, ardından iki sütunu seçmek için bir iletişim kutusu açılır.

5. tık OK. Ardından iki sütunun farklılıkları bir arka plan rengiyle vurgulanmıştır.

Açıklama

Vurgu rengini, koddaki renk indeksini değiştirerek kendi ihtiyacınıza göre değiştirebilirsiniz, Renk indeksi referansı:

Bu bölümde veri seti aşağıda gösterildiği gibi hem B sütununda hem de C sütununda bulunan tüm değerleri aynı anda bulmak veya sadece B sütunundaki değerleri bulmak istiyorsunuz.

Örnek dosyayı indirmek için tıklayın

2.1 İki sütun hücresini hücrelere göre karşılaştırın ve karşılaştırma sonuçlarını başka bir sütunda görüntüleyin (formül kullanarak)

Burada, iki sütunu karşılaştırmak ve B sütununda olan ancak C sütununda olmayan değerleri bulmak için EĞER ve EĞERSAY işleviyle birleştirilen formülü kullanabilirsiniz.

Basın Keşfet tuşuna basın ve otomatik doldurma tutamacını D8 hücresine sürükleyin.

Açıklama

1. Bu formül, iki sütunu büyük / küçük harfe duyarlı olmadan karşılaştırır.

2. "C'de Hayır" ve "C'de Evet" açıklamalarını başkalarına değiştirebilirsiniz.

3. İki sütunun tamamını karşılaştırmak istiyorsanız, $ C $ 2: $ C $ 8 sabit aralığını $ C: $ C olarak değiştirin.

2.2 Yinelenen veya benzersiz verileri karşılaştırın ve vurgulayın (Koşullu Biçimlendirme kullanarak)

The Koşullu biçimlendirme Excel'deki özellik güçlüdür, burada iki sütun hücresini hücrelere göre karşılaştırmak ve ardından ihtiyacınız olan farklılıkları veya eşleşmeleri vurgulamak için kullanabilirsiniz.

Tüm kopyaları veya benzersiz değerleri iki sütunda vurgulayın

1. Karşılaştıracağınız iki sütunu seçin ve ardından Anasayfa > Koşullu biçimlendirme > Hücreler Kurallarını Vurgulayın > Yinelenen Değerler.

2. patlamada Yinelenen Değerler iletişim kutusunda, aşağı açılır değerler listesinden ihtiyacınız olan vurgulama formatını seçin.

3. tık OK. Ardından iki sütundaki kopyalar vurgulanmıştır.

Açıklama

Eşsiz değerleri (farkları) iki sütunda vurgulamak isterseniz, Anasayfa > Koşullu biçimlendirme > Hücreler Kurallarını Vurgulayın > Yinelenen Değerler görüntülemek için Yinelenen Değerler iletişim kutusu, değiştir çift için Benzersiz soldaki açılır listede, ardından açılır listedeki değerlerden başka bir biçim seçin, tıklayın OK.

Benzersiz değerler vurgulanacaktır.

C sütununda da bulunuyorsa, B sütunundaki değerleri bulun ve vurgulayın

B sütununda da C sütununda bulunan değerleri vurgulamak isterseniz, Cek Biçimlendirme özelliği de size bir iyilik yapabilir.

1. B2: B8 sütununu seçin, tıklayın Anasayfa > Koşullu biçimlendirme > Yeni kural.

2. içinde Yeni Formatlama Kuralı diyalog seç Biçimlendirilecek hücreleri belirlemek için bir formül kullanın itibaren Bir Kural Türü seçin bölüm, ardından yazın = EĞERSAY ($ C $ 2: $ C $ 8, $ B2)> 0 metin kutusuna Bu formülün geçerli olduğu değerleri biçimlendir.

3. tıklayın oluşturulan gitmek için biçim Hücreler diyalog, altında Doldurmak sekmesinde eşleşmeleri vurgulamak için bir renk seçin.

Yazı Tipi, Sayı, Kenarlık sekmelerindeki değerleri vurgulamak için diğer biçimleri kullanabilirsiniz.

4. tık OK > OK. Ardından C sütununda da bulunan B sütunundaki değerler belirtilen renkle vurgulanmıştır.

Yalnızca B sütununda olan ancak C sütununda olmayan değerleri vurgulamak istiyorsanız, yukarıdaki adımları tekrarlayın, ancak 2. adımdaki formülü şu şekilde değiştirin: = EĞERSAY ($ C $ 2: $ C $ 8, $ B2) = 0 içinde Yeni Formatlama Kuralı iletişim.

Ardından değerleri formatlamak için başka bir renk seçin.

Açıklama

Burada Koşullu Biçimlendirme, iki sütunu büyük / küçük harfe duyarlı olmaksızın karşılaştırır.

2.3 Yinelenen veya benzersiz verileri karşılaştırın ve seçin veya vurgulayın (kullanışlı bir araç kullanarak)

Bazen, iki sütunu karşılaştırdıktan sonra, eşleşmeler veya farklılıklar üzerinde seçim, silme, kopyalama vb. Gibi başka işlemler yapabilirsiniz. Bu durumda, kullanışlı bir araç - Aynı ve Farklı Hücreleri Seçin of Kutools for Excel Bir sonraki işlemi daha iyi yapmak için doğrudan eşleşmeleri veya farkı seçebilir, ayrıca değerleri doğrudan vurgulayabilir.

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

1. tık Kutools > seçmek > Aynı ve Farklı Hücreleri Seçin.

2. Sonra Aynı ve Farklı Hücreleri Seçin iletişim kutusu, lütfen aşağıdaki işlemleri yapın:

1) içinde Değerleri bulun içinde ve Göre bölümler, karşılaştırmak istediğiniz iki sütun seçin;

2) Seçin Her sıra seçenek;

3) Seçin Aynı değerler or Farklı değerler gerek duyduğunuz kadar;

4) Değerleri vurgulamak istiyorsanız, ihtiyacınız olan rengi seçin.

3. tık Ok, bulunan değerlerin sayısını size hatırlatmak için bir iletişim kutusu açılır, OK diyaloğu kapatmak için. Ve aynı zamanda değerler seçildi, artık silebilir, kopyalayabilir veya başka işlemler yapabilirsiniz.

işaretlerseniz Arka rengi doldur ve Yazı tipi rengini doldur onay kutuları, sonuç şu şekilde gösterilir:

Açıklama

Büyük / küçük harfe duyarlı karşılaştırmak istiyorsanız, Harfe duyarlı seçeneği.

Bu araç, farklı çalışma sayfalarındaki iki sütunu karşılaştırmayı destekler.

2.4 İki sütunu karşılaştırın ve başka bir sütundaki tam kopyaları listeleyin (VBA kodunu kullanarak)

İki sütunu hücre hücre karşılaştırdıktan sonra eşleşen değerleri başka bir sütunda listelemek isterseniz, burada aşağıdaki makro kodu size yardımcı olabilir.

1. İki sütunu karşılaştırmak istediğiniz sayfayı etkinleştirin ve ardından Alt + F11 görüntüleme tuşları Uygulamalar için Microsoft Visual Basic pencere.

2. tık Ekle > modül içinde Uygulamalar için Microsoft Visual Basic pencere.

3. Ardından aşağıdaki kodu kopyalayıp yeni boş modül komut dosyasına yapıştırın.

VBA: İki sütunu karşılaştırdıktan sonra yinelenenleri sütunun yanında listeleyin

Sub ExtendOffice_FindMatches()
'UpdatebyKutools20201019
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

Dim xIntSR, xIntER, xIntSC, xIntEC As Integer

On Error Resume Next

SRg:

Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select single column"

    GoTo SsRg

End If

Set xWs = xRg.Worksheet

 

For Each xRgF1 In xRgC1

    For Each xRgF2 In xRgC2

        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value

    Next xRgF2

Next xRgF1

End Sub

4. Basın F5 kodu çalıştırmak için tuşuna basın, iki sütunu ayrı ayrı seçmeniz için birer birer açılan iki iletişim kutusu vardır.

not: Lütfen önce sol sütunu seçin, ardından ikinci iletişim kutusunda sağ sütunu seçin, aksi takdirde kopyalar ikinci sütundaki orijinal verilerin yerini alacaktır.

5. tık OK > OK, eşleşmeler otomatik olarak iki sütunun sağ sütununda listelenir.

Açıklama

VBA kodu, iki sütunu büyük / küçük harfe duyarlı olarak karşılaştırır.

2.5 İki sütunu karşılaştırın ve kopyaları vurgulayın (VBA kodunu kullanarak)

İki sütunu hücre bazında karşılaştırmak ve ardından eşleşmeleri vurgulamak istiyorsanız, aşağıdaki kodu deneyebilirsiniz.

1. İki sütunu karşılaştırmak istediğiniz sayfayı etkinleştirin ve ardından Alt + F11 görüntüleme tuşları Uygulamalar için Microsoft Visual Basic pencere.

2. tık Ekle > modül içinde Uygulamalar için Microsoft Visual Basic pencere.

3. Aşağıdaki makro kodunu kopyalayın ve yeni boşluğa yapıştırın modül komut.

VBA: İki sütunu karşılaştırın ve kopyaları vurgulayın

Sub ExtendOffice_CompareTwoRanges()
'UpdatebyKutools20201019

Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range

SRg:

Set xRgC1 = Application.InputBox("Select the column you want compare according to", "Kutools for Excel", , , , , , 8)

If xRgC1 Is Nothing Then Exit Sub

If xRgC1.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SRg

End If

SsRg:

Set xRgC2 = Application.InputBox("Select the column you want to highlight duplicates in:", "Kutools for Excel", , , , , , 8)

If xRgC2 Is Nothing Then Exit Sub

If xRgC2.Columns.Count <> 1 Then

    MsgBox "Please select a single column"

    GoTo SsRg

End If

 

    For Each xRgF1 In xRgC1

        For Each xRgF2 In xRgC2

            If xRgF1.Value = xRgF2.Value Then

               xRgF2.Interior.ColorIndex = 38 '(you can change the color index as you need)

            End If

        Next

    Next

End Sub

4. Basın F5 kodu çalıştırmak için anahtar. İlk açılan iletişim kutusunda, yinelenen değerleri karşılaştırmak istediğiniz sütunu seçin.

5. tık OK. İkinci iletişim kutusunda, yinelenen değerleri vurgulamak istediğiniz sütunu seçin.

6. tık OK. Şimdi ikinci sütundaki kopyalar, ilk sütunla karşılaştırıldıktan sonra bir arka plan rengiyle vurgulanmıştır.

Açıklama

1. Kod, sütunları büyük / küçük harfe duyarlı olarak karşılaştırır.

2. Koddaki renk indeksini değiştirerek kendi ihtiyacınıza göre vurgulama rengini değiştirebilirsiniz, Renk indeksi referansı:

Bazen, aşağıda gösterilen ekran görüntüsü gibi veri kümesi gibi aynı satırdaki ikiden fazla sütunu karşılaştırmak isteyebilirsiniz. Burada bu bölümde, birden çok sütunu karşılaştırmak için farklı yöntemler listelenmektedir.

Örnek dosyayı indirmek için tıklayın

3.1 Aynı satırdaki tüm hücrelerde tam eşleşmeleri bulun (EĞER formülünü kullanarak)

Aynı satırdaki sütunlarda tam eşleşmeleri bulmak için aşağıdaki EĞER formülü size yardımcı olabilir.

Aynı satırdaki hücreler birbiriyle eşleşirse, "Tam eşleşme" görüntülenir veya "Değil" görüntülenir.

Basın Keşfet İlk karşılaştırma sonucunu almak için tuşuna basın, ardından otomatik doldurma tutamacını E7 hücresine sürükleyin.

Açıklama

1. Formül, sütunları büyük / küçük harfe duyarlı olmadan karşılaştırır.

2. Daha fazla veya eşit sütunu karşılaştırmanız gerekirse, aşağıdaki formülü kullanabilirsiniz:

Formülde 3 sütun sayısıdır, ihtiyacınızı karşılayacak şekilde değiştirebilirsiniz.

3.2 Aynı satırdaki herhangi iki hücrede eşleşenleri bulun (EĞER formülünü kullanarak)

Bazen, aynı satırdaki herhangi iki sütunun eşleşip eşleşmediğini öğrenmek istersiniz, aşağıdaki EĞER formülünü kullanabilirsiniz.

Bu formülde, aynı satırdaki herhangi bir hücre çiftini karşılaştırmanız gerekir. "Eşleşme" eşleşen herhangi iki hücre olduğunu gösterir, eşleşen hücre yoksa, formül "Değil" gösterir, metinleri istediğiniz gibi değiştirebilirsiniz.

Basın Keşfet tuşuna basın ve dolgu tutamacını E7 hücresinin üzerine sürükleyin.

Açıklama

1. Bu formül, büyük / küçük harfe duyarlı olmayı desteklemez.

2. Karşılaştırmak için çok sayıda sütun gerekiyorsa, formüldeki herhangi bir hücre çiftini karşılaştırmak çok uzun olabilir. Bu durumda, EĞER ve EĞERSAY işlevlerini birleştiren aşağıdaki formülü kullanmayı düşünebilirsiniz.

3.3 Aynı satırdaki tüm hücrelerde tam eşleşmeleri bulun ve vurgulayın (Koşullu Biçimlendirme kullanarak)

Tüm hücrelerin birbiriyle eşleştiği satırları vurgulamak isterseniz, Koşullu biçimlendirme Excel'deki özellik.

1. Kullandığınız aralığı seçin ve ardından Anasayfa > Koşullu biçimlendirme > Yeni kural.

2. içinde Yeni Formatlama Kuralı diyalog seç Biçimlendirilecek hücreleri belirlemek için bir formül kullanın itibaren Bir Kural Türü seçin bölümünde aşağıdaki formüllerden birini kullanabilirsiniz. Bu formülün geçerli olduğu değerleri biçimlendir Metin kutusu.

Or

not: Sütun sayısı üçten fazlaysa, 5 varsayalım, formüllerin şu şekilde değiştirilmesi gerekir:

Or

3. tık oluşturulan düğmesine basarak Hücreleri Biçimlendir iletişim kutusuna gidin, ardından satırlardan daha iyi olacak bir dolgu rengini veya başka bir hücre biçimlendirmesini seçin.

4. tık OK > OK, şimdi yalnızca tüm hücrelerin eşleştiği satırlar vurgulanacak.

Açıklama

Yukarıdaki formüller, büyük / küçük harfe duyarlı olmayı desteklemez.

3.4 Birden çok sütunu karşılaştırın ve satır farklılıklarını vurgulayın

Satır farklılıklarını vurgulamak istiyorsanız, yani sütun hücrelerini tek tek karşılaştırır ve ilk sütuna göre farklı hücreleri bulursanız, Excel yerleşik özelliğini kullanabilirsiniz.Özelliğe Git.

1. Satır farklılıklarını vurgulamak istediğiniz aralığı seçin ve Anasayfa > Bul ve Seç > Özelliğe Git.

2. patlamada Özelliğe Git iletişim, kontrol et Satır farklılıkları seçeneği.

3. tık OK. Şimdi sıra farklılıkları seçildi.

4. Şimdi hücreleri seçili tutun, Anasayfa > Renkli Dolgu açılır menüden bir renk seçmek için.

Açıklama

Bu yöntem, hücreleri büyük / küçük harfe duyarlı olmadan karşılaştırır.

İki sütun olduğunu varsayarsak, B sütunu daha uzun ve C sütunu aşağıda gösterilen ekran görüntüsü gibi daha kısadır. B sütunuyla karşılaştırıldığında, C sütunundaki eksik veriler nasıl bulunur?

Örnek dosyayı indirmek için tıklayın

4.1 Eksik veri noktalarını karşılaştırın ve bulun (DÜŞEYARA veya KAÇINCI formülünü kullanarak)

Yalnızca iki sütunu karşılaştırdıktan sonra hangi verilerin eksik olduğunu belirlemek istiyorsanız, aşağıdaki formüllerden birini kullanabilirsiniz:

Or

Basın Keşfet tuşuna basın, ardından otomatik doldurma tutamacını D10 hücresinin üzerine sürükleyin. Artık veriler hem B sütununda hem de C sütunundaysa, formül YANLIŞ değerini döndürür; veriler yalnızca B sütunundaysa ancak C sütununda eksikse, formül DOĞRU verir.

Açıklama

Yukarıdaki iki formül, verileri büyük / küçük harfe duyarlı olmadan karşılaştırır.

4.2 İki sütunu karşılaştırın ve eksik verileri aşağıda listeleyin (INDEX formülünü kullanarak)

İki sütunu karşılaştırdıktan sonra eksik verileri daha kısa sütunun altında listelemek isterseniz, INDEX dizi formülü size yardımcı olabilir.

Daha kısa sütunun aşağıdaki hücresine, C7 hücresini varsayarak aşağıdaki formülü yazın:

Basın ÜstKrktr + Ctrl + Enter İlk eksik veriyi almak için tuşuna basın, ardından otomatik doldurma tutamacını # N / A hata değerini döndürene kadar aşağı sürükleyin.

Daha sonra hata değerini kaldırabilirsiniz ve tüm eksik veriler daha kısa sütunun altında listelenmiştir.

Açıklama

Bu formül, büyük / küçük harfe duyarlı olmayan hücreleri karşılaştırır.

4.3 Eksik veri noktalarını karşılaştırın ve bulun ve bunları başka bir sütunda listeleyin (kullanışlı bir araç kullanarak)

Eksik verileri başka bir sütunda listelemek veya eksik verileri daha kısa sütunun altında tamamlamak gibi iki sütunu karşılaştırdıktan sonra eksik veriler üzerinde bazı takip işlemleri yapmak istiyorsanız, kullanışlı bir araç deneyebilirsiniz.Aynı ve Farklı Hücreleri Seçin of Kutools for Excel.

1. tık Kutools > seçmek > Aynı ve Farklı Hücreleri Seçin.

2. içinde Aynı ve Farklı Hücreleri Seçin iletişim kutusu, aşağıdaki gibi yapın:

1) İçinde Değerleri bul bölümünde tam listeyi içeren daha uzun sütunu seçin.
In Göre bölümünde, bazı verileri gözden kaçıran daha kısa sütunu seçin.

2) Seçin Her sıra seçeneği.

3) Seçin Farklı değerler seçeneği.

3. tık Ok, size eksik verilerin sayısını hatırlatmak için bir iletişim kutusu açılır, OK kapatmak için. Daha sonra eksik veriler seçildi.

Şimdi basabilirsin Ctrl + C tuşlarına basarak seçilen eksik verileri kopyalayın ve bunları yapıştırın. Ctrl + V daha kısa sütunun altındaki tuşları veya ihtiyacınız olan başka bir yeni sütunu kullanın.

Açıklama

İşaretlemek Büyük / küçük harfe duyarlı değil Aynı ve Farklı Hücreleri Seç iletişim kutusundaki seçeneği, iki sütunu büyük / küçük harfe duyarlı olarak karşılaştırır.

4.4 İki sütunu karşılaştırın ve eksik verileri aşağıda listeleyin (VBA kullanarak)

İşte iki sütunun altındaki eksik verileri doldurabilecek bir makro kodu.

1. Kullandığınız sayfayı açın, Alt + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.

2. tık Ekle > modül VBA kodunun altına yerleştirilecek yeni bir modül oluşturmak için.

VBA: İki sütunu karşılaştırın ve eksik verileri doldurun

Sub ExtendOffice_PullUniques()
'UpdatebyKutools20201021

Dim xRg, xRgC1, xRgC2, xFRg1, xFRg2 As Range

Dim xIntR, xIntSR, xIntER, xIntSC, xIntEC As Integer

Dim xWs As Worksheet

On Error Resume Next

SRg:

Set xRg = Application.InputBox("Select two columns:", "Kutools for Excel", , , , , , 8)

If xRg Is Nothing Then Exit Sub

If xRg.Columns.Count <> 2 Then

    MsgBox "Please select two columns as a range"

    GoTo SRg

End If

Set xWs = xRg.Worksheet

 

xIntSC = xRg.Column

xIntEC = xRg.Columns.Count + xIntSC - 1

xIntSR = xRg.Row

xIntER = xRg.Rows.Count + xIntSR - 1

 

Set xRg = xRg.Columns

Set xRgC1 = xWs.Range(xWs.Cells(xIntSR, xIntSC), xWs.Cells(xIntER, xIntSC))

Set xRgC2 = xWs.Range(xWs.Cells(xIntSR, xIntEC), xWs.Cells(xIntER, xIntEC))

xIntR = 1

For Each xFRg In xRgC1

    If WorksheetFunction.CountIf(xRgC2, xFRg.Value) = 0 Then

        xWs.Cells(xIntER, xIntEC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

xIntR = 1

For Each xFRg In xRgC2

    If WorksheetFunction.CountIf(xRgC1, xFRg) = 0 Then

        xWs.Cells(xIntER, xIntSC).Offset(xIntR) = xFRg

        xIntR = xIntR + 1

    End If

Next

End Sub

3. Ardından F5 tuşuna bastığınızda, iki karşılaştırma sütununu seçmek için bir iletişim kutusu açılır.

4. tık OK. Şimdi eksik veriler iki sütunun altında listelenmiştir.

Açıklama

Kod, hücreleri büyük / küçük harfe duyarlı olmadan karşılaştırır.

Aşağıda gösterilen ekran görüntüsü gibi iki tarih sütunu varsa, hangi tarihin aynı satırda daha sonra olduğunu karşılaştırmak isteyebilirsiniz.

Örnek dosyayı indirmek için tıklayın

5.1 Şundan büyük veya küçükse iki sütunu karşılaştırın (formül kullanarak)

Her satırda 1. tarihin 2. tarihten sonra olup olmadığını hızlıca bulmak için basit formülü kullanabilirsiniz.

Basın Keşfet İlk karşılaştırılan sonucu almak için tuşuna basın, ardından tüm sonuçları almak için otomatik doldurma tutamacını C6 hücresine sürükleyin.

Açıklama

1. Excel'de tarihler sayı serisi olarak saklanır, aslında sayılardır. Bu nedenle, tarihleri ​​doğrudan karşılaştırmak için formülü uygularsınız.

2. Tarihin her satırda tarih 1'den önce olup olmadığını karşılaştırmak istiyorsanız, simgeyi değiştirin > için < formülde.

5.2 Biçimden büyük veya küçükse iki sütunu karşılaştırın (Koşullu Biçimlendirme kullanarak)

Tarih 1 sütunundaki hücreleri Tarih 2'den büyükse vurgulamak istiyorsanız, Koşullu biçimlendirme Excel'deki özellik.

1. B sütunundaki (Tarih1) tarihleri ​​seçin ve ardından Anasayfa > Koşullu biçimlendirme > Yeni kural.

2. içinde Yeni Formatlama Kuralı iletişim kutusunu seçin Biçimlendirilecek hücreleri belirlemek için bir formül kullanın içinde Bir Kural Türü seçin bölüm, ardından formül yazın = $ B2> $ C2 metin kutusuna Bu formülün geçerli olduğu değerleri biçimlendir.

B sütununda C sütunundakilerden daha küçük olan hücreleri vurgulamak istiyorsanız, aşağıdaki formülü kullanın = $ B2 <$ C2.

3. tık oluşturulan düğmesini açmak için biçim Hücreler iletişim kutusu, ardından ihtiyacınız olan format türünü seçin.

4. tık OK > OK. Ardından, Date1 sütunundaki Date2 sütunundakilerden daha büyük olan hücreler vurgulanmıştır.

Örneğin, iki tablo var, şimdi B sütununu ve E sütununu karşılaştırmak, sonra C sütunundan göreli fiyatları bulmak ve bunları F sütununda döndürmek istiyorsunuz.

Örnek dosyayı indirmek için tıklayın

6.1 Tam eşleşen verileri alın (formül veya kullanışlı bir araç kullanarak)

Burada bazı yararlı formüller ve bu işi çözmek için bir araç sunar.

Formül yöntemi

F2 hücresinde (döndürülen değeri yerleştirmek istediğiniz hücre), aşağıdaki formüllerden birini kullanın:

Or

Basın Keşfet anahtar ve ilk değer bulundu. Ardından otomatik doldurma tutamacını F6 hücresine sürükleyin, tüm değerler çıkarılmıştır.

Açıklama

1. Formüller büyük / küçük harfe duyarlı olmayı desteklemez.

2. Formüldeki 2 sayısı, eşleşen değerleri tablo dizisinin ikinci sütununda bulduğunuzu gösterir.

3. Formüller göreli değeri bulamazsa, # N / A hata değerini döndürür.

Kullanışlı bir araç yöntemi

Formüllerle kafanız karıştıysa, kullanışlı aracı deneyebilirsiniz - Formül Yardımcısı of Kutools for Excel, Excel'deki çoğu sorunu çözmek için birden çok formül içeren. Bununla birlikte, yalnızca aralığı seçmeniz gerekir, ancak formüllerin nasıl kullanıldığını hatırlamanız gerekmez.

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

1. F2 hücresini (dönüş değerini yerleştirmek istediğiniz hücre) seçin ve Kutools > Formül Yardımcısı > Arama ve Referans > Listede bir değer arayın.

2. içinde Formül Yardımcısı sağda diyalog Bağımsız değişken girişi bölümünde, tablo dizisi aralığını, arama değerini seçin ve dönüş değerini aramak istediğiniz sütunu belirtin.

Bahşiş: Mutlak referansı değiştirmeyi unutmayın. Lookup_value göreceli olarak veya sadece ilk değeri bulabilirsiniz.

3. tık Ok, ilk değer bulundu, ardından otomatik doldurma tutamacını F6 hücresine sürükleyin.

6.2 Kısmi eşleştirme verilerini alın (formül kullanarak)

Karşılaştırılan iki sütun arasında aşağıda gösterilen ekran görüntüsü gibi bazı küçük farklar varsa, yukarıdaki yöntemler işe yaramaz.

Bu işi çözmek için lütfen aşağıdaki formüllerden birini seçin:

Or

Basın Keşfet tuşuna basın, ardından otomatik doldurma tutamacını F5 hücresine sürükleyin, tüm değerler bulundu.

Açıklama

1. Formüller büyük / küçük harfe duyarlı olmayı desteklemez.

2. Formüldeki 2 sayısı, tablo dizisinin ikinci sütununda dönüş değerlerini bulduğunuzu gösterir.

3. Formüller göreli değeri bulamazsa, # N / A hata değerini döndürür.

4. * formülde, herhangi bir karakter veya dizeyi belirtmek için kullanılan bir joker karakter vardır.

Aşağıdaki veri seti, eşleşmeleri veya farkı karşılaştırmak ve saymak için bir örnektir.

Örnek dosyayı indirmek için tıklayın

7.1 İki sütunu karşılaştırın ve eşleşmeleri sayın (SUMPRODUCT formülünü kullanarak)

SUMPRODUCT formülü, eşleşmeleri iki sütunda hızlı bir şekilde sayabilir.

Basın Keşfet sonucu almak için anahtar.

Açıklama

Formül, hücreleri büyük / küçük harfe duyarlı olmadan sayar.

7.2 İki sütunu karşılaştırın ve eşleşmeleri veya farklılıkları sayın (kullanışlı bir araç kullanarak)

İki sütun arasındaki eşleşmeleri veya farklılıkları saymak istiyorsanız, kullanışlı araç - Aynı ve Farklı Hücreleri Seçin.

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

1. tık Kutools > seçmek > Aynı ve Farklı Hücreleri Seçin.

2. içinde Aynı ve Fark Hücrelerini Seçin iletişim kutusunda iki sütun aralığını seçin Değerleri bul ve Göre bölümleri ayrı ayrı seçin, ardından Her sıra, ve Seç Aynı değerler or Farklı değerler İhtiyacınız olan seçenek.

3. tık Ok. Kaç tane eşleşen veya farklı hücrenin seçildiğini söyleyen bir iletişim kutusu açılır.

Eşleşen hücreler

Farklı hücreler

Burada B sütunundaki verilerin bir listesi olduğunu ve D sütununda "Elma" veya "Şeker" içeren hücreleri aşağıda gösterilen ekran görüntüsü gibi saymak istediğinizi varsayalım:

Örnek dosyayı indirmek için tıklayın

Hücrenin bir veya daha fazla değer içerip içermediğini saymak için, bu sorunu çözmek üzere joker karakter içeren bir formül kullanabilirsiniz.

Basın ÜstKrktr + Ctrl + Enter tuşuna basarak ilk denetimi yapın, ardından otomatik doldurma tutamacını F8 hücresine sürükleyin.

Şimdi, ilgili hücre D sütununda bir veya daha fazla değer içeriyorsa, sonuç 0'dan büyük sayıyı görüntüler, D sütununda herhangi bir değer içermiyorsa 0 döndürür.

D sütunundaki değerleri içeren toplam hücre sayısını saymak istiyorsanız, F8 hücresinin altındaki formülü kullanın:

Açıklama

1. Ayrıca, hücre başka bir sütundaki değerler içeriyorsa saymak için formülü kullanabilirsiniz.

Bu formülün sadece basılması gerekiyor Keşfet tuşuna basın ve ardından otomatik doldurma tutamacını sürükleyin.

2. Formüllerde *, herhangi bir karakter veya dizeyi belirten joker karakterdir.

Aşağıdaki ekran görüntüsü gibi iki sütunu karşılaştırdıktan sonra farklılıkları veya eşleşmeleri silmek isterseniz:

Örnek dosyayı indirmek için tıklayın

İlk olarak farkı veya eşleşmeleri bulmak için formülü kullanabilirsiniz:

Basın Keşfet tuşuna basın ve otomatik doldurma tutamacını D8 hücresine sürükleyin.

Ardından, filtre özelliği Excel farklılıkları veya eşleşmeleri filtrelemek için.

Formül sütununu seçin ve ardından Veri > filtre.

Ardından C sütununda filtre düğmesi belirir, açılır menüyü genişletmek için filtre düğmesine tıklayın, Fark or Maç gerek duyduğunuz kadar. Sonra tıklayın OK bitirmek için.

Artık yalnızca farklılıklar veya eşleşmeler filtrelendi. Onları seçebilir ve Sil onları kaldırmak için anahtar.

Şimdi tıklayın Veri > filtre filtreyi temizlemek için tekrar.

Artık ihtiyacınız yoksa formül sütununu kaldırın.

Biri orijinal fiyatları, diğeri satış fiyatlarını içeren iki sütun vardır. Şimdi bu bölüm, bu iki sütunu karşılaştırmak ve ardından iki sütun arasındaki yüzde değişimini hesaplamak için bir formül sunar.

Örnek dosyayı indirmek için tıklayın

Aynı satırdaki iki fiyat arasındaki yüzde değişimini bulmak için aşağıdaki formülü kullanabilirsiniz.

Basın Keşfet tuşuna basın, ardından otomatik doldurma tutamacını D7 hücresine sürükleyin.

Ardından formül sonucunu yüzde olarak biçimlendirin. Sonuçları seçin, tıklayın Anasayfa sekme ve git Numara tıklanacak grup Yüzde Stili.

Formül sonuçları yüzde olarak biçimlendirilmiştir.

Artık yukarıdaki yöntemleri okuduktan sonra iki sütunu nasıl karşılaştıracağınızı biliyorsunuz. Bununla birlikte, bazı durumlarda, iki aralığı karşılaştırmak isteyebilirsiniz (birden çok sütunlu iki seri) Yukarıdaki yöntemleri (formüller veya koşullu biçimlendirme) sütun sütun karşılaştırmak için kullanabilirsiniz, ancak burada kullanışlı bir araç sunulmuştur - Kutools for Excel bu işi farklı durumlarda formül içermeyen hızlı bir şekilde çözebilir.

Örnek dosyayı indirmek için tıklayın

11.1 İki aralığı hücreye göre karşılaştırın

İşte hücreler tarafından karşılaştırılması gereken iki aralık, Aynı ve Farklı Hücreleri Seçin Kutools for Excel'in kullanımı.

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

1. tık Kutools > seçmek > Aynı ve Farklı Hücreleri Seçin.

2. patlamada Aynı ve Farklı Hücreleri Seçin iletişim kutusu, aşağıdaki gibi yapın:

1) içinde Değerleri bul bölümünde, iki aralığı karşılaştırdıktan sonra eşleşmeleri veya farklılıkları bulmak istediğiniz aralığı seçin.

2) içinde Göre bölümünde, karşılaştırılan aralık için kullanılan diğer aralığı seçin.

3) İçinde Dayanarak bölümü, seçim Tek hücre.

4) Sonra bulmak bölümünde, seçmek veya vurgulamak istediğiniz hücre türünü seçin.

5) içinde Sonuçların işlenmesi bölümünde, hücreleri doldurma arka plan rengiyle veya yazı tipi rengiyle vurgulayabilirsiniz, vurgulamanız gerekmiyorsa, onay kutularını işaretlemeyin.

3. tık Ok. Bir iletişim kutusu açılır ve kaç hücre / satır seçildiğini hatırlatır, OK kapatmak için. Şimdi diğer aralıktakilerden farklı olan hücreler seçilmiş ve vurgulanmıştır.

Aynı değerleri vurgulamak

Açıklama

İki aralığı satıra göre karşılaştırmak istiyorsanız, ayrıca Aynı ve Farklı Hücreleri Seçin özelliği, ancak bu durumda, Her sıra seçeneği.

11.2 Veriler aynı sıradaysa iki aralığı karşılaştırın

İki aralığı satıra göre karşılaştırmak isterseniz, Hücreleri Karşılaştır özelliği of Kutools for Excel size yardımcı olabilir.

Sonra ücretsiz kurulum Kutools for Excel, lütfen aşağıdaki işlemleri yapın:

F2: H7 aralığının bir model olduğunu varsayarsak, şimdi B2: D7 aralığındaki verilerin F2: H7 aralığına göre doğru sırada olup olmadığını bulmak istiyorsunuz.

1. tık Kutools > Hücreleri Karşılaştır.

2. içinde Hücreleri Karşılaştır iletişim kutusu, aşağıdaki gibi ayarlayın:

1) İki aralığı seçin Değerleri bul ve Göre kutuları ayrı ayrı.

2) Vurgulamak istediğiniz hücre türünü seçin. bulmak Bölüm.

3) Menüdeki vurgulama türünü seçin. Sonuçların işlenmesi Bölüm.

3. tık Ok. Bir iletişim kutusu açılır ve kaç hücrenin seçildiğini hatırlatır, OK kapatmak için. Şimdi diğer aralıktakilerden farklı olan hücreler seçilmiş ve vurgulanmıştır.

En İyi Ofis Üretkenliği Araçları

Kutools for Excel Sorunlarınızın Çoğunu Çözer ve Verimliliğinizi% 80 Artırır

• Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
• Hücreleri / Satırları / Sütunları Birleştirme ve Verilerin Saklanması; Bölünmüş Hücre İçeriği; Yinelenen Satırları ve Toplam / Ortalamayı Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
• Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
• Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
• Sık Kullanılan ve Hızlı Eklenen Formüller, Aralıklar, Grafikler ve Resimler; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
• Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
• Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
• Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
• Pivot Tablo Gruplaması hafta numarası, haftanın günü ve daha fazlası ... Kilidi Açılmış, Kilitli Hücreleri Göster farklı renklerle; Formülü / Adı Olan Hücreleri Vurgulayın...

Devamı ... Tam Özellikler 30 Günlük Ücretsiz Deneme ... Satın alma... 

---

Office Tab - Office'e sekmeli arabirim getirir ve işinizi çok daha kolay hale getirir

• Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
• Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
• Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!

Devamı ... Tam Özellikler 30 Günlük Ücretsiz Deneme ... Satın alma...