دالة VLOOKUP | مركز البحوث والدراسات متعدد التخصصات

الدالة VLOOKUP في برنامج إكسل Excel

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

تلميح: جرب استخدام دالة Lookup الجديدة، وهي عبارة عن إصدار محسن من دالة VLOOKUP تعمل في أي اتجاه وتقوم بإرجاع التطابقات التامة بشكل افتراضي، مما يسهل استخدامها بشكل أكبر من مهمة دالة VLOOKUP.

تُشير الدالة VLOOKUP في أبسط نموذج لها إلى ما يلي:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

=VLOOKUP (ما الذي تريد البحث عنه، أين تريد البحث عنه، رقم العمود في النطاق الذي يحتوي على القيمة التي سيتم إرجاعها، إرجاع التطابق التقريبي أو التام والذي يكون 1/صواب، أو 0/خطأ).

تلميح: لاستخدام الدالة VLOOKUP بشكل صحيح، يجب عليك تنظيم بياناتك بحيث تكون القيمة التي تبحث عنها (مثلا: فاكهة) إلى يمين القيمة المرجعة (مثلا: كمية) التي تريد العثور عليها.

بناء الجملة

عند استخدام الدالة VLOOKUP في إكسل للبحث عن قيمة في جدول، يكون التركيب النحوي Syntax للجملة كما يلي:

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

أمثلة:

=VLOOKUP(A2,A10:C20,2,صحيح)

=VLOOKUP(“أبو عبيدة”,B2:E7,2,خطأ)

=VLOOKUP(A2,’تفاصيل العميل’!A:F,3,خطأ)

وسيطات الدالة

تستخدم الدالة VLOOKUP الوسيطات التالية:

اسم الوسيطة	الوصف
lookup_value (وسيطة مطلوبة)	القيمة التي تريد البحث عنها. يجب ان تكون القيمة التي تريد البحث عنها في العمود الأول من نطاق الخلايا الذي تحدده في الوسيطة *table_array. على سبيل المثال، إذا كانت مصفوفة الجدول* تشمل نطاق الخلايا B2: D7، فيجب أن يكون الـ lookup_value في العمود B. بإمكان *Lookup_value* أن تكون عبارة عن قيمة أو مرجع إلى خلية.
Table_array (مطلوبة)	نطاق الخلايا الذي ستبحث فيه الدالة VLOOKUP عن *lookup_value* والقيمة التي يتم إرجاعها. يمكنك استخدام نطاق أو جدول مسمى، ويمكنك استخدام الأسماء في الوسيطة بدلا من مراجع الخلايا. يجب ان يحتوي العمود الأول في نطاق الخلايا على الـ *lookup_value. يجب أيضًا أن يتضمن نطاق الخلايا القيمة المُرجعة التي تريد البحث عنها. تعرّف على كيفية تحديد النطاقات في ورقة عمل*.
col_index_num (وسيطة مطلوبة)	رقم العمود (بدءًا من 1 للعمود الأيمن الأيسر من *table_array*) الذي يحتوي على القيمة المُرجعة.
range_lookup (اختياري)	القيمة المنطقية التي تحدد إذا كنت تريد من الدالة VLOOKUP البحث عن تطابق تقريبي أو تطابق تام: التطابق التقريبي – 1/TRUE يفترض أن العمود الأول في الجدول يتم فرزه إما رقميًا أو أبجديًا، سيتم البحث عن أقرب قيمة. هذه هي الطريقة الافتراضية إذا لم تحدد أنت أي طريقة. على سبيل المثال، الصيغة: = VLOOKUP (90, A1: B100, 2, TRUE). التطابق التام – 0/FALSE البحث عن القيمة الصحيحة في العمود الأول. على سبيل المثال، الصيغة: = VLOOKUP (Sameh, A1: B100, 2, TRUE).

وسيطات الدالة VLOOKUP

بدء استخدام دالة VLOOKUP

ستحتاج إلى أربع معلومات حتى يمكنك إنشاء بناء جملة الدالة VLOOKUP، وهذه المعلومات هي:

القيمة التي تريد البحث عنها، والتي تسمى أيضًا قيمة البحث.
النطاق أو نطاق الخلايا الذي تقع فيه قيمة البحث، تذكّر أنه يجب أن تكون قيمة البحث دائمًا في العمود الأول في نطاق الدالة VLOOKUP لكي تعمل بشكل صحيح. على سبيل المثال، إذا كانت قيمة البحث في الخلية C2 فيجب أن يبدأ النطاق بـالعمود C.
رقم العمود في النطاق الذي يحتوي على القيمة المُرجعة. على سبيل المثال، إذا قمت بتحديد B2: D11 كنطاق، يجب أن تحسب B على أنه العمود الأول، C على أنه العمود الثاني، وهكذا.
بشكل اختياري، يمكنك تحديد “صحيح” TRUE إذا كنت تريد تطابقًا تقريبيًا أو “خطأ” FALSE إذا كنت تريد تطابقًا تامًا للقيمة المُرجعة. إذا لم تحدد أي شيء، فستكون القيمة الافتراضية دائمًا هي TRUE (أي مطابقة تقريبية).

