Bu yazımda DüşeyAra ile arama yaptığınız tabloya yeni bir sütun eklendiğinde hatalı değerin döndürülmesini nasıl engelleyebileceğimizi göstermek istiyorum. Tabii her derde deva KAÇINCI işlevinin de yardımını alacağım.

1. DüşeyAra ile Aranan Değeri Çekme

Elimizde aşağıdaki gibi bir ana tabomuz ve bu tablodan sadece belirli kısmı çekmek istediğimiz bir özet tablomuz olsun.

Özet tabloda sadece verim değerini bulmamız isteniyor olsun. Verim değeri ana tablonun baştan itibaren 4. sutununda. Bu veriyi ana tablodan çekmek için aşağıdaki formülü kullanabiliriz:

=DÜŞEYARA($G3;$B$3:$E$8;4,0)

Formülü aşağıya doğru uzattığımızda istediğimiz verim değerleri özet tabloya gelecektir. Oldukça kolay.

2. Ana Tablonun Değişmesi Durumu

Ancak siz bu güzel tabonuzu hazırladıktan sonra, bir iş arkadaşınız tabloda başka verilerin de olması gerektiğini düşünerek ana tablonuza yeni sütunlar ekledi diyelim. Örnek olarak çalışma saati olsun. Ve size haber vermedi. Siz de her zaman olduğu gibi raporunuzu hazırlamak üzere özet tablonuzu açtınız, o da ne,  yazdığınız güzel DÜŞEYARA formülü alakasız değerler gösteriyor. Bunun nedeni eskiden 4. sütunda olan Verim değerinin artık 5. sütuna kaymış olması.

Şimdi sizin tüm yazdığınız formülleri bir daha güncellemeniz gerek. Tabii burada tek bir DÜŞEYARA formülü var ve oldukça sade bir tabloda, onlarca sütundan oluşan çok daha büyük bir tabloda ve birden fazla DÜŞEYARA kullandığınızı düşünün. Hatayı görebilmeniz hem daha zor, hem de bunları düzeltmek zaman alıcı bir iş, ayrıca kontrol etmeden otomatik olarak bir yerlere yollamaya alıştı iseniz hatalı bir raporun gönderilmesi söz konusu olabilir.

3. Kaçıncı İşlevi ve DüşeyAra İçinde Kullanılması

Bunu aşmak için sabit sütun numarası kullanmamamız gerek. Bunun yerine sütun numarasını döndüren bir işlev kullansak daha avantajlı olur. Tabii ilk olarak ana tablonun da girilen yeni sütunlara göre genişlemesini sağlamamız lazım ki arama her zaman genişleyen güncel alan üzerinde yapılsın. O yüzden ben ana tabloyu seçip Shift+T tuşları ile Excel Tablosu haline getireceğim ve sonrasında isim yöneticisinden bu alanın adını AnaTablo olarak değiştireceğim. Bu sayede tablo başlıklarının olduğu alana TabloAdı[#Başlık] adı ile erişebilirim.

İkinci olarak aradığım “Verim” değerinin bu başlık alanında kaçıncı sırada bulunduğunu bulmak. Bunu da KAÇINCI işlevi ile yapacağız:

=KAÇINCI($I$2;AnaTablo[#Üst Bilgiler];0)

Formül 5 değerini döndürecektir. Aradığım değer tablonun 5. sütununda demek. Şimdi bunu DÜŞEYARA ile birleştirelim:

=DÜŞEYARA($H3;AnaTablo;KAÇINCI($I$2;AnaTablo[#Üst Bilgiler];0);0)

Artık ana tablonuza ne kadar sütun eklenirse eklensin her zaman aradığınız değeri döndüren bir DÜŞEYARA formülünüz oldu.

3. Notlar

Bu yöntem sayesinde DüşeyAra formüllerinizde hata oluşma ihtimalini ve sürekli müdahale edilme gerekliliğini ortadan kaldırabilirsiniz. Hatta YatayAra işlevinde de kullanabilir ve aynı şekilde hata olasılığını düşürebilirsiniz.

Her ne kadar her zaman kullandığımız DüşeyAra formülüne göre yazılması biraz daha fazla zaman alıyor olsa da sağladığı faydalara baktığımızda kesinlikle kullanılmasına gerek olan bir yöntem olduğunu düşünüyorum.

Tabii bu yöntemin tüm hataları engellemesi beklenemez. Özellikle tablo başlıklarınız değişir ise bunu da engelleyecek yöntemler geliştirmelisiniz. Tablo başlıklarının değiştirilmeye karşı kilitlenmesi veya alt tablonuzdaki başlıkların da ana tablodaki başlıklardan çekilmesi gibi.

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

Leave a Reply