انتقل إلى المحتوى الرئيسي

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

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


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

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


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

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

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

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

=INDEX(C2:C11,6)

تطابق مؤشر Excel 01

√ ملاحظة: النطاق C2: C11 حيث الدرجات المذكورة، في حين أن العدد 6 يجد درجة الامتحان الخاصة بـ 6الطالب عشر.

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

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

=INDEX(A2:C11,6,3)

تطابق مؤشر Excel 02

الأشياء التي يجب أن نعرفها عن الدالة INDEX في Excel:
  • يمكن أن تعمل الدالة INDEX مع النطاقات الرأسية والأفقية.
  • إذا كان كل من Row_num و العمود يتم استخدام الحجج Row_num يتقدم على العمود، ويقوم INDEX باسترداد القيمة عند تقاطع المحدد Row_num و العمود.

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


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

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

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

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

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

تطابق مؤشر Excel 3

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

الأشياء التي يجب أن نعرفها عن الدالة MATCH في Excel:
  • ترجع الدالة MATCH موضع قيمة البحث في صفيف البحث ، وليس القيمة نفسها.
  • تُرجع الدالة MATCH أول تطابق في حالة التكرارات.
  • تمامًا مثل وظيفة INDEX ، يمكن أن تعمل وظيفة MATCH مع النطاقات الرأسية والأفقية أيضًا.
  • MATCH ليست حساسة لحالة الأحرف.
  • إذا كان ابحث عن القيمة صيغة MATCH في شكل نص، قم بإحاطتها بين علامتي اقتباس.
  • إذا كان ابحث عن القيمة لم يتم العثور على lookup_arrayأطلقت حملة # N / A تم إرجاع الخطأ.

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


كيفية الجمع بين INDEX و MATCH في Excel

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

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

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

تطابق مؤشر Excel 4

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

تطابق مؤشر Excel 5

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

  • Row_num: MATCH("إيفيلين"،A2:A11,0) يزود INDEX بموضع صف القيمة "إيفلين"في النطاق A2: A11، والذي هو 5.
  • العمود: 3 يحدد 3العمود الثالث لـ INDEX لتحديد النتيجة داخل المصفوفة.
  • مجموعة: A2: C11 يأمر INDEX بإرجاع القيمة المطابقة عند تقاطع الصف والعمود المحددين، ضمن النطاق الممتد من A2 إلى C11. وأخيراً نحصل على النتيجة 90.

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

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

(م) تبسيط عمليات البحث باستخدام Kutools: لا يلزم كتابة الصيغة!

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

أدوات بحث Kutools

كوتولس ل إكسيل - تمكينك من خلال أكثر من 300 وظيفة سهلة الاستخدام لتحقيق إنتاجية سهلة. لا تفوت فرصتك لتجربتها مع نسخة تجريبية مجانية كاملة الميزات لمدة 30 يومًا! نبدأ الآن!


أمثلة على صيغة INDEX وMATCH

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


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

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

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

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

تطابق مؤشر Excel 6

كيف تعمل هذه الصيغة:
  • تجد صيغة المطابقة الأولى موقع Evelyn في القائمة A2:A11، مما يوفر 5 كرقم الصف إلى INDEX.
  • تحدد صيغة المباراة الثانية عمود النتائج والعائدات 3 كرقم العمود إلى INDEX.
  • يتم تبسيط الصيغة إلى =الفهرس(A2:C11,5,3)، وإرجاع INDEX 90.

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

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

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

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

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

تطابق مؤشر Excel 7

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

LOOKUP من اليمين إلى اليسار

إذا لم تقم بتثبيت Kutools، انقر هنا ل قم بالتنزيل واحصل على نسخة تجريبية مجانية كاملة المواصفات لمدة 30 يومًا!


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

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

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • مجموعة يشير إلى النطاق الذي تريد إرجاع القيمة منه.
  • ابحث عن القيمة يشير إلى القيمة المراد مطابقتها، مع الأخذ في الاعتبار حالة الأحرف، في lookup_array.
  • lookup_array يشير إلى نطاق الخلايا الذي تريد مقارنة MATCH به ابحث عن القيمة.

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

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

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

تطابق مؤشر Excel 8

كيف تعمل هذه الصيغة:
  • تقارن الدالة بالضبط "جيمي" مع القيم الموجودة في القائمة A2: A11، مع الأخذ في الاعتبار حالة الأحرف: إذا كانت السلسلتان متطابقتين بدقة، مع مراعاة الأحرف الكبيرة والصغيرة، فستُرجع EXACT الحقيقة; وإلا فإنه يعود خاطئة. ونتيجة لذلك نحصل على مصفوفة تحتوي على قيم TRUE وFALSE.
  • تقوم الدالة MATCH بعد ذلك باسترداد موضع القيمة الحقيقية الأولى في المصفوفة، والتي ينبغي أن تكون 10.
  • وأخيرًا، يقوم INDEX باسترداد القيمة الموجودة في ملف 10الموضع الذي توفره MATCH في المصفوفة.

الملاحظات:

  • تذكر إدخال الصيغة بشكل صحيح بالضغط كترل + شيفت + إنتر، إلا إذا كنت تستخدم التفوق 365 or التفوق 2021، وفي هذه الحالة، اضغط ببساطة أدخل.
  • تبحث الصيغة أعلاه ضمن قائمة واحدة C2: C11. إذا كنت تريد البحث ضمن نطاق يحتوي على أعمدة وصفوف متعددة، على سبيل المثال A2: C11، يجب عليك تقديم أرقام الأعمدة والصفوف إلى INDEX:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • في هذه الصيغة المعدلة، نستخدم وظيفة MATCH للبحث عن "JIMMY"، مع الأخذ في الاعتبار حالة الأحرف، في النطاق A2: A11وبمجرد العثور على تطابق، نقوم باسترداد القيمة المقابلة من 3العمود الثاني للنطاق A2: C11.