الآن ضع كل ما ورد أعلاه معاً كما يلي، فينتج الصيغة التالية للدالة:

= VLOOKUP (قيمه البحث، النطاق الذي يحتوي على قيمة البحث، رقم العمود في النطاق الذي يحتوي على القيمة المُرجعة، التطابق التقريبي (TRUE) أو التطابق التام (FALSE)).

أمثلة استخدام دالة VLOOKUP

فيما يلي بعض الأمثلة على استخدام الدالة VLOOKUP في إكسل أو Excel:

المثال 1

المثال 2

المثال 3

المثال 4

المثال 5

دمج البيانات من جداول متعددة في ورقه عمل واحده باستخدام VLOOKUP

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

في المثال التالي، تحتوي الأعمدة A-F وH على قيم أو صيغ تستخدم القيم الموجودة في ورقة العمل فقط. بينما تستخدم الأعمدة المتبقية VLOOKUP وقيم العمود A (رمز العميل) والعمود B (المحامي) للحصول على بيانات أو معلومات من جداول أخرى.

دمج البيانات من جداول متعددة في ورقة عمل واحدة

1. انسخ الجدول الذي يحتوي على الحقول المشتركة إلى ورقة عمل جديدة، وادخل اسمًا أو تسمية له.

2. ثم انقر فوق بيانات > أدوات البيانات > العلاقات لفتح مربع الحوار إدارة العلاقات.

3. لكل علاقة مدرجة، لاحظ ما يلي:

الحقل الذي يربط الجداول (المدرجة في أقواس في مربع الحوار). هذا هو الـ lookup_value لصيغة أو دالة VLOOKUP.
اسم جدول البحث ذو الصلة. هذا هو الـ table_array في صيغة جملة أو دالة VLOOKUP.
الحقل (العمود) في جدول البحث ذو الصلة الذي يحتوي على البيانات أو المعلومات التي تريدها في العمود الجديد، لا تظهر هذه المعلومات في مربع الحوار “إدارة العلاقات”. ثم سيتعين عليك إلقاء نظرة على جدول البحث ذي الصلة للاطلاع على الحقل الذي تريد استرداده. لاحظ أن رقم العمود (A = 1)، هذا هو الـ col_index_num الموجود في الصيغة أو صيغة الدالة.

4. لإضافه حقل إلى الجدول الجديد، أدخل صيغة VLOOKUP في العمود الأول الفارغ باستخدام البيانات أو المعلومات التي قمت بجمعها في الخطوة 3.

في هذا المثال، يستخدم العمود G القيمة “[@Attorney]” (lookup_value) للحصول على بيانات معدل الفاتورة من العمود الرابع (col_index_num = 4) من جدول “tbl_Attorneys” في ورقة العمل، (table_array)، باستخدام الصيغة التالية:

=VLOOKUP ([@Attorney],tbl_Attorneys, 4, FALSE)

ثم يمكن ان تستخدم الصيغة أيضًا مرجع خلية ومرجع نطاق. كما أنه في المثال الخاص بنا، سيكون:

= VLOOKUP (A2, ‘Attorneys‘! A:D, 4, FALSE).

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

أخطاء شائعة في دالة VLOOKUP

المشكلة	الخطأ الذي حدث
تم إرجاع قيمة غير صحيحة	إذا كانت قيمة الوسيطة *range_lookup* تساوي TRUE أو إذا لم يتم تضمينها، فيجب فرز العمود الأول أبجديًا أو رقميًا. وإذا لم يتم فرز العمود الأول، فقد تكون القيمة المُرجعة عبارة عن شيء لم تكن تتوقعه. يتعينّ عليك فرز العمود الأول أو استخدام FALSE للحصول على تطابق تام.
ظهور ‎#N/A في الخلية	إذا كانت قيمة الوسيطة *range_lookup* هي TRUE، وكانت القيمة في *lookup_value* أصغر من القيمة الأصغر في العمود الأول التابع لـ *table_array، فستحصل عندئذٍ على قيمة الخطأ ‎#N/A. كما أنه إذا كانت قيمة الوسيطة range_lookup* هي FALSE، فتشير قيمة الخطأ ‎#N/A إلى أنه لم يتم العثور على الرقم الصحيح. للحصول على مزيد من المعلومات حول حل أخطاء #N/A في الدالة VLOOKUP، راجع كيفية تصحيح خطأ #N/A في الدالة VLOOKUP‏.
يحدث الخطأ ‎#REF!‎ في الخلية	أما في حالة إذا كانت قيمة *col_index_num* أكبر من عدد الأعمدة في *table-array، فستحصل على قيمة الخطأ‎REF! ‎. للحصول على مزيد من المعلومات حول حل أخطاء ‎#REF!‎ في الدالة VLOOKUP، اطلع على كيفية تصحيح الخطأ ‎#REF!‎*.
الخطأ ‎#VALUE!‎ في الخلية	وفي حالة إذا كانت قيمة *table_array* أقل من 1، فستحصل على الخطأ ‎#VALUE!‎. للحصول على مزيد من المعلومات حول حل أخطاء #VALUE! في الدالة VLOOKUP، راجع كيفية تصحيح خطأ #VALUE! في الدالة VLOOKUP‏.
#NAME؟ في الخلية	‎#NAME?‎ تعني قيمة الخطأ عادةً أن الصيغة تفتقد إلى علامات اقتباس. للبحث عن اسم أحد الأشياء أو الأشخاص، تأكد من استخدام علامات اقتباس حول الاسم في الصيغة. على سبيل المثال، أدخل الاسم “عبادي” في =VLOOKUP(“عبادي”,B2:E7,2,FALSE). للحصول على مزيد من المعلومات، اطلع على كيفية تصحيح الخطأ ‎#NAME!‎.

