✅ Tek Bir Hücrede Birden Çok Arama Değeri Alın (Tekrarlı ve Tekrarsız)

İçindekiler

Bir Hücrede Birden Çok Değer Arama ve Döndürme (Formül Kullanarak)
Bir Hücrede Birden Çok Değer Arama ve Döndürme (VBA Kullanarak)

Excel'de tek bir hücrede (virgül veya boşlukla ayrılmış) birden çok değeri arayabilir ve döndürebilir miyiz?

Bu soruyu birçok meslektaşım ve okuyucum tarafından defalarca soruldu.

Excel'in DÜŞEYARA, DİZİN/KAÇINCI (ve şimdi XLOOKUP) gibi bazı harika arama formülleri vardır, ancak bunların hiçbiri birden çok eşleşen değeri döndürmenin bir yolunu sunmaz. Bunların tümü, ilk eşleşmeyi belirleyerek ve onu döndürerek çalışır.

Bu yüzden Excel'de özel bir işlev (Kullanıcı Tanımlı İşlev olarak da adlandırılır) bulmak için biraz VBA kodlaması yaptım.

Güncelleme: Excel, UNIQUE ve TEXTJOIN gibi dinamik diziler ve harika işlevler yayınladıktan sonra, artık basit bir formül kullanmak ve tüm eşleşen değerleri bir hücrede döndür (bu eğitimde ele alınmıştır).

Bu öğreticide, bunu nasıl yapacağınızı (tüm yeni işlevlerle Excel'in en son sürümünü kullanıyorsanız - Microsoft 365) ve daha eski sürümleri kullanıyorsanız bunu yapmanın bir yolunu göstereceğim ( VBA'yı kullanarak).

O halde başlayalım!

Bir Hücrede Birden Çok Değer Arama ve Döndürme (Formül Kullanarak)

Excel 2016 veya önceki sürümleri kullanıyorsanız, bunu VBA kullanarak nasıl yapacağımı gösterdiğim sonraki bölüme gidin.

Microsoft 365 aboneliği ile Excel'iniz artık önceki sürümlerde olmayan çok daha güçlü işlevlere ve özelliklere (DÜŞEYARA, Dinamik Diziler, BENZERSİZ/FİLTRE işlevleri vb.)

Bu nedenle, Microsoft 365 (önceden Office 365 olarak biliniyordu) kullanıyorsanız, bu bölümde ele alınan yöntemleri kullanarak Excel'de tek bir hücrede birden çok değer arayabilir ve döndürebilirsiniz.

Ve göreceğiniz gibi, bu gerçekten basit bir formül.

Aşağıda A sütununda yer alan kişilerin adlarını ve B sütununda aldıkları eğitimleri aldığım bir veri setim var.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Her kişi için hangi eğitimi tamamladığını öğrenmek istiyorum. D sütununda, benzersiz adların listesi var (A sütunundan) ve her kişinin yaptığı tüm eğitimleri hızla aramak ve çıkarmak ve bunları tek bir kümede (virgülle ayrılmış) almak istiyorum.

Bunu yapacak formül aşağıdadır:

