اكسل

وظيفة Excel VLOOKUP

Excel Vlookup Function

وظيفة Excel VLOOKUPملخص

VLOOKUP هي إحدى وظائف Excel للبحث عن البيانات في جدول منظم عموديًا. يدعم VLOOKUP المطابقة التقريبية والدقيقة ، و البدل (*؟) للمطابقات الجزئية. يجب أن تظهر قيم البحث في ملف أول عمود الجدول الذي تم تمريره إلى VLOOKUP.



الغرض ابحث عن قيمة في جدول عن طريق المطابقة في العمود الأول قيمة الإرجاع القيمة المتطابقة من جدول. التركيب = VLOOKUP (القيمة ، الجدول ، col_index ، [range_lookup]) الوسائط
  • القيمة - القيمة التي يجب البحث عنها في العمود الأول من الجدول.
  • طاولة - الجدول المراد استرجاع قيمة منه.
  • col_index - العمود في الجدول المراد استرداد قيمة منه.
  • مجموعة البحث - [اختياري] TRUE = تطابق تقريبي (افتراضي). خطأ = تطابق تام.
إصدار برنامج Excel 2003 ملاحظات الاستخدام

VLOOKUP هي إحدى وظائف Excel للحصول على البيانات من جدول منظم عموديًا. يجب أن تظهر قيم البحث في ملف أول عمود الجدول الذي تم تمريره إلى VLOOKUP. يدعم VLOOKUP المطابقة التقريبية والدقيقة ، و البدل (*؟) للمطابقات الجزئية.

البيانات العمودية | أرقام الأعمدة | يبدو فقط على حق | أوضاع المطابقة | تطابق تام | تطابق تقريبي | المباراة الأولى | مباراة البدل | بحث ثنائي الاتجاه | معايير متعددة | # لا يوجد أخطاء | أشرطة فيديو





V عمودي

الغرض من VLOOKUP هو الحصول على معلومات من جدول منظم على النحو التالي:

VLOOKUP مخصص للبيانات العمودية



باستخدام رقم الطلب في العمود B كقيمة بحث ، يمكن لـ VLOOKUP الحصول على معرف العميل والمبلغ والاسم والحالة لأي طلب. على سبيل المثال ، للحصول على اسم العميل للطلب 1004 ، تكون الصيغة هي:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

بالنسبة للبيانات الأفقية ، يمكنك استخدام هلوكوب و الفهرس والمطابقة ، أو XLOOKUP .

تغيير النص إلى رقم في Excel

يعتمد VLOOKUP على أرقام الأعمدة

عند استخدام VLOOKUP ، تخيل أن كل عمود في ملف طاولة مُرقَّم ، بدءًا من اليسار. للحصول على قيمة من عمود معين ، قم بتوفير الرقم المناسب كـ 'فهرس العمود'. على سبيل المثال ، فهرس العمود لاسترداد الاسم الأول أدناه هو 2:

مثال على المطابقة التامة لـ VLOOKUP

يمكن استرداد اسم العائلة والبريد الإلكتروني من خلال العمودين 3 و 4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

يبدو VLOOKUP صحيحًا فقط

يمكن أن ينظر VLOOKUP إلى اليمين فقط. يمكن أن تظهر البيانات التي تريد استردادها (قيم النتائج) في أي عمود إلى اليمين من قيم البحث:

يمكن أن ينظر VLOOKUP إلى اليمين فقط

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

مطابقة تامة وتقريبية

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

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

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

ملاحظة: يمكنك أيضًا توفير صفر (0) بدلاً من FALSE لمطابقة تامة.

تطابق تام

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

تطابق تام VLOOKUP مع الأفلام

الصيغة في H6 لإيجاد عام ، بناءً على تطابق تام مع عنوان الفيلم ، هو:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

تطابق تقريبي

في الحالات التي تريدها افضل مباراة ، وليس بالضرورة تطابق تام ، سترغب في استخدام الوضع التقريبي. على سبيل المثال ، أدناه نريد البحث عن معدل العمولة في الجدول G5: H10. تأتي قيم البحث من العمود C. في هذا المثال ، نحتاج إلى استخدام VLOOKUP في تطابق تقريبي الوضع ، لأنه في معظم الحالات لن يتم العثور على تطابق تام. تم تكوين صيغة VLOOKUP في D5 لإجراء تطابق تقريبي عن طريق تعيين الوسيطة الأخيرة على TRUE:

