Excel'de iki veya daha fazla kritere göre değer nasıl bulunur?

Diyelim ki aşağıdaki gibi bir meyve satış tablosuyla çalışıyorsunuz. Satış tutarını, meyve türü, satış tarihi ve ağırlık gibi birden fazla bilgiye dayanarak aramanız gerekiyor. Excel’de dizi formüllerini kullanmak, karşılanması gereken birkaç koşul olsa bile bu değerleri verimli şekilde almanızı sağlar. Bu yöntem son derece esnektir ve birden fazla kriterle eşleşen tek bir hücre değerini bulmanız gereken veri kümelerine kolayca uyarlanabilir.

Dizi formülü 1: Excel'de iki veya daha fazla kriterle değer bulun

Bu dizi formülünün genel yapısı aşağıdaki gibidir:

{=INDEX(array,MATCH(1,(criteria1=lookup_array1)*(criteria2= lookup_array2)…*(criteria n= lookup_array n),0))}

Örneğin,mangomeyvesinin 9/3/2019tarihinde yapılan satış tutarını bulmak istiyorsanız, aşağıdaki formülü boş bir hücreye girin ve bunu bir dizi formülü olarak onaylamak için Ctrl+Shift+Entertuşlarına basın:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0))

Not: Bu örnekte,

• F3:F22 aralığı, 'Tutar' sütunundan almak istediğiniz değerlerdir.
• B3:B22 aralığı 'Tarih' sütunudur; C3:C22 aralığı ise 'Meyve' sütunudur.
• J3, ilk kriter olarak seçilen tarihtir; J4, ikinci kriter olarak kullanılan meyve adıdır.

Daha fazla kriter eklemek çok kolay! Örneğin, mango meyvesinin 9/3/2019 tarihinde ve 211 ağırlığında yapılan satış tutarını aramak için, üçüncü koşulu hem EŞLEŞTİR hem de arama dizilerine aşağıdaki gibi ekleyin:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22),0))

Formülü girdikten sonra yine Ctrl+Shift+Enter tuşlarına basarak onaylayın. Sonuç, belirtilen tüm kriterleri karşılayan satış tutarı olacaktır.

Dizi formülü 2: Excel'de birleştirme yoluyla iki veya daha fazla kriterle değer bulun

Alternatif olarak, özellikle daha kompakt bir yapı arıyorsanız formülünüzde birleştirme özelliğinden yararlanabilirsiniz. Temel formül şu şekildedir:

=INDEX(array,MATCH(criteria1&, criteria2…&, criteriaN, lookup_array1&, lookup_array2…&, lookup_arrayN,0),0)

Örneğin,242 ağırlığına sahip bir meyvenin9/1/2019tarihindeki satış tutarını almak için:

=INDEX(F3:F22,MATCH(J3&,J4,B3:B22&,C3:C22,0),0)

Not: Burada,

• F3:F22 tutar sütunudur; B3:B22 tarihtir; E3:E22 ağırlık sütunudur.
• J3, tarihtir; J5, kriterleriniz için ağırlık değeridir.

İkiden fazla kriter için, kriter ve arama dizilerini aynı sırada genişletin:

=INDEX(F3:F22,MATCH(J3&,J4&,J5,B3:B22&,C3:C22&,E3:E22,0),0)

Daha önce olduğu gibi, doğru sonucu almak için Ctrl + Shift + Enter tuşlarına basın.

Her iki dizi formülü yöntemi de tüm kriterlerinizi karşılayan ilk değeri bulmanıza olanak tanır. Ancak bu yöntemler, hücre aralıklarının aynı boyutta olmasını gerektirir ve birden fazla eşleşen değeri döndürmez—yalnızca ilk eşleşme alınır. Eğer eşleşme yoksa, formül bir #YOK hatası verir. Tüm eşleşmeleri görüntülemek istiyorsanız, aşağıda ayrıntılarına yer verilen FİLTRE işlevini değerlendirmeyi düşünün.

Bazı pratik ipuçları ve notlar:

• Daha yeni Excel sürümleriyle (Microsoft 365, Excel 2021) çalışıyorsanız, bu işlemi basitleştirmek için dinamik dizi formüllerini ve FİLTRE işlevini kullanabilirsiniz.
• Eşleşme olmadığında #YOK hatalarını önlemek için formülünüzü EĞERHATA fonksiyonunun içine alabilirsiniz; örneğin, =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),"Not found").
• Arama kriteri hücrelerinizde fazladan boşluk veya farklı veri türleri bulunmadığından emin olun.
• Yalnızca Enter tuşuna bastıktan sonra hata alıyorsanız, formülü bir dizi formülü olarak onaylamak için Ctrl+Shift+Enter tuşlarına basmanız gerekir (Excel 2019 ve önceki sürümler için).

