Farklı bir Düşeyara

Excel’in Düşeyara (Vlookup) formülü en çok kullanılan formüller arasında ilk sıralarda yer alır. Lakin, çalışması için tek şart; aradığımız değerin yer aldığı sütun hep aranacak değerlerin olduğu sütundan önce olmalıdır.

 

Bazı durumlarda bu özellik hoşumuza gitmez, önde kalan sütunları aranan değerin olduğu sütunun sağ tarafına alarak çözüm yolunu tercih edebiliriz. Bazen de farklı bir dosyadan veri almamız gerekir. Her iki dosya açıkken işlevini sorunsuz olarak yerine getiren bu formül, verilerin olduğu dosya kapatıldığı zaman güncelleme yapmaz ve canımızı sıkabilir.

 

Excel, mükemmel bir gelişmiş veritabanı özelliğine sahiptir. Veritabanı olmasının yanı sıra, Visual Basic dilini kullanarakta üst düzey işlemler yapmaya müsaittir. Bu örneğimizde, hem aranan sütunun solunda olsa bile bulunabilecek hem de verilerin olduğu dosya açık olmasa bile sonuçlarımızı kolayca bulabileceğimiz bir örnek üzerinde duracağız.

 

Ek’te bulunan zip’li dosyada iki ayrı Excel dosyası bulunmaktadır. Öncelikle bu iki dosyayı aynı klasör içine atalım. Daha sonra sadece Kapali_Dosya_Duseyarama isimli dosyamızı açalım.

 

Bu dosyada kullanacağımız formül (fonksiyon) tarafımca yazılmış özel bir fonksiyondur. Yani Excel’de böyle bir fonksiyon yok. Bu formülün çalışması için Excel’de Makro Ayarlarınızı yapmanız gerekir. Makro ayarının ne olduğunu kullandığınız Ofis versiyonuna göre Google’dan rahatça bulabilirsiniz.

 

Bu fonksiyonumuz şu şekildedir:

 

Public Function ÖzelArama(ByVal Aranan_Deger, ByVal Aranan_Sayfanin_Adi, ByVal Aranacak_Sutun_Adi, ByVal Bulunacak_Sutun_Adi)

Dim con As Object, rs As Object

Set con = CreateObject(“adodb.connection”)

con.Open “provider=microsoft.ace.oledb.12.0;data source=” & ThisWorkbook.Path & “\data.xlsx” & “;extended properties=””excel 12.0;hdr=yes”””

 

Set rs = CreateObject(“adodb.recordset”)

s = “select ” & Bulunacak_Sutun_Adi & ” from [” & Aranan_Sayfanin_Adi & “$] where ” & Aranacak_Sutun_Adi & ” = ” & Aranan_Deger & “”

 

rs.Open s, con, 1, 1

If rs.RecordCount > 0 Then

ÖzelArama = rs(0).Value

Else

ÖzelArama = “”

End If

End Function

 

Fonksiyonu kendinize uyarlarken dikkat edeceğiniz tek husus:

 

con.Open “provider=microsoft.ace.oledb.12.0;data source=” & ThisWorkbook.Path & “\data.xlsx” & “;extended properties=””excel 12.0;hdr=yes”””

satırında bulunan data.xlsx kısmıdır. Eğer siz aynı klasörde yer alan farklı bir dosyadan veri almak istiyorsanız, data.xlsx kısmına kendi dosyanızın adı ve uzantısını yazmanız yeterli olacaktır.

 

Bu formülü Excel üzerinde şöyle kullanacağız:

 

=ÖzelArama(A1;”DATA”;”KOD”;”ADI”)

ÖzelArama bizim yazdığımız bir fonksiyonun adıdır.

 

A1 kısmı aradığımız hücreyi işaret eder.

 

DATA, verilerimizin olduğu sayfanın adıdır.

 

KOD, A1 hücresinde aradığımız kodun olduğu sütunun en başında yer alan addır.

 

ADI, aradığımız verinin olduğu sütunun adıdır.

 

Bu 3 alanı kendinize uyarlayarak istediğiniz şekilde kapalı bir dosyadan verilerinizi kolayca alabilirsiniz. Burada dikkat edilecek en önemli husus, bu adların sütunların en başında yani 1. satırda olması gerektiğidir. İkinci olarak ise bu alanlara vereceğimiz isimleri yazarken aralarında boşluk bırakmamamız gerekir. Eğer boşluk bırakma ihtiyacınız varsa, boşluk yerine alt tire (_) kullanabilirsiniz. Bu ad verme olayındaki hassasiyet, sayfanın adını verirken de geçerlidir.

 

Yararlı olması temennisiyle…

 

Örnek dosyamız: OzelArama