Birden fazla arama değeri aralığını, tek bir hücrede virgülle ayrılmış şekilde nasıl döndürebilirim?
Excel’de veriyle çalışırken, arama sonucunda birden fazla eşleşen değeri bulmanız ve bunları tek bir hücrede virgül, tire gibi ayırıcılarla görüntülemeniz gerekebilir. "VLOOKUP" ve "DİZİN-EŞLEŞTİR" gibi Excel’in yerleşik arama işlevleri tek bir eşleşmeyi bulmak için mükemmeldir; ancak birden fazla sonucu tek bir hücrede birleştirmek daha gelişmiş bir yaklaşım gerektirir. Bu makalede, Excel’de bu görevi yerine getirmenize yardımcı olacak bazı etkili yöntemler tanıtılacaktır.
Bir hücrede birden fazla Arama değeri aralığı değerini virgülle ayırarak döndürme
TEXTJOIN ve FİLTRE işlevleriyle bir hücrede virgülle ayrılmış şekilde birden fazla Arama değeri aralığı değerini döndürme
Excel 365 veya Excel 2021 ve sonraki sürümlere erişiminiz varsa, dinamik dizi formüllerini kullanmak en kolay yoldur.
Sonucu almak için aşağıdaki formülü boş bir hücreye girin ve ardından "Enter" tuşuna basarak sonucu görün.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Ardından, formülü diğer hücrelere taşımak için doldurma tutamacını aşağı doğru sürükleyin; ekran görüntüsüne bakın:
- FİLTRE(B2:B15; A2:A15=D2): A sütunundaki değerler D2 hücresindeki değere eşit olduğunda, B sütunundaki ilgili tüm değerleri getirir.
- METİN_BİRLEŞTİR(", "; DOĞRU; …): Elde edilen değerleri virgülle ayırarak tek bir dizeye birleştirir.
Kutools for Excel ile bir hücrede virgülle ayrılmış şekilde birden fazla Arama değeri aralığı değerini döndürme
"Kutools for Excel", Excel’de karmaşık görevleri basitleştirmek üzere tasarlanmış güçlü bir eklentidir. Gelişmiş "Çoklu Arama" özelliği sayesinde, karmaşık formüller veya VBA kodlamasına gerek kalmadan tüm eşleşen değerleri belirttiğiniz bir sınırlayıcıyla tek bir hücreye kolayca çıkarabilirsiniz.
Kutools for Excel yüklendikten sonra şu adımları izleyin:
“Kutools” > “Süper ARA” > “Birçoklu arama (birden fazla sonuç döndürür)” yolunu izleyerek iletişim kutusunu açın. İletişim kutusunda aşağıdaki işlemleri gerçekleştirin:
- Metin kutularında sırasıyla "Liste Yerleştirme Alanı ve Arama değeri aralığı" seçeneğini işaretleyin;
- Kullanmak istediğiniz tablo aralığını seçin;
- "Anahtar Sütun" ve "Dönüş sütunu" açılır menülerinden sırasıyla Anahtar Sütun ve Dönüş sütunu seçeneklerini belirleyin;
- Son olarak, “Tamam” düğmesine tıklayın.
Sonuç:
Aynı değere sahip tüm ilgili hücreler virgülle ayrılarak tek bir hücrede birleştirilmiştir; ekran görüntüsüne bakın:
Kullanıcı Tanımlı İşlev ile bir hücrede virgülle ayrılmış şekilde birden fazla Arama değeri aralığı değerini döndürme
Excel 365 veya Excel 2021 sürümüne sahip değilseniz, bu bölümde birden fazla arama değeri aralığını virgülle ayırarak tek bir hücrede döndüren bir Kullanıcı Tanımlı İşlev (UDF) oluşturup kullanmanız için adım adım rehberlik sunulur.
1. "Microsoft Visual Basic for Applications" penceresini açmak için ALT + F11 tuşlarına basılı tutun.
2. "Ekle" > "Modül" seçeneğine tıklayıp aşağıdaki kodu Modül penceresine yapıştırın.
VBA kodu: Birden fazla Arama değeri aralığı değerini tek bir virgülle ayrılmış hücreye döndürme
Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
'Updateby Extendoffice
Dim result As String
Dim i As Long
result = ""
For i = 1 To lookupRange.Rows.Count
If lookupRange.Cells(i, 1).Value = lookupValue Then
result = result & returnRange.Cells(i, 1).Value & delimiter
End If
Next i
If Len(result) > 0 Then
JoinLookupValues = Left(result, Len(result) - Len(delimiter))
Else
JoinLookupValues = ""
End If
End Function
3. Ardından bu kodu kaydedin ve Modül penceresini kapatın. Çalışma sayfanıza dönüp sonucu döndürmek istediğiniz boş bir hücreye aşağıdaki formülü girin. Sonra, formülü diğer hücrelere kopyalamak için doldurma tutamacını aşağı doğru sürükleyin; ekran görüntüsüne bakın:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
Sonuç olarak, birden fazla Arama değeri aralığındaki sonuçları virgülle ayırarak tek bir hücrede döndürmek, Excel sürümünüze ve ihtiyaçlarınıza göre çeşitli yöntemlerle verimli bir şekilde gerçekleştirilebilir. Gereksinimlerinize en uygun yöntemi seçerek veri analizi süreçlerinizi hızlandırabilir ve üretkenliğinizi artırabilirsiniz. Daha fazla Excel ipucu ve püf noktası keşfetmek istiyorsanız, web sitemizde binlerce eğitim içeriği mevcuttur.
İlgili Makaleler:
- Yinelenmeyen birden fazla değeri döndüren VLOOKUP
- Excel’de veriyle çalışırken, belirli bir arama kriterine ilişkin birden fazla eşleşen değeri döndürmeniz gerekebilir. Ancak varsayılan VLOOKUP işlevi yalnızca tek bir sonuç getirir. Birden fazla eşleşme varsa ve bunları yinelenmeden tek bir hücrede görüntülemek istiyorsanız, bu görevi yerine getirmek için alternatif yöntemlerden yararlanabilirsiniz.
- Tek veya çoklu kritere göre birden fazla eşleşen değeri döndürme
- Belirli bir değeri arayıp eşleşen öğeyi döndürmek, çoğu kullanıcı için VLOOKUP işleviyle oldukça kolaydır. Peki ya aşağıdaki ekran görüntüsünde gösterildiği gibi, tek bir kritere mi yoksa birden fazla kritere mi dayalı olarak birden fazla eşleşen değeri döndürmeye çalıştınız? Bu makalede, Excel’de bu karmaşık görevi başarıyla yerine getirmenize yardımcı olacak bazı formüller paylaşacağım.
- VLOOKUP ile son eşleşen değeri döndürme
- Birden çok kez tekrarlanan bir öğe listeniz varsa ve yalnızca belirttiğiniz veriyle eşleşen son değeri bulmak istiyorsanız ne yapmalısınız?
- İki değer arasındaki eşleşen verileri VLOOKUP ile döndürme
- Excel’de normal VLOOKUP işlevini uygulayarak belirli bir veriye göre karşılık gelen değeri alabiliriz. Ancak bazen aşağıdaki ekran görüntüsünde gösterildiği gibi iki değer arasındaki eşleşen değeri döndürmek isteyebilirsiniz. Excel’de bu görevi nasıl halledebilirsiniz?
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