Okurlarımızdan gelen Excel soruları, özellikle bir tablo dolusu veriden istedikleri şartlara uyan bir hücreyi seçmek konusunda bazı okurlarımızın sorunları olduğunu gösteriyor. Bu ay hem DÜŞEYARA hem de EĞER formüllerini iç içe kullanarak tablolardan değer seçmek konusuna bir parça ışık tutmak istiyorum. Öncelikle elimizde yan taraftaki resimde görüldüğü gibi bir hesap tablosu olduğunu varsayalım.
Bu tablomuzda, amacımız bir kişinin kilosuna ve cinsiyetine göre bir katsayı tablosundan uygun bir katsayı bulmak ve kilosu ile bu katsayıyı çarparak bir değer elde etmek ve bunu cümle içerisinde kullanmak. Örnek resimde görüldüğü gibi belirli kilo aralıklarında bay ve bayanlar için değişik katsayıların olduğu bir tablomuz var. B2:D5 arasında bu tablo yerleştirilmiş durumda.
Hemen altındaki satırlarda bir hazır tablo hazırlanmış. B7:H12 arasında yer alan bu tabloyu da soldan sağa doğru inceleyelim. Birinci sütunda hesaplama yapılacak olan kişinin ismi ve soyadı yazılıyor, bu isim ve soyadı daha sonra cümle içerisinde kullanılacak. İkinci sütunda ismi yazılan şahsın cinsiyeti yazılıyor, çünkü bay ve bayanlar için katsayı tablosunda değişik değerler kullanacağız. Eğer cinsiyet erkek ise E, bayan ise K yazıyoruz. Üçüncü sütunda ise ismi ve cinsiyeti yazılmış olan şahsın kilosunun yazılması gerekiyor. Bu kilo hesaplama için kullanılacak. Tablonun elle veri girilecek kısımları sadece bu kadar. Bundan sonrası, yani dört, beş, altı ve yedinci sütunlardaki değerler Excel tarafından otomatik olarak hesaplanıp yazılıyor. Böylece bir çok işlem otomatikleştirilmiş oluyor. Bu tür tablolar oldukça kullanışlı, özellikle bir dizi müşterinin kaydını tutan firmalarda bu tür özel formüller kullanılarak pek çok şeyi Excel ile halletmek mümkün.
Şimdi sırayla hesaplamaların nasıl yapıldığına göz atalım. Dördüncü sütunda
=EĞER(EBOŞSA(C8);"--";EĞER(D8<51;"0-50";EĞER(D8<101;"51-100";"101-150")))
formülü kullanılmış. Burada karşılaştırma şu şekilde yapılıyor:
1. Eğer C8 hücresi (yani cinsiyet) boşsa kategori alanına "--" yaz.
2. Eğer C8 hücresi boş değilse işin içine bir başka karşılaştırma silsilesi giriyor.
3. Eğer D8 hücresindeki değer (yani kilo) 51'den küçükse kategori alanına "0-50" yaz.
4. Eğer D8 hücresindeki değer 51'den büyükse bir formül daha giriyor işin içine.
5. Eğer D8 hücresindeki değer 101'den küçükse kategori alanına "51-100" yaz.
6. Eğer D8 hücresindeki değer 101'den büyükse kategori alanına "101-150" yaz.
Örnek resimde de görülmüş olduğu gibi, C8 hücresi boş değil, D8 hücresinde 75 yazıyor, bu yüzden de E8 hücresine otomatik olarak Excel tarafından "51-100" yazılıyor.
Evet, E8 hücresine bu formülü girerek, ismi yazılan kişinin kilosunun hangi sınır aralığında bulunduğu tespit etmiş olduk. Şimdi bu sınır aralığını ve cinsiyetini kullanarak bu kişi için üst taraftaki standart katsayı tablosundan bir katsayı seçmek gerekiyor. Bu katsayıyı da F8 hücresine yazacağız. Bu durumda F8 hücresinde kullanmamız gereken formül şöyle oluyor:
=EĞER(EBOŞSA(C8);"--";DÜŞEYARA(E8;$B$2:$D$5;EĞER(C8="E";2;3);YANLIŞ))
Bu formülü de adım adım soldan sağa açalım.
1. Eğer C8 hücresi (yani cinsiyet) boşsa kategori alanına "--" yaz.
2. Eğer C8 hücresi boş değilse işin içine bir başka karşılaştırma silsilesi giriyor.
3. E8 hücresindeki değeri (yani sınır aralığını) $B$2;$D$5 tablosu içerisinde yukarıdan aşağıya ara
4. Bu değeri bulduğun satırdan eğer C8 hücresinde E yazıyorsa ikinci değeri, aksi takdirde üçüncü değeri seç ve F8 hücresine yaz.
Bu formül Cenk TARHAN satırı için şöyle işliyor. Kilo 75 ve cinsiyet de erkek olduğuna göre önce sınır aralığı hesaplanıyor. Sınır aralığının 51-100 arasında olduğu bulunuyor. Daha sonra katsayı tablosundan 51-100 sınır aralığı için bayan ve erkek değerlerinin yazıldığı satır bulunuyor (bay için 4,2 ve bayan için 3,4). C8 hücresi yani cinsiyet kodu kontrol ediliyor, burada E yazdığına göre tablonun 2. sütundaki değer, yani 4.2 F8 hücresine yazılıyor.
Bu formülde katsayı tablosuna $B$2;$D$5 şeklinde referans vermemizin nedeni, formülü diğer satırlar için aşağı doğru kopyaladığımızda tablo referansının kaymaması, sabit kalması içindir. Tabloyu seçili duruma getirip bir alan adı tanımlayarak da aynı işlemi yapabiliriz. Bu durumda $B$2;$D$5 değeri yerine tanımlamış olduğumuz alan adını kullanmamız mümkün olacaktır.
Gelelim H8 hücresindeki uzun cümlenin nasıl otomatik olarak oluşturulacağına. Burada
=EĞER(EBOŞSA(C8);"--";EĞER(C8="E";BİRLEŞTİR("Bay ";B8;" için kullanmamız gereken kalori miktarı ";G8;" olacaktır.");BİRLEŞTİR("Bayan ";B8;" için kullanmamız gereken kalori miktarı ";G8;" olacaktır.")))
formülü kullanılıyor. Uzun görünmesine rağmen aslında çok basit bir formüldür. Aşağıdaki gibi açabiliriz:
1. Eğer C8 hücresi (yani cinsiyet) boşsa kategori alanına "--" yaz.
2. Eğer C8 hücresi boş değilse işin içine bir başka karşılaştırma silsilesi giriyor.
3. Eğer C8 hücresinde E yazıyorsa cümleyi Bay Cenk Tarhan (B8 hücresindeki değer) için kullanmamız gereken kalori miktarı 315 (G8 hücresindeki değer) şeklinde oluştur.
4. Aksi takdirde yine aynı formülü kullan ancak cümlenin başında "Bay" yerine "Bayan" yaz.
Bir alt satırda da gördüğünüz gibi, aynı formül bir Bayan için uygulandığında değişik sonuç veriyor. Tabloyu oluşturmak isteyen bilgisayar kullanıcısının yapacağı tek şey kişinin adını, kilosunu ve cinsiyetini yazmak. Gerisini Excel onun için hallediveriyor. Yukarıdaki örneğin çalışan bir Excel dosyası halini incelemek istiyorsanız http://www.pcmagazine.com.tr adresinden erişebileceğiniz ailenizin Web sitesi PC Magazine Online'a gelin, dergide adı geçen dosyalar bölümünde Excel dosyasını KATSAYI.XLS adında sizi bekler bulacaksınız. Örnek dosyayı kullanarak DÜŞEYARA ve EĞER formülleri ile denemeler yapıp kendi özel otomatik tablolarınızı da yaratabilirsiniz.