المشاكل الشائعة

أخطاء إملائية

المشكلة

الخطأ الذي حدث

أخطاء #SPILL! في الخلية

هذا الخطا #SPILL! يعني عادة أن الصيغة معتمدة على التقاطع الضمني لقيمة أو قيم البحث، واستخدام عمود بأكمله كمرجع. على سبيل المثال، = VLOOKUP (a:a, a:c, 2, FALSE). يمكنك حل هذه المشكلة عن طريق إرساء مرجع البحث مع المشغل @ كالتالي: = VLOOKUP (@A: A, a:c, 2, FALSE). بدلا من ذلك، يمكنك استخدام أسلوب VLOOKUP التقليدي والإشارة إلى خلية واحدة بدلا من عمود بأكمله كما يلي: = VLOOKUP (A2, a:c, 2, FALSE).

أخطاء إملائية في دالة VLOOKUP في إكسل

أفضل الممارسات لاستخدام دالة VLOOKUP

قم بما يلي	لماذا
استخدم المراجع المطلقة للوسيطة range_lookup	يسمح لك استخدام المراجع المطلقة بتعبئة الصيغة بحيث تبحث دائمًا في نطاق البحث نفسه. تعرّف على كيفية استخدام مراجع الخلايا المطلقة.
لا تخزّن القيم الرقمية أو قيم التاريخ كقيم نصية	عند البحث عن قيم رقمية أو قيم تاريخ، تأكد من عدم تخزين البيانات الموجودة في العمود الأول للوسيطة *table_array* كقيم نصية. وفي هذه الحالة، قد تُرجع VLOOKUP قيمة غير صحيحة أو غير متوقعة.
افرز العمود الأول	قم بفرز العمود الأول التابع للوسيطة *table_array* قبل استخدام الدالة VLOOKUP عندما تكون قيمة الوسيطة range_lookup هي TRUE.
استخدم أحرف البدل	إذا كان *range_lookup* تساوي FALSE و*lookup_value* عبارة عن نص، يمكنك استخدام أحرف البدل، مثل علامة الاستفهام(؟) والعلامة النجمية ()، في lookup_value. تطابق علامة الاستفهام أي حرف واحد. أما علامة النجمة، فتطابق أي تسلسل من الأحرف. وإذا أردت البحث عن علامة استفهام أو علامة نجمة فعلية، فاكتب علامة المد (~) قبل الحرف. على سبيل المثال، الدالة = VLOOKUP (“أبو عبيدة?”, B2: E7, 2, FALSE) ستبحث عن كل مثيلات أبو عبيدة* مع إمكانية أن يكون الحرف الأخير مختلف.
تأكد من عدم احتواء البيانات على أحرف خاطئة	عند البحث عن قيم نصية في العمود الأول، تأكد من عدم احتواء البيانات في العمود الأول على مسافات بادئة أو مسافات زائدة، أو من استخدام علامات الاقتباس (‘ أو “) وعلامات الاقتباس المتعرجة (‘ أو “) بطريقة غير متناسقة، أو من استخدام أحرف غير قابلة للطباعة. في هذه الحالات، قد تُرجع VLOOKUP قيمة غير متوقعة. للحصول على نتائج دقيقة، حاول استخدام الدالة CLEAN أو الدالة TRIM لإزالة المسافات الزائدة بعد قيم الجدول في الخلية.

أفضل الممارسات لاستخدام دالة VLOOKUP في إكسل

المراجع

برنامج الجداول الإلكترونية مايكروسوفت إكسل Microsoft Excel، ترجمة وإعداد: د. م. مصطفى عبيد، مركز البحوث والدراسات متعدد التخصصات، 2021.
الموقع الرسمي لشركة مايكروسوفت Microsoft.