Excel INDEX MATCH: عمليات البحث الأساسية والمتقدمة

في Excel، يُعد استرجاع بيانات محددة بدقة حاجة متكررة. فعلى الرغم من أن لكلٍّ من دالتي INDEX وMATCH مزاياهما الفريدة، فإن الجمع بينهما يُطلق العنان لمجموعة أدوات بحث قوية ومرنة. معًا، تُسهّل هاتان الدالتان طيفًا واسعًا من إمكانيات البحث—بدءًا من عمليات البحث الرأسية والأفقية الأساسية، ووصولًا إلى وظائف أكثر تقدمًا مثل البحث ثنائي الاتجاه، والبحث الحساس لحالة الأحرف، والبحث القائم على معايير متعددة. وبالمقارنة مع VLOOKUP، يوفّر استخدام INDEX وMATCH معًا دقةً أعلى ومرونةً أوسع في استرجاع البيانات. في هذا البرنامج التعليمي، سنستكشف معًا أقصى إمكانات هذه المجموعة القوية.

• دالة INDEX
• دالة MATCH
• دمج INDEX وMATCH

• بحث ثنائي الاتجاه
• بحث يسارًا
• بحث حساس لحالة الأحرف
• أقرب تطابق
• بحث بمعايير متعددة
• بحث عبر أعمدة متعددة
• البحث عن أول قيمة غير فارغة
• البحث عن أول قيمة رقمية
• البحث عن الارتباطات ذات القيمة العظمى (MAX) أو الصغرى (MIN)
• تلميح: قم بتخصيص رسائل الخطأ #N/A الخاصة بك

كيفية استخدام INDEX وMATCH في Excel

قبل استخدام دالتي INDEX وMATCH، دعونا أولًا نتأكد من فهمنا التام لكيفية مساعدتهما في البحث عن القيم.

كيفية استخدام دالة INDEX في Excel

تُرجع دالةINDEX في Excel القيمة الموجودة في موقع معيّن ضمن نطاق محدد. صيغة دالة INDEX هي كما يلي:

• array (مطلوبة) تشير إلى النطاق الذي تريد إرجاع القيمة منه.
• row_num(مطلوبة، ما لم تكن)column_num موجودة) تشير إلى رقم الصف في الصفيف.
• column_num(اختيارية، ولكنها تُصبح إلزامية إذا تم حذف)row_num) وتشير إلى رقم العمود في الصفيف.

على سبيل المثال، لمعرفةدرجة جيف، وهو الطالب رقم6في القائمة، يمكنك استخدام دالة INDEX بهذه الطريقة:

=INDEX(C2:C11,6)

√ ملاحظة: النطاقC2:C11هو المكان الذي تظهر فيه الدرجات، بينما الرقم6يُحدّد درجة امتحان الطالب رقم6في القائمة.

هنا، دعونا نجري اختبارًا بسيطًا. بالنسبة للصيغة=INDEX(A1:C1,2)، ما القيمة التي سترجعها؟ --- نعم، سترجعتاريخ الميلاد، وهي القيمة رقم2 في الصف المحدد.

الآن، أصبح من المهم أن ندرك أن دالةINDEX تعمل بكفاءة مثالية مع النطاقات الأفقية أو الرأسية. لكن ماذا لو احتجنا إلى استرجاع قيمة من نطاق أوسع يحتوي على عدة صفوف وأعمدة؟ في هذه الحالة، يتعيّن علينا إدخال رقم الصف ورقم العمود معًا. فمثلًا، لمعرفةدرجة جيفضمن نطاق الجدول — وليس عمودًا واحدًا فقط — يمكننا تحديد درجته باستخدامرقم صف 6ورقم عمود 3في النطاقA2 إلى C11، بهذه الطريقة:

=INDEX(A2:C11,6,3)

مع ذلك، في حالة قاعدة بيانات كبيرة جدًّا تحتوي على صفوف وأعمدة عديدة، من المؤكد أنه لن يكون مناسبًا لنا استخدام الصيغة برقم صف ورقم عمود محددين يدويًّا. وهنا يأتي دور دالة MATCH التي يجب أن ندمجها مع INDEX.

