Kas 242010
 

ms excel‘de seçtiğiniz bir alanın ilk sütununu referans alarak, alan içerisinden bir değeri çekmek istediğinizde(misal, bir sıra numarasına karşılık gelen ürünü bulmak istediğinizde) kullanılan çok güzel bir fonksiyondur ‘vlookup‘.

madem o kadar güzel, o halde neden hemen anlatmaya başlamıyorum ki?

 

öncelikle çok basit bir örnekle ne iş yaptığını göstereyim:

 

yukarıdaki tabloda 27. sırada yer alan kişiyi bulmak istediğimizi ve formülü girdiğimiz hücreye de bu kişinin ismini çekmek istediğimizi varsayalım. yapılması gereken, sonucun görünmesini istediğimiz hücreye aşağıdaki formülü yazarak enter tuşuna basmaktan ibaret:

 

 

formülü daha ayrıntılı inceleyelim sonra sonuca geçeriz:

 

yukarıda da görüldüğü üzere  VLOOKUP(27, A2:C9, 3, FALSE) formülündeki  A2:C9 alanı, arama yapılacak alanın sınırlarını belirliyor. vlookup fonksiyonu bu alanın ilk sütununda(sıra no), istediğimiz değer olan 27‘yi arar. bu değeri bulduğunda, değerin yer aldığı satırın 3. sütunundaki(isim) veriyi(hayriye) formülün bulunduğu hücreye çeker. formülü yazdıktan sonra enter tuşuna basınca şunu görürüz:

 

 

27‘nin yer aldığı satırda isim yerine bölümün çekilmesini isteseydik, formüldeki 3 yerine 2 yazmamız gerekecekti. formülün son bölümündeki  FALSE ise arattığımız değerin tam olarak girdiğimiz haliyle aranacağını gösterir. bunun yerine, excel girdiğimiz değere yakın bir değeri de bulabilir. yani 27‘yi arattığımız vakit, eğer 27 yoksa ona en yakın değeri-örneğin 26.7- de bulabilir. bunun için  FALSE yerine  TRUE yazmak yeterli.

 

 

 

tablo bu kadar küçük, iş bu kadar basitken formülle/fonksiyonla uğraşmaya tabiki gerek duyulmazdı lakin devasa bir tablodan veri çekmek istendiğinde bu formülün ne kadar can olduğu daha iyi anlaşılır. değerini anlatabilmek için az daha karışık bir iş yapayım.

meselam 2 tablomuz olsun: ilki, kişilerin bölüm bilgilerini; diğeri ise şehir bilgilerini içeriyor olsun ama ikinci tablodaki kişi sayısı birinci tablodakinden farklı olsun yani tablolarımız tam olarak eşleşmiyor olsun. o halde tablolarımız şunlar olsun:

 

bu tablolarla yapmak istediğimiz 2 tabloyu kısmen birleştirmek olsun. şöyle ki, ikinci tablodaki kişilerin bölüm bilgilerini birinci tablodan çekmemiz gerektiğini varsayalım. yapılacak ilk iş G2 hücresine şu fotodaki formülü girmektir:

 

 

bu formülü yakından inceleyelim:  VLOOKUP(E2, A2:B15, 2, FALSE)

E2: aranacak değer.  (bu örnekte ikinci tablodaki ilk kişi olan ‘ringo’ya denk geliyor)

A2:B15: aratmak istediğimiz değerin(bu örnekte ‘ringo’), nerede(bu örnekte birinci tablonun tamamını kapsıyor) aranacağını belirtir.

2: birinci tabloda aradığımız değer bulununca(!seçili alanın yani ilk tablonun 1. sütununda arar!) 2. sütunundaki veriyi çekmek istediğimizi gösterir.

FALSE: ilk örnekte bahsetmiştim.

 

yukarıdaki işlemin sonucu şu olur:

 

peki bu sonuç bizi datmin eder mi? etmez. bizi anca sağdaki tabloda yer alan bütün bünyelerin bölüm bilgilerini soldaki tablodan almak datmin eder. o halde G2 hücresinin sağ alt köşesini çift tıklamak ya da tutup aşağı kadar çekmek suretiyle formülü alt satırlara da kopyalamak gerekir. fekat bundan evvel illaki formülde şu değişikliği yapmak gerekir:

 

 

