Gelişmiş arama ve dizi formülleriyle analiz yapma (Lookup and array formulas), bir tablodaki değerleri başka bir tabloda bulup eşleştirmek ve birden fazla koşulu tek formülde değerlendirmek demektir. Arama işlevleri (DÜŞEYARA, ÇAPRAZARA, İNDİS ve KAÇINCI) aradığınız kaydı bulur; dizi formülleri ise aralıkların tamamı üzerinde tek adımda hesap yapar. Bu sayfada hangi işlevin hangi durumda seçileceğini ve üç uygulamalı örnekle nasıl kurulacağını bulacaksınız.
Son bağımsız değişken, kod listede yoksa #YOK hatası yerine kendi mesajınızı göstermenizi sağlar.
Karşılaştırma işlemi her satır için DOĞRU ya da YANLIŞ üretir; çarpma bunları 1 ve 0'a çevirir, böylece yalnızca koşulu sağlayan satırlar toplama girer.
İki yönlü arama için İNDİS işlevine satır sırasını ve sütun sırasını iki ayrı KAÇINCI ile verirsiniz:
Analizi okunur kılmak için şu adımları uygulayın:
İlk kod için ürün adı olarak Monitör, fiyat olarak 5.200,00 gelir. İkinci kod için Harici Disk ve 2.350,00 sonucunu alırsınız. Listede bulunmayan kodda ÇAPRAZARA bizim verdiğimiz metni (Bulunamadı), İNDİS ve KAÇINCI ikilisi ise EĞERHATA sayesinde Fiyat yok metnini gösterir.
İstanbul cirosu 53.160,00 olarak hesaplanır; Excel yalnızca şehir sütunu İstanbul olan satırların adet ve fiyat çarpımlarını toplar. Ankara için en yüksek satır tutarı 15.600,00 bulunur. Son formül, adedi 10 ve üzeri olan İstanbul satırlarının sayısını (2) verir. Koşul eklemek için çarpıma yeni bir karşılaştırma bölümü yazmanız yeterlidir.
KAÇINCI işlevlerinden biri ürünün satırını, diğeri ayın sütununu bulur; İNDİS bu kesişimdeki değeri getirir ve sonuç 135 olur. Dönem toplamı hücresi (493) seçili ürünün tüm aylardaki adetlerini tek formülle toplar. B8 ve B9 hücrelerine Veri Doğrulama ile açılır liste eklerseniz tabloyu seçim yaparak sorgulayabilirsiniz.
Arama işlevleri hangi durumda kullanılır?
Arama işlevleri, bir anahtar değere (ürün kodu, personel numarası gibi) bakıp aynı kayda ait başka bir bilgiyi getirir. Seçim yaparken şu farklara bakın:- DÜŞEYARA (VLOOKUP): Aranan değeri tablonun ilk sütununda arar ve sonucu sütun numarasıyla döndürür. Tam eşleşme için son bağımsız değişkene YANLIŞ yazın.
- YATAYARA (HLOOKUP): Aynı işi ilk satırda yapar; başlıkları üstte olan tablolarda kullanılır.
- ÇAPRAZARA (XLOOKUP): Aranan diziyi ve sonuç dizisini ayrı ayrı belirtirsiniz; sonuç sütunu solda da olabilir. Varsayılan olarak tam eşleşme arar ve bulunamadığında göstereceğiniz metni bağımsız değişken olarak alır. Excel 2021 ve Microsoft 365'te bulunur.
- İNDİS (INDEX) ve KAÇINCI (MATCH): KAÇINCI değerin sırasını bulur, İNDİS o sıradaki hücreyi getirir. Tüm sürümlerde çalışır ve iki yönlü aramaya olanak verir.
Dizi formülü nedir, nasıl yazılır?
Dizi formülü, tek bir hücrede bir aralığın tüm değerleriyle işlem yapan formüldür. Örneğin bir şehre ait satırları süzüp tutarlarını çarpmak ve toplamak için yardımcı sütun açmanız gerekmez.- Microsoft 365 ve Excel 2021: Formülü yazıp Enter tuşuna basmanız yeterlidir. Sonuç birden fazla hücreye yayılıyorsa, yayılacağı hücrelerin boş olması gerekir.
- Excel 2019 ve öncesi: Diziyi işleyen formüller Ctrl+Shift+Enter ile girilir ve Excel formülü süslü parantez içinde gösterir. Süslü parantezleri elle yazmayın.
- TOPLA.ÇARPIM (SUMPRODUCT): Dizileri kendi içinde işlediği için her sürümde ek tuş kombinasyonu gerektirmez; çok koşullu toplamlarda en güvenli yoldur.
Excel formülü
TR
=TOPLA.ÇARPIM((A2:A9="İstanbul")*C2:C9*D2:D9)
EN
=SUMPRODUCT((A2:A9="İstanbul")*C2:C9*D2:D9)Arama ve dizi formüllerini analizde birlikte kullanma
Gerçek raporlarda iki yaklaşım birlikte çalışır. Önce arama işlevleriyle başka tablolardaki fiyat, oran ya da bölge bilgisini satırlara getirir, sonra dizi formülleriyle koşula göre toplar ya da sayarsınız.İki yönlü arama için İNDİS işlevine satır sırasını ve sütun sırasını iki ayrı KAÇINCI ile verirsiniz:
- Seçim hücrelerini listeye çevirin: Veri > Veri Araçları > Veri Doğrulama (Data > Data Tools > Data Validation) ile aranan ürün ve ay hücrelerine açılır liste ekleyin; yazım hatası kalmaz.
- Sonuçları vurgulayın: Giriş > Stiller > Koşullu Biçimlendirme (Home > Styles > Conditional Formatting) ile belirli bir değerin altındaki ya da üstündeki hücreleri renklendirin.
- Başvuruları sabitleyin: Aranan tabloyu gösteren başvurularda F4 tuşuyla dolar işareti ekleyin; formülü aşağı kopyaladığınızda aralık kaymaz.
- Hataları yönetin: Bulunamayan kayıtlar için EĞERHATA (IFERROR) ya da ÇAPRAZARA'nın bulunamazsa bağımsız değişkenini kullanın.
1. Ürün koduna göre ürün adı ve fiyat getirme
Soldaki listede ürün kodları, adları ve birim fiyatlar yer alıyor. D sütununa yazdığınız koda göre ürün adını ÇAPRAZARA ile, fiyatı ise İNDİS ve KAÇINCI ikilisiyle getiriyoruz. Listede olmayan bir kod da denenmiştir.Gelişmiş Arama ve Dizi Formülleriyle Analiz Yapma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ürün Kodu | Ürün | Birim Fiyat | Aranan Kod | Bulunan Ürün | Bulunan Fiyat |
| 2 | K-101 | Kablosuz Fare | 450,00 | K-103 | Monitör | 5.200,00 |
| 3 | K-102 | Klavye | 780,00 | K-107 | Harici Disk | 2.350,00 |
| 4 | K-103 | Monitör | 5.200,00 | K-999 | Bulunamadı | Fiyat yok |
| 5 | K-104 | Kulaklık | 1.150,00 | |||
| 6 | K-105 | Web Kamerası | 890,00 | |||
| 7 | K-106 | USB Bellek | 210,00 | |||
| 8 | K-107 | Harici Disk | 2.350,00 | |||
| 9 | K-108 | Yazıcı | 3.400,00 |
Fiyat AramaExcelDepo v1.0.0
2. Çok koşullu toplama ve sayma
Şehir bazlı satış tablosunda Tutar sütunu adet ile birim fiyatın çarpımıdır. Alt kısımdaki üç formül, yardımcı sütun kullanmadan TOPLA.ÇARPIM ile dizi işlemi yapar: bir şehrin cirosu, başka bir şehrin en yüksek satır tutarı ve belirli bir adet sınırını geçen satışların sayısı.Gelişmiş Arama ve Dizi Formülleriyle Analiz Yapma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Şehir | Ürün | Adet | Birim Fiyat | Tutar |
| 2 | İstanbul | Monitör | 4 | 5.200,00 | 20.800,00 |
| 3 | Ankara | Klavye | 15 | 780,00 | 11.700,00 |
| 4 | İzmir | Kulaklık | 10 | 1.150,00 | 11.500,00 |
| 5 | İstanbul | Klavye | 12 | 780,00 | 9.360,00 |
| 6 | Ankara | Monitör | 3 | 5.200,00 | 15.600,00 |
| 7 | Bursa | Yazıcı | 2 | 3.400,00 | 6.800,00 |
| 8 | İzmir | Monitör | 5 | 5.200,00 | 26.000,00 |
| 9 | İstanbul | Kulaklık | 20 | 1.150,00 | 23.000,00 |
| 10 | İstanbul cirosu | 53.160,00 | |||
| 11 | Ankara en yüksek tutar | 15.600,00 | |||
| 12 | İstanbul 10 ve üzeri adetli satış | 2 |
Dizi AnaliziExcelDepo v1.0.0
3. Ürün ve aya göre iki yönlü arama
Satır başlıkları ürün, sütun başlıkları ay olan bir satış adedi tablosunda, B8 ve B9 hücrelerine yazdığınız ürün ve aya göre kesişimdeki değeri İNDİS ve KAÇINCI ile buluyoruz. Aynı ürünün dört aylık toplamını ise dizi çarpımıyla hesaplıyoruz.Gelişmiş Arama ve Dizi Formülleriyle Analiz Yapma
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Ürün | Ocak | Şubat | Mart | Nisan |
| 2 | Monitör | 40 | 35 | 42 | 38 |
| 3 | Klavye | 120 | 110 | 135 | 128 |
| 4 | Fare | 150 | 140 | 160 | 155 |
| 5 | Kulaklık | 60 | 72 | 65 | 80 |
| 6 | Yazıcı | 18 | 22 | 20 | 25 |
| 7 | Web Kamerası | 30 | 28 | 35 | 33 |
| 8 | Aranan ürün | Klavye | |||
| 9 | Aranan ay | Mart | |||
| 10 | Satış adedi | 135 | |||
| 11 | Dönem toplamı | 493 |
İki Yönlü AramaExcelDepo v1.0.0
Sık sorulan sorular
- DÜŞEYARA yerine ÇAPRAZARA kullanmalı mıyım? Sürümünüz destekliyorsa evet. Sonuç sütunu solda olabilir, varsayılan eşleşme tamdır ve sütun numarası saymanız gerekmez. Eski sürümlerde İNDİS ve KAÇINCI ikilisini kullanın.
- Ctrl+Shift+Enter hala gerekli mi? Microsoft 365 ve Excel 2021'de gerekmez; Enter yeterlidir. Excel 2019 ve öncesinde dizi işleyen formüllerde gerekir, TOPLA.ÇARPIM ise bu tuşlara ihtiyaç duymaz.
- Arama formülüm neden #YOK hatası veriyor? Çoğunlukla aranan değer listede yoktur ya da fazladan boşluk içerir. Sayı olarak girilmiş bir kodla metin olarak girilmiş kod da eşleşmez. Boşlukları KIRP (TRIM) ile temizleyin.
- Dizi formülleri dosyayı yavaşlatır mı? Çok sayıda dizi formülü ve çok büyük aralıklar hesaplamayı yavaşlatabilir. Aralıkları verinin gerçek boyutuyla sınırlayın ve gereksiz tam sütun başvurularından kaçının.
Kullanırken dikkat edin
- Aralık boyutlarını uyumlu tutun: TOPLA.ÇARPIM içinde birlikte işlenen aralıkların boyutları uyumlu olmalıdır. Satır sayıları farklı iki sütunu çarparsanız formül hata verir. Yazarken her aralığın aynı satırdan başlayıp aynı satırda bittiğini kontrol edin.
- Çarpılan aralıklarda metin bulunmasın: Koşulları çarparak birleştirdiğinizde sayı sütunlarında metin ya da boşluk karakteri bulunan hücreler #DEĞER! hatasına yol açabilir. Sayıların metin olarak girilmediğinden emin olun.
- KAÇINCI için tam eşleşmeyi belirtin: KAÇINCI işlevinin son bağımsız değişkenine 0 yazmazsanız sıralı veri varsayılarak yaklaşık eşleşme yapılır ve yanlış satır dönebilir. Kod ve ad gibi metinsel aramalarda 0 kullanın. DÜŞEYARA için karşılığı YANLIŞ değeridir.
- Sürüm farklarını gözetin: ÇAPRAZARA ve dinamik diziler Excel 2021 ile Microsoft 365'te vardır; Excel 2019 ve öncesinde ÇAPRAZARA bulunmaz. Dosyayı eski sürüm kullanan biriyle paylaşacaksanız İNDİS, KAÇINCI ve TOPLA.ÇARPIM gibi her sürümde çalışan işlevleri tercih edin.
- Başvuruları sabitleyin: Formülü aşağı kopyalarken arama aralığının kaymaması için başvuruları F4 tuşuyla sabitleyin; örneğin A2:A9 yerine $A$2:$A$9 yazın. Aranan değer hücresini ise göreli bırakın.
- İlgili konular: Bu konuyla bağlantılı Excel özellikleri ve işlevleri: DÜŞEYARA, YATAYARA, ÇAPRAZARA, İNDİS, KAÇINCI, TOPLA.ÇARPIM, ÇOKETOPLA, EĞERHATA, KIRP, Koşullu Biçimlendirme ve Veri Doğrulama.
Ekli dosyalar
Son düzenleme:
