استخدام وظيفة VLOOKUP وأمثلة محددة

تعد وظيفة VLOOKUP في Excel مفيدة للغاية في البحث عن البيانات. إليك ما تحتاج إلى معرفته حول استخدام الدالة VLOOKUP في Excel .

تريد التحقق من قيمة معينة في جدول بيانات Excel والبحث عن معلومات حولها. يؤدي تشغيل VLOOKUP إلى توفير جهد كبير في هذه الحالة. إنها إحدى أفضل الطرق لتنفيذ استعلام عمودي في Microsoft Excel. ستخبرك المقالة بالمعنى والاستخدام والأمثلة التوضيحية لوظيفة VLOOKUP في Microsoft Excel.

• هذه هي الوظائف الأساسية في Excel والتي تحتاج إلى فهمها

كيفية استخدام وظيفة VLOOKUP

• ما هي وظيفة VLOOKUP؟
• صيغة دالة VLOOKUP في Excel 2016، 2013، 2010، 2007، 2003
• فيديو تعليمي حول استخدام وظيفة VLOOKUP
• مثال 1: استخدم وظيفة VLOOKUP لتقييم تقييمات الطلاب والموظفين:
• مثال 2: استخدم VLOOKUP للكشف المطلق للحصول على البيانات
• مثال 3: استخدم VLOOKUP لاستخراج البيانات
• المثال 4: استخدم الدالة VLOOKUP في ورقتي Excel مختلفتين
• ما الذي يجب استخدام وظيفة VLOOKUP فيه؟
• أشياء يجب تذكرها حول وظيفة VLOOKUP

ما هي وظيفة VLOOKUP؟

وظيفة VLOOKUP هي وظيفة بحث عن البيانات في Excel. أفترض أن لديك خلفية في برنامج Excel ويمكنك استخدام الوظائف الأساسية مثل SUM وAVERAGE وTODAY. إحدى وظائف VLOOKUP الأكثر شيوعًا هي وظيفة البيانات، مما يعني أنها تعمل على جداول قاعدة البيانات، أو ببساطة، قوائم العناصر. القائمة لديها الكثير من التنوع. يمكنك إنشاء جدول حول موظفي شركتك، أو فئات المنتجات، أو قوائم العملاء، أو أي شيء آخر. وفيما يلي قائمة بالمنتجات التي تبيعها الشركة "أ" في السوق:

غالبًا ما تحتوي جداول قاعدة البيانات على معرفات لكل منتج. في جدول البيانات أعلاه، علامة التعريف الخاصة هي عمود "رمز الصنف". ملاحظة: لتتمكن من استخدام الدالة VLOOKUP، يجب أن يحتوي جدول البيانات على عمود يحتوي على علامات تعريف مثل الكود أو المعرف، ويجب أن يكون العمود الأول مثل الجدول أعلاه.

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

على الجانب السلبي، وظيفة VLOOKUP محدودة ولها قيم افتراضية خطيرة. على عكس INDEX وMATCH (أو XLOOKUP)، تحتاج وظيفة VLOOKUP إلى جدول كامل يحتوي على قيم البحث في العمود الأول. وهذا يجعل استخدام الدالة VLOOKUP بمعايير متعددة أمرًا صعبًا. بالإضافة إلى ذلك، فإن سلوك المطابقة الافتراضي لـ VLOOKUP يجعل من السهل الحصول على نتائج غير صحيحة. لكن لا تقلق! مفتاح استخدام وظيفة VLOOKUP بنجاح هو إتقان الأساسيات.

تحتوي VLOOKUP على 4 وسيطات: lookup_value، وtable_array، وcolumn_index_num، وrange_lookup. Lookup_value هي القيمة التي يجب البحث عنها وtable_array هو نطاق البيانات الرأسي الذي يجب البحث فيه. يجب أن يحتوي العمود الأول من table_array على قيم البحث المطلوب البحث عنها. الوسيطة column_index_num هي رقم عمود القيمة المراد استردادها، حيث يكون العمود الأول في table_array هو العمود 1.

وأخيرًا، يتحكم range_lookup في سلوك البحث عن التطابق. إذا كانت قيمة range_lookup تساوي TRUE، فستجري الدالة VLOOKUP تطابقًا تقريبيًا. إذا كانت قيمة range_lookup FALSE، فستجري الدالة VLOOKUP تطابقًا تامًا. الأهم من ذلك، أن range_lookup اختياري ويتم تعيينه افتراضيًا على TRUE، لذلك ستجري VLOOKUP مطابقة تقريبية بشكل افتراضي.

