INDEX & MATCH function With Ex (دالة INDEX & MATCH مع الأمثلة)
جدول المحتويات: Table of contents
1- ماهي دالة INDEX & MATCH؟
?What is the INDEX & MATCH Function
2- كيفية استخدام دالة INDEX & MATCH؟
?How to Use the INDEX & MATCH Function
1-2 استخدام دالة INDEX
Using the INDEX function
A- دالة INDEX على طريقة (Array Form)
Excel INDEX Function (Array Form)
B- دالة INDEX على طريقة (Reference Form)
Excel INDEX Function (Reference Form)
C- دالة Index مع قوائم متعددة
Index function with multiple lists
2-2 استخدام دالة MATCH
Using the MATCH function
3-2 استخدام دالة INDEX و MATCH
Using the INDEX and MATCH function
1- ماهي دالة INDEX & MATCH؟
دالة INDEX and MATCH هي الاداة التي تمكنك من إجراء عمليات بحث أكثر تقدمًا. هذا لأن INDEX و MATCH مرنة بشكل لا يصدق ، وكذلك يمكنك إجراء عمليات بحث أفقية وعمودية ، وعمليات بحث ثنائية الاتجاه ، وعمليات بحث لليسار، وعمليات بحث حساسة لحالة الأحرف ، وأيضاََ عمليات بحث تستند إلى معايير متعددة. فإذا كنت ترغب في تحسين مهاراتك في Excel يجب أن يكون INDEX و MATCH ضمن قائمتك.
======================
2- كيفية استخدام دالة INDEX & MATCH؟
1-2 استخدام دالة INDEX ه//ه Using the INDEX function
ترجع الدالة INDEX القيمة في موقع محدد في جدول أو نطاق بيانات ، أي بمعنى ترجع الدالة INDEX القيمة عند تقاطع عمود وصف. يمكنك استخدام INDEX لاسترداد القيم الفردية ، أو الصفوف والأعمدة بأكملها. هذه الوظيفة مفيدة بشكل خاص عندما تريد استخراج البيانات بناءً على أرقام الصفوف والأعمدة المحددة.
الغرض منها : الحصول على قيمة في قائمة أو جدول بناءً على الموقع.
القيمة المرجعة (الناتج) : القيمة في موقع معين.
- بناء الجملة (Syntax) : (Array Form)
=INDEX(array, row_num, [col_num], [area_num])
الحجج أو العناصر أو الوسائط (Arguments):
array - نطاق من الخلايا، أو جدول البيانات الخاص بك.
row_num - رقم صف الجدول الذي توجد به بياناتك.
col_num - [اختياري] رقم عمود الجدول الذي توجد به بياناتك.
- بناء الجملة (Syntax) : (Reference Form)
=INDEX(reference, row_num, [col_num], [area_num])
الحجج أو العناصر أو الوسائط (Arguments):
reference - نطاق من الخلايا ، أو جدول البيانات الخاص بك.
row_num - رقم صف الجدول الذي توجد به بياناتك.
col_num - [إختياري] رقم عمود الجدول الذي توجد به بياناتك.
area_num - [إختياري] النطاق المرجعي الذي ينبغي استخدامه.
لمزيد من المعلومات على دالة (INDEX) أذهب من هنا
======================
A- دالة INDEX على طريقة (Array Form)
مثال: حدد عنصرًا من قائمة / Select an Item from a List
في هذا المثال لدينا جدول بيانات مكون من : رقم الموظف (Number ID) / الإسم الأول (First Name) / الأسم الثاني ( Last Name) / والرواتب (Salary). المطلوب هو ايجاد عنصر معين من القائمة أو جدول البيانات. وفي مثالنا هنا ايجاد الاسم الأول للموظف (Dalia Sami).
كما هو موضح في الصورة أعلاه أستخدمنا دالة (INDEX) لإيجاد الاسم الأول للموظف (Dalia Sami) وهو (Dalia) وكانت الصيغة كالتالي :
=INDEX(B5:E11,4,2)
==============================
B- دالة INDEX على طريقة (Reference Form)
مثال: إرجاع صف أو عمود بالكامل من نطاق / Returning a Row or Column Entirely from a Range
هنا لدينا جدول بيانات أو قائمة تحتوي على معلومات عن أسماء معينة مع أعمارهم وتخصصهم الدراسي ومكان إقامتهم. المطلوب هو إيجاد صف معين لإسم محدد يحتوي على جميع المعلومات الخاصة به أو عامود معين. وفي مثالنا هنا المطلوب هو إيجاد صف لإسم (Mary) يحتوي على جميع معلوماتها. وعامود لأعمار (Age) جميع الأسماء المذكورة في الجدول.
شرح المثال:
1- بإستخدام دالة (INDEX) أوجدنا صف متكامل يوجد به جميع معلومات (Mary) والتي تحتوي على العمر (32) ومكان إقامتها (Paris) وتخصصها الدراسي (Teacher) وكانت الصيغة كالتالي :
=INDEX(B24:E27,2,1):INDEX(B24:E27,2,4)
OR
=INDEX(B24:E27,2,0)
2- كما هو موضح في الصورة أعلاه أستخدمنا دالة (INDEX) لإيجاد عامود الأعمار (Age) لكل الأسماء وكانت الصيغة كالتالي :
=INDEX(B24:E27,1,2):INDEX(B24:E27,4,2)
OR
=INDEX(B24:E27,0,2)
==============================
C- دالة Index مع قوائم متعددة
مثال (1): إرجاع قيمة أو قيم من نطاقات متعددة / Returns a value or values from multiple ranges
في هذا المثال لدينا ثلاث قوائم تحتوي على فواكه معينة. المطلوب هو إيجاد عنصر محدد من قائمة أو عدة قوائم وذلك بطريقتين :
الأولى - استخدام مرجع خلية (cell reference) والتي تحتوي على رقم الصف (row number) وهو رقم (3).
الثانية - إختيار عنصر (Orange) من القائمة الأولى ورقم الصف الثالث.
شرح المثال:
بالطريقة الأولى أوجدنا العنصر (Orange) من القائمة الثالثة ومرتبط بمرجع الخلية (G12) التي تحتوي على الرقم (3) وكانت الصيغة كالتالي كما هو موضح في الصورة أعلاه :
=INDEX(C6:C9,G12)
ولكن بالطريقة الثانية كان إختيار العنصر (Orange) من عدة قوائم كما هو موضح في الصورة أعلاه وكانت الصيغة كالتالي :
=INDEX((C6:C9,E6:E9,G6:G9),3,1,1)
ملاحظة (Note):
عندما تريد أن تغير من قائمة إلى أخري يجب أن تغير الوسيطة (area_num) برقم القائمة التي تريدها.
========================
مثال (2): إرجاع قيمة أو قيم من نطاقات متعددة / Returns a value or values from multiple ranges
في هذا المثال لدينا قائمتين من الفواكه والكمية. والمطلوب إرجاع قيمة محددة من القائمتين. وفي مثالنا مطلوب إيجاد عدد الفاكه (Kiwi) من القائمة الثانية.
شرح المثال:
كما هو موضح في الصورة أعلاه أستطعنا إيجاد كمية فاكهة (Kiwi) من القائمة وذلك بإستخدام دالة (INDEX) وكانت الصيغة كالتالي:
INDEX(($C$24:$D$27,$F$24:$G$27),2,2,2)=
=====================
2-2 استخدام دالة MATCH
تبحث الدالة Match عن قيمة محددة في صف أو عامود أو نطاق بيانات أو نطاق من الخلايا، ثم تُرجع الموضع النسبي لهذا العنصر في النطاق. وممكن استخدام أو دمج دالة Match & Index وذلك لإجراء عمليات بحث أكثر تقدماََ، وسوف نرى هذا مع الأمثلة.
الغرض منها: الحصول على موضع عنصر ما في نطاق معين.
القيمة المرجعة (الناتج): عنصر في موقع معين.
بناء الجملة (Syntax):
=MATCH(lookup_value, lookup_array, [match_type])
الحجج أو العناصر أو الوسائط (Arguments):
lookup_value - القيمة المطلوبة مطابقتها.
lookup_array - نطاق من الخلايا أو جدول بيانات.
match_type - [اختياري] 1 = تامة أو تالية الأصغر (افتراضي) ، 0 = مطابقة تامة ، -1 = تامة أو تالية الأكبر.
لمزيد من المعلومات على دالة (Match) أذهب من هنا
=====================
مثال : حدد عنصرًا من القائمة / Select an Item from a List
في هذا المثال لدينا جدول بيانات والمطلوب :
1- إيجاد رقم الصف لرقم موظف ، ورقمه (35).
2- إيجاد رقم الصف للموظف (Hussam) وذلك بإستخدام حروف البدل (Wildcard).
شرح المثال:
1- كانت النتيجة هي (4) وهو رقم الصف كما هو موضح في الصورة أعلاه وكانت الصيغة كالتالي:
=MATCH(H7,B5:B11,0)
2- وكان رقم الصف للموظف هو (3) وذلك بإستخدام أحرف البدل (*) مع دالة (Match) وكانت الصيغة كالتالي:
=MATCH("Hus*",D5:D11,0)
==========================
3-2 استخدام دالة INDEX و MATCH
مثال (1): بحث ثنائي الاتجاه باستخدام INDEX و MATCH (تطابق تام) / Two-way lookup with INDEX and MATCH (Exact match)
في هذا المثال لدينا قائمة تحتوي على أرقام المنتجات (Product Nu) , السنين (years) , وأسعار المبيعات (Seale). المطلوب هو إيجاد قيمة محددة بمعايير متعددة. وفي مثالنا المطلوب إيجاد قيمة مبيعات سنة (2022) المرتبطة برقم المنتج (30) بالتطابق التام.
شرح المثال:
كما هو موضح في الصورة أعلاه كانت النتيجة هي مبيعات سنة (2022) ورقم المنتج (30) هي (6,370) وذلك بإستخدام دالتي MATCH و INDEX بالتطابق التام (Exact match) وكانت الصيغة كالتالي:
=INDEX(B5:E11,MATCH(H6,B5:B11,0),MATCH(H5,B4:E4,0))
==================
مثال (2): بحث ثنائي الاتجاه باستخدام INDEX و MATCH (تطابق تقريبي) / Two-way lookup with INDEX and MATCH (approximate match)
نفس المثال السابق ونفس المطلوب ولكن بالتطابق التقريبي.
شرح المثال:
بإستخدام دالتي MATCH و INDEX أستطعنا أن نحصل على مبيعات سنة (2022) ورقم المنتج (32) وكان ما بين (30) و (35) فأصبحت نتيجة المبيعات تساوي (6,370) وذلك بإستخدام التطابق التقريبي (approximate match) وكانت الصيغة كالتالي:
=INDEX(B21:E27,MATCH(H22,B21:B27,1),MATCH(H21,B20:E20,1))
وأخيرأ: هذا كل شيء عن درس اليوم. وهذه بعض الطرق السهلة لاستخدام وظيفة (Excel Index & Match). لمزيد من التوضيح يرجى تنزيل ورقة التدريب العملي ومشاهدة الفديوا. شكرا لصبركم في قراءة هذا الدرس.
Download the Excel Tutorial workbook
قم بتنزيل مصنف Excel التعليمي
تعليقات
إرسال تعليق