كيفية استخدام XLookup في Excel

دائمًا ما يكون العمل مع مجموعات البيانات الكبيرة أمرًا صعبًا. يعد Excel أحد أقوى الأدوات التي يمكنك استخدامها لمعالجة مجموعات كبيرة من المعلومات وتنسيقها وتحليلها.

توجد العديد من الوظائف المعروفة لمساعدتك في البحث عن تطابقات محددة ضمن مجموعة بيانات ، مثل VLookup.

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

يمكن أن تؤدي معرفة كيفية استخدامه بفعالية إلى تقليل الوقت الذي تقضيه في محاولة تحليل البيانات في Excel بشكل كبير.

ماذا يفعل XLookup في Excel؟

فوائد XLookup في Excel

XLookup الوسيطات المطلوبة

XLookup الوسائط الاختيارية

كيفية استخدام XLookup في Excel

أفضل الممارسات لـ XLookup في Excel

ماذا يفعل XLookup في Excel؟

تتمثل الوظيفة الأساسية لـ XLookup في القدرة على البحث عن قيم محددة في نطاق أو مجموعة من البيانات. عند تشغيل الدالة ، يتم إرجاع قيمة مقابلة من نطاق أو مصفوفة أخرى.

في النهاية ، إنها أداة بحث لمجموعات بيانات Excel الخاصة بك.

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

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

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

تحقق من هذا البرنامج التعليمي XLookup Excel لرؤيته أثناء العمل:

فوائد XLookup في Excel

تتمثل الفائدة الرئيسية لبرنامج XLookup في Excel في توفير الوقت الذي يوفره. يمكنك تحقيق نتيجة البحث دون التلاعب في تحديد موقع البيانات ، على سبيل المثال.

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

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

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

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

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

XLookup الوسيطات المطلوبة

تتطلب وظيفة XLookup ثلاث وسيطات:

• Lookup_value: هذه هي القيمة التي تبحث عنها في المصفوفة.
• Lookup_array: هذا هو نطاق الخلايا حيث تريد عرض القيمة المرجعة.
• Return_array: هذا هو نطاق الخلايا حيث تريد أن تقوم الوظيفة بالبحث عن القيمة.

لذا فإن إصدارًا بسيطًا من XLookup سيبدو كما يلي:

= XLOOKUP (lookup_value ، lookup_array ، return_array)

XLookup الوسائط الاختيارية

تحتوي وظيفة XLookup أيضًا على العديد من الوسائط الاختيارية التي يمكنك استخدامها لسيناريوهات أكثر تعقيدًا أو لتضييق نطاق البحث:

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

مع تضمين الوسائط الاختيارية ، تبدو وظيفة XLookup كما يلي:

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

كيفية استخدام XLookup في Excel

1. افتح Excel وورقة البيانات التي تريد استخدام وظيفة XLookup عليها.

2. حدد الخلية حيث تريد وضع صيغة XLookup Excel.

3. اكتب "=" في تلك الخلية ثم اكتب "XLookup". انقر فوق خيار "XLookup" الذي يظهر في القائمة المنسدلة.

4. سيتم إنشاء قوس فتح تلقائيًا. بعد قوس الفتح ، أدخل الوسيطات المطلوبة بالترتيب الصحيح: lookup_value و lookup_array و return_array.

هذا يعني تحديد نطاق الخلايا لكل وسيطة ووضع فاصلة قبل الانتقال إلى الوسيطة التالية.

5. إذا كنت تستخدم أي وسيطات اختيارية ، أدخلها بعد الوسيطات المطلوبة.

6. عند اكتمال وسيطات XLookup الخاصة بك ، قم بإضافة قوس إغلاق قبل الضغط على Enter.

7. يجب عرض نتائج XLookup في الخلية التي أدخلت فيها الوظيفة.

في مثال Excel XLookup هذا ، استخدمنا XLookup لمعرفة الدرجة لطالب معين. لذلك ، كان lookup_value اسم الطالب ("Ruben Pugh").

كانت lookup_array قائمة بأسماء الطلاب ضمن العمود A. وأخيرًا ، كان lookup_return عبارة عن قائمة تقديرات الطلاب ضمن العمود C. باستخدام هذه الصيغة ، أعادت الدالة قيمة تقدير الطالب: C-.

ولكن كيف تستخدم XLookup مع الوسائط الاختيارية؟

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

هنا ، نطلب من Excel إعادة الرسالة "غير موجود" إذا لم يتمكن من استرداد القيمة ، مشيرًا إلى أن الطالب "سميث" غير موجود في هذه القائمة:

الآن ، لنفترض أننا نريد تحديد ما إذا كان معدل حضور أي طالب 70٪. لكننا نريد أيضًا معرفة أقرب معدل حضور تالي إذا لم يكن 70٪ موجودًا في مجموعة البيانات.

سنستخدم القيمة "1" ضمن وسيطة match_mode للعثور على العنصر الأكبر التالي إذا لم يكن العنصر 70 موجودًا:

نظرًا لعدم حصول أي طالب على معدل حضور 70٪ ، أعاد XLookup "75" ، وهي القيمة التالية الأكبر.

أفضل الممارسات لـ XLookup في Excel

استخدم المراجع الوصفية

يُفضل استخدام المراجع الوصفية للخلايا التي تستخدمها بدلاً من النطاقات العامة مثل A1: A12.

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

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

استخدم المطابقة التامة حيثما أمكن ذلك

يساعد استخدام القيمة الافتراضية للمطابقة التامة داخل الصيغة (على عكس أحرف البدل) على ضمان عدم حصولك على قيم غير مقصودة في الإرجاع.

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

احتفظ بنطاقات الوسيطة بنفس الحجم

عند تجميع وسيطاتك معًا ، تأكد من استخدام نفس عدد الخلايا لمصفوفة return_array ومصفوفة look_up. إذا لم يكن الأمر كذلك ، فسوف تحصل على خطأ ، وسيرجع Excel فقط #VALUE في الخلية.

اختبر الصيغة الخاصة بك

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

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

ابدء

كطريقة جديدة ومحسّنة لاستخدام وظائف البحث في Excel ، يتفوق XLookup على VLookup الكلاسيكي بطرق متعددة.

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