Bu yazımda KAÇINCI, İNDİS, KAYDIR ile bir tabloda aradığımız değerin solunda veya sağında yer alan başka bir değeri nasıl çekebileceğimizi göstereceğim. Bu yazıya başlamadan önce bu iki işleve hakim olduğunuzu varsayıyorum, eğer bu işlevler hakkında bilginiz yoksa önce bu işlevlerle ilgili yazılarımı okuyun lütfen.
1. Örnek Değerler
Bana tüm değerlerin yer aldığı bir ana tablo ve sadece istediğim değerleri çekeceğim bir alt tablo lazım. Ben aşağıdaki tabloyu B2 hücresinden itibaren oluşturdum.
| Ad Soyad | Giriş Tarihi | Sicil | Bölüm | Görev |
|---|---|---|---|---|
| Hande Kılıç | 14.04.2010 | 2730 | Üretim | Müdür |
| Necmiye Çalışkan | 09.05.2010 | 3143 | Satış | Müdür |
| Basri Dalgıç | 13.05.2010 | 3662 | Finans | Müdür |
| Mehmet Kiraz | 14.04.2011 | 2532 | retim | Mühendis |
| Ahmet Özhan | 15.04.2011 | 2249 | Finans | Muhasebeci |
| Hasan Topçu | 24.03.2012 | 4967 | Planlama | Mühendis |
| Niyazi Yıldız | 11.06.2013 | 3953 | Bakım | Bakım Şefi |
| Kenan Demirci | 13.07.2013 | 4386 | Muhasabe | Müdür |
| Merve Meşhur | 24.07.2013 | 4205 | Üretim | Mühendis |
| Zeynep Çığırtkan | 16.01.2015 | 2562 | Bakım | Mühendis |
| Emin Nizami | 26.01.2015 | 3204 | Planlama | Mühendis |
| Berra Karaküp | 07.12.2015 | 2914 | Satış | Mühendis |
| Mustafa Demir | 24.10.2016 | 2855 | Muhasabe | Muhasebeci |
Bir de bilgileri çekeceğim ek bir alt tabloya ihtiyacım var. Ben sadece Sicil, Giriş Tarihi ve Ad Soyad başlıklarından oluşan 2 satırlık bir tabloyu H2:J3 aralığında oluşturdum.
Eğer siz ana ve alt tablonuzu farklı yerlerde oluşturursanız formüllerde yer alan hücre adreslerini buna göre değiştirin lütfen.
Ben alt tabloda sicil bilgisini aratacağımdan buraya bir de “Veri Doğrulama”, “Liste” seçeneği ile ana tablonun sicil sütunundan bir liste tanımladım. Ve sicil numarası olarak 2249 rakamını seçtim.
2. Uygulama
a. Temel Formül
Aradığım değer alt tabloda H3 hücresinde, arayacağım aralık ise D3:D15 aralığı. Hangi satırda olduğunu bulmak için hemen KAÇINCI işlevini kullanacağız:
=KAÇINCI($H$3;$D$3:$D$15;0)
İşlev bu değeri D3:D15 aralığının 5 elemanında buldu ve 5 değerini döndürdü. Ancak bana daha sonra kullanmak üzere hücrenin başvuru adresi lazım, bunu bulmak için ise İNDİS işlevini kullanacağız:
=İNDİS($D$3:$D$15;KAÇINCI($H$3;$D$3:$D$15;0);)
Bu formül de 5 değerini döndürecek ancak ufak bir farkla, önce hücre adresini bulacak, sonra hücre içindeki değeri gösterecek.
Şimdi aradığım “Giriş Tarihi” değeri tabloda “Sicil” sütunundan 1 sütun önce, “Ad Soyad” ise 2 sütun önce. Bu sütunlardaki değeri çekmek için bulduğunuz hücreden sırası ile bir ve iki sütun sola kaymamız gerek. KAYDIR işlevi de zaten bunu yapıyor. I3 ve J3 hücrelerine sırası ile aşağıdaki formüllerini yazarak Sicil değerini bulduğum tabloda sırası ile 1 ve 2 sütun soldaki değerleri çekiyorum:
=KAYDIR(İNDİS($D$3:$D$15;KAÇINCI($H$3;$D$3:$D$15;0););0;-1;1;1)
=KAYDIR(İNDİS($D$3:$D$15;KAÇINCI($H$3;$D$3:$D$15;0););0;-2;1;1)
Formüller sırası ile aradığımız değerleri döndürecektir.
b. Kaydır İşlevini Otomatikleştirme
Bir önceki aşamada tamamladığımız formül, tablo sabit kalırsa sorun çıkarmadan çalışacaktır. Ancak ana tabloya yeni sütunlar eklenirse hatalı değer döndürecektir. O yüzden “KAYDIR” kısmını biraz otomatiğe bağlamak istiyorum.
Yapmak istediğim şey aradığım değer sütununun tabloda kaçıncı sütun olduğunu bulacağım, sonrasında aynı işlemi Sicil değeri için yapacağım ve bulduğum değerleri birbirinden çıkaracağım. Tabii bu değerleri formüle el ile girmek yerine alt tablo başlığından çekersem daha da iyi olur. İlk aradığım değer Giriş Tarihi, alt tablonun I2 hücresinde, aradığım yer de ana tablonun başlıkları B2:F2 aralığı. Buna göre formül:
=KAÇINCI(I$2;$B$2:$F$2;0)
İkincisi ise Sicil değeri:
=KAÇINCI($H$2;$B$2:$F$2;0)
Şimdi bu iki değerin farkını alalım:
=KAÇINCI(I$2;$B$2:$F$2;0)-KAÇINCI($H$2;$B$2:$F$2;0)
Bu ikisinin farkı bana -1 değerini verecek. Bu formülü bir önceki formüldeki sütun değerinin yanına ekleyeyim:
=KAYDIR(İNDİS($D$3:$D$15;KAÇINCI($H$3;$D$3:$D$15;0););;KAÇINCI(I$2;$B$2:$F$2;0)-KAÇINCI($H$2;$B$2:$F$2;0);1;1)
Bu sayede hem ana tabloya yapılacak eklemelerde etkilenmeyen hem de alt tablo başlıklarına göre arama yapan bir formülümüz oldu. Formülü yana doğru uzattığımızda da formül sonucu alt tablo başlığına göre bulacağından, teknik olarak ana tablodaki istediğiniz başlığı alt tabloya ekleyerek istenen değeri bulmayı sağlayabilirsiniz.
