Ana içeriğe atla

Virgülle ayrılmış tek bir hücrede birden çok arama değeri nasıl döndürülür?

Yazar: Xiaoyang Son Değiştirilme Tarihi: 2020-05-29

Excel'de, bir tablo hücresinden ilk eşleşen değeri döndürmek için DÜŞEYARA işlevini uygulayabiliriz, ancak bazen, tüm eşleşen değerleri ayıklamamız ve ardından virgül, tire, vb. Gibi belirli bir sınırlayıcıyla tek bir Aşağıdaki ekran görüntüsü gibi hücre. Excel'de virgülle ayrılmış tek bir hücrede birden çok arama değerini nasıl alabilir ve döndürebiliriz?

doc virgülle ayrılmış birden çok değer döndürür 1

Kullanıcı Tanımlı İşlev ile virgülle ayrılmış tek bir hücrede birden çok arama değeri döndür

Kutools for Excel ile virgülle ayrılmış tek bir hücrede birden çok arama değeri döndür

Kullanıcı Tanımlı İşlev ile virgülle ayrılmış tek bir hücrede birden çok arama değeri döndür

Normalde, birden çok eşleşen değeri ayıklayıp döndürmemizin ve virgülle ayırarak tek bir hücreye döndürmemizin doğrudan bir yolu yoktur, burada, bu işi çözmek için bir Kullanıcı Tanımlı İşlev oluşturabilirsiniz, lütfen aşağıdaki işlemleri gerçekleştirin:

1. Basılı tutun ALT + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.

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

VBA kodu: Birden çok arama değerini virgülle ayrılmış bir hücreye döndürür

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Ardından bu kodu kaydedin ve Modül penceresini kapatın, çalışma sayfanıza geri dönün ve şu formülü girin: = SingleCellExtract (D2, A2: B15,2, ",") sonucu döndürmek istediğiniz boş bir hücreye. Ve ardından Keşfet sonucu almak için anahtar, ekran görüntüsüne bakın:

doc virgülle ayrılmış birden çok değer döndürür 2

not: Yukarıdaki formülde:

D2: aramak istediğiniz hücre değerlerini gösterir;

A2: B15: veriyi getirmek istediğiniz veri aralığıdır;

2: 2 sayısı, eşleşen değerin döndürüleceği sütun numarasıdır;

,: virgül, birden çok değeri ayırmak istediğiniz ayırıcıdır.

Bunları ihtiyacınıza göre değiştirebilirsiniz.

Kutools for Excel ile virgülle ayrılmış tek bir hücrede birden çok arama değeri döndür

Eğer varsa Kutools for Excel, bu görev artık bir sorun olmayacak. Gelişmiş Kombine Satırları yardımcı programı, bir sütuna dayalı tüm göreli değerleri birleştirmenize yardımcı olabilir.

Kutools for Excel : 300'den fazla kullanışlı Excel eklentisi ile 30 günde sınırlama olmaksızın ücretsiz deneyin. 
İndir'e git
30 gün Ücretsiz DenemeSatın alma
PayPal / Ticaretim

Kurduktan sonra Kutools for Excellütfen aşağıdaki işlemleri yapın:

1. Eşleşen tüm değerleri 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, birleştirmek istediğiniz sütun adına tıklayın ve ardından Birincil anahtar düğmesi, ekran görüntüsüne bakın:

doc virgülle ayrılmış birden çok değer döndürür 4

4. Ardından, eşleşen değerlerini birleştirmek istediğiniz diğer sütun adını tıklayın ve Birleştirmek birleştirilmiş değerleri ayırmak için bir ayırıcı seçmek için ekran görüntüsüne bakın:

doc virgülle ayrılmış birden çok değer döndürür 5

5. Sonra tıklayın OK düğmesi, aynı değere sahip tüm karşılık gelen hücreler virgülle ayrılmış tek bir hücrede birleştirilmiştir, ekran görüntülerine bakın:

doc virgülle ayrılmış birden çok değer döndürür 6 2 doc virgülle ayrılmış birden çok değer döndürür 7

Bu Advanced Combine Rows yardımcı programı hakkında daha fazla bilgi edinmek için tıklayın…

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

Demo: Kutools for Excel ile virgülle ayrılmış tek bir hücrede birden çok arama değeri döndür

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ç Seti:  12 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 (19)
Rated 5 out of 5 · 2 ratings
Bruno
Bruno
This comment was minimized by the moderator on the site
Como faço para ajustar a função do "MultipleValues" para ignorar células vazias?

Pois no meu caso tenho algumas células em branco e quando faço o merge, fico com algumas vírgulas repetidas.

