İki Kriterli ÇAPRAZARA Kullanma

ÇAPRAZARA (XLOOKUP) tek bir arama değeri alır, bu yüzden iki kriterli ÇAPRAZARA kullanmak için kriterleri mantıksal çarpım ya da birleştirme ile tek bir arama değerine dönüştürmeniz gerekir. En yaygın yöntemde her kriter için bir mantıksal dizi üretilir, diziler çarpılır ve ÇAPRAZARA bu çarpımda 1 değerini arar. Böylece 'X ürününün Y bölgesindeki satış adedi' gibi soruların cevabını tek formülle alırsınız.

İki kritere göre ÇAPRAZARA nasıl yapılır?​

ÇAPRAZARA bir arama değeri ve bir arama dizisi alır; ikinci kriter için ayrı bir bağımsız değişkeni yoktur. En esnek çözüm mantıksal dizi yöntemidir. Her kriter için aralık, aranan değerle karşılaştırılır ve DOĞRU/YANLIŞ dizileri oluşur. Bu diziler çarpıldığında Excel DOĞRU değerini 1, YANLIŞ değerini 0 sayar; iki koşulu da sağlayan satırlar 1, diğerleri 0 olur. ÇAPRAZARA bu çarpım dizisinde 1 değerini arar ve eşleşen satıra karşılık gelen sonucu getirir.
Aşağıdaki formül, A sütunundaki ürün ile B sütunundaki bölgeye göre D sütunundan satış adedini getirir (kriterler F2 ve F3 hücrelerindedir):
Dördüncü bağımsız değişken, hiçbir satır uymadığında #YOK hatası yerine gösterilecek metindir. Kriterlere uyan son kaydı bulmak için altıncı bağımsız değişken olan arama modunu (search_mode) -1 yapın; eşleştirme modu ise 0 (tam eşleşme) kalır:
Yöntemi şu şekillerde genişletebilirsiniz:

• Üçüncü kriter: Formüle bir çarpım daha ekleyin; her yeni kriter için yalnızca aralık ve kriter hücresi çiftini çarpıma katmanız yeterlidir.
• Tüm satırı getirme: Dönüş dizisini A2:D9 gibi çok sütunlu bir aralık yaparsanız eşleşen satırın tüm değerleri yan yana hücrelere taşar; taşacak hücrelerin boş olması gerekir.
• Sayısal ve tarihsel koşullar: Eşitlik yerine büyüktür ya da küçüktür gibi karşılaştırmaları da aynı çarpım içinde kullanabilirsiniz.

Kriterleri birleştirerek ÇAPRAZARA kullanma​

İkinci yöntemde iki kriter metin olarak birleştirilir ve arama dizisindeki iki sütun da formül içinde aynı şekilde birleştirilir. ÇAPRAZARA birleşik değeri birleşik dizide arar. Yöntem yalnızca tam eşleşme gerektiren metin aramalarında pratiktir.
Birleştirirken araya mutlaka bir ayırıcı koyun; aksi halde farklı kriter çiftleri birleşince aynı metni verebilir (örneğin 'AB' ile 'C', 'A' ile 'BC'). Ayırıcı olarak veride geçmeyen bir karakter, örneğin dik çizgi kullanılabilir:
Birleştirme sonucu her zaman metindir; tarih ya da sayı birleştirirseniz biçimleri kaybolur ve seri sayı olarak metne eklenir. Büyüktür/küçüktür gibi koşullar için bu yöntem uygun değildir.

Hangi yöntemi seçmelisiniz, tüm eşleşmeleri nasıl listelersiniz?​

Mantıksal dizi yöntemi daha fazla senaryoya uyar: Üç ve daha fazla kritere kolayca genişler, sayısal ve tarihsel karşılaştırmaları destekler ve arama modu ile son eşleşmeyi bulabilir. Birleştirme yöntemi ise formülü kısa tutar, ancak yalnızca metin tabanlı tam eşleşmelerde işe yarar.
Her iki yöntem de yalnızca tek bir kayıt döndürür. Kriterlere uyan tüm kayıtları listelemek istiyorsanız FİLTRE (FILTER) işlevi daha uygundur:
FİLTRE sonucu alt alta ve yan yana taşar; bu nedenle formülün altındaki ve sağındaki hücreler boş olmalıdır, aksi halde taşma (SPILL) hatası alırsınız.

1. Ürün ve bölgeye göre satış adedi bulma​

Tabloda ürün, bölge, satış temsilcisi ve satış adedi bilgileri var. F2 ve F3 hücrelerine aranacak ürünü ve bölgeyi yazdık. F4 hücresine şu formülü girin:
F4 hücresi 10 değerini döndürür. Laptop A ve Kuzey çiftini birlikte sağlayan ilk satır tablonun ikinci satırıdır ve ÇAPRAZARA ilk eşleşmede durur. F2 ya da F3 hücresindeki kriteri değiştirdiğinizde sonuç otomatik güncellenir; hiçbir satır uymazsa Kayıt Yok metni görünür.

