Excel’de Birden Fazla Kritere Göre Eşleşen Birden Çok Değeri Döndürme (Tam Kılavuz)
Excel kullanıcıları, genellikle birden fazla kriteri aynı anda karşılayan değerleri ayıklamak ve tüm eşleşen sonuçları bir sütun, satır ya da tek bir hücre içinde görüntülemek zorunda kaldığı senaryolarla sıkça karşılaşır. Bu kılavuz, Excel’in tüm sürümleri için geçerli yöntemleri ve Excel 365 ile Excel 2021’de tanıtılan yeni FİLTRE işlevini ayrıntılı şekilde incelemektedir.
Birden fazla kritere göre eşleşen birden çok değeri tek bir hücrede döndürme
Excel’de birden fazla kritere göre eşleşen birden çok değeri tek bir hücrede toplamak, oldukça yaygın bir zorluktur. İşte bu sorunu çözmek için iki verimli yöntem.
Yöntem 1: METİN.BİRLEŞTİR işlevini kullanma (Excel365 / 2021,2019)
Tüm eşleşen değerleri bir hücrede ayırıcılarla bir araya getirmek için METİN.BİRLEŞTİR işlevi tam da size göre!
Aşağıdaki formülü boş bir hücreye girin veya kopyalayın, ardından Enter tuşuna basın (Excel 2021 ve Excel 365) veya Excel 2019’te Ctrl + Shift + Enter tuşlarına basın:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) formülü, her satırın “Satıcı E2’ye eşit” ve “Ay F2’ye eşit” koşullarını karşılayıp karşılamadığını denetler. Her iki koşul da sağlanıyorsa sonuç 1; aksi takdirde 0 olur. Yıldız işareti (*), her iki koşulun da doğru olmasını gerektirdiğini gösterir.
- IF(…, $C$2:$C$21, "") satır eşleşirse ürün adını döndürür; aksi takdirde boş bir değer verir.
- METİN.BİRLEŞTİR(", ", DOĞRU, …) tüm boş olmayan ürün adlarını ", " ile ayırarak tek bir hücrede birleştirir.
Yöntem 2: Kutools for Excel kullanma
Kutools for Excel, karmaşık formüllere gerek kalmadan birden fazla kritere göre eşleşen değerleri hızlıca almanızı ve bunları tek bir hücrede kolayca birleştirmenizi sağlayan güçlü ama basit bir çözüm sunar.
Kutools for Excel’i yükledikten sonra şu adımları izleyin:
- Kriterlere göre tüm ilgili değerleri almak için istediğiniz Veri Aralığını seçin.
- Ardından, Kutools > Birleştir ve Ayır > Gelişmiş Satırları Birleştir seçeneğine tıklayın, ekran görüntüsüne bakın:
- Gelişmiş Satırları Birleştir iletişim kutusunda aşağıdaki seçenekleri yapılandırın:
- Eşleştirme kriterlerinizi içeren sütun başlıklarını seçin (örneğin, Satıcı ve Ay). Seçtiğiniz her sütunu birincil anahtar olarak tanımlamak için **Birincil Anahtar**’a tıklayın.
- Birleştirilecek “Birleştirilmiş Sonuçlar” sütununun başlığına tıklayın (örneğin, Ürün). “Birleştir” bölümünden tercih ettiğiniz ayırıcıyı seçin (örneğin, virgül, boşluk veya özel bir ayırıcı).
- Son olarak, Tamam düğmesine tıklayın.
Sonuç: Kutools, eşleşen tüm değerleri her eşsiz kriter kombinasyonu için otomatik olarak tek bir hücrede birleştirir.
Birden fazla kritere göre eşleşen birden çok değeri bir sütunda döndürme
Excel, bir veri kümesinden birden fazla koşula göre eşleşen kayıtları çıkarmak ve sonuçları dikey sütun biçiminde görüntülemek gerektiğinde çeşitli güçlü çözümler sunar.
Yöntem 1: Bir dizi formülü kullanma (tüm sürümler için)
Sonuçları dikey olarak bir sütunda döndürmek için aşağıdaki dizi formülünü kullanabilirsiniz:
1. Aşağıdaki formülü boş bir hücreye girin veya kopyalayın:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. İlk eşleşen sonucu almak için Ctrl + Shift + Enter tuşlarına basın, ardından ilk formül hücresini seçip dolgu tutamacını boş bir hücre görünene kadar aşağı doğru sürükleyin. Böylece aşağıdaki ekran görüntüsünde gösterildiği gibi tüm eşleşen değerler döndürülmüş olur:
- $A$2:$A$18=$E$2: Satıcının, E2 hücresindeki değere eşit olup olmadığını denetler.
- $B$2:$B$18=$F$2: Ayın, F2 hücresindeki değere eşit olup olmadığını denetler.
- * mantıksal VE işlecidir (her iki koşulun da doğru olması gerekir).
- ROW($C$2:$C$18)-ROW($C$2)+1: Her ürün için göreli satır numarası oluşturur.
- SMALL(…, ROW(1:1)): Formül aşağı doğru sürüklendikçe eşleşen satırlar arasından n'inci en küçük değeri getirir.
- INDEX(…): Eşleşen satırdaki ürünü döndürür.
- IFERROR(…, ""): Eşleşme kalmadığında hücreyi boş bırakır.
Yöntem 2: FİLTRE işlevini kullanma (Excel365 / 2021)
Excel 365 veya Excel 2021 kullanıyorsanız, FİLTRE işlevi; sadeliği, açıklığı ve karmaşık dizi formüllerine gerek kalmadan sonuçları dinamik olarak doldurma yeteneğiyle, birden fazla kritere göre birden çok sonucu döndürmek için mükemmel bir seçimdir.
Aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve Enter tuşuna basın; böylece tüm eşleşen kayıtlar çoklu kriterlere göre döndürülür.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FİLTRE(…), her iki koşulun da sağlandığı C2:C18 aralığındaki tüm değerleri döndürür.
- (A2:A18=E2)*(B2:B18=F2): Satıcı ve ay eşleşmelerini kontrol eden mantıksal dizidir.
- "Eşleşme yok": Değer bulunamadığında görüntülenecek isteğe bağlı mesajdır.
Birden fazla kritere göre eşleşen birden çok değeri bir satırda döndürme
Excel kullanıcıları genellikle bir veri kümesinden birkaç koşulu sağlayan çoklu değerleri çıkarmak ve bunları yatay olarak (bir satırda) görüntülemek ister. Bu, dikey alanda kısıtlı olan dinamik raporlar, pano görünümleri veya özet tablolar oluşturmak için yararlıdır. Bu bölümde, iki güçlü yöntemi inceleyeceğiz.
Yöntem 1: Bir dizi formülü kullanma (tüm sürümler için)
Geleneksel dizi formülleri, INDEX, SMALL, IF ve COLUMN işlevlerini kullanarak birden fazla eşleşen değeri çıkarmayı sağlar. Dikey (sütun tabanlı) çıkarımdan farklı olarak, sonuçların bir satırda görüntülenmesi için formülü buna göre uyarlarız.
1. Aşağıdaki formülü boş bir hücreye kopyalayın veya girin:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. İlk eşleşen sonucu almak için Ctrl + Shift + Enter tuşlarına basın, ardından ilk formül hücresini seçip formülü sütunlar boyunca sağa doğru sürükleyerek tüm sonuçları alın.
- $A$2:$A$18=$E$2: Satıcının eşleşip eşleşmediğini kontrol eder.
- $B$2:$B$18=$F$2: Ayın eşleşip eşleşmediğini kontrol eder.
- *: Mantıksal VE — her iki koşulun da doğru olması gerekir.
- ROW($C$2:$C$18)-ROW($C$2)+1: Göreli satır numaralarını oluşturur.
- COLUMN(A1): Formül sağa ne kadar sürüklenirse, o kadar çok eşleşme döndürür.
- IFERROR(…): Eşleşmeler tükendiğinde hata oluşmasını engeller.
Yöntem 2: Filtre işlevini kullanma (Excel 365 / 2021)
Aşağıdaki formülü boş bir hücreye kopyalayın veya girin ve Enter tuşuna basın; böylece tüm eşleşen değerler çıkarılır ve tek bir satırda görüntülenir. Ekran görüntüsüne göz atın:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FİLTRE(…): C sütunundan, iki koşula göre eşleşen değerleri getirir.
- (A2:A18=E2)*(B2:B18=F2): Her iki koşulun da sağlanması gerekir.
- DEVRİK.DÖNÜŞÜM(…): FİLTRE işlevi tarafından döndürülen dikey diziyi yatay diziye dönüştürür.
🔚 Sonuç
Excel’de çoklu kriterlere göre birden fazla eşleşen değeri almak, sonuçları bir sütunda, bir satırda veya tek bir hücre içinde mi göstermek istediğinize bağlı olarak birkaç farklı yolla mümkündür.
- Excel 365 veya Excel 2021 kullananlar için FİLTRE işlevi, karmaşıklığı en aza indiren modern, dinamik ve zarif bir çözümdür.
- Daha eski sürümleri kullananlar için dizi formülleri güçlü araç olmaya devam eder; ancak biraz daha fazla kurulum ve özen ister.
- Ayrıca sonuçları tek bir hücrede birleştirmek istiyorsanız veya kod yazmadan bir çözüm tercih ediyorsanız, METİN.BİRLEŞTİR işlevi ya da Kutools for Excel gibi üçüncü taraf araçlar süreci büyük ölçüde kolaylaştırabilir.
Excel sürümünüzle ve tercih ettiğiniz düzene en iyi şekilde uyumlu yöntemi seçin; böylece çok kriterli aramaları verimli ve doğru bir şekilde gerçekleştirmeye hazır olursunuz. Daha fazla Excel ipucu ve püf noktası keşfetmek istiyorsanız,web sitemizde Excel’i ustalıkla öğrenmenize yardımcı olacak binlerce eğitim sunuyoruz.
İlgili diğer makaleler:
- Birden Çok Arama değeri aralığı’i Tek Bir Virgülle Ayrılmış Hücrede Döndürme
- Excel’de VLOOKUP işleviyle tablo hücrelerinden yalnızca ilk eşleşen değeri döndürebiliriz. Ancak bazen tüm eşleşen değerleri alıp bunları virgül, tire gibi belirli bir ayırıcıyla tek bir hücrede birleştirmemiz gerekir (aşağıdaki ekran görüntüsünde gösterildiği gibi). Excel’de birden çok arama değeri aralığını, tek bir hücrede virgülle ayrılmış şekilde nasıl alabilir ve döndürebiliriz?
- Google Sayfasında VLOOKUP Kullanarak Tüm Eşleşen Değerleri Aynı Anda Döndürme
- Google Sayfalar'da normal VLOOKUP işlevi, belirtilen bir veriye göre ilk eşleşen değeri bulmanıza ve döndürmenize yardımcı olabilir. Ancak bazen aşağıdaki ekran görüntüsünde gösterildiği gibi tüm eşleşen değerleri döndürmeniz gerekebilir. Google Sayfalar'da bu görevi yerine getirmek için etkili ve kolay yöntemleriniz var mı?
- Açılır Liste'den VLOOKUP Kullanarak Birden Çok Değer Döndürme
- Excel’de, açılır listeden bir öğe seçtiğinizde tüm ilgili değerlerin aynı anda görüntülenmesini sağlamak için VLOOKUP ile nasıl birden çok ilgili değer döndürebilirsiniz? Bu makalede size adım adım çözümü göstereceğim.
- Excel’de VLOOKUP Kullanarak Birden Çok Değeri Dikey Olarak Döndürme
- Normalde ilk eşleşen değeri almak için VLOOKUP işlevini kullanabilirsiniz. Ancak bazen belirli bir kritere uyan tüm kayıtları döndürmeniz gerekebilir. Bu makalede, VLOOKUP ile tüm eşleşen değerleri dikey, yatay veya tek bir hücrede nasıl getirebileceğinizi göstereceğim.
- Excel’de İki Değer Arasındaki Eşleşen Verileri VLOOKUP ile Döndürme
- Excel’de belirli bir veriye göre ilgili değeri almak için standart VLOOKUP işlevini kullanabilirsiniz. Ancak bazen aşağıdaki ekran görüntüsünde gösterildiği gibi, iki değer aralığı arasında eşleşen değeri döndürmeniz gerekebilir. Excel’de bu tür bir görevi nasıl gerçekleştirebilirsiniz?
En İyi Office Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi üst seviyeye taşıyın ve verimlilikten önce hiç yaşanmamış bir deneyim yaşayın.Kutools for Excel, verimliliğ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 arayüz getirir ve 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.
- Birden fazla belgeyi yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açın ve oluşturun.
- Günlük verimliliğ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 için eklentileri ile Office Tab Pro’yu içerir ve 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— dakikalar içinde hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı verimlilik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi değer— tek tek eklenti satın almaya göre tasarruf sağlar