Excel'de benzersiz değerleri nasıl birleştiririm?
Elektronik tablolarla çalışırken, bir sütundaki yalnızca benzersiz değerleri birleştirmeniz veya benzersiz girdileri ilgili kayıtlarıyla birlikte özetleyen listeler oluşturmanız gereken durumlarla sıkça karşılaşılır. Yinelenenleri yönetmek ve özetlenmiş bilgileri sunmak, verilerinizi düzenlemekle kalmaz, aynı zamanda raporları daha net ve anlamlı hale getirir. Excel’de bu hedeflere ulaşmanın birkaç pratik yolu vardır: yerleşik işlevlerden gelişmiş eklentilere ya da özel koda kadar çeşitlilik gösterir. Bu eğitim, benzersiz değerleri birleştirme ve benzersiz girdileri ilgili verileriyle birlikte listeleme konularında çok sayıda yöntemi ayrıntılı olarak ele alır. Sunulan çözümler farklı Excel sürümlerini ve kullanıcı tercihlerini kapsar; böylece senaryonuza en uygun yaklaşımı kolayca seçebilirsiniz.
Bir sütundan yalnızca benzersiz değerleri birleştirin
- TEXTJOIN ve UNIQUE işlevlerini kullanarak
- KUTOOLS AI Aide kullanarak
- Kullanıcı Tanımlı İşlev kullanarak
- Gelişmiş Excel formülü kullanarak (alternatif çözüm)
Benzersiz değerleri listeleyin ve ilgili değerleri birleştirin
- TEXTJOIN ve UNIQUE işlevlerini kullanarak
- Kutools for Excel kullanarak
- VBA kodu kullanarak
- Formüllerle birlikte Excel Özet Tablosu kullanarak (alternatif çözüm)
Bir sütundan yalnızca benzersiz değerleri birleştirin
Excel’de veri analizi yaparken karşılaşılan yaygın görevlerden biri, bir sütundaki farklı girdileri tek bir hücrede birleştirmektir. Bu işlem, özellikle özet raporlar oluştururken, listede yinelenen değerlerden kaçınırken veya verileri daha fazla işleme hazırlarken oldukça kullanışlıdır. Kullanılacak yöntemin seçimi, Excel sürümünüz, veri kümesinin büyüklüğü ve formüllerle ya da kodla olan aşinalık düzeyinize bağlıdır. Aşağıdaki yöntemler farklı ihtiyaçlara yönelik çözümler sunar; her birinin dikkat edilmesi gereken noktalarını vurgular ve doğru şekilde uygulanması için pratik ipuçları içerir.
Yöntem 1: TEXTJOIN ve UNIQUE işlevlerini kullanma
Excel 365 ve Excel 2021 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ürekliyse ve tüm benzersiz öğeleri seçtiğiniz bir sınırlayıcıyla tek bir hücrede hızlıca birleştirmek istiyorsanız en iyisidir. Yinelenenleri otomatik olarak kaldırır, kolayca denetlenebilir ve gerektiğinde aralığı veya sınırlayıcıyı değiştirmenize olanak tanır. Ancak bu yaklaşımın yalnızca en yeni Excel sürümlerinde kullanılabildiğini unutmayın; eski sürümler UNIQUE işlevini desteklemez.
Sonucu görüntülemek istediğiniz hücreye aşağıdaki formülü girin (verilerinizin A2:A18 hücrelerinde olduğunu varsayarak):
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A18))
- UNIQUE(A2:A18), A2:A18 aralığındaki yinelenen kayıtları filtreleyerek yalnızca benzersiz değerleri döndürür.
- TEXTJOIN(", ", TRUE, ...), bu benzersiz değerleri virgül ve boşlukla ayrılmış şekilde tek bir hücrede birleştirir. TRUE bağımsız değişkeni, birleştirme sırasında boş hücrelerin göz ardı edilmesini sağlar.
Yararlı ipuçları ve sorun giderme:
- Excel sürümünüzün UNIQUE ve TEXTJOIN işlevlerini desteklediğinden emin olun. #AD? hatası alıyorsanız, eski bir sürüm kullanıyor olabilirsiniz.
- TEXTJOIN işlevinde kullanılan sınırlayıcıyı "; " veya "|" gibi istediğiniz karakterle değiştirebilirsiniz.
- Orijinal aralığa veri eklediğinizde veya sildiğinizde formül otomatik olarak güncellenir.
- Formüldeki sınırlayıcı bağımsız değişkeninizi, istenmeyen fazladan boşluklardan veya sınırlayıcılardan kaçınmak için iki kez kontrol edin.
Yöntem 2: KUTOOLS AI Aide kullanma
Formül yazmadan benzersiz değerleri birleştirmenin daha hızlı ve tamamen otomatik bir yolunu mu arıyorsunuz? Kutools for Excel’in “AI Asistanı” aracı, tüm yetenek seviyelerindeki kullanıcılar için 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 büyük kolaylık sağlar.
Kutools for Excel yüklendikten sonra bu özelliğe erişmek için "Kutools" > "AI Asistanı" yolunu izleyerek "KUTOOLS AI Aide" bölmesini açın.
- Tek bir hücrede birleştirmek istediğiniz değerleri içeren hücreleri seçin; seçiminizin hedeflediğiniz verilerle tam olarak eşleştiğinden emin olun.
- Sohbet kutusuna ihtiyacınızı açıklayın. Örneğin şunu yazabilirsiniz:
Aralık seç sütunundan benzersiz değerleri virgülle birleştir ve sonucu C2 hücresine yerleştir - Enter tuşuna basın veya "Gönder" düğmesine tıklayın. Yapay zeka isteğinizi analiz eder ve işlemden sonra Kutools'un işlemi gerçekleştirmesi için "Yürüt"e basın. Sonuç, belirtildiği şekilde döndürülür.
Notlar ve ipuçları:
- Tüm yapay zeka özelliklerinden yararlanmak için en güncel Kutools sürümünü kullandığınızdan emin olun.
- En iyi sonuçlar için metin komutunuzda hem sınırlayıcıyı hem de hedef hücreyi belirtin.
- KUTOOLS AI, özellikle büyük aralıklarda veya çeşitli veri kümelerinde tekrarlanması gereken iş akışları için son derece verimlidir.
Yöntem 3: Kullanıcı Tanımlı İşlev kullanma
Gelişmiş esneklik, özel sınırlayıcılar veya birden çok çalışma kitabında yeniden kullanılabilir bir çözüm arayan kullanıcılar için VBA ile bir Kullanıcı Tanımlı İşlev (UDF) yazmak, benzersiz değerleri otomatik olarak birleştirmenin etkili bir yoludur. Bu VBA çözümü tüm Excel sürümleriyle uyumludur ve yeni işlevlerin kullanılabilirliğiyle sınırlı değildir.
- Çalışma kitabınızda makroları etkinleştirmeniz gerekiyor.
- Bu VBA kodunu gelecekte de kullanmaya devam etmek istiyorsanız, dosyayı "makro etkin" ().xlsm) olarak kaydedin.
- Çalışma kitabınızın düzenli yedeklerini almanız, yeni kod çalıştırmadan önce şiddetle önerilir.
1. ALT + F11 tuşlarına basarak Microsoft Visual Basic for Applications penceresini açın.
2. VBA penceresinde Ekle > Modül yolunu izleyin, ardından aşağıdaki kodu kopyalayıp yapıştırın:
VBA kodu: Benzersiz değerleri tek bir hücrede birleştir:
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 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 tuşuna basın. Hücre, “Sınırlı aralık”tan gelen tüm benzersiz değerleri virgülle ayırarak gösterecektir.
- Aralığınız farklıysa, A2:A18 ifadesini buna göre ayarlayın.
- Farklı bir sınırlayıcıya ihtiyacınız varsa, formüldeki ","ifadesini tercih ettiğiniz sembolle değiştirin (örn.)";" veya |).
- Bir #AD? hatası alırsanız, makroların etkin olduğundan ve KKD adının tam olarak eşleştiğinden emin olun.
İpucu: Bu işlevi diğer çalışma kitaplarında da kullanmak istiyorsanız, VBA kodunu o kitapların modüllerine de kopyalamanız gerekir.
Yöntem 4: Gelişmiş Excel formülü kullanma (alternatif çözüm)
UNIQUE işlevinin kullanılamadığı ortamlarda (örneğin Excel 2016 veya Excel 2019 gibi), klasik EĞER, EĞERSAY ve METİNBİRLEŞTİR işlevlerini kullanarak daha karmaşık bir formül kombinasyonuyla benzersiz değerleri birleştirebilirsiniz. Bu yöntem işe yarasa da, yüksek hesaplama yükü nedeniyle yalnızca küçük veri kümeleri için uygundur.
1. Hedef bir hücreye (örn. C2) aşağıdaki dizi formülünü girin (girdikten sonra sadece Enter tuşuna basmak 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ünüyorsa, formülünüz dizi formülü olarak doğru şekilde girilmiştir. Formül, A2:A18 aralığındaki benzersiz değerleri virgülle ayırarak birleştirip döndürecektir.
Not: Bu yöntemde, verilerinize uygun şekilde aralıkları güncellemeniz gerekir. Çok büyük aralıklar işlem süresini uzatabilir. Dizi formüllerinde pek rahat hissetmiyorsanız, yukarıda açıklanan VBA veya eklenti çözümlerini deneyin.
Benzersiz değerleri listeleyin ve ilgili değerleri birleştirin
Veri raporlamasında sıklıkla yalnızca bir sütundaki benzersiz değerleri çıkarmakla kalmaz, aynı zamanda başka bir sütundaki ilgili kayıtları toplamak veya birleştirmek istersiniz. Örneğin, her satış temsilcisinin sattığı tüm ürünleri tek bir listede toplamak ya da aynı kimlikle ilişkilendirilmiş tüm girişleri bir araya getirmek gibi. Doğru yöntemi seçmek, verilerinizin karmaşıklığına ve önceliğinizin otomasyon, kullanım kolaylığı mı yoksa uyumluluk mu olduğuna bağlıdır.
Yöntem 1: METİNBİRLEŞTİR ve UNIQUE işlevlerini kullanma
Excel 365 veya Excel 2021 kullanıyorsanız, sağlam ve tamamen formül tabanlı bir çözüm için UNIQUE ve FİLTRE işlevlerini METİNBİRLEŞTİR ile birleştirebilirsiniz. Bu yöntem, tek bir değerin birden fazla kayıta karşılık geldiği ve bu ilişkili kayıtların bir sınırlayıcıyla ayrılmış bir liste olarak sunulmasını istediğiniz durumlarda mükemmel sonuç verir.
1. A Sütunundaki tüm benzersiz değerleri listelemek için boş bir sütuna aşağıdaki formülü girin:
=UNIQUE(A2:A17)
2. Ardından, benzersiz değerlerinizin yer aldığı sütunun hemen yanındaki sütuna (örneğin, benzersiz değerleriniz D2 hücresinden başlıyorsa E2 hücresine) her bir benzersiz giriş için B sütunundaki ilgili değerleri birleştiren aşağıdaki formülü girin ve gerektiği kadar aşağıya sürükleyin:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$17, $A$2:$A$17 =D2))
- UNIQUE(A2:A17), A Sütunundaki benzersiz öğelerden oluşan bir dizi oluşturur.
- FILTER(B2:B17, A2:A17 = D2), D2 hücresindeki her benzersiz değer için B sütunundan ilgili tüm değerleri içeren bir dizi oluşturur.
- TEXTJOIN(", ", TRUE, ...), ilgili değerleri virgülle ayırarak birleştirir.
- Farklı bir sınırlayıcıya ihtiyacınız varsa, TEXTJOIN içindeki ", " ifadesini buna göre değiştirin.
- Hatalardan kaçınmak için formüllerinizdeki aralıkların eşit uzunlukta olduğundan ve FILTER işlevinin eksik eşleşmeler durumunda hata vermediğinden emin olun.
- Bu yaklaşım, verileriniz değiştiğinde sonuçları otomatik olarak günceller; böylece dinamik özet tablolar için idealdir.
Yöntem 2: Kutools for Excel kullanma
Kutools for Excel, benzersiz değerlere göre verileri gruplamak ve seçtiğiniz sınırlayıcıyla ilişkili değerleri birleştirmek için özel olarak geliştirilmiş «Gelişmiş Satırları Birleştir» aracına sahiptir. Bu araç, formül veya kod yazmada rahat hissetmeyen kullanıcılar için sezgisel ve görsel bir çözüm sunar. Özellikle büyük veri kümeleriyle çalışırken ya da periyodik raporlama ve sürekli veri bakımı gibi sık tekrarlanan yeniden gruplama işlemlerinde büyük kolaylık sağlar.
Değişiklik yapmadan önce, orijinal verilerinizi başka bir konuma yedeklemek iyi bir uygulamadır. Ardından aşağıdaki adımları izleyin:
- Düzenlemek istediğiniz veri aralığını seçin.
- Aşağıda gösterildiği gibi "Kutools" > "Birleştir ve Ayır" > "Gelişmiş Satırları Birleştir"yolunu izleyin:
- Açılan iletişim kutusunda:
- Birleştirilecek yinelenenleri içeren sütunu seçin ve "İşlem" sütununda bunu **"Birincil Anahtar"** olarak belirleyin.
- Toplamak (birleştirmek) istediğiniz sütunu seçin ve tercih ettiğiniz sınırlayıcıyı “İşlem” bölümündeki açılır listeden belirtin.
- Yürütme için Tamam düğmesine tıklayın.
Sonuç:
Kutools, verilerinizi yapılandırmanıza göre yeniden düzenleyecek, benzersiz girişleri çıkaracak ve ilişkili tüm değerleri birleştirecektir.
- Bir hata yaparsanız, Excel’deki Geri Al özelliğini (Ctrl+Z) kullanarak işlemi kolayca geri alın.
- Bu işlem, muhtemelen yüzlerce hatta binlerce kayıttan oluşan veri kümeleriyle sorunsuz çalışır ve çeşitli sınırlayıcıları destekler.
Yöntem 3: VBA kodu kullanma
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 özelleştirilmiş iş akışları, otomasyon ya da UNIQUE ve FİLTRE gibi işlevlerin bulunmadığı durumlar için idealdir. Veri yapınız sık sık değişiyorsa, bu VBA çözümü kolayca uyarlanabilir.
Aşağıdaki kodu kullanmak için aşağıdaki adımları izleyin:
1. VBA düzenleyicisini açmak için ALT + F11 tuşlarına basın.
2. Ekle > Modülseçeneğine gidin ve açılan modül penceresine aşağıdaki kodu yapıştırın:
VBA kodu: Benzersiz değerleri listele ve ilgili verileri birleştir
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. Açılan pencere, Veri Aralığı'nı seçmenizi isteyecektir. Kesinlikle iki sütun seçtiğinizden emin olun: ilk sütun benzersiz değerler için, ikinci sütun ise ilgili değerler içindir.
4. Tamam düğmesine tıklayın ve sonuç tablosunun başlaması gereken ilk hücreyi seçin.
5. Tamam düğmesine tıkladıktan sonra kod, yalnızca benzersiz değerleri ve ilişkili birleştirilmiş verileri içeren bir tablo oluşturacaktır.
- Sütun sayısıyla ilgili bir hata alırsanız, seçiminizin yalnızca iki sütundan oluştuğundan emin olun.
- Sınırlayıcınızı virgülden başka bir sembole değiştirmeniz 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 güncelleyin. - Yeni VBA betiklerini çalıştırmadan önce her zaman dosyanızın yedeğini alın.
Özetle, Excel benzersiz değerleri birleştirmek ve ilişkili verileri tek çatı altında toplamak için çeşitli yaklaşımlar sunar. Formül tabanlı yöntemler, modern Excel’de hızlı ve dinamik sonuçlar sağlarken; VBA ve Kutools çözümleri daha geniş uyumluluk ve gelişmiş kontrol imkânı sunar. Her zaman veri boyutunuza, kullandığınız Excel sürümüne ve tercih ettiğiniz iş akışına en uygun yöntemi seçin. Özellikle betik tabanlı veya toplu işlemler yapmayı düşünüyorsanız, mutlaka örnek veriler üzerinde test edin ve çalışmalarınızı yedeklemeyi unutmayın. Daha fazla Excel rehberliği ve gelişmiş ipucu için tüm eğitim koleksiyonumuzu inceleyin.
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi, Excel, Word, Outlook ve PowerPoint eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar