كيفية استخدام دالة XLOOKUP للبحث عن البيانات في جدول Excel وجداول بيانات Google

¿كيفية استخدام دالة XLOOKUP للعثور على البيانات في جدول Excel وجداول بيانات Google؟ إذا كنت تعمل بشكل متكرر مع جداول البيانات، فستتعب عاجلاً أم آجلاً من البحث اليدوي عن البيانات بين الصفوف والأعمدة. هذه الوظيفة يبحث إنها مثالية لهذا الغرض: فهي التطور الطبيعي لدالتي VLOOKUP وHLOOKUP، وأكثر مرونة وأسهل في الصيانة. في كل من Excel وGoogle Sheets (وحتى في BigQuery في سياق أكثر تقدماً)، تتيح لك تحديد قيمة في جدول وإرجاع البيانات المرتبطة بها دون الحاجة إلى التعامل مع أعمدة مُعدّة يدوياً أو نطاقات جامدة.

ستشاهدون في هذا الدليل، خطوة بخطوة، كيفية استخدام دالة XLOOKUP للبحث عن البيانات في جدول في Excel وGoogle Sheets، ما الذي يفعله كل وسيط بالضبط، وكيف يعمل وضع المطابقة ووضع البحث، وكيفية التعامل مع النصوص (بما في ذلك عمليات البحث الجزئية، والأحرف الكبيرة/الصغيرة، والمسافات الإضافية) وحتى بعض الاستخدامات المتقدمة مثل عمليات البحث ثنائية الاتجاه أو إضافة نتائج من عمليات مطابقة متعددة.

ما هي دالة XLOOKUP ولماذا هي أفضل من دالتي VLOOKUP و HLOOKUP؟

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

في برنامج إكسل، تكون الصيغة العامة لدالة XLOOKUP كما يلي:

=XLOOKUP(lookup_value; lookup_array; return_array; ; ; )

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

وسائط دالة XLOOKUP في برنامج Excel: شرح مفصل

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

قيمة_البحث البيانات المطلوبة (إلزامية) هي البيانات التي تريد البحث عنها. يمكن أن تكون رقمًا، أو تاريخًا، أو نصًا، أو مرجع خلية، أو حتى نتيجة صيغة أخرى. في حال حذفها، قد تفشل دالة XLOOKUP في ظروف استثنائية. إرجاع الخلايا الفارغة داخل مصفوفة البحثومع ذلك، في الاستخدام العادي، يُنصح دائمًا بتحديد القيمة أو مرجع الخلية التي سنبحث عنها بوضوح.

مصفوفة البحث (مطلوب) هو النطاق أو المصفوفة التي تحاول دالة XLOOKUP العثور على قيمة البحث فيها. يجب أن يكون عمود واحد أو صف واحدلأنه المحور الذي تتم عليه عملية المطابقة. على سبيل المثال، عمود من رموز المنتجات أو صف يحتوي على أسماء الأشهر.

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

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

تحدد الوسيطة (الاختيارية) كيفية مقارنة قيمة البحث بالبيانات الموجودة في مصفوفة البحث. فيما يلي الخيارات الرئيسية:

• 0: مطابقة تامة؛ إذا لم يتم العثور عليها، يتم إرجاع #N/A. هذه هي القيمة الافتراضية.
• -1: تطابق تام، أو إذا لم يكن هناك تطابق، القيمة الأصغر التالية.
• 1: تطابق تام، أو إذا لم يكن هناك تطابق، القيمة الأكبر التالية.
• 2: تفعيل مباراة مع بطاقات البدل، حيث أن * و ؟ و ~ لها معانٍ خاصة.

وأخيرًا، (اختياري) يتحكم في أي اتجاه وبأي طريقة يقوم XLOOKUP بمسح نطاق البحث:

