Basitten Karmaşığa Düşeyara (Vlookup) Fonksiyonu Kullanımı

Öncelikle “ne işe yarar?” sorusu ile başlayalım. Aşağıdaki örnek incelendiğinde kullanım amacı daha iyi anlaşılacaktır ama yine de kısaca yaptığı işi anlatacak olursak, elimizde var olan bir değeri, başka bir tabloda bulup, karşısındaki bir bilgiyi getirmek için kullanılır. Aşağıdaki örnekte, Fatura’ya eklenmiş olan ürün kodları, Ürünler tablosunda aranarak, karşısındaki ürün adı ve fiyat bilgileri getirilecektir.

Düşeyara (Vlookup) fonksiyonu, Excel’in en çok kullanılan fonksiyonlarından birisidir. Bu fonksiyonu daha önce kullandıysanız, ilk adımlardaki basit uygulamayı atlayabilirsiniz. Yazının alt başlıklarında, Düzeyara (Vlookup) fonksiyonunu kullanırken dikkat edilmesi gereken hususlara ve farklı kullanım şekillerini bulabilirsiniz.

En Basit Kullanım Şekliyle Düşeyara (Vlookup)

Aşağıdaki resimlerde göründüğü gibi, Fatura Bilgileri ve Ürünler sayfalarını içeren bir Excel dosyamız olsun.

 

Fatura Bilgileri sayfasında, satılan ürünlerin kodları yazılarak bir fatura oluşturulmuştur. Burada yapmak istediğimiz, faturadaki ürün kodlarını, “Ürün Tablosu”nda aratarak, karşısındaki Ürün Adı ve Satış Fiyatını faturaya eklemektir. İlk ürün kodu olan KZ001 için Ürün Adını alalım:

1- Fatura Bilgileri sayfasında, KZ001 ürününün Ürün Adını görmek istediğimiz B3 hücresini seçiyoruz.

2- Arama ve Başvuru (Lookup & References) kategorisinden Düşeyara (Vloookup) fonksiyonunu seçiyoruz.

3- Düşeyara (Vlookup) fonksiyon penceresi karşımıza gelmiştir.

4- Aranan_değer (Lookup_value): Fatura Bilgilerinde KZ001 ürününün yazılmış olduğu hücre, bu örnekte A3,

Tablo_dizisi (Table_array): KZ001 ürününü arayacağımız tablo, bu örnekte ÜRÜNLER!A1:D15,

Sütun_indis_sayısı (Col_index_num): Ürünler tablosundan almak istediğimiz Ürün Adı bilgisinin bulunduğu sütun sırası, bu örnekte 2,

Aralık_bak (Range_lookup): Ürünler tablonuz Ürün Koduna göre sıralı değilse 0 ya da YANLIŞ (False) yazınız. Nedenini yazının devamında bulabilirsiniz.

5- Tamam düğmesine tıkladığınızda, Excel “Kazak” olarak ilk ürün ismini getirecektir.

Ürünün satış fiyatını almak istediğimizde, formülde tek değişmesi gereken Sütun_indis_sayısı (Col_index_num) alanına yazıla değerdir. Ürünler tablosuna tekrar dikkat edecek olursak, ilk işlemde Ürün Adını getiriyorduk ve Ürünler tablosunda 2. sırada yer alıyordu. Ürünün Satış Fiyatı ise Ürünler tablosunda 3. sırada yer alıyor. Bu nedenle Sütun_indis_sayısı (Col_index_num), Satış Fiyatını getirirken 3 olmalıdır.

Özet olarak aşağıdaki şekillerde açıklamaya çalışacak olursak,

Fatura Bilgileri sayfasında yer alan KZ001 ürün kodunu, Ürünler sayfasındaki tabloda ara, bulduğunda 2. sütunda yer alan Ürün Adını getir.

Düşeyara (Vlookup) Kullanırken Dikkat Edilmesi Gereken Hususlar

Düşeyara (Vlookup) fonksiyonu, seçilen tablonun sadece ilk sütununda arama yapar. Yukarıdaki örnekte, aradığımız Ürün Kodu, seçtiğimiz Ürünler Tablosunda ilk sütunda olmalıdır. Ürünler tablosunda Ürün Kodu ve Ürün Adı sütunlarının yerlerini değiştirirsek fonksiyon doğru sonuç vermeyecek ya da #YOK (#N/A) hatası verecektir.

