LOADING

Type to search

الدالة xLookUp وتغيير شامل لمستقبل دوال البحث في Excel

الدالة xLookUp وتغيير شامل لمستقبل دوال البحث في Excel

Share

تنبيه: هذا المقال يزيد على 1300 كلمة – والمعلومات الواردة به غير قابلة للتطبيق حاليا، ولكنها لمحة للمستقبل ومتابعة لتطورات اعمال البحث والتطوير في Office 365. إذا كنت من الشغوفين بمتابعة التطورات في هذا المجال فيمكنك قراءة المقال، إن لم تكن مهتما بذلك فيمكنك إكمال ما في يديك والعودة في وقت لاحق في وقت فراغك.

تم الإعلان يوم الثلاثاء الماضي عن تحديث جديد لمستخدمي Office 365 يتضمن إضافة دالة جديدة إلى Excel تحمل اسم xLookUp.

الدالة xLookUp تقع ضمن دوال البحث مثل VLookUp و HLookUp  و Index و Match وغيرها. ويمكنها القيام بعمل الدالتين VLookUp و HLookUp، مع علاج بعض القيود والمشاكل التي لا تستطيع الدوال الحالية التعامل معها بصورة مباشرة.

يمكن تلخيص وظائف الدالة xLookUp ومزاياها كما يلي:

  1. البحث بتحديد الأعمدة على نحو يشبه الدالة SumIFS بدلا من تحديد الجدول وكتابة رقم العمود. مما يعني أن المعادلة لن تتأثر إذا أضيفت أعمدة جديدة في منتصف الجدول، كما تم الاستغناء عن شرط كون القيمة المطلوب البحث عنها LookUp Value في العمود الأول من الجدول.
  2. إمكانية إيجاد نتائج البحث داخل أكثر من عمود مع التمتع بميزة الصفيف المتحرك Dynamic Arrays
  3. إمكانية تحديد اتجاه البحث من بداية الجدول للنهاية أو العكس، مما يسهل عملية الاستعلام عن آخر قيمة مثل الدالة القديمة LookUp.
  4. دعم استخدام أحرف البدل WildCards للاستعلام عن النصوص مع عدم تذكر الهجاء الصحيح.
  5. دعم البحث التقريبي بطرق أكثر (التقريب للأقل أو الأكبر)، في الدوال الحالية يتم إيجاد أقرب قيمة أقل (فقط).

يمكن شرح تركيب الدالة xLookUp كما يلي:

=xLookUp(Lookup Value, Lookup Array, Return Array, Match Type, Search Mode)

حيث أن:

Lookup Value : القيمة المطلوب البحث بدلالتها كما في الدوال VLookUp و HLookUp

LookUp Array: العمود أو الصف المطلوب البحث بداخله، وليس الجدول بالكامل، وبالتالي يمكن أن يكون أي عمود داخل الجدول ولا يشترط أن يكون الأول ولا يشترط اتجاه معين للجدول.

Return Array: العمود أو الصف المطلوب إيجاد القيمة منه، نقوم بتحديد نفس العمود كما في استخدام الدوال SumIFS وأخواتها، ولا نكتب رقمه مثل VLookUp وغيرها.

Match Type: طريقة المطابقة، وتتضمن أربع خيارات متاحة نوضحها كما يلي:

XLookup match type
طريقة المطابقة في xlookup

مع ملاحظة أن تحديد طريقة المطابقة غير إلزامي، وإذا لم يتم تحديد طريقة مطابقة فإن الاختيار الافتراضي هو صفر 0 أو False، وليس 1 أو True  مثل VLookup.

Search Mode : اتجاه البحث، وبه أربعة اختيارات تتميز بأنها تمكننا من البحث حتى دون ترتيب القيم الرقمية تصاعديا أو تنازليا، نوضحها فيما يلي

مع ملاحظة أن تحديد اتجاه البحث غير إلزامي، وإذا لم يتم تحديد اتجاه للبحث فإن الاتجاه الافتراضي هو 1، أي البحث من أعلى الجدول لأسفله مثل VLookUp دون ترتيب. يجب مراعة أنه أيضا تم استخدام الخيارات 2 و -2 حتى لا يقوم لوغاريثم الدالة بالترتيب مما يساهم في توفير الوقت لأداء أفضل، إلا أن التجربة على جداول متوسطة الحجم أثبتت أنه لا يوجد فرق في الأداء وذلك بفضل الدور الذي يلعبه محرك البحث السريع الذي تم تطويره لدوال البحث في 2018 والمتاح بالفعل لمستخدمي Office 365.

وفيما يلي مثال تطبيقي عملي يوضح كيفية استخدام الدالة xLookUp مع ملاحظة أن الجداول باللون الأحمر تحتوي البيانات الأصلية المطلوب البحث بداخلها، والألوان الأخرى لجداول الاستعلام أو البحث. في المثال الأول سنستخدم جدولا بسيطا يضم أوامر الشراء POs وسنقوم بالبحث عن بيانات أمر الشراء باستخدام رقم الأمر، بيانات الجدول تظهر بالصورة التالية:

يظهر جدول الاستعلام باستخدام رقم أمر الشراء كما يلي:

والمطلوب إيجاد كافة بيانات أمر الشراء الموجود تحت عمود Order ID وهو العمود A مثلا، في هذه الحالة سنستخدم الدالة xLookUp في كتابة معادلتين، الأولى لإيجاد تاريخ الأمر، حيث أنه يقع في نطاق سابق لنطاق رقم الأمر، والثانية لإيجاد باقي بيانات الأمر الموجودة بجدول أوامر الشراء السابق. تكون معادلة إيجاد تاريخ أمر الشراء والمطلوب كتابتها تحت عمود Date كما يلي:

=XLOOKUP(A17,Orders[PO’#],Orders[Date])

كما تظهر بالصورة التالية:

نلاحظ استخدام قيمة البحث وهي رقم أمر الشراء لإيجاد قيمة سابقة عليها في الجدول وهي التاريخ، وذلك بدلا من اللجوء لكتابة معادلات معقدة باستخدام Index و Match.

أما المعادلة الثانية، فيمكن استخدامها لإيجاد باقي بيانات أمر الشراء، وذلك بالبحث في أكثر من عمود وإيجاد النتيجة بمعادلة واحدة فقط عبدون استخدام معادلة لكل عمود أو Match أو  Offset كما بالسابق، وذلك باستخدام المعادلة التالية:

=XLOOKUP(A17,Orders[PO’#],Orders[[Supplier Name]:[Payment type]])

كما تظهر بالصورة التالية:

وتظهر النتائج كاملة كما بالجدول التالي:

والصورة التالية توضح النطاقات المحددة في المعادلتين:

وتظهر إحدى الفوائد التي تقدمها الدالة xLookUp في إيجاد تاريخ آخر أمر شراء مسجل بالجدول من المورد Ahmed، وكذلك قيمة هذا الأمر (أو في مثال آخر يمكن بنفس الطريقة إيجاد آخر سعر شراء)، وذلك باستخدام المعادلتين التاليتين (بفرض وجود اسم المورد Ahmed في الخلية A23):

لإيجاد تاريخ الشراء

=XLOOKUP(A23,Orders[Supplier Name],Orders[Date],0,-1)

لإيجاد قيمة أمر الشراء:

=XLOOKUP(A23,Orders[Supplier Name],Orders[Total Amount],0,-1)

حيث تم استخدام الخيار -1 للبحث من أسفل الجدول لأعلاه في المعادلتين

وتظهر المعادلتان في نافذة Function Arguments  كما يلي:

والصورة التالية توضح النطاقات المحددة في المعادلتين:

البحث باستخدام أحرف البدائل Wildcards:

يمكنك استخدام الدالة xLookup للبحث عن الكلمات التي تتذكر هجاءها بالضبط أو إيجاد النتيجة الأولى أو الأخيرة لعدة كلمات متشابهة أو للكلمات ذات بادئة معينة، وذلك بالاستعانة بأحرف البدائل. لاستخدام أحرف البدائل في الدالة xLookup نستخدم الرقم 2 في الخيار Match Type كما يوضح المثال التالي:

في جدول أوامر الشراء الأصلي المشار إليه أعلاه، يمثل العمود Item code رمز الصنف الصادر بشأنه الأمر، ويشمل جزءا خاصا بخط الإنتاج أو المجموعة أو الفئة، توضح البيانات أن لدينا فئتين يرمز إليهما بالرمزين Mo و Ti إذا أردنا الحصول على رقم آخر أمر شراء لكل مجموعة منتجات فإنه يمكننا استخدام الدالة xLookup، وبفرض وجود رمز المجموعة أو الفئة في الخلية A27 وA28 تكون المعادلة كما يلي:

=XLOOKUP(A27&”*”,Orders[Item Code],Orders[PO’#],2,-1)

حيث تم استخدام الحرف البديل * والذي يعني وجوده “أي عدد من الحروف” فيصبح معنى المعادلة كالتالي: “ابحث عن آخر كلمة تبدأ بحروف Mo في عمود رمز الصنف، واكتب رقم أمر الشراء المقابل لها (لاحظ أن الرقم -1 بنهاية المعادلة يعني البحث من أسفل الجدول لأعلاه).

وتظهر المعادلة في نافذة Function Arguments  كما توضح الصورة التالية:

ويمكن توضيح نتيجة المعادلة والنطاقات المحددة كما بالصورة التالية:

البحث التقريبي مثل VLOOKUP:

يمكن استخدام الدالة xLookUp للبحث التقريبي Approximate Match وذلك باستخدام الخيار -1 وهو الخيار المماثل للخيار TRUE في VLookup. فمثلا إذا أردنا تصنيف رواتب الموظفين إلى فئات كما يوضح الجدول التالي:

وأردنا كتابة معادلة توضح فئة كل راتب من الرواتب الموجودة بالجدول التالي:

فإن المعادلة المطلوبة ستكون كما يلي:

=XLOOKUP(F32,Grades[Start from],Grades[Grade],-1)

حيث يشير الرقم -1 إلى استخدام التقريب لأقرب رقم أقل مثل الاختيار True في الدالة VLookup

وتظهر المعادلة في نافذة Function Arguments كما يلي:

وتكون النتائج كما يلي:

البحث التقريبي عكس VLookUP:

يمكن استخدام الدالة xLookUp للبحث التقريبي Approximate Match وذلك باستخدام الخيار 1، والذي يعني التقريب إلى أقرب رقم أكبر (على عكس استخدام True في الدالة VLookup)، فمثلا إذا استخدمنا تصنيفا معينا لوصف درجات الحرارة عن طريق الحدود العليا كما يلي:

حيث أن كل الدرجات أقل من 8 هي شديد البرودة، وكل الدرجات أقل من 15 هي بارد وهكذا.

وكان المطلوب كتابة حالة الطقس بمعرفة درجات الحرارة الموضحة بالجدول التالي

وباعتبار عمود درجات الحرارة يبدأ من الخلية E42

 فإنه يمكن كتابة حالة الطقس بمعرفة درجة الحرارة باستخدام الدالة xLookUp كما يلي

=XLOOKUP(E42,weather[Up to],weather[Grade],1,1)

حيث يشير الرقم 1 قبل الأخير بالمعادلة إلى البحث التقريبي عكس VLookUp كما وضحنا.

وتظهر المعادلة في نافذة Function Arguments كالتالي:

البحث أفقيا مثل HLookup

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

وكان لدينا جدول للبحث باستخدام اليوم كما يلي:

فإنه يمكن استخدام الدالة xLookup لكتابة معادلة للبحث عن عدد الحاضرين تتغير بتغير اليوم الموجود بالخلية المجاورة

وتكون المعادلة المطلوبة كما يلي:

=XLOOKUP(J53,B52:H52,B53:H53)

حيث تم تحديد الصفوف بدلا من الأعمدة، كما تظهر الصورة التالية للنطاقات المستخدمة داخل المعادلة:

المحزن في هذا الأمر أننا لن نرى الدالة xLookUp متاحة لجميع المستخدمين في القريب العاجل، فهي الآن متاحة على نطاق ضيق لمجموعة من مستخدمي Office 365 فقط، ولا ينتظر أن تتاح لكافة مستخدمي Office 365 على المدى القريب، لكن المتوقع أن تكون متاحة لجميع المستخدمين عند وصول الإصدار القادم من حزمة برامج أوفيس للأجهزة الشخصية، وحينها سيتم الحديث عن الجديد في هذا الإصدار والذي يتوقع أن يتضمن الدالة xLookUp بالإضافة إلى دوال الصفيف المتحرك Dynamic Arrays، مما سيحدث نقلة كبرى في تلبية احتياجات المستخدمين، خاصة في ظل التكامل مع أنظمة تخطيط موارد المنشأة ERP وأنظمة الحوسبة السحابية Cloud Computing.

Tags:

You Might also Like