Ana içeriğe atla

Excel'deki ölçütlere göre metni kolayca nasıl birleştirebilirim?

Bazı kopyaları ve bir sütun adı içeren bir kimlik numarası sütunum olduğunu varsayarsak, şimdi, gösterilen sol ekran görüntüsü gibi benzersiz kimlik numaralarına dayalı olarak adları bir araya getirmek istiyorum, metni ölçütlere göre hızlı bir şekilde birleştirmek için, nasıl yapabiliriz? Excel'de ne yapmalı?

doc, metni ölçüt 1'e göre birleştirir

Kullanıcı Tanımlı İşlev ile ölçütlere göre metni birleştirin

Kutools for Excel ile ölçütlere göre metni birleştirin


Metni benzersiz kimlik numaralarıyla birleştirmek için, önce benzersiz değerleri çıkarabilir ve ardından adları benzersiz kimliğe göre birleştirmek için bir Kullanıcı Tanımlı İşlev oluşturabilirsiniz.

1. Aşağıdaki verileri örnek olarak alın, önce benzersiz kimlik numaralarını çıkarmanız gerekir, lütfen bu dizi formülünü uygulayın: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Bu formülü boş bir hücreye girin, örneğin D2, ardından Ctrl + Üst Karakter + Enter anahtarlar birlikte, ekran görüntüsüne bakın:

doc, metni ölçüt 2'e göre birleştirir

Bahşiş: Yukarıdaki formülde, A2: A15 benzersiz değerleri çıkarmak istediğiniz liste veri aralığıdır, D1 çıkarma sonucunu çıkarmak istediğiniz sütunun ilk hücresidir.

2. Ardından, boşluklar görüntülenene kadar tüm benzersiz değerleri çıkarmak için dolgu tutamacını aşağı doğru sürükleyin, ekran görüntüsüne bakın:

doc, metni ölçüt 3'e göre birleştirir

3. Bu adımda, bir Kullanıcı Tanımlı İşlev isimleri benzersiz kimlik numaralarına göre birleştirmek için lütfen ALT + F11 anahtarlar ve açılır Uygulamalar için Microsoft Visual Basic pencere.

4. tıklayın Ekle > modülve aşağıdaki kodu modül Pencere.

VBA kodu: ölçütlere göre metni birleştirin

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. Ardından bu kodu kaydedin ve kapatın, çalışma sayfanıza geri dönün ve bu formülü E2 hücresine girin, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , ekran görüntüsüne bakın:

doc, metni ölçüt 4'e göre birleştirir

6. Ardından doldurma tutamacını bu formülü uygulamak istediğiniz hücrelere sürükleyin ve ilgili tüm isimler kimlik numaralarına göre birleştirilmiştir, ekran görüntüsüne bakın:

doc, metni ölçüt 5'e göre birleştirir

İpuçları:

1. Yukarıdaki formülde, A2: A15 dayalı olarak birleştirmek istediğiniz orijinal veridir, D2 çıkardığınız benzersiz değerdir ve B2: B15 birleştirmek istediğiniz ad sütunudur.

2. Gördüğünüz gibi, virgülle ayrılan değerleri birleştirdim, formülün virgülünü "" istediğiniz gibi değiştirerek başka herhangi bir karakteri kullanabilirsiniz.


Eğer varsa Kutools for Excel, Onun ile Gelişmiş Kombine Satırları yardımcı program, metin tabanını ölçütlere göre hızlı ve kolay bir şekilde birleştirebilirsiniz.

Kutools for Excel : 300'den fazla kullanışlı Excel eklentisi ile 30 günde sınırlama olmaksızın ücretsiz deneyin.

Kurduktan sonra Kutools for Excel, aşağıdaki adımları uygulayın:

1. Bir sütuna göre birleştirmek istediğiniz veri aralığını seçin.

2. tıklayın Kutools > Birleştir ve Böl > Gelişmiş Kombine Satırları, ekran görüntüsüne bakın:

