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

2024 مؤلف: Abigail Brown | [email protected]. آخر تعديل: 2023-12-17 06:38

ما يجب معرفته

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

تشرح هذه المقالة كيفية استخدام دالتي INDEX و MATCH معًا في جميع إصدارات Excel ، بما في ذلك Excel 2019 و Microsoft 365.

ما هي دالتي INDEX و MATCH؟

INDEX و MATCH هما من وظائف البحث في Excel. في حين أنهما وظيفتان منفصلتان تمامًا يمكن استخدامهما بمفردهما ، يمكن أيضًا دمجهما لإنشاء صيغ متقدمة.

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

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

INDEX و MATCH التركيب والحجج

هكذا يجب كتابة كلتا الوظيفتين حتى يفهمهما Excel:

=INDEX(مجموعة ، row_num ، [column_num])

arrayهو نطاق الخلايا التي ستستخدمها الصيغة. يمكن أن يكون صفًا أو عمودًا واحدًا أو أكثر ، مثل A1: D5. مطلوب.
row_numهو الصف الموجود في المصفوفة الذي يتم إرجاع قيمة منه ، مثل 2 أو 18. وهو مطلوب ما لم يكن column_num موجودًا.
column_numهو العمود في المصفوفة الذي يتم إرجاع قيمة منه ، مثل 1 أو 9. وهو اختياري.

=MATCH(lookup_value، lookup_array، [match_type])

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

نوع المطابقة الذي يجب استخدامه
نوع المطابقة	ماذا تفعل	القاعدة	مثال
1	البحث عن أكبر قيمة أقل من أو تساوي lookup_value.	يجب وضع قيم lookup_array بترتيب تصاعدي (على سبيل المثال ، -2 ، -1 ، 0 ، 1 ، 2 ؛ أو A-Z ؛ ، أو FALSE ، TRUE.	lookup_value هي 25 لكنها مفقودة من lookup_array ، لذا يتم إرجاع موضع الرقم الأصغر التالي ، مثل 22 ، بدلاً من ذلك.
0	البحث عن القيمة الأولى التي تساوي بالضبط lookup_value.	يمكن أن تكون قيم lookup_array بأي ترتيب.	lookup_value هي 25 ، لذا فهي تُرجع موضع 25.
-1	البحث عن أصغر قيمة أكبر أو تساوي lookup_value.	يجب وضع قيم lookup_array بترتيب تنازلي (على سبيل المثال ، 2 ، 1 ، 0 ، -1 ، -2).	lookup_value هي 25 لكنها مفقودة من lookup_array ، لذا يتم إرجاع موضع الرقم التالي الأكبر ، مثل 34 ، بدلاً من ذلك.

استخدم 1 أو -1 للأوقات التي تحتاج فيها إلى إجراء بحث تقريبي على طول مقياس ، مثل عند التعامل مع الأرقام وعندما تكون التقديرات على ما يرام. لكن تذكر أنه إذا لم تحدد match_type ، فسيكون 1 هو الخيار الافتراضي ، والذي يمكن أن يؤدي إلى تحريف النتائج إذا كنت تريد حقًا المطابقة التامة.

مثال لصيغ الفهرس والمطابقة

قبل أن ننظر في كيفية دمج INDEX و MATCH في صيغة واحدة ، نحتاج إلى فهم كيفية عمل هاتين الدالتين بمفردهما.

فهرس أمثلة

=INDEX (A1: B2، 2، 2)

=INDEX (A1: B1، 1)

=INDEX (2: 2، 1)=INDEX (B1: B2، 1)

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

=INDEX (A1: B2، 2، 2)يبحث من خلال A1: B2 للعثور على القيمة في العمود الثاني والصف الثاني ، وهو Stacy.
=INDEX (A1: B1، 1)يبحث من خلال A1: B1 للعثور على القيمة في العمود الأول ، وهو جون.
=INDEX (2: 2، 1)يبحث في كل شيء في الصف الثاني لتحديد القيمة في العمود الأول ، وهو Tim.
=INDEX (B1: B2، 1)يبحث من خلال B1: B2 لتحديد القيمة في الصف الأول ، وهو Amy.

أمثلة المباراة

=MATCH ("Stacy"، A2: D2، 0)

=MATCH (14، D1: D2)

=MATCH (14، D1: D2، -1)=MATCH (13، A1: D1، 0)

فيما يلي أربعة أمثلة سهلة لوظيفة MATCH:

=MATCH ("Stacy"، A2: D2، 0)يبحث عن Stacy في النطاق A2: D2 ويعيد 3 كنتيجة.
=MATCH (14، D1: D2)تبحث عن 14 في النطاق D1: D2 ، ولكن نظرًا لعدم وجودها في الجدول ، تجد MATCH القيمة التالية الأكبر هذا أقل من أو يساوي 14 ، وهو في هذه الحالة 13 ، وهو في الموضع 1 من lookup_array.
=MATCH (14، D1: D2، -1)مطابقة للصيغة أعلاه ، ولكن نظرًا لأن المصفوفة ليست بترتيب تنازلي مثل -1 تتطلب ، حصلنا على خطأ.
=MATCH (13، A1: D1، 0)تبحث عن 13 في الصف الأول من الورقة ، والتي تُرجع 4 لأنها العنصر الرابع في هذه المصفوفة.

أمثلة على مطابقة الفهرسة

إليك مثالين حيث يمكننا دمج INDEX و MATCH في صيغة واحدة:

ابحث عن مرجع الخلية في الجدول

=INDEX (B2: B5، MATCH (F1، A2: A5))

هذا المثال يدمج صيغة المطابقة في صيغة الفهرس. الهدف هو تحديد لون العنصر باستخدام رقم الصنف.

إذا نظرت إلى الصورة ، يمكنك أن ترى في الصفوف "منفصلة" كيف ستكتب الصيغ بمفردها ، ولكن نظرًا لأننا نقوم بتداخلها ، فهذا ما يحدث:

MATCH (F1، A2: A5)يبحث عن قيمة F1 (8795) في مجموعة البيانات A2: A5. إذا قمنا بالعد التنازلي للعمود ، يمكننا أن نرى أنه 2 ، وهذا ما توصلت إليه وظيفة MATCH للتو.
مصفوفة INDEX هي B2: B5 لأننا نبحث في النهاية عن القيمة في هذا العمود.
يمكن الآن إعادة كتابة دالة INDEX على هذا النحو لأن 2 هي ما وجدته MATCH: INDEX (B2: B5، 2، [column_num]).
نظرًا لأن column_num اختياري ، فيمكننا إزالة ذلك ليتم تركه مع هذا: INDEX (B2: B5، 2).
الآن ، هذا يشبه صيغة INDEX العادية حيث نجد قيمة العنصر الثاني في B2: B5 ، وهو أحمر.

البحث عن طريق عناوين الصف والعمود

=INDEX (B2: E13، MATCH (G1، A2: A13، 0)، MATCH (G2، B1: E1، 0))

في هذا المثال من MATCH و INDEX ، نقوم ببحث ثنائي الاتجاه. الفكرة هي معرفة مقدار الأموال التي جنيناها من العناصر الخضراء في مايو. هذا مشابه بالفعل للمثال أعلاه ، لكن صيغة MATCH إضافية متداخلة في INDEX.

MATCH (G1، A2: A13، 0)هو العنصر الأول الذي يتم حله في هذه الصيغة. إنه يبحث عن G1 (كلمة "مايو") في A2: A13 للحصول على قيمة معينة. لا نراه هنا ، لكنه 5.
MATCH (G2، B1: E1، 0)هي صيغة المطابقة الثانية ، وهي تشبه الصيغة الأولى حقًا ولكنها تبحث بدلاً من ذلك عن G2 (كلمة "أخضر") في عناوين الأعمدة في B1: E1. هذا واحد يقرر إلى 3.
يمكننا الآن إعادة كتابة صيغة INDEX مثل هذه لتصور ما يحدث: =INDEX (B2: E13، 5، 3). هذا يبحث في الجدول بأكمله ، B2: E13 ، للصف الخامس والعمود الثالث ، الذي يُرجع 180 دولارًا.

قواعد المطابقة والفهرس

هناك العديد من الأشياء التي يجب مراعاتها عند كتابة الصيغ بهذه الوظائف:

MATCH ليست حساسة لحالة الأحرف ، لذلك يتم التعامل مع الأحرف الكبيرة والصغيرة بنفس الطريقة عند مطابقة القيم النصية.
MATCH تُرجعN / A لأسباب متعددة: إذا كانت match_type تساوي 0 ولم يتم العثور على lookup_value إذا كانت match_type تساوي -1 ولم يكن lookup_array بترتيب تنازلي ، إذا كان match_type هو 1 ولم يكن lookup_array في تصاعدي النظام ، وإذا لم يكن lookup_array صفًا أو عمودًا واحدًا.
يمكنك استخدام حرف بدل في وسيطة lookup_value إذا كانت match_type تساوي 0 وكانت lookup_value سلسلة نصية. تطابق علامة الاستفهام أي حرف مفرد وتتطابق علامة النجمة مع أي تسلسل للأحرف (مثل.g.، =MATCH ("Jo "، 1: 1، 0)). لاستخدام MATCH للعثور على علامة استفهام أو علامة نجمية فعلية ، اكتب ~ أولاً.
INDEX إرجاعREF! إذا كان row_num و column_num لا يشيران إلى خلية داخل المصفوفة.

وظائف Excel ذات الصلة

تشبه وظيفة MATCH LOOKUP ، لكن MATCH ترجع موضع العنصر بدلاً من العنصر نفسه.

VLOOKUP هي وظيفة بحث أخرى يمكنك استخدامها في Excel ، ولكن على عكس MATCH التي تتطلب INDEX لعمليات البحث المتقدمة ، فإن صيغ VLOOKUP تحتاج فقط إلى وظيفة واحدة.