=TEXTJOIN(", ",TRUE,IF(D2=$A$2:$A$20,$B$2:$B$20,"))

Formülü E2 hücresine girdikten sonra, sonuçları istediğiniz tüm hücrelere kopyalayın.

Bu formül nasıl çalışır?

Bu formülün yapısını bozmama izin verin ve her parçanın nasıl bir araya geldiğini açıklayayım, bize sonucu verir.

EĞER formülündeki (D2=$A$2:$A$20) mantıksal sınama, D2 ad hücresinin A2:A20 aralığındakiyle aynı olup olmadığını kontrol eder.

A2:A20 aralığındaki her hücreden geçer ve adın D2 hücresinde aynı olup olmadığını kontrol eder. aynı isim ise DOĞRU, yoksa YANLIŞ döndürür.

Yani formülün bu kısmı size aşağıda gösterildiği gibi bir dizi verecektir:

{DOĞRU;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ}

Yalnızca Bob için eğitim almak istediğimizden (D2 hücresindeki değer), yukarıdaki dizide DOĞRU döndüren hücreler için ilgili tüm eğitimi almamız gerekir.

Bu, IF formülünün [eğer_doğruysa_değer] kısmını eğitimi içeren aralık olarak belirterek kolayca yapılabilir. Bu, D2 hücresindeki ad A2:A20 aralığındaki adla eşleşirse, EĞER formülünün o kişinin aldığı tüm eğitimi döndürmesini sağlar.

Ve dizinin bir YANLIŞ döndürdüğü yerde, [yanlışsa_değer] değerini “” (boş) olarak belirledik, bu nedenle boşluk döndürür.

Formülün EĞER kısmı, diziyi aşağıda gösterildiği gibi döndürür:

{"Excel";"";"";"PowerPoint";"";"";"";"";"";"";";"";"";"";"";"";””;””;””}

Bob'un aldığı eğitimin adlarının olduğu yerde ve adın Bob olmadığı yerlerde boşluklar.

Şimdi tek yapmamız gereken bu eğitim adlarını (virgülle ayırarak) birleştirmek ve bir hücrede geri döndürmek.

Ve bu, yeni TEXTJOIN formülü kullanılarak kolayca yapılabilir (Excel2021-2022'de ve Microsoft 365'te Excel'de mevcuttur)

TEXTJOIN formülü üç argüman alır:

Eğitimin virgül ve boşluk karakteriyle ayrılmasını istediğim için örneğimizde “, ” olan Sınırlayıcı
TRUE - TEXTJOIN formülüne boş hücreleri yok saymasını ve yalnızca boş olmayanları birleştirmesini söyler
Birleştirilmesi gereken metni döndüren If formülü

Microsoft 365'te zaten dinamik dizilere sahip Excel kullanıyorsanız, yukarıdaki formülü girip enter tuşuna basmanız yeterlidir. Excel2021-2022 kullanıyorsanız, formülü girmeniz, Control ve Shift tuşlarını basılı tutmanız ve ardından Enter tuşuna basmanız gerekir.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Tek Bir Hücrede Birden Çok Arama Değeri Alın (tekrar olmadan)

BENZERSİZ formül yalnızca Microsoft 365'te Excel'de mevcut olduğundan, bu yöntemi Excel2021-2022'de kullanamazsınız

Aşağıda gösterildiği gibi, veri kümenizde tekrarlar olması durumunda, tek bir hücrede yalnızca benzersiz değerlerin bir listesini elde etmek için formülü biraz değiştirmeniz gerekir.

Yukarıdaki veri setinde, bazı kişiler birden çok kez eğitim almıştır. Örneğin, Bob ve Stan Excel eğitimini iki kez, Betty ise MS Word eğitimini iki kez almıştır. Ancak bizim sonucumuzda bir eğitim adının tekrar edilmesini istemiyoruz.

Bunu yapmak için aşağıdaki formülü kullanabilirsiniz:

=TEXTJOIN(", ",DOĞRU,EŞSİZ(EĞER(D2=$A$2:$A$20,$B$2:$B$20,"))))

Yukarıdaki formül, küçük bir değişiklikle aynı şekilde çalışır. EĞER formülünü BENZERSİZ işlevinde kullandık, böylece eğer formül sonucunda tekrarlar olması durumunda, BENZERSİZ işlevi onu kaldırır.

Örnek dosyayı indirmek için buraya tıklayın

Bir Hücrede Birden Çok Değer Arama ve Döndürme (VBA Kullanarak)

Excel 2016 veya önceki sürümleri kullanıyorsanız, TEXTJOIN formülüne erişiminiz olmayacaktır. Bu nedenle, tek bir hücrede birden çok eşleşen değeri aramanın ve almanın en iyi yolu, VBA kullanarak oluşturabileceğiniz özel bir formül kullanmaktır.

Tek bir hücrede birden çok arama değeri elde etmek için VBA'da bir sütundaki her hücreyi kontrol eden ve arama değeri bulunursa sonuca ekleyen bir işlev (DÜŞEYARA işlevine benzer) oluşturmamız gerekir.

İşte bunu yapabilen VBA kodu:

'Kod by Sumit Bansal (https://trumpexcel.com) Function SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Sol(Sonuç, Len(Sonuç) - 1) Bitiş İşlevi

Bu Kodu Nereye Koymalı?

Bir çalışma kitabı açın ve Alt + F11'e tıklayın (bu, VBA Düzenleyici penceresini açar).
Bu VBA Düzenleyici penceresinde, solda bir proje gezgini vardır (tüm çalışma kitaplarının ve çalışma sayfalarının listelendiği yer). Çalışma kitabında bu kodun çalışmasını istediğiniz herhangi bir nesneye sağ tıklayın ve Ekle -> Modül'e gidin.
Modül penceresinde (sağda görünecek), yukarıdaki kodu kopyalayıp yapıştırın.
Artık hazırsınız. Çalışma kitabındaki herhangi bir hücreye gidin ve yazın =TekHücre Özü ve gerekli girdi bağımsız değişkenlerini (yani LookupValue, LookupRange, ColumnNumber) takın.

Bu formül nasıl çalışır?

Bu işlev DÜŞEYARA işlevine benzer şekilde çalışır.

Girdi olarak 3 argüman alır:

1. arama değeri - Bir dizi hücrede aramamız gereken bir dize.
2. Arama Aralığı - Verileri almamız gereken bir dizi hücre (bu durumda $B3:$C18).
3. Sütun Numarası - Eşleşen değerin döndürüleceği tablonun/dizinin sütun numarasıdır (bu durumda 2).

Bu formülü kullandığınızda, arama aralığındaki en soldaki sütundaki her hücreyi kontrol eder. ve bir eşleşme bulduğunda, formülü kullandığınız hücrede sonuca ekler.

Unutma: Bu formülü yeniden kullanmak için çalışma kitabını makro etkin bir çalışma kitabı (.xlsm veya .xls) olarak kaydedin. Ayrıca, bu işlev tüm çalışma kitaplarında değil, yalnızca bu çalışma kitabında kullanılabilir.

Örnek dosyayı indirmek için buraya tıklayın

Excel'de VBA ile sıkıcı tekrarlayan görevleri nasıl otomatikleştireceğinizi öğrenin. Katılmak Excel VBA Kursu

Tek Bir Hücrede Birden Çok Arama Değeri Alın (tekrar olmadan)

Verilerde tekrarlar olma ihtimali vardır.

Yukarıda kullanılan kodu kullanırsanız sonuç olarak size tekrarlar da verecektir.

Tekrarların olmadığı yerde sonuç almak istiyorsanız, kodu biraz değiştirmeniz gerekir.

İşte size tek bir hücrede herhangi bir tekrar olmadan birden çok arama değeri verecek VBA kodu.

'Code by Sumit Bansal (https://trumpexcel.com) Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String i = 1 To LookupRange.Columns(1).Cells .Count ise LookupRange.Cells(i, 1) = Lookupvalue O zaman J = 1 To i - 1 Eğer LookupRange.Cells(J, 1) = Lookupvalue O zaman LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Sonra Git Sonu Atla Eğer Son ise Sonraki ise J Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Sol(Sonuç, Len(Sonuç) - 1) Son İşlev

Bu kodu VB Editörüne yerleştirdikten sonra (yukarıdaki öğreticide gösterildiği gibi), ÇokluAramaNoRept işlev.

İşte bununla elde edeceğiniz sonucun bir anlık görüntüsü ÇokluAramaNoRept işlev.

Örnek dosyayı indirmek için buraya tıklayın

Bu öğreticide, Excel'de bir hücrede birden çok arama değeri bulmak ve döndürmek için formüllerin ve VBA'nın Excel'de nasıl kullanılacağını ele aldım.

Microsoft 365 aboneliğinde Excel kullanıyorsanız, önceki sürümleri kullanıyorsanız ve TEXTJOIN gibi işlevlere erişiminiz yoksa basit bir formülle kolayca yapılabilirken, yine de VBA kullanarak bunu yapabilirsiniz. kendi özel işlevi.