معدل عمولة المطابقة التقريبية لـ VLOOKUP

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

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

ملاحظة: يجب فرز البيانات بترتيب تصاعدي حسب قيمة البحث عند استخدام وضع المطابقة التقريبي مع VLOOKUP.

المباراة الأولى

في حالة وجود قيم مكررة ، سيجد VLOOKUP ملف المباراة الأولى عندما يكون وضع المطابقة دقيقًا. في الشاشة أدناه ، تم تكوين VLOOKUP للعثور على سعر اللون 'الأخضر'. توجد ثلاثة إدخالات باللون الأخضر ، وتقوم VLOOKUP بإرجاع سعر ملف أول الدخول 17 دولارًا. الصيغة في الخلية F5 هي:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

تقوم VLOOKUP بإرجاع أول مباراة

مباراة البدل

تدعم وظيفة VLOOKUP ملفات البدل ، مما يجعل من الممكن إجراء تطابق جزئي على قيمة بحث. على سبيل المثال ، يمكنك استخدام VLOOKUP لاسترداد القيم من جدول بعد الكتابة فقط جزء من قيمة البحث. لاستخدام أحرف البدل مع VLOOKUP ، يجب عليك تحديد وضع المطابقة التامة عن طريق توفير FALSE أو 0 للوسيطة الأخيرة ، مجموعة البحث . تسترجع الصيغة في H7 الاسم الأول ، 'Michael' ، بعد كتابة 'Aya' في الخلية H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

مباراة البدل VLOOKUP

اقرأ المزيد شرح مفصل هنا .

بحث ثنائي الاتجاه

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

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

بحث ثنائي الاتجاه لـ VLOOKUP

لمزيد من التفاصيل، انظر هذا المثال .

ملاحظة: بشكل عام ، الفهرس والمطابقة هي طريقة أكثر مرونة إجراء عمليات بحث ثنائية الاتجاه .

معايير متعددة

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

 
=C5&D5 // helper column

تم تكوين VLOOKUP للقيام بنفس الشيء لإنشاء قيمة بحث. الصيغة في H6 هي:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

VLOOKUP بمعايير متعددة

للتفاصيل، انظر هذا المثال .

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

أخطاء VLOOKUP و # N / A

إذا كنت تستخدم VLOOKUP ، فستواجه حتما الخطأ # N / A. الخطأ # N / A يعني فقط 'غير موجود'. على سبيل المثال ، في الشاشة أدناه ، لا توجد قيمة البحث 'Toy Story 2' في جدول البحث ، وتعيد جميع صيغ VLOOKUP الثلاثة # N / A:

مثال على خطأ VLOOKUP # N / A

تتمثل إحدى طرق 'اعتراض' خطأ NA في استخدام الامتداد دالة IFNA مثله:

مثال على خطأ VLOOKUP # N / A - ثابت

الصيغة في H6 هي:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

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

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

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

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

اقرأ أكثر: VLOOKUP بدون أخطاء # N / A

المزيد حول VLOOKUP

الملاحظات الأخرى

  • مجموعة البحث يتحكم في ما إذا كان القيمة يجب أن تتطابق تمامًا أم لا. القيمة الافتراضية هي TRUE = السماح بالمطابقة غير التامة.
  • يضع مجموعة البحث إلى FALSE إلى يتطلب تطابق تام و TRUE لـ السماح بمطابقة غير تامة .
  • لو مجموعة البحث هي TRUE (الإعداد الافتراضي) ، وستؤدي المطابقة غير التامة إلى مطابقة وظيفة VLOOKUP لأقرب قيمة في الجدول وهي لا يزال أقل من القيمة .
  • متي مجموعة البحث تم حذفه ، ستسمح وظيفة VLOOKUP بمطابقة غير تامة ، ولكنها ستستخدم المطابقة التامة في حالة وجودها.
  • لو مجموعة البحث هو TRUE (الإعداد الافتراضي) تأكد من فرز قيم البحث في الصف الأول من الجدول بترتيب تصاعدي. وإلا ، فقد ترجع VLOOKUP قيمة غير صحيحة أو غير متوقعة.
  • لو مجموعة البحث هي FALSE (تتطلب المطابقة التامة) ، والقيم الموجودة في العمود الأول من طاولة لا تحتاج إلى الفرز.


^