تشتهر وظيفة Xlookup الخاصة بـ Microsoft Excel لاسترداد قيمة من صف أو عمود معين في مجموعة بيانات. ومع ذلك ، يمكنك أيضًا استخدام هذه الأداة القوية لأداء عمليات البحث في اتجاهين ، وإعادة قيمة عند تقاطع صف وعمود محدد.
إذا كنت تعمل في تطبيق Excel Desktop على جهاز كمبيوتر أو Mac ، فيجب أن تستخدم Excel 2021 أو أحدث (بما في ذلك Excel لـ Microsoft 365) للوصول إلى وظيفة Xlookup. إنه متوفر بسهولة في تتفوق على الويب وتطبيقات Excel Tablet و Mobile.
استخدام Xlookup للبحث في اتجاه واحد في Excel
مفتاح فهم كيفية استخدام Xlookup لأداء عمليات البحث في اتجاهين هو أولاً الحصول على رأسك حول كيفية عمل هذه الوظيفة في سيناريوهات أحادية البعد.
متعلق ب
ننسى Vlookup في Excel: إليكم سبب استخدام Xlookup
خارج مع القديم ، وفي الجديد.
تحتوي وظيفة Xlookup على ست وسيطات:
=XLOOKUP(a,b,c,d,e,f)
أين
- أ (مطلوب) هي قيمة البحث ،
- ب (مطلوب) هو صفيف البحث ،
- ج (مطلوب) هو صفيف العودة ،
- د (اختياري) هو النص المراد إرجاعه إذا كانت قيمة البحث (أ) غير موجود في صفيف البحث (ب) ،
- ه (اختياري) هو وضع المطابقة (0 = المطابقة الدقيقة ؛ -1 = مطابقة دقيقة أو عنصر أصغر التالي ؛ 1 = المطابقة الدقيقة أو العنصر الأكبر التالي ؛ 2 = مطابقة Wildcard) ، و
- و (اختياري) هو وضع البحث (1 = من أعلى إلى أسفل أو إلى اليسار إلى اليمين ؛ -1 من أسفل إلى أعلى أو يمين إلى اليسار ؛ 2 = بحث ثنائي مع صفيف البحث (ب) بالترتيب الصاعد ؛ -2 = البحث الثنائي مع صفيف البحث (ب) في ترتيب تصاعدي.
إذا حجة د يتم حذفه ، إرجاع Excel Zero إذا لم يتم العثور على قيمة البحث في صفيف البحث. إذا حجج ه و و يتم تركها ، يتم تطبيق الخيار الافتراضي (مطابقة دقيقة للوسيطة ه، وبحث من أعلى إلى أسفل أو من اليسار إلى اليمين عن الوسيطة و).
في هذا المثال ، الكتابة:
=XLOOKUP(F2,A2:A100,D2:D100,"Invalid ID",0,1)
في الخلية G2 إرجاع درجة 48.
تأخذ هذه الصيغة المعرف في الخلية F2 (الحجة أ) ، يبحث عنه في الخلايا من A2 إلى A100 (الحجة ب) ، وإرجاع القيمة المقابلة في الخلايا D2 إلى D100 (الوسيطة ج). إذا لم يتم سرد المعرف في الخلية F2 في الخلايا A2 إلى A100 ، فإن البحث يعيد مصطلح “معرف غير صالح” (الوسيطة د). تخبر الحجة الخامسة Excel بإرجاع المطابقة الدقيقة فقط (الوسيطة ه) ، ويضمن السادس أن يبحث البحث من الجزء العلوي من النطاق إلى أسفل النطاق (الوسيطة و).
استخدام Xlookup للبحث في اتجاهين في Excel
على الرغم من أن عمليات البحث في اتجاه واحد مفيد في العديد من السيناريوهات ، إلا أنها لا تسمح لك بتغيير المتغير الذي تبحث عنه. في الواقع ، تُرجع الصيغة أعلاه فقط درجة بناءً على المعرف المحدد ، ولكن ماذا لو أردت رؤية العمر أو الجنس بدلاً من ذلك؟ هذا هو المكان الذي يتم فيه البحث عن البحث في اتجاهين (المعروف أيضًا باسم البحث ثنائي الأبعاد أو المصفوفة).
للقيام بذلك ، تحتاج إلى عش واحد xlookup داخل الآخر:
=XLOOKUP(a¹,b¹,XLOOKUP(a²,b²,c))
أين
- a و ب هل قيمة البحث والمصفوفة للمتغير الأول ،
- A² و B² هل قيمة البحث والمصفوفة للمتغير الثاني ، و
- ج هو صفيف العودة.
يمكنك أيضًا إضافة حجج دو ه، و و لكل Xlookup بنفس الطريقة التي تسير بها عند إجراء عملية بحث أحادية الاتجاه. ومع ذلك ، للحفاظ على الأمور بسيطة ، لقد حذفت هذه من بناء الجملة أعلاه والأمثلة أدناه.
بناءً على هذا ، الكتابة:
=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))
في الخلية G2 إرجاع درجة 48.
هذا لأن Excel يبحث أولاً عن المعرف المحدد (الوسيطة a) في الخلايا A2 إلى A100 (الحجة ب) ، ثم يبحث عن المتغير المحدد (الوسيطة A²) في الخلايا B1 إلى D1 (الحجة B²) ، وإرجاع النتيجة عند تقاطع هاتين البحث في الخلايا B2 إلى D100 (الوسيطة ج).
نتيجة لذلك ، إذا قمت بتغيير المتغير في الخلية G1 إلى “العمر” ، فستحصل على نتيجة 41.
وبالمثل ، إذا قمت بتغييره إلى “الجنس” ، فستحصل على M كنتيجة.
في صيغ Xlookup ثنائية الأبعاد ، لا يهم الطريقة التي تدور حولها Xlookups. في هذا المثال ، يكون البحث المعرف الأول في الصيغة ، ويتم البحث عن رأس العمود. إذا تم تبديل هذه ، فستحصل على نفس النتيجة.
نصيحة للمحترفين: أضف قوائم منسدلة إلى خلايا البحث
الآن بعد اكتمال البحث في اتجاهين ، يمكنك الذهاب خطوة إلى الأمام عن طريق إضافة قائمة منسدلة إلى الخلايا التي تحتوي على قيم البحث لتسريع عملية استرجاع البيانات.
متعلق ب
كيفية إضافة قائمة منسدلة إلى خلية في Excel
يتفوق على الكتابة في نفس الخيارات 200 مرة مختلفة يدويًا.
للقيام بذلك ، حدد إحدى خلايا البحث (في هذه الحالة ، الخلية G1) ، وفي علامة التبويب البيانات على الشريط ، انقر فوق “التحقق من صحة البيانات”.
بعد ذلك ، في حقل السماح من مربع مربع التحقق من صحة البيانات ، حدد “قائمة”. أخيرًا ، قم بتفعيل حقل “المصدر” ، وحدد الخلايا التي تحتوي على القيم التي تريد الظهور في القائمة المنسدلة. في هذه الحالة ، هي رؤوس الأعمدة في الخلايا B1 إلى D1.
عند النقر فوق “موافق” ، سترى زر منسد في الخلية G1 والذي ، عند النقر فوق ، يكشف عن الخيارات المتغيرة.
الآن ، كرر عملية الخلية F2 ، خلية البحث الأخرى.
لاحظ كيف ، في حقل المصدر ، حددت نطاقًا يتجاوز حجم مجموعة البيانات الخاصة بي للسماح بالنمو (تحتوي مجموعة البيانات الخاصة بي على 100 صف ، لكنني مددت المصدر إلى الخلية A1000). تُخبر الفترة (المعروفة أيضًا باسم مشغل Ref Ref في هذا السياق) بعد أن تخبر القولون Excel تقليم أي خلايا فارغة في نهاية نطاق المصدر.
بدلاً من ذلك ، يمكنك استخدام صيغة تحسب عدد الخلايا التي تحتوي على القيم في العمود A وإرجاع هذا العدد من الخيارات في القائمة المنسدلة.
متعلق ب
كيفية إنشاء قائمة منسدلة من عمود من البيانات في Excel
تعتمد الطريقة التي تستخدمها على كيفية تنسيق بياناتك.
الآن ، يمكنك تحديد معرف من القائمة المنسدلة في الخلية F2.
يمكنك أيضًا إجراء عمليات بحث ثنائية الاتجاه في Excel باستخدام الفهرس والمطابقة. يعد هذا الخيار البديل مفيدًا بشكل خاص إذا كنت تستخدم Excel 2019 أو أكبر ، حيث تم تقديم هذه الوظائف إلى البرنامج قبل Xlookup.