Çoklu kriterlerle DÜŞEYARA (multiple criteria VLOOKUP), iki ya da daha fazla sütundaki değerin aynı anda tutulduğu satırı bulup istediğiniz sütundan sonuç getirme tekniğidir. DÜŞEYARA tek bir aranan değerle çalıştığı için ölçütleri yardımcı sütunda birleştirerek formülü çoklu ölçüte uyarlarsınız. Yardımcı sütun istemezseniz İNDİS ve KAÇINCI ya da ÇAPRAZARA işlevleriyle aynı sonuca ulaşabilirsiniz.
Hangi yöntemi seçeceğiniz Excel sürümünüze ve tablonuzu değiştirip değiştiremeyeceğinize bağlıdır. Tabloya sütun ekleyemiyorsanız çarpma yöntemini tercih edin.
Ölçüt sayısı arttıkça formülün uzamasını önlemek için her ölçütü ayrı bir hücreye yazın ve formülde bu hücrelere başvurun. Böylece formülü değiştirmeden farklı kombinasyonları deneyebilirsiniz.
Ölçütlerin sırası da önemlidir: anahtar sütunda hangi sırayla birleştirdiyseniz aranan değerde de aynı sırayı kullanmalısınız.
Ölçütler arasına koşul eklemek gerekirse yeni bir sütun eklemeniz ya da çarpma ifadesine bir parantez daha eklemeniz yeterlidir. Her yeni ölçüt formülü uzatır ama sonucu daha seçici yapar.
Bu nedenle ölçüt sayısını gerçekten ihtiyaç duyduğunuz kadar tutmanız hem okunabilirlik hem de hesaplama hızı için daha iyidir.
Değerler metin ya da sayı olabilir; önemli olan aranan değer ile tablodaki değerin aynı türde olmasıdır.
Bu formül Türkçe ve İngilizce Excel'de aynıdır; & işleci iki hücrenin değerini art arda yazar. Ardından DÜŞEYARA'da aranan değeri de aynı biçimde birleştirirsiniz:
Bu yöntemde dikkat edilecek noktalar şunlardır:
Bu yöntemde anahtar sütun gerekmez; ancak her hesaplamada tüm aralıklar bellekte taranır. Çok büyük tablolarda yardımcı sütun daha hızlı çalışabilir.
A2 hücresindeki Ankara|Defter gibi anahtarlar şehir ve ürünü tek metinde birleştirir. D10 hücresi aynı biçimde birleştirilen aranan değeri A sütununda bulur ve dördüncü sütundan adedi getirir: 210. Şehir ya da ürünü değiştirirseniz sonuç otomatik güncellenir.
Formüldeki iki koşul çarpılır; yalnızca personel ve ay aynı anda tutan satırda sonuç 1 olur. KAÇINCI bu satırın sırasını bulur, İNDİS de C sütunundan o satırdaki tutarı getirir: 42.650,00. Excel 2019 ve öncesinde bu formülü Ctrl+Shift+Enter ile girmeniz gerekir.
Üç koşul çarpılarak 1 değerini veren ilk satır aranır ve D sütunundaki adet getirilir: 52. Ölçütlerden birini tabloda olmayan bir değerle değiştirirseniz formül Bulunamadı metnini gösterir. Formül Excel 2021 ve Excel 365 gerektirir.
Çoklu kriterle DÜŞEYARA nasıl yapılır?
DÜŞEYARA yalnızca tek bir değeri, tablo aralığının ilk sütununda arar ve bulduğu ilk eşleşmenin satırından sonuç getirir. Bu yüzden ölçüt sayısı birden fazla olduğunda iki temel yoldan biri seçilir.- Ölçütleri birleştirmek: Ölçüt sütunlarını tek bir anahtar sütunda birleştirir, DÜŞEYARA'da aranan değeri de aynı biçimde birleştirirsiniz.
- Koşulları çarpmak: Her koşul DOĞRU ya da YANLIŞ döndürür; Excel bunları çarpma işleminde 1 ve 0 olarak ele alır. Çarpım yalnızca tüm koşullar doğruysa 1 olur. Bu, VE (AND) mantığını verir. Toplama ise YA DA (OR) mantığına karşılık gelir.
Hangi yöntemi seçeceğiniz Excel sürümünüze ve tablonuzu değiştirip değiştiremeyeceğinize bağlıdır. Tabloya sütun ekleyemiyorsanız çarpma yöntemini tercih edin.
Ölçüt sayısı arttıkça formülün uzamasını önlemek için her ölçütü ayrı bir hücreye yazın ve formülde bu hücrelere başvurun. Böylece formülü değiştirmeden farklı kombinasyonları deneyebilirsiniz.
Ölçütlerin sırası da önemlidir: anahtar sütunda hangi sırayla birleştirdiyseniz aranan değerde de aynı sırayı kullanmalısınız.
Ölçütler arasına koşul eklemek gerekirse yeni bir sütun eklemeniz ya da çarpma ifadesine bir parantez daha eklemeniz yeterlidir. Her yeni ölçüt formülü uzatır ama sonucu daha seçici yapar.
Bu nedenle ölçüt sayısını gerçekten ihtiyaç duyduğunuz kadar tutmanız hem okunabilirlik hem de hesaplama hızı için daha iyidir.
Değerler metin ya da sayı olabilir; önemli olan aranan değer ile tablodaki değerin aynı türde olmasıdır.
Yardımcı sütunla çoklu kriterli DÜŞEYARA
Yardımcı sütun, çoklu ölçütlü DÜŞEYARA için en anlaşılır ve her Excel sürümünde çalışan yoldur. Veri tablosunun en soluna yeni bir sütun ekler ve ölçüt sütunlarını bir ayırıcıyla birleştirirsiniz:
Excel formülü
=B2&"|"&C2- Anahtar sütunun yeri: DÜŞEYARA (VLOOKUP) yalnızca tablo aralığının ilk sütununda arama yaptığı için anahtar sütun aralığın en solunda olmalıdır.
- Ayırıcı: Ölçütler arasına bir karakter koymak yanlış eşleşmeleri önler. Örneğin ayırıcı olmazsa 1 ile 11 ve 11 ile 1 birleşince aynı metni verir.
- Tam eşleşme: Son bağımsız değişkene YANLIŞ (FALSE) yazın; aksi halde yaklaşık eşleşme yapılır ve sonuç yanlış çıkabilir.
- Sütun numarası: Getirmek istediğiniz sütunun, tablo aralığının solundan itibaren kaçıncı sütun olduğunu yazın. Yardımcı sütun da bu sayıma dahildir.
- Temiz veri: Hücrelerde fazladan boşluk varsa eşleşme bozulur; gerekirse KIRP işleviyle temizleyin.
Yardımcı sütun olmadan çoklu ölçütle arama
Tabloya sütun ekleyemiyorsanız ölçütleri formülün içinde çarparak satırı bulabilirsiniz. İki işlev bu iş için uygundur.- İNDİS ve KAÇINCI (INDEX, MATCH): KAÇINCI, koşulların çarpımının 1 olduğu ilk satırın sırasını verir; İNDİS bu sıradaki değeri getirir. Excel 365 ve Excel 2021'de normal Enter yeterlidir. Daha eski sürümlerde formülü Ctrl+Shift+Enter ile dizi formülü olarak girmeniz gerekir.
- ÇAPRAZARA (XLOOKUP): Excel 365 ve Excel 2021'de bulunur. Aranan değer olarak 1, aranan dizi olarak koşulların çarpımını verirsiniz. Eşleşme yoksa dönecek metni dördüncü bağımsız değişkenle belirleyebilirsiniz.
- ELEMAN ile DÜŞEYARA: ELEMAN (CHOOSE) işleviyle bellekte iki sütunlu bir dizi kurup DÜŞEYARA'ya vermek de mümkündür. Ancak formül zor okunur; İNDİS ve KAÇINCI ya da ÇAPRAZARA daha anlaşılırdır.
Bu yöntemde anahtar sütun gerekmez; ancak her hesaplamada tüm aralıklar bellekte taranır. Çok büyük tablolarda yardımcı sütun daha hızlı çalışabilir.
1. Yardımcı sütunla şehir ve ürüne göre adet bulma
Şehir ve ürün sütunlarını A sütunundaki anahtarda birleştirip DÜŞEYARA ile adet getirin. Aranan şehir ve ürün B10 ve C10 hücrelerindedir.Çoklu Kriterlerle DÜŞEYARA Kullanma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Anahtar | Şehir | Ürün | Adet |
| 2 | Ankara|Defter | Ankara | Defter | 120 |
| 3 | Ankara|Kalem | Ankara | Kalem | 340 |
| 4 | İzmir|Defter | İzmir | Defter | 95 |
| 5 | İzmir|Kalem | İzmir | Kalem | 210 |
| 6 | Bursa|Defter | Bursa | Defter | 150 |
| 7 | Bursa|Kalem | Bursa | Kalem | 80 |
| 8 | ||||
| 9 | Aranan Şehir | Aranan Ürün | Adet | |
| 10 | İzmir | Kalem | 210 |
Yardımcı SütunExcelDepo v1.0.0
2. İNDİS ve KAÇINCI ile personel ve aya göre satış
Tabloya sütun eklemeden, personel adı ve ayı birlikte sağlayan satırdaki satış tutarını getirin. Aranan değerler A10 ve B10 hücrelerindedir.Çoklu Kriterlerle DÜŞEYARA Kullanma
Formülünü incelemek için bir hücre seçin.| A | B | C | |
|---|---|---|---|
| 1 | Personel | Ay | Satış (TL) |
| 2 | Ayşe Yılmaz | Ocak | 45.200,00 |
| 3 | Ayşe Yılmaz | Şubat | 51.800,00 |
| 4 | Mehmet Demir | Ocak | 38.900,00 |
| 5 | Mehmet Demir | Şubat | 42.650,00 |
| 6 | Zeynep Arslan | Ocak | 47.300,00 |
| 7 | Zeynep Arslan | Şubat | 39.500,00 |
| 8 | |||
| 9 | Aranan Personel | Aranan Ay | Satış Tutarı |
| 10 | Mehmet Demir | Şubat | 42.650,00 |
Personel SatışExcelDepo v1.0.0
3. ÇAPRAZARA ile üç ölçütlü arama
Bölge, ürün ve ay olmak üzere üç ölçütü birlikte sağlayan satırın satış adedini bulun. Eşleşme yoksa formül Bulunamadı metnini döndürür. Aranan değerler A12, B12 ve C12 hücrelerindedir.Çoklu Kriterlerle DÜŞEYARA Kullanma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Bölge | Ürün | Ay | Satış Adedi |
| 2 | Marmara | Laptop | Ocak | 42 |
| 3 | Marmara | Laptop | Şubat | 38 |
| 4 | Marmara | Telefon | Ocak | 65 |
| 5 | Marmara | Telefon | Şubat | 71 |
| 6 | Ege | Laptop | Ocak | 27 |
| 7 | Ege | Laptop | Şubat | 31 |
| 8 | Ege | Telefon | Ocak | 44 |
| 9 | Ege | Telefon | Şubat | 52 |
| 10 | ||||
| 11 | Aranan Bölge | Aranan Ürün | Aranan Ay | Satış Adedi |
| 12 | Ege | Telefon | Şubat | 52 |
Bölge SatışExcelDepo v1.0.0
Sık sorulan sorular
- DÜŞEYARA tek başına iki ölçüt alabilir mi? Hayır, DÜŞEYARA tek bir aranan değerle çalışır. İki ölçütü yardımcı sütunda birleştirerek ya da ELEMAN ile oluşturduğunuz diziyle tek değer haline getirirsiniz.
- Birden fazla eşleşme varsa ne olur? DÜŞEYARA, İNDİS-KAÇINCI ve ÇAPRAZARA ilk eşleşen satırı getirir. Tüm eşleşenleri listelemek için Excel 365'teki FİLTRE işlevini kullanın.
- Eşleşme bulunamazsa hata nasıl gizlenir? Formülü EĞERHATA (IFERROR) ile sarmalayın ya da ÇAPRAZARA'nın bulunamadı metni için ayrılmış bağımsız değişkenini kullanın.
- ÇAPRAZARA eski sürümlerde çalışır mı? Hayır. ÇAPRAZARA yalnızca Excel 2021 ve Excel 365'te bulunur; Excel 2019 ve öncesinde yardımcı sütunlu DÜŞEYARA ya da İNDİS-KAÇINCI kullanın.
Kullanırken dikkat edin
- Ayırıcıyı atlamayın: Yardımcı sütunda ölçütleri ayırıcı olmadan birleştirirseniz farklı kombinasyonlar aynı metni üretebilir. Verinizde geçmeyen bir karakter, örneğin dikey çizgi kullanın.
- Tam eşleşme için YANLIŞ yazın: DÜŞEYARA'nın son bağımsız değişkeni boş bırakılırsa ya da DOĞRU yazılırsa yaklaşık eşleşme yapılır ve anahtar sütun sıralı değilse yanlış sonuç çıkar. Çoklu ölçütlerde her zaman YANLIŞ (FALSE) kullanın.
- Yalnızca ilk eşleşme gelir: Aynı ölçüt kombinasyonu tabloda birden çok kez geçiyorsa formül yalnızca ilk satırı getirir. Tüm eşleşmeleri görmek için Excel 365'te FİLTRE işlevini kullanın; sayısal değerleri toplamak için ÇOKETOPLA daha uygundur.
- Sürüm farkına dikkat edin: İNDİS ve KAÇINCI ile koşul çarpma yöntemi Excel 2019 ve öncesinde Ctrl+Shift+Enter ile dizi formülü olarak girilmelidir. ÇAPRAZARA ise yalnızca Excel 2021 ve Excel 365'te bulunur.
- Veri türleri eşleşmeli: Aranan değer sayı iken tablodaki değer metin olarak saklanmışsa eşleşme bulunamaz. Fazladan boşluklar da aynı soruna yol açar; gerekirse KIRP işleviyle temizleyin.
- Hata ayıklama: Formül beklenmedik sonuç veriyorsa Formüller > Formül Denetleme > Formülü Değerlendir (Formulas > Formula Auditing > Evaluate Formula) komutuyla adım adım izleyin. Koşulları ayrı hücrelerde test etmek de hatalı kısmı bulmanızı kolaylaştırır.
- İlgili konular: DÜŞEYARA, İNDİS, KAÇINCI, ÇAPRAZARA, FİLTRE, ÇOKETOPLA, EĞERHATA, KIRP, ELEMAN ve yardımcı sütun ile yapısal başvurular.
Ekli dosyalar
Son düzenleme:
