Bu yazımda İNDİS, KAÇINCI ve belirli kesişim ile aradığımız değeri bulma konusuna değinmek istiyorum. Daha önce kullandığım yöntemlerden biraz daha farklı olacak. Excel’in tanımlı isimlerine ve tablolarına da kullanacağım. Bu nedenle bu yazıyı okumadan önce bu konular hakkında yazdığım yazıları okumanız gerekmekte.

1. Örnek Değerler

Daha önceki örneklerde kullandığım tabloyu kullanacağım. Aşağıda tabloyu bulabilirsiniz.

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

Yine bir ek 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şturuyorum.

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.

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.

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 “2249” sicil numarasını 5. satırda buluyor. Şimdi ben ana tablonun, yani B3:F15 aralığının, 5 satırını, yani  B7:F7 aralığını, bütünüyle çekeceğim. Bunun için İNDİS işlevini kullanacağız:

=İNDİS($B$3:$F$15;KAÇINCI($H$3;$D$3:$D$15;0);)

Bu formül ile istediğimiz aralığı çektik. Bunu “Giriş Tarihi” sütunu ile kesiştirirsem istediğim değeri bulabilirim. Kesişim operatörü ” ” (boşluk karakteri) idi, “Giriş Tarihi” ise C3:C15 aralığında yer alıyor:

=İNDİS($B$3:$F$15;KAÇINCI($H$3;$D$3:$D$15;0);) $C$3:$C$15

Formülü yazar ve ENTER’a basarsanız aradığımız 2249 sicil numarasının “Giriş Tarihi” sütunu ile kesiştiği hücrede yer alan “15.04.2011” değerinin geri döndürüldüğünü görürsünüz. Bu formülü “Ad Soyad” değerini bulmak için aşağıdaki şekilde yazabiliriz.

=İNDİS($B$3:$F$15;KAÇINCI($H$3;$D$3:$D$15;0);) $B$3:$B$15

b. İsim Kullanımı

Bu formülde isim kullanımı çok daha anlaşılır ve hızlı oluyor. Bir önceki uygulamada yaptığım gibi ben burada da tüm tabloyu seçerek seçimden isim oluşturma seçeneği ile sütunlara, sütun başlıklarından oluşan isimler ekliyorum. Kaçıncı formülünü şu şekilde yazabiliriz:

=KAÇINCI($H$3;Sicil;0)

Sonrasında indis formülü:

=İNDİS($B$3:$F$15;KAÇINCI($H$3;Sicil;0);)

haline gelecektir. Kesişimi de yine isim ile yaparız, örnek olarak Giriş tarihini bulmak istiyorsak “Giriş_Tarihi” ile “Ad Soyad” bulmak isterseniz “Ad_Soyad” ismi ile kesişim alabilirsiniz:

=İNDİS($B$3:$F$15;KAÇINCI($H$3;Sicil;0);) Giriş_Tarihi
=İNDİS($B$3:$F$15;KAÇINCI($H$3;Sicil;0);) Ad_Soyad

b. Kesişimi Alt Tablo Başlığından Çekme

Kesişim yapılacak sütunu alt tablo başlığından çekmek için yine DOLAYLI işlevini kullanacağız ancak isimlerde boşluk karakteri yer alamaz, bunlar “_” (alt çizgi) ile değiştirilir. Bizde alt tablo başlıklarında yer alan boşlukları “_” ile değiştirmemiz gerek. Bunu da YERİNEKOY formülü ile yapıyoruz:

=YERİNEKOY(I$2;” “;”_”)

Bu formülü DOLAYLI ile birleştirdiğimizde elimize ilgili alanın tanımlı adı başvuru olarak gelecektir:

=DOLAYLI(YERİNEKOY(I$2;” “;”_”))

Şimdi bu formülü bir önceki formül ile birleştirdiğimizde artık kesişim yapılacak sütun, alt tablonun sütun başlıklarından alınacaktır:

=İNDİS($B$3:$F$15;KAÇINCI($H$3;Sicil;0);) DOLAYLI(YERİNEKOY(I$2;” “;”_”))

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

Leave a Reply