XLOOKUP في Excel: البناء والأمثلة وكيف يتفوّق على VLOOKUP
By Mirko Peters · · Updated
شرح XLOOKUP في Excel: البناء، وكل وسيطة على حدة، وأمثلة للمطابقة التامة ومطابقة أحرف البدل وآخر تطابق، والبحث في عدة أعمدة، والفرق بينه وبين VLOOKUP.
Automatically translated with AI from the original post. Read the English original
تبحث XLOOKUP في Excel عن قيمة في نطاق، وتُرجع العنصر المطابق لها من نطاق آخر: =XLOOKUP(lookup_value, lookup_array, return_array). اكتب رقم الموظف، وحدّد عمود الأرقام، ثم حدّد عمود الأسماء، فتحصل على الاسم. هذا المقال موجّه لمستخدمي Excel الذين ما زالوا يكتبون VLOOKUP من العادة، أو الذين رأوا XLOOKUP للتو في ملف أحد زملائهم. ستجد فيه بناء الدالة، والوسائط الست، وأمثلة محلولة للحالات التي تظهر في العمل، والمقارنة مع VLOOKUP، وقائمة بالأخطاء التي قد تواجهها.
النقاط الرئيسية
- تأخذ XLOOKUP قيمة بحث، ونطاق بحث، ونطاق إرجاع: =XLOOKUP(lookup_value, lookup_array, return_array). تبحث عن أول تطابق وتُرجع العنصر المقابل له من نطاق الإرجاع.
- يمكنها البحث إلى يسار عمود البحث، ويكون التطابق التام هو الافتراضي، وهما أمران لا تستطيع VLOOKUP فعلهما، أو لا تفعلهما افتراضياً.
- الوسيطة الرابعة if_not_found تستبدل الخطأ #N/A بنص تختاره. وبدونها، تُرجع عملية البحث التي لا تجد تطابقاً الخطأ #N/A.
- تفعّل القيمة 2 في match_mode أحرف البدل، والقيمتان -1 و1 تبحثان عن العنصر الأصغر أو الأكبر التالي، والقيمة -1 في search_mode تبحث من آخر عنصر إلى أوله.
- تذكر صفحة Microsoft أن XLOOKUP متاحة في Microsoft 365 وإصداري 2021 و2024، وأن ملاحظاتها حول Excel 2016 وExcel 2019 متعارضة، لذا اختبر الملفات المشتركة قبل الاعتماد عليها.
تتبع الصيغة والخيارات الواردة أدناه صفحة Microsoft عن دالة XLOOKUP. وحيثما لا يرد نمط ما في تلك الصفحة، يذكر النص ذلك. وهي دالة في Excel، وهي الدالة التي تقترحها Microsoft نفسها خياراً أفضل عندما تفتح صفحة المساعدة الخاصة بـ VLOOKUP.
البناء والوسائط الست
تبدو الصيغة الكاملة هكذا:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
الوسائط الثلاث الأولى إلزامية، والوسائط الثلاث الأخيرة اختيارية.
lookup_valueهي القيمة التي تبحث عنها، مثل رقم في الخلية D2. إذا حذفتها، تُرجع XLOOKUP الخلايا الفارغة التي تجدها في lookup_array.lookup_arrayهو النطاق أو المصفوفة المراد البحث فيها، مثل A2:A100.return_arrayهو النطاق أو المصفوفة المراد الإرجاع منها، مثل B2:B100.if_not_foundهو النص الذي يظهر عند عدم وجود تطابق صالح. وإذا حذفته، تحصل على الخطأ #N/A.match_modeيحدّد طريقة المطابقة.0هي المطابقة التامة وهي الافتراضية.-1تعني مطابقة تامة أو، إذا لم توجد، العنصر الأصغر التالي.1تعني مطابقة تامة أو العنصر الأكبر التالي.2هي مطابقة بأحرف البدل، حيث يكون للرموز*و?و~معنى خاص.search_modeيحدّد اتجاه البحث.1يبحث من العنصر الأول إلى الأخير، وهو الافتراضي.-1يبحث من الأخير إلى الأول.2و-2هما بحث ثنائي على بيانات مرتبة تصاعدياً أو تنازلياً. وتحذّر Microsoft من أن البيانات غير المرتبة تُرجع نتائج غير صحيحة مع الوضعين الثنائيين.
اجعل مصفوفة البحث ومصفوفة الإرجاع بالحجم نفسه. فإذا كانت مصفوفة البحث تغطي الصفوف من 2 إلى 100، فيجب أن تغطي مصفوفة الإرجاع الصفوف من 2 إلى 100 أيضاً. لا تذكر صفحة Microsoft ذلك كقاعدة صريحة، لكن النطاقات غير المتطابقة هي السبب المعتاد وراء إرجاع البحث للصف الخطأ أو ظهور خطأ.
عمليات البحث الأساسية ووسيط عدم العثور
لنفترض أن معرّفات الموظفين موجودة في A2:A100 وأن الأسماء موجودة في B2:B100. تكتب المعرّف الذي تريده في D2.
- انقر على الخلية التي يجب أن تظهر فيها النتيجة، مثل E2.
- أدخل
=XLOOKUP(D2,A2:A100,B2:B100). - اضغط على Enter.
ستحصل على الاسم المرتبط بالمعرّف. يُرجع XLOOKUP العنصر الخاص بأول تطابق يجده. إذا لم يكن المعرّف موجوداً، فستظهر لك القيمة #N/A.
لعرض رسالة أوضح، أضف الوسيط الرابع:
=XLOOKUP(D2,A2:A100,B2:B100,"No record found")
الآن يظهر "No record found" عند إدخال معرّف غير مدرج. استخدم هذا في الأوراق التي يقرأها آخرون. لكن انتبه: مثل هذه الرسالة قد تُخفي مشكلة حقيقية. فإذا ظهرت العبارة نفسها في كل الصفوف، فالخلل في النطاقات وليس في البيانات، لذا اختبر أولاً معرّفاً تعرف أنه موجود.
البحث نحو اليسار
لا يستطيع VLOOKUP إرجاع قيمة من عمود يقع على يسار عمود البحث. أما XLOOKUP فيستطيع ذلك، لأن نطاق البحث ونطاق الإرجاع منفصلان. إذا كانت الأسماء في العمود C والمعرّف الذي تريده في العمود A:
=XLOOKUP(F2,C2:C100,A2:A100)
تذكر صفحة Microsoft ذلك صراحةً: يمكن أن يقع عمود البحث على أي جانب من عمود الإرجاع.
أوضاع المطابقة: أحرف البدل والقيم الأقرب
المطابقة بأحرف البدل
اضبط match_mode على 2 لاستخدام أحرف البدل. تمثل النجمة أي عدد من الأحرف، وتمثل علامة الاستفهام حرفاً واحداً بالضبط. وتتيح لك علامة التيلدا (~) البحث عن نجمة أو علامة استفهام بوصفها نصاً حرفياً.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)
يُرجع هذا أول إدخال يبدأ بالنص "Jan". إذا نسيت الرقم 2، فسيبحث Excel عن النص الحرفي "Jan*" ويُرجع رسالة عدم العثور. وهذا أكثر أخطاء أحرف البدل شيوعاً. اجعل أنماط أحرف البدل محددة قدر الإمكان، لأن XLOOKUP يُرجع أول تطابق فقط، والنمط الفضفاض قد يطابق شيئاً لم تقصده.
التالي الأصغر أو الأكبر
الوضعان -1 و1 مخصصان للمقاييس، مثل نسب الضرائب وشرائح العمولات ونقاط تغيّر الأسعار. ضع الحدود في A2:A6 والنسب في B2:B6، وابحث عن دخل في E2:
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
مع -1 يُرجع Excel التطابق التام، أو الحد الأصغر التالي إذا لم يوجد تطابق تام. والخانة الفارغة قبل -1 تتجاوز وسيط عدم العثور. يستخدم مثال Microsoft للتطابق التقريبي الرقم 1 لإرجاع نسبة ضريبة لأقرب قيمة دخل أكبر، لذا اختر الاتجاه الذي يناسب جدولك. وعلى عكس VLOOKUP التقريبي بالطريقة القديمة، لا تحتاج إلى ترتيب الحدود في هذين الوضعين. أما أوضاع البحث الثنائي فهي التي تتطلب بيانات مرتبة.
البحث من الأسفل: آخر تطابق
عندما تظهر القيمة أكثر من مرة، يُرجع XLOOKUP أول تطابق يصادفه. للحصول على آخر تطابق، ابحث من النهاية بضبط search_mode على -1:
=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)
إذا كانت الطلبات مرتبة في الورقة حسب التاريخ، فسيُرجع هذا أحدث إدخال للعميل. والرقم 0 يُبقي المطابقة تامة. هذا النمط مناسب لأسئلة مثل "أحدث سعر" و"آخر تواصل" وما شابهها، وكانت صعبة مع VLOOKUP.
إرجاع عدة أعمدة دفعة واحدة
تشير صفحة Microsoft إلى أن XLOOKUP، على عكس VLOOKUP، يستطيع إرجاع عدة أعمدة كمصفوفة واحدة. وسّع نطاق الإرجاع:
=XLOOKUP(D2,A2:A100,B2:D100)
تضع الصيغة التطابق من العمود B في الخلية، وتنسكب القيم من العمودين C وD في الخلايا الواقعة على يمينها، لذا اترك مساحة لها. وإذا احتوت الخلايا على يمين ذلك بيانات بالفعل، فسيعرض Excel خطأ انسكاب بدلاً من الكتابة فوقها. ويُظهر مثال Microsoft إرجاع اسم موظف وقسمه من صيغة واحدة.
عمليات البحث ثنائية الاتجاه
توضح Microsoft كيفية تداخل دالتي XLOOKUP للعثور على قيمة عند تقاطع صف وعمود. والنمط، كما ورد في صفحتها، هو:
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
تختار الدالة الداخلية العمود الذي يطابق العنوان في الخلية C3. ثم تبحث الدالة الخارجية عن القيمة D2 في تسميات الصفوف، وتعيد القيمة من ذلك العمود. تقارن Microsoft هذا بالتركيبة INDEX وMATCH. إذا كان لديك تقرير تُدرج فيه المنتجات عموديًا على الجانب والأشهر أفقيًا في الأعلى، فهذه الصيغة بديلة عن صيغة أكثر تعقيدًا.
معايير متعددة
تأخذ XLOOKUP قيمة بحث واحدة، ولا تتضمن صفحة Microsoft أي مثال على معايير متعددة. من الأنماط الشائعة أن تجعل Excel يختبر شرطين في الوقت نفسه. لنفترض أن الفئة موجودة في العمود A، والحالة في العمود B، والسعر في العمود C. للعثور على السعر للفئة الموجودة في الخلية F2 والحالة الموجودة في الخلية G2:
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")
تُعيد كل مقارنة القيمة TRUE أو FALSE. وينتج عن ضربها القيمة 1 فقط في الصفوف التي يتحقق فيها الشرطان، وتبحث XLOOKUP عن أول قيمة 1. هذا نمط يعمل في إصدارات Microsoft 365 الحالية، لكنه غير وارد في صفحة الدالة لدى Microsoft، لذا اختبره على عينة صغيرة أولًا. البديل الأقدم هو عمود مساعد يدمج الحقلين، فتضع فاصلًا بين القيمتين، على سبيل المثال =A2&"|"&B2، ثم تبحث عن النص المدمج نفسه.
مقارنة XLOOKUP وVLOOKUP جنبًا إلى جنب
تعرض صفحة VLOOKUP لدى Microsoft الصيغة على النحو الآتي VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])، وتذكر أن قيمة البحث يجب أن تكون في العمود الأول من النطاق. إذا تركت range_lookup دون قيمة، فإن VLOOKUP تُجري مطابقة تقريبية، وتفترض أن العمود الأول مرتب. اضبطها على FALSE للحصول على مطابقة تامة. وتقترح الصفحة نفسها تجربة XLOOKUP، وتذكر أنها تعمل في أي اتجاه وتُعيد المطابقات التامة افتراضيًا.
المهمة نفسها في الدالتين، مع المعرفات في العمود A والأسماء في العمود B:
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
الفروق المهمة في الاستخدام اليومي:
- تشير VLOOKUP إلى النتيجة برقم العمود، لذا فإن إدراج عمود في الجدول يغيّر معنى هذا الرقم. أما XLOOKUP فتشير إلى نطاق الإرجاع مباشرةً.
- لا تستطيع VLOOKUP البحث نحو اليسار، بينما تستطيع XLOOKUP ذلك.
- تستخدم VLOOKUP المطابقة التقريبية افتراضيًا، بينما تستخدم XLOOKUP المطابقة التامة افتراضيًا.
- تتضمن XLOOKUP وسيطة لمعالجة حالة عدم العثور على القيمة، وخيار اتجاه البحث مدمجًا فيها.
لا حاجة إلى إعادة كتابة المصنفات القديمة. إذا كانت VLOOKUP تعمل، فاتركها كما هي. استخدم XLOOKUP في الصيغ الجديدة، وفي عمليات البحث التي تحتاج إلى البحث نحو اليسار أو إلى آخر مطابقة أو إلى عدة أعمدة للإرجاع.
إصدارات Excel التي تدعم XLOOKUP
هنا يجب أن تكون حذرًا. تدرج صفحة Microsoft دالة XLOOKUP ضمن Excel لـ Microsoft 365 على Windows وMac، وExcel 2024، وExcel 2021، وتطبيقات iPad وiPhone وAndroid. وتذكر ملاحظة في الصفحة أنها غير متاحة في Excel 2016 أو 2019، لكنها قد تظهر في المصنفات المنشأة في إصدارات أحدث، كما تدرج الصفحة الإصدارين 2016 و2019 في سطر "ينطبق على". وهذا تناقض، لذا لا تعتبر Excel 2016 و2019 آمنين. كما أن Excel للويب غير مدرج في تلك الصفحة أيضًا.
إذا أرسلت مصنفًا إلى شخص يستخدم إصدارًا أقدم، فقد تُظهر الصيغة خطأً بدلًا من القيمة المطلوبة. اسأل عن الإصدار الذي يستخدمه المستلمون، وبالنسبة إلى الملفات التي تُرسل إلى عدد كبير من الأشخاص، اختبر الصيغة على أقدم إصدار مستخدم. وإذا تعذر عليك الاختبار، فاستخدم طريقة البحث التقليدية لذلك الملف.
الأخطاء الشائعة ومشكلات الأخطاء
- نسيان تعيين
match_modeإلى 2 عند استخدام أحرف البدل. عندها يبحث النظام عن الأحرف الحرفية. - استخدام
search_modeثنائي (2 أو -2) على بيانات غير مرتبة. تذكر Microsoft أن النتائج تكون غير صالحة في هذه الحالة. - اختلاف حجم نطاق البحث ونطاق الإرجاع. اجعلهما بالطول نفسه.
- المسافات المخفية والأرقام المخزنة كنص. فالقيمة "100" والرقم 100 مختلفان، لذلك يفشل البحث وتظهر لك الرسالة #N/A. نظّف البيانات باستخدام TRIM، أو حوّل الأرقام.
- إخفاء المشكلات برسالة "غير موجود" ودية. اختبر قيمة معروفة واحدة قبل الاعتماد على الصيغة.
- توجيه مصفوفة الإرجاع إلى خلايا يجب أن تنسكب فيها النتيجة، مع إبقاء تلك الخلايا مشغولة.
- استخدام مراجع الأعمدة الكاملة في الأوراق الكبيرة جدًا. استخدم نطاقًا محددًا أو جدول Excel، لكي يعالج Excel عددًا أقل من الخلايا.
موضوعات ذات صلة للاستكشاف
- XLOOKUP مقابل INDEX وMATCH. كلتاهما تُجريان بحثًا نحو اليسار، ويعمل الثنائي INDEX وMATCH في إصدارات Excel الأقدم أيضًا. اقرأ عنه إذا كنت تشارك الملفات مع أشخاص يستخدمون Excel 2016 أو 2019.
- نتائج متعددة. تُرجع الدالة XLOOKUP التطابق الأول فقط. للحصول على كل التطابقات، استخدم الدالة FILTER بدلاً من ذلك.
- XLOOKUP مع جداول Excel. تجعل المراجع المنظمة النطاقات تنمو مع البيانات، وتسهل قراءتها داخل الصيغ.
- بيانات من Power Query. بعد تحميل البيانات إلى جدول، يمكن لـ XLOOKUP البحث فيه مثل أي جدول آخر. حدّث الاستعلام أولاً، ثم تحقق من النتائج لاحقاً.
- Excel للويب. لا تدرج صفحة XLOOKUP من Microsoft هذا التطبيق، لذا تحقق في بيئتك الخاصة من أن الدالة تعمل مع زملائك قبل مشاركة أي ملف.
الخطوة التالية
خذ دالة VLOOKUP واحدة من المصنف الحالي وأعد كتابتها باستخدام XLOOKUP. أضف رسالة للحالة التي لا يُعثر فيها على قيمة، وقارن النتيجتين على عدد قليل من المعرّفات التي تعرفها. إذا تطابقت النتيجتان، فلديك نمط يمكنك إعادة استخدامه مع كل دالة XLOOKUP جديدة في Excel.