Excel'de İNDEX ve EŞLEŞTİR birleşimi üzerine ustalık sınıfı

İNDEX ve EŞLEŞTİR işlevlerinin birleşimi, DÜŞEYARA işlevinin sınırlamalarını (örneğin, soldan sağa arama) aşarak herhangi bir satır ve herhangi bir sütunun kesişimindeki verileri bulmanıza olanak tanır. Formül şu prensibe göre çalışır: İNDEX tablodan değeri alır, EŞLEŞTİR ise bu değer için koordinatları (satır ve sütun numarası) hesaplar. Bu, tablo yapısının değişebileceği dinamik raporlar için evrensel bir çözümdür.

İşlev ikilisinin çalışma prensibi

Bu aracı etkili bir şekilde kullanmak için her bir işlevin rolünü anlamak gerekir:

• EŞLEŞTİR (MATCH) — konumu belirler. Belirtilen bir değeri bir aralıkta arar ve sıralı numarasını döndürür (örneğin, "İvanov" listenin 5. satırında bulunur).
• İNDEX (INDEX) — değeri çıkarır. Veri aralığını ve satır ile sütun numaralarını kabul eder ve bunların kesişimindeki hücrenin içeriğini döndürür.

Temel sözdizimi ve basit bir örnek

Standart bir görevi ele alalım: Bir ürün tablosu var (sütunlar: Ürün Kodu, Ad, Fiyat, Stok). Belirli bir ürünün fiyatını adına göre bulmanız gerekiyor.

Varsayalım ki:

• Veri aralığı: A2:D100
• Ürün adları B sütununda (B2:B100)
• Fiyatlar C sütununda (C2:C100)
• Aranacak ad F1 hücresinde yazılı.

Formül şöyle görünecektir:

=İNDEX(C2:C100; EŞLEŞTİR(F1; B2:B100; 0))

Mantığın analizi:

1. EŞLEŞTİR(F1; B2:B100; 0) F1 hücresindeki ürünün bulunduğu satır numarasını bulur. 0 bağımsız değişkeni tam eşleşmeyi ifade eder.
2. İNDEX bu numarayı alır ve C2:C100 fiyat aralığının ilgili satırındaki değeri döndürür.

İki boyutlu arama: Satır ve sütun matrisi

Bu birleşimin en güçlü özelliği, aynı anda iki kritere göre değer aramasıdır (örneğin, belirli bir ayda veya bölgede bir ürünün fiyatı). Burada EŞLEŞTİR iki kez kullanılır: bir kez satır numarasını bulmak için, ikinci kez sütun numarasını bulmak için.

Senaryo: Bir satış matrisiniz var:

• Satırlar (A2:A10): Ürün Adları.
• Sütunlar (B1:E1): Aylar (Ocak, Şubat, Mart, Nisan).
• Veriler (B2:E10): Satış hacmi.
• "Dizüstü Bilgisayar" ürünü (G1 hücresi) için "Mart" (H1 hücresi) ayındaki satışları bulmanız gerekiyor.

Formül:

=İNDEX(B2:E10; EŞLEŞTİR(G1; A2:A10; 0); EŞLEŞTİR(H1; B1:E1; 0))

Bu durumda:

1. İlk EŞLEŞTİR ürünün satır numarasını bulur.
2. İkinci EŞLEŞTİR ayın sütun numarasını bulur.
3. İNDEX bulunan koordinatların kesişimindeki hücreden değeri döndürür.

Hata yönetimi ve optimizasyon

Büyük veri kümeleriyle çalışırken, aranan değerin mevcut olmadığı durumları öngörmek önemlidir. EŞLEŞTİR öğeyi bulamazsa, tüm formül #YOK hatası döndürür.

Raporu temiz tutmak için yapıyı EĞERHATA işlevi içine alın:

=EĞERHATA(İNDEX(...); "Veri yok")

Performans önerileri:

• Tabloda 100 binden fazla satır varsa, EŞLEŞTİR için aralık olarak tüm sütunları (örneğin, A:A) belirtmekten kaçının. Bu, yeniden hesaplamayı yavaşlatır. Bunun yerine belirli aralıklar (A2:A5000) veya Excel akıllı tabloları kullanmak daha iyidir.
• Yeni Excel sürümlerinde (365, 2021) çok karmaşık seçimler için XLOOKUP (ARA) işlevini düşünebilirsiniz, ancak İNDEX + EŞLEŞTİR birleşimi eski dosyalarla uyumluluk açısından standart olmaya devam etmektedir.

Excel'de arama yöntemlerinin karşılaştırılması

Sık yapılan hatalar

• #YOK Hatası: Çoğunlukla metindeki fazladan boşluktan ("Ürün " yerine "Ürün") veya format farklılıklarından (sayı metin olarak yazılmış) kaynaklanır. Verileri temizlemek için KIRP işlevini kullanın.
• Aralık uyumsuzluğu: İki boyutlu aramada, İNDEX için aralığın yüksekliği satır arama aralığının yüksekliğiyle, genişliği ise sütun arama aralığının genişliğiyle eşleşmelidir.
• Unutulan eşleşme türü: EŞLEŞTİR içinde üçüncü bağımsız değişkeni belirtmezseniz (0 koymazsanız), işlev yaklaşık bir sonuç döndürebilir; bu, kesin veriler için kritiktir. Sonuna her zaman ; 0) yazın.

SSS

DÜŞEYARA'yı bu birleşimle değiştirebilir miyim? Evet, hatta bu tercih edilir. Birleşim, tablo yapısı değiştiğinde daha güvenilirdir ve anahtar sütunun solundaki değerleri aramanıza olanak tanır.

Formül neden değer yerine 0 döndürüyor? Hücre formatını kontrol edin. Sonucun metin olması gerekirken hücre sayı olarak biçimlendirilmişse (veya tam tersi), sıfır görüntülenebilir. Ayrıca kaynak verilerde gizli karakterler olup olmadığını da kontrol edin.

Bu Google E-Tablolar'da çalışıyor mu? Evet, INDEX ve MATCH işlevleri (İNDEX ve EŞLEŞTİR'in karşılıkları) Google Sheets'te aynı şekilde çalışır, sözdizimi tamamen uyumludur.