• 1: بحث عادي يبدأ من العنصر الأول إلى الأخير (افتراضي).
• -1: البحث العكسي، بدءًا من العنصر الأخير صعودًا، وهو مفيد لتحديد موقع التطابق الأخير.
• 2: البحث الثنائي بافتراض أن مصفوفة البحث مرتبة بترتيب تصاعدي؛ إذا لم تكن كذلك، فقد تحدث نتائج غير صحيحة.
• -2: البحث الثنائي بافتراض الترتيب التنازلي؛ وبالمثل، إذا لم يتم احترام الترتيب، فستكون النتائج غير موثوقة.

أمثلة عملية لاستخدام دالة XLOOKUP في برنامج Excel

لمساعدتك على فهم كيفية ربط كل هذه الحجج معًا، دعنا نستعرض العديد من السيناريوهات النموذجية التي يغطيها XLOOKUP في Excel والتي تظهر غالبًا في التقارير والقوائم والنماذج المالية.

مثال 1: إيجاد رمز الهاتف الخاص بدولة ما
تخيل أن لديك جدولاً يحتوي على قائمة بالدول في العمود B (B2:B11) ورموز الاتصال الدولية الخاصة بها في العمود D (D2:D11). في الخلية F2، أدخل اسم دولة وتريد من دالة XLOOKUP أن تُرجع رمز الاتصال الدولي المقابل.

ستكون الصيغة على النحو التالي:

=BUSCARX(F2; B2:B11; D2:D11)

هنا، تأخذ دالة LOOKUP محتوى الخلية F2 كقيمة lookup_value، وتبحث عن ذلك البلد في مصفوفة البحث B2:B11 ويعيد رمز الهاتف المقابل من المصفوفة المُعادة D2:D11. ليس من الضروري تحديد وضع المطابقة لأنه يتم تنفيذه افتراضيًا. تطابق تام.

مثال 2: استرجاع نقاط بيانات متعددة من موظف باستخدام صيغة واحدة
لنفترض أن لدينا جدولًا للموظفين يحتوي على رقم تعريف في عمود واحد، وفي العمودين C وD (من C5 إلى D14) اسم الموظف وقسمه. بدلًا من استخدام دالة VLOOKUP للاسم وأخرى للقسم، يمكن استخدام دالة XLOOKUP. إرجاع مصفوفة تحتوي على عدة عناصر في الوقت نفسه، شيء مستحيل باستخدام VLOOKUP بهذه الطريقة النظيفة.

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

مثال 3: استخدام دالة XLOOKUP مع نص مخصص في حالة عدم العثور على أي نتائج
استنادًا إلى المثال السابق، قد ترغب في عرض شيء أكثر وضوحًا، مثل "غير موجود" أو حتى صفر، بدلًا من #N/A في حال عدم وجود رقم تعريف الموظف. وهنا يأتي دور الوسيط if_not_found.

قد تكون الصيغة النموذجية كالتالي:

=XLOOKUP(employee_id; ID_range; data_range; "لم يتم العثور على الموظف")

وبالتالي، عندما لا يظهر المعرّف في النطاق، تم عرض رسالتك الشخصية. بدلاً من الخطأ المعياري.

مثال 4: البحث عن شرائح الدخل ومعدلات الضرائب
هذه حالة نموذجية للبحث التقريبي: يعرض العمود C الدخل الشخصي، بينما يعرض العمود B معدل الضريبة المقابل لكل شريحة. في الخلية E2، تُدخل دخلاً محدداً وتريد من دالة XLOOKUP أن تُرجع معدل الضريبة المناسب، حتى لو لم يكن هذا الدخل مُدرجاً تحديداً في الجدول.

يمكن ضبط الصيغة على النحو التالي:

=XLOOKUP(E2; C:C; B:B; 0; 1; 1)

هذا هو المكان الذي تم فيه إصلاحه إذا لم يتم العثور عليه = 0 بحيث إذا لم يكن هناك تطابق صالح، يتم إرجاع الصفر بدلاً من الخطأ؛ match_mode = 1 بحيث إذا لم تكن هناك قيمة محددة، فإن دالة XLOOKUP تُرجع القيمة الأكبر التالية؛ و وضع_البحث = 1 للتنقل عبر العمود من العنصر الأول إلى العنصر الأخير.

