Bir hesap tablosu sütunundaki veriyi alıp başka bir yere yerleştirmeniz gerektiğinde VLOOKUP fonksiyonunu kullanın. Bu fonksiyon, Excel' in Windows ve Mac işletim sistemleri altında çalışan herhangi bir sürümünde ve ayrıca Google Sheet uygulamasında çalışır. Bir tablodaki verileri, başka bir tablodaki ortak bir tanımlayıcıyı kullanarak bulmanızı sağlar. Her iki tablo da farklı hesap tablolarından veya çalışma kitaplıklarından (workbook) oluşabilir. Aynı işlemi sütunlar yerine satırlar üzerinde yapan HLOOKUP isminde bir fonksiyon da mevcuttur.

MATCH ve INDEX fonksiyonları, bir kişinin adını içeren sütun veya satır gibi belirli verilerin bulunduğu konumla ilgili kullanılır.

Premium Seçenekler

Excel fonksiyonlarına girmeden önce, Envato Market' in gelişmiş işlevleri yerine getiren bir dizi Excel komut dosyasına ve eklentilerine sahip olduğunu biliyormuydunuz?

Örneğin, şunları:

• WordPress verisini Excel' e aktarmayı
• Bir PHP sınıfıyla Excel' den veri alma ve ayrıştırmayı
• Bir Excel hesap tablosunu HTML tablosuna dönüştürmeyi
• Excel dosyalarını .NET DataTable'a (veri tablosu) dönüştürmeyi
• ve daha fazlasını yapabilirsiniz.

Video Kaydı

How to Extract Data from a Spreadsheet using VLOOKUP, MATCH and INDEX

Dilerseniz, bu eğitimi kendi Excel dosyanızla yada bu eğitimin ekinde yer alan zip dosyayı indirip içindeki vlookup example.xlsx isimli çalışma tablosunu kullanarak da takip edebilirsiniz.

VLOOKUP Kullanımı

VLOOKUP (dikey arama), kaynak tabloda belirttiğiniz  benzersiz tanımlayıcıyı bulduğunda, o satırdaki herhangi bir hücreyi bulup o hücrede yer alan veriyi geri dönebilir. Kaynak veri tablosunun ilk sütununda tanımlayıcı yer alması gerekir, bunu unutmayın.

Sözdizimi

VLOOKUP fonksiyonunun sözdizimi şöyledir:

=VLOOKUP(aranılan değer, tablo aralığı, sütun numarası, [true/false])

Parametrelerin anlamı:

• Aranılan değer. Benzersiz tanımlayıcının yer aldığı hücre.
• Tablo aralığı. İlk sütundaki benzersiz tanımlayıcıyı ve diğer sütunlardaki verileri içeren hücre aralığı.
• Sütun numarası. Aradığınız verinin yer aldığı sütunun numarası. Sütun başlıkları ile bunu karıştırmayın. Yukardaki örnekte, state sütununun numarası 4' tür.
• True/False. Bu parametre opsiyoneldir. True yaklaşık bir değer ile eşleşmenin, False ise sadece kesin eşleşmelerin kabul edilebilir olduğu anlamındadır.

Yukarıdaki resimde yer alan tablodan satış tutarını bulmak istiyoruz, dolayısıyla şu parametreleri kullanıyoruz:

Mutlak Referans için Seçilen Aralığın Adlandırılması

vlookup example.xlsx dosyasındaki Sales Amounts isimli çalışma sayfasına bakın. Formülü B5 hücresine girdikten sonra, formülü alttaki hücrelere kopyalamak için formun otomatik doldurma (AutoFill) özelliğini kullanacağız. Bu, formüldeki tablo aralığının mutlak bir referans olması gerektiği anlamına gelir. Bunu yapmanın en iyi yoluda tablo aralığını adlandırmaktır.

Excel' de Bir Aralığı Adlandırma