3. In Sütuna Göre Satırları Birleştirme iletişim kutusunda, Kimlik sütununu tıklayın ve ardından Birincil anahtar bu sütunu, birleştirilmiş verilerinizin dayandığı anahtar sütun yapmak için ekran görüntüsüne bakın:

doc, metni ölçüt 7'e göre birleştirir

4. Ve sonra tıklayın İsim değerleri birleştirmek istediğiniz sütunu, ardından Birleştirmek seçeneğini belirleyin ve birleştirilmiş veriler için bir ayırıcı seçin, ekran görüntüsüne bakın:

doc, metni ölçüt 8'e göre birleştirir

5. Bu ayarları tamamladıktan sonra OK iletişim kutusundan çıkmak için B sütunundaki veriler A anahtar sütununa göre birleştirilmiştir. Ekran görüntüsüne bakın:

doc, metni ölçüt 9'e göre birleştirir

Bu özellik sayesinde aşağıdaki sorun mümkün olan en kısa sürede çözülecektir:

Birden çok satırı tek bir satırda birleştirmek ve Excel'de kopyaları toplamak nasıl?

Kutools for Excel'i şimdi indirin ve ücretsiz deneyin!


Kutools for Excel: 300'den fazla kullanışlı Excel eklentisi ile 30 günde sınırlama olmaksızın ücretsiz deneyin. Hemen indirin ve ücretsiz deneme!

En İyi Ofis Üretkenlik Araçları

🤖 Kutools AI Yardımcısı: Aşağıdakilere dayalı olarak veri analizinde devrim yaratın: Akıllı Yürütme   |  Kodunu oluşturun  |  Özel Formüller Oluşturun  |  Verileri Analiz Edin ve Grafikler Oluşturun  |  Kutools İşlevlerini Çağır...
Popüler Özellikler: Yinelenenleri Bul, Vurgula veya Tanımla   |  Boş Satırları Sil   |  Veri Kaybı Olmadan Sütunları veya Hücreleri Birleştirin   |   Formülsüz Tur ...
Süper Arama: Çoklu Ölçütlü VLookup    Çoklu Değer VLookup  |   Birden Çok Sayfada VLookup   |   Bulanık Arama ....
Gelişmiş Açılır Liste: Hızla Açılır Liste Oluşturun   |  Bağımlı Açılır Liste   |  Çoklu Seçim Açılır Liste ....
Sütun Yöneticisi: Belirli Sayıda Sütun Ekleme  |  Sütunları Taşı  |  Gizli Sütunların Görünürlük Durumunu Değiştir  |  Aralıkları ve Sütunları Karşılaştırın ...
Öne Çıkan Özellikler: Izgara Odağı   |  Tasarım görünümü   |   Büyük Formül Çubuğu    Çalışma Kitabı ve Sayfa Yöneticisi   |  Kaynak Kütüphanesi (Otomatik metin)   |  Tarih Seçici   |  Çalışma Sayfalarını Birleştirin   |  Hücreleri Şifrele/Şifresini Çöz    E-postaları Listeye Göre Gönder   |  Süper Filtre   |   Özel Filtre (kalın/italik/üstü çizili filtre...) ...
En İyi 15 Araç Seti12 Metin Tools (Metin ekle, Karakterleri Kaldır, ...)   |   50+ Grafik Türleri (Gantt şeması, ...)   |   40+ Pratik Formüller (Yaşı doğum gününe göre hesapla, ...)   |   19 sokma Tools (QR Kodunu Girin, Yoldan Resim Ekle, ...)   |   12 Dönüştürme Tools (Sayılardan Kelimelere, Para Birimi Dönüştürme, ...)   |   7 Birleştir ve Böl Tools (Gelişmiş Kombine Satırları, Bölünmüş hücreler, ...)   |   ... ve dahası

Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar.  En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...

Açıklama


Office Tab, Office'e Sekmeli Arayüz Getirir ve İş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!
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
This comment was minimized by the moderator on the site
BTW i used the VBA solution
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For I = 1 To CriteriaRange.Count
If CriteriaRange.Cells(I).Value = Condition Then
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations