كيفية أتمتة Vlookup باستخدام Excel VBA

تعد Vlookup وظيفة أساسية في برنامج Excel وأصبحت جزءًا مهمًا من معالجة البيانات. فهو يوفر عددًا من الوظائف التي يمكنك دمجها مع قاعدة بيانات شاملة.

ومع ذلك، إذا كنت تريد أتمتة هذه الوظيفة، فيمكنك ذلك، خاصة إذا كنت تعرف كيفية استخدام لغة الأتمتة المتوفرة في Excel وVBA. إليك كيفية أتمتة وظيفة vlookup في Excel VBA .

صيغة دالة Vlookup في Excel

=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 لتحديد نوع المجموعة.

وظيفة VBA Vlookup في Excel

لا يختلف استخدام وظيفة Vlookup كثيرًا عند استخدامها مباشرة في Excel أو عبر VBA. سواء كان محلل البيانات يعالج صفوفًا من المعلومات أو كان مدير المستودع يعمل باستمرار مع منتجات جديدة، فإن وظيفة vlookup مفيدة.

عند العمل باستخدام نطاق بحث ديناميكي، فإن أتمتة الصيغة هو الأفضل، لتجنب الاضطرار إلى معالجة نفس الصيغة عدة مرات في Excel. من خلال أتمتة وظيفة Vlookup في VBA، يمكنك إجراء حسابات متعددة الأعمدة بنقرة واحدة.

توضيح

تتكون مجموعة البيانات هذه من جزأين: جدول البحث ونقاط البيانات المستهدفة. يحتوي جدول البحث على 3 أعمدة، مع البيانات في الحقلين الفئة الفرعية واسم المنتج:

يحتوي الجدول الهدف على الأعمدة المناسبة، ولا يحتوي على مجلدات فرعية أو بيانات اسم المنتج. لاحظ أن القيم الموجودة في عمود معرف الطلب تظهر أيضًا في نفس العمود في جدول البحث:

1. قم بإجراء الإعدادات اللازمة

ابدأ بفتح محرر التعليمات البرمجية (اضغط على Alt + F11 أو انتقل إلى علامة التبويب "المطور ") في مصنف Excel جديد وأضف الوحدة النمطية لبدء البرمجة. في محرر التعليمات البرمجية، أضف الأسطر التالية إلى أعلى نافذة البرمجة لإنشاء روتين فرعي:

Sub vlookup_fn1()End Sub

الروتين الفرعي عبارة عن حاوية لتعليمات VBA البرمجية وهو مطلوب لتشغيله بنجاح. البديل الآخر هو إنشاء نموذج مستخدم VBA لجعل واجهة المستخدم أكثر تفاعلية.

2. قم بالإعلان عن المتغيرات وإنشاء نطاقات مرجعية

أولاً، عليك الإعلان عن أنواع بيانات المتغيرات باستخدام الأمر Dim:

Dim i as integer, lastrow as long

بعد ذلك، قم بإنشاء متغير lastrow لتخزين قيمة آخر صف تم ملؤه في نطاق البحث. تستخدم الدالة lastrow الدالة end(xldown) لحساب مرجع الصف الأخير في نطاق معين.

في هذا المثال، يحتوي متغير الصف الأخير على قيمة آخر صف تم ملؤه في نطاق البحث، وتحديدًا 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 للبحث. كما يقومون أيضًا بتمرير النطاق الذي حددته في البداية وفهرس العمود ذي الصلة لجلب القيمة النهائية.

رمز vlookup جاهز، لكنك لا تزال بحاجة إلى زيادة متغير الصف في كل مرة في الحلقة:

i = i + 1

في كل مرة يتم تشغيل الحلقة، تزيد القيمة i بمقدار 1 . تذكر أن قيمة صف البداية هي 2، وتحدث الزيادة في كل مرة يتم فيها تشغيل الحلقة. استخدم الكلمة الأساسية "loop" لتعيين نهاية مقطع التعليمات البرمجية هذا.

عند تشغيل الكود بالكامل، سيتم ملء كلا العمودين بالنتائج، بناءً على القيم الموجودة في الجدول المصدر.

فيما يلي الرمز المرجعي بأكمله:

 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. قم بإنشاء زر لتشغيل الكود

يمكنك إنشاء زر في جدول Excel لتشغيله بنقرة واحدة. أدخل الشكل المطلوب في ورقة Excel الفارغة، ثم انقر بزر الماوس الأيمن عليه ثم انقر فوق خيار Assign Macro .

في مربع الحوار، حدد اسم الروتين الفرعي وانقر فوق " موافق" .

عندما تريد تشغيل التعليمات البرمجية، انقر فوق الزر لمعرفة كيفية ملء البيانات على الفور.

أعلاه هو كيفية استخدام Excel VBA لأتمتة وظائف البحث . نأمل أن تكون المقالة مفيدة لك.