vlookup

• ....a...b
  1 a....1
  2 b....2
  3 c....3
  4 d....4
  5 e....5
  
  c1'e yazdığımız değeri a sütunundan bulup, b sütünundaki karşılığını yazdırmak için şu formülü yazmamız gerekir. :
  =düşeyara(c1;a1:b5;2;0)
  
  c1 : karşılığını bulmak istediğimiz değerin yazılı olduğu hücre
  a1:b5 : tablonun tamamı
  2 : ikinci sütundaki, yani hemen yanındaki sütundan veri getirmek için
  0 : aradığımız değerin tam olarak uyanını bulmak için..
• bu formülü kullanacağınız zaman başvuru yapılacak sütundaki verilerin sıralı olmasına gerek yoktur lakin tavsiye edilen kullanım şekli verilerin sıralı olması gerektiği yönündedir. sıralı sırasız kargaşasını bir tarafa bırakacak olursak formülün şöyle bir handikapı mevcuttur. mutlak başvuru için "0"^* kullanılmışsa ve aranan değer sütunda birden fazla hücrede yazılıysa bulduğu en yakın satırdaki değerleri verir. eğer formül sıralı bir sütunda sıfır kullanılmadan yazılmışsa en alttaki, yani son satırdaki değerleri verir. eğer formülü yazdığınız hücrelerin bir kısmında sıfır kullanılmış, bir kısmında kullanılmamış ise o zaman durum iyice içinden çıkılmaz bir hale gelir. bir örnekle açıklayalım;
  
  a............b............c............d
  1........ahmet......100.....10/02/2010
  2........barış........200......03/02/2010
  3........cemal.......300......01/01/2010
  1........derya.......150......02/03/2009
  4........elif............400......24/06/2007
  5........fatih..........500......12/04/2008
  
  diyelim ki elimize bu şekilde bir database mevcut. yukarıda harflerle belirtilmiş kısımların sütunları temsil etmekte olduğunu, referans hücremizin ise e1 olduğunu kabul ediyoruz ve başlıyoruz formülümüzü yazmaya;
  
  f1 hücresi: =vlookup(e1;a1:d6;2;0)
  g1 hücresi: =vlookup(e1;a1:d6;3;0)
  h1 hücresi: =vlookup(e1;a1:d6;4;0)
  
  e1 hücresine 1 yazıldığında karşılaşılacak sonuç:
  
  e............f............g............h
  1.......ahmet......100.......10/02/2010
  
  olarak görünürken, a sütunu sıraya sokulup;
  
  a............b............c............d
  1........ahmet......100.....10/02/2010
  1........derya.......150......02/03/2009
  2........barış........200......03/02/2010
  3........cemal.......300......01/01/2010
  4........elif............400......24/06/2007
  5........fatih..........500......12/04/2008
  
  formüllerin sonundaki sıfırlar kaldırılıp;
  
  f1 hücresi: =vlookup(e1;a1:d6;2)
  g1 hücresi: =vlookup(e1;a1:d6;3)
  h1 hücresi: =vlookup(e1;a1:d6;4)
  
  e1 hücresine 1 yazıldığında karşılaşılacak sonuç:
  
  e............f............g............h
  1........derya.......150......02/03/2009
  
  bu şekilde olacaktır. şimdi de "g1" hücresindeki formüle sıfır ekleyelim ve sonuca bakalım;
  
  f1 hücresi: =vlookup(e1;a1:d6;2)
  g1 hücresi: =vlookup(e1;a1:d6;3;0)
  h1 hücresi: =vlookup(e1;a1:d6;4)
  
  e1 hücresine 1 yazıldığında karşılaşılacak sonuç:
  
  e............f............g............h
  1........derya.......100......02/03/2009
  
  evet, görüldüğü üzere g1 hücresinde aslında "150" yazması gerekirken formül bulduğu ilk değerin karşılığını yazdığı için sonuç "100" çıkmaktadır. olsun, "100" de güzel rakamdır, severiz bağrımıza basarız.
  
  işin şakası bir yana, bu formülü kullanırken dikkatli olmakta fayda var. yoksa bütün veriler birbirine girebilir. bunların yanısıra, kullanılan tablo için bir alan adı belirlenirse formül yazmak çok daha kolay olacak, kafa karıştırmayacaktır. şöyle;
  
  bu tabloda a1:d6 hücrelerini mouse ile seçip üstteki menü tuşlarından "ekle/ad/tanımla" tuşlayıp açılan pencerede basit^* bir kelime yazıp enter tuşuna bastığınızda bu alan o isimle tanımlanacak, formülde de her seferinde "a1:d6" yazmak yerine "basit" yazmanız yeterli olacaktır. yani formüller;
  
  f1 hücresi: =vlookup(e1;basit;2;0)
  g1 hücresi: =vlookup(e1;basit;3;0)
  h1 hücresi: =vlookup(e1;basit;4;0)
  
  olacaktır. basit tablolarda pek mantıklı gelmese de işin içine başka formüller falan girdiğinde tatından yenmez.
  
  gelen bir uyarı üzerine edit: vlookup yani ingilizce kullanacaklar için formüldeki noktalı virgüllerin yerine virgül konması gerekiyor. düşeyara yani türkçe olarak kullanacaklarda bir değişiklik yok. vlookup yerine düşeyara yazacaklar o kadar.
