VLOOKUP

 

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

دالة VLOOKUP لأداة BigQuery

تُجري بحثًا عموديًا وتعرض القيم في عمود بيانات استنادًا إلى موضع العناصر المطابقة في عمود البحث.

مثال

‫VLOOKUP("تفاح"،اسم_الجدول!الفاكهة،اسم_الجدول!السعر)

البنية

‫VLOOKUP(مفتاح_البحث، النطاق،الفهرس، يتم_ترتيبه)

• مفتاح_البحث: القيمة المطلوب البحث عنها في عمود البحث.
• عمود_البحث: عمود البيانات المطلوب البحث فيه.
• عمود_النتيجة: عمود البيانات المطلوب استخدامه للنتيجة.
• يتم_ترتيبه: [اختياري] الطريقة التي يتم بها العثور على تطابق مع مفتاح_البحث.
  • خطأ: للحصول على مطابقة تامة، ويُنصح باستخدام هذا الخيار.
  • صحيح: للحصول على مطابقة تقريبية، ويكون هذا هو الخيار التلقائي إذا لم يتم تحديد الإدخال يتم_ترتيبه.
    ملاحظة: قبل استخدام مطابقة تقريبية، عليك ترتيب مفتاح البحث تصاعديًا، وإلا فقد تحصل على قيمة معروضة غير صحيحة. تعرَّف على سبب ظهور قيمة معروضة خاطئة.

ملاحظة: لإنشاء طلبات أكثر مرونة في قواعد البيانات في BigQuery، استخدِم دالة XLOOKUP.

البنية

‫=VLOOKUP(مفتاح_البحث، النطاق، الفهرس، [يتم_ترتيبه])

الإدخالات

1. مفتاح_البحث: القيمة المطلوب البحث عنها في أول عمود من النطاق.
2. النطاق: القيم العليا والدنيا المطلوب البحث بينهما.
3. الفهرس: فهرس العمود الذي يتضمّن القيمة المعروضة في النطاق. يجب أن يكون الفهرس عددًا صحيحًا موجبًا.
4. يتم_ترتيبه: إدخال اختياري. حدِّد أحد الخيارَين التاليَين
   • خطأ = مطابقة تامة. وينُصح باستخدام هذا الخيار.
   • صحيح = مطابقة تقريبية. وهذا هو الخيار التلقائي إذا لم يتم تحديد الإدخال يتم_ترتيبه.
     ملاحظة مهمة: قبل استخدام مطابقة تقريبية، عليك ترتيب مفتاح البحث تصاعديًا، وإلا فقد تحصل على قيمة معروضة خاطئة. تعرَّف على سبب ظهور قيمة معروضة خاطئة.

القيمة المعروضة

أول قيمة مطابقة من النطاق المحدَّد.

التفاصيل الفنية:

مثال:

‫=VLOOKUP(G9،‏ B4:D8،‏ 3، خطأ)

‫=VLOOKUP("تفاح"، B4:D8،‏ 3، صحيح)

الإدخالات	الوصف
مفتاح_البحث	هذه هي القيمة التي تبحث عنها في أول عمود من النطاق. إذا كنت تتوقع قيمة ليست خطأ، يجب أن يكون مفتاح البحث في أول عمود من النطاق. مرجع الخلية متاح أيضًا. لإجراء عملية تحقق بسيطة: إذا كان مفتاح_البحث متوفّرًا في B3، يجب أن يبدأ نطاقك بالعمود B.
النطاق	في هذا النطاق: تبحث الدالة عن مفتاح البحث المحدَّد في أول عمود. تعرض VLOOKUP القيمة من العمود الذي يحدِّده الفهرس. يمكنك أيضًا استخدام نطاق مُعنوَن. لعرض قيمة ليست خطأ، يجب أن يكون مفتاح البحث في أول عمود من النطاق. لإجراء عملية تحقق بسيطة: إذا كان مفتاح_البحث متوفّرًا في B3، يجب أن يبدأ نطاقك بالعمود B.
الفهرس	يُعرف أيضًا باسم "رقم العمود". وهذا هو فهرس العمود في النطاق الذي يحتوي على القيمة المعروضة. أصغر فهرس ممكن هو 1. أكبر فهرس ممكن هو الحد الأقصى لعدد الأعمدة في هذا النطاق. بعد إعداد النطاق، لا تبحث دالة VLOOKUP في عمود مفتاح البحث إلا عندما يكون الفهرس = 1، أو عدد الأعمدة التي تكون على يساره. نصيحة: عند استخدام VLOOKUP، تخيَّل أنّ أعمدة النطاق مرقَّمة من اليمين إلى اليسار وتبدأ بالعدد 1.
يتم_ترتيبه	هذا الإدخال اختياري. الخياران المتاحان هما صحيح وخطأ. إذا تم ضبط الإدخال يتم_ترتيبه على صحيح، ستستخدم دالة VLOOKUP المطابقة التقريبية. ملاحظة مهمة: قبل استخدام مطابقة تقريبية، عليك ترتيب مفتاح البحث تصاعديًا، وإلا فقد تحصل على قيمة غير متوقّعة. تعرَّف على سبب ظهور قيمة معروضة خاطئة. إذا كانت قيمة الإدخال يتم_ترتيبه هي خطأ، ستستخدم دالة VLOOKUP المطابقة التامة. إذا لم يتم تحديد الإدخال يتم_ترتيبه، تكون القيمة هي صحيح تلقائيًا. ننصحك بشدة بإجراء ما يلي: استخدِم الخيار خطأ للإدخال يتم_ترتيبه بسبب نمطه المتسق سواء تم ترتيب عمود مفتاح البحث أم لا. حدِّد دائمًا قيمة الإدخال يتم_ترتيبه لتحسين قابلية القراءة، على الرغم من أنّ هذا الإدخال اختياري.

 

المُخرَجات

الوصف

القيمة المعروضة

هذه هي القيمة التي تعرضها دالة VLOOKUP استنادًا إلى الإدخالات. كل دالة من دوال VLOOKUP تعرض قيمة واحدة فقط. في حال تطابق قيم متعدّدة لمفتاح البحث، يتم عرض القيمة في عمود القيمة المعروضة التي تتم أولاً مطابقة مفتاح البحث المرتبط بها في عمود مفتاح البحث. في حال عرض خطأ ‎#N/A، لن يتم العثور على قيمة. في حال ظهور قيمة أو خطأ متوقع، مثل ‎#N/A أو #VALUE!‎، عليك تحديد المشاكل وحلّها. إذا أردت استبدال ‎#N/A بقيمة أخرى، تعرَّف على مزيد من المعلومات حول كيفية استخدام IFNA()‎ على VLOOKUP()‎.

أمثلة على دالة VLOOKUP الأساسية:

دالة VLOOKUP على مفاتيح بحث مختلفة

استخدِم دالة VLOOKUP لمعرفة سعر البرتقال والتفاح.

تجربة الدالة

الشرح:

عند استخدام دالة VLOOKUP، يمكنك استخدام مفاتيح بحث مختلفة مثل "تفاح" و"برتقال".

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

مفتاح_البحث هو "برتقال"	‫=VLOOKUP("برتقال"، B4:D8،‏ 3، خطأ) القيمة المعروضة = 1.01 دولار أمريكي
مفتاح_البحث هو "تفاح"	‫=VLOOKUP("تفاح"، B4:D8،‏ 3، خطأ) القيمة المعروضة = 1.50 دولار أمريكي
مفتاح_البحث الذي يستخدم مرجع الخلية "تفاح" في G9.	‫=VLOOKUP(G9،‏ B4:D8،‏ 3، خطأ) القيمة المعروضة = 1.50 دولار أمريكي

‫VLOOKUP على فهارس أعمدة مختلفة

استخدِم VLOOKUP لمعرفة كمية "البرتقال" في عمود الفهرس الثاني.

تجربة الدالة

الشرح:

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

الفهرس = 2 معرفة كمية البرتقال، وهي العمود الثاني في النطاق

‫=VLOOKUP(G3،‏ B4:D8،‏ 2، خطأ) القيمة المعروضة = 5

مطابقة تامة أو مطابقة تقريبية من خلال VLOOKUP

• استخدِم المطابقة التامة من خلال VLOOKUP للعثور على معرّف دقيق.
• استخدِم المطابقة التقريبية من خلال VLOOKUP للعثور على معرّف تقريبي.

تجربة الدالة

الشرح:

استخدِم مطابقة تقريبية أو الإدخال يتم_ترتيبه = صحيح عند البحث عن أفضل مطابقة، وليس عن مطابقة تامة.

إذا أردت البحث عن المعرّف = 102، ولكن الرقم غير متاح في الجدول، ستتراجع المطابقة التقريبية خطوةً واحدة لعرض النتيجة: المعرّف = 101. وذلك لأنّه في عمود مفتاح البحث، تكون القيمة 101 هي أقرب قيمة أقلّ من 102.

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

ملاحظة مهمة: قبل استخدام مطابقة تقريبية، عليك ترتيب مفتاح البحث تصاعديًا لعرض القيمة الصحيحة، وإلا قد تظهر لك قيمة غير متوقّعة.

عند البحث عن مطابقة تامة، مثلاً يتم_ترتيبه = خطأ، سيتم عرض مطابقة تامة. على سبيل المثال، اسم الفاكهة للمعرّف = 103 هو "موز". إذا لم تكن هناك مطابقة تامة، ستحصل على خطأ ‎#N/A. وبما أنّه يمكن التنبؤ بنمط المطابقة التامة بدرجة أكبر، ننصحك باستخدامها.

مطابقة تامة	‫=VLOOKUP(G6،‏ A4:D8،‏ 2، خطأ) القيمة المعروضة = "تفاح"
مطابقة تقريبية	‫=VLOOKUP(G3،‏ A4:D8،‏ 2، صحيح) أو ‫=VLOOKUP(G3،‏ A4:D8،‏ 2) القيمة المعروضة = "موز"

التطبيقات الشائعة لدالة VLOOKUP

استبدال قيمة الخطأ من VLOOKUP

قد تريد استبدال قيمة الخطأ التي تعرضها VLOOKUP عند عدم توفّر مفتاح البحث. في هذه الحالة، إذا كنت لا تريد عرض ‎#N/A، يمكنك استخدام دوال IFNA()‎ لاستبدال ‎#N/A. مزيد من المعلومات عن IFNA()‎.

تجربة الدالة

في الأصل، تعرض VLOOKUPالخطأ ‎#N/A لأنّ مفتاح البحث "Pencil" غير متوفّر في العمود "Fruit". تستبدل IFNA()‎ الخطأ ‎#N/A بالإدخال الثاني المحدَّد في الدالة. في هذه الحالة، الإدخال هو "لم يتم العثور عليه".

‫=IFNA(VLOOKUP(G3, B4:D8, 3, خطأ),"لم يتم العثور عليه") القيمة المعروضة = "لم يتم العثور عليه"

ملاحظة: إذا كنت تريد استبدال أخطاء أخرى مثل ‎#REF!‎، تعرَّف على مزيد من المعلومات عن IFERROR()‎.

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

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

تجربة الدالة

1. يمكنك إنشاء عمود مساعد إذا كنت تستخدم "&" للجمع بين الاسم الأول واسم العائلة.	حدِّد ‎=C4&D4 واسحب للأسفل بداية من B4 إلى B8 لإنشاء "العمود المساعد".
2. استخدِم مرجع الخلية B7, JohnLee كمفتاح البحث.	‫=VLOOKUP(B7, B4:E8, 4, خطأ) القيمة المعروضة = "الدعم"

استخدام VLOOKUP مع حرف بدل أو مطابقات جزئية

في VLOOKUP، يمكنك أيضًا استخدام أحرف البدل أو المطابقات الجزئية. يمكنك استخدام أحرف البدل التالية:

• تتطابق علامة الاستفهام "؟" مع أي حرف مفرد.
• تتطابق علامة النجمة "*" مع أي تسلسل من الأحرف.

لاستخدام أحرف البدل في VLOOKUP، يجب استخدام مطابقة تامة: "يتم_ترتيبه = خطأ".

تجربة الدالة

يتم استخدام "سم*" لمطابقة أي نص يبدأ بالحرفين "سم" بغض النظر عن عدد الأحرف، مثل "سمر" أو "سم2" أو "سماعات" أو "سمرررررررر".

‫=VLOOKUP("سم*", B4:D8, 3, خطأ) القيمة المعروضة = "التسويق"

تحديد الأخطاء وتصحيحها وأفضل الممارسات:

القيمة المعروضة غير صحيحة

• عرض قيمة غير متوقعة: إذا تم ضبط الإدخال يتم_ترتيبه على صحيح، ولكن لم يتم ترتيب أول عمود في النطاق رقميًا أو أبجديًا بترتيب تصاعدي، إذًا عليك تغيير الإدخال "يتم_ترتيبه" إلى خطأ.