Formüllerin yanı sıra Excel, iki veya daha fazla kriteri karşılayan tüm satırları filtrelemenize ve başka bir yere kopyalamanıza olanak tanıyan Gelişmiş Filtre özelliğini de sunar. Bu yöntem, yalnızca tek bir değer almak yerine belirttiğiniz koşullara uyan tüm kayıtları görüntülemek istediğinizde özellikle kullanışlıdır. İşte nasıl yapacağınız:

1. Veri sekmesine gidin ve Gelişmiş Filtre iletişim kutusunu açmak için Sırala ve Filtrele grubundan Gelişmiş seçeneğini belirleyin.

2. Gelişmiş Filtre iletişim kutusunda aşağıdaki ayarları yapın:
(1) Başka bir konuma kopyala seçeneğini Eylem bölümünde işaretleyin.
(2) Liste aralığıiçin filtrelemek istediğiniz verileri içeren aralığı seçin ()A1:E21 bu örnekte).
(3) Kriter aralığıiçin Filtre Koşulları’nı içeren aralığı seçin ()H1:J2 burada). Bu kriter aralığındaki başlıkların veri tablonuzdakilerle tamamen aynı olduğundan emin olun.
(4) Şuraya kopyalakısmında, filtrelenmiş sonuçların yapıştırılmasını istediğiniz ilk hücreyi seçin ()H9 bu durumda).

3. Filtrelemeyi gerçekleştirmek için Tamam düğmesine tıklayın.

Kriter aralığınızda belirttiğiniz tüm koşulları sağlayan satırlar, belirttiğiniz hedef alana kopyalanacaktır. Bu özellikle, aynı anda birden fazla Filtre Koşulları ile eşleşen tüm kayıtları incelemek veya raporlamak için son derece kullanışlıdır.

Bazı ipuçları ve dikkat edilmesi gerekenler:

• Kriter aralığınızın başlıklarının ana veri tablonuzdakilerle tamamen aynı olduğundan emin olun; aksi takdirde filtre düzgün çalışmayabilir.
• Gelişmiş Filtre, hem VE hem de VEYA koşullarını destekler: kriterleri aynı satıra yerleştirmek, tümünün doğru olması gereken VE mantığını uygularken; ayrı satırlarda belirtmek, herhangi birinin doğru olması yeterli olan VEYA mantığını devreye sokar.
• Gelişmiş Filtre, verileriniz değiştiğinde otomatik olarak güncellenmez; verilerinizi veya kriterlerinizi değiştirdikten sonra filtreyi yeniden uygulamanız gerekir.
• Kriter aralığında boş hücrelerin, o alan için “herhangi bir değeri eşleştir” şeklinde değerlendirileceğini unutmayın.

Gelişmiş Filtre, formüle dayalı çözümlere kıyasla yalnızca tek bir hücreyi değil, kriterlere uyan tüm veri kümesini çıkarmak için özellikle uygundur. Ancak veri değişikliklerinden sonra filtreyi tekrar çalıştırmanız gerektiği için gerçek zamanlı veya sıklıkla güncellenen aramalar için uygun değildir.

Eğer yeni bir Excel sürümü (Microsoft 365 veya Excel 2021 ve sonrası) kullanıyorsanız, FİLTRE işlevi, birden fazla kritere uyan tüm değerleri çekmek için dinamik ve sezgisel bir yol sunar. Veri ya da kriterler değiştiğinde sonuçların otomatik olarak güncellenmesini isteyen kullanıcılar için bu çözüm şiddetle önerilir ve karmaşık dizi girişi gerektirmez.

1. Boş bir hücreye aşağıdaki formüle benzer bir formül girin:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

Bu formülde:

• F3:F22, tutar sütununuzdur.
• B3:B22 tarih sütunudur ve J3 hücresindeki tarihe göre eşleştirilir.
• C3:C22 meyve sütunudur ve J4 hücresindeki meyveye göre eşleştirilir.

Ağırlık sütununda (E3:E22)yer alan bir değeri J5hücresindeki bir değerle eşleştiren üçüncü bir koşul eklemek istiyorsanız, formülü genişletin:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Enter tuşuna bastıktan sonra Excel, tüm kriterlere uyan Tutarları gösterecektir. Eşleşme bulunamazsa formül #HESAP! hatası döndürür; bu hatayı EĞERHATA işleviyle yönetebilirsiniz:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Avantajlar:

• Sonuçlar, verileriniz ya da kriterleriniz değiştiğinde otomatik olarak güncellenir.
• Formüller, eski dizi formüllerine kıyasla sürdürülmesi ve genişletilmesi daha kolaydır.
• Yalnızca ilk eşleşen değeri değil, tüm eşleşmeleri döndürür.

Sınırlama: Yalnızca Microsoft 365, Excel 2021 veya sonraki sürümlerde kullanılabilir. Eski sürümlerde desteklenmez.