كيفية استخدام دالة MATCH في Excel

تُرجع دالة MATCH في Excel قيمة رقمية تمثّل موقع عنصر معيّن ضمن نطاق محدد. صيغة دالة MATCH هي كما يلي:

• lookup_value (مطلوبة) تشير إلى القيمة التي سيتم مطابقتها فيlookup_array.
• lookup_array (مطلوبة) تشير إلى نطاق الخلايا الذي تريد أن تبحث فيه دالة MATCH.
• match_type(اختيارية):1،0أو-1.
  • 1(افتراضيًا)، ستُجري MATCH عملية البحث عن أكبر قيمة أقل من أو تساويlookup_value. ويجب أن تكون القيم فيlookup_array مرتبة ترتيبًا تصاعديًا.
  • 0، ستقوم MATCH بإيجاد أول قيمة تطابقlookup_value تمامًا. ويمكن أن تكون القيم فيlookup_array بأي ترتيب. (في الحالات التي يتم فيها ضبط نوع المطابقة على 0، يمكنك استخدام أحرف البدل.)
  • -1، ستقوم MATCH بإيجاد أصغر قيمة أكبر من أو تساويlookup_value. يجب أن تكون القيم فيlookup_array مرتبة تنازليًا.

على سبيل المثال، لمعرفةموقع فيرا في العمود قائمة الأسماء، يمكنك استخدام الدالة تمييز الصيغ بهذه الطريقة:

=MATCH("Vera",A2:A11,0)

√ ملاحظة: النتيجة "4" تشير إلى أن الاسم «Vera» موجود في الموقع الرابع في القائمة.

بعد أن تعرّفنا الآن على الاستخدامات الأساسية لدالتي INDEX وMATCH في Excel، حان الوقت لنُشَمِّر عن سواعدنا ونستعد لدمجهما معًا!

كيفية دمج INDEX وMATCH في Excel

يرجى الاطلاع على المثال أدناه لفهم كيفية دمج دالتي INDEX وMATCH:

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

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

حسنًا، بما أن الصيغة قد تبدو معقدة بعض الشيء، دعونا نشرح كل جزء منها.

صيغةINDEXتحتوي على ثلاث حجج:

• row_num:MATCH(«Evelyn»,A2:A11,0)توفر لـ INDEX موقع الصف للقيمة "Evelyn" في النطاقA2:A11، وهو5.
• column_num:3 يُحدِّد العمود3 لتحديد مكان الدرجة داخل الصفيف باستخدام دالة INDEX.
• array:A2:C11 تُوجِّه دالة INDEX لإرجاع القيمة المطابقة عند تقاطع الصف والعمود المحدَّدين، ضمن النطاق الممتد منA2 إلى C11. وأخيرًا، نحصل على النتيجة90.

في الصيغة أعلاه، استخدمنا قيمة ثابتة، وهي«Evelyn». لكن في الممارسة العملية، تكون القيم الثابتة غير عملية لأنها تتطلب تعديلًا في كل مرة نرغب فيها بالبحث عن بيانات مختلفة، مثل درجة طالب آخر. في مثل هذه السيناريوهات، يمكننا استخدام مراجع الخلايا لإنشاء صيغ ديناميكية. على سبيل المثال، في هذه الحالة، سأقومبتغيير «Evelyn» إلى F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

أمثلة على استخدام INDEX وتمييز الصيغ

في هذا الجزء، سنستعرض مختلف السيناريوهات التي يمكن فيها استخدام دالتي INDEX وMATCH لتلبية احتياجات متنوعة.

استخدام INDEX وMATCH لإجراء بحث ثنائي الاتجاه

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

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

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

استخدام INDEX وMATCH لإجراء بحث إلى اليسار

الآن، دعنا ننظر في سيناريو تحتاج فيه إلى معرفة صف إيفلين. ربما لاحظت أن عمود الصف يقع إلى يسار عمود الأسماء، وهي حالة تتجاوز قدرات دالة بحث أخرى قوية في Excel تُسمى VLOOKUP.

