10 أمثلة على VLOOKUP للمبتدئين والمستخدمين المتقدمين

وظيفة VLOOKUP هي المعيار.

إذا كنت تعرف كيفية استخدام وظيفة VLOOKUP ، فأنت تعرف شيئًا عن Excel.

إذا لم تقم بذلك ، فمن الأفضل ألا تدرج Excel كأحد نقاط قوتك في سيرتك الذاتية.

لقد أجريت مقابلة جماعية وبمجرد أن ذكر أحد المرشحين أن برنامج Excel هو مجال خبرته ، كان أول ما تم طرحه هو - لقد فهمت الفكرة - ميزة VLOOKUP.

الآن بعد أن عرفنا أهمية وظيفة Excel هذه ، فمن المنطقي استيعابها تمامًا حتى نتمكن من القول بفخر - "أعرف شيئًا أو اثنين في Excel."

سيكون هذا برنامجًا تعليميًا ضخمًا لـ VLOOKUP (وفقًا لمعاييري).

سأغطي كل شيء عن ذلك ، ثم أعرض لك مثالًا مفيدًا وعمليًا عن VLOOKUP.

متى تستخدم وظيفة VLOOKUP في Excel؟

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

لنأخذ مثالًا بسيطًا هنا لفهم متى يجب استخدام Vlookup في Excel.

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

وإليك كيف يعمل:

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

هذا هو بالضبط ما تفعله لك ميزة Excel VLOOKUP (لا تتردد في استخدام هذا المثال في مقابلتك التالية).

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

بناء الجملة

= VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

معلمات الإدخال

• ابحث عن القيمة -هذه هي قيمة البحث التي تحاول العثور عليها في العمود الموجود في أقصى اليسار من الجدول.يمكن أن تكون قيمة أو مرجع خلية أو سلسلة نصية.في مثال ورقة التسجيل ، سيكون هذا هو اسمك.
• table_array -هذا هو صفيف الجداول الذي تبحث فيه عن القيم.يمكن أن يكون هذا مرجعًا إلى نطاق خلايا أو نطاق مسمى.في مثال جدول الدرجات ، سيكون هذا هو الجدول بأكمله الذي يحتوي على درجات الجميع لكل موضوع
• col_index -هذا هو رقم فهرس العمود الذي تريد الحصول على قيم مطابقة منه.في مثال جدول الكسور ، إذا كنت تريد كسرًا للرياضيات (هذا هو العمود الأول في الجدول الذي يحتوي على كسور) ، يمكنك إلقاء نظرة على العمود 1.إذا كنت تريد درجة مادية ، يمكنك إلقاء نظرة على العمود 1 و 2.
• [مجموعة البحث] -هنا يمكنك تحديد ما إذا كنت تريد مطابقة تامة أو مطابقة تقريبية.إذا تم حذفه ، يتم تعيين الإعدادات الافتراضية على TRUE - مطابقة تقريبية(انظر الملاحظات الإضافية أدناه).

ملاحظات إضافية (مملة ، لكن من المهم معرفة ذلك)

• يمكن أن تكون المطابقات تامة (FALSE أو 0 في range_lookup) أو تقريبية (TRUE أو 1).
• في البحث التقريبي ، تأكد من فرز القائمة بترتيب تصاعدي (من أعلى إلى أسفل) ، وإلا فقد تكون النتائج غير دقيقة.
• عندما تكون قيمة range_lookup هي TRUE (بحث تقريبي) ويتم فرز البيانات بترتيب تصاعدي:
  • إذا لم تتمكن الدالة VLOOKUP من العثور على القيمة ، فإنها تُرجع أكبر قيمة أقل من lookup_value.
  • تُرجع الخطأ # N / A إذا كانت قيمة lookup_value أقل من الحد الأدنى للقيمة.
  • إذا كانت lookup_value نصًا ، فيمكن استخدام أحرف البدل (انظر المثال أدناه).

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

10 أمثلة على برنامج Excel VLOOKUP (أساسي ومتقدم)

فيما يلي 10 أمثلة مفيدة لاستخدام Excel Vlookup لتظهر لك كيفية استخدامه في عملك اليومي.

مثال 1 - أوجد نتيجة براد في الرياضيات

في مثال VLOOKUP أدناه ، لدي قائمة بأسماء الطلاب في العمود أقصى اليسار والدرجات الخاصة بالمواد المختلفة في الأعمدة B إلى E.

