دليل 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) يشير إلى رقم العمود في النطاق.

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

=INDEX(C2:C11,6)

√ ملاحظة: النطاق C2:C11 هو المكان الذي توجد فيه الدرجات، بينما الرقم 6 يعثر على نتيجة الطالب السادس.

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

الآن يجب أن نعلم أن دالة INDEX يمكن أن تعمل بشكل مثالي مع النطاقات الأفقية أو الرأسية. ولكن ماذا لو احتجنا إلى استرجاع قيمة من نطاق أكبر يحتوي على عدة صفوف وأعمدة؟ في هذه الحالة، يجب علينا تطبيق كل من رقم الصف ورقم العمود. على سبيل المثال، لمعرفة درجة Jeff ضمن نطاق الجدول بدلاً من عمود واحد فقط، يمكننا تحديد درجته باستخدام رقم الصف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 ترتيبًا تنازليًا.

على سبيل المثال، لمعرفة موضع Vera في قائمة الأسماء، يمكنك استخدام صيغة MATCH كما يلي:

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

√ ملاحظة: النتيجة "4" تعني أن الاسم "Vera" في الموضع الرابع من القائمة.

الآن بعد أن تعرفنا على الاستخدامات الأساسية لدالتي INDEX وMATCH في Excel، دعونا نستعد لدمج الوظيفتين معًا.

كيفية دمج دالتي INDEX وMATCH في Excel

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

للعثور على درجة Evelyn، مع العلم أن الدرجات في العمود الثالث، يمكننا استخدام دالة 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 يحدد العمود الثالث لـ INDEX لتحديد الدرجة داخل المصفوفة.
• array: A2:C11 توجه INDEX لإرجاع القيمة المطابقة عند تقاطع الصف والعمود المحددين، ضمن النطاق من A2 إلى C11. في النهاية، نحصل على النتيجة90.

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

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

أمثلة على استخدام دالتي INDEX وMATCH

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

استخدام INDEX وMATCH لتنفيذ بحث ثنائي الاتجاه

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

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

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

استخدام INDEX وMATCH لتنفيذ بحث من اليسار

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

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

للعثور على صف Evelyn، استخدم الصيغة التالية للبحث عن Evelyn في 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.

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

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

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

استخدام 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، باستثناء Excel365 وExcel2021 والإصدارات الأحدث.

استخدام INDEX وMATCH لتنفيذ بحث بمعايير متعددة

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

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

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

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

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

استخدام INDEX وMATCH لتنفيذ بحث عبر أعمدة متعددة

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

على سبيل المثال، في الجدول أدناه، كيف يمكننا مطابقة الطالب Shawn مع صفه باستخدام 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 لتسهيل العملية، مما يجعل من السهل وسريعًا مطابقة الإدخالات مع الفئات المقابلة لها. للاستفادة من هذه الأداة القوية ومطابقة Shawn مع صفه بسهولة، ما عليك سوى تنزيل وتثبيت Kutools لـ Excel، ثم اتبع الخطوات التالية:

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

النتيجة

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

استخدام 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 عندما لا يكون هناك نتيجة مطابقة. على سبيل المثال، في الجدول أدناه، عند محاولة العثور على درجة طالبة اسمها Samantha، يظهر خطأ #N/A لأنها غير موجودة في مجموعة البيانات.

لجعل جداول البيانات أكثر سهولة للمستخدم، يمكنك تخصيص رسالة الخطأ هذه من خلال تغليف صيغة INDEX MATCH بدالة IFNA:

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

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