بالنسبة للكثيرين ، فإن مجموعة INDEX-XMATCH في Excel هي طريقة الانتقال لاسترداد قيمة من مجموعة بيانات. ومع ذلك ، يمكنك أيضًا استخدام هذا الثنائي الديناميكي لإجراء عمليات البحث في اتجاهين ، وإعادة قيمة عند تقاطع صف وعمود محدد.
تهدف هذه المقالة إلى القراء عبر جميع مستويات خبرة Excel ، مما يزيد في التعقيد مع تقدمه. إذا لم تكن معتادًا على وظائف الفهرس و Xmatch ، فتواصل القراءة. ومع ذلك ، إذا كنت تعرف بالفعل كيفية استخدام الفهرس و Xmatch للبحث في اتجاه واحد ، فاقب مباشرة إلى القسم الموجه باستخدام الفهرس مع XMatch للبحث في اتجاهين.
كيف تعمل فهرس Excel و Xmatch وظائف
قبل أن ننظر إلى كيفية استخدام هاتين الوظيفتين معًا في صيغة واحدة ، دعنا نأخذ لحظة لاستكشاف كيفية عملهما بمعزل عن غيرها.
وظيفة الفهرس
تقوم وظيفة الفهرس بإرجاع قيمة في نطاق وفقًا لرقم الصف ورقم العمود الذي تحدده. بناء الجملة كما يلي:
=INDEX(a,b,c)
أين
- أ هو النطاق ،
- ب هو رقم الصف ، و
- ج هو رقم العمود.
على سبيل المثال ، الكتابة:
=INDEX(T_Profit,3,6)
في الخلية H2 إرجاع القيمة في الخلية التي تقع عند تقاطع الصف الثاني والعمود الثالث من جدول T_Profit.
ما هو أكثر من ذلك ، بدلاً من ترميز أرقام الصف والأعمدة ، يمكنك الرجوع إلى الخلايا التي تحتوي عليها ، مما يجعل الصيغة أكثر مرونة. هنا ، الكتابة:
=INDEX(T_Profit,I2,I3)
في الخلية i5 يسحب رقم الصف من الخلية i2 ورقم العمود من الخلية i3.
وظيفة XMatch
تبحث وظيفة XMatch عن عنصر في نطاق وإرجاع موقعه.
إذا كنت تعمل في تطبيق Excel Desktop على جهاز كمبيوتر أو Mac ، فيجب أن تستخدم Excel 2021 أو أحدث (بما في ذلك Excel لـ Microsoft 365) لاستخدام وظيفة XMatch. إنه متوفر بسهولة في تتفوق على الويب وعلى أجهزة الكمبيوتر اللوحي والتطبيقات المحمولة.
هذا هو بناء الجملة:
=XMATCH(a,b,c,d)
أين
- أ هو العنصر للبحث عن ،
- ب هو النطاق للبحث ،
- ج هل نوع المطابقة (0 = مطابقة دقيقة (افتراضي) ؛ -1 = مطابقة دقيقة أو أصغر عنصر التالي ؛ 1 = مطابقة دقيقة أو أكبر عنصر ؛ 2 = مطابقة البدل) ، و
- د هو وضع البحث (1 = الأول إلى الماضي (افتراضي) ، -1 = الماضي إلى الأول ، 2 = البحث الثنائي حيث ب هو في ترتيب تصاعدي ، -2 = البحث الثنائي حيث ب في ترتيب تنازلي).
قد تكون على دراية بوظيفة المطابقة ، وهي السلف لوظيفة XMatch الأكثر حداثة. إنها تعمل بطرق مماثلة ، على الرغم من أن الوسيطات الافتراضية في بناء جملة XMatch أكثر سهولة من تلك الموجودة في بناء جملة المطابقة ، حيث تفضل مطابقة دقيقة على واحدة تقريبية. أيضًا ، يتيح لك XMatch البحث في أي من الاتجاهين واستخدام أحرف Bildcard للمباريات الجزئية – لا يمكنك القيام بها بمطابقة.
في هذا المثال ، الكتابة:
=XMATCH(1927,T_Profit(Employee))
في الخلية H2 إرجاع 3 ، لأن رقم معرف الموظف 1927 هو القيمة الثالثة في عمود الموظف في جدول T_Profit.
لاحظ كيف الحجج ج و د ليست مطلوبة في هذا السيناريو ، لأننا نريد تطابقًا دقيقًا في البحث الذي يتم تشغيله من الجزء العلوي من الجدول إلى الأسفل ، وهذه هي الإعدادات الافتراضية لهذه الوظيفة.
وبالمثل ، الحجة أ يمكن أن يكون مرجعًا إلى خلية تحتوي على قيمة البحث ، مما يعني أنه يمكنك تغيير قيمة البحث بسهولة دون تحرير الصيغة:
=XMATCH(I2,T_Profit(Employee))
حيث تحتوي الخلية I2 على معرف الموظف للبحث في عمود اللاعب في جدول T_SCORES.
استخدام الفهرس مع XMatch للبحث في اتجاه واحد
في حين أن وظائف الفهرس و XMatch يمكن أن تكون مفيدة من تلقاء نفسها ، يتم تحقيق إمكاناتها الحقيقية عند استخدامها معًا. مفتاح فهم كيفية استخدام مجموعة INDEX-XMATCH لأداء عمليات البحث في اتجاهين هو أولاً الحصول على رأسك حول كيفية عمله في المواقف أحادية البعد.
لنفترض أنك تريد عرض إجمالي الربح الذي تم إنشاؤه بواسطة موظف عند كتابة معرفه في الخلية I2.
للقيام بذلك ، في الخلية i2 ، اكتب:
=INDEX(T_Profit,XMATCH(I2,T_Profit(Employee)),6)
أين
- t_profit هو اسم الجدول حيث سيتم العثور على القيمة ،
- XMatch (i2 ، t_profit (موظف)) يخبر وظيفة الفهرس أي صف من عمود الموظف الذي يجب البحث فيه ، بناءً على القيمة في الخلية I2 ، و
- 6 يخبر وظيفة الفهرس لإرجاع القيمة في العمود السادس من هذا الصف.
في هذا السيناريو ، لا تحتاج إلى إدخال وسيطات وضع المطابقة أو وسيطات البحث لجزء XMatch من الصيغة ، حيث أن الإعدادات الافتراضية تُرجع مطابقة دقيقة وابحث من أعلى إلى أسفل.
ولكن ماذا لو كنت ترغب في إرجاع القيمة من عمود آخر ، مثل ربح الموظف في سنة معينة؟ هذا هو المكان الذي تأتي فيه عمليات البحث في اتجاهين في متناول يدي.
باستخدام الفهرس مع XMatch للبحث في اتجاهين
إن الاستفادة من استخدام الفهرس مع XMatch للبحث في اتجاهين هو أنه يمكنك تغيير المعلمات لبحثك دون تحرير الصيغة. وذلك لأن XMatch يحدد كلاً من رقم الصف ورقم العمود ، مما يعني أنه لا يتعين عليك ترميزها في الصيغة.
لنفترض أنك ترغب في معرفة مقدار الربح الموظف 1191 في عام 2021.
للقيام بذلك ، في الخلية i4 ، اكتب:
=INDEX(T_Profit,XMATCH(I2,T_Profit(Employee)),XMATCH(I3,T_Profit(#Headers)))
أين
- t_profit هو اسم الجدول حيث سيتم العثور على القيمة ،
- XMatch (i2 ، t_profit (موظف)) يخبر وظيفة الفهرس أي صف من عمود الموظف الذي يجب البحث فيه ، بناءً على القيمة في الخلية I2 ، و
- XMatch (i3 ، t_profit (#bheaders)) يروي وظيفة الفهرس أي عمود من جدول T_Profit للنظر فيه ، استنادًا إلى القيمة في الخلية I3.
إذا كانت رؤوس العمود الخاصة بك تحتوي على قيم رقمية ، مثل التواريخ ، فقد ترى خطأ #N/A عند الضغط على Enter.
وذلك لأن Excel يقوم بالفعل بتخزين رؤوس الأعمدة كنص ، حتى لو كانت تبدو رقمية. لذلك ، للتأكد من حصولك على تطابق مشابه بين قيمة البحث ومصفوفة البحث ، حدد الخلية التي تحتوي على قيمة البحث المقابلة (والتي ، في هذا المثال ، الخلية i3) ، وفي علامة التبويب المنزل على الشريط ، انقر فوق “نص” في القائمة المنسدلة لتنسيق الأرقام.
بعد ذلك ، حدد الخلية التي تحتوي على قيمة بحث العمود (I3) ، واضغط على F2 لتنشيط وضع تحرير الخلية ، واضغط على Enter. الآن ، يرى Excel كلاً من قيمة البحث ومصفوفة البحث على نفس تنسيق الرقم ، وبالتالي فإن الصيغة تُرجع القيمة المتوقعة بشكل صحيح.
الآن ، اكتب معلمات البحث المختلفة في الخلايا i2 و i3 ، وشاهد الصيغة تُرجع النتيجة المقابلة.
الآن بعد أن عرفت كيفية استخدام الفهرس و XMatch لإجراء عمليات البحث في اتجاهين ، يمكنك الذهاب خطوة واحدة إلى الأمام واستخدام التحقق من صحة البيانات لإنشاء قوائم منسدلة في الخلايا التي تحتوي على قيم البحث ، وزيادة تسريع عملية البحث وضمان عدم إدخال قيمة غير صالحة.
ومع ذلك ، ضع في اعتبارك أنه لا يمكنك استخدام رؤوس الأعمدة كمصدر لقائمة التحقق من صحة البيانات. للتغلب على هذه العقبة ، أدخل مراجع الخلية المباشرة في حقل المصدر لمربع مربع التحقق من صحة البيانات ، أو تسمي النطاقات والرجوع إليها بدلاً من ذلك.
(tagstotranslate) Microsoft (T) Microsoft Excel (T) Microsoft 365