VLOOKUP, MATCH ve INDEX ile Excel Hesap Tablosundan Veri Ayıklama

Turkish (Türkçe) translation by Oğuz Çelikdemir (you can also view the original English article)

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.

Excel scripts and plugins on Envato Market — Envato Market' teki Excel komut ve eklentileri

Video Kaydı

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.

unique identifiers for vlookup — Benzersiz tanımlayıcı, aynı tabloda hiç biri birbirinin aynısı olmayan seri numaraları gibi olmalıdır.

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:

syntax of vlookup — VLOOKUP fonksiyonunun sözdizimi

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

Formülü girmeden önce, Source Data çalışma sayfasına gidin.
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.
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.
data yazın ve ardından Enter tuşuna basın.
Artık formülde $A$4:$H$203 yerine data ismini kullanabilirsiniz.

Name box displaying a range name — Name Box alanı genellikle seçilen hücrenin adresini gösterir. Tıklayın ve aralığı tanımlamak için bir isim belirleyin.

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

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

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.
Ctrl-Shift-Aşağı Ok (Mac' te Command-Shift-Aşağı Ok) tuşlarına basın. Bu mevcut verileri seçer.
Menüden Data menüsüne tıklayın ardından Named and protected ranges satırını seçin.
Sağ taraftaki Name and protected ranges box alanına data yazın ardından Done butonuna tıklayın.

Data menu in Google Sheets — Google Sheet uygulamasında aralık adı tanımlama

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.

entering the vlookup function — VLOOKUP fonksiyon girişi

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.

mouse pointer on the autofill dot — Fare imlecini, hücrenin sağ alt köşesindeki noktanın üzerine getirdiğinizde, AutoFill işareti olan artıya dönüşür

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

worksheet with data after using autofill — Sütundaki boş alanlara formülü kopyalamak için AutoFill işareti artıya ç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:

Sonraki en yüksek değer	-1	Değerler azalan sırada olmalıdır.
Hedef değer	0	Değerler herhangi bir sırada olabilir.
Sonraki en düşük değer	1	Varsayılan tip. Değerler artan sırada omalı. Değerler artan sırada omalıdır.

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.

name box with one column defined — Aralık tanımı sadece bir sütundan, bir satırdan yada bir hücreden de oluşabilir.

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

=MATCH(A5,order_number,1)

entering the match function — MATCH fonksiyonu girişi

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

completed match function — MATCH fonksiyonunun sonucu

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)

entering the index function — Index fonksiyonu girişi

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.