مقدمة عن XLOOKUP في Excel
XLOOKUP هي واحدة من أقوى دوال البحث التي تم تقديمها في الإصدارات الحديثة من Microsoft Excel. فهي تحل محل الدوال القديمة مثل VLOOKUP وHLOOKUP وحتى تركيبات INDEX وMATCH. مع بناء جملة أبسط ومرونة أكبر، يسمح لك XLOOKUP بالبحث عن القيم في مجموعة بيانات وإرجاع النتائج المطابقة بسرعة ودقة.
إذا كنت تعمل بانتظام مع جداول البيانات الكبيرة، مثل تقارير المبيعات، قوائم الموظفين، أو جداول المخزون، فإن XLOOKUP يمكنه تبسيط سير العمل بشكل كبير. في هذا الدليل، ستتعلم كيفية استخدام XLOOKUP خطوة بخطوة، وفهم بناء جملته، وتطبيقه على أمثلة من واقع الحياة.
لماذا يعتبر XLOOKUP أفضل من VLOOKUP
قبل الغوص في الصيغ، يساعد فهم سبب تفضيل العديد من المحترفين في Excel لـ XLOOKUP على الدوال القديمة في البحث.
- لا حاجة لعدد فهارس الأعمدة – على عكس VLOOKUP، لا تحتاج إلى عد الأعمدة يدويًا.
- يعمل من اليسار إلى اليمين أو العكس – يمكن لـ XLOOKUP إرجاع القيم من أي اتجاه.
- معالجة الأخطاء المدمجة – يمكنك بسهولة تحديد ما يجب إرجاعه إذا لم يتم العثور على مطابقة.
- خيارات بحث أكثر مرونة – تدعم المطابقة الدقيقة والمطابقة التقريبية والبحث باستخدام الرموز النمطية.
- تعامل مع عمليات البحث الرأسية والأفقية – يقضي على الحاجة لكل من VLOOKUP وHLOOKUP.
تجعل هذه التحسينات XLOOKUP أكثر سهولة في الصيانة وأقل عرضة للأخطاء في جداول البيانات التجارية الحقيقية.
بناء جملة صيغة XLOOKUP
بناء الجملة العام لـ XLOOKUP هو:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
شرح المعاملات
- lookup_value – القيمة التي ترغب في البحث عنها.
- lookup_array – النطاق الذي يبحث فيه Excel عن قيمة البحث.
- return_array – النطاق الذي يحتوي على النتيجة التي تريد إرجاعها.
- if_not_found – رسالة اختيارية في حال لم يتم العثور على القيمة.
- match_mode – يحدد نوع المطابقة (دقيقة، تقريبية، أو باستخدام رموز نمطية).
- search_mode – يتحكم في اتجاه البحث (من الأول إلى الأخير أو من الأخير إلى الأول).
في معظم حالات الاستخدام اليومية، ستحتاج فقط إلى المعاملات الثلاثة الأولى.
مجموعة بيانات نموذجية
تخيل أن لديك جدول موظفين بسيط:
- العمود A: رقم تعريف الموظف
- العمود B: اسم الموظف
- العمود C: القسم
- العمود D: الراتب
تريد البحث عن رقم تعريف موظف وإرجاع اسم الموظف تلقائيًا.
خطوة بخطوة: أول صيغة XLOOKUP لك
الخطوة 1: إعداد قيمة البحث
اختر خلية حيث سيتسنى للمستخدمين إدخال الرقم الذي يريدون البحث عنه. على سبيل المثال:
Cell F2 = رقم تعريف الموظف المراد البحث عنه
الخطوة 2: تحديد العمود الذي يحتوي على القيم
العمود الذي يحتوي على القيمة التي تريد البحث عنها هو مصفوفة البحث. في مثالنا:
A2:A100
يحتوي هذا العمود على أرقام تعريف الموظفين.
الخطوة 3: تحديد عمود النتائج
مصفوفة النتائج هي العمود الذي يحتوي على البيانات التي تريد إرجاعها. لإرجاع اسم الموظف:
B2:B100
الخطوة 4: إدخال صيغة XLOOKUP
في خلية النتائج، اكتب:
=XLOOKUP(F2, A2:A100, B2:B100)
سيتطلع Excel للبحث عن القيمة المدخلة في F2 ضمن النطاق A2:A100. عندما يجد المطابقة، فإنه يرد القيمة المطابقة من B2:B100.
التعامل مع القيم المفقودة
تعد مشكلة شائعة في صيغ البحث هي الحالة التي لا توجد فيها قيمة في مجموعة البيانات. بدلاً من إرجاع خطأ مثل #N/A، يتيح لك XLOOKUP عرض رسالة مخصصة.
صيغة مثال
=XLOOKUP(F2, A2:A100, B2:B100, "الموظف غير موجود")
إذا لم يتمكن Excel من العثور على رقم تعريف الموظف، فسيعرض رسالة “الموظف غير موجود” بدلاً من خطأ.
إرجاع أعمدة متعددة
يمكن أيضًا لـ XLOOKUP إرجاع قيم متعددة دفعة واحدة. على سبيل المثال، إذا كنت ترغب في إرجاع كل من القسم والراتب لموظف، يمكنك توسيع نطاق الإرجاع.
صيغة مثال
=XLOOKUP(F2, A2:A100, C2:D100)
هذه الصيغة ترجع عمودين:
- القسم
- الراتب
سيقوم Excel تلقائيًا بإسقاط النتائج في خلايا مجاورة.
استخدام XLOOKUP للبحث من اليسار إلى اليمين
إحدى القيود في VLOOKUP هو أنه يمكنه البحث فقط من اليسار إلى اليمين. بينما لا يملك XLOOKUP هذا القيد.
على سبيل المثال، إذا كانت ورقة العمل الخاصة بك تسرد أسماء الموظفين قبل أرقام تعريفهم:
- العمود A: اسم الموظف
- العمود B: رقم تعريف الموظف
يمكنك البحث عن رقم تعريف وإرجاع الاسم باستخدام:
=XLOOKUP(E2, B2:B100, A2:A100)
تجعل هذه المرونة تصميم جداول البيانات أسهل بكثير.
استخدام الرموز النمطية في XLOOKUP
يدعم XLOOKUP رموز نمطية لتسهيل البحث.
أكثر الرموز النمطية شيوعًا تشمل:
- * – يمثل عدة أحرف
- ? – يمثل حرفًا واحدًا
مثال
<pإذا كنت ترغب في البحث عن الموظفين الذين تبدأ أسماؤهم بـ "Sam":
=XLOOKUP("Sam*", B2:B100, C2:C100,,2)
يمكن للرمز 2 في معامل نوع المطابقة تمكين مطابقتك برموز نمطية.
البحث من الأخير إلى الأول
أحيانًا تحتاج إلى العثور على الإدخال الأكثر حداثة في مجموعة البيانات. يتيح لك XLOOKUP البحث من الأسفل إلى الأعلى.
مثال الصيغة
=XLOOKUP(F2, A2:A100, D2:D100,,0,-1)
تخبر وضع البحث -1 Excel للبحث من القيمة الأخيرة إلى الأولى.
حالات الاستخدام في العالم الحقيقي
1. تقارير المبيعات
في تقرير المبيعات، قد ترغب في استرداد أسعار المنتجات بناءً على رمز المنتج.
مثال:
=XLOOKUP(A2, Products!A:A, Products!C:C)
تجد هذه الصيغة رمز المنتج وتعيد سعره.
2. إدارة المخزون
XLOOKUP يمكن أن تظهر مستويات المخزون بسرعة عند إدخال رمز المنتج.
=XLOOKUP(E2, A2:A500, D2:D500)
يساعد ذلك فرق المستودعات في مراقبة المخزون المتاح.
3. سجلات الموظفين في قسم الموارد البشرية
غالبًا ما تقوم أقسام الموارد البشرية بتخزين معلومات الموظفين في جداول بيانات كبيرة. يمكن لـ XLOOKUP استرداد:
- أقسام الموظفين
- تواريخ البدء
- الرواتب
- أسماء المديرين
يسمح ذلك لموظفي الموارد البشرية بإنشاء أدوات بحث سريعة عن الموظفين.
أخطاء شائعة يجب تجنبها
1. نطاقات غير متطابقة
يجب أن تحتوي مصفوفة البحث ومصفوفة النتائج عادةً على نفس عدد الصفوف أو الأعمدة. قد تؤدي النطاقات غير المتطابقة إلى نتائج غير صحيحة.
2. معادلة مطابقة غير صحيحة
بشكل افتراضي، يقوم XLOOKUP بإجراء مطابقة دقيقة. إذا كنت بحاجة إلى المطابقة التقريبية أو البحث باستخدام الرموز النمطية، يجب عليك تحديد وضع المطابقة الصحيح.
3. البحث في العمود الخطأ
تأكد من أن مصفوفة البحث تحتوي على القيم التي تبحث عنها. إذا لم يتمكن Excel من العثور على القيمة، فإنه سيتم تشغيل نتيجة “غير موجود”.
نصائح عملية لاستخدام XLOOKUP
- استخدم النطاقات المسماة لتسهيل قراءة الصيغ.
- اجمع بين ذلك مع التحقق من البيانات لإنشاء لوحات معلومات قابلة للبحث.
- استخدم الجداول المنسقة لصيغ ديناميكية تتوسع تلقائيًا.
- أضف رسائل الأخطاء باستخدام معامل
if_not_found.
يمكن أن تجعل هذه التحسينات الصغيرة جداول بياناتك أسهل للصيانة من قبل الفرق.
XLOOKUP مقابل INDEX وMATCH
قبل أن يكون XLOOKUP موجودًا، اعتمد الكثير من المستخدمين المتقدمين على مجموعة INDEX وMATCH للتغلب على قيود VLOOKUP.
مثال:
=INDEX(B2:B100, MATCH(F2, A2:A100, 0))
يبسط XLOOKUP هذه العملية بشكل كبير:
=XLOOKUP(F2, A2:A100, B2:B100)
النتيجة أسهل في القراءة وأبسط في التدقيق وأسرع في البناء.
متى يجب استخدام XLOOKUP
XLOOKUP مثالي عندما:
- تبحث بشكل متكرر عن قيم متطابقة في مجموعات البيانات.
- تريد استبدال صيغ VLOOKUP القديمة.
- تحتاج إلى عمليات بحث مرنة عبر اتجاهات مختلفة.
- تريد معالجة الأخطاء المدمجة.
لكن، تذكر أن XLOOKUP متاح فقط في إصدارات Excel الأحدث مثل Microsoft 365 وExcel 2021 أو الإصدارات اللاحقة.
الخاتمة
XLOOKUP هو واحدة من أكثر دوال Excel حداثة وفائدة للمحترفين الذين يعملون مع البيانات. تصميمه المرن يزيل العديد من القيود الموجودة في صيغ البحث القديمة ويسمح لك بإنشاء جداول بيانات أكثر نظافة وموثوقية.
من خلال فهم بناء الجملة الأساسي وممارسة الأمثلة الواقعية، يمكنك تطبيق XLOOKUP بسرعة على مهام مثل بحث الموظفين، والبحث عن المنتجات، وتقارير المبيعات، وتتبع المخزون. بمجرد أن تتعود على هذه الدالة، فمن المحتمل أن تحل محل معظم صيغ VLOOKUP وHLOOKUP الخاصة بك.
ابدأ بممارسة مجموعات بيانات صغيرة، جرب المعاملات الاختيارية مثل المطابقة باستخدام الرموز النمطية والبحث العكسي، ودمج XLOOKUP تدريجيًا في سير عمل Excel اليومي الخاص بك.