Excel'de iki sütun nasıl karşılaştırılır ve kopyalar nasıl kaldırılır (vurgulama, renklendirme, taşıma)

Bu makaleyi okumanız yaklaşık 10 dakikanızı alacaktır. Sonraki 5 dakika içinde, Excel'deki iki sütunu kolayca karşılaştırabilir ve içlerinde kopya olup olmadığını öğrenebilir, silebilir veya renkli olarak vurgulayabilirsiniz. Yani, zaman geldi!

Excel, büyük miktarda veri oluşturmak ve işlemek için çok güçlü ve gerçekten harika bir uygulamadır. Veri içeren birkaç çalışma kitabınız (veya yalnızca bir büyük tablonuz) varsa, muhtemelen 2 sütunu karşılaştırmak, yinelenen değerler bulmak ve sonra bunlarla bir şeyler yapmak istersiniz, örneğin, içeriği silmek, vurgulamak veya temizlemek . Sütunlar aynı tabloda olabilir, bitişik veya bitişik olmayabilir, 2 farklı sayfada veya hatta farklı kitaplarda olabilir.

İnsanların adlarını içeren 2 sütunumuz olduğunu hayal edin – sütun başına 5 isim A ve bir sütunda 3 isim B. Bu iki sütundaki adları karşılaştırmanız ve kopyaları bulmanız gerekir. Anladığınız gibi, bunlar yalnızca örnek alınan hayali verilerdir. Gerçek tablolarda binlerce hatta on binlerce kayıtla uğraşıyoruz.

Seçenek A: her iki sütun da aynı sayfada. Örneğin, bir sütun A ve sütun B.

Seçenek B: Sütunlar farklı sayfalardadır. Örneğin, bir sütun A sayfada Sheet2 ve sütun A sayfada Sheet3.

Excel 2013, 2010 ve 2007 yerleşik bir araca sahiptir Yinelemeleri Kaldır (Yinelenenleri Kaldır) ancak 2 sütundaki verileri karşılaştıramadığı için bu durumda güçsüzdür. Ayrıca, yalnızca kopyaları kaldırabilir. Renkleri vurgulama veya değiştirme gibi başka seçenekler yoktur. Ve nokta!

Daha sonra, Excel'de iki sütunu karşılaştırmanın olası yollarını göstereceğim, bu da yinelenen kayıtları bulmanızı ve kaldırmanızı sağlayacak.

• 2 sütunu karşılaştırın ve formülleri kullanarak kopyaları arayın
  
  • A Seçeneği: her iki sütun da aynı sayfada
  • B Seçeneği: Sütunlar farklı sayfalarda veya farklı çalışma kitaplarında
• Bulunan kopyaların işlenmesi
  
  • A sütununda yalnızca yinelenen satırları göster
  • Rengi değiştirin veya bulunan kopyaları vurgulayın
  • İlk sütundan kopyaları kaldır

Excel'deki 2 sütunu karşılaştırın ve formülleri kullanarak yinelenen girişleri bulun

A Seçeneği: her iki sütun da aynı sayfada

1. İlk boş hücrede (örneğimizde bu C1 hücresi), aşağıdaki formülü yazıyoruz:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   formülümüzde A1 bu, karşılaştıracağımız ilk sütunun ilk hücresi. $B$1 и $B$10000 bunlar, karşılaştırmayı yapacağımız ikinci sütunun ilk ve son hücrelerinin adresleridir. Mutlak referanslara dikkat edin – sütun harfleri ve satır numaralarından önce bir dolar işareti ($) gelir. Formülleri kopyalarken hücre adreslerinin aynı kalması için mutlak başvurular kullanıyorum.

   Bir sütunda kopyaları bulmak istiyorsanız B, referansları formül şöyle görünecek şekilde değiştirin:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Yerine "Bir tek"Ve"çift» Kendi etiketlerinizi yazabilirsiniz, örneğin, «Bulunamadı"Ve"Bulunan", ya da sadece "çift' ve ikinci değer yerine bir boşluk karakteri girin. İkinci durumda, hiçbir kopyası bulunmayan hücreler boş kalacaktır ve verilerin bu temsilinin daha sonraki analizler için en uygun olduğuna inanıyorum.

2. Şimdi formülümüzü sütundaki tüm hücrelere kopyalayalım C, sütundaki verileri içeren en alt satıra kadar A. Bunu yapmak için fare işaretçisini hücrenin sağ alt köşesine getirin C1, işaretçi aşağıdaki resimde gösterildiği gibi siyah bir artı işareti şeklini alacaktır:Sol fare düğmesini tıklayın ve basılı tutun ve formülü eklemek istediğiniz tüm hücreleri vurgulayarak çerçevenin kenarlığını aşağı doğru sürükleyin. Gerekli tüm hücreler seçildiğinde, fare düğmesini bırakın:

   

Bahşiş: Büyük tablolarda, klavye kısayollarını kullanırsanız formülü kopyalamak daha hızlı olacaktır. Bir hücreyi vurgula C1 ve basın Ctrl + C (formülü panoya kopyalamak için), ardından Ctrl + Üst Karakter + End (C sütunundaki tüm boş olmayan hücreleri seçmek için) ve son olarak Ctrl + V (formülü seçili tüm hücrelere eklemek için).

3. Harika, şimdi tüm yinelenen değerler “ olarak işaretlendiçift":

B Seçeneği: iki sütun farklı sayfalarda (farklı çalışma kitaplarında)

