برنامج Excel VLOOKUP التعليمي للمبتدئين: أمثلة خطوة بخطوة
شارلوت ميلر
ما هو VLOOKUP؟
Vlookup (V تعني 'Vertical') هي وظيفة مدمجة في برنامج Excel تسمح بإنشاء علاقة بين أعمدة Excel المختلفة. بمعنى آخر، يسمح لك بالعثور على (البحث) عن قيمة من عمود واحد من البيانات وإرجاع القيمة المقابلة لها أو القيمة المقابلة لها من عمود آخر.
استخدام VLOOKUP
عندما تحتاج إلى العثور على بعض المعلومات في جدول بيانات كبير، أو تحتاج إلى البحث عن نفس النوع من المعلومات في جدول البيانات، استخدم وظيفة Vlookup.
لنأخذ مثال Vlookup على النحو التالي:
جدول رواتب الشركة والذي يديره الفريق المالي للشركة - في جدول رواتب الشركة، تبدأ بمعلومة معروفة بالفعل (أو يمكن استرجاعها بسهولة). المعلومات التي تعمل بمثابة فهرس.
لذلك كمثال:
تبدأ بالمعلومات المتوفرة بالفعل:
(في هذه الحالة اسم الموظف)
للعثور على المعلومات التي لا تعرفها:
(في هذه الحالة نريد البحث عن راتب الموظف)
جدول بيانات Excel للمثال أعلاه:
في جدول البيانات أعلاه، لمعرفة راتب الموظف الذي نعرفه -
سوف نقوم بإدخال رمز الموظف الذي هو متاح بالفعل.
علاوة على ذلك، عن طريق تطبيق VLOOKUP، سيتم عرض قيمة (راتب الموظف) الخاصة برمز الموظف المقابل.
كيفية استخدام وظيفة VLOOKUP في Excel
فيما يلي دليل خطوة بخطوة حول كيفية تطبيق دالة VLOOKUP في Excel:
الخطوة 1) انتقل إلى الخلية التي تريد عرضها
نحتاج إلى الانتقال إلى الخلية التي تريد عرض راتب الموظف المعين فيها. - (في هذه الحالة، انقر فوق الخلية التي تحتوي على الفهرس "H3")
الخطوة 2) أدخل الدالة VLOOKUP =VLOOKUP ()
أدخل وظيفة VLOOKUP في الخلية أعلاه: ابدأ بـ علامة المساواة التي تشير إلى أنه تم إدخال دالة، 'الكلمة الأساسية VLOOKUP يتم استخدامه بعد علامة المساواة التي تصور وظيفة VLOOKUP =البحث ()
سيحتوي القوسان على مجموعة الوسائط (الوسائط هي جزء من البيانات التي تحتاجها الوظيفة للتنفيذ).
تستخدم VLOOKUP أربع وسيطات أو أجزاء من البيانات:
الخطوة 3) الوسيطة الأولى - أدخل قيمة البحث التي تريد البحث عنها أو البحث عنها.
ستكون الوسيطة الأولى هي مرجع الخلية (كعنصر نائب) للقيمة التي يجب البحث عنها أو قيمة البحث. تشير قيمة البحث إلى البيانات المتوفرة بالفعل أو البيانات التي تعرفها. (في هذه الحالة، يعتبر رمز الموظف بمثابة قيمة البحث بحيث تكون الوسيطة الأولى هي H2، أي أن القيمة التي يجب البحث عنها أو البحث عنها ستكون موجودة في مرجع الخلية 'H2').
الخطوة 4) الوسيطة الثانية – مصفوفة الجدول
يشير إلى مجموعة القيم المطلوب البحث عنها. في Excel، تُعرف كتلة القيم هذه باسم مصفوفة الجدول أو جدول البحث في حالتنا، جدول البحث سيكون من مرجع الخلية B2 إلى E25،أي الكتلة الكاملة حيث سيتم البحث عن القيمة المقابلة.
ملاحظة: يجب أن تكون قيم البحث أو البيانات التي تعرفها موجودة في العمود الأيسر من جدول البحث، أي نطاق الخلايا الخاص بك.
الخطوة 5) الوسيطة الثالثة - بناء جملة VLOOKUP هو column_index_no
يشير إلى مرجع العمود. بمعنى آخر، يقوم بإعلام VLOOKUP بالمكان الذي تتوقع العثور فيه على البيانات التي تريد عرضها. (مرجع العمود هو فهرس العمود في جدول البحث الخاص بالعمود حيث يجب العثور على القيمة المقابلة.) في هذه الحالة، سيكون مرجع العمود هو 4 لأن عمود "راتب الموظف" يحتوي على فهرس 4 وفقًا لجدول البحث.
الخطوة 6) الوسيطة الرابعة - تطابق تام أو تطابق تقريبي
الوسيطة الأخيرة هي البحث عن النطاق. فهو يخبر الدالة VLOOKUP ما إذا كنا نريد المطابقة التقريبية أو المطابقة التامة لقيمة البحث. في هذه الحالة، نريد المطابقة التامة (الكلمة الرئيسية "FALSE").
- خطأ: يشير إلى المطابقة التامة.
- حقيقة: يشير إلى المطابقة التقريبية.
الخطوة 7) اضغط على Enter!
اضغط على "أدخل" لإعلام الخلية بأننا أكملنا الوظيفة. ومع ذلك، ستتلقى رسالة خطأ على النحو التالي لأنه لم يتم إدخال أي قيمة في الخلية H2i.e. لم يتم إدخال رمز الموظف في رمز الموظف مما سيسمح بقيمة البحث.
ومع ذلك، عند إدخال أي رمز موظف في H2، فإنه سيعيد القيمة المقابلة، أي راتب الموظف.
إذن ما حدث باختصار هو أنني أخبرت الخلية من خلال صيغة VLOOKUP أن القيم التي نعرفها موجودة في العمود الأيسر من البيانات، أي أنها تصور العمود الخاص برمز الموظف. الآن عليك أن تنظر إلى جدول البحث الخاص بي أو نطاق الخلايا الخاص بي وفي العمود الرابع على يمين الجدول ابحث عن القيمة في نفس الصف، أي القيمة المقابلة (راتب الموظف) في نفس الصف من راتب الموظف المقابل شفرة.
يوضح المثال أعلاه التطابقات التامة في VLOOKUP، أي الكلمة الأساسية FALSE كمعلمة أخيرة.
VLOOKUP للمطابقات التقريبية (الكلمة الأساسية TRUE كمعلمة أخيرة)
فكر في سيناريو حيث يقوم الجدول بحساب الخصومات للعملاء الذين لا يرغبون في شراء عشرات أو مئات العناصر بالضبط.
كما هو موضح أدناه، قامت بعض الشركات بفرض تخفيضات على كمية من العناصر تتراوح من 1 إلى 10,000:
من غير المؤكد الآن أن العميل يشتري بالضبط مئات أو آلاف العناصر. في هذه الحالة، سيتم تطبيق الخصم وفقًا للمطابقات التقريبية لـ VLOOKUP. بمعنى آخر، لا نريد قصر البحث عن التطابقات على القيم الموجودة في العمود فقط وهي 1، 10، 100، 1000، 10000. فيما يلي الخطوات:
الخطوة 1) انقر على الخلية أين يجب تطبيق وظيفة VLOOKUP، أي مرجع الخلية "I2".
الخطوة 2) أدخل '=VLOOKUP()' في الخلية. بين قوسين أدخل مجموعة الحجج للمثال أعلاه.
الخطوة 3) أدخل الحجج:
الوسيطة 1: أدخل مرجع الخلية للخلية التي سيتم البحث فيها عن القيمة الموجودة عن القيمة المقابلة في جدول البحث.
الخطوة 4) الوسيطة 2: اختر جدول البحث أو مصفوفة الجدول التي تريد أن يبحث VLOOKUP فيها عن القيمة المقابلة. (في هذه الحالة، اختر العمودين الكمية والخصم)
الخطوة 5) الوسيطة 3: ستكون الوسيطة الثالثة هي فهرس العمود الموجود في جدول البحث الذي تريد البحث فيه عن القيمة المقابلة.
الخطوة 5) الوسيطة 4: ستكون الحجة الأخيرة هي الشرط لـ التطابقات التقريبية أو التطابقات التامة. في هذه الحالة، نحن نبحث بشكل خاص عن المطابقات التقريبية (الكلمة الرئيسية الحقيقية).
الخطوة 6) اضغط دخول.' سيتم تطبيق صيغة Vlookup على مرجع الخلية المذكور، وعند إدخال أي رقم في حقل الكمية، سيظهر لك الخصم المفروض بناء على المطابقات التقريبية في VLOOKUP.
NOTE: إذا كنت تريد استخدام TRUE كمعلمة أخيرة، فيمكنك تركها فارغة وستختار TRUE بشكل افتراضي للمطابقات التقريبية.
يتم تطبيق وظيفة Vlookup بين ورقتين مختلفتين موضوعتين في نفس المصنف
دعونا نرى مثالًا مشابهًا لسيناريو الحالة أعلاه. لقد تم تزويدنا بمصنف واحد يحتوي على ورقتين مختلفتين. حيث يتم إعطاء رمز الموظف مع اسم الموظف وتسمية الموظف ورقة أخرى تحتوي على رمز الموظف وراتب الموظف المعني (كما هو موضح أدناه).
ورقة 1:
ورقة 2:
الهدف الآن هو عرض جميع البيانات في صفحة واحدة، أي الورقة 1 على النحو التالي:
يمكن أن تساعدنا VLOOKUP في تجميع كافة البيانات حتى نتمكن من رؤية رمز الموظف واسمه وراتبه في مكان أو ورقة واحدة.
سنبدأ عملنا في الورقة 2 حيث تزودنا هذه الورقة بوسيطين لوظيفة VLOOKUP وهما - يتم إدراج راتب الموظف في الورقة 2 والتي سيتم البحث عنها بواسطة VLOOKUP و مرجع فهرس العمود هو 2 (حسب جدول البحث).
ونعلم أيضًا أننا نريد العثور على راتب الموظف المتوافق مع قانون الموظف.
علاوة على ذلك، تبدأ هذه البيانات في A2 وتنتهي في B25. لذلك سيكون هذا لدينا جدول البحث أو وسيطة صفيف الجدول.
الخطوة 1) انتقل إلى الورقة 1 وأدخل العناوين المعنية كما هو موضح.
الخطوة 2) انقر فوق الخلية التي تريد تطبيق وظيفة VLOOKUP فيها. في هذه الحالة، ستكون الخلية بجوار راتب الموظف مع مرجع الخلية "F3".
أدخل وظيفة Vlookup: =VLOOKUP ().
الخطوة 3) الوسيطة 1: أدخل مرجع الخلية الذي يحتوي على القيمة المطلوب البحث عنها في جدول البحث. في هذه الحالة، يكون "F2" هو الفهرس المرجعي الذي سيحتوي على رمز الموظف لمطابقة راتب الموظف المقابل في جدول البحث.
الخطوة 4) الوسيطة 2: في الوسيطة الثانية، ندخل جدول البحث أو مصفوفة الجدول. ومع ذلك، في هذه الحالة، لدينا جدول البحث الموجود في ورقة أخرى في نفس المصنف. لذلك، لبناء علاقة نحتاج إلى إدخال عنوان جدول البحث على هيئة ورقة2!A2:B25 - (يشير A2:B25 إلى جدول البحث في الورقة 2)
الخطوة 5) الوسيطة 3: تشير الوسيطة الثالثة إلى فهرس العمود الموجود في جدول البحث حيث يجب أن تكون القيم موجودة.
الخطوة 6) الوسيطة 4: تشير الوسيطة الأخيرة إلى التطابقات التامة (خطأ) or التطابقات التقريبية (صحيح). في هذه الحالة، نريد استرداد المطابقات الدقيقة لراتب الموظف.
الخطوة 7) اضغط على Enter وعندما تقوم بإدخال رمز الموظف في الخلية، سيتم إرجاعك براتب الموظف المقابل لرمز الموظف هذا.
وفي الختام
تشرح السيناريوهات الثلاثة المذكورة أعلاه عمل وظائف VLOOKUP. يمكنك اللعب باستخدام المزيد من المثيلات. VLOOKUP هي ميزة مهمة موجودة في مايكروسوفت اكسل مما يسمح لك بإدارة البيانات بشكل أكثر كفاءة.
تحقق أيضًا من برنامج Excel التعليمي بتنسيق PDF: - اضغط هنا