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

ما هي وظيفة Xlookup في Excel ؟ كيفية استخدام Xlookup في إكسيل ؟ دعونا نكتشف ذلك مع LuckyTemplates.com!

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

تعتبر هذه الوظيفة مفيدة بشكل خاص عندما يتعين عليك معالجة الكثير من البيانات في جدول بيانات Excel. في Excel، لديك عدة أنواع من وظائف LOOKUP. XLOOKUP هو واحد منهم. إذن ما هي وظيفة XLOOKUP وما هي صيغة XLOOKUP وكيفية استخدامها؟ دعونا نكتشف ذلك مع LuckyTemplates.com!

أشياء يجب معرفتها حول وظيفة XLOOKUP في Excel

• ما هو إكسلوكوب؟
  • كيفية الوصول إلى وظيفة XLOOKUP
  • XLOOKUP بناء جملة الدالة
• مزايا وعيوب وظيفة XLOOKUP في Excel
  • مزايا وظيفة XLOOKUP
  • عيوب الدالة XLOOKUP
• كيفية استخدام وظيفة XLOOKUP
  • لا توجد أرقام فهرس أعمدة إضافية
  • المطابقة التامة بشكل افتراضي
  • يمكن العثور على XLOOKUP على اليسار
  • استخدم XLOOKUP للبحث عن النطاقات
  • يستبدل XLOOKUP وظيفة HLOOKUP
  • يمكن عرض XLOOKUP من الأسفل إلى الأعلى

ما هي وظيفة XLOOKUP في Excel؟

في الأساس، يحل XLOOKUP محل الوظائف القديمة مثل HLOOKUP وVLOOKUP وLOOKUP. وهو يدعم البحث عن البيانات عموديا وأفقيا.

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

هناك العديد من الطرق لتحقيق أقصى استفادة من وظيفة XLOOKUP.

كيفية الوصول إلى وظيفة XLOOKUP

حاليًا، XLOOKUP متاح فقط لمستخدمي برنامج Insiders. يمكن لأي شخص ينضم إلى برنامج Insider الوصول إلى أحدث ميزات Excel عندما تصبح متاحة. ستقوم Microsoft قريبًا بطرح هذه الوظيفة لجميع مستخدمي Office 365. لسوء الحظ، XLOOKUP غير متاح للمستخدمين الذين يستخدمون إصدارات Office 2010 و2013 و2016 و2019.

إذا كنت لا تستخدم مجموعة Microsoft 365 على جهاز الكمبيوتر الخاص بك، فقد تحتاج إلى الترقية إلى Microsoft 365 لتتمكن من الوصول إلى وظيفة XLOOKUP. وإذا كنت تستخدم Microsoft 365، فسترى الخيار ممكّنًا في Excel. يتوفر XLOOKUP أيضًا على Office 365 Online.

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

يشبه بناء جملة الدالة XLOOKUP بناء جملة الدالتين VLOOKUP وHLOOKUP. إذا كنت قد استخدمتها من قبل، فستجد أن استخدام XLOOKUP أكثر ملاءمة. فيما يلي بناء جملة الدالة XLOOKUP في Excel:

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

تدعم الدالة XLOOKUP ما يصل إلى 6 وسيطات، وإليك قيمها.

1. lookup_value (مطلوب): القيمة التي تريد البحث عنها.

2. lookup_array (مطلوب): المصفوفة التي تريد البحث فيها عن قيمة البحث.

3. return_array (مطلوب): المصفوفة من المكان الذي تريد جلب القيم وإرجاعها عند العثور على قيمة البحث.

4. [if_not_found] (اختياري): في حالة عدم العثور على أي تطابق، تكون هذه هي القيمة التي يتم إرجاعها.

5. [match_mode] (اختياري): تتيح لك هذه الوسيطة تحديد نوع المطابقة الذي تبحث عنه. هناك قيم مختلفة لتحديدها:

• 0 - يبحث عن تطابق تام ويجب أن تتطابق القيمة مع القيمة الموجودة في lookup_array تمامًا. يتم أيضًا تعيين هذه القيمة كقيمة افتراضية عندما لا يتم ذكرها.
• -1 - يبحث عن تطابق تام، وبمجرد العثور عليه، سيعود إلى القيمة الأصغر التالية.
• 1 - يبحث عن تطابق تام وعند العثور عليه يُرجع القيمة الأكبر التالية.
• 2 - إجراء مطابقة جزئية باستخدام أحرف البدل حيث *, ? و ~ له معنى خاص.

6. [وضع_البحث] (اختياري): يُستخدم لتحديد وضع بحث XLOOKUP في lookup_array. هناك قيم مختلفة لتحديد نفسها:

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

مزايا وعيوب وظيفة XLOOKUP في Excel

لا تزال XLOOKUP وظيفة مفيدة مقارنة بـ VLOOKUP وINDEX/ATCH. ولكن لديها أيضا عيوبها.

