27.10.2017
5.00 / 1 oy

Excel VLOOKUP Kullanımı ve Örnekleri

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.

Excel VLOOKUP Fonksiyonu

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:

Excel VLOOKUP Ekranı

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:

Excel VLOOKUP tablo

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.

Excel VLOOKUP referans

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ı.

Excel VLOOKUP değer bulmak

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ı.

Excel VLOOKUP tablosu

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.

Excel VLOOKUP tabloları

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.

Excel VLOOKUP örneği

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:

Excel VLOOKUP sorusu

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)

Excel VLOOKUP hesaplaması

İş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.

Excel VLOOKUP çözümü

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:

Excel VLOOKUP değerleri

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)

Excel VLOOKUP toplamı

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

Excel VLOOKUP sonucu

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..

Excel VLOOKUP Kullanımı ve Örnekleri
Bu makalenin telif hakkı ve tüm sorumlulukları yazara ait olup, şikayetler için lütfen bizimle iletişime geçiniz.
URL:
Etiketler:

Bu makale 14309 kez okundu

27.10.2017 tarihinde yazıldı
Reitix

Yorumlar

  • RainingCodes
    23.08.2019

    bir veriye göre eşleme yaparak ilgili başka bir veriyi getirebilmesi özelliği ile excel'in en faydalı fonksiyonlarından birisi vlookup. ama birkaç bin satırlık verilerle uğraşıyorsanız dosya boyutunu da bir o kadar artırıyor ve işlem süreleri de artan satırlarla birlikte 1-5-10-20 saniye diye artarak devam ediyor. eğer parametreye bağlı bir eşleştirme yapmıyorsanız işlem bittikten sonra formülleri değerlerle replace etmeniz inanılmaz zaman ve performans kazancı sağlar size

  • nilayyy
    16.04.2019

    Türkçe excel'de hazırlanmış bir dosyayı ingilizce bir excel'de açınca fonksiyon adları da değişmiyor mu? isteyen ingilizce kurar öyle kullanır bence, ben de fonksiyon adlarının Türkçe karşılıklarını çok beğendiğimden değil ama kimse de ingilizce kuramazsın sen Türkiye'desin demiyor ki

  • ercan cskn
    16.04.2019

    bütün dünya artık açık kayak yazılımları benimsedi ve birbirlerinin açıklarını kapatabilecekleri ve güçlerini birleştirebilecekleri github tarzı sosyal repo platformlarını kullanıyorlar. microsoft gibi ticari yazılım üreten bir yazılım şirketinin office araçlarını açık kaynaklı yapması elbette beklenemez ama en azından excel dosyalarının tarayıcı üzerinde ortak olarak düzenlenebileceği platformları sunabiliyor olmaları gerek. bunun için de bir Türk ile bir (atıyorum) isveçli'nin ortak excel geliştirme dili ingilizce olacaktır. ne isveçli sizin düşeyara fonksiyonunuzu anlayabilir ne de siz onun VTSÖK (üşenmedim isveççe vlookup fonksiyonu neymiş diye baktım) fonksiyonunun ne olduğunu anlayabilirsiniz

  • esprit
    16.04.2019

    bence de nasıl programlama dillerinde eğitim almakta olan bir kişi örneğin C ya da C++ dilinde bir döngü kurması gerektiğinde for diyorsa (türkçesi için kelimesi olurdu sanırım) excel'de de fonksiyon isimleri orijinal isimleri ile adlandırılmalıydı

  • aylin katranci
    16.04.2019

    excel fonksiyonlarının Türkçeleştirmesi bence büyük bir hata, average yerine ortalama demek ingilizce bilmeyenler için kullanım kolaylığı sağlayabilir gibi görünüyor ama vlookup yeriine düşeyara demek nasıl bir mantıktır?

  • Sabri86
    17.03.2019

    match fonksiyonu ile birlikte kullanıldığında tepeye her bir alanın kaçıncı sırada olduğunu yazmaya gerek kalmıyor, vlookup'ın (hlookup olsa yine aynı olur) sıra parametresinin olduğu yere match fonksiyonu yazılırsa daha küçük bir excel vektörü ile işlem tamamlanabiliyor

  • fites
    05.03.2019

    excel match fonksiyonu da çok benzer gereksinimler ile uygulanıyor ve aynı işleri görebilmeyi sağlıyor, belki vlookup ile match fonksiyonlarını karşılaştıracak bir makale de hazırlarsınız

  • a77
    13.12.2018

    excel fonksiyonları arasında en kullanışlı olanı hangisi diye sorarsanız hiç şüphesiz vlookup derim, veritabanlarında group by yaparak tablolar arasında bağlantı kuran mantığın excel'deki tezahürü de vlookup fonksiyonudur

  • ozdines
    24.08.2018

    detaylı ve güzel açıklamalı bir paylaşım, vlookup dediğiniz fonksiyon excel'in deyim yerindeyse belkemiğidir, veritabanlarında where kelimesi ile buldurduğunuz tablo değerlerini excel'de en kolay yoldan çağırabildiğiniz fonksiyondur

  • teknak
    09.07.2018

    iki tablo arası veri alışverişi için mükemmel bir fonksiyon, bir tablonun başka bir tablodaki değerini alıp nereye istenirse konabiliyor ve üzerinden matematiksel işlemler yapılabiliyor

  • cerrah2011
    19.04.2018

    ben de bir ipucu vereyim: mesela elinizde adı ve soyadı bilgisi olan büyük bir tablo var, ne ada göre ne de soyada göre vlookup kullanamıyorsunuz çünkü her ikisi de birden çok kişide olabileceği için hata veriyor. Çözüm olarak ad ve soyadı birleştirerek yeni bir sütun daha yaratın ve halen mükerrer bir kayıt olup olmadığını tespit edin, eğer elinizdeki veri çok büyükse ve halen aynı ad&soyad verisine sahip kişiler varsa yanlarına kendi yarattığınız ID değerlerini ya da yaşlarını ekleyebilirsiniz. Tüm satırlarda benzeri olmayan bir sütun yaratabildiğinizde vlookup kullanabilirsiniz

Bu yazıya siz de yorum yapabilirsiniz

İnternet sitemizdeki deneyiminizi iyileştirmek için çerezler kullanıyoruz. Bu siteye giriş yaparak çerez kullanımını kabul etmiş sayılıyorsunuz. Daha fazla bilgi.