Excel'de birden fazla kritere göre birden fazla eşleşen değeri döndürme (Tam rehber)
Excel kullanıcıları, genellikle birkaç kriteri aynı anda karşılayan birden fazla değeri çıkarmak ve tüm eşleşen sonuçları bir sütunda, satırda veya tek bir hücrede birleştirilmiş şekilde sunmak zorunda kalır. Bu rehber, tüm Excel sürümleri için yöntemleri ve Excel 365 ile 2021'de bulunan yeni FILTER fonksiyonunu ele almaktadır.
Tek bir hücrede birden fazla kritere göre birden fazla eşleşen değeri döndürme
Excel'de, tek bir hücre içinde birden fazla kritere göre birden fazla eşleşen değeri çıkarmak yaygın bir zorluktur. Burada iki etkili yöntemi inceleyelim.
Yöntem 1: Textjoin fonksiyonunu kullanma (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 “Satıcı E2'ye eşit” ve “Ay F2'ye eşit” olmak üzere iki koşulu da karşılayıp karşılamadığını kontrol eder. Her iki koşul da karşılanıyorsa sonuç 1'dir; aksi takdirde 0'dır. Yıldız işareti * her iki koşulun da doğru olması gerektiğini belirtir.
- IF(..., $C$2:$C$21, "") satır eşleşiyorsa ürün adını döndürür; aksi halde boş döner.
- TEXTJOIN(", ", TRUE, ...) tüm boş olmayan ürün adlarını bir hücrede birleştirir ve bunları ", " ile ayırır.
Yöntem 2: Kutools for Excel'i kullanma
Kutools for Excel, karmaşık formüller olmadan birden fazla eşleşmeyi hızlıca almanıza ve tek bir hücrede birleştirmenize olanak tanıyan 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 ilgili 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:
- 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 (örneğin, Satıcı ve Ay). Her seçili sütun için Anahtar olarak belirlemek için Ana Anahtar'a tıklayın.
- Birleştirilmiş sonuçların görüneceği sütun başlığını tıklayın (örneğin, Ürün). Birleştirme bölümünden tercih ettiğiniz ayraç seçin (örneğin, virgül, boşluk veya özel ayırıcı).
- Son olarak, Tamam düğmesine tıklayın.
Sonuç: Kutools, tüm eşleşen değerleri her benzersiz kriter kombinasyonu için tek bir hücrede birleştirir.
Bir sütunda birden fazla kritere göre birden fazla eşleşen değeri döndürme
Bir veri setinden birkaç koşula dayalı olarak birden fazla eşleşen kaydı çıkarmak ve sonuçları dikey bir sütun formatında göstermek 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 olarak 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 formülü aşağıya doğru sürükleyerek boş hücreye kadar doldurun, şimdi tüm eşleşen değerler aşağıdaki ekran görüntüsünde gösterildiği gibi döndürülmüştür:
- $A$2:$A$18=$E$2: Satıcının E2 hücresindeki değere eşleşip eşleşmediğini kontrol eder.
- $B$2:$B$18=$F$2: Ay'ın F2 hücresindeki değere eşleşip eşleşmediğini kontrol eder.
- *: Mantıksal VE operatö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 göreceli bir sıra numarası oluşturur.
- SMALL(..., ROW(1:1)): Formül aşağı çekilirken 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 yoksa boş bir hücre döndürür.
Yöntem 2: Filter fonksiyonunu kullanma (Excel365 / 2021)
Excel 365 veya Excel 2021 kullanıyorsanız, FILTER fonksiyonu, karmaşık dizi formülleri olmadan sonuçları dinamik olarak dökme yeteneği, basitliği ve açıklığı sayesinde birden fazla kritere göre birden fazla sonuç döndürmek için mükemmel 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 göre döndürülür.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) her iki koşul da karşılandığında C2:C18'den 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 dizi kontrolü yapar.
- "Eşleşme yok": Hiçbir değer bulunamazsa isteğe bağlı mesaj.
Bir satırda birden fazla kritere göre birden fazla eşleşen değeri döndürme
Excel kullanıcıları genellikle birkaç koşulu karşılayan bir veri setinden birden fazla değer çıkarmak ve bunları yatay olarak (bir satırda) görüntülemek ister. Bu, dikey alanın sınırlı olduğu dinamik raporlar, panolar veya özet tablolar oluştururken yararlı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 izin verir. Dikey çıkarma (sütun tabanlı) 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ütunlara 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: Göreceli sıra numaraları oluşturur.
- COLUMN(A1): Formül ne kadar sağa çekilmişse, buna bağlı olarak hangi eşleşmeyi döndüreceğini ayarlar.
- IFERROR(...): Eşleşmeler bittiğinde hataları önler.
Yöntem 2: Filter fonksiyonunu 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 yerleştirilir. Ekran görüntüsüne bakın:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): İki koşula göre C sütunundan eşleşen değerleri alır.
- (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 fazla eşleşen değeri çıkarmak, sonuçları bir sütunda, bir satırda veya tek bir hücrede göstermek istediğinize bağlı olarak çeşitli şekillerde yapılabilir.
- 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 biraz daha fazla kurulum ve özen gerektirse de güçlü araçlardır.
- Ek olarak, sonuçları tek bir hücrede birleştirmek veya kod yazmadan çözüm tercih ediyorsanız, TEXTJOIN fonksiyonu veya Kutools for Excel gibi üçüncü parti araçlar süreci önemli ölçüde kolaylaştı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 işlemek için iyi donanımlı olacaksınız. Eğer daha fazla Excel ipucu ve hilesi keşfetmeye ilgi duyuyorsanız, web sitemiz binlerce öğretici sunarak Excel'i ustalaştırmak için size yardımcı olacaktır.
Daha fazla ilgili makale:
- Virgülle Ayrılmış Tek Bir Hücrede Birden Fazla Arama Değeri Döndürme
- Excel'de, VLOOKUP fonksiyonunu uygulayarak bir tablodan ilk eşleşen değeri döndürebiliriz, ancak bazen tüm eşleşen değerleri çıkarmamız ve ardından belirli bir ayraçla (virgül, tire vb.) tek bir hücrede birleştirmemiz gerekir. Excel'de birden fazla arama değerini nasıl alıp virgülle ayrılmış tek bir hücrede döndürebiliriz?
- Google Sheet'te Aynı Anda Birden Fazla Eşleşen Değer Döndürme
- Google Sheet'teki normal Vlookup fonksiyonu, belirli bir veriye göre ilk eşleşen değeri bulmanıza ve döndürmenize yardımcı olabilir. Ancak, bazen tüm eşleşen değerleri döndürmeniz gerekebilir. Bu görevi Google Sheet'te çözmek için iyi ve kolay bir yol var mı?
- Açılır Liste Üzerinden Birden Fazla Değer Döndürme
- Excel'de, bir açılır listeden birden fazla karşılık gelen değeri nasıl vlookup yapabilir ve döndürebilirsiniz? Bu, açılır listeden bir öğe seçtiğinizde, tüm ilgili değerlerin bir kerede gösterildiği anlamına gelir. Bu makalede, adım adım çözümü anlatacağım.
- Excel'de Birden Fazla 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, tüm eşleşen değerleri dikey, yatay veya tek bir hücrede nasıl döndürebileceğinizi anlatacağım.
- Excel'de İki Değer Arasındaki Eşleşen Verileri Döndürme
- Excel'de, belirli bir veriye göre karşılık gelen değeri almak için normal Vlookup fonksiyonunu uygulayabiliriz. Ancak, bazen iki değer arasındaki eşleşen değeri döndürmek isteyebiliriz. Bu görevi Excel'de nasıl çözebilirsiniz?
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi geliştirin ve daha önce hiç olmadığı kadar verimli olun. Kutools for Excel, üretkenliğinizi artırmak ve zamanınızı kaydetmek için300'den fazla gelişmiş özellik sunar. En çok ihtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Office'e sekmeli arayüz getirir ve işinizi çok daha kolaylaştırır
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Aynı pencerenin yeni sekmelerinde birden fazla belge açın ve oluşturun, yeni pencerelerde değil.
- Verimliliğinizi %50 artırır ve her gün yüzlerce fare tıklamasını azaltır!