• تقديم VLOOKUP لأول مطابقة: تعرض VLOOKUP أول مطابقة فقط. إذا كان لديك عدة مفاتيح بحث مطابِقة، يتم عرض قيمة، ولكنها قد لا تكون القيمة المتوقّعة.
• البيانات غير النظيفة: إنّ القيم التي تليها أو تسبقها مسافات قد تبدو أحيانًا متشابهةً، إلا أنّ VLOOKUP تتعامل معها بشكلٍ مختلف. على سبيل المثال، تتعامل دالة VLOOKUP بشكل مختلف مع القيم التالية:
  • " Apple"
  • "Apple "
  • "Apple"

للحصول على النتائج المتوقّعة، عليك إزالة المسافات قبل استخدام VLOOKUP.

للحصول على مزيد من المعلومات، يمكنك الاطّلاع على قسم أفضل الممارسات.

‎#N/A

• إذا تم استخدام مطابقة تقريبية أو كان الإدخال يتم_ترتيبه = صحيح وإذا كان مفتاح البحث في VLOOKUP أصغر من أصغر قيمة في أول عمود، حينئذٍ تعرض VLOOKUP الخطأ ‎#N/A.
• إذا تم استخدام مطابقة تامة أو كان الإدخال يتم_ترتيبه = خطأ، لن يتم العثور على المطابقة التامة لمفتاح البحث في VLOOKUP في أول عمود. إذا كنت لا تريد عرض ‎#N/A عند عدم العثور على مفتاح البحث في أول عمود، يمكنك استخدام الدالة IFNA()‎.

‎#REF!‎

يمكنك تحديد نطاق عن طريق الخطأ بعدد أعمدة أكبر من الحد الأقصى لعدد أعمدة النطاق. ولتجنّب ذلك، تأكَّد من إجراء ما يلي:

• عدّ الأعمدة للنطاق المحدَّد، وليس للجدول بالكامل
• بدء العدّ من 1 بدلاً من 0

‎#VALUE!‎

إذا حصلت على الخطأ ‎#VALUE!‎، قد يرجع السبب إلى أنّه:

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

ملاحظة مهمة: يمكن إدخال رقم فقط في الفهرس.

‎#NAME?‎

• ربما أغفلت عن إضافة علامة اقتباس في مفتاح البحث عندما يكون مفتاح_البحث بيانات نصية.

أفضل الممارسات

 

المهام	السبب
استخدام المراجع المطلقة للنطاق	عليك استخدام: مرجع مطلق لنطاق VLOOKUP ‫VLOOKUP(G3, $B$3:$D$7, 3, خطأ) يجب عدم استخدام: ‫VLOOKUP(G3, B3:D7, 3, خطأ) يمنع هذا حدوث تغييرات غير متوقّعة في النطاق عند نسخه أو سحبه للأسفل.
ترتيب أول عمود تصاعديًا عند استخدام مطابقة تقريبية، مثلاً يتم_ترتيبه = صحيح	إذا كنت تستخدم مطابقة تقريبية أو كان الإدخال يتم_ترتيبه = صحيح، عليك ترتيب أول عمود تصاعديًا، وإلا فمن المرجّح أنّ تحصل على قيمة عرض غير صحيحة. مزيد من المعلومات حول كيفية ترتيب البيانات
تنظيف البيانات قبل استخدام VLOOKUP	قبل استخدام VLOOKUP، لا تنسَ تنظيف البيانات. قد تؤدي البيانات غير النظيفة إلى أن تعرض VLOOKUP قيمة غير متوقّعة. في ما يلي بعض المشاكل الشائعة للبيانات غير النظيفة: المسافات البادئة: " apple" المسافات اللاحقة: "apple " الفراغات أو المسافات: "" و " " غير متطابقين لإزالة المسافات البيضاء البادئة أو اللاحقة، يمكنك النقر على البيانات تنظيف البيانات إزالة المسافة البيضاء.
عدم إدخال قيم الأرقام أو التاريخ كنص	تأكَّد من عدم إدخال قيم التاريخ أو الأرقام في أول عمود من نطاق VLOOKUP، مثل عمود مفتاح البحث، كقيم نصية. قد تحصل على قيمة معروضة غير متوقّعة. في أعلى "جداول بيانات Google"، اختَر عمود مفتاح البحث. انقر على قائمة التنسيق رقم. حدِّد خيارًا حسب نوع البيانات المطلوب: التاريخ الرقم