مثال 5: نوع الفهرس المتقاطع للبحث المزدوج (الرأسي والأفقي)
في بعض التقارير، تحتاج إلى إيجاد قيمة عند تقاطع صف وعمود، على سبيل المثال، "إجمالي الإيرادات" في "الربع الأول". يحتوي العمود B على عناصر مثل إجمالي الإيرادات والتكاليف والربح، وما إلى ذلك، ويحتوي الصف أعلاه (C5:F5) على الأرباع. تتيح لك دالة XLOOKUP القيام بذلك. لإنشاء تطابق رأسي وتطابق أفقي باستخدام دالة متداخلة.

أولاً، يحدد هذا النمط قيمة "إجمالي الإيرادات" في العمود B، ثم قيمة "Trim1" في صف العناوين، وأخيراً يُعيد قيمة الخلية التي تقع عند تقاطع القيمتين. يُعد هذا النمط مكافئاً حديثاً لدمج دالتي INDEX وMATCH، ولكنه أكثر وضوحاً وسهولة في القراءة.

مثال 6: جمع نطاق ديناميكي بناءً على دالتين XLOOKUP
ومن الاستخدامات المتقدمة الأخرى العملية للغاية ما يلي: اجمع كل القيم بين نقطتين في جدولتخيل قائمة بالفواكه مع مبيعاتها في عمود واحد: تريد جمع المبيعات من "العنب" إلى "الكمثرى"، بما في ذلك كليهما.

إحدى الصيغ المحتملة هي:

=SUMA(BUSCARX(B3; B6:B10; E6:E10):BUSCARX(C3; B6:B10; E6:E10))

تُرجع دالة XLOOKUP نطاقًا كنتيجة، لذا عند تقييم الصيغة الكاملة، تصبح شيئًا مثل =SUM($E$7:$E$9)يمكنك التحقق من ذلك بنفسك باستخدام أداة "تقييم الصيغة" في Excel (الصيغ > تدقيق الصيغة > تقييم الصيغة)، وذلك باتباع الخطوات لمعرفة كيفية حل النطاقات.

دالة LOOKUPX في جداول بيانات جوجل و BigQuery: بناء الجملة والفروق الدقيقة

في جداول بيانات جوجل و BigQuery لديك أيضًا XLOOKUP، مع نهج مشابه جدًا، على الرغم من وجود بعض الفروق الدقيقة في أسماء الوسائط وفي الأوضاع المتاحة.

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

الصيغة المبسطة لدالة XLOOKUP في BigQuery هي:

XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode)

وتتلخص الحجج فيما يلي: مفتاح البحث هي القيمة التي تريد تحديد موقعها (على سبيل المثال، 42، "القطط" أو مرجع إلى B24)؛ نطاق البحث هذا هو العمود المستخدم في البحث؛ نطاق النتائج هو العمود الذي سيتم إرجاع النتيجة منه؛ قيمة مفقودة هي القيمة الاختيارية التي ستُعرض في حال عدم العثور على تطابق (القيمة الافتراضية: #N/A)؛ و وضع المطابقة يتحكم في كيفية مطابقة مفتاح البحث.

القيم المقبولة لـ match_mode هي:

• 0تطابق تام.
• 1: تطابق تام أو قيمة أعلى مباشرة من قيمة مفتاح البحث.
• -1: تطابق تام أو القيمة التالية أقل من قيمة مفتاح البحث.
• 2: المطابقة باستخدام الأحرف البديلة.

في BigQuery، لا يوجد وسيط. وضع البحثلذا يمكنك فقط تعديل نوع المطابقة، ولكن ليس الاتجاه أو البحث الثنائي.

ومن الأمثلة الكلاسيكية على الاستخدام في هذا السياق ما يلي:

=XLOOKUP("Apple", table_name!fruit, table_name!price)

والذي من شأنه أن يحدد موقع السلسلة "Apple" في عمود الفاكهة ويعيد السعر المقابل من عمود السعر، مع تطبيق وضع المطابقة الافتراضي (المطابقة التامة).

دالة XLOOKUP في جداول بيانات جوجل: النسخة الكاملة مع وضع البحث

في جداول بيانات جوجل، يكون بناء جملة XLOOKUP الكامل مشابهًا جدًا لبناء جملة Excel، مع بعض التغييرات في أسماء الوسائط ولكن بنفس فلسفة العمل.

بناء الجملة العام هو:

XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)

