Excel VBA kullanarak Vlookupı otomatikleştirme

Vlookup , Excel'in önemli bir işlevidir ve veri işlemenin önemli bir parçası haline gelmiştir. Kapsamlı bir veritabanıyla birleştirebileceğiniz bir dizi işlev sağlar.

Ancak bu işlevi otomatikleştirmek istiyorsanız, özellikle Excel ve VBA'da bulunan otomasyon dilini nasıl kullanacağınızı biliyorsanız bunu yapabilirsiniz. Excel VBA'da vlookup işlevini nasıl otomatikleştirebileceğiniz aşağıda açıklanmıştır .

Excel'de Vlookup işlevi formülü

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

Formüldeki argümanların anlamı:

• lookup_value: Dizide aramak istediğiniz temel değer.
• lookup_array: Kaynak veri, aranan_değer referans olarak kullanılacaktır.
• column_index: Sütun değeri döndürür.
• exact_match/partial_match: Kombinasyon türünü belirtmek için 0 veya 1'i kullanın.

Excel'de VBA Vlookup işlevi

Doğrudan Excel'de veya VBA aracılığıyla kullandığınızda Vlookup işlevinin kullanımı pek farklı değildir. Bir veri analistinin bilgi satırlarını işlemesi veya bir depo yöneticisinin sürekli olarak yeni ürünlerle çalışması fark etmez, vlookup işlevi faydalıdır.

Dinamik bir arama aralığıyla çalışırken, aynı formülü Excel'de birden çok kez değiştirmek zorunda kalmamak için formülü otomatikleştirmek en iyisidir. VBA'da Vlookup fonksiyonunu otomatikleştirerek tek tıklamayla çok sütunlu hesaplamalar yapabilirsiniz.

İllüstrasyon

Bu veri kümesinin iki bölümü vardır: bir arama tablosu ve hedef veri noktaları. Arama tablosu, Alt Kategori ve Ürün Adı alanlarındaki verileri içeren 3 sütun içerir:

Hedef tablo uygun sütunlara sahiptir, alt klasörler veya ürün adı verileri yoktur. Sipariş Kimliği sütunundaki değerlerin arama tablosunda da aynı sütunda göründüğünü unutmayın:

1. Gerekli ayarları yapın

Yeni bir Excel çalışma kitabında kod düzenleyiciyi açarak başlayın ( Alt + F11 tuşlarına basın veya Geliştirici sekmesine gidin ) ve kodlamaya başlamak için modülü ekleyin. Kod düzenleyicide, bir alt rutin oluşturmak için programlama penceresinin en üstüne aşağıdaki satırları ekleyin:

Sub vlookup_fn1()End Sub

alt rutin, VBA kodu için bir kapsayıcıdır ve onu başarıyla çalıştırmak için gereklidir. Diğer bir alternatif ise kullanıcı arayüzünü daha etkileşimli hale getirmek için bir VBA kullanıcı formu oluşturmaktır.

2. Değişkenleri bildirin ve referans kapsamları oluşturun

Öncelikle değişkenlerin veri türlerini Dim komutunu kullanarak bildirmeniz gerekir:

Dim i as integer, lastrow as long

Ardından, arama aralığındaki son doldurulan satırın değerini depolamak için bir lastrow değişkeni oluşturun. Lastrow işlevi, belirli bir aralıktaki son satır referansını hesaplamak için end(xldown) işlevini kullanır .

Bu örnekte lastrow değişkeni, arama aralığındaki son doldurulan satırın değerini, özellikle de 17'yi içerir.

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

Yukarıdaki örnekte, hedef tablo A2:C17'den, kaynak tablo ise F2:H17'den uzanmaktadır. Vlookup formülü, A sütunundaki her değer arasında döngü yapacak, bu değeri kaynak tabloda arayacak ve birleştirilmiş girişleri B ve C sütunlarına yapıştıracaktır.

Ancak döngüye girmeden önce aralık değerlerini aşağıdaki gibi yeni bir değişkende ( my_range ) saklamanız gerekir :

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

Başlangıç ​​hücre başvurusunu (F3) ve bitiş sütunu başvurusunu (H) açıkça belirtmeniz gerekir. VBA, arama dizisini tamamlamak için otomatik olarak satır değerleri ekler.

3. Her arama değeri arasında geçiş yapmak için bir döngü yazın

Döngüyü yazmadan önce başlangıç ​​satırı değerini tanımlayın, 2. Dinamik bileşenlerle uğraşırken döngü için bir başlangıç ​​değeri tanımlamak gerekir. Hedef tabloda satır 2, verilerin ilk satırıdır. Veriler farklı bir satırda başlıyorsa bu değeri değiştirin.

i = 2

2. satırdan başlayıp 17. satırda biten her satırı işlemek için bir do-while döngüsü kullanabilirsiniz . Kodun geri kalanı gibi, bu özellik de esnektir; Döngü, koşul yanlış olana kadar çalışacaktır. Geçerli satırın ilk hücresinde boş olmayan bir değer olup olmadığını test etmek için koşulu kullanın.

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

Döngünün içinde hücreleri doldurmak için Vlookup işlevini çağırabilirsiniz:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

Bu komutlar, sırasıyla 2. ve 3. sütunlarda geçerli satırdaki hücrelerin değerlerini ayarlar. WorksheetFunction nesnesini kullanarak Vlookup işlevini çağırır ve karşılık gelen değeri 1. sütundan aramaya iletir. Ayrıca, nihai değeri getirmek için başlangıçta tanımladığınız aralığı ve ilgili sütunun dizinini de iletirler.

Vlookup kodu hazır, ancak yine de döngüdeki her seferinde satır değişkenini artırmanız gerekiyor:

i = i + 1

Döngü her çalıştırıldığında i değeri 1 artırılır . Unutmayın, başlangıç ​​satırı değeri 2'dir, döngü her çalıştırıldığında artış gerçekleşir. Bu kod bloğunun sonunu ayarlamak için loop anahtar sözcüğünü kullanın.

Kodun tamamı çalıştırıldığında, kaynak tablodaki değerlere göre her iki sütunu da sonuçlarla dolduracaktır.

Referans kodunun tamamı aşağıdadır:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. Kodu çalıştırmak için bir düğme oluşturun

Tek tıklamayla çalıştırmak için Excel tablosunda bir düğme oluşturabilirsiniz. İstediğiniz şekli boş Excel sayfasına ekleyin, sağ tıklayın ve Makro Ata seçeneğini tıklayın .

Diyalog kutusunda alt rutinin adını seçin ve Tamam'a tıklayın .

Kodu çalıştırmak istediğinizde butona tıklayarak verilerin nasıl doldurulduğunu anında görebilirsiniz.

Yukarıda, arama işlevlerini otomatikleştirmek için Excel VBA'nın nasıl kullanılacağı açıklanmaktadır . Umarım makale sizin için faydalıdır.