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

مقدمة عن دالة XLOOKUP في الإكسل

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

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

في هذا الدليل التفصيلي خطوة بخطوة، ستتعلم:

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

ما هي دالة XLOOKUP؟

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

تمكنك هذه المرونة من بناء جداول بيانات موثوقة بدون الحاجة لإعادة ترتيب بياناتك.

المهام الشائعة التي تحلها XLOOKUP

• البحث عن معلومات الموظف باستخدام رقم الهوية
• العثور على أسعار المنتجات من قائمة المنتجات
• إرجاع إجماليات المبيعات بناءً على منتج معين
• مطابقة أسماء العملاء مع سجلات الطلبات

بنية صيغة XLOOKUP

الصيغة العامة لدالة XLOOKUP هي:

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

وفيما يلي شرح لكل عنصر:

• lookup_value: القيمة التي تريد العثور عليها.
• lookup_array: النطاق الذي سيبحث فيه الإكسل عن القيمة.
• return_array: النطاق الذي يحتوي على النتيجة التي تريد إرجاعها.
• if_not_found: قيمة اختيارية تعاد إذا لم يتم العثور على تطابق.
• match_mode: إعداد اختياري يتحكم في كيفية مطابقة القيم.
• search_mode: إعداد اختياري يحدد اتجاه البحث.

في معظم الحالات اليومية، ستحتاج فقط إلى الثلاثة عناصر الأولى.

خطوة بخطوة: صيغة XLOOKUP الأولى لك

لنبدأ بمثال بسيط. تخيل أن لديك قائمة بأرقام هوية الموظفين والأسماء، وتريد أن يعيد الإكسل قسم الموظف.

مجموعة بيانات المثال

• العمود أ: رقم الهوية
• العمود ب: اسم الموظف
• العمود ج: القسم

تريد إدخال رقم هوية موظف وإرجاع القسم تلقائياً.

الخطوة 1: تحديد قيمة البحث

افترض أن الخلية E2 تحتوي على رقم الهوية الذي تريد البحث عنه.

الخطوة 2: كتابة صيغة XLOOKUP

أدخل الصيغة التالية:

=XLOOKUP(E2, A2:A100, C2:C100)

الخطوة 3: اضغط انتر

يبحث الإكسل في النطاق A2:A100 عن رقم الهوية في E2 ويعيد القسم المطابق من C2:C100.

هذا هو النمط الأساسي الذي ستستخدمه في معظم الجداول.

مثال: البحث عن سعر المنتج

لنلق نظرة على سيناريو عملي للأعمال.

أنت تدير قائمة منتجات بالهيكل التالي:

• العمود أ: رقم المنتج
• العمود ب: اسم المنتج
• العمود ج: السعر

إذا قام المستخدم بإدخال رقم منتج في الخلية F2، تريد أن يعيد الإكسل سعر المنتج.

الصيغة

=XLOOKUP(F2, A2:A200, C2:C200)

التوضيح:

• F2 هي القيمة التي تبحث عنها.
• A2:A200 هو عمود رقم المنتج.
• C2:C200 يحتوي على الأسعار لإرجاعها.

عندما يتم إدخال رقم المنتج، يعيد الإكسل السعر الصحيح فوراً.

التعامل مع القيم المفقودة باستخدام IF_NOT_FOUND

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

يمكنك التحكم بهذا باستخدام العنصر الاختياري if_not_found.

صيغة المثال

=XLOOKUP(F2, A2:A200, C2:C200, "المنتج غير موجود")

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

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

استخدام XLOOKUP للبحث باتجاه اليسار

أحد قيود VLOOKUP هو أنه يمكن فقط البحث إلى اليمين. XLOOKUP تزيل هذا القيد.

على سبيل المثال، تخيل أن بياناتك تبدو هكذا:

• العمود أ: القسم
• العمود ب: رقم هوية الموظف
• العمود ج: اسم الموظف

إذا كنت تريد البحث حسب رقم هوية الموظف وإعادة القسم، تعمل XLOOKUP بسهولة.

صيغة المثال

=XLOOKUP(E2, B2:B100, A2:A100)

حتى وإن كان عمود النتائج يقع إلى يسار عمود البحث، تعمل الصيغة بشكل صحيح.

المطابقة الدقيقة مقابل المطابقة التقريبية

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

خيارات وضع المطابقة

• 0 – مطابقة دقيقة (افتراضي)
• -1 – مطابقة دقيقة أو القيمة الأصغر التالية
• 1 – مطابقة دقيقة أو القيمة الأكبر التالية
• 2 – مطابقة باستخدام الرموز البرية

مثال مع وضع المطابقة

=XLOOKUP(F2, A2:A50, B2:B50, "غير موجود", 0)

تفرض هذه الصيغة مطابقة بحث دقيقة.

البحث من الأسفل إلى الأعلى

إحدى القدرات المفيدة الأخرى لـ XLOOKUP هي التحكم في اتجاه البحث.

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

بدلاً من البحث من الأعلى، يمكنك البحث من الأسفل.

صيغة المثال

=XLOOKUP(F2, A2:A200, C2:C200, "غير موجود", 0, -1)

العنصر الأخير -1 يخبر الإكسل بالبحث من الصف الأخير صعودًا.

هذا مفيد بشكل خاص عند العمل مع السجلات أو المعاملات أو البيانات التاريخية.

استخدام XLOOKUP مع عدة أعمدة

يمكن لـ XLOOKUP إعادة عدة أعمدة مرة واحدة إذا كانت مصفوفة الإرجاع تحتوي على عدة أعمدة.

مجموعة بيانات المثال

• العمود أ: رقم المنتج
• العمود ب: اسم المنتج
• العمود ج: السعر
• العمود د: الفئة

صيغة

=XLOOKUP(F2, A2:A100, B2:D100)

تعيد هذه الصيغة اسم المنتج والسعر والفئة في الخلايا المجاورة.

هذه الميزة مفيدة بشكل خاص للوحات التحكم وأوراق التقارير.

الأخطاء الشائعة في XLOOKUP

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

استخدام نطاقات غير متوافقة

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

أنواع البيانات غير الصحيحة

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

تأكد دائمًا من أن أنواع البيانات لديك متناسقة.

المراجع العمودية الكاملة غير الضرورية

استخدام النطاقات مثل A:A يمكن أن يبطئ جداول البيانات الكبيرة. بدلاً من ذلك، استخدم نطاقات محددة مثل A2:A1000.

نصائح عملية لاستخدام XLOOKUP بكفاءة

• استخدم الجداول المنظمة لجعل الصيغ أسهل للقراءة.
• قم بتسمية النطاقات الهامة لتحصل على صيغ أوضح.
• اجمع XLOOKUP مع دوال مثل IF أو SUM للحصول على منطق متقدم.
• اختبر قيم البحث لضمان توافقها مع تنسيق مجموعة البيانات.
• أضف رسالة if_not_found لإثراء تجربة المستخدم.

XLOOKUP مقابل VLOOKUP

العديد من مستخدمي الإكسل معتادون على VLOOKUP، لكن XLOOKUP توفر عدة مزايا.

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

بفضل هذه التحسينات، يفضل العديد من المحترفين الآن XLOOKUP للجداول الجديدة.

أفكار ختامية

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

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

إذا كنت تعمل بانتظام مع صيغ البحث، فإن استثمار قليل من الوقت في إتقان XLOOKUP سيوفر لك ساعات من البحث اليدوي وحل المشاكل في الجداول.