Selamlar,

Uzun süredir kafamda excelde günlük hayatımda en çok kullandığım fonksiyonları burada anlatmak vardı. Aslında çok kolay fonksiyonlar ama hemen hemen hergün 10larca kez kullanıyorum. Ne yazıkki çevremdeki bir çok insan bilmedikleri için Exceli açmıyorlar bile. Belki bu yazı onlara yardımcı olabilir :

DÜŞEYARA()

Düşeyarayı veritabanında yazdığını SQL Select sorgusu gibi düşünebilirsiniz. Belli bir tablo içinden anahtar ile bir satır arıyorsunuz ve o satırdaki herhangi bir kolonun değerini dönüyorsunuz.

=DÜŞEYARA(ArananDeğer , Arama Yapılacak Alan, Dönülecek Kolon Numarası , Yakın Eşleşmemi Tam Eşleşmemi)

ArananDeğer kısmı hangi değeri arıyorsunuz, yani SQLde WHERE ID=”$$$” dediğiniz kısımdaki $$$

Arama Yapılacak Alan kısmı ise hangi veriler arasında arayacaksınız. Bunu SQL deki tablo adı olarak düşünebilirsiniz. Ama excelde alan seçmeniz gerekiyor(yada daha önce Adlandırdığınız alanı belirtmeniz). Burdaki önemli nokta şu, düşeyara fonksiyonu aranacakdeğeri, belirtilen alanın en sol kolonunda bakıyor. Yani siz A1:D5 gibi bir alan seçtiğinizde, aranacak değerleri A1,A2,A3,A4,A5 kolonunda olmalı. Eğer anahtarınız B kolonunda ise B1:D5 olarak alan belirtmeniz lazım (tabi bu şekilde A kolonundan bilgi alamazsınız, eğer A kolonundan bilgi almanız gerekiyorsa, B kolonu ile A kolonunun yerini değiştirip A1:D5 olarak aratın)

Dönülecek Kolon Numarası, arama yapıldıktan sonra hangi kolonun değerinin yazdırılacağını belirtiyor. Yani siz A1:D5 alan seçtiyseniz ve dönülecek kolon numarasını 1 yaparsanız size bulunan satırın A kolonunun değerini döner. 3 yazarsanız C kolonunu döner. Eğer 6 yazarsanız Boş döner çünkü siz D kolonuna(Max 4) kadar alan seçmiştiniz. 6 girerseniz A+5 = F kolonunu kastetmiş olursunuz. Ki oda seçili değil.

Yakın eşleşmemi tam eşleşmemi kısmına DOĞRU veya YANLIŞ yazabiliyorsunuz. Bunu her zaman YANLIŞ olarak belirtin. YANLIŞ derseniz tam eşleşme gereksinimi olduğunu söylüyorsunuz. Yani eğer aranan değeri bulamazsa #YOK diye uyarı verecektir. Eğer DOĞRU derseniz, aranan değer olmasa bile ona en yakın değeri bulacaktır. Ki buda genelde sizin istemediğiniz bir durum. O yüzden elinizi YANLIŞ yazmaya alıştırın.

Düşey Ara yaparken sık sık karşılaşılan bir durum var. Birden fazla kolon anahtarı oluşturuyorsa DÜŞEYARA fonksiyonu yetersiz kalıyor. Yani şöle bir sql düşünün SELECT ADRES FROM TABLO WHERE IL=’06′ AND ILCE =’343′. Burda WHERE kısmında iki koşul koyuyorsunuz. Yukarıda gördüğünüz gibi DÜŞEYARA sadece tek koşul alıyor, oda belirtilen alanın en soldaki kolonu). Bu tarz birden fazla kolon koşulu olduğu durumlarda en kolay yöntem kendi anahtarınızı oluşturmak.

Örnek olarak 3 kolonlu bir exceliniz olduğunu düşünün. Plaka İl,Müşteri Tipi,Değer. Amacınız Plaka ili ve Müşteri tipi belirterek hangi değer aldığını bulmak. Yapmanız gereken bir kolon açıp (değerin sol tarafında olsun) iki anahtar alanınızı birleştirip yeni bir değer kümesi oluşturmak. Bunun için yeni açtığınız kolona =A1&B1 yazıp aşağı sürükleyebilirsiniz. Böylece iki kelimenin birleşimi yazacaktır. Fakat benim kişisel önerim =A1&B1 yerine =A1&”|”&B1 gibi bir şey yazmak. Yani birleştirme esnasında araya bir karakter koymak. Nedeni şu : Mesela ilk kolon 1den 20ye kadar sayılar, ikinci kolon 0 dan 10 a kadar olan sayılar(bunların metin olduğunu düşünün). Siz bunları =A1&B1 şeklinde birleştirirseniz iki tane 110 elde edersiniz (1+10 ve 11+0 (metin olduğu için direk karakter karakter birleştirdim. Sayısal olsa dahada curcuna)). Bu şekilde bir anahtar oluşturduğunuz aslında unique lik özelliğini kaybediyorsunuz. Bu tür bir sorunla karşılaşmamak için =A1&”|”&B1 tarzında birleştirirseniz 1|10 ve 11|0 gibi farklı anahtarlar oluşacaktır(seçiceğiniz karakterin anahtarlın içinde olmamasına dikkat edin. | karakteri genelde pek bulunmaz o yüzden onu seçmiştim).

DOLAYLI()

Dolaylı fonksiyonuda bir çok zaman ihtiyacınız olacak fonksiyon. Her ne kadar ismi çok amacını anlatmasada, bu fonksiyonu belirli bir hücrenin değerini, hücre adresini isim olarak yazarak bulmaya yarıyor. Yani B1 kolonuna gelip =DOLAYLI(”A1″) yazarsanız size A1 deki değeri yazar. Oldukça kullanışlı çünkü içerisi kelime olarak girildiğinden =DOLAYLI(”D”&(SATIR()+12)) gibi alt fonksiyonlar yazabilirsiniz. Kolay ve oldukça kullanışlı bir fonksiyon

ADRES()

Adres fonksiyonu tek başına anlamlı olmasada DOLAYLI ile birlikte çok kullanacaksınız. Amacı belirtilen satır numarası ve sütun numarası için size o hücrenin Excel adresini vermek. Mesela 1 1 verirseniz size A1 yazıcaktır. 44 22 derseniz V44 ismini dönecektir. Bunun bazı alt parametreleri var ($ yazsınmı yazmasınmı gibi). Bunları açıkçası hiç kullanma gereği duyduğumu hatırlamıyorum. Ama bir iki deneme yaparak sizde çözebilirsiniz.

SATIR() ve SÜTUN()

Satır ve sütun fonksiyonları adından da anlaşılacağı gibi bulunduğu hücrenin satır veya sütun numaralarını verir. Mesela A4 kolonuna =SATIR() yazarsanız 1, =SÜTUN() yazarsanız 4 değerini verecek. Bunu yukarıdaki fonksiyonlarla kullanarak ilginç denemeler yapabilirsiniz. Mesela bir satırın sağ altındaki değeri bulmak için =DOLAYLI(ADRES(SATIR()+1;SÜTUN()+1)) yazabilirsiniz.

Aklıma geldikçe başka fonksiyonlarıda yazmaya çalışırım ama dediğim gibi bu fonksiyonlar günlük ihtiyacınızın %80 ini karşılayacaktır. Yukarıda anlattıklarımı içeren örnek excel dokümanını da inceleyebilirsiniz.

Örnek Excel Dosyası