دمج الجداول باستخدام INDEX وMATCH

لنفترض أن لديك جدولين أو أكثر يشتركان في عمود مشترك، لكن البيانات في الأعمدة المشتركة غير مرتبة بنفس الترتيب. في هذه الحالة، ولدمج الجداول أو ربطها مع مطابقة البيانات بدقة، يمكنك الاعتماد على دالتَيINDEX وMATCH.

كيف تدمج الجداول باستخدام دالتي INDEX وMATCH؟

لدمجالجدول 1والجدول 2لجمع جميع المعلومات فيالجدول الجديدكما هو موضح في لقطة الشاشة أعلاه، ابدأ أولًا بنسخ البيانات من الجدول 1 أو الجدول 2 إلى الجدول الجديد (لقد نسختُ البيانات من الجدول 1 هنا، انظر لقطة الشاشة أدناه). وبأخذ أول معرّف طالب23201 في الجدول الجديد كمثال، ستساعدك دالتا INDEX وMATCH على استرداد معلومات درجته ومرتبته بهذه الطريقة: تقوم دالة MATCH بإرجاع موضع الصف الخاص بمعرّف الطالب الذي يطابق23201 فيالجدول 2، ثم يتم تمرير هذا الموضع إلى دالة INDEX لاسترداد القيمة عند تقاطع ذلك الصف مع العمود المحدَّد (عمود الدرجة أو المرتبة).

الصيغة العامة

=INDEX()return_table,MATCH()lookup_value,lookup_array,0),col_num)

√ ملاحظة: نظرًا لأننا أدخلنا بالفعل المعلومات منالجدول 1، فإننا الآن نحتاج فقط إلى استرداد البيانات المقابلة منالجدول 2.

لـاسترداد البيانات المقابلة من الجدول 2 لجمع جميع المعلومات في الجدول الجديد، يُرجى نسخ الصيغ أدناه أو إدخالها في الخليتين F16 وG16، ثم الضغط علىEnterللحصول على النتائج:

الخلية F16 (الدرجة)
=INDEX()$F$5:$H$11،MATCH()C16،$F$5:$F$11،0)،2)
الخلية G16 (المرتبة)
=INDEX()$F$5:$H$11،MATCH()C16،$F$5:$F$11،0)،3)

√ ملاحظة: تشير علامات الدولار ($) أعلاه إلى مراجع مطلقة، ما يعني أنreturn_tableوlookup_arrayفي الصيغة لن تتغيّر عند نقل الصيغة أو نسخها إلى خلايا أخرى. ومع ذلك، لا توجد علامات دولار مضافة إلىlookup_valueلأنك تريد أن تكون ديناميكيًا، اسحب مقبض التعبئة لأسفل بعد إدخال الصيغة لتطبيقها على الخلايا الموجودة أسفلها.

شرح الصيغة

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

=INDEX()$F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2)

• MATCH(C16,$F$5:$F$11,0):إنmatch_type 0يجبر دالة MATCH على تنفيذ مطابقة دقيقة. بعد ذلك، تُرجع الدالة موضع القيمة المطابقة لـ23201(القيمة الموجودة في)C16) ضمن نطاق البحث$F$5:$F$11. وبالتالي، تُرجع الدالة3، نظرًا لأن القيمة المطابقة تقع فيالموضع الثالثضمن النطاق.
• INDEX()$F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2) = INDEX($F$5:$H$11تقوم دالة INDEX بإرجاع القيمة عند تقاطع الصف الثالث3 والعمود الثاني2 من جدول الإرجاع$F$5:$H$11، وهي91.

الدوال ذات الصلة

الصيغ ذات الصلة