• index ve match fonksiyonlarını iç içe kullanmayı öğrenene kadar excel'in en şahane fonksiyonu gibi görüyorsunuz bunu. oysa index + match'e alıştıktan sonra vlookup gereksiz ve yetersiz kalıyor. sütun saydırmak, araya sütun sokarsam nolacak acaba tedirginlikleri filan.. hepsi gidiyor.
  
  diyelim ki ana tabloda ilk sütunumuz tarih verisi olsun, diğer sütunlar da birtakım ürünleri temsil etsinler. yani tablomuz her satırda belli bir tarihteki bütün ürünlerin stok değerlerini veriyor olsun.
  bu tablodan istediğimiz bazı tarihlerde, istediğimiz bazı ürünlerin değerlerini getirecek formülasyonu vlookup yerine index-match kullanarak yapmak isteyelim. yani mesela vlookup kullanırken x'inci ürün hangi kolondaydı ya diye saydırmak yerine, doğrudan o ürünün adını belirteceğimiz bir formül kuralım ki araya sütun girerse filan, formülümüz yine de doğru ürünün kolonuna gitsin.
  
  vlookup yerine index ve match'in başlangıçta karışık gibi gelecek olan kullanımı şu şekilde:
  
  öncelikle, index komutunun mantığı
  index(aranacak bütün alan yani ana tablo, istenen satır numarası, istenen sütun numarası)
  şimdi satır ve sütun numaralarını , match ile bulduralım. şöyle olacak
  index(aranacak bütün alan yani ana tablo, match(istenen tarih, tarihlerin bulunduğu kolon vektörü,0), match(istenen ürün, ürünlerin bulunduğu satır vektörü,0))
  yani kolayca, şu satır ve şu sütun anahtarlarının kesiştiği yerdeki bilgiyi al diyebiliyoruz bu şekilde. evet tabii ki ürün anahtarının unique olması lazım.
• uğruna dizeler dökebileceğim, methiyeler yazabileceğim fonksiyon. cross check yaparkenki tek yardımcı.
• sadece benzersiz veriler ile çalışıyorsanız iyi ama aynı kolonda birden fazla benzer verinin karşılığı gelen verileri toplamak istiyorsanız o zaman işe yaramaz.
  
  vlookup kolonda aradığınız veriyi ilk karşılaştığı yerde tanır ve gerisine bakmaz. dolayısı ile eğer bir satış tablosunda bir maldan kaç adet satıldığını bulmak istiyorsanız aşağıdaki formül işe yarar.
  
  =sum(("veriyiaradığınızkolon"="aradığınızveri")*("verininkarşılığıgelecekverininbulunduğukolon")
  
  yani;
  
  =sum((a1:a9)="ekşi")*(b1:b9))
  
  formülün ilk kısmı true,false mantığı ile çalışır, her true 1'e, her false da 0'a eşit olur, bunları b1:b9 kolonundaki tüm verilerle çarpıp sonuçları toplatırsak sadece true olanlar 0'dan farklı çakacağı için bize aradığımız tüm "ekşi" karşılığında bulunan değerlerin toplamını verecektir. "ekşi" yerine herhangi bir kutunun isimini de verebilirsiniz. örneğin c3 gibi...
  
  formülün mantığından yola çıkarak, filtrelemeleri birden fazlaya çıkarabilirsiniz, yani =sum((a1:a9="ekşi")*(b1:b9="entry")*(c1:c9="sözlük")*(d1:d9))
  
  ilk kolondaki tüm ekşilerin yanında entry yazanların ve bunlarında karşılarında sözlük yazanların karşısında bulunan sayıları toplar bu formül, afiyet olsun.
  
  ha, bu formülün çalışması için formülü bir kere yazdıktan sonra ctrl+shift+enter yapmak gerekmenktedirt!
