دليل شامل لاستخدام الدالة XLOOKUP في Excel: كيفية البحث عن القيم بسرعة ودقة في جداول البيانات

بسم الله الرحمن الرحيم

اهلا بكم متابعى موقع عالم الاوفيس

دليل شامل لاستخدام الدالة XLOOKUP في Excel: كيفية البحث عن القيم بسرعة ودقة في جداول البيانات

اخيرا وصلت الدالة Excel XLOOKUP.

إذا كنت تستخدم VLOOKUP أو INDEX / MATCH ، فأنا متأكد من أنك ستحب المرونة التي توفرها وظيفة XLOOKUP.

في هذا المقال والدرس التعليمي ، سأغطي كل ما يمكن معرفته عن وظيفة XLOOKUP وبعض الأمثلة التي ستساعدك على معرفة أفضل طريقة لاستخدامها.

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

وتعد الدالة XLOOKUP أكثر دقة وسرعة من الدوال الأخرى المستخدمة في البحث عن القيم في Excel مثل VLOOKUP و HLOOKUP، وهي متاحة في إصدارات Excel 365 و Excel 2019 و Excel 2016 و Excel Online.

يمكن استخدام الدالة XLOOKUP بمجموعة متنوعة من الطرق ، بما في ذلك البحث بالقيمة الدقيقة، البحث بالقيمة الأقرب، البحث بالعنوان النسبي، البحث بالعنوان المطابق، والبحث بأكثر من قيمة في نفس الوقت.

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

اذا هيا بنا نبدأ!

ما هى الدالة XLOOKUP؟

الدالة XLOOKUP هي دالة جديدة هي Office 365 وهي إصدار جديد ومحسّن من الدالة VLOOKUP / HLOOKUP.

تقوم الدالة XLOOKUP كل ما اعتادت الدالة VLOOKUP القيام به ، وأكثر من ذلك بكثير.

الدالة XLOOKUP

هي دالة ووظيفة فى الاكسل تسمح لك بالبحث بسرعة عن قيمة في مجموعة بيانات (رأسية أو أفقية) وإرجاع القيمة المقابلة في صف / عمود آخر.على سبيل المثال ، إذا كنت قد حصلت على درجات الطلاب في أحد الاختبارات ، فيمكنك استخدام الدالة XLOOKUP للتحقق سريعًا من مقدار الدرجات التي حصل عليها الطالب باستخدام اسم الطالب.

ستصبح قوة هذه الدالة أكثر وضوحًا عندما أغوص معك بعمق في بعض أمثلة XLOOKUP لاحقًا في هذا المقال والدرس التعليمي.

لكن قبل الدخول في الأمثلة ، هناك سؤال كبير - كيف يمكنني الوصول إلى الدالة XLOOKUP ؟

كيفية الوصول إلى الدالة XLOOKUP ؟

اعتبارًا من الآن ، يتوفر الدالة XLOOKUP فقط لمستخدمي Office 365.

لذلك ، إذا كنت تستخدم إصدارات سابقة من Excel (2010/2013/2016/2019) ، فلن تتمكن من استخدام هذه الدالة XLOOKUP.

لست متأكدًا أيضًا مما إذا كان سيتم إصدار هذا للإصدارات السابقة أم لا (ربما يمكن لـ Microsoft إنشاء وظيفة إضافية بالطريقة التي فعلوا بها لـ Power Query). ولكن اعتبارًا من الآن ، لا يمكنك استخدامه إلا إذا كنت تستخدم Office 365.

إذا كنت تستخدم Office 365 بالفعل (الإصدار المنزلي أو الشخصي أو إصدار الجامعة) ولا يمكنك الوصول إليه ، فيمكنك الانتقال إلى علامة التبويب "ملف" ثم النقر فوق "الحساب".

سيكون هناك برنامج Office Insider ويمكنك النقر فوق برنامج Office Insider والانضمام إليه. سيتيح لك هذا الوصول إلى وظيفة XLOOKUP

انا أتوقع أن تتوفر الدالة XLOOKUP على جميع إصدارات Office 365 قريبًا.

ملاحظة: يتوفر XLOOKUP أيضًا لـ Office 365 لنظام التشغيل Mac و Excel للويب (Excel عبر الإنترنت)

بناء جملة الدالة XLOOKUP

يوجد أدناه بناء جملة الدالة XLOOKUP:

= XLOOKUP (lookup_value، lookup_array، return_array، [if_not_found]، [match_mode]، [search_mode])

إذا كنت قد استخدمت VLOOKUP ، فستلاحظ أن البنية متشابهة تمامًا ، مع بعض الميزات الإضافية الرائعة بالطبع.

لا تقلق إذا كانت البنية والحجة تبدو أكثر من اللازم. أقوم بتغطية هذه ببعض أمثلة XLOOKUP السهلة لاحقًا في هذا الدرس والمقال التعليمي والتي ستجعلها واضحة تمامًا.

يمكن الدالة XLOOKUP سرد 6 وسيطات (3 إلزامية و 3 اختيارية):

lookup_value - القيمة التي تبحث عنها

lookup_array - المصفوفة التي تبحث فيها عن قيمة البحث

return_array - المصفوفة التي تريد جلب القيمة منها وإرجاعها (المقابلة للموضع الذي توجد فيه قيمة البحث)

[if_not_found] - القيمة المراد إرجاعها في حالة عدم العثور على قيمة البحث. في حالة عدم تحديد هذه الوسيطة ، سيتم إرجاع الخطأ # N / A

[match_mode] - هنا يمكنك تحديد نوع المطابقة التي تريدها:

0 - مطابقة تامة ، حيث يجب أن يتطابق lookup_value تمامًا مع القيمة الموجودة في lookup_array. هذا هو الخيار الافتراضي.

-1 - للبحث عن المطابقة التامة ، ولكن إذا تم العثور عليها ، يتم إرجاع العنصر / القيمة الأصغر التالية

1 - يبحث عن المطابقة التامة ، ولكن إذا تم العثور عليها ، يتم عرض العنصر / القيمة الأكبر التالية

2 - لإجراء المطابقة الجزئية باستخدام أحرف البدل (* أو ~)

[search_mode] - هنا تحدد كيفية قيام وظيفة XLOOKUP بالبحث في lookup_array

1 - هذا هو الخيار الافتراضي حيث تبدأ الوظيفة في البحث عن lookup_value من الأعلى (العنصر الأول) إلى الأسفل (العنصر الأخير) في lookup_array

-1 - يقوم بالبحث من الأسفل إلى الأعلى. مفيد عندما تريد البحث عن آخر قيمة مطابقة في lookup_array

2 - يقوم بإجراء بحث ثنائي حيث يلزم فرز البيانات بترتيب تصاعدي. إذا لم يتم الفرز ، فقد يؤدي ذلك إلى حدوث خطأ أو نتائج خاطئة

-2 - يقوم بإجراء بحث ثنائي حيث يلزم فرز البيانات بترتيب تنازلي. إذا لم يتم الفرز ، فقد يؤدي ذلك إلى حدوث خطأ أو نتائج خاطئة

أمثلة على الدالة XLOOKUP

الآن دعنا نصل إلى الجزء المثير للاهتمام فى هذا المقال - بعض أمثلة XLOOKUP العملية.

ستساعدك هذه الأمثلة على فهم كيفية عمل الدالة XLOOKUP بشكل أفضل ، وكيف يختلف عن VLOOKUP و INDEX / MATCH وبعض التحسينات والقيود الخاصة بهذه الوظيفة.

المثال الاول : إحضار قيمة بحث

افترض أن لديك مجموعة البيانات التالية وتريد إحضار النتيجة الرياضية لـ Greg (قيمة البحث).

في الصيغة أعلاه ، استخدمت للتو الوسيطات الإلزامية حيث تبحث عن الاسم (من أعلى إلى أسفل) ، وتجد تطابقًا تامًا ، وتُرجع القيمة المقابلة من B2: B15.

إحدى الفوائد الفورية لوجود lookup_array و return_array كوسيطتين منفصلتين تعني أنه يمكنك الآن النظر إلى اليسار. كان لدى VLOOKUP هذا القيد حيث يمكنك فقط البحث والعثور على قيمة على اليمين. ولكن مع XLOOKUP ، ذهب هذا القيد.

هنا مثال. لدي نفس مجموعة البيانات ، حيث يوجد الاسم على اليمين والمرتجع على اليسار.

فيما يلي الصيغة التي يمكنني استخدامها للحصول على درجة Greg في الرياضيات (مما يعني النظر إلى يسار lookup_value)

يحل XLOOKUP مشكلة رئيسية أخرى - في حالة إدراج عمود جديد ، أو نقل الأعمدة من مكان لآخر ، فستظل البيانات الناتجة صحيحة. من المحتمل أن تنكسر VLOOKUP أو تعطي نتيجة غير صحيحة في مثل هذه الحالات حيث يتم تشفير قيمة فهرس العمود في معظم الأوقات.

المثال الثانى : بحث وجلب سجل كامل

لنأخذ نفس البيانات كمثال.

في هذه الحالة ، لا أريد مجرد جلب نتيجة جريج في الرياضيات. أريد الحصول على الدرجات في جميع المواد.

في هذه الحالة ، يمكنني استخدام الصيغة التالية:

= XLOOKUP (F2، A2: A15، B2: D15(

تستخدم الصيغة أعلاه نطاق return_array أكبر من عمود (B2: D15). لذلك عندما يتم العثور على قيمة البحث في A2: A15 ، تُرجع الصيغة الصف بأكمله من return_array.

أيضًا ، لا يمكنك حذف الخلايا التي هي جزء من المصفوفة التي تم ملؤها تلقائيًا فقط. في هذا المثال ، لا يمكنك حذف H2 أو I2. إذا حاولت ، فلن يحدث شيء. إذا حددت هذه الخلايا ، ستتحول الصيغة في شريط الصيغة إلى اللون الرمادي (للإشارة إلى أنه لا يمكن تغييرها)

يمكنك حذف الصيغة في الخلية G2 (حيث أدخلناها في الأصل) ، وسوف تحذف النتيجة بأكملها.

يعد هذا تحسينًا مفيدًا كما هو الحال سابقًا مع VLOOKUP ، سيتعين عليك تحديد رقم العمود بشكل منفصل لكل صيغة.

موضوعات ذات صلة

شرح الدالة INDEX - Match

اهم 10 دوال فى الاكسيل

شرح الدالة INT