كيفية استخدام VLOOKUP في Excel

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

VLOOKUP هي وظيفة Excel . ستفترض هذه المقالة أن القارئ لديه بالفعل فهم عابر لوظائف Excel ، ويمكنه استخدام الوظائف الأساسية مثل SUM و AVERAGE و TODAY. في الاستخدام الأكثر شيوعًا ، تعد VLOOKUP وظيفة قاعدة بيانات ، مما يعني أنها تعمل مع جداول قاعدة البيانات - أو بشكل أكثر بساطة ، قوائم بالأشياء في ورقة عمل Excel. أي نوع من الأشياء؟ حسنًا ، أي نوع من الأشياء. قد يكون لديك ورقة عمل تحتوي على قائمة بالموظفين أو المنتجات أو العملاء أو الأقراص المضغوطة في مجموعة الأقراص المضغوطة أو النجوم في سماء الليل. لا يهم حقًا.

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

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

أصعب جزء في استخدام VLOOKUP هو فهم الغرض منه بالضبط. لذلك دعونا نرى ما إذا كان يمكننا توضيح ذلك أولاً:

يقوم VLOOKUP باسترداد المعلومات من قاعدة بيانات / قائمة بناءً على مثيل مزود بالمعرف الفريد.

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

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

لنقم بإنشاء مثال على ذلك: نموذج فاتورة يمكننا إعادة استخدامه مرارًا وتكرارًا في شركتنا الوهمية.

أولاً نبدأ Excel ، وننشئ لأنفسنا فاتورة فارغة:

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

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

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

لذلك ، أنشأنا قاعدة بيانات منتجاتنا ، والتي تبدو كالتالي:

لاختبار صيغة VLOOKUP التي نحن على وشك كتابتها ، نقوم أولاً بإدخال رمز عنصر صالح في الخلية A11 من فاتورتنا الفارغة:

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

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

يظهر مربع يسمح لنا بتحديد أي من الوظائف المتوفرة في Excel.

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

يظهر مربع وسيطات الوظيفة ، ويطلب منا تقديم جميع الوسائط (أو المعلمات ) اللازمة لإكمال وظيفة VLOOKUP. يمكنك التفكير في هذا المربع كوظيفة تطرح علينا الأسئلة التالية:

1. ما هو المعرف الفريد الذي تبحث عنه في قاعدة البيانات؟
2. أين هي قاعدة البيانات؟
3. أي جزء من المعلومات من قاعدة البيانات ، يرتبط بالمعرف الفريد ، ترغب في استرداده من أجلك؟

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

سنكمل الحجج بالترتيب من أعلى إلى أسفل.

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

انقر فوق أيقونة المحدد على يمين الوسيطة الأولى:

ثم انقر مرة واحدة على الخلية التي تحتوي على رمز العنصر (A11) ، واضغط على Enter :

يتم إدراج قيمة "A11" في الوسيطة الأولى.

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

حدد الآن موقع قاعدة البيانات / القائمة وحدد القائمة بأكملها - وليس بما في ذلك سطر العنوان. في مثالنا ، توجد قاعدة البيانات في ورقة عمل منفصلة ، لذلك ننقر أولاً على علامة تبويب ورقة العمل هذه:

بعد ذلك نختار قاعدة البيانات بأكملها ، وليس بما في ذلك سطر العنوان:

... واضغط على Enter . يتم إدخال نطاق الخلايا الذي يمثل قاعدة البيانات (في هذه الحالة "قاعدة بيانات المنتج"! A2: D7 ") تلقائيًا بالنسبة لنا في الوسيطة الثانية.

نحتاج الآن إلى إدخال المعامل الثالث ، Col_index_num . نستخدم هذه الوسيطة لنحدد لـ VLOOKUP أي جزء من المعلومات من قاعدة البيانات ، يرتبط برمز العنصر الخاص بنا في A11 ، والذي نرغب في إعادته إلينا. في هذا المثال بالذات ، نرغب في إعادة وصف العنصر إلينا. إذا نظرت إلى ورقة عمل قاعدة البيانات ، ستلاحظ أن عمود "الوصف" هو العمود الثاني في قاعدة البيانات. هذا يعني أنه يجب علينا إدخال قيمة "2" في مربع Col_index_num :

من المهم ملاحظة أننا لا ندخل "2" هنا لأن عمود "الوصف" موجود في العمود B في ورقة العمل هذه. إذا بدأت قاعدة البيانات في العمود K من ورقة العمل ، فسنظل ندخل "2" في هذا الحقل لأن عمود "الوصف" هو العمود الثاني في مجموعة الخلايا التي حددناها عند تحديد "Table_array".

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

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

إذا كان العمود الأول من قاعدة البيانات لا فرزها، أو انها مرتبة تنازليا، فإنك يجب إدخال قيمة خاطئة في هذه الحجة

نظرًا لعدم فرز العمود الأول من قاعدة البيانات الخاصة بنا ، فإننا ندخل خطأ في هذه الوسيطة:

هذا هو! لقد أدخلنا جميع المعلومات المطلوبة لـ VLOOKUP لإرجاع القيمة التي نحتاجها. انقر فوق الزر " موافق " ولاحظ أنه تم إدخال الوصف المقابل لرمز العنصر "R99245" بشكل صحيح في الخلية B11:

تبدو الصيغة التي تم إنشاؤها لنا كما يلي:

إذا أدخلنا رمز عنصر مختلفًا في الخلية A11 ، فسنبدأ في رؤية قوة وظيفة VLOOKUP: تتغير خلية الوصف لتتطابق مع رمز العنصر الجديد:

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

... وستبدو الصيغة كما يلي:

لاحظ أن الاختلاف الوحيد بين الصيغتين هو أن الوسيطة الثالثة ( Col_index_num ) قد تغيرت من "2" إلى "3" (لأننا نريد استرداد البيانات من العمود الثالث في قاعدة البيانات).

إذا قررنا شراء عنصرين من هذه العناصر ، فسنقوم بإدخال "2" في الخلية D11. سنقوم بعد ذلك بإدخال صيغة بسيطة في الخلية F11 للحصول على إجمالي السطر:

= D11 * E1

... الذي يشبه هذا ...

استكمال نموذج الفاتورة

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

قالب الفاتورة الخاص بنا لم يكتمل بعد. ولإكماله نقوم بما يلي:

1. سنقوم بإزالة نموذج رمز العنصر من الخلية A11 و "2" من الخلية D11. سيؤدي هذا إلى عرض صيغ VLOOKUP المنشأة حديثًا لرسائل الخطأ:

   
   

   
   يمكننا معالجة ذلك من خلال الاستخدام الحكيم لوظائف Excel IF () و ISBLANK () . نغير معادلتنا من ...      = VLOOKUP (A11، 'Product Database'! A2: D7،2، FALSE) ... إلى هذا ... = IF (ISBLANK (A11)، ""، VLOOKUP (A11، 'Product Database'! A2 : D7،2، FALSE))

   
   

2. سنقوم بنسخ الصيغ في الخلايا B11 و E11 و F11 وصولاً إلى باقي صفوف عناصر الفاتورة. لاحظ أنه إذا قمنا بذلك ، فلن تشير الصيغ الناتجة بشكل صحيح إلى جدول قاعدة البيانات. يمكننا إصلاح ذلك عن طريق تغيير مراجع الخلايا لقاعدة البيانات إلى مراجع خلية مطلقة . بدلاً من ذلك - بل والأفضل - يمكننا إنشاء اسم نطاق لقاعدة بيانات المنتج بالكامل (مثل "المنتجات") ، واستخدام اسم النطاق هذا بدلاً من مراجع الخلايا. ستتغير الصيغة من هذا ...      = IF (ISBLANK (A11)، ""، VLOOKUP (A11، 'Product Database'! A2: D7،2، FALSE)) ... إلى هذا ...       = IF (ISBLANK (A11)، " ، VLOOKUP (A11، Products، 2، FALSE)) … وبعد ذلك انسخ الصيغ لأسفل إلى باقي صفوف صنف الفاتورة.
3. كنا على الارجح "قفل" الخلايا التي تحتوي على الصيغ لدينا (أو بالأحرى فتح و غيرها من الخلايا)، ومن ثم حماية ورقة العمل، وذلك للتأكد من أن لدينا صيغ بعناية ليست الكتابة عن طريق الخطأ عندما يأتي شخص ما لملء في الفاتورة.
4. سنحفظ الملف كقالب ، حتى يمكن إعادة استخدامه من قبل كل فرد في شركتنا

إذا كنا نشعر بالذكاء حقًا ، فسننشئ قاعدة بيانات لجميع عملائنا في ورقة عمل أخرى ، ثم نستخدم معرّف العميل الذي تم إدخاله في الخلية F5 لملء اسم العميل وعنوانه تلقائيًا في الخلايا B6 و B7 و B8.

إذا كنت ترغب في التدرب على VLOOKUP ، أو مجرد رؤية نموذج الفاتورة الناتج ، فيمكن تنزيله من هنا.