tablodaki formülde küçük bir değişiklik yapmak gerekiyor yukarıda da görüldüğü gibi. seçili alanı belirten adres kısmına iki tane ‘$‘ işareti ekliyoruz. bu ne iş görür? bu, formülü tutup çekmek ya da çift tıklamak suretiyle kopyaladığımızda satır numaralarının değişmemesini sağlar. yani seçili alanımızın sabit kalmasını istediğimiz için kullanıyoruz burada. aranacak değer için bunu yapmıyoruz çünkü zaten amacımız formülü alt satırlara kopyalayarak o satırlarda yer alan kişileri aranacak değer olarak belirlemek. aranacak değeri de sabit tutarsak bütün satırlarda aynı sonucu vereceği için anlamsız olur. ‘$‘ işaretinin bu kullanımı excel’in genelinde geçerlidir bu arada. formülü, paragrafın ikinci cümlesinde anlattığım şekilde kopyaladıktan sonra çıkan sonuç şu olur:

 

 

çok da iyi çok da güzel oldu, tamam mı? özetle ne yaptık iki tablolu örnekte: sağdaki tabloda isim ve şehir bilgileri bulunan ancak bölüm bilgileri bulunmayan arkadaşların bölüm bilgilerini başka bir tablodan çektik. o tabloda(soldaki) aynı isimli birden fazla satır için formül ilk karşılaştığı satırı(bu örnekte soldaki tablo 9. satır) değerlendirdi ve sağdaki tabloda ilgili hücrelere yazdı. solda karşılığı olmayan isimlerin bölüm bilgisine ise ‘#N/A‘ girdi.

vlookup‘ formülündeki ‘v‘ harfi tablolarda düşeyde(vertical:düşey) arama yapmak için kullanılıyormuş, tablolarınız yataysa ‘v‘ yerine ‘h‘ yazıp yatayda(horizontal:yatay) arama yaptırabilirsiniz.

örnek tabloların yer aldığı excel dosyasını şurada bulabilirsiniz.

 

vlookup‘ formülü daha farklı ve karmaşık işler için de kullanılabiliyor(bkz: tr, ing) ancak benim kullandığım şekil bu şekil. zaten bu yazıyı da aslında bu formülün kullanımını bir dahaki unutuşumda açıp bakayım diye daha çok kendim için yazdım, kırk yılda bir kullanmam gerekince her seferinde unutmuş oluyorum nasıl kullanacağımı. peki niye embesile anlatır gibi anlattım ben de anlamadım, hayat işte..

oldu, iyi tablolar.

 

 

 Posted by at 17:34

  21 Responses to “‘vlookup’ nasıl kullanılır?”

  1. gaarrdasimm bu gadar gozel anlatmisin da , bi de okunusunu yazsaydin yaa. ele gune irezil olduk. ve luuuk up :)

  2. Guzel aciklama, tesekkur ederiz.

  3. Öncelikle çoook teşekkürler. Günlerimi kurtardınız. Fakat bir şey dikkatimi çekti: son örnekte 6 tane “yar” varken ve hepsinin de karşı rengi farklı iken neden sadece ilk sıradaki “yeşil”i çekti? Ve bunun önüne nasıl geçeriz?

    • güle güle kullanın öğrendiklerinizi :)

      bahsettiğiniz örnekte vlookup, sağdaki tabloda ‘yar’ sözcüğü için soldaki tabloda bulduğu ilk karşılığı alır; o da ‘yeşil’e denk gelir.
      sonra sağdaki tabloda ikinci ‘yar’ sözcüğüne geçer ve onun soldaki tabloda karşılığını arar ancak soldaki tabloda aramaya yine taa en baştan başlar ve dolayısıyla bulduğu ilk karşılık yine ‘yeşil’ olur ve onu alır.
      ‘vlookup’ ile tek başına bunun önüne geçmenin yolu yok sanırım çünkü yapısı böyle. belki ‘if’ formülleriyle beraber kullanarak bunun önüne geçilebilir ama çok kolay olmayacaktır.

  4. herşey tamamda $$ işaretini formulun neresine ekliyoruz bir türlü çözemedim şunu

    • 8. görselde(sondan bir önceki) G2 hücresine bakın, şöyle yazıyor;

      =VLOOKUP(E2,A$2:B$15,2,FALSE)

      $ sembollerini işte bu formülde gösterildiği şekilde yazacaksınız. bu sembollerin neye yaradığını ise görselin üstündeki ve altındaki açıklamalarda anlatmıştım.

      ayrıca yazının sonlarında da verdiğim şu bağlantıdan örnek dosyayı indirip inceleyebilirsiniz;
      http://www.box.net/shared/zd0f6itnrs

 Leave a Reply

(gerekli)

(gerekli)