VLOOKUP Function from basic to advanced (دالة VLOOKUP من الأساسي إلى المتقدم)
محتويات البرنامج التعليمي:
1- ماهي دالة (VLOOKUP) / About the VLOOKUP function
2- كيفية استخدام دالة (VLOOKUP) / How to use the VLOOKUP function
1-2 VLOOKUP بمطابقة تامة / VLOOKUP with an exact match
2-2 VLOOKUP مع المطابقة التقريبية / VLOOKUP with approximate match
3- VLOOKUP باستخدام القوائم المنسدلة
VLOOKUP Using Drop-Down Lists
4- كيفية استخدام VLOOKUP من ورقة أخرى
How to use VLOOKUP from another sheet
5- VLOOKUP مطابقة جزئية / VLOOKUP partial match
6- كيفية البحث عن معايير متعددة / How to Vlookup multiple criteria
1-6 دالة VLOOKUP بمعيارين / VLOOKUP with two criteria
2-6 دالة VLOOKUP مع أعمدة متعددة / Excel VLOOKUP Multiple Columns
7- الأخطاء الشائعة في وظيفة VLOOKUP
Common Errors in VLOOKUP Function
الدالة (VLOOKUP) هي إحدى وظائف Excel ويرمز لها بـ "البحث العامودي". فهي تجعل Excel يبحث عن قيمة معينة في جدول بيانات منظم عامودياََ يسمى بـ "مصفوفة الجدول" (table array) ، من أجل إرجاع قيمة من عمود مختلف في نفس الصف. دالة (VLOOKUP) تدعم المطابقة التامة والتقريبية , وسوف تتعلم في هذا الدرس كيفية استخدام وظيفة VLOOKUP.
الغرض منها: البحث عن قيمة في جدول عن طريق المطابقة في العامود الأول
القيمة المرجعة (الناتج): القيمة المتطابقة من الجدول
بناء الجملة (Syntax):
=VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])
الحجج أو العناصر أو الوسائط (Arguments):
lookup_value - القيمة التي يجب البحث عنها في العمود الأول من الجدول.
table_array - الجدول المراد استرداد قيمة منه.
column_index_num - رقم العمود في الجدول المراد استرداد قيمة منه.
range_lookup - [اختياري] TRUE = تطابق تقريبي (approximate match),
(افتراضي) FALSE = تطابق تام (exact match).
ملاحظة مهمة (Note):
في البحث التقريبي (approximate match)، تأكد من فرز القائمة بترتيب تصاعدي (من أعلى إلى أسفل) أو من (A to Z)، وإلا فقد تكون النتيجة غير دقيقة.
--------------------------------
2- كيفية استخدام دالة (VLOOKUP) / How to use the VLOOKUP function
1-2 VLOOKUP مع مطابقة تامة / VLOOKUP with an exact match
مثال:
لنفترض أن لديك جدول بيانات يحتوي على معلومات حول المنتجات في متجر، وتريد استرداد سعر منتج معين بناءً على اسمه.
شرح المثال:
كما هو موضح في الصورة أعلاه بإستخدام دالة (VLOOKUP) حصلنا على سعر المنتج (Banana) وكانت الصيغة كالتالي:
=VLOOKUP("Banana",$B$4:$D$8,3,FALSE)
(Banana) - lookup_value: وهي ما نريد أن نبحث عنه
(B4:D8) - table_array: هو جدول البيانات
(3) - column_index_num: هو رقم العامود
range_lookup - (FALSE): هو تطابق تام (exact match)
أقرأ أيضاََ: Read also
--------------------------------------
2-2 VLOOKUP مع مطابقة تقريبية / VLOOKUP with approximate match
مثال:
لنفترض أن لديك جدول بيانات يحتوي على معلومات حول الطلاب ودرجات الاختبار المقابلة لهم. تريد العثور على المستوى الدراسي للطالب بناءً على درجات الاختبار الخاصة به.
شرح المثال:
TestScore هو مرجع الخلية لدرجة الاختبار (85).
A4: C7 هو نطاق جدول البيانات.
2 يمثل رقم العمود الذي تريد استرداد القيمة منه وهو (Grade Level).
TRUE - هي المطابقة التقريبية.
وكما شاهدنا في الصورة أعلاه تم العثور على مستوى الطالب وهو (B) وكانت نتيجة الصيغة كالتالي:
=VLOOKUP(E4,$B$4:$C$7,2,TRUE)
3- VLOOKUP باستخدام القوائم المنسدلة / VLOOKUP Using Drop-Down Lists
مثال:
لنفترض أن لديك جدول بيانات يحتوي على معلومات حول الموظفين ، وتريد استرداد راتب موظف معين بناءً على هويته (Employee ID) وذلك بإستخدام القوائم المنسدلة.
شرح المثال:
Employee ID - هو مرجع الخلية لأسم الموظف الذي أدخلته.
B4: E8 - هو نطاق جدول البيانات ، باستثناء الرؤوس.
4 يمثل رقم العمود الذي تريد استرداد القيمة منه.
FALSE أي المطابقة التامة.
والصيغة التي أستخدمت كانت كالتالي:
=VLOOKUP(G5,$B$4:$E$8,4,0)
ولمعرفة كيفية إدراج قائمة منسدلة شاهد هذا الفديو من هنا
4- كيفية استخدام VLOOKUP من ورقة أخرى / How to use VLOOKUP from another sheet
مثال :
لنفترض أن لديك جدول بيانات يتكون من أسماء عملاء , وشركات متابعين لها , ومكان إقامتهم , ورقمهم الوظيفي. وأنت تريد أن تعرف ماهي الشركات والدول التابعة لبعض العملاء وكيفية إختيارهم من جدول بيانات موجود في ورقة أخرى.
شرح المثال:
كما هو موضح في المثال بإستخدام دالة (VLOOKUP) أستطعنا أن نحصل على أسماء الشركات والدول التابعة لبعض العملاء وكانت الصيغة كالتالي:
=VLOOKUP($B5,Data!$A$2:$D$30,3,0)
وضعنا علامة ($) قبل (B5) وذلك لكي يتم تثبيت العامود عند نقله إلى الخلية التالية وتلك تسمي (Mixed references).
أقرأ أيضاََ: Read also
==================
5- VLOOKUP مطابقة جزئية / VLOOKUP partial match
مثال:
تدعم وظيفة (VLOOKUP) أحرف البدل ، مما يجعل من الممكن إجراء تطابق جزئي على قيمة بحث. على سبيل المثال ، يمكنك استخدام (VLOOKUP) لاسترداد القيم من جدول بيانات بناءً على كتابة جزء فقط من قيمة البحث.
في هذا المثال نفترض أن لديك جدول بيانات مكون من بعض المنتجات وأسعارها , وتريد إسترداد سعر منتج معين من جدول البيانات بإستخدام التطابق الجزئي , بمعنى أخر هو كتابة بعض الأحرف من اسم المنتج المحدد. وسوف نستخدم علامة النجمة (*) كحرف بدل يطابق صفرًا أو أكثر من الأحرف. ولكي يتم ربط قيمة النطاق المحدد بحرف البدل علينا إستخدام علامة العطف (&) للتسلسل. على سبيل المثال إذا قمنا بكتابة سلسلة مثل "Ora" في خلية البحث تكون النتيجة "*Ora", والتي يتم إرجاعها مباشرة إلى VLOOKUP كقيمة بحث , وهي إختصار لكلمة (Orange).
شرح المثال:
Ama (E4&"*") - هو مرجع الخلية لأسم المنتج (Amazon).
B4:C8 - هو نطاق جدول البيانات.
2 يمثل رقم العمود الذي تريد استرداد سعر المنتج منه.
FALSE أو (0) أي المطابقة التامة.
والصيغة التي أستخدمت كانت كالتالي:
=VLOOKUP(E4&"*",B4:C8,2,0)
أقرأ أيضاََ: Read also
====================
6- كيفية البحث عن معايير متعددة / How to Vlookup multiple criteria
1-6 دالة VLOOKUP بمعيارين / VLOOKUP with two criteria
مثال:
لنفترض أن لديك جدول بيانات يحتوي على معلومات حول المنتجات بما في ذلك السعر والكمية. تريد استرداد سعر المنتج بناء على معيارين , إسم المنتج (Product Name) وإسم مندوب المبيعات (Salesperson).
إن دالة (Vlookup) على الشكل المعتاد لا تعمل في هذه الحالة لأنها تُرجع أول تطابق تم العثور عليه بناءََ على قيمة بحث واحدة تحددها. ولكي نتغلب على هذا، يمكننا إضافة عامود مساعد بقيم عامودي العميل والمنتج
(Product Name and Salesperson) وهو أول عامود في جدول البيانات وإسمه (Helper) لأنه هو المكان الذي تبحث فيه دالة (Vlookup) دائماًً عن قيمة البحث.
وكانت صيغة العامود المساعد (Helper) كالتالي:
=H4&" "&H5
وبعد ذلك نستخدم دالة (Vlookup) ونضع مكان (lookup_value) نفس صيغة العامود المساعد (Helper) كما هو موضح في الصورة أعلاه ، فكانت نتيجة الصيغة النهائية كالتالي:
=VLOOKUP(H4&" "&H5,B4:E9,4,0)
2-6 دالة VLOOKUP مع أعمدة متعددة / Excel VLOOKUP Multiple Columns
مثال (1):
أحد الجوانب السلبية لاستخدام VLOOKUP هو أنه يمكنه إرجاع القيمة من عمود واحد فقط.
لنفترض أن لديك جدول بيانات يحتوي على معلومات حول المنتجات وأرقام معاملاتها وأسعارها. وتريد الحصول على مجموعة من فئات المنتجات (Category) وأسعار أو مبالغ المبيعات (Sales Amount) بناء على رقم المعاملة (Transaction ID) وذلك في قائمة واحدة. تابع الخطوات حول كيفية دالة (VLOOKUP) مع أعمدة متعددة.
الشرح بالخطوات:
الخطوة الأولى: حدد الخلية التي تريد إدراج القيم فيها، ثم أدخل دالة (VLOOKUP) في الخلية المحددة.
=VLOOKUP(
الخطوة الثانية: أدخل الوسيطة الأولى (Lookup_value) وهي (F4) والوسيطة الثانية (Table_array) وهو (B4:D9) ثم الوسيطة الثالثة ({Col_index_num1, Col_index_num2}) وهي ({2,3}).
=VLOOKUP(F4,$B$4:$D$9,{2,3},
الخطوة الثالثة: نحتاج الى إدخال الوسيطة الرابعة والأخيرة وهي ([Range_lookup]) وهي (0) فكانت نتيجة الصيغة النهائية كما هو موضح في الصورة أعلاه كالتالي:
=VLOOKUP(F4,$B$4:$D$9,{2,3},0)
===================
مثال (2):
من المميزات القوية جدأ في دالة (VLOOKUP) هي دمجها مع وظائف أو دوال أخرى مثل
(Sum, Max, or Average) وذلك لحساب القيم في أعمدة متعددة. وعندما يتم دمج دالة (VLOOKUP) مع دالة أخرى تسمى هذه الصيغة بصيغة صفيف (array formula) ولكي تعمل هذه الصيغة نحتاج الضغط على (CTRL+SHIFT+ENTER) في نهاية الصيغة.
لنفترض أن لديك جدول بيانات يحتوي على معلومات عن المنتجات ومبيعات شهر (March) وشهر (April). وتريد العدد الإجمالي لمنتج معين خلال الشهرين المذكورة.
الخطوة الأولى: أدخل دالة (Sum) في الخلية المحددة ثم أدخل دالة (VLOOKUP) كما هو موضح في الصورة أعلاه ..
=SUM(VLOOKUP(
الخطوة الثانية: أدخل وسيطات دالة (VLOOKUP).
الوسيطة الأولى (Lookup_value) وهي (G19) , والوسيطة الثانية (Table_array) وهي (B17:D22) , ثم الوسيطة الثالثة ({Col_index_num1, Col_index_num2}) وهي جمع العامود {2,3} ثم الوسيطة الرابعة والأخيرة ([Range_lookup]). فأصبحت نتيجة الصيغة كالتالي:
=SUM(VLOOKUP(G19,$B$17:$D$22,{2,3},0))
أقرأ أيضاََ: Read also
7- الأخطاء الشائعة في وظيفة VLOOKUP
Common Errors in VLOOKUP Function
1- N/A# خطأ (error) – يحدث إذا فشلت وظيفة VLOOKUP في العثور على تطابق مع الوسيطة lookup_value .
2- REF# خطأ (error) – يحدث عندما تكون الوسيطة col_index_num أكبر (>) من عدد الأعمدة في الوسيطة table_array , أو تحاول الصيغة الإشارة إلى خلايا غير موجودة .
3- VALUE# خطأ (error) – يحدث إذا كانت الوسيطة col_index_num أقل (<) من 1 أو لم يتم التعرف عليها كقيمة رقمية ، أو لم يتم التعرف على وسيطة range_lookup كإحدى القيم المنطقية TRUE أو FALSE.
في نهاية الدرس: هذا كل ما تحتاج لمعرفته حول VLOOKUP في Excel. ولمزيد من التوضيح قم بتنزيل مصنف Excel وشاهد الفديوا. شكراَ لمتابعتكم وأتمني لكم الفائدة واذا لديكم أي إستفسارات رجاء ضعها في التعليقات.
Download the Excel Tutorial workbook
قم بتنزيل مصنف Excel التعليمي
تعليقات
إرسال تعليق