آموزش خودکارسازی Vlookups خود با Excel VBA

با استفاده از تابع محبوب VLOOKUP در کد VBA خود، برنامه نویسی اکسل خود را یک قدم جلوتر ببرید.

Vlookup یک عملکرد ضروری در اکسل است و به بخشی حیاتی از پردازش داده تبدیل شده است. برخی از عملکردهایی را که معمولاً ممکن است با یک پایگاه داده کامل مرتبط کنید فراهم می کند.

اما اگر بخواهید این عملکرد را خودکار کنید چه؟ بله، این امکان پذیر است، به خصوص اگر بدانید که چگونه از زبان اتوماسیون بومی اکسل، VBA، استفاده کنید. در اینجا نحوه خودکارسازی عملکرد vlookup در Excel VBA آورده شده است.

نحو Vlookup

سینتکس تابع vlookup بسیار ساده است، اما دارای چهار جنبه است که باید روی آن تمرکز کنید، حتی زمانی که آن را در Excel VBA خودکار می کنید.

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

آرگومان های تابع به معنای زیر است:

lookup_value: این مقدار اولیه ای است که می خواهید از آرایه جستجو جستجو کنید.
lookup_array: این داده منبع است که lookup_value از آن به عنوان مرجع استفاده می کند.
column_index: ستونی برای برگرداندن مقدار از.
exact_match/partial_match: از 0 یا 1 برای تعیین نوع تطابق استفاده کنید.

آماده سازی داده ها

این مجموعه داده دارای دو بخش است: یک جدول جستجو و نقاط داده مقصد.

جدول جستجو از سه ستون تشکیل شده است، با داده هایی که فیلدهای زیر شاخه و نام محصول را پر می کند:

جدول مقصد دارای ستون‌های منطبق است، بدون داده‌های زیررده یا نام محصول. توجه داشته باشید که مقادیر موجود در ستون Order ID آن نیز در همان ستون در جدول جستجو وجود دارد:

استفاده مستقیم از Vlookup در یک صفحه گسترده اکسل

برای پر کردن ستون های مقصد، B و C، می توانید از تابع vlookup اکسل استفاده کنید.

در سلول B2 فرمول زیر را وارد کنید:

=VLOOKUP($A2, $F$3:$H$17, 2, 0)

به طور مشابه، در سلول C2، این فرمول را وارد کنید:

=VLOOKUP($A2, $F$3:$H$17, 3, 0)

می توانید فرمول را از سلول B2 تا انتهای ستون یعنی B17 به پایین بکشید. این روند را برای ستون C نیز تکرار کنید. مقادیر برای هر ورودی بعدی به طور خودکار در هر دو ستون به روز می شوند.

مطلب مرتبط: با استفاده از پایتون یک واکشی تصادفی تصویر بسازید

مقادیری که به تابع vlookup ارسال می کنید عبارتند از:

A2: مقدار جستجو در این سلول است.
F3:H17: این شامل محدوده جستجو است.
2/3: ستون های مرجع برای واکشی مقدار از.
0: نشان دهنده یک تطابق دقیق است.

تابع Vlookup Excel VBA

تابع vlookup شباهت های زیادی دارد چه از آن به طور مستقیم در اکسل یا از طریق VBA استفاده کنید. چه یک تحلیلگر داده باشید که با ردیف های داده سروکار دارد، یا یک مدیر موجودی که به طور مرتب با محصولات جدید سروکار دارد، می توانید با استفاده از vlookup از آن بهره مند شوید.

هنگام کار با محدوده جستجوی پویا، همیشه بهتر است فرمول های خود را خودکار کنید تا از اعمال مکرر فرمول ها در اکسل خودداری کنید. با خودکار کردن عملکرد Vlookup خود در VBA، می توانید محاسبات چند ستونی را با یک کلیک انجام دهید.

1. تنظیمات مورد نیاز را انجام دهید

با باز کردن ویرایشگر کدنویسی (فشار Alt + F11 یا رفتن به تب Developer) در یک کتاب کار جدید اکسل شروع کنید و یک ماژول برای شروع نوشتن کد خود اضافه کنید. در ویرایشگر کد، خطوط زیر را در بالای پنجره کدنویسی اضافه کنید تا یک روال فرعی ایجاد کنید:

 Sub vlookup_fn1()End Sub

روال فرعی محفظه ای برای کد VBA شماست و برای اجرای موفقیت آمیز آن ضروری است. یک جایگزین این است که یک فرم کاربر VBA ایجاد کنید تا رابط کاربری شما تعاملی تر شود.

2. متغیرهای خود را اعلام کنید و محدوده مرجع خود را ایجاد کنید

ابتدا باید انواع داده های متغیر را با استفاده از عبارت Dim اعلام کنید:

Dim i as integer, lastrow as long

بعد، یک متغیر lastrow ایجاد کنید تا مقدار آخرین ردیف پر شده را در محدوده جستجوی خود ذخیره کنید. تابع lastrow از تابع end(xldown) برای محاسبه مرجع ردیف انتهایی در محدوده مشخص شده استفاده می کند.

مطلب مرتبط: نحوه آزمایش API های Express.js REST با استفاده از Cypress

در این مورد، متغیر سطر آخر، مقدار آخرین ردیف پر شده محدوده جستجو را ذخیره می‌کند، 17.

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

در مثال بالا، جدول مقصد از A2:C17 گسترش می یابد، در حالی که جدول مبدا از F2:H17 گسترش می یابد. فرمول vlookup از طریق هر مقدار ذخیره شده در ستون A حلقه می زند، آن را در جدول منبع جستجو می کند و ورودی های مطابق در ستون های B و C را جای می دهد.

با این حال، قبل از پرش به حلقه، باید مقادیر محدوده را در یک متغیر جدید (my_range) به صورت زیر ذخیره کنید:

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

شما باید به صراحت مرجع سلول شروع (F3) و مرجع ستون پایانی (H) را تعریف کنید. VBA به طور خودکار مقدار ردیف را برای تکمیل آرایه جستجو اضافه می کند.

3. یک حلقه بنویسید تا از طریق هر مقدار جستجوی چرخه انجام شود

قبل از نوشتن حلقه، مقدار سطر شروع را تعریف کنید، 2. تعریف یک مقدار شروع برای حلقه شما ضروری است زیرا با عناصر پویا سر و کار دارید. در جدول مقصد، ردیف 2 اولین ردیف داده است. اگر داده‌های شما در ردیف دیگری شروع می‌شوند، این مقدار را تغییر دهید.

 i = 2

شما می توانید از یک حلقه do-while برای پردازش هر سطر استفاده کنید، که از ردیف 2 شروع می شود و به ردیف 17 ختم می شود. مانند بقیه کد، این جنبه نیز پویا است. حلقه تا زمانی اجرا می شود که شرط نادرست باشد. از شرط برای بررسی یک مقدار غیر خالی در سلول اول ردیف فعلی استفاده کنید.

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

در داخل حلقه، می توانید تابع VLookup را برای پر کردن سلول ها فراخوانی کنید:

 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)

این عبارات مقدار سلول های ردیف فعلی را به ترتیب در ستون های 2 و 3 تنظیم می کند. آنها از شی WorksheetFunction برای فراخوانی تابع VLookup استفاده می کنند و مقدار مربوطه را از ستون 1 برای جستجو ارسال می کنند. آنها همچنین محدوده ای را که قبلاً تعریف کرده اید و شاخص ستون مربوطه را برای دریافت مقدار نهایی از آن پاس می کنند.

مطلب مرتبط: PICO-8 چیست؟

کد vlookup آماده است، اما همچنان باید متغیر ردیف را هر بار در اطراف حلقه افزایش دهید:

 i = i + 1

هر بار که حلقه اجرا می شود، مقدار i را 1 افزایش می دهد. به یاد داشته باشید، مقدار سطر شروع 2 است و هر بار که حلقه اجرا می شود این افزایش اتفاق می افتد. از کلمه کلیدی حلقه برای تنظیم انتهای بلوک کد حلقه استفاده کنید.

وقتی کل کد را اجرا می کنید، نتایج را در هر دو ستون، بر اساس مقادیر جدول منبع، پر می کند.

در اینجا کل کد برای مرجع است:

 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. یک دکمه برای اجرای کد ایجاد کنید

با حذف کد، می توانید یک دکمه در کتاب کار اکسل خود ایجاد کنید تا آن را با یک کلیک اجرا کنید. شکل دلخواه خود را در یک صفحه خالی اکسل قرار دهید، روی آن کلیک راست کرده و روی گزینه Assign Macro کلیک کنید.

در کادر محاوره ای، نام زیر روال خود را انتخاب کرده و بر روی OK کلیک کنید.

هنگامی که می خواهید کد خود را اجرا کنید، روی دکمه کلیک کنید تا ببینید چگونه داده ها بلافاصله پر می شوند.

استفاده از Excel VBA برای خودکارسازی توابع جستجو

Excel VBA یک زبان منعطف است که به خوبی برای آسان کردن اتوماسیون در جنبه های مختلف MS Excel تنظیم شده است. گزینه های زیادی در MS Excel VBA وجود دارد، درست از خودکار کردن توابع اکسل تا ایجاد جداول محوری دقیق به طور خودکار.

هنگام کار با بخش های مختلف اکسل، می توانید گزینه های مختلفی را آزمایش کنید تا زندگی خود را آسان تر و کارآمدتر کنید.