INDEX وMATCH للعثور على أقرب تطابق

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

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • مجموعة يشير إلى النطاق الذي تريد إرجاع القيمة منه.
  • lookup_array يشير إلى نطاق القيم الذي تريد العثور على أقرب تطابق له ابحث عن القيمة.
  • ابحث عن القيمة يشير إلى القيمة للعثور على أقرب تطابق لها.

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

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

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

كيف تعمل هذه الصيغة:
  • عبس (C2:C11-85) حساب الفرق المطلق بين كل قيمة في النطاق C2: C11 و 85، مما أدى إلى مجموعة من الاختلافات المطلقة.
  • الحد الأدنى (ABS (C2:C11-85)) يبحث عن القيمة الدنيا في مصفوفة الاختلافات المطلقة، والتي تمثل أقرب فرق إلى 85.
  • وظيفة MATCH MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) ثم يجد موضع الحد الأدنى للفرق المطلق في مجموعة الاختلافات المطلقة، التي ينبغي أن تكون 10.
  • وأخيرًا، يقوم INDEX باسترداد القيمة الموجودة في الموضع في القائمة A2: A11 الذي يتوافق مع النتيجة الأقرب إلى 85 في النطاق C2: C11.

الملاحظات:

  • تذكر إدخال الصيغة بشكل صحيح بالضغط كترل + شيفت + إنتر، إلا إذا كنت تستخدم التفوق 365 or التفوق 2021، وفي هذه الحالة، اضغط ببساطة أدخل.
  • في حالة التعادل، ستعيد هذه الصيغة المباراة الأولى.
  • لايجاد أقرب مباراة لمتوسط ​​النتيجة، يحل محل 85 في الصيغة مع المتوسط ​​(C2:C11).

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

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

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

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

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

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

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

تطابق مؤشر Excel 9

الملاحظات:

  • في هذه الصيغة، نتجنب القيم ذات الترميز الثابت، مما يجعل من السهل الحصول على درجة بمعلومات مختلفة عن طريق تعديل القيم في الخلايا G2, G3و G4.
  • يجب عليك إدخال الصيغة بالضغط كترل + شيفت + إنتر باستثناء التفوق 365 or التفوق 2021، حيث يمكنك الضغط ببساطة أدخل.
    إذا كنت تنسى الاستخدام باستمرار كترل + شيفت + إنتر لإكمال الصيغة والحصول على نتائج غير صحيحة، استخدم الصيغة التالية الأكثر تعقيدًا، والتي يمكنك من خلالها استكمالها بصيغة بسيطة أدخل مفتاح:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • يمكن أن تكون الصيغ معقدة ويصعب تذكرها. لتبسيط عمليات البحث متعددة المعايير دون الحاجة إلى إدخال الصيغة يدويًا، فكر في استخدام كوتولس ل إكسيلالصورة بحث متعدد الشروط ميزة. بمجرد تثبيت Kutools، انتقل إلى ملف كوتولس علامة التبويب في Excel الخاص بك، ثم انقر فوق سوبر بحث > بحث متعدد الشروط في ال المعادلة المجموعة.

    بحث متعدد الشروط

    إذا لم تقم بتثبيت Kutools، انقر هنا ل قم بالتنزيل واحصل على نسخة تجريبية مجانية كاملة المواصفات لمدة 30 يومًا!


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)), "")

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

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

نتيجة

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

ملحوظة: لتجربة الفهرسة والمطابقة على أعمدة متعددة الميزة، ستحتاج إلى تثبيت Kutools for Excel على جهاز الكمبيوتر الخاص بك. إذا لم تقم بتثبيته بعد، فلا تنتظر --- قم بتنزيله وتثبيته الآن للحصول على نسخة تجريبية مجانية مدتها 30 يومًا دون أي قيود. اجعل Excel يعمل بشكل أكثر ذكاءً اليوم!


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))
  • هناك وسيطتان في الصيغ أعلاه:
    • مجموعة يشير إلى النطاق الذي تريد إرجاع المعلومات ذات الصلة منه.
    • lookup_array يمثل مجموعة القيم المراد فحصها أو البحث عنها لمعايير محددة، أي القيم القصوى أو الدنيا.

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

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

كيف تعمل هذه الصيغة:
  • MAX (C2: C11) يبحث عن أعلى قيمة في النطاق C2: C11، والذي هو 96.
  • تقوم الدالة MATCH بعد ذلك بالبحث عن موضع أعلى قيمة في الصفيف C2: C11الذي يجب أن يكون 1.
  • أخيرًا، يقوم INDEX باسترداد ملف 1القيمة الحادية في القائمة A2: A11.

الملاحظات:

  • في حالة وجود أكثر من قيمة قصوى أو دنيا واحدة، كما هو موضح في المثال أعلاه حيث حقق طالبان نفس أعلى الدرجات، ستعيد هذه الصيغة المطابقة الأولى.
  • لتحديد من حصل على أقل الدرجات، استخدم الصيغة التالية:
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

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

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

تطابق مؤشر Excel 15

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

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

تطابق مؤشر Excel 16

الملاحظات:

  • يمكنك تخصيص رسائل الخطأ الخاصة بك عن طريق استبدال "غير معثور عليه" مع أي نص من اختيارك.
  • إذا كنت تريد معالجة كافة الأخطاء، وليس فقط #N/A، ففكر في استخدام خطأ مرجّح تعمل بدلا من إيفنا:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    لاحظ أنه قد لا يكون من المستحسن منع كافة الأخطاء لأنها تعمل بمثابة تنبيهات للمشكلات المحتملة في الصيغ الخاصة بك.

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

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations