VLOOKUP مع MATCH | قم بإنشاء صيغة مرنة باستخدام VLOOKUP MATCH
تعمل صيغة Vlookup فقط عندما لا يتغير صفيف الجدول في الصيغة ، ولكن إذا تم إدراج عمود جديد في الجدول أو تم حذف عمود ، فإن الصيغة تعطي نتيجة غير صحيحة أو تعكس خطأ ، لجعل الصيغة خالية من الأخطاء في في مثل هذه المواقف الديناميكية ، نستخدم وظيفة المطابقة لمطابقة فهرس البيانات فعليًا وإرجاع النتيجة الفعلية.
اجمع بين VLOOKUP و Match
صيغة vlookup هي الوظيفة الأكثر استخدامًا والتي يتم استخدامها للبحث وإرجاع إما نفس القيمة في فهرس العمود المحدد أو القيمة من فهرس عمود مختلف مع الإشارة إلى القيمة المتطابقة من العمود الأول. يتمثل التحدي الرئيسي الذي واجهته أثناء استخدام vlookup في أن فهرس العمود المراد تحديده ثابت ولا يحتوي على وظيفة ديناميكية. خاصة عندما تعمل على معايير متعددة تتطلب منك تغيير فهرس العمود المرجعي يدويًا. وبالتالي يتم تلبية هذه الحاجة باستخدام صيغة "MATCH" للحصول على قبضة أو تحكم أفضل في فهرس العمود المتغير باستمرار في صيغة VLOOKUP.
صيغة VLookup والمطابقة
# 1 - صيغة VLOOKUP
صيغة دالة VLOOKUP في Excel
هنا جميع الحجج التي يجب إدخالها إلزامية.
- Lookup_value - هنا يجب إدخال خلية مرجعية أو نص بعلامات اقتباس مزدوجة ليتم تحديده في نطاق الأعمدة.
- مصفوفة الجدول - تتطلب هذه الوسيطة إدخال نطاق الجدول حيث يجب البحث في Lookup_value وتوجد البيانات المراد استردادها في نطاق عمود معين.
- Col_index_num - في هذه الوسيطة ، يجب إدخال رقم فهرس العمود أو عدد العمود من العمود الأول للمرجع والذي يجب سحب القيمة المقابلة منه من نفس موضع القيمة التي تم البحث عنها في العمود الأول.
- [Range_lookup] - ستعطي هذه الوسيطة خيارين.
- TRUE - المطابقة التقريبية: - يمكن إدخال الوسيطة بصيغة TRUE أو رقمية "1" ، والتي تُرجع المطابقة التقريبية المقابلة لعمود المرجع أو العمود الأول. علاوة على ذلك ، يجب فرز القيم في العمود الأول من صفيف الجدول بترتيب تصاعدي.
- خطأ - تطابق تام: - هنا يمكن أن تكون الوسيطة المراد إدخالها إما FALSE أو رقم "0". سيعيد هذا الخيار فقط المطابقة التامة للقيمة المطابقة ليتم تحديدها من الموضع في نطاق العمود الأول. سيؤدي الفشل في البحث عن القيمة من العمود الأول إلى إرجاع رسالة الخطأ "# N / A".
# 2 - صيغة المباراة
تقوم دالة المطابقة بإرجاع موضع الخلية للقيمة التي تم إدخالها لصفيف الجدول المحدد.
جميع الحجج داخل بناء الجملة إلزامية.
- Lookup_value - هنا يمكن أن تكون الوسيطة التي تم إدخالها إما مرجع الخلية للقيمة أو سلسلة نصية بعلامات اقتباس مزدوجة والتي يلزم سحب موضع الخلية الخاص بها.
- Lookup_array - نطاق الصفيف للجدول مطلوب لإدخال قيمته أو محتوى الخلية المطلوب تحديده.
- [نوع المطابقة] - توفر هذه الوسيطة ثلاثة خيارات كما هو موضح أدناه.
- "1-أقل من" - هنا الوسيطة المراد إدخالها هي "1" الرقمية والتي ستُرجع القيمة الأقل من أو تساوي قيمة البحث. وكذلك يجب فرز مصفوفة البحث بترتيب تصاعدي.
- "0-تطابق تام" - هنا يجب أن تكون الوسيطة المراد إدخالها رقمية "0". سيعيد هذا الخيار الموضع الدقيق لقيمة البحث المتطابقة. ومع ذلك ، يمكن أن يكون صفيف البحث بأي ترتيب.
- "-1- أكبر من" - يجب أن تكون الوسيطة المراد إدخالها رقمية "-1". يبحث الخيار الثالث عن أصغر قيمة أكبر من قيمة البحث أو مساوية لها. هنا يجب وضع ترتيب مصفوفة البحث بترتيب تنازلي.
# 3 - VLOOKUP مع صيغة MATCH
= VLOOKUP (lookup_value، table_array، MATCH (lookup_value، lookup_array، [match_type]) ، [بحث النطاق])
كيفية استخدام VLOOKUP مع صيغة المطابقة في Excel؟
سيساعد المثال أدناه في فهم طريقة عمل صيغة البحث والمطابقة عند التجميع.
يمكنك تنزيل VLookup مع قالب Excel المطابق هنا - VLookup مع قالب Excel المطابقضع في اعتبارك جدول البيانات أدناه الذي يصف مواصفات السيارة التي سيتم شراؤها.
للحصول على وضوح الوظيفة المدمجة لوظيفة vlookup والمطابقة ، دعنا نفهم كيف تعمل الصيغة الفردية ثم نصل إلى نتائج مطابقة vlookup عند وضعها معًا.
الخطوة # 1 - دعونا نطبق صيغة vlookup على المستوى الفردي للوصول إلى النتيجة.
الإخراج موضح أدناه:
هنا تتم الإشارة إلى قيمة البحث إلى $ B9 وهو الطراز "E" ويتم إعطاء مصفوفة البحث كنطاق لجدول البيانات بالقيمة المطلقة "$" ، ويشار إلى فهرس العمود إلى العمود "4" وهو عدد العمود "النوع" ويتم إعطاء البحث عن النطاق تطابقًا تامًا.
وبالتالي يتم تطبيق الصيغة التالية لإرجاع قيمة العمود "وقود".
الإخراج موضح أدناه:
هنا قيمة البحث مع السلسلة المطلقة "$" المطبقة على قيمة البحث ويساعد lookup_array في إصلاح الخلية المرجعية حتى إذا تم نسخ الصيغة إلى خلية مختلفة. في عمود "الوقود" ، نحتاج إلى تغيير فهرس العمود إلى "5" حيث تتغير القيمة التي يلزم استرجاع البيانات منها.
الخطوة # 2 - الآن دعنا نطبق صيغة المطابقة لاسترداد الموضع لقيمة البحث المحددة.
الإخراج موضح أدناه:
كما يتضح في لقطة الشاشة أعلاه ، نحاول هنا استرداد موضع العمود من صفيف الجدول. في هذه الحالة ، يُشار إلى رقم العمود المراد سحبه بالخلية C8 التي تمثل العمود "النوع" ويتم تقديم نطاق البحث المراد البحث عنه على أنه نطاق رؤوس الأعمدة ويتم إعطاء نوع المطابقة مطابقة تامة ليكون " 0 ".
هكذا سيعطي الجدول أدناه النتيجة المرجوة لمواضع العمود "وقود".
الآن هنا يُعطى العمود المطلوب البحث فيه ليكون الخلية D8 ويتم إرجاع فهرس العمود المطلوب ليكون "5".
الخطوة # 3 - الآن سيتم استخدام صيغة المطابقة داخل وظيفة vlookup للحصول على القيمة من موضع العمود المحدد.
الإخراج موضح أدناه:
في الصيغة أعلاه ، يتم وضع دالة المطابقة بدلاً من معلمة فهرس العمود لوظيفة vlookup. هنا ستحدد وظيفة المطابقة الخلية المرجعية لقيمة البحث "C8" وإرجاع رقم العمود من خلال مصفوفة الجدول المحددة. سيخدم موضع العمود هذا الغرض كمدخل إلى وسيطة فهرس العمود في وظيفة vlookup. والذي بدوره سيساعد vlookup في تحديد القيمة التي سيتم إرجاعها من رقم فهرس العمود الناتج؟
وبالمثل ، قمنا بتطبيق vlookup مع صيغة المطابقة لعمود "الوقود" أيضًا.
الإخراج موضح أدناه:
يمكننا بالتالي تطبيق هذه الوظيفة المركبة للأعمدة الأخرى "النوع" و "الوقود" أيضًا.
أشياء للذكرى
- يمكن تطبيق VLOOKUP على قيم البحث فقط في الجانب الأيسر الأول لها. ستُرجع أي قيم موجودة ليتم البحث عنها على الجانب الأيمن من جدول البيانات قيمة الخطأ "# N / A".
- يجب أن يكون نطاق table_array الذي تم إدخاله في الوسيطة الثانية هو مرجع الخلية المطلق "$" ، وهذا سيحافظ على نطاق مصفوفة الجدول الثابت عند تطبيق صيغة البحث على خلايا أخرى ، وإلا ستنتقل الخلايا المرجعية لنطاق مصفوفة الجدول إلى الخلية التالية المرجعي.
- يجب ألا تكون القيمة التي تم إدخالها في قيمة البحث أصغر من أصغر قيمة في العمود الأول من صفيف الجدول ، وإلا ستُرجع الدالة قيمة الخطأ "# N / A".
- قبل تطبيق مطابقة تقريبية "TRUE" أو "1" في الوسيطة الأخيرة ، تذكر دائمًا فرز مصفوفة الجدول بترتيب تصاعدي.
- تقوم دالة المطابقة بإرجاع موضع القيمة فقط في مصفوفة جدول vlookup ولا تُرجع القيمة.
- في حالة عدم قدرة دالة المطابقة على تحديد موضع قيمة البحث في صفيف الجدول ، ترجع الصيغة "# N / A" في قيمة الخطأ.
- وظائف Vlookup والمطابقة غير حساسة لحالة الأحرف عند مطابقة قيمة البحث مع قيمة النص المطابقة في مصفوفة الجدول.
