كيفية استخدام VLOOKUP وXLOOKUP في Excel: دليل كامل وأمثلة عملية

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

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

ما هو VLOOKUP و XLOOKUP في Excel؟

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

تُعدّ دالة VLOOKUP الصيغة الكلاسيكية للبحث عن البيانات عموديًا ، أي داخل عمود واحد. وقد ظلت هذه الدالة أساسية في برنامج Excel لسنوات. ومع ذلك، فقد ظهرت دالة XLOOKUP في الإصدارات الأحدث، وهي دالة أكثر قوة ومرونة تتغلب على العديد من قيود دالة VLOOKUP.

لماذا يُعدّ تعلّم كليهما أمرًا بالغ الأهمية؟ لأنّ العديد من الشركات لا تزال تستخدم إصدارات قديمة من برنامج Excel، حيث لا تتوفر دالة XLOOKUP، ولأنّ دالة VLOOKUP لا تزال مفيدة ومتوافقة إلى حدّ كبير. مع ذلك، إذا كنت تستخدم الإصدار المناسب، فإنّ دالة XLOOKUP تفتح أمامك آفاقًا واسعة من الإمكانيات الجديدة.

ما هو كل واحد منهم؟ الاستخدامات الأساسية والمواقف الحياتية الواقعية

تُستخدم دالة VLOOKUP للبحث عن البيانات بالرجوع إلى قيمة في العمود الأيسر من الجدول ، حيث تُعيد قيمةً مُرتبطةً بها من عمود آخر في نفس الصف. على سبيل المثال: لديك قائمة منتجات تتضمن رموزًا ووصفًا وأسعارًا، وتريد عرض الوصف والسعر تلقائيًا عند إدخال رمز. تُسهّل دالة VLOOKUP هذه العملية.

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

لنلقِ نظرة على مثال عملي شائع جدًا: إدارة الفواتير. تخيّل أنك صاحب مشروع صغير يبيع مستلزمات طبية. ليس لديك نظام فواتير متطور، وتحتاج إلى استخراج البيانات من قائمة تضم أكثر من 3.000 صنف في كل مرة يصلك طلب. سيكون إدخال البيانات يدويًا عمليةً تستغرق وقتًا طويلًا للغاية وعرضةً للأخطاء. باستخدام دالتي VLOOKUP أو XLOOKUP، يمكنك أتمتة العملية بحيث يتم ملء الوصف والعرض والسعر تلقائيًا باستخدام رمز المنتج فقط.

الاختلافات الرئيسية: ما هي حدود VLOOKUP ولماذا يعد XLOOKUP هو التطور؟

تُعد دالة VLOOKUP وظيفة رائعة، ولكنها تحتوي على بعض القيود التي قد تكون غير مريحة في جداول البيانات المعقدة:

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

تزيل دالة XLOOKUP جميع هذه القيود تقريبًا وتضيف تحسينات حاسمة:

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

ما هي إصدارات Excel التي تسمح بكل وظيفة؟

تتوفر دالة VLOOKUP في جميع إصدارات Excel الحديثة ، من أقدمها إلى أحدثها. ستجدها في كلٍ من إصدار Microsoft 365 وإصدارات سطح المكتب الدائمة من Excel.

أما دالة XLOOKUP، فهي متاحة فقط في إصدارات Excel من 1910 فصاعدًا . إذا كنت تستخدم Excel 2016 أو أحد إصدارات أوائل 2019، فمن المحتمل أنها غير متوفرة لديك. لمعرفة ما إذا كان بإمكانك استخدام XLOOKUP، اكتب ببساطة =XLOOKUP في شريط الصيغة، وتحقق مما إذا كانت تظهر في قائمة الاقتراحات.

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

بناء جملة VLOOKUP: تحليل كل وسيطة

دعونا نحلل صيغة VLOOKUP لنكتشف بالضبط كيف تعمل :

=VLOOKUP(قيمة البحث؛ مصفوفة الجدول؛ مؤشر العمود؛ )

• ابحث عن القيمة:البيانات التي تريد العثور عليها (يمكن كتابتها مباشرة أو في خلية مرجعية).
• جدول المصفوفة:النطاق الكامل للبحث، بما في ذلك العمود الذي توجد به بيانات المرجع وأعمدة المعلومات التي يجب استرجاعها.
• أعمدة المؤشرات:رقم العمود (يبدأ بـ 1) الذي تريد استخراج النتيجة منه ضمن النطاق المحدد. إذا كنت تريد البيانات من العمود الثاني من النطاق، فسوف تضع 2 هنا.
• : خياري. إذا كانت القيمة TRUE أو تم حذفها، فسيتم العثور على تطابق تقريبي؛ إذا كانت القيمة FALSE، فسيتم إرجاع المطابقات الدقيقة فقط.