في الواقع، تُعدّ القدرة على إجراء عمليات بحث إلى اليسار إحدى الجوانب التي يتفوق فيها الجمع بين INDEX وMATCH على VLOOKUP.

للعثور علىصف إيفلين، استخدم الصيغة التالية للـبحث عن إيفلين في النطاق B2:B11واسترجاع القيمة المقابلة من النطاق A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

استخدام INDEX وMATCH لإجراء بحث حساس لحالة الأحرف

دالةMATCH بطبيعتها غير حساسة لحالة الأحرف. ومع ذلك، عندما تحتاج إلى تمييز الأحرف الكبيرة عن الصغيرة في صيغتك، يمكنك تعزيزها باستخدام دالةEXACT. وبمجرد دمج دالةMATCH معEXACT داخل صيغةINDEX، يُصبح بإمكانك إجراء بحث حساس لحالة الأحرف بشكل فعّال، كما هو موضح أدناه:

• array يشير إلى النطاق الذي تريد إرجاع القيمة منه.
• lookup_value تشير إلى القيمة التي تريد مطابقتها مع مراعاة حالة الأحرف فيlookup_array.
• lookup_array يشير إلى نطاق الخلايا الذي تُقارنه دالة MATCH بـlookup_value.

على سبيل المثال، لمعرفةدرجة جيمي في الامتحان، استخدم الصيغة التالية:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ ملاحظة: هذه صيغة صفيفية تتطلب إدخالها باستخدامCtrl+Shift+Enter، باستثناء Excel 365 وExcel 2021 والإصدارات الأحدث.

استخدام INDEX وMATCH لإيجاد أقرب تطابق

قد تواجه في Excel حالات تحتاج فيها إلى إيجاد القيمة الأقرب أو الأكثر تشابهًا مع قيمة معيّنة ضمن مجموعة بيانات. في مثل هذه السيناريوهات، يمكن أن يكون استخدام مزيج من دالتي INDEX وMATCH مع دالتي ABS وMIN مفيدًا جدًّا.

• array يشير إلى النطاق الذي تريد إرجاع القيمة منه.
• lookup_array يشير إلى نطاق القيم الذي تريد العثور فيه على أقرب تطابق لـlookup_value.
• lookup_value تشير إلى القيمة التي تريد العثور على أقرب تطابق لها.

على سبيل المثال، لمعرفةمن درجته الأقرب إلى 85، استخدم الصيغة التالية للـبحث عن الدرجة الأقرب إلى 85 في C2:C11واسترجاع القيمة المقابلة من A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ ملاحظة: هذه صيغة صفيفية تتطلب إدخالها باستخدامCtrl+Shift+Enter، باستثناء Excel 365 وExcel 2021 والإصدارات الأحدث.

استخدام INDEX وMATCH لإجراء بحث متعدد المعايير

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

√ ملاحظة: هذه صيغة صفيفية يجب إدخالها باستخدامCtrl+Shift+Enter. بعد الإدخال، سيظهر زوج من الأقواس المعقوفة في شريط الصيغة.

• array يشير إلى النطاق الذي تريد إرجاع القيمة منه.
• (lookup_value=lookup_array) يمثل شرطًا واحدًا، حيث يتحقّق مما إذا كانت القيمةlookup_value مطابقةً لأيٍّ من القيم الموجودة فيlookup_array.

على سبيل المثال، للعثور علىدرجة كوكو من الفصل أ، الذي تاريخ ميلاده هو 7/2/2008، يمكنك استخدام الصيغة التالية:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

دالتي INDEX وMATCH لتطبيق بحث عبر أعمدة متعددة

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

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

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

هنا يأتي دورKutools لـ Excelمع ميزته القوية المخصصة لـفهرسة ومطابقة عدة أعمدة، والتي تسهّل هذه العملية بشكل كبير، وتجعل ربط الإدخالات المحددة بفئاتها المقابلة سريعًا وسهلًا. ولتفعيل هذه الأداة وربط شون بفصله دون عناء، ما عليك سوىتنزيل وتثبيت إضافة Kutools لـ Excel، ثم اتبع الخطوات التالية:

1. اختر الخلية الوجهة التي تريد عرض الفئة المطابقة فيها.
2. في علامة التبويبKutools، انقر فوقمساعد الصيغة > Lookup & Reference > فهرسة ومطابقة عدة أعمدة.
3. 
3. في مربع الحوار المنبثق، قم بما يلي:
   1. انقر على الزر الأول الموجود بجانبLookup_col لتحديد العمود الذي يحتوي على المعلومات الأساسية التي تريد إرجاعها، أي أسماء الفصول. (يمكنك تحديد عمود واحد فقط هنا.)
   2. انقر على الزر الثانيالموجود بجانبTable_rngلتحديد الخلايا التي سيتم مطابقتها مع القيم في العمود المحددLookup_col، أي أسماء الطلاب.
   3. انقر على الزر الثالثالموجود بجانبLookup_value لتحديد الخلية التي تحتوي على اسم الطالب الذي تريد مطابقته مع فصله—وفي هذه الحالة، هو Shawn.
   4. انقرموافق.
   5. 

النتيجة

لقد قام Kutools بإنشاء الصيغة تلقائيًّا، وسترى اسم فصل شون معروضًا فورًا في الخلية الوجهة.

دالتي INDEX وMATCH للبحث عن أول قيمة غير فارغة

لاسترجاع أول قيمة غير فارغة من عمود أو صف مع تجاهل الأخطاء، يمكنك استخدام صيغة تعتمد على دالتي INDEX وMATCH. أما إذا كنت لا ترغب في تجاهل الأخطاء الموجودة في نطاقك، فأضف دالة ISBLANK.

• الحصول على أول قيمة غير فارغة في عمود أو صف مع تجاهل الأخطاء:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• الحصول على أول قيمة غير فارغة في عمود أو صف مع تضمين الأخطاء:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

دالتي INDEX وMATCH للبحث عن أول قيمة رقمية

لاسترجاع أول قيمة رقمية من عمود أو صف، استخدم الصيغة المُعتمدة على دالات INDEX وMATCH وISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

دالتي INDEX وMATCH للبحث عن القيم المرتبطة بأعلى (MAX) أو أقل (MIN) قيمة

إذا كنت بحاجة إلى استرجاع قيمة مرتبطة بأعلى قيمة أو القيمة الدنيا ضمن نطاق معين، يمكنك استخدام دالة MAX أو MIN مع دالتي INDEX وMATCH.

• استخدام INDEX وMATCH لاسترداد القيمة المرتبطة بـ القيمة القصوى:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• استخدام INDEX وMATCH لاسترداد القيمة المرتبطة بـ القيمة الدنيا:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• هناك وسيطان في الصيغ أعلاه:
• • array يشير إلى النطاق الذي تريد استخراج المعلومات ذات الصلة منه.
  • lookup_array تمثّل مجموعة القيم التي سيتم البحث فيها وفق معايير محددة، سواءً كانت القيمة العظمى أو القيمة الدنيا.

على سبيل المثال، إذا أردت تحديدمن لديه أعلى درجة، استخدم الصيغة التالية:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

تلميح: قم بتخصيص رسائل الخطأ #N/A الخاصة بك

عند استخدام دالتي INDEX وMATCH في Excel، قد تظهر رسالة الخطأ #N/A إذا لم يُعثَر على قيمة مطابقة. فعلى سبيل المثال، في الجدول أدناه، عند محاولة البحث عن درجة طالبة تُدعى سامانثا، يظهر خطأ #N/A لأن اسمها غير موجود في مجموعة البيانات.

لجعل جداولك أكثر سهولة في الاستخدام، يمكنك تخصيص رسالة الخطأ هذه عن طريق وضع صيغتك التي تحتوي على INDEX تمييز الصيغ داخل دالة IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

ما سبق يغطي كل ما يتعلق بدالتي INDEX وMATCH في Excel. نأمل أن تجد هذا الدليل مفيدًا! وإذا كنت ترغب في اكتشاف المزيد من نصائح وحيل Excel،فاضغط هناللوصول إلى مجموعتنا الواسعة التي تضم آلاف الدروس التعليمية.