تطابق الفهرس مع معايير متعددة | أمثلة على برنامج Excel خطوة بخطوة
تطابق الفهرس صفوف وأعمدة معايير متعددة
نستخدم جميعًا VLOOKUP يومًا بعد يوم لجلب البيانات ، كما أننا ندرك حقيقة أن VLOOKUP يمكنه جلب البيانات من اليسار إلى اليمين ، لذلك يجب أن تكون قيمة البحث دائمًا على الجانب الأيسر من أعمدة النتائج. ومع ذلك ، لدينا العديد من البدائل التي يمكن استخدامها كبديل لوظيفة VLOOKUP في Excel. باستخدام التكنولوجيا المتقدمة ، يمكننا استخدام صيغة INDEX + MATCH هذه لمطابقة معايير متعددة للصفوف والأعمدة. لذلك ستأخذك هذه المقالة الخاصة بالتفصيل حول هذه التقنية.
كيفية استخدام صيغة INDEX + MATCH لمطابقة معايير متعددة؟
نوضح هنا كيفية استخدام صيغة الفهرس المطابقة لمطابقة معايير متعددة للصفوف والأعمدة بأمثلة.
يمكنك تنزيل نموذج Excel لمعايير متعددة لمطابقة الفهرس من هنا - نموذج Excel لمطابقة الفهرس ومعايير متعددةالمثال رقم 1 - INDEX + MATCH Formula
ليست غالبية وظائف بحث مستخدمي Excel خارج VLOOKUP ، فقد تكون الأسباب كثيرة. على أي حال ، دعونا نحصل على مقدمة بسيطة لهذه الصيغة قبل أن ننتقل إلى المستوى المتقدم.
على سبيل المثال ، انظر إلى بنية البيانات أدناه في Excel.
لدينا أسماء "مندوبي المبيعات" وقيم المبيعات الخاصة بهم. من ناحية أخرى ، لدينا قائمة منسدلة لـ "مندوب المبيعات" في الخلية D2.
بناءً على التحديد الذي نجريه من القائمة المنسدلة ، يجب أن يظهر مبلغ مبيعات القائمة في الخلية E2.
تكمن المشكلة في أننا لا نستطيع تطبيق صيغة VLOOKUP لأن قيمة البحث "مندوب المبيعات" على يمين عمود النتيجة "المبيعات" لذلك في هذه الحالات يمكننا استخدام صيغة قيمة البحث المركبة INDEX + MATCH.
يبحث INDEX عن قيمة رقم الصف المذكورة في النطاق A2: A11 وفي هذا النطاق ، نحتاج إلى توفير أي صف نحتاج إلى أن تأتي قيمة المبيعات منه. تستند قيمة الصف هذه إلى اسم "مندوب المبيعات" المحدد في القائمة المنسدلة في Excel ، لذلك تبحث وظيفة MATCH عن رقم صف "مندوب المبيعات" في النطاق B2: B11 وتُرجع رقم الصف للقيمة المتطابقة .
مثال # 2 - معايير متعددة في صيغة INDEX + MATCH
الآن لدينا بنية بيانات مثل البنية أدناه.
لدينا قيم مبيعات شهرية لـ "مندوب المبيعات". من هذا الجدول ، نحتاج إلى نتائج ديناميكية كما في الخلية A15 لقد قمت بإنشاء قائمة منسدلة "مندوب مبيعات" وفي خلية B14 قمت بإنشاء قائمة منسدلة "شهر".
بناءً على التحديد الذي تم إجراؤه في هاتين الخليتين ، يجب أن تقوم صيغتنا بإحضار البيانات من الجدول أعلاه.
على سبيل المثال ، إذا اخترت "مندوب 8" و "أبريل" ، فعليه إظهار قيمة مبيعات "مندوب 8" لشهر "أبريل".
لذلك ، في هذه الحالات ، نحتاج إلى مطابقة كل من الصفوف والأعمدة. اتبع الخطوات أدناه لتطبيق الصيغة لمطابقة كل من الصفوف والأعمدة.
الخطوة 1: افتح الدالة INDEX في الخلية B15.
الخطوة 2: الوسيطة الأولى لوظيفة INDEX هي "صفيف" ، أي من أي نطاق من الخلايا نحتاج إلى النتيجة. لذلك ، في هذه الحالة ، نحتاج إلى قيم المبيعات ، لذا اختر نطاق الخلايا من B2 إلى G11.
الخطوة 3: الوسيطة التالية لوظيفة INDEX من أي صف من النطاق المحدد نحتاج إلى النتيجة. في هذه الحالة ، نحتاج إلى الوصول إلى رقم صف "مندوب المبيعات" بناءً على التحديد الذي تم إجراؤه في الخلية المنسدلة A15. لذلك لجلب رقم الصف ديناميكيًا بناءً على وظيفة التحديد المفتوحة MATCH.
الخطوة 4: LOOKUP VALUE لوظيفة MATCH هي "مندوب المبيعات" لذا اختر خلية A15 كمرجع.
الخطوة 5: صفيف البحث سيكون نطاق أسماء "مندوبي المبيعات" في الجدول الرئيسي. لذا اختر النطاق من A2 إلى A11.
الخطوة 6: سيكون نوع مطابقة دالة MATCH دقيقًا ، لذا أدخل صفرًا كقيمة وسيطة.
الخطوة 7: الوسيطة التالية لوظيفة INDEX هي "Column Number" ، أي من نطاق الخلايا المحدد الذي نحتاج إلى النتيجة منه. هذا يعتمد على الشهر الذي نختاره من القائمة المنسدلة للخلية B14. لذلك للحصول على رقم العمود ، افتح تلقائيًا وظيفة MATCH أخرى.
الخطوة 8: ستكون قيمة البحث في الوقت هذه هي اسم الشهر ، لذا حدد خلية B14 كمرجع.
الخطوة 9: سيكون صفيف البحث عبارة عن نطاق شهري من الخلايا في الجدول الرئيسي ، أي من B1 إلى G1.
الخطوة 10: الوسيطة الأخيرة هي نوع المطابقة ، اختر "مطابقة تامة" كمعيار. أغلق قوسين واضغط على مفتاح الإدخال للحصول على النتيجة.
كما نرى أعلاه ، اخترنا "مندوب 6" و "أبريل" كشهر وقد أعادت صيغتنا قيمة المبيعات لشهر "أبريل" لـ "مندوب 6".
ملاحظة: الخلية ذات اللون الأصفر هي المرجع بالنسبة لك.
أشياء للذكرى
- يمكن أن يكون الجمع بين INDEX + MATCH أقوى من صيغة VLOOKUP.
- يمكن لـ INDEX & MATCH مطابقة رؤوس الصفوف والأعمدة وإرجاع النتيجة من الجدول الأوسط.
- يمكن لـ MATCH إرجاع رقم الصف ورقم العمود لرؤوس الجدول لكل من الصفوف والأعمدة.