1. Çalışma sayfasındaki ilk boş sütunun ilk hücresinde Sheet2 (bizim durumumuzda bu sütun B'dir) aşağıdaki formülü girin:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   İşte Sheet3 2. sütunun bulunduğu sayfanın adıdır ve $ A $ 1: $ A $ 10000 bu 1. sütunda 2'den sonuncuya kadar olan hücre adresleridir.

2. Formülü bir sütundaki tüm hücrelere kopyalayın B (A seçeneği ile aynı).
3. Bu sonucu elde ederiz:

Bulunan kopyaların işlenmesi

Harika, ilk sütunda, ikinci sütunda da bulunan girişler bulduk. Şimdi onlarla bir şeyler yapmamız gerekiyor. Bir tablodaki tüm mükerrer kayıtları manuel olarak gözden geçirmek oldukça verimsizdir ve çok fazla zaman alır. Daha iyi yollar var.

A sütununda yalnızca yinelenen satırları göster

Sütunlarınızın başlıkları yoksa, bunları eklemeniz gerekir. Bunu yapmak için, imleci ilk satırı temsil eden sayının üzerine getirin ve aşağıdaki şekilde gösterildiği gibi siyah bir oka dönüşecektir:

Sağ tıklayın ve içerik menüsünden seçin sokma (Sokmak):

Sütunlara ad verin, örneğin, “Name"Ve"Çiftleme?» Ardından sekmeyi açın Veri (Veri) ve tuşuna basın filtre (Filtre):

Bundan sonra “ yanındaki küçük gri oka tıklayınÇiftleme?« filtre menüsünü açmak için; dışında bu listedeki tüm öğelerin işaretini kaldırın. çift, ve bas OK.

Hepsi bu, şimdi yalnızca sütunun bu öğelerini görüyorsunuz. А, sütunda çoğaltılan В. Eğitim masamızda bu tür sadece iki hücre var, ancak anladığınız gibi pratikte bunlardan çok daha fazlası olacak.

Bir sütunun tüm satırlarını yeniden görüntülemek için А, sütundaki filtre simgesini tıklayın Вşimdi küçük bir ok içeren bir huni gibi görünen ve Tümünü seç (Hepsini seç). Veya aynısını tıklayarak Şerit üzerinden de yapabilirsiniz. Veri (Veri) > Seç ve Filtrele (Sırala ve Filtrele) > Temizle (Temizle) aşağıdaki ekran görüntüsünde gösterildiği gibi:

Rengi değiştirin veya bulunan kopyaları vurgulayın

Eğer notlar "çift” amacınız için yeterli değil ve yinelenen hücreleri farklı bir yazı tipi rengi, dolgu rengi veya başka bir yöntemle işaretlemek istiyorsunuz…

Bu durumda, kopyaları yukarıda gösterildiği gibi filtreleyin, filtrelenmiş tüm hücreleri seçin ve Ctrl + 1diyaloğu açmak için biçim Hücreler (hücre biçimi). Örnek olarak, yinelenen satırlardaki hücrelerin dolgu rengini parlak sarıya değiştirelim. Tabii ki, araçla dolgu rengini değiştirebilirsiniz. Doldurmak (Dolgu Rengi) sekmesi Ana Sayfa (Ev) ancak iletişim kutusu avantajı biçim Hücreler (Hücre Biçimi), tüm biçimlendirme seçeneklerini aynı anda yapılandırabilirsiniz.

Artık kopyaları olan hiçbir hücreyi kesinlikle kaçırmayacaksınız:

İlk sütundan yinelenen değerleri kaldırma

Tabloyu, yalnızca yinelenen değerlere sahip hücreler gösterilecek şekilde filtreleyin ve bu hücreleri seçin.

Karşılaştırdığınız 2 sütun farklı sayfalardaysa, yani farklı tablolarda seçili aralığa sağ tıklayın ve Sırayı sil (Satırı kaldır):

Basın OKExcel sizden tüm sayfa satırını gerçekten silmek istediğinizi onaylamanızı ve ardından filtreyi temizlemenizi istediğinde. Gördüğünüz gibi, yalnızca benzersiz değerlere sahip satırlar kalır:

Aynı sayfada 2 sütun varsa, birbirine yakın (bitişik) veya birbirine yakın değil (bitişik değil), o zaman kopyaları kaldırma işlemi biraz daha karmaşık olacaktır. Yinelenen değerlere sahip tüm satırı kaldıramayız, çünkü bu, hücreleri ikinci sütundan da kaldıracaktır. Bir sütunda yalnızca benzersiz girişler bırakmak için А, Bunu yap:

1. Tabloyu yalnızca yinelenen değerleri gösterecek şekilde filtreleyin ve bu hücreleri seçin. Onlara sağ tıklayın ve içerik menüsünden seçin İçeriği temizle (içeriği temizle).
2. Filtreyi temizleyin.
3. Bir sütundaki tüm hücreleri seç А, hücreden başlayarak A1 verileri içeren en alta kadar.
4. Tıkla Veri (Veri) ve tuşuna basın A'dan Z'ye sırala (A'dan Z'ye sıralayın). Açılan iletişim kutusunda, öğesini seçin. Mevcut seçimle devam et (Belirtilen seçim içinde sıralayın) ve düğmesine tıklayın. Siyah (Sıralama):
5. Formül içeren sütunu silin, artık ihtiyacınız olmayacak, bundan sonra yalnızca benzersiz değerleriniz var.
6. İşte bu, şimdi sütun А yalnızca sütunda olmayan benzersiz verileri içerir В:

Gördüğünüz gibi, formülleri kullanarak Excel'deki iki sütundan kopyaları kaldırmak o kadar da zor değil.