مزايا وظيفة XLOOKUP

• يعمل الفك عموديًا وأفقيًا.
• هناك حاجة إلى 3 وسيطات فقط، بدلاً من 4 كما هو الحال في VLOOKUP وINDEX MATCH.
• الافتراضي دائمًا هو المطابقة التامة.
• يمكن إجراء عمليات البحث الجزئية باستخدام أحرف البدل.
• يمكن إجراء عمليات البحث بترتيب تنازلي.
• استخدم دالة واحدة بدلاً من اثنتين في INDEX MATCH.

عيوب الدالة XLOOKUP

• قد تبدو الوسائط الاختيارية معقدة بالنسبة للمبتدئين.
• يمكن أن يستغرق الأمر وقتًا أطول عند تحديد نطاقين ووجود عدد كبير جدًا من الخلايا في جدول البيانات.
• إرجاع خطأ عند البحث عن مصفوفة ليست بنفس الطول وإرجاعها.
• تذكر كلاً من نطاقات البحث والإرجاع.

فوائد استخدام وظيفة XLOOKUP Excel

• يجعل XLOOKUP الصيغ الأكثر استخدامًا في Excel أكثر بساطة وأقل عرضة للخطأ . كل ما عليك فعله هو كتابة =XLOOKUP (البيانات المراد البحث عنها، القائمة، قائمة النتائج) وستحصل على الإجابة أو #N/A إذا لم يتم العثور على القيمة.
• نتائج البحث الافتراضية دقيقة . أحد عيوب VLOOKUP هو أنه يجب عليك ذكر FALSE كمعلمة أخيرة للحصول على النتيجة الصحيحة. يعمل XLOOKUP على إصلاح ذلك عن طريق إجراء تطابق تام بشكل افتراضي. يمكنك استخدام معلمة وضع المطابقة لتغيير سلوك البحث إذا أردت ذلك.
• تدعم المعلمة الرابعة الحالة التي لم يتم العثور فيها على القيمة . في معظم مواقف الأعمال، ستضطر إلى "إحاطة" صيغة البحث باستخدام IFERROR أو IFNA لإزالة الأخطاء. يوفر XLOOKUP المعلمة الرابعة حتى تتمكن من معرفة النتيجة الافتراضية المطلوبة إذا لم يتم العثور على القيمة.
• يوفر XLOOKUP معلمات التحديد للعثور على المواقف الخاصة . يمكنك البحث من أعلى أو أسفل، بواسطة أحرف خاصة، وخيارات أسرع للعثور على البيانات التي تمت تصفيتها.

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

ألقِ نظرة على المثال أدناه لفهم كيفية عمل XLOOKUP. في هذا المثال، نحتاج إلى إرجاع الجزء من العمود F لكل معرف في العمود A.

هذا مثال يبحث عن نتائج دقيقة، لكن وظيفة XLOOKUP تتطلب ثلاثة أنواع من المعلومات فقط.

تُظهر الصورة أدناه XLOOKUP مع 5 وسيطات، لكن الوسائط الثلاث الأولى مطلوبة للحصول على النتائج الصحيحة. لذلك دعونا نركز عليهم:

• Lookup_value : قيمة البحث
• Lookup_array : نطاق البحث
• Return_array : النطاق الذي يحتوي على القيمة المراد إرجاعها

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

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

الآن دعنا نستكشف بعض مزايا XLOOKUP مقارنة بـ VLOOKUP.

لا توجد أرقام فهرس أعمدة إضافية

الوسيطة الثالثة لـ VLOOKUP هي تحديد عدد أعمدة المعلومات المطلوب إرجاعها من نطاق الجدول. لم تعد هذه مشكلة في XLOOKUP لأن الوظيفة تسمح للمستخدم بتحديد نطاق الإرجاع. (العمود F في هذا المثال).

ولا تنس أن XLOOKUP يمكنه رؤية البيانات المتبقية للخلية المحددة، على عكس VLOOKUP.

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

المطابقة التامة بشكل افتراضي

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

يمكن العثور على XLOOKUP على اليسار

تمنح القدرة على تحديد نطاقات البحث XLOOKUP مرونة أكبر من VLOOKUP. مع XLOOKUP، لا يهم ترتيب الأعمدة في الجدول.

يتم تحديد VLOOKUP من خلال البحث في العمود الموجود في أقصى يسار الجدول ثم العودة من عدد محدد من الأعمدة على اليمين.

في المثال أدناه، نحتاج إلى البحث عن المعرف (العمود E) وإرجاع اسم الشخص (العمود D).

استخدم الصيغة أدناه:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

استخدم XLOOKUP للبحث عن النطاقات

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

هذه المرة لا نبحث عن قيمة محددة ونحتاج إلى معرفة النطاق الذي تقع فيه القيم في العمود B في العمود E لتحديد الخصم.

