تُستخدم دوال XLOOKUP و VLOOKUP في Excel لتحليل مجموعات كبيرة من البيانات بسرعة. VLOOKUP هي دالة تم استخدامها في Excel منذ سنوات. ومع ذلك ، هناك دالة أحدث يجب أن تستخدمها تُسمى XLOOKUP.
كلاهما له مزايا وقيود. نحن هنا لعرض الاختلافات ومُساعدتك في تحديد أيهما أكثر مُلاءمة لمشروعك التحليلي. تحقق من العديد من الحيل المفيدة على Microsoft Excel التي من شأنها أن تُبهر رئيسك.
كيف تعمل VLOOKUP؟
VLOOKUP تعني البحث العمودي. إنها إحدى دوال Excel التي تقوم بإجراء عمليات البحث عن عناصر في جدول أو نطاق بحسب الصف من مجموعة البيانات ثم تقوم بإرجاع قيمة فريدة من عمود آخر ، في نفس الصف. يكون بناء جملة الدالة VLOOKUP كما يلي:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
لنلقي نظرة على مثال. لنفترض أنَّ لديك قائمة بطرز أجهزة الهواتف الذكية في مجموعة البيانات الخاصة بك ، وتحتاج إلى سحب بعض المعلومات المُحددة عنها.
يُمكنك استخدام دالة VLOOKUP للحصول على معلومات إضافية حول مُعالجاتها ونقاط السعر.
فيما يلي دليل مُفصل حول كيفية استخدام الدالة VLOOKUP في Excel لاستخراج معلومات مُحددة من مجموعة بيانات. على الرغم من أنَّ VLOOKUP هي واحدة من أفضل الدوال في Excel ويُمكن أن تساعدك على تبسيط مجموعات البيانات الكبيرة ، إلا أن لها بعض القيود. على سبيل المثال ، يجب ترتيب البيانات بترتيب تصاعدي حتى تعمل الدالة.
كيف تعمل XLOOKUP؟
تُتيح لك XLOOKUP إجراء عمليات بحث أفقية ورأسية للبحث عن قيمة في جدول بيانات. تقوم الدالة بشكل أساسي بالبحث عن مصفوفة. إذا تم العثور على تطابق ، فإنها تقوم بإرجاع العنصر المقابل من نطاق البيانات الثاني (return_array).
إنها أكثر فاعلية من خيارات البحث الأخرى لأنها تأتي بمُطابقة تامة افتراضيًا. يُمكنك استخدام الوسيطات الاختيارية في الدالة ([match_mode]) لتحديد نوع قيم المُطابقة التقريبية التي تحتاجها — مطابقة تامة أو قيمة أقل أو أعلى لاحقة ، أو تطابق حرف بدل.
يُمكن استخدامها لأغراض مُختلفة ، بما في ذلك التحليل المالي والتسويق وتحليل المبيعات وما إلى ذلك. يكون بناء جملة الدالة XLOOKUP كما يلي:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
سيأخذك الدليل التالي عبر الأساسيات ويُساعدك على فهم كيفية استخدام دالة XLOOKUP في جدول بياناتك. تتطلب هاتان الدالتان ثلاث حجج على الأقل.
الاختلافات الرئيسية بين XLOOKUP و VLOOKUP
في حين أنَّ هناك العديد من أوجه التشابه بين XLOOKUP و VLOOKUP ، إلا أنه من المُهم فهم الاختلافات.
1. رقم فهرس العمود
عند تشغيل VLOOKUP ، ستحتاج إلى الانتقال إلى الجدول المصدر للتحقق وتحديد رقم فهرس العمود بالضبط.
تتطلب VLOOKUP مُعلمة رقم فهرس العمود لإرجاع النتائج الصحيحة. يُمكن أن يؤدي استخدام رقم فهرس عمود غير صحيح إلى إخراج مختلف من ورقة العمل أو البيانات ، إما في شكل قيمة غير صحيحة أو خطأ “#REF”.
في المثال أعلاه ، لنفترض أنك بحاجة إلى استخراج تفاصيل المعالج للجهاز ، Apple iPhone 12. سنستخدم مجموعة البيانات الثانية لجميع الأمثلة.
وفقًا لذلك ، نحتاج إلى إدخال قيمة البحث ، على سبيل المثال ، جهاز Apple iPhone 12 في العمود J ، والذي يُصادف أنه العمود الموجود في أقصى اليسار في مجموعة البيانات النموذجية لدينا ، والمُوافق لوسيطة table_array في الدالة (العمودان J و K). يجب استرداد القيمة من العمود الثاني في table_array ، أي عمود البحث K ، وبالتالي ، سيكون رقم فهرس العمود 2.
XLOOKUP هي دالة ديناميكية تُلغي وسيطة رقم فهرس العمود ، مما يعني أنه لا داعي للقلق بشأن حساب عدد الأعمدة في الجدول. ستحتاج فقط إلى تحديد أعمدة البحث ، مما سيُوفر عليك الكثير من الوقت والجهد.
2. المتانة والمرونة
بمجرد إنشاء النموذج الخاص بك ، يجب أن تكون قادرًا على تحديث الجدول المصدر بسرعة ببيانات جديدة دون القلق بشأن تعطل الدالة. باستخدام VLOOKUP ، تعني إضافة الأعمدة أو نقلها أنه يتعين عليك إعادة صياغة الدالة بأكملها.
XLOOKUP هي دالة أكثر قوة تسمح لك بإضافة أعمدة أو إزالتها دون القلق بشأن الأخطاء. فقط تأكد من عدم إدراج عمود أو صف خارج صفيف البحث ، وفي هذه الحالة ستُرجع الدالة خطأً.
3. رسائل خطأ مُخصصة
تعد دالة XLOOKUP مُلائمة للاستخدام. حيث تسمح لك بإدخال رسالة نصية خطأ مُخصصة في الوسيطة [if_not_found] التي ستظهر إذا لم يتم العثور على قيمة البحث.
لا تُقدم VLOOKUP إمكانيات مُماثلة. قد تحتاج إلى استخدام دوال أخرى مثل IFERROR كحل بديل ، في الحالات التي لا يتم فيها العثور على مُطابقات. تحقق من أهم الأسباب للتخلص من Excel والبدء في استخدام “جداول بيانات Google”.
لماذا تُعتبر XLOOKUP أفضل مقارنة بـ VLOOKUP؟
على الرغم من استخدامات VLOOKUP ، إلا أنها محدودة في وظائفها أكثر من XLOOKUP. هذا يجعل XLOOKUP هي الخيار المُفضل للعمل على مجموعات بيانات أكبر. النتائج أسرع وأقل عرضة للخطأ أيضًا. دعونا نفهم لماذا تعمل XLOOKUP بشكل أفضل مقارنةً بـ VLOOKUP.
1. لا تقتصر XLOOKUP على نطاقات بيانات مُعينة
تُعد XLOOKUP أكثر مرونة من VLOOKUP ، والتي يُمكنها البحث عن القيم فقط في العمود الموجود في أقصى اليسار من الجدول ، وإرجاع القيم من الأعمدة المُقابلة على اليمين ، كما رأينا في المثال أعلاه. في المقابل ، يتطلب نموذج XLOOKUP خطوات أبسط ويُمكنها إرجاع قيم لأي عمود في أي اتجاه.
2. يتم تعيين XLOOKUP افتراضيًا على مُطابقة تامة
بشكل افتراضي ، تفترض دالة VLOOKUP في Excel البحث عن تطابق تقريبي. ومع ذلك ، إذا كانت المطابقة التامة مطلوبة لورقة العمل الخاصة بك ، فيجب عليك إدخال “FALSE” في وسيطة range_lookup.
تبحث الدالة XLOOKUP عن قيمة البحث في النطاق المُحدد والتي تكون مطابقة تامة افتراضيًا. إذا تعذر العثور على تطابق تام ، يُمكنك تعديل الوسيطة للبحث عن تطابق تقريبي قد يكون أقل من قيمة البحث أو أكثر منها.
للقيام بذلك ، يُمكنك تعيين الوسيطة الاختيارية [match_mode] على 0 = المطابقة التامة (افتراضي) ، -1 = المطابقة التامة أو الأصغر التالية ، 1 = المطابقة التامة أو التالية الأكبر ، أو 2 = مطابقة حرف البدل.
3. يُمكن لـ XLOOKUP معالجة عمليات إدراج الأعمدة أو حذفها
تُعد VLOOKUP دالةً رائعةً للعثور على بيانات مُحددة من جدول بيانات أو جدول آخر. ومع ذلك ، فهي تُوفر مقياس ثابت — إذا كنت بحاجة إلى تحديث مصدر البيانات أو إجراء تغييرات أو إعادة ترتيب الأعمدة ، فإنَّ الدالة تتعطل ، وينتهي بك الأمر بفقدان دالة VLOOKUP.
من ناحية أخرى ، تُعد XLOOKUP مقياسًا أكثر ديناميكية يُمكنها التعامل مع تغييرات الأعمدة وإرجاع نتائج بحث دقيقة. كما تأتي مع معلمات أكثر تحديدًا ، مثل صفيف البحث وصفيف الإرجاع ، بدلاً من وسيطة صفيف الجدول ورقم فهرس العمود في VLOOKUP.
4. تقوم XLOOKUP بإجراء عمليات بحث عمودية وأفقية
يُمكن لدالة XLOOKUP العثور على البيانات في كل من المصفوفات الأفقية والعمودية ، على عكس VLOOKUP ، التي يُمكنها إجراء عمليات بحث عمودية عن البيانات فقط.
5. تُوفر XLOOKUP تطابقات تقريبية أكثر ذكاءً
إذا كنت تبحث عن عنصر قريب أكثر مما تُريد ، فسوف تمنحك XLOOKUP نتائج أكثر دقة. والتي تُقدم نتائج أكثر دقة وإيجابيات خاطئة أقل.
مع VLOOKUP ، ومع ذلك ، إذا كنت تُريد العثور على تطابق تقريبي من جدول البيانات الخاص بك ، فإنَّ الدالة ستُرجع فقط القيمة الأصغر التالية لمرجع المطابقة. لكن XLOOKUP تسمح لك بإجراء تطابق تقريبي أفضل وإرجاع القيم الأكبر أو الأصغر التالية من جدول البحث أيضًا.
6. XLOOKUP تحتاج إلى عدد أقل من مراجع الخلايا
أثناء استخدام XLOOKUP ، كل ما تحتاجه هو تحديد نطاقين مُحددين من البيانات — مصفوفة أو نطاق من حيث تُريد البحث عن قيمة (lookup_array) ، ومصفوفة / نطاق ترغب في استرداد القيمة (return_array) منها.
من ناحية أخرى ، تتطلب منك دالة VLOOKUP تحديد نطاق كامل من البيانات باستخدام المعلمة table_array. قد تكون هذه مشكلة في مجموعات البيانات المُعقدة لأنه إذا كان جدول البيانات كبيرًا ، فقد يكون من الصعب إعادة ترتيب أو تحديد نطاق الخلايا بأكمله الذي تُريد إرجاع قيمة منه. علاوة على ذلك ، لإرجاع تطابق تقريبي باستخدام VLOOKUP ، يجب دائمًا فرز البيانات الموجودة في الجدول بترتيب تصاعدي.
VLOOKUP أو XLOOKUP: أي دالة تُناسبك؟
إذن ، هل XLOOKUP أفضل من VLOOKUP؟ عند المقارنة المباشرة ، يجب أن تكون الإجابة نعم. ومع ذلك ، قد لا يكون الأمر بهذه البساطة لجميع المُستخدمين. على سبيل المثال ، قد تفضل دالة أقدم إذا كنت بارعًا في التعامل معها بشكل خاص أو عندما تجد استخدامًا لدالة مُعينة مُناسبة لاستخدامك.
بالنسبة لمجموعات البيانات الأكبر ، من المحتمل أن تكون XLOOKUP هي الخيار الأفضل لك نظرًا لوظائفها المُتزايدة. تحتوي VLOOKUP على العديد من القيود التي تجعل من الصعب استخدامها للبيانات المُعقدة — ونتيجة لذلك ، تتمتع XLOOKUP بقدر أكبر من المرونة ، مما يجعلها الخيار الأفضل لمُعظم مستخدمي جداول البيانات. يُمكنك الإطلاع الآن على مهارات Word و Excel التي يجب امتلاكها للنجاح في الوظيفة المكتبية.
