اكسل

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

How Use Index Match

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



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

دالة INDEX | دالة MATCH | الفهرس والمطابقة | 2-طريقة البحث | بحث اليسار | حساسية الموضوع | أقرب مباراة | معايير متعددة | مزيد من الأمثلة





وظيفة الفهرس

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

 
= INDEX (B3:B11,4)

باستخدام INDEX للحصول على اسم الكوكب الرابع
تُرجع الدالة INDEX القيمة الموجودة في الصف الرابع من النطاق.



فيديو: كيف تبحث عن الأشياء باستخدام INDEX

ماذا لو كنت تريد الحصول على قطر المريخ باستخدام INDEX؟ في هذه الحالة ، يمكننا توفير رقم صف ورقم عمود ، وتوفير نطاق أكبر. تستخدم صيغة INDEX أدناه النطاق الكامل للبيانات في B3: D11 ، مع رقم الصف 4 ورقم العمود 2:

 
= INDEX (B3:D11,4,2)

باستخدام INDEX للحصول على قطر الكوكب الرابع
يسترد مؤشر INDEX القيمة في الصف 4 ، العمود 2.

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

في هذه المرحلة ، قد تفكر في 'ماذا في ذلك؟ كم مرة تعرف بالفعل موضع شيء ما في جدول بيانات؟

صحيح تماما. نحتاج إلى طريقة لتحديد موقع الأشياء التي نبحث عنها.

أدخل وظيفة المباراة.

وظيفة MATCH

تم تصميم وظيفة MATCH لغرض واحد: العثور على موضع عنصر في النطاق. على سبيل المثال ، يمكننا استخدام MATCH للحصول على موضع كلمة 'peach' في قائمة الفواكه مثل هذا:

 
= MATCH ('peach',B3:B9,0)

استخدام MATCH لإيجاد موضع في نطاق عمودي
تعيد MATCH 3 ، لأن 'Peach' هو العنصر الثالث. MATCH ليست حساسة لحالة الأحرف.

لا تهتم MATCH إذا كان النطاق أفقيًا أو رأسيًا ، كما ترى أدناه:

 
= MATCH ('peach',C4:I4,0)

استخدام MATCH لإيجاد موضع في نطاق أفقي
نفس النتيجة مع نطاق أفقي ، تُرجع MATCH 3.

فيديو: كيفية استخدام MATCH للمطابقات التامة

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

INDEX و MATCH معًا

الآن بعد أن غطينا أساسيات INDEX و MATCH ، كيف نجمع بين الوظيفتين في صيغة واحدة؟ ضع في اعتبارك البيانات أدناه ، وهي جدول يعرض قائمة مندوبي المبيعات وأرقام المبيعات الشهرية لثلاثة أشهر: يناير وفبراير ومارس.

المبيعات عن طريق مندوب المبيعات بالشهر

لنفترض أننا نريد كتابة صيغة تُرجع رقم المبيعات لشهر فبراير لمندوب مبيعات معين. من المناقشة أعلاه ، نعلم أنه يمكننا إعطاء INDEX رقم صف وعمود لاسترداد قيمة. على سبيل المثال ، لإرجاع رقم مبيعات فبراير لـ Frantz ، نقدم النطاق C3: E11 مع الصف 5 والعمود 2:

 
= INDEX (C3:E11,5,2) // returns 94

لكن من الواضح أننا لا نريد ترميز الأرقام. بدلاً من ذلك ، نريد ملف متحرك ابحث عن.

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

 
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)

بأخذ الأمور خطوة إلى الأمام ، سنستخدم القيمة من H2 في MATCH:

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)

INDEX و MATCH للبحث عن مبيعات فبراير لأي اسم
تعثر MATCH على 'Frantz' وترجع 5 إلى INDEX للصف.

كي تختصر:

  1. INDEX يحتاج إلى مواضع رقمية.
  2. تجد MATCH تلك المواقف.
  3. المباراة هي متداخل داخل INDEX.

دعنا الآن نتناول رقم العمود.

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

أعلاه ، استخدمنا وظيفة MATCH للعثور على رقم الصف ديناميكيًا ، لكننا قمنا بترميز رقم العمود. كيف يمكننا جعل الصيغة ديناميكية بالكامل ، حتى نتمكن من إرجاع المبيعات لأي مندوب مبيعات معين في أي شهر معين؟ الحيلة هي استخدام MATCH مرتين - مرة للحصول على موضع صف ، ومرة ​​للحصول على موضع عمود.

من الأمثلة أعلاه ، نعلم أن MATCH تعمل بشكل جيد مع كل من المصفوفات الأفقية والرأسية. هذا يعني أنه يمكننا بسهولة العثور على المركز لشهر معين باستخدام MATCH. على سبيل المثال ، تُرجع هذه الصيغة موضع مارس ، وهو 3:

 
= MATCH ('Mar',C2:E2,0) // returns 3

لكن بالطبع لا نريد الترميز الثابت أي القيم ، لذلك دعونا نقوم بتحديث ورقة العمل للسماح بإدخال اسم الشهر ، واستخدام MATCH للعثور على رقم العمود الذي نحتاجه. تظهر الشاشة أدناه النتيجة:

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

 
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))

تُرجع صيغة MATCH الأولى 5 إلى INDEX كرقم الصف ، وتعيد صيغة MATCH الثانية 3 إلى INDEX كرقم عمود. بمجرد تشغيل MATCH ، يتم تبسيط الصيغة إلى:

 
= INDEX (C3:E11,5,3)

وإرجاع INDEX بشكل صحيح 10525 دولارًا ، وهو رقم مبيعات فرانتز في مارس.

ملاحظة: يمكنك استخدام ملفات تأكيد صحة البيانات لإنشاء قوائم منسدلة لتحديد مندوب المبيعات والشهر.

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

فيديو: كيفية تصحيح أخطاء صيغة باستخدام F9 (لمشاهدة قيم إرجاع MATCH)

بحث اليسار

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

البحث الأيسر باستخدام INDEX و MATCH

اقرأ شرحًا مفصلاً هنا .

بحث حساس لحالة الأحرف

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

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

اقرأ شرحًا مفصلاً هنا .

ملاحظة: هذا ملف صيغة الصفيف ويجب إدخالها باستخدام control + shift + enter ، ما عدا في اكسل 365 .

أقرب مباراة

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

ابحث عن أقرب تطابق مع INDEX و MATCH

اقرأ شرحًا مفصلاً هنا .

ملاحظة: هذا ملف صيغة الصفيف ويجب إدخالها باستخدام control + shift + enter ، ما عدا في اكسل 365 .

بحث متعدد المعايير

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

INDEX و MATCH مع معايير متعددة

اقرأ شرحًا مفصلاً هنا .

ملاحظة: هذا ملف صيغة الصفيف ويجب إدخالها باستخدام control + shift + enter ، ما عدا في اكسل 365 .

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

المزيد من الأمثلة على INDEX + MATCH

فيما يلي بعض الأمثلة الأساسية عن الفهرس والمطابقة أثناء العمل ، ولكل منها شرح مفصل:

مرفقات المؤلف ديف برونز ملف فهرس exceljet و match.xlsx


^