يحتوي XLOOKUP على وسيطة رابعة اختيارية (تذكر أن إعداداتها الافتراضية هي المطابقة المطلقة) تسمى match_mode.

يمكنك أن ترى أن XLOOKUP يمكنه العثور على التطابقات التقريبية بشكل أفضل من VLOOKUP.

يوجد خيار للعثور على أقرب تطابق أقل من (-1) أو أقرب تطابق أكبر من (1) قيمة البحث. هناك أيضًا خيار لاستخدام أحرف البدل (2)، مثل ؟ أو *. لا يتم تمكين هذا الإعداد افتراضيًا كما هو الحال مع VLOOKUP.

ترجع الصيغة في هذا المثال أقرب قيمة أقل من قيمة البحث إذا لم يتم العثور على تطابق تام:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

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

ميزة أخرى لوظيفة XLOOKUP هي أنها لا تتطلب نطاق بحث بترتيب تصاعدي مثل VLOOKUP.

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

تقوم الصيغة بتصحيح الخطأ على الفور. ليس هناك مشكلة في وجود 0 في نهاية جدول النطاق.

يستبدل XLOOKUP وظيفة HLOOKUP

كما ذكرنا سابقًا، يمكن أن تحل وظيفة XLOOKUP أيضًا محل وظيفة HLOOKUP. وظيفة واحدة يمكن أن تحل محل وظيفتين، ما الذي يمكن أن يكون أفضل؟

تبحث الدالة HLOOKUP بشكل أفقي، وتُستخدم للبحث حسب الصف.

هذه الوظيفة ليست مشهورة مثل VLOOKUP ولكنها مفيدة في الحالات التي يكون فيها العنوان في العمود A والبيانات موجودة في الصفين 4 و5 مثل المثال أدناه.

يمكن لـ XLOOKUP البحث في كلا الاتجاهين، أسفل الأعمدة وعلى طول الصفوف.

في هذا المثال، يتم استخدام صيغة لإرجاع قيمة المبيعات المرتبطة بالاسم الموجود في العمود A2. يبحث في الصف 4 عن الاسم ويعيد القيمة من الصف 5:

=XLOOKUP(A2,B4:E4,B5:E5)

يمكن عرض XLOOKUP من الأسفل إلى الأعلى

عادةً ما تحتاج إلى البحث من الأعلى إلى الأسفل في القائمة للعثور على التواجد الأول للقيمة. يحتوي XLOOKUP على وسيطة خامسة تسمى search_mode. تسمح هذه الوسيطة بإعادة توجيه البحث للبدء من أسفل القائمة للعثور على آخر تواجد لقيمة ما.

في المثال أدناه، نحتاج إلى العثور على مستوى المخزون لكل منتج في العمود أ.

تم ترتيب جدول البحث حسب التاريخ. نريد إرجاع مستوى المخزون من الفحص الأخير (آخر تواجد لمعرف المنتج).

توفر الوسيطة الخامسة للدالة XLOOKUP أربعة خيارات. سنستخدم هنا خيار البحث من الأخير إلى الأول .

الصيغة المستخدمة في هذا المثال:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

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

الدالة XLOOKUP هي الوريث المتوقع لكل من الدالتين VLOOKUP وHLOOKUP. تم استخدام سلسلة من الأمثلة في هذه المقالة لتوضيح مزايا الدالة XLOOKUP.

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

ومع ذلك، يمكنك الوصول إلى XLOOKUP من Office 365 Online. لا يزال XLOOKUP قيد التطوير، لذا سيستغرق مستخدمو Excel بعض الوقت لاعتماده.

أتمنى لك النجاح!

الفرق بين XLOOKUP وVLOOKUP

لماذا يعتبر XLOOKUP أفضل من VLOOKUP؟

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

• XLOOKUP لا يحد من نطاق البيانات : XLOOKUP أكثر مرونة من VLOOKUP. يمكن لـ VLOOKUP البحث عن القيم الموجودة فقط في العمود الموجود في أقصى يسار الجدول وإرجاع القيم من العمود المقابل على اليمين. وفي الوقت نفسه، يتطلب XLOOKUP خطوات أبسط فقط ويمكنه إرجاع قيم لأي عمود في أي من الاتجاهين.
• النتيجة الافتراضية لـ XLOOKUP هي القيمة الأكثر دقة .
• بشكل افتراضي، تقوم الدالة VLOOKUP في Excel دائمًا بالبحث عن نتائج تقريبية . ومع ذلك، إذا كنت تريد نتائج دقيقة، فيجب عليك إدخال FALSE في وسيطة range_lookup.
• تبحث الدالة XLOOKUP عن قيمة بحث في نطاق معين وتقوم دائمًا بتعيين القيمة الافتراضية بشكل افتراضي. إذا لم تتمكن من العثور على وسيطة، يمكنك تحرير الوسيطة للعثور على تطابق تقريبي أصغر أو أكبر من قيمة البحث.

أسباب أخرى:

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