تعمل الحجج على النحو التالي: مفتاح البحث هذه هي القيمة التي ستبحث عنها؛ نطاق البحث هو نطاق البحث، والذي يجب أن يكون عمودًا واحدًا أو صفًا واحدًا؛ نطاق النتائج هو النطاق الذي ستأتي منه النتيجة، بنفس حجم الصفوف أو الأعمدة مثل lookup_range؛ قيمة مفقودة القيمة الاختيارية التي يتم إرجاعها في حالة عدم وجود تطابق (الافتراضي، #N/A)؛ وضع المطابقة حدد نوع التطابق؛ و وضع البحث يشير إلى كيفية اجتياز النطاق.

أما فيما يتعلق بـ وضع المطابقةالخيارات هي:

• 0تطابق تام.
• 1: تطابق تام أو قيمة أعلى مباشرة من قيمة مفتاح البحث.
• -1: تطابق تام أو القيمة التالية أقل من قيمة مفتاح البحث.
• 2: مطابقة مع أحرف البدل.

ولـ وضع البحث لديك:

• 1: يبحث من أول إدخال إلى آخر إدخال.
• -1: يبحث من آخر إدخال إلى أول إدخال.
• 2: البحث الثنائي في نطاق مُرتب بترتيب تصاعدي.
• -2: البحث الثنائي في نطاق مرتب تنازليًا.

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

من الأمثلة النموذجية على استخدام دالة XLOOKUP في جداول بيانات جوجل والتي تحل محل دالتي VLOOKUP و HLOOKUP ما يلي:

• XLOOKUP(«Apple», A2:A, E2:E) كبديل لـ VLOOKUP(«Apple», A2:E, 5, FALSE).
• XLOOKUP(«السعر»، A1:E1، A6:E6) كبديل لـ HLOOKUP(«Price», A1:E6, 6, FALSE).
• XLOOKUP(«Apple»، E2:E7، A2:A7) للبحث في عمود على اليمين وإرجاع قيمة موجودة على اليسار، وهو أمر لا تستطيع دالة VLOOKUP القيام به بدون حيل المصفوفات.

دالة XLOOKUP مع نص في Excel: مطابقة تامة، مطابقة جزئية، ومطابقة حساسة لحالة الأحرف

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

للبحث عن سلسلة نصية ثابتة، يمكنك كتابتها مباشرة بين علامتي اقتباس في الصيغة، على سبيل المثال:

=XLOOKUP(«Sub 2»; B3:B7; C3:C7)

في هذه الحالة، ستبحث دالة XLOOKUP حرفيًا عن "Sub 2" في العمود B وتعيد القيمة المرتبطة بها من العمود C. وهناك بديل آخر أكثر مرونة وهو الإشارة إلى خلية تحتوي على النص التي تريد تحديد موقعها، مما يسمح لك بتغيير المعايير دون المساس بالصيغة:

=BUSCARX(E3; B3:B7; C3:C7)

إذا كنت ترغب في البحث بدلاً من نص واحد نصوص متعددة في وقت واحديكفي أن تحتوي E3:E4 على قائمة المعايير؛ عند استخدام هذا النطاق كقيمة البحث، ستُنشئ دالة XLOOKUP صيغة مصفوفة ديناميكية:

=BUSCARX(E3:E4; B3:B7; C3:C7)

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

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

=BUSCARX(E3; $B$3:$B$7; $C$3:$C$7)

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

ابحث عن آخر تطابق في نص باستخدام XLOOKUP

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

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

=XLOOKUP(E3; B3:B7; C3:C7;;; -1)

في هذه الحالة، تُترك الوسيطتان الاختياريتان if_not_found و match_mode فارغتين (مع الإبقاء على الفواصل المنقوطة)، ويتم فقط تحديد وضع_البحث = -1 بحيث يجد XLOOKUP آخر تطابق للنص في E3 في النطاق B3:B7.

دالة XLOOKUP حساسة لحالة الأحرف مع دالة EXACT

من المصنع مباشرة، SEARCHX لا يفرق بين الأحرف الكبيرة والصغيرة.تُعامل المصطلحات "Code" و"CODE" و"code" كنص واحد. إذا كنتَ بحاجة إلى أن يكون البحث حساسًا لحالة الأحرف (على سبيل المثال، الرموز الداخلية حيث يختلف "ABc1" عن "abc1")، فيمكنك دمجه مع دالة EXACT.

هذا تركيب مفيد للغاية:

=LOOKUP(TRUE; EXACT(E3; B3:B7); C3:C7)

هنا، تقارن الدالة EXACT محتويات الخلية E3 مع كل قيمة في النطاق B3:B7، وتعيد مصفوفة من صواب أم خطأتُستخدم هذه المصفوفة كمصفوفة بحث لدالة XLOOKUP، التي تبحث عن أول قيمة TRUE وتعيد القيمة المقابلة لها من النطاق C3:C7. وبهذه الطريقة، لا يحدث تطابق إلا عندما يتطابق النص حرفًا بحرف، مع مراعاة الأحرف الكبيرة.

لا ينجح هذا الأسلوب إلا إذا تم تمرير النطاق B3:B7 كنطاق (مصفوفة) إلى الدالة EXACT، لأن الدالة بدورها تُرجع نتيجة مصفوفة تُغذي مباشرة الدالة XLOOKUP، مما يسمح بالمطابقة الحساسة لحالة الأحرف دون الحاجة إلى صيغ معقدة للغاية.

نتائج البحث الجزئية باستخدام XLOOKUP: أحرف البدل ووظائف البحث النصي

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

الخيار الأبسط هو الاستفادة من match_mode = 2مما يُفعّل خاصية مطابقة الأحرف البديلة. مثال نموذجي على ذلك:

=XLOOKUP(«*» & E3 & «*»; B3:B7; C3:C7;; 2)

تعني علامة النجمة (*) "أي سلسلة نصية مهما كان طولها". وضعها في بداية ونهاية البحث سيبحث عن أي نص يحتوي على التسلسل الموجود في الخلية E3 في أي مكان ضمن نطاق الخلايا من B3 إلى B7. على سبيل المثال، إذا كانت E3 تحتوي على "RS"، فسيبحث عن نصوص مثل "RS COURSE" أو "MRS40" أو ما شابه، وذلك بحسب محتويات مصفوفة البحث.

وهناك أسلوب آخر يتمثل في استخدام الدالة يجدتحدد هذه الدالة موضع نص داخل نص آخر دون التمييز بين الأحرف الكبيرة والصغيرة. وباستخدامها مع دالة ISNUMBER، يمكنك تحويل نتيجة تحديد الموضع إلى قيمة منطقية (صواب/خطأ) تُستخدم في دالة XLOOKUP.

=LOOKUP(TRUE; ISNUMBER(FIND(E3; B3:B7)); C3:C7;; 2)

هنا، تُعيد الدالة FIND رقم الموضع إذا عثرت على النص من الخلية E3 ضمن كل عنصر من عناصر النطاق B3:B7، أو تُعيد خطأً إذا لم تعثر عليه. بعد ذلك، تتولى الدالة ISNUMBER تحويل كل رقم موضع إلى القيمة TRUE وكل خطأ إلى القيمة FALSE. تُستخدم مصفوفة القيم المنطقية هذه كمصفوفة بحث، لذا ستبحث الدالة XLOOKUP عن أول قيمة TRUE وتُعيد التطابق الجزئي الذي تم العثور عليه.

إذا كنت بحاجة إلى تطابق جزئي، فهذا نعم، إنه يميز بين الأحرف الكبيرة والصغيرة.يمكنك استبدال HALLAR بـ ENCONTRAR، والتي تعمل بنفس الطريقة ولكنها حساسة لحالة الأحرف:

=LOOKUP(TRUE; ISNUMBER(FIND(E3; B3:B7)); C3:C7;; 2)

وبهذه الطريقة، لن يتم اعتبار التطابق الجزئي إلا عندما يظهر الجزء E3 داخل نص B3:B7 بنفس تركيبة الأحرف الكبيرة والصغيرة.

مطابقة جزئية "دقيقة" للكلمة الكاملة باستخدام LOOKUP

تعاني تقنيات المطابقة الجزئية السابقة من نقطة ضعف بسيطة: فهي لا تستطيع التمييز بين الكلمة الكاملة والتطابق "داخل" كلمة أخرى. على سبيل المثال، يمكن للنمط "EPA" أن يطابق كلاً من الكلمة "EPA" المستقلة والتسلسل "epa" داخل كلمة "Separate"، وذلك بحسب اللغة.

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

=XLOOKUP(«* » & E3 & » *»; » » & B3:B7 & » «; C3:C7;; 2)

في هذه الصيغة، تُضاف مسافات إلى بداية ونهاية كل من قيمة البحث وكل عنصر في النطاق B3:B7. وبهذه الطريقة، عند البحث باستخدام الأحرف البديلة، لن يجد XLOOKUP إلا التطابقات التي يظهر فيها التسلسل في E3 ككلمة مفصولة بمسافات، مما يقلل من النتائج الإيجابية الخاطئة في الكلمات الأطول.

المشاكل الشائعة في دالة XLOOKUP عند التعامل مع النصوص: المسافات الزائدة

أحد أكثر الأخطاء شيوعًا عند التعامل مع النصوص في دالة XLOOKUP لا علاقة له بمنطق الصيغة، بل بـ مساحات "غير مرئية" إضافية في البيانات. يتم التعامل مع هذه المسافات كجزء من النص، لذا فإن "Sub 2" و "Sub 2 (مع مسافتين)" ليسا متطابقين، ويفشل البحث على الرغم من أنهما يبدوان متطابقين للوهلة الأولى.

ولتجنب هذه المشاكل، يُنصح بالتأكد من عدم وجود مساحات أو فجوات غير مرغوب فيها في الجدران. قيمة_البحث ولا في مصفوفة البحثإذا لاحظت أن البيانات قد تحتوي على مسافات إضافية (على سبيل المثال، من عملية استيراد)، فإن الشيء الأكثر عملية هو الجمع بين XLOOKUP ووظيفة TRIM، مما يؤدي إلى تنظيف النص.

تتمثل الصيغة النموذجية لتنظيف كل من معايير البحث وعمود البحث فيما يلي:

=BUSCARX(ESPACIOS(E3); ESPACIOS(B3:B7); C3:C7)

تُقلل دالة TRIM المسافات المتتالية بين الكلمات إلى مسافة واحدة، وتزيل المسافات الزائدة في بداية ونهاية النص. ويتم تطبيقها بشكل منهجي على كل من قيمة البحث والمصفوفة التي يُجرى فيها البحث. يمكنك تقليل أخطاء #N/A الناتجة عن تنسيق نص غير نظيف وبذلك يمكنك استخدام دالة XLOOKUP للعمل مع السلاسل النصية القابلة للمقارنة بطريقة أكثر قوة.

يُحسّن إتقان دالة XLOOKUP في Excel وGoogle Sheets، وحتى في بيئات مثل BigQuery، كفاءتك بشكل كبير عند التعامل مع المعلومات: إذ يمكنك تحديد البيانات الدقيقة أو التقريبية، والعمل مع نتائج متعددة في وقت واحد، وإدارة نتائج "غير موجودة" بشكل أفضل، وإنشاء عمليات بحث معقدة باستخدام النصوص والأحرف البديلة ومراعاة حالة الأحرف دون اللجوء إلى صيغ معقدة. مع مزيج جيد من وسائطها وبعض وظائف مساعدة مثل EQUAL و FIND و SPACESيصبح أداة قوية للغاية لأي مستخدم يرغب في التوقف عن المعاناة مع دالة VLOOKUP والانتقال إلى مستوى أكثر مرونة وملاءمة.