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

• شرح الحجج خطوة بخطوة
• النحو والحجج
• أمثلة على VLOOKUP
  المطابقة التامة مقابل المطابقة التقريبية   |   Vlookup مع شروط متعددة
• VLOOKUP الأخطاء الشائعة والحلول
  # لا يوجد خطأ   |   #VALUE خطأ   |   خطأ #REF   |   قيمة غير صحيحة

أمثلة على VLOOKUP .xlsx أخطاء VLOOKUP .xlsx

مقاطع فيديو ذات صلة

شرح الحجج خطوة بخطوة

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

الخطوة 1: ابدأ وظيفة VLOOKUP

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

=VLOOKUP(

الخطوة 2: حدد قيمة البحث

أولاً ، حدد قيمة البحث (وهو ما تبحث عنه) في وظيفة VLOOKUP. هنا ، أشير إلى الخلية G6 التي تحتوي على رقم معرف معين 1005.

=VLOOKUP(G6

الخطوة 3: حدد مصفوفة الجدول

بعد ذلك ، حدد نطاقًا من الخلايا يحتوي على كل من القيمة التي تبحث عنها والقيمة التي تريد إرجاعها. في هذه الحالة ، أحدد النطاق B6: E12. تظهر الصيغة الآن على النحو التالي:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

الخطوة 4: حدد العمود الذي تريد إرجاع قيمة منه

ثم حدد العمود الذي تريد إرجاع قيمة منه.

في هذا المثال ، حيث أحتاج إلى إرجاع البريد الإلكتروني بناءً على رقم المعرف ، أدخل هنا رقمًا 4 لإخبار VLOOKUP بإرجاع قيمة من العمود الرابع من نطاق البيانات.

=VLOOKUP(G6,B6:E12,4

الخطوة 5: ابحث عن تطابق تقريبي أو تطابق تام

أخيرًا ، حدد ما إذا كنت تبحث عن تطابق تقريبي أم تطابق تام.

• للعثور على تطابق تام، تحتاج إلى استخدام خاطئة كحجة أخيرة.
• للعثور على تطابق تقريبي، استخدم الحقيقة كحجة أخيرة ، أو اتركها فارغة فقط.

في هذا المثال ، أستخدم FALSE للمطابقة التامة. تبدو الصيغة الآن كما يلي:

=VLOOKUP(G6,B6:E12,4,FALSE

اضغط على مفتاح Enter للحصول على النتيجة

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

النحو والحجج

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

• ابحث عن القيمة (مطلوب): القيمة (قيمة حقيقية أو مرجع خلية) التي تبحث عنها. تذكر أن هذه القيمة يجب أن تكون في العمود الأول من table_array.
• Table_array (مطلوب): يحتوي نطاق الخلايا على كل من عمود قيمة البحث وعمود القيمة المرجعة.
• Col_index (مطلوب): يمثل العدد الصحيح رقم العمود الذي يحتوي على القيمة المرجعة. يبدأ بالرقم 1 للعمود الموجود في أقصى اليسار من table_array.
• مجموعة البحث (اختياري): قيمة منطقية تحدد ما إذا كنت تريد أن يبحث VLOOKUP عن تطابق تقريبي أو تطابق تام.
  • تطابق تقريبي - اضبط هذه الحجة على الحقيقة, 1 أو اتركه فارغة.
    مهم: للعثور على تطابق تقريبي ، يجب فرز القيم الموجودة في العمود الأول من table_array بترتيب تصاعدي في حالة إرجاع VLOOKUP للنتيجة الخاطئة.
  • تطابق تام - اضبط هذه الحجة على خاطئة or 0.

أمثلة

يوضح هذا القسم بعض الأمثلة لمساعدتك في الحصول على فهم أكثر شمولاً لوظيفة VLOOKUP.

مثال 1: المطابقة التامة مقابل المطابقة التقريبية في VLOOKUP

إذا كنت مرتبكًا بشأن المطابقة التامة والمطابقة التقريبية عند استخدام VLOOKUP ، فيمكن أن يساعدك هذا القسم في إزالة هذا الالتباس.

تطابق تام في VLOOKUP

في هذا المثال ، سأجد الأسماء المقابلة بناءً على الدرجات المدرجة في النطاق E6: E8 ، لذلك أدخل الصيغة التالية في الخلية F6 واسحب مقبض الملء التلقائي لأسفل إلى F8. في هذه الصيغة ، يتم تحديد الوسيطة الأخيرة كـ خاطئة لإجراء بحث مطابق تمامًا.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

ومع ذلك ، نظرًا لعدم وجود الدرجة 98 في العمود الأول من نطاق البيانات ، تُرجع VLOOKUP نتيجة الخطأ # N / A.

تطابق تقريبي في VLOOKUP

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

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

نظرًا لعدم وجود الدرجة 98 ، تعثر VLOOKUP على القيمة التالية الأكبر التي تقل عن 98 ، وهي 95 ، وتُرجع اسم الدرجة 95 كأقرب نتيجة.

مثال 2: استخدم VLOOKUP مع معايير متعددة

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

الخطوة 1: قم بإضافة عمود مساعد لتوصيل القيم من أعمدة البحث

في هذه الحالة ، نحتاج إلى إنشاء عمود مساعد لربط القيم من الاسم العمود و القسم المخصص العمود.

1. أضف عمودًا مساعدًا إلى يسار نطاق بياناتك وأدخل رأسًا لهذا العمود. انظر لقطة الشاشة:
   
   
2. في عمود المساعد هذا ، حدد الخلية الأولى أسفل الرأس ، وأدخل الصيغة التالية في ملف شريط الصيغة، و اضغط أدخل.
   

   =C6&" "&D6

   
   ملاحظة: في هذه الصيغة ، نستخدم علامة العطف (&) لربط النص في عمودين لإنتاج جزء واحد من النص.

   • C6 هو الاسم الأول لـ الاسم عمود للانضمام ، D6 هو أول قسم في القسم المخصص العمود للانضمام.
   • يتم ربط قيم هاتين الخليتين بمسافة بينهما.
3. حدد خلية النتيجة هذه ، ثم اسحب ملف مقبض الملء التلقائي لأسفل لتطبيق هذه الصيغة على الخلايا الأخرى في نفس العمود.
   
   

الخطوة 2: قم بتطبيق وظيفة VLOOKUP وفقًا للمعايير المحددة

حدد خلية حيث تريد إخراج النتيجة (هنا أحدد I7) ، أدخل الصيغة التالية في ملف شريط الصيغة، ثم اضغط أدخل.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

نتيجة

أخطاء وحلول VLOOKUP الشائعة

يسرد هذا القسم الأخطاء الشائعة التي قد تواجهها عند استخدام VLOOKUP ويوفر الحلول لإصلاحها.

# N / A خطأ يتم إرجاعه

الخطأ الأكثر شيوعًا في VLOOKUP هو الخطأ # N / A ، مما يعني أن Excel لم يتمكن من العثور على القيمة التي كنت تبحث عنها. فيما يلي بعض الأسباب التي قد تؤدي إلى إرجاع VLOOKUP للخطأ # N / A.

السبب 1: قيمة البحث ليست في العمود الأول من table_array

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

كما هو موضح في لقطة الشاشة أدناه ، أريد إرجاع اسم بناءً على المسمى الوظيفي المحدد. هنا قيمة البحث (مدير المبيعات) في العمود الثاني من table_array وقيمة الإرجاع على يسار عمود البحث ، لذلك تُرجع VLOOKUP الخطأ # N / A.

الحلول

يمكنك تطبيق أي من الحلول التالية لإصلاح هذا الخطأ.

• أعد ترتيب الأعمدة
  
  يمكنك إعادة ترتيب الأعمدة لوضع عمود البحث في العمود الأول من table_array.
• استخدم دالتي INDEX و MATCH معًا
  
  هنا نستخدم وظائف INDEX و MATCH معًا كبديل لـ VLOOKUP لحل هذه المشكلة.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• استخدم الدالة XLOOKUP (متوفرة في Excel 365 و Excel 2021 والإصدارات الأحدث)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

السبب 2: لم يتم العثور على قيمة البحث في عمود البحث (مطابقة تامة)

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

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

الحلول

لإصلاح هذا الخطأ ، يمكنك تجربة أحد الحلول التالية.

• إذا كنت تريد أن يبحث VLOOKUP عن القيمة التالية الأكبر التي تكون أقل من قيمة البحث ، فقم بتغيير الوسيطة الأخيرة خاطئة (تطابق تام) إلى الحقيقة (تطابق تقريبي). لمزيد من المعلومات ، يرجى الاطلاع مثال 1: المطابقة التامة مقابل المطابقة التقريبية باستخدام VLOOKUP.
• لتجنب تغيير الوسيطة الأخيرة والحصول على تذكير في حالة عدم العثور على قيمة البحث ، يمكنك تضمين الدالة VLOOKUP داخل الدالة IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

السبب 3: قيمة البحث أصغر من أصغر قيمة في عمود البحث (تطابق تقريبي)

كما هو موضح في لقطة الشاشة أدناه ، تقوم بإجراء بحث تقريبي عن المطابقة. القيمة التي تبحث عنها (رقم المعرف 1001 في هذه الحالة) أصغر من أصغر قيمة 1002 في عمود البحث ، لذلك تُرجع VLOOKUP الخطأ # N / A.

الحلول

إليك حلين لك.

• تأكد من أن قيمة البحث أكبر من أو تساوي أصغر قيمة في عمود البحث.
• إذا كنت تريد أن يذكرك Excel بأنه لم يتم العثور على قيمة البحث ، فقم فقط بتضمين دالة VLOOKUP في الدالة IFERROR على النحو التالي:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

السبب 4: تنسيق الأرقام كنص

كما ترى في لقطة الشاشة أدناه ، يرجع الخطأ # N / A في هذا المثال إلى عدم تطابق نوع البيانات بين خلية البحث (G6) وعمود البحث (B6: B12) في الجدول الأصلي. هنا القيمة في G6 هي رقم ، والقيم الموجودة في النطاق B6: B12 هي أرقام منسقة كنص.

الحلول

لحل هذه المشكلة ، تحتاج إلى تحويل قيمة البحث مرة أخرى إلى رقم. إليك طريقتان لك.

• قم بتطبيق ميزة التحويل إلى رقم
  
  انقر فوق الخلية التي تريد تحويل النص إلى رقم ، حدد هذا الزر  بجانب الخلية ثم حدد قم بالتحويل إلى رقم.
  
• تطبيق أداة يدوية لتحويل دفعة بين النص والرقم
  
  • التحويل بين النص والرقم سمة من سمات Kutools for Excel يساعدك على تحويل مجموعة من الخلايا بسهولة من نص إلى رقم والعكس صحيح. احصل على نسخة تجريبية مجانية كاملة الميزات لمدة 30 يومًا الآن!
  

السبب 5: الجدول table_array ليس ثابتًا عند سحب صيغة VLOOKUP إلى خلايا أخرى

كما هو موضح في لقطة الشاشة أدناه ، هناك قيمتان للبحث في E6 و E7. بعد الحصول على النتيجة الأولى في F6 ، اسحب صيغة VLOOKUP من الخلية F6 إلى F7 ، تم إرجاع نتيجة خطأ # N / A. ذلك لأن مراجع الخلايا (B6: C12) نسبية بشكل افتراضي ، ويتم تعديلها أثناء التنقل لأسفل خلال الصفوف. تم نقل مصفوفة الجدول إلى B7: C13 ، والتي لم تعد تحتوي على درجة البحث 73.

الحلول

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

يتم إرجاع خطأ #VALUE

قد تتسبب الحالات التالية في قيام VLOOKUP بإرجاع نتيجة الخطأ #VALUE.

السبب 1: تتجاوز قيمة البحث 255 حرفًا

كما هو موضح في لقطة الشاشة أدناه ، تتجاوز قيمة البحث في الخلية H4 255 حرفًا ، لذلك تُرجع VLOOKUP نتيجة الخطأ #VALUE.

الحلول

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

• الفهرس والمطابقة:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• دالة XLOOKUP (متوفر في Excel 365 و Excel 2021 والإصدارات الأحدث):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

السبب 2: وسيطة col_index أقل من 1

يحدد فهرس العمود رقم العمود في صفيف الجدول الذي يحتوي على القيمة التي تريد إرجاعها. يجب أن تكون هذه الوسيطة رقمًا موجبًا يتوافق مع عمود صالح في صفيف الجدول.

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

الحلول

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

يتم إرجاع خطأ #REF

يسرد هذا القسم أحد أسباب إرجاع VLOOKUP للخطأ #REF ويقدم حلولاً لهذه المشكلة.

السبب: الوسيطة col_index أكبر من عدد الأعمدة

كما ترى في لقطة الشاشة أدناه ، تحتوي مصفوفة الجدول على 4 أعمدة فقط. ومع ذلك ، فإن فهرس العمود الذي حددته في صيغة VLOOKUP هو 5 ، وهو أكبر من عدد الأعمدة في صفيف الجدول. نتيجة لذلك ، لن يتمكن VLOOKUP من تحديد موقع العمود وسيعيد في النهاية الخطأ #REF.

الحلول

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

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  ملاحظة: في الصيغة أعلاه ، MATCH ("Email"، B5: E5، 0) يتم استخدام الوظيفة للحصول على رقم عمود "البريد الإلكتروني"في نطاق التاريخ B6: E12. هنا تكون النتيجة 4 ، والتي تُستخدم على أنها col_index في دالة VLOOKUP.

يتم إرجاع قيمة غير صحيحة

إذا وجدت أن VLOOKUP لا يقوم بإرجاع النتيجة الصحيحة ، فقد يرجع ذلك إلى الأسباب التالية

السبب 1: لا يتم فرز عمود البحث بترتيب تصاعدي

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

الحلول

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

1. حدد خلايا البيانات في عمود البحث ، وانتقل إلى ملف البيانات ، انقر فوق الفرز من الأصغر إلى الأكبر في ال فرز وتصفية المجموعة.
2. في مجلة فرز تحذير في مربع الحوار ، حدد ملف قم بتوسيع التحديد الخيار، وانقر OK.

السبب 2: تم إدراج عمود أو إزالته

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

الحلول

إليك حلين لك.

• يمكنك تغيير رقم فهرس العمود يدويًا لمطابقة موضع عمود الرجوع. يجب تغيير الصيغة هنا إلى:

  =VLOOKUP(H6,B6:F12,5,FALSE)

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

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

ملاحظات وظيفية أخرى

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

مقالات ذات صلة