صيغة وظيفة VLOOKUP في Excel

تحتوي الدالة VLOOKUP في Excel على الصيغة التالية:

=VLOOKUP(lookup_value,table_array,col_index_num ,[range_lookup] )

هناك:

• VLOOKUP: هو اسم الوظيفة
• المعلمات بالخط العريض مطلوبة.
• lookup_value: القيمة المستخدمة للبحث
• table_array: جدول يحتوي على القيمة المراد البحث عنها، ضع القيمة المطلقة مع علامة $ في المقدمة، على سبيل المثال: $A$3:$E$40.
• col_index_num: ترتيب العمود الذي يحتوي على قيمة البحث في table_array. على سبيل المثال، في الجدول $A$3:$E$40، يحتوي العمود B على القيمة المطلوب البحث عنها، ثم col_index_num هنا سيكون 2؛ الجدول $C$3:$F$40، يحتوي العمود E على قيمة البحث، ثم col_index_num هنا هو 3.
• range_lookup: هو نطاق البحث، TRUE يعادل 1 (بحث نسبي)، FALSE يعادل 0 (بحث مطلق). هذه المعلمة ليست مطلوبة دائمًا في الصيغة.

لا يمكن تطبيق البحث النسبي إلا عندما تكون القيم المطلوب البحث عنها في table_array مرتبة بالترتيب (تصاعدي أو تنازلي أو أبجديًا). باستخدام هذه الجداول، يمكنك استخدام البحث النسبي، وهو ما يشبه استخدام دالة IF لا نهائية . بالنسبة للقيم التي لا يمكن البحث عنها أو التي لم يتم فرزها، استخدم البحث المطلق للعثور على القيمة الدقيقة.

فيديو تعليمي حول استخدام وظيفة VLOOKUP

مثال 1: وظيفة VLOOKUP لتقييم تقييمات الطلاب والموظفين

لنفترض أن لديك نسخة الطالب على النحو التالي:

وجدول تنظيم الترتيب هو كما يلي:

سنستخدم الآن وظيفة VLOOKUP لإدخال تقييمات الطلاب. ستلاحظ أن جدول الترتيب قد تم ترتيبه من الأقل إلى الأعلى (من الضعيف إلى الجيد)، لذا يمكننا في هذه الحالة استخدام البحث النسبي.

في Excel يتم عرض هذين الجدولين على النحو التالي:

هنا، القيمة المطلوب اكتشافها موجودة في العمود C، بدءًا من السطر 6. نطاق البحث هو $A$18:$B$21، وترتيب العمود الذي يحتوي على القيمة المكتشفة هو 2.

في الخلية D6، أدخل الصيغة: =VLOOKUP($C6,$A$18:$B$21,2,1). هذه صيغة بحث نسبية، يمكنك إجراء بحث مطلق إذا أردت (أو لأن الترتيب لم يتم فرزه) عن طريق إضافة 0 إلى الصيغة مثل هذا: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). اضغط دخول.

انقر فوق الخلية D6، وسيظهر مربع صغير في الزاوية اليمنى السفلية، انقر عليه واسحب الجدول لأسفل لنسخ صيغة الدرجات للطلاب المتبقين.

وفي ذلك الوقت كانت لدينا نتائج استخدام الدالة VLOOKUP لتصنيف الأداء الأكاديمي للطلاب كما يلي:

هل تتساءل لماذا يجب استخدام $ قبل C6؟ $ سيبقي العمود C ثابتًا، مع تغيير الصفوف فقط عند سحب الصيغة إلى أسفل الجدول. هناك أيضًا $A$18:$B$21 للمساعدة في إصلاح جدول قواعد الترتيب، مما يجعله لا يتغير عند سحب الصيغة.

مثال 2: تقوم وظيفة VLOOKUP بالبحث بشكل مطلق للحصول على البيانات

لنفترض أن لديك جدول بيانات الموظف، حيث يقوم بتخزين رمز الموظف والاسم الكامل والمنصب. ويخزن جدول آخر رمز الموظف ومسقط رأسه ومستوى التعليم. الآن تريد ملء معلومات البلدة والمستوى التعليمي لكل موظف، فماذا عليك أن تفعل؟

لنفترض أن لديك جدول الموظفين ومسقط رأس الموظفين على النحو التالي:

الآن تريد ملء معلومات مسقط رأس الموظفين. في الخلية D4، أدخل صيغة البحث المطلقة كما يلي: =Vlookup($A4,$A$16:$C$25,2,0)

