Note: The other languages of the website are Google-translated. Back to English
تسجيل الدخول  \/ 
x
or
x
إنشاء حساب  \/ 
x

or

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

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

ماذا تفعل دالات INDEX و MATCH في Excel

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


ماذا تفعل دالات INDEX و MATCH في Excel

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

استخدام دالة الفهرس في Excel

أفضل INDEX دالة في Excel تُرجع القيمة في موقع معين في نطاق معين. يكون بناء جملة الدالة INDEX كما يلي:

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

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

= INDEX (E2: E11، 6) >>> يعود 60

تطابق مؤشر Excel 01

√ ملاحظة: النطاق E2: E11 حيث يتم سرد الامتحان النهائي ، في حين أن الرقم 6 يجد درجة الامتحان الخاصة بـ 6الطالب عشر.

هنا دعونا نجري اختبارًا بسيطًا. للصيغة = INDEX (B2: E2,3،XNUMX)، ما هي القيمة التي ستعود؟ - نعم ، سوف يعود الصينأطلقت حملة 3rd القيمة في النطاق المحدد.

الآن يجب أن نعرف أن دالة INDEX يمكن أن تعمل بشكل مثالي مع النطاقات الأفقية أو الرأسية. ولكن ماذا لو احتجنا إليها لإرجاع قيمة في نطاق أكبر يحتوي على عدة صفوف وأعمدة؟ حسنًا ، في هذه الحالة ، يجب أن نطبق كلاً من رقم الصف ورقم العمود. على سبيل المثال ، لمعرفة ذلك البلد الذي تأتي منه إميلي باستخدام INDEX ، يمكننا تحديد القيمة برقم صف 8 ورقم عمود 3 في الخلايا من خلال B2 إلى E11 على النحو التالي:

= INDEX (B2: E11,8,3،XNUMX) >>> يعود الصين

تطابق مؤشر Excel 02

وفقًا للأمثلة أعلاه ، حول وظيفة INDEX في Excel ، يجب أن تعلم أن:

  • يمكن أن تعمل الدالة INDEX مع النطاقات الرأسية والأفقية.
  • لا تتحسس الدالة INDEX حالة الأحرف.
  • يذهب رقم الصف قبل رقم العمود (إذا كنت بحاجة إلى كلا الرقمين) في صيغة INDEX.

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

الآن ، دعنا نتعرف على أساسيات وظيفة MATCH أولاً.


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

ترجع الدالة MATCH في Excel قيمة رقمية ، موقع عنصر معين في النطاق المحدد. يكون بناء جملة دالة MATCH كما يلي:

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

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

= MATCH ("vera"، C2: C11,0،XNUMX) >>> يعود 4

تطابق مؤشر Excel 03

√ ملاحظة: وظيفة MATCH ليست حساسة لحالة الأحرف. النتيجة "4" تشير إلى أن اسم "Vera" في المركز الرابع من القائمة. "4" في الصيغة هو نوع المطابقة الذي سيجد القيمة الأولى في صفيف البحث التي تساوي تمامًا قيمة البحث "Vera".

أن تعرف موقع الدرجة "96" في الصف من B2 إلى E2، يمكنك استخدام MATCH مثل هذا:

= MATCH (96، B2: E2,0،XNUMX) >>> يعود 4

تطابق مؤشر Excel 04

الأشياء التي يجب أن نعرفها عن وظيفة MATCH في Excel:

  • ترجع الدالة MATCH موضع قيمة البحث في صفيف البحث ، وليس القيمة نفسها.
  • تُرجع الدالة MATCH أول تطابق في حالة التكرارات.
  • تمامًا مثل وظيفة INDEX ، يمكن أن تعمل وظيفة MATCH مع النطاقات الرأسية والأفقية أيضًا.
  • MATCH ليست حساسة لحالة الأحرف أيضًا.
  • إذا كانت قيمة البحث لصيغة MATCH في شكل نص ، قم بتضمينها بين علامتي اقتباس.

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


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

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

مثال للجمع بين INDEX و MATCH

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

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

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> يعود 90

تطابق مؤشر Excel 05

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

تطابق مؤشر Excel 06

كما ترون أعلاه ، الكبير INDEX تحتوي الصيغة على ثلاث وسيطات:

  • مجموعة: A2: D11 يخبر INDEX بإرجاع القيمة المطابقة من الخلايا من خلال A2 إلى D11.
  • row_num: MATCH ("evelyn"، B2: B11,0،XNUMX) يخبر INDEX بالصف الدقيق للقيمة.
  • حول صيغة MATCH ، يمكننا شرحها على النحو التالي: لإرجاع موضع القيمة الأولى التي تساوي تمامًا "evelyn" في الخلايا من B2 إلى B11 بقيمة رقمية ، وهي 5.
  • العمود: MATCH ("الاختبار النهائي" ، A1: D1,0،XNUMX) يخبر INDEX بعمود القيمة بالضبط.
  • حول صيغة MATCH ، يمكننا شرحها على النحو التالي: لإرجاع موضع القيمة الأولى التي تساوي تمامًا "الاختبار النهائي" في الخلايا من A1 إلى D1 بقيمة رقمية ، وهي 4.

لذلك ، يمكنك أن ترى الصيغة الكبيرة بسيطة مثل تلك التي أظهرناها أدناه:

= الفهرس (A2: D11,5,4)

في المثال ، استخدمنا القيم المشفرة ، "evelyn" و "الاختبار النهائي". ومع ذلك ، في مثل هذه الصيغة الكبيرة ، لا نريد قيمًا مشفرة بشكل ثابت حيث سيتعين علينا تغييرها في كل مرة سنبحث فيها عن شيء جديد. في مثل هذه الظروف ، يمكننا استخدام مراجع الخلايا لجعل الصيغة ديناميكية كما يلي:

= الفهرس (A2: D11,تطابق(G2، B2: B11,0،XNUMX),تطابق(F3، A1: D1,0،XNUMX))

تطابق مؤشر Excel 07


INDEX و MATCH لتطبيق بحث على اليسار

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

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

أن تعرف صف إيفلين، كل ما عليك فعله هو تغيير القيمة في الخلية F3 إلى "فئة" ، واستخدام نفس الصيغة كما هو موضح أعلاه ، ستخبرك الدالتان INDEX و MATCH بالإجابة على الفور:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> يعود A

تطابق مؤشر Excel 08

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

تطابق مؤشر Excel 09

انقر هنا للحصول على خطوات ملموسة لتطبيق ميزة البحث الأيسر مع Kutools for Excel.


INDEX و MATCH لتطبيق بحث ثنائي الاتجاه

الآن ، هل أنت قادر على إنشاء صيغة تركيبة INDEX و MATCH بقيم بحث ديناميكي لتطبيق عمليات بحث ثنائية الاتجاه؟ دعنا نتدرب على عمل الصيغ في الخلايا G3 و G4 و G5 كما هو موضح أدناه:

تطابق مؤشر Excel 10

ها هي الإجابات:

الخلية G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
الخلية G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
الخلية G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ ملاحظة: بعد تطبيق الصيغ ، يمكنك بسهولة الحصول على معلومات أي طالب عن طريق تغيير الاسم في الخلية G2.


INDEX و MATCH لتطبيق بحث حساس لحالة الأحرف

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

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ ملاحظة: هذه صيغة مصفوفة تتطلب منك إدخالها باستخدام كترل + شيفت + إنتر. سيظهر بعد ذلك زوج من الأقواس المتعرجة في شريط الصيغة.

على سبيل المثال ، لمعرفة درجة امتحان JIMMY، استخدم الوظائف مثل هذا:

تطابق مؤشر Excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> يعود 86

أو يمكنك استخدام مراجع الخلايا:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> يعود 86
ملاحظة: لا تنسى الدخول بـ كترل + شيفت + إنتر.


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

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

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
ملاحظة: هذه صيغة صفيف تتطلب منك إدخالها باستخدام كترل + شيفت + إنتر. سيظهر بعد ذلك زوج من الأقواس المتعرجة في شريط الصيغة.

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

تطابق مؤشر Excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> يعود 88
√ ملاحظة: لا تنس الدخول بـ كترل + شيفت + إنتر.

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

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

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

تطابق مؤشر Excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> يعود 88

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

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

تطابق مؤشر Excel 14

انقر هنا للحصول على خطوات ملموسة لتطبيق ميزة البحث متعدد الشروط باستخدام Kutools for Excel.


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

إذا كان لدينا جدول بيانات Excel يحتوي على أعمدة مختلفة تشترك في تعليق واحد كما هو موضح أدناه ، فكيف يمكننا مطابقة اسم كل طالب بفصله / صفها مع INDEX و MATCH؟

تطابق مؤشر Excel 15

هنا ، دعني أوضح لك طريقة إكمال المهمة باستخدام أداتنا الاحترافية كوتولس ل إكسيل. مع ل مساعد الصيغة ، يمكنك مطابقة الطلاب مع فصولهم الدراسية بسرعة كما هو موضح بالخطوات الموضحة أدناه:

1. حدد الخلية الوجهة حيث تريد تطبيق الوظيفة.

2. تحت كوتولس تاب، انتقل إلى مساعد الصيغة، اضغط هنا مساعد الصيغة في القائمة المنسدلة.

تطابق مؤشر Excel 16

3. اختار بحث من نوع الصيغة، ثم انقر على فهرسة ومطابقة على أعمدة متعددة.

تطابق مؤشر Excel 17

4 ا. انقر فوق الأول رمز مطابقة فهرس Excelعلى الجانب الأيمن من Lookup_col لتحديد الخلايا التي تريد إرجاع قيمة منها ، أي أسماء الفئات. (يمكنك فقط تحديد عمود أو صف واحد هنا.)
    ب. انقر فوق الثاني رمز مطابقة فهرس Excelعلى الجانب الأيمن من Table_rng لتحديد الخلايا لمطابقة القيم في المحدد Lookup_col، أي أسماء الطلاب.
    ج. انقر فوق الثالث رمز مطابقة فهرس Excelعلى الجانب الأيمن من ابحث عن القيمة لتحديد الخلية المراد البحث عنها ، أي اسم الطالب الذي تريد مطابقته مع فصله / صفها.

تطابق مؤشر Excel 18

5. انقر فوق "موافق" ، سترى اسم الفصل لجيمي يظهر في الخلية الوجهة.

تطابق مؤشر Excel 19

6. يمكنك الآن سحب مقبض التعبئة لأسفل لملء فصول الطلاب الآخرين.

تطابق مؤشر Excel 20

انقر لتنزيل Kutools for Excel للحصول على نسخة تجريبية مجانية مدتها 30 يومًا.



  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى...
  • دمج الخلايا / الصفوف / الأعمدة وحفظ البيانات. تقسيم محتوى الخلايا ؛ اجمع الصفوف المكررة والمجموع / المتوسط... منع تكرار الخلايا؛ قارن النطاقات...
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • المفضلة وإدراج الصيغ بسرعةوالنطاقات والمخططات والصور ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات...
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF...
  • تجميع الجدول المحوري حسب رقم الأسبوع واليوم من الأسبوع والمزيد ... إظهار الخلايا غير المؤمنة والمغلقة بألوان مختلفة قم بتمييز الخلايا التي لها صيغة / اسم...
علامة تبويب kte 201905
  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.