Yazdığımız formülü diğer ürün isimlerini bulmak için kopyaladığımızda, bazı ürünler için #YOK (#N/A) hatası verecektir. Bunun sebebi, fonksiyonun Tablo_dizisi (Table_array) bölümünde seçilen Ürünler tablosunun, klavyeden F4 tuşuna basılarak sabitlenmemesidir. $ işaretleri kullanılarak Ürünler tablosu mutlak referans haline getirilmeli ya da Tablo özelliğinin kazandırılarak formülde Tablo adının kullanılması hatayı önleyecektir.

Sütun_indis_sayısı (Col_index_num), dönecek değerin seçilen tabloda kaçıncı sütunda olduğunu belirtir. Aşağıdaki şekilde yer alan tablodan Ürün Kodunu aratarak Ürün Adını almak istediğimizi varsayalım. Ürün Adı C sütununda yani sayfanın 3. sütununda yer almaktadır. Bu değer Düşeyara (Vlookup) fonksiyonu için önemli değildir. Fonksiyon için önemli olan, tablo Ürün Adı alanının tablo içerisindeki sırasıdır. Yani Sütun_indis_sayısı (Col_index_num) için kullanılması gereken değer 2’dir.

Aralık_bak (Range_lookup) 0 ya da 1 Yazmam Neyi Değiştirir?

Bu özellik, aradığımız değeri bulamadığında fonksiyonun ne yapacağına karar vermek amacıyla kullanılır.  0 yazdığımızda, aradığımız değeri bulamazsa #YOK (#N/A) hatası verecektir. 1 yazdığımızda ya da boş bıraktığımızda, aradığımız değeri bulamazsa, en yakın bir önceki değeri alacaktır. Bunu logaritma örneği ile açıklayacak olursak, aşağıdaki şekilde 10 tabanında logaritma 1..10 arası sayıların karşılığı gösterilmektedir.

4,7 rakamını Düşeyara (Vlookup) fonksiyonu ile A1:B10 aralığındaki tabloda aradığımızda, 4,7 sayısına en yakın bir üst değer olan 4 rakamının karşısındaki değer sonuç olarak dönmektedir.

Burada, tablonun A sütununa göre sıralanmış olduğuna dikkat ediniz. Peki neden? Tabloyu ilk sütuna göre sıralamam gerekli mi? Eğer Aralık_bak (Range_lookup) alanına 1 yazmışsak ya da boş bırakmışsak EVET, gerekli! Bu alana 1 yazdığımızda ya da boş bıraktığımızda, Excel formülün işleyişinde bir değişiklik yapar ve yarıya bölme metodu ile sonuca ulaşmaya çalışır. Bir örnek ile inceleyelim. Aşağıdaki şekilde B2 hücresinde yazılı olan “G” harfini arıyoruz. Düşeyara (Vlookup) fonksiyonun C2 hücresinde bize bulduğu değer ise “E” harfidir. Aradığımız “G” harfi A6 hücresinde olmasına rağmen Excel yanlış sonuç getiriyor.

Yukarıdaki şekilde fonksiyonun Aralık_bak (Range_lookup) alanına 1 yazıldığı görünmektedir. Excel bu durumda fonksiyonu şu şekilde çalıştıracaktır:

1. Adım: Tablonun tam ortasındaki değere git: B

2. Adım: Aradığım G harfi alfabetik olarak B’den sonra geldiği için, B’nin üst kısmında kalan değerleri atla, alt kısımda kalanların tam ortasındaki değere git: F

3. Adım: Aradığım G harfi alfabetik olarak F’den sonra geldiği için, Bve F’nin arasında kalan değerleri atla, F’nin alt kısımda kalanların tam ortasındaki değere git: E

4. Adım: Başka veri kaldı mı?: Hayır, Geldiğim E değeri alfabetik olarak aradığım G harfinden önce mi? Evet, E değerini döndür.

Eğer bu örnekte A8 hücresinde E yerine H olsaydı sizce ne dönerdi? Cevabı aşağıdaki şekillerde göstermeye çalıştım.

1. Adım: Tablonun tam ortasındaki B’ye git.

2. Adım: B’den sonra kalanların tam ortasındaki F’ye git.

3. Adım: F’den sonra kalan tek değeri yani H’yi kontrol et.

Buraya kadar üstteki örnek ile aynı işlemleri uyguluyor. Fakat son gelinen değerler, üstteki örnekte E (G’den alfabetik olarak önce), bu örnekte ise H (G’den alfabetik olarak sonra) harfidir. Bu işlemde gelinen son değer olan H harfi, alfabetik olarak G’den sonra olduğu için, bir önceki en küçük değer yani F dönecektir.

Kullandığım Örnek Dosyası

http://1drv.ms/1wYHZEV