2. Birleştirme yöntemiyle departman ve pozisyona göre maaş​

Tabloda departman, pozisyon, personel adı ve maaş bilgisi var. F2 ve F3 hücrelerine departmanı ve pozisyonu yazdık. F4 hücresine personelin adını getiren formülü girin; iki kriteri dik çizgi ayırıcı ile birleştiriyoruz:
F5 hücresinde aynı formülü kullanın, yalnızca dönüş dizisini D2:D9 yapın.
F4 hücresi bu departman ve pozisyondaki personelin adını (Elif Çelik), F5 hücresi ise maaşını (50.000,00) getirir. Aranan değer ile arama dizisi aynı ayırıcıyla birleştirildiği için Muhasebe ve Müdür çifti diğer satırlarla karışmaz. Bu yöntem yalnızca tam metin eşleşmesinde çalışır.

3. Müşteri ve ürüne göre son siparişi bulma​

Tabloda müşteri, ürün, sipariş tarihi ve tutar bilgileri var. Belirli bir müşterinin belirli bir ürün için verdiği son siparişi bulmak istiyoruz. F2 ve F3 hücrelerine müşteri ile ürünü yazdık; F4 hücresine şu formülü girin:
F5 hücresinde aynı formülü kullanın, yalnızca dönüş dizisini D2:D9 yapın.
Akdeniz Market müşterisinin Kahve ürünü için üç kaydı vardır; altıncı bağımsız değişken -1 olduğu için ÇAPRAZARA aramaya tablonun sonundan başlar ve en son kaydı bulur. F4 hücresi son sipariş tarihini (07.05.2024), F5 hücresi ise o siparişin tutarını (4.900,00) gösterir. Bu bağımsız değişkeni sildiğinizde ya da 1 yaptığınızda formül ilk kaydı getirir.

Sık sorulan sorular​

ÇAPRAZARA iki kriteri doğrudan destekler mi? Hayır. Tek arama değeri ve tek arama dizisi alır; ikinci kriteri mantıksal çarpım ya da birleştirme ile arama değerinin içine katmanız gerekir.
Formül neden #YOK sonucu veriyor? Hiçbir satır iki kriteri birlikte sağlamıyordur. Kriter hücresindeki fazladan boşluğu, yazım farkını ve sayı ile metin uyumsuzluğunu kontrol edin; kayıt bulunamadığında görünecek metni dördüncü bağımsız değişkene yazarak hatayı yönetebilirsiniz.
Eski Excel sürümlerinde çalışır mı? ÇAPRAZARA Excel 2021 ve Microsoft 365 ile gelir; Excel 2019 ve öncesinde yoktur. Bu sürümlerde İNDİS ve KAÇINCI ile dizi formülü kurmanız gerekir.
Üç kriterle de kullanabilir miyim? Evet. Mantıksal dizi yönteminde çarpıma bir kriter çifti daha eklemeniz yeterlidir.

Kullanırken dikkat edin​

• Aralık boyutlarını eşit tutun: Mantıksal çarpıma giren tüm aralıkların ve dönüş dizisinin satır sayısı aynı olmalıdır. Örneğin A2:A9 ile B2:B10 çarpılırsa #DEĞER! hatası alırsınız.
• Boşluk ve veri türü farklarına dikkat edin: Kriter hücresindeki ya da verideki fazladan boşluklar ve sayı ile metin uyumsuzluğu eşleşmeyi bozar. Verideki gereksiz boşlukları KIRP işleviyle temizleyebilirsiniz. Birleştirme yönteminde tarih ve sayılar metne dönüştüğü için kriter ile arama dizisinin aynı biçimde birleştiğinden emin olun.
• Sürüm farkına dikkat edin: ÇAPRAZARA yalnızca Excel 2021, Microsoft 365 ve Excel web sürümünde bulunur. Excel 2019 ve öncesinde aynı iş için İNDİS ve KAÇINCI işlevlerini dizi formülü olarak kurmanız gerekir.
• Büyük veride performans ve Excel Tablosu: On binlerce satırlık verilerde mantıksal diziler hesaplamayı yavaşlatabilir. Verinizi Ekle > Tablo (Insert > Table) ile Excel Tablosuna çevirirseniz formülde SatışTablom[Ürün] gibi yapısal başvurular kullanabilirsiniz; tabloya eklenen yeni satırlar aralığa otomatik dahil olur.
• Ayırıcı kullanın: Birleştirme yönteminde iki değerin arasına dik çizgi gibi bir ayırıcı koymazsanız farklı kriter çiftleri aynı metni oluşturup yanlış kaydı döndürebilir.
• İlgili konular:
  • ÇAPRAZARA
  • FİLTRE
  • ÇOKETOPLA
  • İNDİS ve KAÇINCI
  • EĞERHATA
  • KIRP
  • DÜŞEYARA
  • Excel Tablosu ve yapısal başvurular

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.