Excel'de benzersiz değerleri nasıl birleştirirsiniz?
Elektronik tablolarla çalışırken, yalnızca bir sütundaki benzersiz değerleri birleştirmek (concatenate) veya ilgili kayıtlarla birlikte benzersiz girişleri özetleyen listeler oluşturmak gibi durumlarla karşılaşmak yaygındır. Yinelenen verileri işleme ve özet bilgileri sunma, verilerinizi düzenli tutmanın yanı sıra raporlarınızı daha anlaşılır ve daha iç görü verecek şekilde de yapar. Excel'de bu hedeflere ulaşmak için çeşitli pratik yollar vardır; yerleşik işlevlerden gelişmiş eklentilere veya özel kodlara kadar. Bu öğretici, benzersiz değerleri birleştirme ve ilgili verilerle birlikte benzersiz girişleri listeleme konusunda birden fazla yöntemi detaylı olarak ele almaktadır. Sunulan çözümler, farklı Excel sürümleri ve kullanıcı tercihlerini karşılamakta olup size senaryonuz için en iyi yaklaşımı seçmede yardımcı olacaktır.
Bir sütundan yalnızca benzersiz değerleri birleştirin
- TEXTJOIN ve UNIQUE işlevlerini kullanarak
- Kutools AI Asistanı kullanarak
- Kullanıcı Tanımlı İşlev kullanarak
- Gelişmiş Excel formülü ile (alternatif çözüm)
Benzersiz değerleri listeleyin ve karşılık gelen değerleri birleştirin
- TEXTJOIN ve UNIQUE işlevlerini kullanarak
- Kutoolsfor Excel'i kullanarak
- VBA kodu kullanarak
- Formüllerle Excel PivotTablosu kullanarak (alternatif çözüm)
Bir sütundan yalnızca benzersiz değerleri birleştirin
Excel veri analizi sırasında sık karşılaşılan bir görev, bir sütundaki yalnızca farklı girişleri tek bir hücrede birleştirmektir. Bu, özellikle özet raporlar oluşturmak, bir listedeki yinelenen değerlere engel olmak veya verileri daha fazla işleme için hazırlamak açısından faydalı olabilir. Yöntem seçimi, Excel sürümünüze, veri seti boyutunuza ve formüller veya kod konusundaki aşinalığınıza bağlıdır. Aşağıdaki yöntemler farklı ihtiyaçları ele alır, her biri için dikkat edilmesi gereken hususları vurgular ve doğru uygulamayı sağlamak için pratik ipuçları sunar.
Yöntem 1: TEXTJOIN ve UNIQUE işlevlerini kullanma
Excel365 ve Excel2021 kullanıcıları için TEXTJOIN ve UNIQUE işlevlerinin tanıtılması, bir sütundaki benzersiz değerleri birleştirmeyi hem basit hem de esnek hale getirir.
Bu çözüm, veri sütununuz sürekli olduğunda ve tüm benzersiz öğeleri seçilen bir sınırlayıcı ile tek bir hücreye hızlı bir şekilde birleştirmek istediğinizde en iyisidir. Yinelenenleri otomatik olarak ortadan kaldırır, kolayca denetlenebilir ve gerektiğinde aralığı veya sınırlayıcıyı değiştirebilirsiniz. Ancak, bu yaklaşım yalnızca en son Excel sürümlerinde kullanılabilir; eski sürümler UNIQUE işlevini desteklemez.
Sonucu görüntülemek istediğiniz hücreye aşağıdaki formülü girin (verileriniz A2:A18 hücrelerinde olduğunu varsayarsak):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18), A2:A18 aralığındaki yinelenen girişleri filtreler ve yalnızca benzersiz değerleri döndürür.
- TEXTJOIN(", ", TRUE, ...) bu benzersiz değerleri virgül ve boşlukla ayrılmış tek bir hücrede birleştirir (concatenate). TRUE argümanı, boş hücrelerin birleştirme sırasında göz ardı edilmesini sağlar.
Faydalı ipuçları ve sorun giderme:
- Excel sürümünüzün UNIQUE ve TEXTJOIN işlevlerini desteklediğinden emin olun. #AD? hatası görüyorsanız, muhtemelen eski bir sürüm kullanıyorsunuzdur.
- TEXTJOIN'de kullanılan sınırlayıcı, tercihinize göre "; " veya "|" gibi herhangi bir şeye değiştirilebilir.
- Orijinal aralığa veri eklerseniz veya çıkarırsanız, formül otomatik olarak güncellenir.
- İstem dışı fazladan boşluk veya sınırlayıcılar oluşmasını önlemek için, formüldeki sınırlayıcı argümanınızı iki kez kontrol edin.
Yöntem 2: Kutools AI Asistanı'nı kullanma
Formül yazmadan, tamamen otomatikleştirilmiş bir şekilde benzersiz değerleri birleştirmenin daha hızlı bir yoluna ihtiyacınız olduğunda—Kutools for Excel'in "AI Asistanı" aracı, tüm beceri seviyelerindeki kullanıcılar için zaman kazandıran pratik bir çözüm sunar. Bu yöntem özellikle Excel'in gelişmiş formüllerine aşina değilseniz veya verileriniz sık sık değişiyor ve tekrarlanan görevler gerektiriyorsa çok faydalıdır.
Kutools for Excel yüklendikten sonra, bu özelliği "Kutools" > "AI Asistanı" öğesine tıklayarak açılan "KUTOOLS AI Asistanı" paneline erişebilirsiniz.
- Tek bir hücrede birleştirmek istediğiniz değerleri içeren hücreleri seçin, seçimizin amaçladığınız verilerle eşleştiğinden emin olun.
- Sohbet kutusunda, talebinizi açıklayın. Örneğin, şu şekilde yazabilirsiniz:
Seçili aralıktaki benzersiz değerleri virgülle birleştirin ve birleştirilmiş sonucu C2 hücresine yerleştirin - Şunu tuşlayın Enter tuşuna basın ya da "Gönder" düğmesine tıklayın. AI talebinizi analiz eder ve işlemeyi tamamladıktan sonra “Yürüt” düğmesine basarak Kutools'un işlemi gerçekleştirmesi sağlanır. Sonuç, tarif edildiği gibi döndürülecektir.
Notlar ve ipuçları:
- Tüm AI özelliklerine erişmek için en son Kutools sürümünü kullandığınızdan emin olun.
- En iyi sonuçlar için metin komutunuzda belirgin olun; sınırlayıcı ve hedef hücreyi belirtin.
- Kutools AI, geniş aralıklar veya çeşitli veri setlerinde tekrarlanması gereken iş akışları için özellikle etkilidir.
Yöntem 3: Kullanıcı Tanımlı İşlev Kullanımı
Gelişmiş esnekliğe ihtiyaç duyan, özel sınırlayıcılar gerektiren veya birden fazla çalışma kitabı için yeniden kullanılabilir bir araç isteyen kullanıcılar için VBA'da bir Kullanıcı Tanımlı İşlev (UDF) kodlamak, benzersiz değerleri otomatik olarak birleştirmek için etkili bir yoldur. Bu VBA çözümü tüm Excel sürümleriyle uyumludur ve yeni işlevlerin kullanılabilirliği ile kısıtlanmaz.
- Çalışma kitabınızda makroları etkinleştirmelisiniz.
- Bu VBA kodunu gelecekte de kullanmaya devam edecekseniz dosyayı "makro etkin" (.xlsm) olarak kaydedin.
- Yeni kod çalıştırmadan önce çalışma kitabınızın düzenli olarak yedeklenmesi önerilir.
Microsoft Visual Basic for Applications penceresini açmak için ALT + F11 tuşlarına basılı tutun.
2. VBA penceresinde, Ekle > Modül'e tıklayın, ardından aşağıdaki kodu kopyalayıp yapıştırın:
VBA kodu: Benzersiz değerleri tek bir hücreye birleştirin:
Function ConcatUniq(xRg As Range, xChar As String) As String
'updateby Extendoffice
Dim xCell As Range
Dim xDic As Object
Set xDic = CreateObject("Scripting.Dictionary")
For Each xCell In xRg
xDic(xCell.Value) = Empty
Next
ConcatUniq = Join$(xDic.Keys, xChar)
Set xDic = Nothing
End Function
3. Çalışma sayfanıza geri dönün ve boş bir hücreye (örneğin C2) aşağıdaki formülü yazın:
=ConcatUniq(A2:A18,",")Onaylamak için Enter'a basın. Hücre, belirtilen aralıktaki tüm benzersiz değerleri virgüllerle ayrılmış olarak gösterecektir.
- Aralığınız farklıysa, A2:A18'i buna göre ayarlayın.
- Farklı bir sınırlayıcıya ihtiyaç duyulursa, formüldeki "," işaretini tercih ettiğiniz simgeyle değiştirin (örneğin, ";" veya |).
- #AD? hatasıyla karşılaşırsanız, makroların etkin olduğundan ve UDF adının tam olarak eşleştiğinden emin olun.
İpucu: Bu işlevi diğer çalışma kitaplarında da kullanmak için VBA kodunu onların modüllerine de kopyalayın.
Yöntem 4: Gelişmiş Excel formülü ile (alternatif çözüm)
UNIQUE işlevinin bulunmadığı ortamlarda (örneğin, Excel 2016 veya Excel 2019'da), yine de klasik IF, COUNTIF ve TEXTJOIN işlevlerinin dizin formülleri kombinasyonunu kullanarak benzersiz değerleri birleştirebilirsiniz. Bu yaklaşım işe yarar ancak hesaplama yükü nedeniyle daha küçük veri setleri için uygundur.
1. Hedef hücrede (örneğin, C2), aşağıdaki dizi formülünü girin (girdiden sonra sadece Enter yerine Ctrl+Shift+Enter tuşlarına basın):
=TEXTJOIN(", ", TRUE, IF(MATCH(A2:A18, A2:A18,0) = ROW(A2:A18) - MIN(ROW(A2:A18)) +1, A2:A18, ""))2. Formülünüzün etrafında kıvırcık parantezler {} görürseniz, bu bir dizi formülü olarak doğru şekilde girilmiştir. Formül, A2:A18 aralığından virgülle ayrılmış birleştirilmiş benzersiz değerleri döndürecektir.
Not: Bu yöntem, aralıkları verilerinize göre ayarlamanızı gerektirir. Çok büyük aralıklar için hesaplama süresi artabilir. Dizi formülleri konusunda rahat değilseniz, yukarıda açıklanan VBA veya eklenti çözümlerini denemeyi düşünün.
Benzersiz değerleri listeleyin ve karşılık gelen değerleri birleştirin
Veri raporlamasında sıkça, yalnızca bir sütundan benzersiz değerleri çıkarmakla kalmaz, aynı zamanda başka bir sütundan ilgili girişlerini birleştirmek veya bir araya getirmek isteyebilirsiniz. Örneğin, her satış temsilcisi tarafından satılan tüm ürünleri bir araya getirmek veya aynı kimlikle ilişkilendirilmiş tüm girişleri derlemek gibi. Doğru yöntemi seçmek, verilerinizin karmaşıklığına ve otomasyon, kullanım kolaylığı veya uyumluluğun önceliğine bağlıdır.
Yöntem 1: TEXTJOIN ve UNIQUE işlevlerini kullanma
Excel 365 veya Excel 2021 kullanıyorsanız, UNIQUE ve FILTER işlevlerini TEXTJOIN ile birleştirerek güçlü, tamamen formül odaklı bir yaklaşım izleyebilirsiniz. Bu yöntem, bir değerin birden fazla kayda ilişkin olduğu ve bu ilgili kayıtların sınırlayıcılarla ayrılmış bir liste şeklinde olmasını istediğiniz durumlarda verileri özetlemek için iyi çalışır.
1. Boş bir sütunda, sütun A’dan tüm benzersiz değerleri listelemek için aşağıdaki formülü girin:
=UNIQUE(A2:A17)
2. Şimdi, her benzersiz giriş için sütun B’den karşılık gelen değerleri birleştirmek üzere, benzersiz değerinizin yanındaki (örneğin, D2'deki benzersiz değerleriniz varsa E2) bir sonraki sütunda bu formülü girin ve gerektiği kadar aşağı çekin:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17), sütun A'dan benzersiz öğelerin bir dizisini oluşturur.
- FILTER(B2:B17, A2:A17 = D2), D2'deki her benzersiz değer için sütun B'deki ilgili tüm değerleri içeren bir dizi oluşturur.
- TEXTJOIN(", ", TRUE, ...) bu ilgili değerleri virgüllerle birleştirir.
- Farklı bir sınırlayıcıya ihtiyacınız varsa, TEXTJOIN içindeki ", " işaretini buna göre değiştirin.
- Hatalardan kaçınmak için, formüllerinizdeki aralıkların uzunluğunun eşit olduğundan ve FILTER'in eksik eşleşmeler için hata döndürmediğinden emin olun.
- Bu yaklaşım, verileriniz değişirken sonuçları otomatik olarak günceller, dinamik özet tablolar için uygundur.
Yöntem 2: Kutools for Excel'i kullanma
Kutools for Excel, benzersiz değerlere göre verileri gruplamak ve seçtiğiniz sınırlayıcı ile karşılık gelen değerleri birleştirmek için tasarlanmış bir "Gelişmiş Satırları Birleştir" aracı içerir. Bu, formül veya kod yazmaktan hoşlanmayan kullanıcılar için grafiksel bir çözüm arayanlara uygundur. Özellikle büyük veri setleriyle uğraşırken veya periyodik raporlar veya devam eden veri bakımında sıkça yeniden gruplama yapılması gerektiğinde değerlidir.
Değişiklik yapmadan önce, orijinal verileri başka bir konuma kopyalayarak verilerinizi yedeklemeniz iyi bir uygulamadır. Ardından şu adımları izleyin:
- Düzenlemek istediğiniz veri aralığını seçin.
- Şuraya gidin "Kutools" > "Birleştir ve Böl" > "Gelişmiş Satırları Birleştir", aşağıda gösterildiği gibi:
- Açılan iletişim kutusunda:
- Birleştirilecek yinelenenleri içeren sütunu seçin ve "İşlem" sütununda "Anahtar" olarak ayarlayın.
- Birleştirmek istediğiniz sütunu (birleştirmek için değerler) seçin; "İşlem" altında açılır listeden tercih ettiğiniz sınırlayıcıyı belirtin.
- Uygula'yı tıklayın.
Sonuç:
Kutools, kurulumunuza göre verilerinizi yeniden düzenleyerek benzersiz girişleri çıkaracak ve tüm ilişkili değerleri birleştirecektir.
- Bir hata yaparsanız, Excel'deki Geri Al özelliğini kullanın (Ctrl+Z) geri dönebilirsiniz.
- Bu işlem, yüzlerce veya binlerce kayıt içeren veri setleri için çalışır ve çeşitli sınırlayıcıları destekler.
Yöntem 3: VBA kodu kullanma
Bir VBA betiği kullanmak, verilerinizi nasıl çıkaracağınız ve özetleyeceğiniz konusunda tam kontrol sağlar. Bu yaklaşım tüm Excel sürümleriyle uyumludur ve özellikle özel iş akışları, otomasyon veya UNIQUE veya FILTER gibi işlevlerin bulunamaması durumunda çok uygundur. Veri yapınız sık sık değişiyorsa, bu VBA çözümü kolayca uyarlanabilir.
Aşağıdaki kodu kullanmak için, şu adımları izleyin:
1. VBA düzenleyicisini açmak için ALT + F11 tuşlarına basın.
2. Ekle > Modül'e gidin, ardından aşağıdaki kodu açılan modül penceresine yapıştırın:
VBA kodu: Benzersiz değerleri listeleyin ve karşılık gelen verileri birleştirin
Sub test()
'updateby Extendoffice
Dim xRg As Range
Dim xArr As Variant
Dim xCell As Range
Dim xTxt As String
Dim I As Long
Dim xDic As Object
Dim xOutputRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Please select the data range", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "Does not support multiple selections", , "Kutools for Excel"
Exit Sub
End If
If xRg.Columns.Count <> 2 Then
MsgBox "There must be only two columns in the selected range", , "Kutools for Excel"
Exit Sub
End If
Set xOutputRg = Application.InputBox("Please select the output cell", "Kutools for Excel", Type:=8)
If xOutputRg Is Nothing Then Exit Sub
xArr = xRg
Set xDic = CreateObject("Scripting.Dictionary")
xDic.CompareMode = 1
For I = 1 To UBound(xArr)
If Not xDic.Exists(xArr(I, 1)) Then
xDic.Item(xArr(I, 1)) = xDic.Count + 1
xArr(xDic.Count, 1) = xArr(I, 1)
xArr(xDic.Count, 2) = xArr(I, 2)
Else
xArr(xDic.Item(xArr(I, 1)), 2) = xArr(xDic.Item(xArr(I, 1)), 2) & "," & xArr(I, 2)
End If
Next
xOutputRg.Resize(xDic.Count, 2).Value = xArr
End Sub
3. Betiği çalıştırmak için F5 tuşuna basın. Size veri aralığını seçmenizi isteyen bir açılır pencere çıkacaktır. Tam olarak iki sütun seçtiğinizden emin olun: birincisi benzersiz değerler için, ikincisi ise karşılık gelen değerler için.
4. Tamam'ı tıklayın ve sonuç tablosunun başlaması gereken ilk hücreyi seçin.
5. Tamam'ı tıkladıktan sonra, kod yalnızca benzersiz değerleri ve bunlara ait birleştirilmiş verileri içeren bir tablo oluşturacaktır.
- Sütun sayısıyla ilgili bir hata alırsanız, seçiminizde yalnızca iki sütun bulunduğundan emin olun.
- Sınırlayıcınızın virgülden başka bir sembole değiştirilmesi gerekiyorsa,
xArr(xDic.Item(xArr(I,1)),2) = xArr(xDic.Item(xArr(I,1)),2) & "," & xArr(I,2)satırındaki kodu gerektiği gibi ayarlayın. - Yeni VBA betikleri çalıştırmadan önce dosyanızı her zaman yedekleyin.
Özetle, Excel, benzersiz değerleri birleştirme ve ilgili verileri birleştirme konusunda çeşitli yaklaşımlar sunar. Formül yöntemleri modern Excel'de hızlı ve dinamiktir, VBA ve Kutools çözümleri ise daha geniş uyumluluk ve daha fazla kontrol sağlar. Her zaman veri boyutunuza, Excel sürümünüze ve tercih ettiğiniz iş akışına uygun yöntemi seçin. Komut dosyası tabanlı veya toplu işlemler denediğinizde, özellikle örnek verilerde test etmeyi veya çalışmanızı yedeklemeyi unutmayın. Daha fazla Excel rehberliği ve gelişmiş ipuçları için tam öğretici koleksiyonumuzu inceleyin.
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