• bu mükemmel ötesi formülün şöyle aksayan bir yönü olabiliyor.. diyelim ki 75 satıra kadar veri tablonuz mevcut ama siz yanlışlıkla referans veri olarak (ilk sütun değeri) 76 ya da üzeri bir sayı yazdınız.. bu durumda formül size 76 diye bir değer olmadığı konusunda uyarı yapmayacak, en yakın rakam olan 75'in değerini verecektir ve bu durumda kontrol etmiyorsanız sıçtınız demektir.. bunu önlemek için referans veri tablosunun en alt satırına 76 yazmalı, referans veri sütunlarına da çizgi - (ya da başka anlamsız bir işaret) koymalısınız.. bunu yaptığınızda yanlış veri girmeniz durumunda bile hata nal gibi kendini belli edecektir..
  
  yıllar sonra gelen edit: son parametreye "false" yazdığımızda hata uyarısı veriyormuş..
  yazar sair fikiradami'na teşekkürler..
• office xp'de aynı dosya içinde farklı şiitler arasında veri transferinde bariz bir şekilde batırabilen formüldür. bu özelliği 2003de azalmış olsa da yok olmamıştır. dolayısıyla garantici çözüm, şiitin kopyalanıp dışarı bir book1'e atılıp formülün o bölgeden yürütülmesidir.
  
  mecburi olmasam yanına bile yaklaşmam, güvensiz ve kontrolsüzdür zira. dikkatsizlik hatasını affetmez, insanı utandırır.
  
  ayrıca garip hataları olan bir arkadaş, örneklemek gerekirse;
  
  bir başka mecradan kopyalanarak gelen bilgi başındaki üsten ayırmaç(shift+2 işte hücre içine "'ekim 15" yazınca sadece ekim 15 çıkartan nane işte) eğer ki bir şekilde format delisi, neysen osundurcu "text to columns" tarafından iplenmez ise vlookup arkadaşımız çalışmaz. sonra concatenate araya girer. formül içinde bahsi geçen iki listedeki referans kolonlarının başına ' ekleyip tekrar bir kolon oluştururuz vs vs.
  
  seveceksin sayacaksın ama çok da güvenmeyeceksin. ne datası çektiysen bir kere daha çekeceksin yanına da hemen bir =exact(b1;c1) yapıp aşağıya kadar uzatacaksın. nolur nolmaz.