Ex.: (001,002,,,,003).

Obrigado!
Reply
0
0
skyyang
skyyang     Bruno
This comment was minimized by the moderator on the site
Hello, Bruno,
To ignore the blank cells when combine, pleae apply the following code:

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String) As String
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            ' Check if the cell in the specified column is not empty before appending
            If Not IsEmpty(LookupRange.Cells(I, ColumnNumber)) Then
                If xRet = "" Then
                    xRet = LookupRange.Cells(I, ColumnNumber) & Char
                Else
                    xRet = xRet & LookupRange.Cells(I, ColumnNumber) & Char
                End If
            End If
        End If
    Next
    If Len(xRet) > 0 Then
        SingleCellExtract = Left(xRet, Len(xRet) - 1)
    Else
        SingleCellExtract = ""
    End If
End Function


Please have a try, hope it can help you!
Reply
Report
0
0
Bruno
Bruno     skyyang
This comment was minimized by the moderator on the site
Hello skyyang,

Yes, it works!

Thank you!
Reply
0
0
Yery
Yery
This comment was minimized by the moderator on the site
No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4;S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

me devuelva los valores en líneas diferentes.
S-01-08-0017->Micro Post 1R, Black, Stainless Steel -End,->4
S-01-08-0057->Micro Post 2R, Black, Stainless Steel -End,->2

La función es:
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function
Reply
0
0
skyyang
skyyang     Yery
This comment was minimized by the moderator on the site
Hello, Yery,
Did you mean to split a cell into multiple rows based on the semicolon character?
If so, the following VBA code can help you:
Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

Please have a try, hope it can help you!
Reply
Report
0
0
Manikanta
Manikanta
This comment was minimized by the moderator on the site
Hi,

I want exact validation code for multiple values separated by comma and space for each value.

Example:
Lucy, Tom, Nicol, Akash, Apple

Please replay if you have any suggestions.
Reply
0
0
Rated 4.5 out of 5
skyyang
skyyang     Manikanta
This comment was minimized by the moderator on the site
Hello， Manikanta
To separate the multiple values by a comma and space, you just need to add a space behind the comma, change the formula as this: =SingleCellExtract(D2,A2:B15,2,", ").
Please try, hope it can help you!
Reply
Report
0
0
Manikanta
Manikanta     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

Thanks for your replay!

I tried already same way but in the cell value last one extra comma (,) coming below is the example.

Lucy, Tom, Nicol, Akash, Apple,

This will not work for Json file, so I want values separated by comma and space like below.

Lucy, Tom, Nicol, Akash, Apple

Thank you!
Reply
0
0
skyyang
skyyang     Manikanta
This comment was minimized by the moderator on the site
Hello, Manikanta
In this case, you can apply the below User Defined Function:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


After pasting the code, please use this formula: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Please try, hope this can help you!
If you still have any other problem, please comment here.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
Reply
Report
0
0
Manikanta
Manikanta     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

This is working now, Thank you for your quick response.

It's very use full to me once again Thank you for your help.

Regards,
Manikanta.
Reply
0
0
Rated 5 out of 5
KPRS92
KPRS92
This comment was minimized by the moderator on the site
Good Morning,

the VBA code worked perfectly with my worksheet, pretty clear and simple, however, I was trying to find a way to tell excel to only return the unique values. Would that be possible using this same code?
Reply
Report
0
0
Rated 5 out of 5
Biplab Das
Biplab Das
This comment was minimized by the moderator on the site
When 2 Criteria Match then Return Multiple Lookup Values In One Comma Separated Cell
A2=B2 Then Result From Range by "SingleCellExtract" - Please.......
Reply
0
0
abhi
abhi
This comment was minimized by the moderator on the site
Error pops up if increase the array size
Reply
0
0
Js
Js
This comment was minimized by the moderator on the site
VB command breaks when the range is longer than 154 rows (ie :B154)....
Reply
0
0
M Macklam
M Macklam
This comment was minimized by the moderator on the site
thank you, firstly i managed to get this to work without the slow down in performance. I'm using values rather that text so my question is i want to bring back all those with less than say 19 points in a list. Can the single cell Extract work for that or does it have to be a specific value?
Reply
0
0
A Ward
A Ward
This comment was minimized by the moderator on the site
This simply does not work. I was unable to get it to work in my own application, so I copy/pasted the vba and the formula and it returned an error every time
Reply
0
0
K Ray
K Ray
This comment was minimized by the moderator on the site
This works but slows down my excel majorly! Any tips to help speed?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations