Bu yazımda KAÇINCI, İNDİSKAYDIR 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 SoyadGiriş TarihiSicilBölümGörev
Hande Kılıç14.04.20102730ÜretimMüdür
Necmiye Çalışkan09.05.20103143SatışMüdür
Basri Dalgıç13.05.20103662FinansMüdür
Mehmet Kiraz14.04.20112532šretimMühendis
Ahmet Özhan15.04.20112249FinansMuhasebeci
Hasan Topçu24.03.20124967PlanlamaMühendis
Niyazi Yıldız11.06.20133953BakımBakım Şefi
Kenan Demirci13.07.20134386MuhasabeMüdür
Merve Meşhur24.07.20134205ÜretimMühendis
Zeynep Çığırtkan16.01.20152562BakımMühendis
Emin Nizami26.01.20153204PlanlamaMühendis
Berra Karaküp07.12.20152914SatışMühendis
Mustafa Demir24.10.20162855MuhasabeMuhasebeci

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.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , ,

Leave a Reply