• almancasi sverweis (ya da wverweis) olan excel harikasi. aylarca wenn'li islemler yap(amadik)tiktan sonra hayat kurtaricisi.
• yurdum plaza insanlarinin ogrendikeri vakit excelde kont statusune yukseldikleri formul. eger ofiste statu belirleyen sey nedir diye bir soru olsaydi cevabi banko vlookup olurdu.
• ilk öncelikle hiçbir yerde basit bir anlatımını bulamazsınız, en kolay öğrenme yolu uygulamalı olarak birisinin anlatması ve bunla da kalmayıp size bir kaç tane yaptırmasıdır. öbür türlü de olur ama kastırır. üşengeç suserlar için basit bir anlatım yapmaya çalışacağım şimdi.
  
  -mouse'u sağ elimize alıyoruz,
  -ihtiyacımız olan verinin hangi hücreye gelmesini istiyorsak faremizin sol tuşu vasıtasıyla (buraya dikkat, yazılı olmayan kurallardandır bunlar herkes söylemez) o hücreye bir kez tıklıyoruz,
  -hücremizin seçili konuma geldiğinden emin olduktan sonra excel'de araç çubuğunun altında bulunan text bar'ın hemen başında bir fx yazısı var ona tıklıyoruz ve oradan düşeyara veya vlookup formülünü seçiyoruz.
  
  işin zor kısmını atlattık, gördüğünüz gibi o kadar da zor değilmiş
  
  gelelim ikinci aşamaya
  
  açılan pencerede 4 tane kutucuk var, bunları doldurduğumuzda formülümüz tamam demektir,
  
  1. kutu: buraya anahtar değeri girmemiz lazım, yani excel sana veriyi getirmek için hangi sayıyı ya da yazıyı kullanacak? heh işte anahtar hangi hücredeyse o hücreye bir tıklayıver biraaader (b2).
  
  mesela aşağıdaki örnekte kişi numaralarının hangi isme ait olduğunu veren bir formül yazacak olsak. anahtar hücre, numaraların yazdığı hücrelerdir (b2, b3, b4 hücreleri) ,
  faremizle ilk başta tıkladığımız hücre ise onun bir sağında bulunan hücrelerdir (c2), yani formülü c2'ye yazmak istiyoruz, çünkü isimler oraya gelecek.
  
  a b c
  1 numara isim
  2 144 ??
  3 143 ??
  4 151 ??
  
  buraya kadar sıkıntı yok sanırım, anlamayan olursa parmak kaldırsın. zaten bundan sonrası yokuş aşağı sayılır.
  
  2. kutu: buraya bir matris gireceğiz, bir çok kişinin öğrenme safhasında yanlış yaptığı yerlerden birisi burasıdır diye düşünüyorum. gelelim bu matrisin nasıl birşey olduğuna. bu matris arkadaşım senin anahtar verinin aranacağı (numara) ve anahtar verine karşılık gelen yani esas hücreye gelecek verinin excelde şu anda bulunduğu tabloyu içermeli. anlayacağın veri öbeğinin olduğu kısmı komple seçili hale getiriyorsun, o kadar da değil de anladın işte.
  
  örneğin:
  
  l -- m ---- n
  6 151 ahmet
  7 143 mehmet
  8 144 kezban
  
  gibi bir tablodan kişinin numarası girilince adını getirecektik
  
  2. kutuya bu durumda m6:n8 girilmiş olacak. ya da fareyle de burayı seçebiliriz. (klavyenin imleci 2. kutunun içinde yanıp sönerken)
  
  burada excel henüz bir şey anlamıyor tabiki durumdan, ulan ben hangisini nerede arayaacağım yahu, sen bana bir tane sayı verdin ama bir tabloda veriyi ara karşılığını da getir diyorsun diyip çipil çipil bakıyor. işte 3. kutu burada hop karrrşimm beni unuttun diyor.
  
  3. kutu: şimdi söyleyeceğim cümleyi dikkatle okuyun biraz uzun olabilir ama saçma olmayacaktır; bu kutuya ilk tıkladığımız hücre vardı ya (c3), heh işte ona hangi veri gelecek onun tarifini yapıyoruz. o tarif de şöyle oluyor: gelmesini istediğimiz veri az önce seçtiğimiz matrisin kaçıncı sütunu? bu soruya cevap veriyoruz, benim verdiğim örnekte 2. sütunda yazıyordu mesela isimler. 3. kutuya sadece 2 yazıyoruz anlayacağınız (m sütunu birinci sütun, n sütunu ikinci sütun benim verdiğim matriste). eğer isimin yanında kişinin boyu posu cinsiyeti vs. olsaydı onları çağırırken de 3. kutuya 3,4,5 gibi sayılar yazmamız gerekecek. tabi 3-4-5 sütunlarının olaiblmesi için bir önceki aşamada matrise dahil etmemiz lazım geliyor.
  
  4. kutu: bu kutuya 0 yazın gitsin.
  
  (bkz: özürlüye anlatır gibi anlatın hocam)
  
  edit: arada sırada sapıtıp çalışmayabiliyor, verileri sıralayıp tekrar denemekte yarar var^*