الآن دعنا نبدأ العمل ونستخدم وظيفة VLOOKUP للقيام بما هو أفضل.من البيانات أعلاه ، أريد أن أعرف ما سجله براد في الرياضيات.

من البيانات أعلاه ، أريد أن أعرف ما سجله براد في الرياضيات.

إليك صيغة VLOOKUP التي تُرجع درجة براد في الرياضيات:

= VLOOKUP ("طارق"، 3 دولارات أسترالية: 10,2,0،XNUMX،XNUMX دولارات أمريكية)

تحتوي الصيغة أعلاه على أربع معلمات:

• "براد": - هذه هي قيمة البحث.
• 3 دولارات أسترالية: 10 دولارات أمريكية - هذا هو نطاق الخلايا الذي نبحث عنه.تذكر أن Excel يبحث عن قيم البحث في العمود الموجود في أقصى اليسار.في هذا المثال ، يبحث عن اسم Brad في A3: A10 ، وهو العمود الموجود في أقصى اليسار من الصفيف المحدد.
• 2 - بمجرد أن تعثر الوظيفة على اسم طارق ، تنتقل إلى العمود الثاني من المصفوفة وتعيد القيمة في نفس الصف مثل اسم طارق.تعني القيمة 2 هنا أننا نبحث عن نتائج من العمود الثاني من المصفوفة المحددة.
• 0 - هذا يخبر وظيفة VLOOKUP بالبحث عن المطابقات التامة فقط.

إليك كيفية عمل صيغة VLOOKUP في المثال أعلاه.

أولاً ، يبحث عن القيمة "براد" في العمود الموجود في أقصى اليسار.يبحث عن القيمة الموجودة في الخلية A6 من أعلى إلى أسفل.

بمجرد العثور على القيمة ، ينتقل إلى اليمين في العمود الثاني ويمسك القيمة فيه.

يمكنك استخدام نفس الصيغة للحصول على درجة أي شخص في أي موضوع.

على سبيل المثال ، للعثور على درجة ماريا في الكيمياء ، استخدم صيغة VLOOKUP التالية:

= VLOOKUP ("Maria"، 3 دولارات أسترالية: 10,4,0،XNUMX،XNUMX دولارات أمريكية)

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

تكمن فائدة استخدام مراجع الخلايا في أنها تجعل الصيغة ديناميكية.

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

إذا أدخلت قيمة بحث غير موجودة في العمود الموجود في أقصى اليسار ، فسيتم إرجاع الخطأ # N / A.

مثال 2 - بحث ثنائي الاتجاه

في المثال 1 أعلاه ، قمنا بترميز قيم العمود.لذلك ، ستُرجع الصيغة دائمًا درجة الرياضيات لأننا نستخدم 2 كرقم فهرس العمود.

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

فيما يلي مثال على وظيفة VLOOKUP ثنائية الاتجاه.

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

للقيام بذلك ، تحتاج إلى استخدام الدالة MATCH كمعامل عمود.

إليك صيغة VLOOKUP التي ستفعل ذلك:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

تستخدم الصيغة أعلاه MATCH (H3، $ A $ 2: $ E $ 2,0،XNUMX) كرقم عمود.تأخذ الدالة MATCH اسم الموضوع كقيمة بحث (في H3) وتقوم بإرجاع موضعه في A2: E2.لذلك إذا كنت تستخدم Math ، فستُرجع 2 لأن Math موجودة في B2 (وهي الخلية الثانية في نطاق الصفيف المحدد).

المثال 3 - استخدام القائمة المنسدلة كقيمة بحث

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

في هذه الحالة ، قد تكون الفكرة الجيدة هي إنشاء قائمة منسدلة بقيم البحث (في هذه الحالة يمكن أن يكون اسم الطالب والموضوع) والاختيار ببساطة من القائمة.

اعتمادًا على التحديد ، ستقوم الصيغة تلقائيًا بتحديث النتيجة.

كما هو مبين أدناه:

يعد هذا مكونًا رائعًا للوحة المعلومات لأنه يمكن أن يكون لديك مجموعة بيانات ضخمة من مئات الطلاب في الواجهة الخلفية ، ولكن يمكن للمستخدمين النهائيين (مثل المدرسين) ببساطة المنسدلة من ملف.

كيف أقوم بهذا العمل:

صيغة VLOOKUP المستخدمة في هذا المثال هي نفسها المستخدمة في المثال 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

تم تحويل قيم البحث إلى قوائم منسدلة.

فيما يلي خطوات إنشاء قائمة منسدلة:

• حدد الخلية التي تحتاج إلى قائمة منسدلة.في هذا المثال ، في G4 ، نحتاج إلى اسم الطالب.
• انتقل إلى البيانات -> أدوات البيانات -> التحقق من صحة البيانات.
• في علامة التبويب "الإعدادات" في مربع الحوار "التحقق من صحة البيانات" ، حدد قائمة من القائمة المنسدلة "السماح".
• في المصدر ، حدد $ A $ 3: $ A $ 10
• انقر فوق موافق.

سترى الآن القائمة المنسدلة في الخلية G4.وبالمثل ، يمكنك إنشاء واحد في H3 لموضوع ما.

مثال 4 - بحث ثلاثي

ما هو البحث ثلاثي الاتجاهات؟

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

الآن ، لنفترض أنه في غضون عام ، سيخضع الطالب لثلاثة مستويات مختلفة من الاختبارات ، وامتحانات الوحدة ، وامتحانات منتصف الفصل ، والامتحانات النهائية (هذا هو الاختبار من أيام دراستي).

سيتمكن البحث ثلاثي الاتجاهات من الحصول على درجة الطالب لموضوع محدد من مستوى اختبار محدد.

كما هو مبين أدناه:

في المثال أعلاه ، يمكن لوظيفة VLOOKUP البحث عن درجات الطلاب المحددين في المواد المحددة وإرجاعها في ثلاثة جداول مختلفة (اختبارات الوحدة ، والفترات النصفية ، والنهائيات).

فيما يلي الصيغة المستخدمة في الخلية H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

تستخدم هذه الصيغة الدالة CHOOSE للتأكد من الرجوع إلى الجدول الصحيح.دعنا نحلل جزء الاختيار من الصيغة:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

الوسيطة الأولى للصيغة هي IF (H2 = "Unit Test"، 1، IF (H2 = "Midterm"، 2,3،2)) ، والتي تفحص الخلية HXNUMX وترى مستوى الاختبار المشار إليه.إذا كان اختبار وحدة ، فسيتم إرجاع $ A $ 3: $ E $ 7 ، والذي يحتوي على درجة اختبار الوحدة.تُرجع 11 دولارًا أمريكيًا: 15 دولارًا أمريكيًا إذا كان اختبارًا نصفيًا ، وبخلاف ذلك يتم إرجاع 19 دولارًا أستراليًا: 23 دولارًا أمريكيًا.

يؤدي القيام بذلك إلى جعل صفيف جدول VLOOKUP ديناميكيًا ، مما يجعله بحثًا ثلاثي الاتجاهات.

مثال 5 - احصل على آخر قيمة من القائمة

يمكنك إنشاء صيغة VLOOKUP للحصول على آخر قيمة في القائمة.

أكبر رقم موجب يمكنك استخدامه في Excel هو 9.99999999999999E + 307 . هذا يعني أيضًا أن أكبر رقم بحث في رقم VLOOKUP هو نفسه.

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

افترض أن لديك مجموعة بيانات مثل التالية(في A1: A14)، وتريد الحصول على الرقم الأخير في القائمة.

إليك الصيغة التي يمكنك استخدامها:

= VLOOKUP (9.99999999999999E + 307، $ A $ 1: $ 14 $، TRUE)

لاحظ أن الصيغة أعلاه تستخدم مطابقة تقريبية لـ VLOOKUP  (لاحظ القيمة TRUE في نهاية الصيغة ، وليس FALSE أو 0).لاحظ أيضًا أن صيغة VLOOKUP هذه تعمل بدون فرز القائمة.

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

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

في المثال أعلاه ، الحالة الثالثة في العمل.

بسبب9.99999999999999E + 307هو أكبر رقم يمكن استخدامه في Excel ، لذلك عند استخدامه كقيمة بحث ، فإنه يُرجع آخر رقم في القائمة.

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

= VLOOKUP ("zzz"، 1 دولار أسترالي: 8,1،XNUMX دولار أسترالي، الحقيقة )

اتبع نفس المنطق.يبحث Excel في جميع الأسماء ، وبما أن zzz يعتبر أكبر من أي اسم / نص يبدأ بحرف قبل zzz ، فإنه سيعيد العنصر الأخير في القائمة.

مثال 6 - بحث جزئي باستخدام أحرف البدل و VLOOKUP

تعد أحرف البدل في Excel مفيدة في العديد من المواقف.

هذه الجرعة السحرية هي التي تمنح وصفتك قوى خارقة.

مطلوب بحث جزئي عندما يتعين عليك البحث عن قيمة في قائمة ولا يوجد تطابق تام.

على سبيل المثال ، افترض أن لديك مجموعة بيانات مثل المجموعة أدناه ، وتريد العثور على شركة ABC في قائمة ، لكن القائمة بها ABC Ltd بدلاً من ABC.

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

ومع ذلك ، يمكنك استخدام أحرف البدل في وظيفة VLOOKUP للحصول على التطابقات.

أدخل الصيغة التالية في الخلية D2 واسحبها إلى خلايا أخرى:

= VLOOKUP ("*" & C2 & "*"، 2 دولار أسترالي: 8,1،XNUMX دولار أسترالي ، خطأ)

كيف تعمل هذه الصيغة؟

في الصيغة أعلاه ، بدلاً من استخدام قيمة البحث كما هي ، تكون محاطة بعلامات نجمية (*) - "*" & C2 & "*"

العلامات النجمية هي أحرف بدل في Excel يمكن أن تمثل أي عدد من الأحرف.

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

على سبيل المثال ، تحتوي الخلية C2 على ABC ، ​​لذلك تبحث الدالة VLOOKUP في الأسماء الموجودة في A2: A8 وتبحث عن ABC.تعثر على تطابق في الخلية A2 لأنها تحتوي على ABC من ABC Ltd. لا يهم ما إذا كان هناك أي أحرف على يسار أو يمين ABC.سيتم اعتباره مطابقًا حتى يوجد ABC في السلسلة النصية.

ملاحظة: تقوم الدالة VLOOKUP دائمًا بإرجاع القيمة المطابقة الأولى وإيقاف عمليات البحث الإضافية.لذلك إذا كان هناك ABC في القائمة المحدودة.وشركة ABC ، ​​ستعيد الأول وتتجاهل الباقي.

مثال 7 - يُرجع VLOOKUP خطأ بالرغم من مطابقة قيمة البحث

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

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

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

يحدث هذا عادةً عند استيراد بيانات من قاعدة بيانات أو الحصول على بيانات من شخص آخر.تميل المسافات البادئة / اللاحقة إلى التسلل إلى الداخل.

الحل هنا هو وظيفة TRIM.يزيل أي مسافات بادئة أو لاحقة أو مسافات زائدة بين الكلمات.

هذه هي الصيغة التي ستمنحك النتيجة الصحيحة.

= VLOOKUP ("مات"، TRIM (دولار أسترالي 2: 9 دولارات أسترالية) ، 1,0،XNUMX)

نظرًا لأن هذه صيغة صفيف ، استخدم Control + Shift + Enter بدلاً من Enter.

قد يكون الأسلوب الآخر هو معالجة صفيف البحث الخاص بك أولاً باستخدام وظيفة TRIM للتأكد من اختفاء جميع المسافات الزائدة ، ثم استخدم وظيفة VLOOKUP كالمعتاد.

مثال 8 - إجراء بحث حساس لحالة الأحرف

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

ولكن إذا كنت تريد بحثًا حساسًا لحالة الأحرف ، فأنت بحاجة إلى استخدام وظيفة EXACT ووظيفة VLOOKUP.

هذا مثال:

كما ترى ، الخلايا الثلاث لها نفس الاسم (في A2 و A4 و A5) ولكن بأحرف كبيرة مختلفة.على اليمين ، لدينا ثلاثة أسماء (مات ، مات ، مات) ودرجاتهم في الرياضيات.

وظيفة VLOOKUP غير مجهزة حاليًا للتعامل مع قيم البحث الحساسة لحالة الأحرف.في المثال أعلاه ، تُرجع دائمًا 38 ، وهي درجة مات في A2.

لجعلها حساسة لحالة الأحرف ، نحتاج إلى استخدام عمود مساعد (كما هو موضح أدناه):

للحصول على القيمة في عمود التعليمات ، استخدم الدالة = ROW ().سيحصل فقط على رقم الصف في الخلية.

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

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

الآن دعنا نقسمها ونفهم ما تفعله:

• دقيق (E2، $ A $ 2: $ A $ 9) - يقارن هذا الجزء قيمة البحث في E2 مع جميع القيم الموجودة في A2: A9.تقوم بإرجاع مصفوفة من TRUE / FALSE ، حيث يتم إرجاع TRUE في حالة وجود تطابق تام.في هذه الحالة ، ستعيد المصفوفة التالية: {TRUE؛ FALSE؛ FALSE؛ FALSE؛ FALSE؛ FALSE؛ FALSE؛ FALSE}.
• EXACT (E2، $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - يضاعف هذا الجزء المصفوفة TRUE / FALSE مع رقم الصف. طالما أن هناك TRUE ، فسيعطي رقم الصف ، وإلا فإنه يعطي 0. في هذه الحالة سيعود {2؛ 0؛ 0؛ 0؛ 0؛ 0؛ 0؛ 0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。في هذه الحالة سيعود 2 (وهو رقم السطر الدقيق).
• الآن نستخدم فقط هذا الرقم كقيمة بحث ونستخدم مصفوفة البحث كـ B2: C9

ملاحظة: نظرًا لأن هذه صيغة صفيف ، استخدم Control + Shift + Enter بدلاً من الكتابة فقط.

مثال 9 - استخدام VLOOKUP مع شروط متعددة

تبحث وظيفة Excel VLOOKUP ، في شكلها الأساسي ، عن قيمة بحث وتعيد القيمة المقابلة من صف محدد.

لكن عادةً ما تكون VLOOKUP مطلوبة في Excel بمعايير متعددة.

افترض أن لديك بيانات تحتوي على أسماء الطلاب وأنواع الاختبارات ودرجات الرياضيات (الموضحة أدناه):

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

على سبيل المثال ، إذا حاولت استخدام VLOOKUP مع Matt كقيمة بحث ، فستُرجع دائمًا 91 ، وهي النتيجة لأول تكرار لـ Matt في القائمة.للحصول على درجة Matt لكل نوع اختبار (وحدة ، منتصف الفصل ، ونهائي) ، تحتاج إلى إنشاء قيمة بحث فريدة.

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

الآن ، لإنشاء مؤهل فريد لكل مثيل لاسم ، استخدم الصيغة التالية في C2: = A2 & ”|” & B2

انسخ هذه الصيغة إلى جميع الخلايا في عمود المساعد.سيؤدي هذا إلى إنشاء قيمة بحث فريدة لكل مثيل من الاسم (كما هو موضح أدناه):

الآن ، على الرغم من وجود أسماء مكررة ، عند دمج الأسماء مع مستويات الاختبار ، لا توجد نسخ مكررة.

هذا سهل لأنه يمكنك الآن استخدام قيمة العمود المساعد كقيمة البحث.

هذه هي الصيغة التي تعطيك النتيجة في G3: I8.

= VLOOKUP ($ F3 & "|" & G $ 2، $ C $ 2: $ D $ 19,2,0،XNUMX،XNUMX)

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

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

لاحظ أنه على الرغم من اختلاف A2 و A3 عن اختلاف B2 و B3 ، إلا أن التركيبة هي نفسها في النهاية.ومع ذلك ، إذا كنت تستخدم المحددات ، فستكون المجموعة مختلفة (D2 و D3).

فيما يلي برنامج تعليمي حول كيفية استخدام VLOOKUP مع شروط متعددة دون استخدام الأعمدة المساعدة.

مثال 10 - معالجة الأخطاء عند استخدام وظيفة VLOOKUP

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

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

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

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

ها هي الصيغة:

= IFERROR (VLOOKUP (D2، $ A $ 2: $ B $ 7,2,0،XNUMX،XNUMX)، "Not Found")

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

تتوفر الوظيفة IFERROR منذ Excel 2007.إذا كنت تستخدم إصدارًا سابقًا ، فيرجى استخدام الوظائف التالية:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

هذا كل ما في هذا البرنامج التعليمي VLOOKUP.

أحاول تقديم المثال الرئيسي لاستخدام وظيفة Vlookup في Excel.اسمحوا لي أن أعرف في قسم التعليقات إذا كنت ترغب في رؤية المزيد من الأمثلة المضافة إلى هذه القائمة.

وظائف Excel ذات الصلة:

• وظيفة Excel HLOOKUP.
• وظيفة Excel XLOOKUP
• وظيفة فهرس Excel.
• Excel وظيفة غير مباشرة.
• وظيفة تطابق Excel.
• وظيفة الإزاحة في Excel.