مثال نموذجي : لديك جدول عملاء في النطاق A1:D16. تريد إيجاد رقم الهاتف (الموجود في العمود 4، أي العمود D) للعميل ذي المعرف 8. ستكون الصيغة كالتالي:

=VLOOKUP(8؛A1:D16؛4؛خطأ)

إذا لم تكن البيانات موجودة وطلبت تطابقًا دقيقًا، فسيظهر الخطأ #N/A. إذا سمحت بالمطابقة التقريبية، فسوف يتم إرجاع النتيجة الأقرب أدناه.

حيل ومتغيرات VLOOKUP المتقدمة

لا تقتصر دالة VLOOKUP على البحث في العمود الأول فقط، بل يمكنك إعادة تعريف النطاق للبحث في أي عمود . على سبيل المثال، إذا كان لديك أسماء في العمود B وتريد البحث بالاسم بدلاً من المعرّف، فما عليك سوى تحديد النطاق من العمود B فصاعدًا وتعديل مؤشر العمود.

=VLOOKUP("ميغيل بيريا راموس";B1:D16;3;خطأ)

سيتم البحث حسب الاسم وإرجاع رقم الهاتف المقابل، على سبيل المثال.

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

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

=IF(VLOOKUP(8;A1:D16;4;FALSE);»لم يتم العثور على العميل»)

بناء جملة XLOOKUP: الوسائط وقوتها

تتم صياغة صيغة XLOOKUP على النحو التالي:

=XLOOKUP(قيمة البحث؛ مصفوفة البحث؛ المصفوفة المُعادة؛ ؛ ؛ )

• ابحث عن القيمة:البيانات التي تريد تحديد موقعها (خلية أو قيمة مباشرة).
• مصفوفة البحث:النطاق الذي يجب أن يبحث فيه Excel عن تلك البيانات.
• المصفوفة المرتجعة:النطاق الذي سيتم استخراج النتيجة منه. يمكن أن يكون عمودًا واحدًا أو عدة أعمدة إذا كنت تريد قطعًا متعددة من البيانات في وقت واحد، على عكس VLOOKUP.
• : خياري. إذا لم يتم العثور على القيمة، فيمكنك تحديد الرسالة أو البيانات التي تريد ظهورها (على سبيل المثال، "لم يتم العثور عليها").
• : خياري. يحدد كيفية البحث عن القيمة: 0 للقيمة الدقيقة، -1 للقيمة الدقيقة أو الأدنى التالية، 1 للقيمة الدقيقة أو الأعلى التالية، 2 لمطابقة الأحرف البدل.
• : خياري. يسمح لك باختيار كيفية عبور البيانات: 1 من الأعلى إلى الأسفل، -1 من الأسفل إلى الأعلى، 2 بحث ثنائي تصاعدي، -2 بحث ثنائي تنازلي.

مثال نموذجي لاستخدام دالة XLOOKUP : تريد البحث عن منتج في قائمة (الرموز في العمود A والأسعار في العمود E)، وعرض السعر، وتخصيص الرسالة في حال عدم وجود المنتج. ستكون الصيغة كالتالي:

=XLOOKUP(B2؛ الأسعار! $A$1: $A$7000؛ الأسعار! $E$1: $E$7000؛ "لم يتم العثور عليه"؛ 0)

هنا، إذا لم يكن العنصر موجودًا، فسيظهر مباشرةً "غير موجود" دون الحاجة إلى IFERROR.

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

إحدى المزايا الرائعة لـ XLOOKUP هي مرونة حججها الاختيارية. دعونا نراهم واحدا تلو الآخر:

• :يمكنك إرجاع نص أو رقم أو حتى خلية فارغة عن طريق إدخال "". انسى الخطأ المرئي #N/A وقم بتخصيص التجربة.
• :
  • 0: تطابق دقيق (افتراضي).
  • -1: تطابق دقيق أو القيمة الأدنى التالية.
  • 1: تطابق دقيق أو القيمة الأعلى التالية.
  • 2: مطابقة الأحرف البدل (* للأحرف المتعددة، ? لحرف واحد، ~ لتجاهل الحرف الخاص).