1. Formülü girmeden önce, Source Data çalışma sayfasına gidin.
2. A4 (Order # sütununun başlığı) ile H203 arasındaki tüm hücreleri aşağı doğru seçin. Bunu hızlı yapmanın yolu, A4 sütununa tıklayıp ardından Ctrl-Shift-End (Mac' te Command-Shift-End) tuşlarına basmaktır.
3. A sütunun üstündeki Name Box alanına tıklayın (artık Name Box alanında A4 yazısı görünür.
4. data yazın ve ardından Enter tuşuna basın.
5. Artık formülde $A$4:$H$203 yerine data ismini kullanabilirsiniz.

Google Sheet' de Aralık Adı Tanımlama

Google Sheet' de, ad tanımı biraz farklıdır.

1. Kaynak veri tablosundaki ilk sütunun başlığına tıklayın, ardından Ctrl-Shift-Sağ Ok (Mac' te Command-Shift-Sağ Ok) tuşlarına birlikte basın. Bu kolon başlıklarının olduğu satırı seçer.
2. Ctrl-Shift-Aşağı Ok (Mac' te Command-Shift-Aşağı Ok) tuşlarına basın. Bu mevcut verileri seçer.
3. Menüden Data menüsüne tıklayın ardından Named and protected ranges satırını seçin.
4. Sağ taraftaki Name and protected ranges box alanına data yazın ardından Done butonuna tıklayın.
   

Formül Girişi

Formülü girmek için, Sales Amounts isimli çalışma sayfasına gidin ve B5 hücresine tıklayın.

Formülü girin:

=VLOOKUP(A5,data,8,FALSE)

Enter tuşuna basın.

Sonuç 40 olmalıdır. Değerleri aşağıya doğru doldurmak için gerekirse B5 üzerine tıklayın. Fare imlecini hücrenin sağ alt köşesindeki AutoFill (otomatik doldurma) noktasına koyun, böylece fare imleci bir artı işaretine dönüşür.

Sütundaki boş alanları doldurmak için çift tıklayın.

Dilerseniz, sonraki sütunda VLOOKUP fonksiyonunu  lastname yada state gibi alanları ayıklamak için çalıştırabilirsiniz.

MATCH Kullanımı

MATCH (eşleştirme) fonksiyonu geriye bir veri döndürmez; aradığınız değeri belirtirsiniz, fonksiyonda size bulunduğu konumu geri döndürür. Bu bir anlamda, Rumeli Caddesi 135 numara nerede sorusuna, caddedeki 4. bina yanıtını almak gibidir.

Sözdizimi

MATCH fonksiyonunun sözdizimi şöyledir:

=MATCH(aranılan değer, tablo aralığı, [eşleşme tipi])

Parametreler:

• Aranılan değer. Benzersiz tanımlayıcının yer aldığı hücre.
• Tablo aralığı. Arama yapılacak hücre aralığı.
• Eşleşme Tipi. Opsiyoneldir. İstediğiniz eşleşmeye yakın olanı aşağıdaki şekilde belirtebilirsiniz:

VLOOKUP fonksiyonunda olduğu gibi, bir aralık adı tanımladığınızda muhtemelen MATCH fonksiyonunun daha kolay kullanıldığını göreceksiniz.  Source Data tablosuna gidin, B4 (order # başlığının olduğu sütun) sütununu aşağıya doğru seçin, A sütununun üstündeki Name box alanına tıklayın ve order_number olarak adlandırın Dikkat edin değerler artan düzendedir.

Çalışma kitaplığından Match sekmesine gidin. B5 hücresine MATCH fonksiyonunu girin:

=MATCH(A5,order_number,1)

Aralık adı tanımlamadıysanız, fonksiyonu şöyle yazarsınız:

=MATCH(A5,'Source Data'!A5:A203,0)

Her iki şekilde de, bunun 14. pozisyonda/satırda olduğunu görürsünüz (13. order oluyor).

INDEX Kullanımı

INDEX fonksiyonu MATCH fonksiyonunun tersidir ve VLOOKUP' a benzer. Fonksiyona istediğiniz verinin hangi satır ve sütunda olduğunu bildirirsiniz, oda size hücredeki değeri döndürür.

Sözdizimi

INDEX fonksiyonunun sözdizimi şöyledir:

=INDEX(veri aralığı, satır numarası, [sütun numarası])

Parametreler:

• Veri aralığı. Tıpkı diğer iki fonksiyon gibi veri tablosudur.
• Satır numarası. Çalışma sayfasının satırını değil, verinin satırını ifade eder. Tablo aralığı tablonun 10. satırından başlıyorsa, bu 1. sıra olur.
• Sütun numarası. Veri aralığının sütun numarası. Veri aralığı E sütunundan başlıyorsa bu 1 numaralı sütun olur.

Excel dökümanında, sütun numarası' nın isteğe bağlı olduğu ifade edilir fakat satır numarası da isteğe bağlıdır. Tablo aralığı sadece bir sütun veya bir satırdan oluşuyorsa, diğer parametreyi kullanmanıza gerek yoktur.

Çalışma kitaplığından Index sayfasına gidin ve C6 hücresine tıklayın. İlk önce, tablonun 9. sütun 3. satırında ne olduğunu bulmak istiyoruz. Formülde daha önce oluşturduğumuz aralık adını kullanacağız.

Formülü girin:

=INDEX(data,A6,B6)

Strevel soyadındaki bir müşteriyi döndürür. A6 ve B6 değerlerini değiştirin, C6 hücresinde farklı sonuçlar görüntülenecektir (birçok satırın aynı state ve product name bilgisine sahip olduğuna dikkat edin).

Sonuç

Bir çalışma kitabından diğer bir çalışma kitabına göz atma ve verileri ayıklama yeteneği mükemmel bir araçtır. Bu yolla, çoğu amaç için gerekli olan tüm veriyi içeren bir çalışma sayfanız olabilir, ardından da belirli durumlar için ihtiyacınız olanı ayıklayabilirsiniz.