Excel'de birden fazla kriter bazında birden çok eşleşen değeri döndürme (Tam rehber)
Excel kullanıcıları, genellikle aynı anda birkaç kriteri karşılayan birden fazla değeri çıkarmak ve tüm eşleşen sonuçları bir sütunda, bir satırda veya tek bir hücrede birleştirilmiş şekilde sunmak zorunda kalır. Bu rehber, tüm Excel sürümlerindeki yöntemleri ve Excel 365 ile 2021'de bulunan daha yeni FILTER fonksiyonunu ele almaktadır.
Tek bir hücrede birden fazla kritere göre birden çok eşleşen değer döndürme
Excel'de, tek bir hücre içinde birden fazla kritere göre birden çok eşleşen değeri çıkarmak yaygın bir zorluktur. Burada iki etkili yöntemi keşfedelim.
Yöntem 1: Textjoin fonksiyonu kullanarak (Excel365 / 2021, 2019)
Tüm eşleşen değerleri ayraçlarla tek bir hücreye almak için TEXTJOIN fonksiyonu size yardımcı olabilir.
Aşağıdaki formülü boş bir hücreye girin veya kopyalayın, ardından Sonuç için Enter tuşuna basın (Excel 2021 ve Excel 365) veya Excel 2019'da 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), her satırın hem "Satıcı E2'ye eşit" hem de "Ay F2'ye eşit" koşullarını karşılayıp karşılamadığını kontrol eder. Her iki koşul da sağlandığında, sonuç 1; aksi takdirde 0'dır. Yıldız işareti (*) her iki koşulun da doğru olması gerektiğini gösterir.
- IF(..., $C$2:$C$21, "") satır eşleşirse ürün adını döndürür; aksi takdirde boş bırakır.
- TEXTJOIN(", ", TRUE, ...) tüm boş olmayan ürün adlarını ", " ile birleştirerek tek bir hücreye yerleştirir.
Yöntem 2: Kutools for Excel'i kullanarak
Kutools for Excel, karmaşık formüller olmadan birden çok eşleşmeyi hızlı bir şekilde almanızı ve tek bir hücreye birleştirmenizi sağlayan güçlü ancak basit bir çözüm sunar.
Kutools for Excel'i yükledikten sonra lütfen şu adımları izleyin:
- Kriterlere göre tüm karşılık gelen değerleri almak istediğiniz veri aralığını seçin.
- Ardından, Kutools > Birleştir ve Böl > Gelişmiş Satırları Birleştir'e tıklayın, ekran görüntüsüne bakınız:
- Gelişmiş Satırları Birleştir iletişim kutusunda lütfen aşağıdaki seçenekleri yapılandırın:
- Eşleşme kriterlerini içeren sütun başlıklarını seçin (örn., Satıcı ve Ay). Her seçili sütun için Anahtar olarak belirlemek üzere Arama koşullarınızı tanımlayın.
- Birleştirmek istediğiniz sonuçların bulunduğu sütun başlığını tıklayın (örn., Ürün). Birleştir bölümünden tercih ettiğiniz ayırıcıyı seçin (örn., virgül, boşluk veya özel ayırıcı).
- Son olarak, Tamam düğmesine tıklayın.
Sonuç: Kutools will instantly merge all matching values into a single cell per unique criteria combination.
Bir sütunda birden fazla kritere göre birden çok eşleşen değer döndürme
Bir veri setinden birkaç koşula dayalı olarak birden çok eşleşen kaydı çıkarmak ve sonuçları dikey bir sütun formatında görüntülemek istediğinizde, Excel güçlü birkaç çözüm sunar.
Yöntem 1: Dizi formülü kullanma (tüm sürümler için)
Aşağıdaki dizi formülünü kullanarak sonuçları dikey bir sütunda döndürebilirsiniz:
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), ROW(1:1))), "")2. İlk eşleşen sonucu almak için Ctrl + Shift + Enter tuşlarına basın ve ardından ilk formül hücresini seçip dolgu tutamacını aşağıya doğru sürükleyerek boş hücreye kadar tüm eşleşen değerleri alın, aşağıdaki ekran görüntüsünde gösterildiği gibi.
- $A$2:$A$18=$E$2: Satıcının E2 hücresindeki değerle eşleşip eşleşmediğini kontrol eder.
- $B$2:$B$18=$F$2: Ay'ın F2 hücresindeki değerle eşleşip eşleşmediğini kontrol eder.
- * mantıksal VE operatörüdür (her iki koşul da doğru olmalıdır).
- ROW($C$2:$C$18)-ROW($C$2)+1: Her ürün için nispi satır numarası oluşturur.
- SMALL(..., ROW(1:1)): Formül aşağıya doğru sürüklenirken n-inci en küçük eşleşen satırı getirir.
- INDEX(...): Eşleşen satırdan ürünü döndürür.
- IFERROR(..., ""): Daha fazla eşleşme olmadığında boş hücre döndürür.
Yöntem 2: Filter fonksiyonu kullanma (Excel365 / 2021)
Excel 365 veya Excel 2021 kullanıyorsanız, FILTER fonksiyonu, karmaşık dizi formüllerine gerek duymadan dinamik olarak sonuçları sıralama yeteneği, basitliği ve açıklığı sayesinde birden çok kritere dayalı olarak birden çok sonucu döndürmek için harika bir seçenektir.
Aşağıdaki formülü boş bir hücreye kopyalayın veya girin, ardından Enter tuşuna basın, tüm eşleşen kayıtlar birden fazla kritere dayalı olarak döndürülür.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) her iki koşul da sağlandığında C2:C18'deki tüm değerleri döndürür.
- (A2:A18=E2)*(B2:B18=F2): Eşleşen satıcı ve ay için mantıksal bir dizi oluşturur.
- "No match": İsteğe bağlı olarak hiçbir değer bulunamazsa gösterilecek mesaj.
Bir satırda birden fazla kritere göre birden çok eşleşen değer döndürme
Excel kullanıcıları, genellikle bir veri setinden birkaç koşulu karşılayan birden fazla değeri çıkarmak ve bunları yatay olarak (bir satırda) göstermek isterler. Bu, dikey alanın sınırlı olduğu dinamik raporlar, panolar veya özet tablolar oluştururken kullanışlıdır. Bu bölümde, iki güçlü yöntemi inceleyeceğiz.
Yöntem 1: Dizi formülü kullanma (tüm sürümler için)
Geleneksel dizi formülleri, INDEX, SMALL, IF ve COLUMN fonksiyonlarını kullanarak birden fazla eşleşen değeri çıkarmaya olanak tanır. Dikey çıkarma (sütun bazlı) yerine, formülü bir satırda sonuç döndürmek için ayarları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 ve ardından ilk formül hücresini seçip formülü sağa doğru sütunlar boyunca 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şul da doğru olmalıdır.
- ROW($C$2:$C$18)-ROW($C$2)+1: Nispi satır numaraları oluşturur.
- COLUMN(A1): Formül ne kadar sağa sürüklendiyse buna bağlı olarak hangi eşleşmenin döneceğini ayarlar.
- IFERROR(...): Eşleşmeler bittiğinde hataları önler.
Yöntem 2: Filter fonksiyonu kullanma (Excel365 / 2021)
Aşağıdaki formülü boş bir hücreye kopyalayın veya girin, ardından Enter tuşuna basın, tüm eşleşen değerler çıkarılır ve bir satırda konumlandırılır. Ekran görüntüsüne bakınız:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): İki koşula göre C sütunundaki eşleşen değerleri getirir.
- (A2:A18=E2)*(B2:B18=F2): Her iki koşul da doğru olmalıdır.
- TRANSPOSE(...): FILTER tarafından döndürülen dikey diziyi yatay diziye dönüştürür.
🔚 Sonuç
Excel'de birden fazla kritere göre birden çok eşleşen değer çıkarmak, sonuçları bir sütunda, bir satırda veya tek bir hücrede mi göstermek istediğinize bağlı olarak çeşitli yollarla gerçekleştirilebilir.
- Excel 365 veya Excel 2021 kullanan kullanıcılar için, FILTER fonksiyonu karmaşıklığı en aza indiren modern, dinamik ve zarif bir çözümdür.
- Eski sürümleri kullananlar için, dizi formülleri hala güçlü araçlardır, ancak biraz daha fazla düzenleme ve dikkat gerektirirler.
- Ayrıca, sonuçları tek bir hücreye birleştirmek veya kod içermeyen bir çözüm tercih ediyorsanız, TEXTJOIN fonksiyonu veya Kutools for Excel gibi üçüncü parti araçlar süreci önemli ölçüde hızlandırabilir.
Excel sürümünüz ve tercih ettiğiniz düzenin en uygun yöntemini seçin ve çoklu kriterli aramaları verimli ve doğru bir şekilde ele almak için iyi donanımlı olacaksınız. Eğer daha fazla Excel ipucu ve püf noktasını keşfetmeye ilgi duyuyorsanız, web sitemiz binlerce öğretici sunarak Excel'i ustalaşmanıza yardımcı olur.
Daha fazla ilgili makale:
- Virgülle Ayrılmış Tek Bir Hücrede Birden Çok Arama Değerini Döndürme
- Excel'de, VLOOKUP fonksiyonunu uygulayarak bir tablodaki ilk eşleşen değeri döndürebiliriz, ancak bazen tüm eşleşen değerleri çıkarmamız ve bunları belirli bir ayraçla (örneğin virgül, tire vb.) tek bir hücrede birleştirmemiz gerekebilir, aşağıdaki ekran görüntüsünde gösterildiği gibi. Excel'de birden çok arama değerini nasıl alıp tek bir virgülle ayrılmış hücrede döndürebiliriz?
- Google Sheets'te Aynı Anda Birden Çok Eşleşen Değeri Döndürme
- Google Sheets'teki normal Vlookup fonksiyonu, verilen bir veriye dayalı olarak 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ürmek isteyebilirsiniz. Bu görevi Google Sheets'te çözmek için iyi ve kolay bir yöntem var mı?
- Açılır Liste Üzerinden Birden Çok Değer Döndürme
- Excel'de, bir açılır listeden birden çok karşılık gelen değeri nasıl vlookup yapabilir ve döndürebilirsiniz? Bu, bir açılır listeden bir öğe seçtiğinizde, tüm ilgili değerlerin bir kerede gösterilmesi anlamına gelir, aşağıdaki ekran görüntüsünde gösterildiği gibi. Bu makalede, adım adım çözümü anlatacağım.
- Excel'de Birden Çok Eşleşen Değeri Dikey Olarak Döndürme
- Normalde, ilk karşılık gelen değeri almak için Vlookup fonksiyonunu kullanabilirsiniz, ancak bazen belirli bir kritere göre tüm eşleşen kayıtları döndürmek istersiniz. Bu makalede, birden çok eşleşen değeri dikey, yatay veya tek bir hücreye nasıl döndürebileceğimizi tartışacağım.
- Excel'de İki Değer Arasındaki Eşleşen Veriyi Döndürme
- Excel'de, normal Vlookup fonksiyonunu belirli bir veriye dayalı olarak karşılık gelen değeri almak için uygulayabiliriz. 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 isteriz. Bu görevi Excel'de nasıl gerçekleştirebiliriz?
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin