اكسل

23 شيئًا يجب أن تعرفه عن VLOOKUP

23 Things You Should Know About Vlookup

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



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

هذا يمكن أن يؤدي إلى نتائج تبدو طبيعية تمامًا ، على الرغم من أنهم كذلك غير صحيح تماما . صدقني ، هذا ليس شيئًا تريد محاولة شرحه لرئيسك في العمل ، بعد أن أرسلت بالفعل جدول البيانات إلى الإدارة :)





اقرأ أدناه لمعرفة كيفية إدارة هذا التحدي ، واكتشف نصائح أخرى لإتقان وظيفة Excel VLOOKUP .

1. كيف يعمل VLOOKUP

VLOOKUP هي وظيفة للبحث عن البيانات واستردادها في جدول. يرمز الحرف 'V' في VLOOKUP إلى الوضع الرأسي ، مما يعني أنه يجب ترتيب البيانات الموجودة في الجدول عموديًا ، مع وجود البيانات في الصفوف. (للحصول على البيانات المنظمة أفقيًا ، راجع هلوكوب ).



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

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

نظرة عامة حول كيفية عمل VLOOKUP

في الجدول أعلاه ، توجد معرفات الموظفين في العمود 1 على اليسار وعناوين البريد الإلكتروني في العمود 4 على اليمين.

لاستخدام VLOOKUP ، عليك توفير 4 أجزاء من المعلومات أو 'الوسائط':

  1. القيمة التي تبحث عنها ( ابحث عن القيمة )
  2. نطاق الخلايا التي يتألف منها الجدول ( table_array )
  3. رقم العمود المراد استرداد نتيجة منه ( العمود_الفهرس )
  4. وضع المباراة ( مجموعة البحث ، TRUE = تقريبي ، FALSE = دقيق)

فيديو: كيفية استخدام VLOOKUP (3 دقيقة)

إذا كنت لا تزال لا تفهم الفكرة الأساسية لـ VLOOKUP ، فإن Jon Acampora ، في Excel Campus ، لديه شرح عظيم بناءً على قائمة قهوة ستاربكس.

2. يبدو VLOOKUP صحيحًا فقط

ربما يكون أكبر قيود على VLOOKUP هو أنه يمكن فقط النظر إلى اليمين لاسترداد البيانات.

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

يمكن أن ينظر VLOOKUP إلى يمين عمود قيمة البحث فقط

يمكنك التغلب على هذا القيد باستخدام INDEX و MATCH بدلاً من VLOOKUP.

3. يجد VLOOKUP المباراة الأولى

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

يجد VLOOKUP دائمًا أول تطابق

ملاحظة: يمكن أن يتغير السلوك عند استخدام VLOOKUP في وضع المطابقة التقريبية. تشرح هذه المقالة الموضوع بالتفصيل.

4. VLOOKUP ليست حساسة لحالة الأحرف

عند البحث عن قيمة ، لا يعالج VLOOKUP النص بأحرف كبيرة وصغيرة بشكل مختلف. بالنسبة إلى VLOOKUP ، فإن رمز المنتج مثل 'PQRF' مطابق لـ 'pqrf'. في المثال أدناه ، نبحث عن الأحرف الكبيرة 'JANET' لكن VLOOKUP لا تميز الحالة ، لذا فهي تطابق 'Janet' ، نظرًا لأن هذه هي أول مطابقة تعثر عليها:

VLOOKUP ليس حساسًا لحالة الأحرف

كما نقدم تدريب مدفوع الأجر لـ VLOOKUP و INDEX / MATCH

5. يحتوي VLOOKUP على وضعين للمطابقة

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

مثال على المطابقة التامة لـ VLOOKUP - مطابقة الأفلام

الصيغة في H6 للبحث في العام بناءً على التطابق التام لعنوان الفيلم هي:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

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

مثال مطابقة تقريبية لـ VLOOKUP - العمولات

تقوم الصيغة في D5 بمطابقة تقريبية لاسترداد العمولة الصحيحة:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. تنبيه: يستخدم VLOOKUP التطابق التقريبي افتراضيًا

يتم التحكم في المطابقة التامة والتقريبية في VLOOKUP بواسطة الوسيطة الرابعة ، والتي تسمى 'بحث النطاق'. هذا الاسم ليس بديهيًا ، لذلك عليك فقط حفظ كيفية عمله.

للمطابقة التامة ، استخدم FALSE أو 0. للمطابقة التقريبية ، اضبط range_lookup على TRUE أو 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

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

يعد هذا افتراضيًا خطيرًا لأن العديد من الأشخاص يتركون VLOOKUP عن غير قصد في الوضع الافتراضي ، مما قد يتسبب في ظهور ملف نتيجة غير صحيحة عندما لا يتم فرز الجدول.

لتجنب هذه المشكلة ، تأكد من استخدام FALSE أو صفر كوسيطة رابعة عندما تريد مطابقة تامة.

7. يمكنك إجبار VLOOKUP على إجراء تطابق تام

لإجبار VLOOKUP على البحث عن تطابق تام ، تأكد من تعيين الوسيطة 4 (range_lookup) على FALSE أو صفر. هاتان الصيغتان متكافئتان:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

في وضع المطابقة التامة ، عندما يتعذر على VLOOKUP العثور على قيمة ، ستُرجع # N / A. هذا مؤشر واضح على أن القيمة غير موجودة في الجدول.

8. يمكنك إخبار VLOOKUP بالقيام بمطابقة تقريبية

لاستخدام VLOOKUP في وضع المطابقة التقريبية ، قم إما بحذف الوسيطة الرابعة (range_lookup) أو قم بتوفيرها على أنها TRUE أو 1. هذه الصيغ الثلاثة متكافئة:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

نوصي دائمًا بتعيين وسيطة range_lookup بشكل صريح ، على الرغم من أن VLOOKUP لا تتطلبها. بهذه الطريقة ، سيكون لديك دائمًا تذكير مرئي بوضع المطابقة الذي تتوقعه.

فيديو: كيفية استخدام VLOOKUP للمطابقات التقريبية

9. للمطابقات التقريبية ، يجب فرز البيانات

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

فيلين هيرمانز مثال رائع على هذه المشكلة هنا ، من تحليل رائع أجرته على أرض الواقع إنرون جداول البيانات!

10. يمكن لـ VLOOKUP دمج البيانات في جداول مختلفة

حالة الاستخدام الشائعة لـ VLOOKUP هي ضم البيانات من جدولين أو أكثر. على سبيل المثال ، ربما يكون لديك بيانات الطلب في جدول ، وبيانات العميل في جدول آخر وتريد إحضار بعض بيانات العميل إلى جدول الطلبات لتحليلها:

تقوم VLOOKUP بدمج البيانات عن طريق الانضمام إلى الجداول قبل ذلك

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

تقوم VLOOKUP بدمج البيانات عن طريق الانضمام إلى الجداول - بعد

وصلة: مثال على الدمج مع VLOOKUP .

فيديو: كيفية استخدام VLOOKUP لدمج الجداول .

11. يمكن لـ VLOOKUP تصنيف البيانات أو تصنيفها

إذا احتجت في أي وقت إلى تطبيق فئات عشوائية على سجلات البيانات ، فيمكنك القيام بذلك بسهولة باستخدام VLOOKUP ، باستخدام جدول يعمل بمثابة 'المفتاح' لتعيين الفئات.

المثال الكلاسيكي هو التقديرات ، حيث تحتاج إلى تعيين تقدير بناءً على الدرجة:

تستخدم VLOOKUP لتصنيف - تعيين الدرجات

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

ولكن يمكنك أيضًا استخدام VLOOKUP لتعيين فئات عشوائية. في المثال أدناه ، نستخدم VLOOKUP لحساب مجموعة لكل قسم باستخدام جدول صغير (يسمى 'مفتاح') يحدد التجميع.

تستخدم VLOOKUP لتصنيف - تعيين مجموعات عشوائية

12. المراجع المطلقة تجعل VLOOKUP أكثر قابلية للنقل

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

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

تجعل المراجع المطلقة صيغ VLOOKUP أكثر قابلية للنقل

13. تجعل النطاقات المسماة VLOOKUP أسهل في القراءة (وأكثر قابلية للنقل)

النطاقات المطلقة تبدو قبيحة جدًا ، لذا يمكنك جعل صيغ VLOOKUP الخاصة بك أكثر نظافة وأسهل في القراءة عن طريق استبدال المراجع المطلقة بنطاقات محددة ، والتي تكون مطلقة تلقائيًا.

على سبيل المثال ، في مثال بيانات الموظف أعلاه ، يمكنك تسمية خلية الإدخال 'معرف' ثم تسمية البيانات في الجدول 'البيانات' ، يمكنك كتابة الصيغة الخاصة بك على النحو التالي:

تسهل النطاقات المسماة قراءة صيغ VLOOKUP

هذه الصيغة ليست أسهل في القراءة فحسب ، بل إنها سهلة الحمل أيضًا ، نظرًا لأن النطاقات المسماة مطلقة تلقائيًا.

14. قد يؤدي إدخال عمود إلى كسر صيغ VLOOKUP الموجودة

إذا كانت لديك صيغ VLOOKUP موجودة في ورقة عمل ، فقد تنكسر الصيغ إذا قمت بإدراج عمود في الجدول. هذا لأن قيم فهرس العمود المشفر الثابت لا تتغير تلقائيًا عند إدراج الأعمدة أو حذفها.

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

قد يؤدي إدراج عمود في الجدول إلى كسر VLOOKUP

لتجنب هذه المشكلة ، يمكنك حساب فهرس العمود كما هو موضح في النصين التاليين.

15. يمكنك استخدام ROW أو COLUMN لحساب فهرس العمود

إذا كنت من النوع الذي يضايقك أي قدر من التحرير بعد نسخ صيغة ، يمكنك استخدام إما ROW أو COLUMN لإنشاء فهارس أعمدة ديناميكية. إذا كنت تحصل على بيانات من أعمدة متتالية ، فإن هذه الخدعة تتيح لك إعداد صيغة VLOOKUP واحدة ، ثم نسخها عبر أي تغييرات مطلوبة.

على سبيل المثال ، مع بيانات الموظف أدناه ، يمكننا استخدام دالة COLUMN لإنشاء فهرس عمود ديناميكي. بالنسبة للصيغة الأولى في الخلية C3 ، سيعود COLUMN بمفرده 3 (لأن العمود C هو الثالث في ورقة العمل) لذلك نحتاج ببساطة إلى طرح واحد ، ونسخ الصيغة عبر:

مثال على استخدام COLUMN لحساب فهرس العمود لـ VLOOKUP

جميع الصيغ متطابقة دون الحاجة إلى التحرير اللاحق.

الصيغة التي نستخدمها هي:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. استخدم VLOOKUP + MATCH لفهرس عمود ديناميكي بالكامل

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

يسمى هذا أحيانًا بحث ثنائي الاتجاه لأنك تبحث عن كل من الصف والعمود.

من الأمثلة على ذلك البحث عن مبيعات مندوب مبيعات في شهر معين ، أو البحث عن سعر منتج معين من مورد معين.

على سبيل المثال ، افترض أن لديك مبيعات شهرية ، مقسمة حسب مندوب المبيعات:

VLOOKUP بحث ذو اتجاهين - كيف تبحث عن الشهر؟

يمكن لـ VLOOKUP العثور بسهولة على مندوب المبيعات ، ولكن لا توجد طريقة للتعامل مع اسم الشهر تلقائيًا. الحيلة هي استخدام وظيفة MATCH بدلاً من فهرس عمود ثابت.

VLOOKUP بحث ثنائي الاتجاه باستخدام MATCH للحصول على فهرس العمود

لاحظ أننا نقدم تطابقًا نطاقًا يتضمن جميع الأعمدة في الجدول من أجل 'مزامنة' أرقام الأعمدة المستخدمة بواسطة VLOOKUP.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

ملاحظة: ستشاهد غالبًا عمليات بحث ثنائية الاتجاه يتم إجراؤها باستخدام INDEX و MATCH ، وهو نهج يوفر مزيدًا من المرونة وأداء أفضل في مجموعات البيانات الضخمة. انظر كيف في هذا الفيديو السريع: كيفية إجراء بحث ثنائي الاتجاه باستخدام INDEX و MATCH .

17. يسمح VLOOKUP بأحرف البدل للمطابقة الجزئية

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

يوفر Excel حرفين من أحرف البدل: تطابق العلامة النجمية (*) حرفًا واحدًا أو أكثر ، وعلامة الاستفهام (؟) تطابق حرفًا واحدًا.

على سبيل المثال ، يمكنك كتابة علامة النجمة مباشرة في خلية والإشارة إليها كقيمة بحث باستخدام VLOOKUP. في الشاشة أدناه ، أدخلنا 'Mon *' في H3 ، وهو نطاق مسمى يسمى 'val'. يؤدي هذا إلى مطابقة VLOOKUP للاسم 'Monet'.

VLOOKUP مع أحرف البدل - باستخدام علامة النجمة مباشرة

الصيغة في هذه الحالة بسيطة:

 
= VLOOKUP (val,data,1,0)

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

VLOOKUP مع أحرف البدل - يتم ربط العلامة النجمية بقيمة البحث

في هذه الحالة ، نقوم بربط علامة النجمة بقيمة البحث داخل دالة VLOOKUP:

 
= VLOOKUP (val&'*',data,1,0)

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

18. يمكنك اعتراض أخطاء # N / A وعرض رسالة ودية

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

بمجرد أن تبدأ في استخدام VLOOKUP ، فأنت ملزم بالوقوع في الخطأ # N / A ، والذي يحدث عندما يتعذر على VLOOKUP العثور على تطابق.

عد خلية إذا كانت تحتوي على نص

هذا خطأ مفيد ، لأن VLOOKUP يخبرك بوضوح أنه لا يمكنه العثور على قيمة البحث. في هذا المثال ، لا يوجد 'Latte' كمشروب في الجدول ، لذلك ألقى VLOOKUP خطأ # N / A

تظهر VLOOKUP خطأ # N / A

الصيغة في هذه الحالة هي مطابقة تامة قياسية تمامًا:

 
= VLOOKUP (E6,data,2,0)

ومع ذلك ، فإن أخطاء # N / A ليست ممتعة للغاية عند النظر إليها ، لذلك قد ترغب في اكتشاف هذا الخطأ وعرض رسالة أكثر ودية.

أسهل طريقة لتعويض الأخطاء باستخدام VLOOKUP هي التفاف VLOOKUP في الدالة IFERROR. يتيح لك IFERROR 'اكتشاف' أي خطأ وإرجاع نتيجة من اختيارك.

لتعويض هذا الخطأ وعرض رسالة 'غير موجود' بدلاً من الخطأ ، يمكنك ببساطة التفاف الصيغة الأصلية داخل IFERROR وتعيين النتيجة التي تريدها:

VLOOKUP # N / A خطأ محاصر مع IFERROR

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

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. الأرقام كنص يمكن أن تسبب خطأ المطابقة

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

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

تم إدخال الأرقام كنص على خطأ VLOOKUP

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

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

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

 
= VLOOKUP (id&'',planets,2,0)

تم إدخال الأرقام كنص حل خطأ VLOOKUP

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

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. يمكنك استخدام VLOOKUP لاستبدال عبارات IF المتداخلة

أحد الاستخدامات الأكثر إثارة للاهتمام لـ VLOOKUP هو استبدال عبارات IF المتداخلة. إذا قمت ببناء سلسلة من IFs المتداخلة ، فأنت تعلم أنها تعمل بشكل جيد ، لكنها تتطلب القليل من الجدل بين الأقواس. عليك أيضًا توخي الحذر بشأن الترتيب الذي تعمل به ، حتى لا تحدث خطأ منطقيًا.

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

تعيين التقديرات باستخدام صيغة IF متداخلة طويلة

تبدو صيغة IF المتداخلة بالكامل كما يلي:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

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

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

بعد تحديد نطاق مسمى 'مفتاح' لجدول مفتاح التقدير ، تكون صيغة VLOOKUP بسيطة جدًا وتنتج نفس الدرجات مثل صيغة IFs الأصلية المتداخلة:

تعيين الدرجات باستخدام صيغة VLOOKUP بسيطة

باستخدام جدول مفتاح التقدير المسمى 'المفتاح' ، لدينا صيغة VLOOKUP بسيطة جدًا:

 
= VLOOKUP (C5,key,2,TRUE)

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

فيديو: كيفية استبدال IFs المتداخلة بـ VLOOKUP

21. يمكن لـ VLOOKUP معالجة معيار واحد فقط

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

هذا يعني أنه لا يمكنك بسهولة القيام بأشياء مثل البحث عن موظف بالاسم الأخير 'سميث' في 'المحاسبة' ، أو البحث عن موظف استنادًا إلى الاسم الأول والأخير في أعمدة منفصلة.

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

مشكلة معايير متعددة في VLOOKUP - كيف تبحث عن الاسم الأول والأخير؟

أولاً ، أضف عمودًا مساعدًا يربط ببساطة الاسمين الأول والأخير معًا:

معايير متعددة VLOOKUP الخطوة 2 - إضافة عمود مساعد يربط معايير متعددة

ثم قم بتكوين VLOOKUP لاستخدام جدول يتضمن هذا العمود الجديد ، وقم بربط الأسماء الأولى والأخيرة لقيمة البحث:

VLOOKUP معايير متعددة - الخطوة 3 - ضم المعايير لتشكيل قيمة البحث

تبحث صيغة VLOOKUP النهائية عن الأسماء الأولى والأخيرة معًا باستخدام عمود المساعد كمفتاح:

 
= VLOOKUP (C3&D3,data,4,0)

22. اثنين من VLOOKUPS أسرع من VLOOKUP واحد

قد يبدو الأمر مجنونًا تمامًا ، ولكن عندما يكون لديك مجموعة كبيرة من البيانات وتحتاج إلى إجراء تطابق تام ، يمكنك تسريع VLOOKUP كثيرًا عن طريق إضافة VLOOKUP آخر إلى الصيغة!

الخلفية: تخيل أن لديك الكثير من بيانات الطلب ، على سبيل المثال ، أكثر من 10000 سجل وأنك تستخدم VLOOKUP للبحث عن إجمالي الطلب بناءً على معرف الطلب. لذا ، فأنت تستخدم شيئًا مثل هذا:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

يفرض FALSE في النهاية على VLOOKUP إجراء مطابقة تامة. تريد مطابقة تامة لأن هناك احتمال ألا يتم العثور على رقم الطلب. في هذه الحالة ، سيتسبب إعداد المطابقة التامة في قيام VLOOKUP بإرجاع الخطأ # N / A.

تكمن المشكلة في أن المطابقات التامة بطيئة حقًا ، لأن Excel يجب أن يتابع بطريقة خطية عبر جميع القيم حتى يجد تطابقًا أم لا.

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

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

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

تبدو الصيغة النهائية كما يلي:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

لقد تعلمت هذا النهج من تشارلز ويليامز من FastExcel ، الذي لديه مقالة رائعة ومفصلة هنا: لماذا 2 VLOOKUPS أفضل من 1 VLOOKUP .

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

23. يمكن لـ INDEX و MATCH معًا القيام بكل شيء يمكن لـ VLOOKUP القيام به وأكثر من ذلك

إذا كنت تتابع Excel عبر الإنترنت ، فمن المحتمل أن تصطدم بملف VLOOKUP مقابل مناقشة INDEX / MATCH. يمكن أن تصبح الحجة ساخنة بشكل مدهش :)

الجوهر هو: يمكن لـ INDEX + MATCH أن يفعل كل ما يمكن لـ VLOOKUP (و HLOOKUP) القيام به ، بمزيد من المرونة ، على حساب تعقيد أكثر قليلاً. لذا ، فإن أولئك الذين يؤيدون INDEX + MATCH سوف يجادلون (بصراحة شديدة) أنك قد تبدأ أيضًا في تعلم INDEX و MATCH ، لأنه يمنحك مجموعة أدوات أفضل في النهاية.

الحجة ضد INDEX + MATCH هي أنها تتطلب وظيفتين بدلاً من واحدة ، لذا فهي بطبيعتها أكثر تعقيدًا للمستخدمين (خاصة المستخدمين الجدد) للتعلم والإتقان.

سنتان هما أنه إذا كنت تستخدم Excel بشكل متكرر ، فستحتاج إلى معرفة كيفية استخدام INDEX و MATCH. إنه مزيج قوي للغاية.

لكنني أعتقد أيضًا أنه يجب عليك تعلم VLOOKUP ، والتي ستصادفها في كل مكان ، غالبًا في أوراق العمل التي ترثها من الآخرين. في المواقف المباشرة ، ستنجز VLOOKUP المهمة على ما يرام دون أي ضجة.

لمعرفة المزيد حول INDEX و MATCH ، انظر هذا المقال .

المؤلف ديف برونز


^