KAYDIR (OFFSET) ve KAÇINCI (MATCH) İkilisiyle İki Yönlü Dinamik Arama

Selam! 

Excel’de veri arama denince çoğumuzun aklına önce DÜŞEYARA (VLOOKUP) fonksiyonu gelir. Gerçekten de Excel’in en güçlü araçlarından biridir. Ancak, raporlama sırasında veya geniş ve karmaşık veri tablolarında hem satır hem de sütun ölçütlerine göre veri çekmek gerektiğinde, DÜŞEYARA’nın kapasitesi sınırlı kalır. Özellikle " Bu ay, şu bölgede, o ürünün satış miktarı kaçtı? " gibi iki farklı kritere bağlı sorulara tek başına DÜŞEYARA ile yanıt bulmak mümkün değildir.

Tam da bu noktada, KAYDIR (OFFSET) ve KAÇINCI (MATCH) fonksiyonları imdadımıza yetişir. Bu iki fonksiyonu birlikte kullanarak, veri tablolarında esnek ve çift yönlü aramalar gerçekleştirebilirsin. Bu yazıda, bu iki fonksiyonun nasıl birlikte kullanılacağına dair pratik bilgileri bulacaksın.

Haydi başlayalım.

Tablolardaki Navigasyon Aracımız KAÇINCI (MATCH) Fonksiyonu

İki yönlü aramanın ilk ve en kritik adımı, aradığın verinin tablonun neresinde olduğunu bulmak. KAÇINCI fonksiyonu tam olarak bunu yapıyor. Bir değerin kendisini değil, o değerin bulunduğu sıranın / konumun numarasını döndürerek.

Bu neden Önemli? Çünkü KAYDIR fonksiyonu (bir sonraki fonksiyonumuz) hareket etmek için bu konum numaralarına ihtiyaç duyuyor.

KAÇINCI

1. Satır Konumu: Aradığın Ürün Adı'nın veri aralığında kaçıncı sırada olduğunu bulursun.
2. Sütun Konumu: Aradığın Ay/Bölge Adı'nın başlık satırında kaçıncı sütunda olduğunu bulursun.

İpucu: Eğer D3 hücresine =KAÇINCI("Mart"; B1:E1; 0) yazarsan, sonuç 3 olur. Neden? Çünkü "Mart", B1:E1 aralığının 3. sütunudur. Unutma, KAÇINCI'da Eşleştirme_Türü mutlaka 0 (Tam Eşleşme) olmalıdır.

Dinamik Başvuru İçin KAYDIR (OFFSET)

KAYDIR, Excel'deki en güçlü ancak en az anlaşılan fonksiyonlardan birisidir. En basit haliyle : Bir başlangıç noktasından (referans), belirli sayıda satır aşağı ve belirli sayıda sütun sağa hareket ederek yeni bir hücreye veya hücre aralığına dinamik olarak başvurmanızı sağlar.

KAYDIR'ın yapısı şöyledir: =KAYDIR(Başvuru; Satırlar; Sütunlar; [Yükseklik]; [Genişlik])

Burada meselemiz, Satırlar ve Sütunlar argümanlarına sabit sayılar yazmak yerine, başka bir fonksiyonun (KAÇINCI'nın) sonucunu yerleştirmek.

• Başvuru (A1): Hareket edeceğin başlangıç noktası.
• Satırlar: KAÇINCI'dan gelen satır konumu.
• Sütunlar: KAÇINCI'dan gelen sütun konumu.

Bu sayede, arama kriteri (örneğin ay adı) değiştiğinde, KAÇINCI hemen yeni koordinatı hesaplar ve KAYDIR bu koordinata kayarak doğru veriyi getirir!

İki Yönlü Arama İçin Formülleri Birleştirme

Şimdi, muhasebeci veya satış yöneticisi olarak senin için önemli olan o sonuca ulaşalım: Şimdi KAYDIR ve KAÇINCI'yı tek bir formülde birleştiriyoruz.

Senaryo : Yukarıdaki tablodan, A2 hücresindeki "LAP001" kodlu ürünün, C2 hücresindeki "Mart" ayındaki satış adedini otomatik olarak çekmek.

Adım Adım Gidelim

Başlangıç Noktası (Referans):

• Veri tablomuzun sol üst köşesini (A1) başlangıç noktası olarak seç. KAYDIR fonksiyonu bu noktadan itibaren kaydırma yapacak.
• Argüman: A1 (KAYDIR işlevinin referans aldığı köşe.)
• Sonuç: A1

Satır Kaydırma Miktarı (Ürün Kodunu Bulalım):

• "LAP001" ürün kodunun, ürün kodlarının bulunduğu A2:A4 aralığında kaçıncı sırada olduğunu bulmak için KAÇINCI fonksiyonunu yazdık.
• Argüman: KAÇINCI("LAP001"; A2:A4; 0) (A2:A4 aralığında "LAP001" değeri kaçıncı sırada tam olarak eşleşiyor?)
• Sonuç: 1 (Çünkü "LAP001", A2:A4 aralığının 1. sırasında)

Sütun Kaydırma Miktarı (Ay Bulalım):

• "Mart" ayının, başlıkların bulunduğu A1:E1 aralığında kaçıncı sırada olduğunu buluruz. Ancak KAYDIR fonksiyonu A1'den başladığı için, çıkan sütun sonucundan 1 çıkarmamız gerekmektedir. Çünkü A1, başvuru noktası olarak 0. sütun gibi kabul edilir.
• Argüman: KAÇINCI("Mart"; A1:E1; 0) - 1
• Açıklama: A1:E1 aralığında "Mart" değeri kaçıncı sırada tam olarak eşleşiyor ve KAYDIR'ın referans noktasına göre doğru sütun kaydırma sayısını bulmak için 1 çıkar.
• KAÇINCI("Mart"; A1:E1; 0) sonucu 4'tür (A1 "Ürün Kodu", B1 "Ocak", C1 "Şubat", D1 "Mart" -> Mart 4. sıradadır).
• 4 - 1 = 3. Bu, A1'den itibaren 3 sütun sağa kaydırmamız gerektiğini gösterir.
• Sonuç: 3 (3. sütun)

Formül

=KAYDIR(A1; KAÇINCI("LAP001"; A2:A4; 0); KAÇINCI("Mart"; A1:E1; 0)-1)

Sonuç Özeti:

Not : Eğer adlandırılmış aralıklar kullanırsan, formüllerin çok daha okunur hale gelir. Örneğin, A2:A4 aralığına "Ürünler", A1:E1 aralığına "Aylar" adını vererek formülü şuna dönüştürebilirsin.

 =KAYDIR(A1; KAÇINCI(H2; Ürünler; 0); KAÇINCI(J2; Aylar; 0))

Okuması ve hata ayıklaması çok daha kolay olur.

 Artık Veri Analizinde Bir Adım Öndesin!

Geleneksel DÜŞEYARA fonksiyonunun sınırlarını aşan, matrislerde iki yönlü ve dinamik arama kabiliyetine sahip bir Excel kullanıcısı olarak, KAYDIR ve KAÇINCI fonksiyonlarının bir arada kullanımı sayesinde yalnızca veri çekmekle kalmaz, aynı zamanda raporlama süreçlerinde büyük bir esneklik ve hız kazandırırsınız. Bu güçlü kombinasyon, veri analizinde sizi bir adım öne taşır ve karmaşık tablolar üzerinde dahi pratik çözümler sunar.

İlgili Linkler:

Arama ve Başvuru Fonksiyonları

DÜŞEYARA

KAYDIR

İNDİS-KAÇINCI