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 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 |
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;” “;”_”))