ثم اضغط على Enter. انقر فوق المربع الصغير الذي يظهر في زاوية الخلية D4 واسحبه لأسفل عبر الجدول لنسخ الصيغة إلى الموظفين الآخرين.

لملء معلومات التأهيل للموظفين، في الخلية E4، أدخل صيغة البحث المطلقة: =VLOOKUP($A4,$A$16:$C$25,3,0)

مع الاستمرار في الضغط على Enter والتمرير للأسفل لنسخ الصيغة إلى باقي الموظفين، سنحصل على النتيجة التالية:

مثال 3: استخدم VLOOKUP لاستخراج البيانات

بالاستمرار في مجموعة البيانات في المثال 2، سنجد الآن مسقط رأس 3 موظفين: Nguyen Hoang Anh، وTran Van Anh، وNguyen Quang Vinh. لقد قمت باستخراجه إلى جدول جديد F15:G18.

سأقوم بهذا البحث في الجدول A3:E13، بعد ملء بيانات مسقط رأسي ومؤهلاتي. الآن، أدخل صيغة البحث المطلقة التالية في الخلية G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > اضغط على Enter.

بنسخ الصيغة للموظفين المتبقيين نحصل على النتيجة التالية:

لاحظ أنه في هذا المثال، قيمة الكشف موجودة في العمود B، لذلك يتم حساب منطقة الكشف من العمود B وليس من العمود A.

المثال 4: استخدم الدالة VLOOKUP في ورقتي Excel مختلفتين

وبالعودة إلى مجموعة البيانات في المثال 2، بعد ملء مؤهلات الموظف ومسقط رأسه، نقوم بتسمية الورقة QTM.

في ورقة أخرى من جدول البيانات، تسمى QTM1، تحتاج إلى الحصول على معلومات حول مؤهلات الموظف ومنصبه مع تغيير ترتيب ترتيب الموظف. هذا عندما ترى القوة الحقيقية لوظيفة VLOOKUP.

للعثور على بيانات حول "مؤهلات" الموظف، أدخل الصيغة: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) في الخلية C4 من الورقة QTM1.

هناك:

• B4 هو العمود الذي يحتوي على القيمة المستخدمة للكشف.
• كيو تي ام! هو اسم الورقة التي تحتوي على الجدول الذي يحتوي على القيمة المراد اكتشافها. بعد اسم الورقة، أضف !
• $B$3:$E$13 هو الجدول الذي يحتوي على قيم البحث والورقة التي تحتوي على الجدول (QTM).
• 4 هو الرقم التسلسلي لعمود "المؤهلات"، ويتم حسابه من عمود "الاسم الكامل" في ورقة QTM.
• 0 هو الكشف المطلق.

اضغط على Enter، ثم انسخ الصيغة لجميع الموظفين المتبقين في الجدول، نحصل على النتيجة التالية:

للعثور على بيانات "المنصب" الخاصة بالموظف، في الخلية D4 من الورقة QTM1، أدخل الصيغة: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0)، اضغط على Enter.

وبنسخ الصيغة لبقية الموظفين نحصل على ما يلي:

ما الذي يجب استخدام وظيفة VLOOKUP فيه؟

نحتاج أولاً إلى العثور على الإجابة الدقيقة للسؤال "لماذا نستخدم وظيفة VLOOKUP؟".

يتم استخدام وظيفة VLOOKUP لدعم البحث عن المعلومات في حقل بيانات أو قائمة بناءً على المعرفات المتاحة.

على سبيل المثال، سيؤدي إدراج وظيفة VLOOKUP مع رمز المنتج في جدول بيانات آخر إلى عرض معلومات المنتج المقابلة لهذا الرمز. يمكن أن تكون هذه المعلومات وصفًا وسعرًا وكمية المخزون، اعتمادًا على محتوى الوصفة التي تكتبها.

كلما قلت كمية المعلومات التي تحتاج إلى العثور عليها، زادت صعوبة كتابة الدالة VLOOKUP. عادةً ما تستخدم هذه الوظيفة في ورقة عمل قابلة لإعادة الاستخدام كقالب. في كل مرة يتم فيها إدخال رمز المنتج المناسب، سيقوم النظام باسترداد كافة المعلومات الضرورية حول المنتج المقابل.

أشياء يجب تذكرها حول وظيفة VLOOKUP

فيما يلي قائمة بالأشياء المهمة التي يجب تذكرها حول وظيفة VLOOKUP في Excel:

• عند حذف range_lookup، ستسمح VLOOKUP بالمطابقات غير التامة، ولكنها ستستمر في استخدام المطابقات التامة إذا كانت متوفرة.
• أكبر قيود الدالة هو أنها تأخذ دائمًا القيمة الموجودة على اليمين. ستقوم الدالة باسترداد البيانات من الأعمدة الموجودة على يمين العمود الأول في الجدول.
• إذا كان عمود البحث يحتوي على قيم مكررة، فستطابق الدالة VLOOKUP القيمة الأولى فقط.
• الدالة ليست حساسة لحالة الأحرف.
• افترض أن هناك صيغة VLOOKUP موجودة في ورقة العمل. في هذه الحالة، قد تنكسر الصيغ إذا قمت بإدراج عمود في الجدول، لأن قيم فهرس العمود تكون مشفرة بشكل ثابت، ولا تتغير تلقائيًا عند إدراج الأعمدة أو حذفها.
• تسمح VLOOKUP باستخدام أحرف البدل، على سبيل المثال، العلامات النجمية (*) أو علامات الاستفهام (؟).
• لنفترض أنك تعمل في جدول باستخدام دالة تحتوي على أرقام تم إدخالها كنص. إذا كنت تحصل فقط على أرقام كنص من عمود في جدول، فهذا لا يهم. ولكن إذا كان العمود الأول من الجدول يحتوي على أرقام تم إدخالها كنص، فإن العلامة #N/A! سيتم عرضه إذا كانت قيمة البحث ليست نصًا أيضًا.
• خطأ # غير متاح! يحدث إذا لم تعثر الدالة VLOOKUP على تطابق لقيمة lookup_value المتوفرة.
• خطأ #REF! يحدث إذا:
  • الوسيطة col_index_num أكبر من عدد الأعمدة في table_array المتوفرة
  • حاولت الصيغة الإشارة إلى خلايا غير موجودة.
• الخطأ #VALUE! يحدث إذا:
  • الوسيطة col_index_num أقل من 1 أو لم يتم التعرف عليها كقيمة رقمية
  • لم يتم التعرف على الوسيطة range_lookup كإحدى القيم المنطقية TRUE أو FALSE.

الاختلافات الرئيسية بين وظائف VLOOKUP وXLOOKUP في Excel

ص - نعم

ن - لا

شاهد المزيد:

• كيفية استخدام الدالة IF في Excel
• كيفية استخدام الدالة AVERAGEIF في Excel

بعد معرفة كيفية استخدام VLookup، تحتاج أيضًا إلى معرفة الأخطاء الشائعة وكيفية إصلاح المشكلات.

الأخطاء الشائعة عند استخدام الدالة VLOOKUP في Excel

قيمة البحث موجودة في العمود الخطأ

أحد الأخطاء الأكثر شيوعًا التي ستواجهها عند استخدام صيغة VLOOKUP هو أن الدالة تُرجع القيمة #N/A فقط. يحدث هذا الخطأ عندما لا يتمكن من العثور على قيمة البحث التي طلبت من VLOOKUP البحث عنها.

في بعض الحالات، يمكنك استخدام الدالة XLOOKUP بدلاً من VLOOKUP، ومع ذلك، لا يمكن تصحيح هذا الخطأ بسهولة إلا في الدالة VLOOKUP.

كيف تحل المشكلة:

قد يكون سبب عدم عثور VLOOKUP على القيمة المطلوبة هو عدم وجود القيمة في الجدول. ومع ذلك، إذا قامت بإرجاع كافة قيم #N/A، فإن قيمة البحث هي نص، فمن المرجح أن يكون ذلك لأنه لا يمكن قراءتها إلا من اليسار إلى اليمين.

عند إنشاء جدول في Excel لـ VLOOKUP، تأكد من وضع قيمة البحث على يسار القيمة التي تريد إرجاعها. أسهل طريقة للقيام بذلك هي وضعها في العمود الأول في الجدول.

الآن ستعيد الصيغة قيمة:

بشكل عام، تعد VLOOKUP طريقة رائعة للاستعلام عن البيانات بشكل أسرع في Microsoft Excel. على الرغم من أن استعلامات VLOOKUP تعمل عموديًا على الأعمدة ولديها بعض القيود الأخرى، إلا أن Microsoft تقوم باستمرار بتحديث ميزات البحث في Excel لتوسيع إمكانية تطبيقها. على سبيل المثال، يمكن أن تساعدك HLOOKUP وXLOOKUP... في البحث عن البيانات في العديد من الاتجاهات المختلفة. سيستمر موقع LuckyTemplates.com في تزويدك بالمعلومات حول كيفية استخدام هذه الوظيفة في المقالات التالية.