• :
  • 1: من الصف الأول إلى الصف الأخير (افتراضي).
  • -1: من الأخير إلى الأول (بحث عكسي).
  • 2: البحث الثنائي التصاعدي (يتطلب بيانات مرتبة من الأدنى إلى الأعلى).
  • -2: البحث الثنائي التنازلي (يتطلب بيانات مرتبة من الأعلى إلى الأدنى).

أمثلة عملية على استخدام XLOOKUP

سنوضح لكم كيف يحقق تطبيق BUSCARX ميزة في سيناريوهات واقعية مختلفة :

• البحث عن عنصر بيانات واحد:يبحث عن اسم بلد ما ويعيد رمز البلد الخاص به.
• البحث عن بيانات متعددة:على عكس VLOOKUP، يمكنك إرجاع صف كامل من المعلومات (الاسم، القسم، وما إلى ذلك) باستخدام صيغة واحدة.
• التخصيص الخالي من الأخطاء:إذا كنت تريد تجنب الخطأ النموذجي #N/A عندما لا توجد البيانات، فكل ما عليك فعله هو استخدام الوسيطة .
• البحث المشترك (العمودي والأفقي):يمكنك استخدام XLOOKUP لتقاطع القيم في جدول في كلا البعدين، على غرار الجمع بين INDEX وMATCH.
• النطاقات الديناميكية:إذا كان لديك نطاقان من البيانات (جداول متعددة)، فيمكنك ربط كليهما في الصيغة وسوف يقوم XLOOKUP بالبحث في كليهما في نفس الوقت.
• البحث الجزئي باستخدام الأحرف البدل:البحث عن البيانات عن طريق المطابقة الجزئية باستخدام *، ؟ أو ~ في قيمة البحث الخاصة بك.

مقارنة سريعة للحجج: VLOOKUP مقابل XLOOKUP

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

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

أمثلة على الحجج والحالات المحلولة

تخيل جدولًا للدول مع بيانات السكان، ومتوسط ​​العمر المتوقع، ورموز الدول . إذا كنت تريد بيانات السكان فقط، فحدد مصفوفة الإرجاع كعمود السكان. أما إذا كنت تريد بيانات متعددة، فحدد مصفوفة الإرجاع كأعمدة متعددة. بوضع الصيغة في الخلية G6، سيتم أيضًا ملء البيانات في العمودين H6 وI6 ديناميكيًا.

ماذا يحدث عند البحث عن بيانات غير موجودة؟ باستخدام دالة VLOOKUP، إذا بحثت عن "أوروغواي" (وهي غير موجودة في القائمة) ولم تستخدم دالة IFERROR ، فستحصل على الخطأ #N/A. أما باستخدام دالة XLOOKUP، فيمكنك تحديد الرسالة التي تظهر، أو الرقم، أو النتيجة التي تريدها.

ما وراء البحث الدقيق: وضع المطابقة واستخداماته

• للعثور على أصغر قيمة تالية، استخدم -1. على سبيل المثال: البحث عن 46.520 في جدول الخصم، إذا لم يكن موجودًا، فسوف يؤدي إلى إرجاع الخصم للمبلغ الأقل التالي.
• بالنسبة للقيمة الأعلى التالية، استخدم 1. مفيد إذا كنت تبحث عن نطاقات تقدمية.
• لاستخدام الأحرف البدل، استخدم 2. على سبيل المثال، ابحث عن "*الجنوب*" وستحصل على "الجنوب"، "الجنوب الشرقي"، "الشمال-الجنوب"...

كيف تعمل الأحرف البدل في XLOOKUP؟

تستبدل علامة النجمة (*) أي عدد من الأحرف . إذا بحثت عن "*east"، فسترى "شرق"، "جنوب شرق"، "شمال شرق"...

علامة الاستفهام (?) تحل محل حرف واحد فقط . هل أنت غير متأكد من صحة "ماريا" أو "ماريو"؟ جرب "ماريا" وسيتم العثور على كليهما.

تلغي علامة المد (~) قيمة الحرف البديل . إذا بحثت عن "How~?" فلن تجد "Howr"، بل ستجد "How?" فقط.

ما هو البحث الثنائي الذي يقدمه XLOOKUP؟

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

هل يمكنني البحث في جداول متعددة أو حسب أكثر من معيار في وقت واحد؟

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

ماذا لو أردتُ الربط بين عدة معايير؟ هنا يأتي دور XLOOKUP المتداخل: يمكنك استخدام بحث أولي لتحديد نطاق العمود وآخر للصف، مما يُعيد البيانات الدقيقة عند التقاطع، كما لو كان نسخة متقدمة من INDEX + MATCH.

ما هو القدر الذي يمكنني تخصيص نتائج XLOOKUP به؟

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