Excel'de birden fazla sütundan benzersiz değerler nasıl çıkarılır?
Eğer sık sık Excel'de birkaç sütuna yayılmış veri setleriyle çalışıyorsanız, belirli değerlerin aynı sütun içinde veya farklı sütunlar arasında tekrarlandığı durumlarla karşılaşabilirsiniz. Birçok raporlama veya veri analizi görevinde, tüm benzersiz değerleri — yani seçimin tamamında yalnızca bir kez görünen değerleri — tespit etmek ve çıkarmak gerekir, konumlarından bağımsız olarak. Bunu elle yapmak zaman alıcı olabilir ve hata yapma riski taşır, özellikle büyük veri setleri veya karmaşık tablolarla uğraşırken. Neyse ki, Excel bu benzersiz değerleri verimli bir şekilde çıkarmak için çeşitli yöntemler sunar.
Bu kılavuz, formüller (tüm sürümler için uygun olanlar), son sürümler için dinamik dizi formülleri, Kutools AI Asistanı ile basit sonuçlar elde etme, Pivot Tablolar ile görsel birleştirme ve karmaşık senaryolarda otomatik çıkarma için VBA kodu gibi Excel sürümünüze ve tercihlerinize göre kullanabileceğiniz çeşitli çözümler tanıtmaktadır.
Formüllerle birden fazla sütundan benzersiz değerleri çıkarın
Bazı durumlarda bu çıkarmayı Excel'in yerleşik işlevlerini kullanarak gerçekleştirmek isteyebilirsiniz. Bu bölüm, tüm Excel sürümleri için uygun olan bir dizi formülü ve Excel 365 ve Excel 2021 gibi daha yeni sürümlerde bulunan dinamik dizi formüllerini kullanarak bunu nasıl yapacağınızı ayrıntılı olarak açıklamaktadır. Bu yöntemler, doğrudan formül tabanlı bir çözüme ihtiyaç duyduğunuzda, verileriniz değişirken sık sık güncelleme gerektirdiğinde veya harici eklentilerden veya kodlardan kaçınmak istediğinizde idealdir.
Tüm Excel sürümleri için dizi formülü ile birden fazla sütundan benzersiz değerleri çıkarın
Tüm Excel sürümleriyle uyumluluk açısından, dinamik dizilere destek vermese bile, bir dizi formülü kullanarak birkaç sütundan benzersiz değerleri çıkarabilirsiniz. Bu yaklaşım INDIRECT, TEXT, MIN, IF, COUNTIF, ROW ve COLUMN işlevlerinin bir kombinasyonunu kullanır, bu da onu çeşitli veri yapıları için esnek kılar.
Verileriniz A2:C9 aralığında olsun. Benzersiz değerleri E2 hücresinden başlayarak çıkarmak için aşağıdaki işlemi kullanın:
1. E2 hücresine (veya çıktı aralığınızın ilk hücresine) tıklayın ve aşağıdaki dizi formülünü girin:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
- A2:C9, benzersiz değerlerin çıkarılmasını istediğiniz veri aralığıdır.
- E1:E1, ilk çıktı hücresinin hemen üstündeki hücrelere atıfta bulunur ve hangi girişlerin zaten çıktılanmış olduğunu takip etmek için gereklidir.
- $2:$9 verilerinizin satır referansları; $A:$C sütun referanslarıdır. Kendi çalışma sayfanızın düzenine uyacak şekilde bunları gerektiği gibi ayarlayın.
2. Formülü girdikten sonra, Enter tuşuna basmak yerine Ctrl + Shift + Enter tuşlarına birlikte basın, böylece onu bir dizi formülü olarak onaylayın. Doğru yapıldığında, formül çubuğundaki formülünüzün çevresinde süslü parantezler {} belirecektir. Ardından, E2'den itibaren sütun boyunca doldurma tutamacını sürükleyin. Boş hücreler belirene kadar sürüklemeye devam edin, bu da artık çıkarılacak başka benzersiz değer kalmadığını gösterir. Bu süreç, tüm benzersiz değerlerin hedef sütunda görüntüleneceğini garanti eder.
- $A$2:$C$9: Benzersiz değerlerin incelenmesi gereken tüm hücre kümesini belirtir.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>"" boş hücrelerin göz ardı edilmesini sağlar.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 yalnızca yeni (henüz çıkarılmamış) değerlerin dahil edildiğinden emin olur.
- Her iki koşul da doğruysa, ilgili çıktı, hücrenin satırına ve sütununa dayalı bir hesaplamadır, bu da benzersiz bir indeks numarası oluşturur.
- Herhangi bir koşul yanlışsa, formül, yanlış seçimleri önlemek için çok büyük bir sayı (7^8) döndürür.
- MIN(...): En düşük indeks numarasını belirler, veri içindeki bir sonraki kullanılabilir benzersiz değerin konumunu etkin bir şekilde bulur.
- TEXT(...,"R0C00"): İndeksi R1C1 stilini kullanarak geçerli bir hücre referansına dönüştürür.
- INDIRECT(...): Yukarıda oluşturulan hücre referansını veri aralığından bir değere dönüştürür.
- &"": Formül sonucunun metin olarak ele alınmasını sağlar, böylece biçimlendirme sürprizleri önlenir.
Excel 365, Excel 2021 ve daha yeni sürümler için formüllerle birden fazla sütundan benzersiz değerleri çıkarın
Eğer Excel 365, Excel 2021 veya daha yeni bir sürüm kullanıyorsanız, dinamik dizi fonksiyonlarına erişiminiz vardır, bu da birden fazla sütundan benzersiz değerleri çıkarmak için daha basit ve sezgisel bir yol sağlar. UNIQUE ve TOCOL işlevleri, özellikle sürekli güncellenen veya daha büyük veri setleriyle çalışanlar için, tek bir adımda sütunlar arasında veri birleştirme ve yinelenenleri ortadan kaldırma işlemini kolaylaştırır ve hızlandırır.
Bu yöntemi kullanmak için, basitçe boş bir hücreye tıklayın (örneğin, E2 ya da sonuçların görünmesini istediğiniz herhangi bir yer), bu formülü girin ve Enter tuşuna basın:
=UNIQUE(TOCOL(A2:C9,1))Enter tuşuna bastıktan sonra, A2:C9 aralığındaki tüm benzersiz değerler formülün altındaki hücrelere otomatik olarak dökülecektir. Bu özellik özellikle etkilidir — çıkışınız, kaynak verileriniz değiştiğinde dinamik olarak güncellenir, size manuel yenileme adımlarından tasarruf ettirir.
- TOCOL(A2:C9,1): Verilerinizi birden fazla sütundaki değerlerden tek bir sütuna dönüştürür ve boş hücreleri otomatik olarak kaldırır.
- UNIQUE(...): Her bir değeri yalnızca bir kez çıkarır ve temiz, yinelenenlerden arındırılmış bir liste sağlar.
Kutools AI Asistanı ile birden fazla sütundan benzersiz değerleri çıkarın
Daha akıcı bir yaklaşım tercih eder ve manuel çabayı azaltmak isterseniz, Kutools for Excel'deki Kutools AI Asistanı, birden fazla sütundan benzersiz değerleri kolayca çıkmanıza yardımcı olabilir. Bu yöntem, özellikle formüllerle tanışık değilseniz veya formül hataları riskinden kaçınmak istiyorsanız değerlidir. Kutools AI Asistanı talimatlarınızı yorumlar ve verileri otomatik olarak işler, bu da hem başlangıç seviyesindeki kullanıcılar hem de birkaç tıklamayla hızlı bir çözüm arayan kullanıcılar için idealdir.
Yüklemeden sonra, Kutools AI > AI Asistanı'na tıklayarak "Kutools AI Asistanı" panelini açın:
- Sohbet kutusuna talebinizi girin, örneğin: "A2:C9 aralığından boş hücreleri görmezden gelerek benzersiz değerleri çıkarın ve sonuçları E2'den başlayarak yerleştirin:"
- "Gönder"e tıklayın veya Enter tuşuna basın, ardından AI talebi analiz ettikten sonra, "Yürüt"e tıklayarak çalıştırın. Sonuçlar, belirttiğiniz konumda anında çalışma sayfasında görünecektir.
İpucu: Bu çözüm, veri çıkarma iş akışınız değişiyorsa veya doğal dil işleme özellikleri istiyorsanız çok kullanışlıdır. Özgün verileriniz tam olarak tutarlı değilse, boş hücreler için çıkarılan listeyi çift kontrol etmeyi unutmayın, çünkü boş girişler AI talep detaylarınıza bağlı olarak dahil edilmiş veya filtrelenmiş olabilir.
Pivot Tablo ile birden fazla sütundan benzersiz değerleri çıkarın
Pivot Tablolar, özellikle benzersiz öğeleri özetlemek veya daha fazla analiz etmek (örneğin, oluşumları saymak) istiyorsanız, benzersiz değerleri çıkarmak için başka bir uygun yöntemdir. Bu yaklaşım basittir ve formüller gerektirmez. Ancak, özellikle ilgili sütunların farklı başlıklara sahip olması durumunda, birkaç adım kurulum ve küçük veri düzenleme gerektirir.
Pivot Tablo kullanarak benzersiz değerleri çıkarmak için önerilen bir süreç şöyledir:
1. Verilerinizin hemen soluna yeni bir boş sütun ekleyin. Örneğin, verileriniz B sütununda başlıyorsa, yeni bir A sütunu ekleyin. Bu düzenleme, doğru aralık birleşimini sağlamaya yardımcı olur.
2. Veri setinizdeki herhangi bir hücreyi seçin, Alt + D tuşlarına basın ve ardından P tuşuna basın PivotTable ve PivotChart Sihirbazı'nı başlatmak için. Sihirbazın ilk adımında, "Çoklu konsolidasyon aralıkları" seçeneğini seçin. Bu, birçok sütundaki değerleri tek bir özet alanına birleştirmenizi sağlar.
3. İleri'ye tıklayın, ardından "Benim için tek bir sayfa alanı oluştur" seçeneğini seçin. Bu adım, tüm verilerinizi benzersiz değerlerin daha kolay çıkarılması için tek bir grupta organize eder.
4. Bir sonraki adımda, tüm veri aralığını seçin (yeni boş sütunu da dahil edin), Ekle düğmesine tıklayarak seçiminizi "Tüm aralıklar" listesine getirin ve İleri'ye tıklayın.
5. Sihirbazın son adımında, Pivot Tabloyu nereye yerleştirmek istediğinizi seçin (yeni çalışma sayfası veya mevcut sayfa) ve ardından Pivot Table raporunu oluşturmak için Tamam'a tıklayın.
6. Yeni Pivot Tabloda, "Rapora eklenecek alanları seç" bölümündeki tüm alanların işaretini kaldırarak varsayılan görünümü temizleyin.
7. Son olarak, "Değer" alanını Satırlar alanına sürükleyin. Pivot Tablo, orijinal çoklu sütun aralığınızdan tüm benzersiz değerleri tek bir sütunda düzenli bir şekilde görüntüler.
Sınırlamalar: Veriler ön düzenleme gerektirir ve kaynak veri setiniz güncellenirse, yeni benzersiz değerleri görmek için Pivot Tabloyu yenilemeniz gerekir.
VBA kodu ile birden fazla sütundan benzersiz değerleri çıkarın
Otomatik çıkarma yapmanız veya büyük ve düzensiz veri setlerini işlemeniz gereken durumlarda, VBA (Visual Basic for Applications) kodu kullanımı hızlı ve yeniden kullanılabilir bir çözüm sağlayabilir. Bu, Excel VBA düzenleyiciyle temel düzeyde aşina olan kullanıcılar veya manuel işlemleri en aza indirmek istediğiniz tekrarlayan görevler için idealdir. VBA ayrıca büyük veri hacimlerini dizi formüllerinden daha verimli bir şekilde işleyebilir.
1. VBA düzenleyiciyi açmak için Alt + F11 tuşlarına basın. Görünen "Microsoft Visual Basic for Applications" penceresinde, Ekle > Modül'e tıklayarak yeni bir modül ekleyin.
2. Yeni modüle aşağıdaki kodu yapıştırın:
VBA: Birden fazla sütundan benzersiz değerleri çıkarın
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. Kodu çalıştırmak için F5 tuşuna basın. Bir iletişim kutusu, veri aralığını seçmenizi isteyecektir. Tüm ilgili sütunları seçin (boş hücre içerenleri de dahil).
4. Tamam'a tıkladıktan sonra, başka bir iletişim kutusu benzersiz değerlerin nereye çıktısını alacağını sorar. Sonuçların listeleneceği üst hücreyi belirtin (örneğin, E2).
5. Tamam'a tıklayın ve makro otomatik olarak çalışacaktır. Tüm benzersiz değerler, belirttiğiniz konumdan başlayarak görünecektir.
- Formüller kullanırken #DEĞER! veya #SPILL! gibi hatalar alırsanız, aralıklarınızı kontrol edin ve çıktı alanının boş olduğundan emin olun.
- Benzersiz değer çıkarmada doğruluğu etkileyebilecek gizli satırlar veya birleştirilmiş hücreler olup olmadığını her zaman kontrol edin.
- Dizi ve dinamik dizi formülleri değişikliklerle otomatik olarak güncellenir, ancak Gelişmiş Filtre ve Pivot Tablo çözümleri manuel yenileme veya yeniden çalıştırma gerektirebilir.
- Tekrarlayan görevler için, tutarlılık ve hız için VBA kullanarak çıkarmayı otomatikleştirmeyi düşünün.
- Özellikle karmaşık çalışma kitaplarında, herhangi bir toplu çıkarma veya otomasyon rutinlerini uygulamadan önce verilerinizi yedekleyin.
Daha fazla ilgili makale:
- Bir listeden benzersiz ve farklı değerlerin sayısını sayma
- Diyelim ki bazı yinelenen öğeler içeren uzun bir değer listesi var ve bir sütundaki benzersiz değerlerin (yalnızca bir kez görünen değerler) veya toplam farklı değerlerin sayısını öğrenmek istiyorsunuz, soldaki ekran görüntüsünde gösterildiği gibi. Bu makale, Excel'de benzersiz ve farklı girişleri saymak için etkili yöntemler açıklamaktadır.
- Excel'de kriterlere göre benzersiz değerleri çıkarın
- Diyelim ki, A sütunundaki belirli bir koşula dayalı olarak B sütunundan yalnızca benzersiz isimleri çıkarmak istiyorsunuz, ekran görüntüsünde gösterildiği gibi sonuçlar üretmek istiyorsunuz. Bu öğretici, benzersiz değerleri çıkarırken kriter uygulamanın yollarını gösterir.
- Excel'de yalnızca benzersiz değerlere izin verin
- Eğer bir çalışma sayfası sütununda yalnızca benzersiz girişlere izin vermek ve yinelenen değerlere engel olmak istiyorsanız, bu makale Excel'de benzersizlik kurallarını uygulamak için pratik teknikler tanıtmaktadır.
- Excel'de kriterlere göre benzersiz değerleri toplayın
- Örneğin, yanındaki sütundaki isimlere göre bir "Sipariş" sütunundaki yalnızca benzersiz değerleri toplamanız gerekebilir, ekran görüntüsünde gösterildiği gibi. Bu makale, benzersiz ve koşullu hesaplamaları birleştirme yaklaşımlarını tartışmaktadır.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin