Excel VLOOKUP (DÜŞEYARA) Fonksiyonu ve Örnekleri
Önceki yazılarımda Excel’in neden bu kadar çok sık kullanıldığını ve sunduğu problem çözme yaklaşımının ne kadar pratik ve kullanışlı olduğundan bahsetmeye çalışmıştım. VLOOKUP fonksiyonu da bence Excel’i bu kadar değerli yapan en temel özelliklerden birisi. Minik bir veritabanı işleme yazılı gibi değişken referans bazlı arama işlemlerinde önceden hazırlanmış olan tablolardan her ne istersek çat diye çağırabiliyoruz. Nasıl mı? Öncelikle VLOOKUP’ın tanımını yapalım, daha sonra da örneklerde anlayalım ve birazcık derinlere inelim.
Tanım: VLOOKUP, soldan sağa işleme mantığı ile çalışan ve önceden hazırlanmış bir tablodan en soldaki değeri referans alarak dilediğimiz sütunu çağırabilmemizi sağlayan bir Excel fonksiyonudur.
Tanımlar, ne olduğunu bilmediğimiz şeyleri anlamamızda bazen yetersiz kalabilirler, ama ne olduğunu anladıktan sonra tekrar okunduklarında daha anlaşılır olurlar. Bu yüzden bir örnek üzerinden anlamaya çalışalım.
Örnek: Otomobil satın almak istiyoruz ama önümüzde o kadar çok seçenek var ki karar vermekte zorlanıyoruz. Alacağımız araç hem prestijli olmalı (marka), hem yüksek beygir gücü olmalı, hem de yakıt tüketimi az olmalı. Bunun yanısıra fiyatı ve yıllık vergisi de az olmalı (düşük motorlu olmalı). Araba markalarından, fiyatlarından ve motor ile beygir güçlerinden oluşan hayali bir tablo yapalım. Reklama girmesin diye de bütün marka ve modelleri hayali olarak kurgulayalım.
Bir markanın birden çok modeli olabileceği için hayali olarak 5 marka ve 10 adet model kurguladık. Artık tablonun herhangi bir yerinde VLOOKUP kullanarak modellerin dilediğimiz özelliğini çağırabiliriz, nasıl mı? Öncelikle VLOOKUP nasıl çalışır onu bir inceleyelim.
Excel VLOOKUP Nasıl Çalışır?
VLOOKUP fonksiyonu 4 parametre alarak çalışır. Bunlar:
Lookup_value: Aradığımız temel referans değer (tabloda en solda olmalı)
Table_array: Aramayı gerçekleştireceğimiz tablo
Col-index_num: Kaçıncı sütundaki değeri istediğimiz
Range_lookup: Tam olarak aradığımız değerin mi yoksa en yakın sonucun mu istendiği
Örnek üzerinden devam edelim. Yukarıdaki otomobiller tablosu içerisinden Model D’nin beygir gücünü öğrenmek istiyoruz. O zaman:
Burada dikkat etmemiz gereken bazı noktalar varç Bunlardan birincisi, table_array alanında tanımladığımız tablo sınırlarını sabitlememizde çok büyük fayda var, çünkü eğer bu vlookup fonksiyonunu bir çok alan için kullanmak üzere çekip uzatırsak referans tablomuz değişmeden aynı kalmalı. Daha detaylı bilgiler ve örnekler için Excel’de referans hücrelerin sabitlenmesi ile ilgili makaleme de göz atabilirsiniz. Tablo sabitleme konusunda pratik bir araç da tabloya isim vermek olabilir. Bunun hücre ile ilgili olan denemesini Excel Rasgele Sayı Üretme Yöntemleri makalesinde incelemiştik. Tablo için nasıl uygulanacağını da birazdan göreceğiz.
İkinci önemli nokta ise, uygulamaların tamamına yakınında aradığımız şeye tam olarak denk gelen lookup değerlerini görmek isteriz. Tabloda olmaması durumunda ise en yakın sonuç aslında bizim için aradığımız sonuç olmayacaktır. Bu sebeple range_lookup değerini çoğunlukla false olarak kullanmanız daha sağlıklı olacaktır.
Daha detaylı ve iç içe Vlookup sorguları barındıran bir örnek ile Vlookup fonksiyonunun ne kadar işlevsel olduğunu birlikte inceleyelim.
Excel VLOOKUP Örnekleri
Detaylı Örnek: Önceki örnekte kullandığımız kurgusal otomobil modellerini ve özelliklerini kullanarak araç seçimi yapmamızı sağlayan bir excel dosyası geliştirelim. Bu tabloda otomobilin her bir özelliği karar almamızda kendi oranında faktör olarak dikkate alınsın.
- Markası (listedeki bazı markalar daha prestijli olarak düşünelim)
- Beygir gücü (aracın ne kadar çok beygir gücü olursa o kadar iyi olacaktır)
- Motor gücü (motor gücünün az olması, daha az motorlu taşıtlar vergisi ödememizi sağlayacaktır)
- Yakıt Tüketim Değeri (Aracımızın yakıt tüketim değerlerinin az olmasını isteriz)
- Fiyatı (Aracın fiyatının az olması da en önemli kriterlerimizden birisi)
Tüm bu değerlerin, seçimimizde etki sağlayacak belirli ağırlık yüzdeleri olmalı. Örneğin karar verirken aracın fiyatı bizim için %40 öneme sahip iken beygir gücünün yüksekliği %10 öneme sahip olabilir.
Bunun için bir özellik önem indeksi yaratalım.
Peki hangi markalar daha prestijli? Bunun için de her bir marka için birer prestij değeri yazmamız gerekiyor. Örneğimizde 5 adet marka vardı.
Benzer şekilde 1200, 1600, 1800 ve 2000cc motorlar için de bir puantaj yapalım. Ne kadar düşük motorlu olursa o kadar yüksek puan olmalı.
Kalan kriterlerimiz için 2 farklı yaklaşım üretebiliriz. Bunlardan birisi aralıklar için puantaj üretmek, diğeri ise sıralamaya tabi tutarak puantaj üretmek. Sıralama için henüz bir tutorial yapmadığımız için aralıklar üzerinden birer puantaj yaratalım, böylece range_lookup değerinin true olması senaryosunu da örneklemiş olalım.
Peki her bir aracın puanını nasıl hesaplayacağız? Formül şu şekilde olacak:
(%10 x Marka Prestij Puanı) + (%10 x BeygirGücü Puanı) + (%20 x Motor Gücü Puanı) + (%20 x Yakıt Tüketim Değeri Puanı) + (%40 x Araç Fiyatı Puanı)
Mevcut excel tablomuzu da bütün olarak çözüme geçmeden inceleyelim.
Geldik problemin çözümüne. Öncelikle marka prestij puanını vlookup ile çağırarak başlayabiliriz.
Marka Puanı 1. adım: B15 hücresinde Model A’nın markasını çağıralım:
=VLOOKUP(A15;$A$2:$F$11;2;FALSE)
Marka Puanı 2. adım: Çağırdığımız markanın puanını, prestij değerleri tablosundan bulalım
=VLOOKUP(VLOOKUP(A15;$A$2:$F$11;2;FALSE);$H$10:$I$14;2;FALSE)
Biraz karışık olarak görünüyor olabilir ancak iç içe 2 vlookup kullanarak önce modelin markasını, sonra da ilgili markanın puanını bulmamız gerekiyor. Bu tip uygulamalar, gerçek işletme çalışmalarında da sıkça karşınıza çıkacaktır.
Not: Eğer bu işlemi yaptığınızda N/A hatası alıyorsanız, model sütunlarındaki değerleriniz bire bir eşleşmiyor ve vlookup tarafından karşılığı bulunamıyor demektir. Örneğin Marka 1 ile Marka1 terimleri, aralarındaki boşluk sebebiyle benzer bir hata üretebilirler.
Marka puanlarımızı bulduysak bunu bir görselle kutlayalım:
Gerçekten de Excel, vlookup sayesinde modelini bildiğim aracın önce markasını, sonra da markayı kullanarak ilgili puan değerini karşıma getirdi. Üstelik fonksiyon kullanmak dışında 1 satır dahi kod yazmadık.
Gelelim beygir güçlerine.. Burada çözmemiz gereken ek bir problem bar, örneğin Model H 94 beygir gücünde, ama beygir gücü puantajında 94 beygir için bir değer yok.
=VLOOKUP(VLOOKUP(A22;$A$2:$F$11;3;FALSE);K4:L13;2;FALSE)
İşte bu noktada, vlookup fonksiyonunun range_lookup değerini true yapma fırsatı bulacağız çünkü eğer sayısal bir değerin başka bir sayısal karşılığını arıyorsak (94’ün sayısal puan karşılığı) 90’ı kullanabilmeliyiz. Yukarıda yazdığımız formülde dışarıdaki vlookup input_range değerini TRUE olarak güncelleyelim.
Not: Bu işlemin çalışabilmesi için beygir gücü puantajı tablosunun da artan sırada güncellenmesi gerekir.
Bonus: Tablolara isim verebileceğimizden bahsetmiştik. Örneğin beygir gücü puantajı tablosunda değerlerimizi sakladığımız alana kısaca bgp adını verelim. Bu işlem için K4:L13 arası hücreleri seçerek aktive edelim ve sol üstteki hücre/tablo değerini yazan alana bgp yazalım. Artık bu alanı her seçtiğimizde bu alanda tablo adının yazdığını görebilirsiniz ve daha da güzeli artık bu tabloyu formüllerimizde kullanırken bgp yazmamız yeterli olacaktır.
Puan tablomuza dönelim ve kalan beygir gücü puanlarını hesaplayalım.
Formül (C15 hücresi için): =VLOOKUP(VLOOKUP($A15;$A$2:$F$11;3;FALSE);bgp;2;TRUE)
Diğer alanlar (C16:C24) için de bu formülü uzattığımızda:
Motor gücü puanının hesaplanması, tam karşılık değerleri elimizde zaten olduğu için marka puanı hesaplamasındaki gibi olacak:
D15 hücresi için formül: =VLOOKUP(VLOOKUP($A15;$A$2:$F$11;4;FALSE);$H$17:$I$20;2;FALSE)
Benzer şekilde yakıt tüketimi de en yakın değer olmadığı için beygir puanı hesaplaması ile aynı olacak:
E15 hücresi için formül: =VLOOKUP(VLOOKUP($A15;$A$2:$F$11;5;FALSE);$K$17:$L$21;2;TRUE)
Fiyat tüketimi formülünü de benzer şekilde yazabiliriz
F15 hücresi için: =VLOOKUP(VLOOKUP($A15;$A$2:$F$11;6;FALSE);$K$25:$L$30;2;TRUE)
Yüzdük yüzdük kuyruğuna geldik, bakalım verdiğimiz özellik önem endeksi oranlarına bağlı olarak hesapladığımız puanları orantılayarak topladığımızda kazanan kim olacak?
Toplam formülü G15 hücresi için: =$I$3*B15+$I$4*C15+$I$5*D15+$I$6*E15+$I$7*F15
80 beygirlik gücüne karşılık 68.000 TL fiyatı ile sorumuzun yanıtı Model G oldu. Elbette bunun sebebi, %40 oranında fiyata önem vermemiz oldu.
Karar değişikliği yaparak tasarruf yapmaktan vazgeçtiniz ve bunca hesabı en baştan yapmaktan mı korkuyorsunuz? Excel sayesinde tek yapmanız gereken önem endeksindeki değerleri güncellemek. Bırakın Excel gerisini halletsin..