برنامج تعليمي لبرنامج Excel VLOOKUP للمبتدئين
شارلوت ميلر
⚡ ملخص ذكي
يشرح هذا الدليل التعليمي لدالة VLOOKUP في برنامج Excel كيفية بحث هذه الدالة في العمود الأول من الجدول وإرجاع قيمة مطابقة من عمود آخر. ويغطي هذا الدليل بناء الجملة، والمطابقات التامة والتقريبية، والبحث بين أوراق العمل المختلفة، والأخطاء الشائعة، والبديل الحديث لها، دالة XLOOKUP.
ما هو VLOOKUP؟
دالة VLOOKUP (حيث يشير الحرف V إلى العمودي) هي دالة مدمجة في برنامج Excel تُستخدم لإنشاء علاقة بين الأعمدة في جدول البيانات. فهي تتيح لك البحث عن قيمة في عمود ما وإرجاع القيمة المقابلة لها من عمود آخر في نفس الصف.
صيغة دالة VLOOKUP ومعاملاتها
قبل استخدام دالة VLOOKUP، من المفيد فهم بنية الصيغة. تأخذ هذه الدالة أربعة وسائط وتتبع نمطًا ثابتًا في جميع إصدارات برنامج Excel.
- ابحث عن القيمة — القيمة التي تريد العثور عليها (مرجع خلية أو قيمة حرفية).
- table_array — نطاق الخلايا التي تحتوي على عمود البحث وعمود الإرجاع.
- col_index_num — رقم العمود في مصفوفة الجدول الذي سيتم إرجاع القيمة منه (1 هو العمود الأيسر).
- مجموعة البحث — FALSE للمطابقة التامة، TRUE (أو محذوفة) للمطابقة التقريبية على البيانات المصنفة.
هام: يجب أن تكون قيمة البحث موجودة في العمود الأيسر من مصفوفة الجدول، ويقوم VLOOKUP بالبحث من اليسار إلى اليمين فقط.
استخدام VLOOKUP
عندما تحتاج إلى العثور على معلومات محددة في جدول بيانات كبير، أو استرداد نفس النوع من القيم بشكل متكرر، فإن VLOOKUP يوفر وقتًا كبيرًا مقارنة بالتصفية اليدوية.
النظر في جدول رواتب الشركة يتم الاحتفاظ بها بواسطة الفريق المالي. تبدأ بمعلومة معروفة - فهرس - وتستخدم دالة VLOOKUP لجلب القيمة المجهولة.
على سبيل المثال، أنت تعرف اسم الموظف بالفعل:
وتريد البحث عن راتب الموظف:
جدول بيانات إكسل للمثال المذكور أعلاه:
لإيجاد راتب الموظف المجهول، ندخل بيانات الموظف. Code هذا متاح بالفعل.
باستخدام دالة VLOOKUP، يتم تحديد قيمة الراتب المقابلة لذلك الموظف. Code يظهر تلقائيا.
كيفية استخدام وظيفة VLOOKUP في Excel
اتبع هذا الدليل خطوة بخطوة لتطبيق دالة VLOOKUP في برنامج Excel:
الخطوة 1) انتقل إلى الخلية المستهدفة
انقر على الخلية التي تريد أن يظهر فيها راتب الموظف المحدد - في هذا المثال، الخلية H3.
الخطوة الثانية) أدخل دالة VLOOKUP =VLOOKUP()
اكتب الدالة في الخلية. ابدأ بعلامة يساوي (التي تخبر برنامج Excel أن الصيغة تتبعها) ثم الكلمة المفتاحية VLOOKUP: =VLOOKUP().
تحتوي الأقواس على مجموعة الوسائط (أجزاء البيانات التي تحتاجها الدالة).
تتطلب دالة VLOOKUP أربعة وسائط:
الخطوة 3) الوسيط الأول — قيمة البحث
الوسيط الأول هو مرجع الخلية للقيمة التي تريد البحث عنها. في هذه الحالة، الموظف Code هي قيمة البحث، لذا فإن الوسيط الأول هو H2 - الخلية التي يجب أن يتطابق محتواها مع Excel.
الخطوة 4) الوسيط الثاني — مصفوفة الجدول
يشير هذا إلى مجموعة القيم المراد البحث عنها، والمعروفة في برنامج إكسل باسم مصفوفة الجدول أو جدول بحث. في مثالنا، يعمل جدول البحث من B2 إلى E25.
NOTE: يجب أن يكون عمود البحث هو العمود الأيسر في مصفوفة الجدول الخاصة بك.
الخطوة 5) الوسيط الثالث — رقم فهرس العمود
يُحدد هذا الأمر لدالة VLOOKUP العمود الذي يحتوي على القيمة المُعادة داخل مصفوفة الجدول. يقع راتب الموظف في العمود الرابع، لذا فإن فهرس العمود هو 4.
الخطوة السادسة) الحجة الرابعة — تطابق تام أو تقريبي
الوسيط الأخير هو علامة البحث عن النطاق. وهي تتحكم فيما إذا كانت دالة VLOOKUP تُرجع تطابقًا تامًا أم تطابقًا تقريبيًا. هنا نريد تطابقًا تامًا (خطأ).
- خاطئة — تطابق تام.
- الحقيقة — تطابق تقريبي.
الخطوة 7) اضغط على Enter
اضغط على مفتاح الإدخال (Enter) لإكمال الصيغة. ستظهر لك رسالة خطأ في البداية لعدم وجود موظف. Code لم يتم إدخالها في النصف الثاني من العام بعد.
بمجرد إدخال موظف صالح Code في الخلية H2، تعرض الخلية راتب الموظف المقابل.
باختصار، تخبر الصيغة برنامج إكسل أن القيم المعروفة موجودة في العمود الأيسر من البيانات (الموظف). Codeثم تقوم دالة VLOOKUP بمسح الجدول وإرجاع قيمة العمود الرابع في الصف المطابق - وهو راتب الموظف.
تناول هذا المثال التطابقات التامة (الكلمة المفتاحية FALSE). يشرح القسم التالي التطابقات التقريبية.
VLOOKUP للمطابقات التقريبية (الكلمة الأساسية TRUE كمعلمة أخيرة)
تخيل سيناريو يقوم فيه جدول بحساب الخصومات للعملاء الذين لا يشترون عشرات أو مئات من المنتجات بالضبط.
كما هو موضح أدناه، تطبق إحدى الشركات خصومات على الكميات التي تتراوح من 1 إلى 10,000:
نادرًا ما يشتري العميل 100 أو 1,000 وحدة بالضبط. يتيح وضع المطابقة التقريبية لدالة VLOOKUP إيجاد أقرب قيمة أقل بدلًا من اشتراط رقم دقيق. الخطوات:
الخطوة 1) انقر على الخلية التي ستُدرج فيها دالة VLOOKUP — مرجع الخلية I2.
الخطوة 2) أدخل الصيغة =VLOOKUP() في الخلية وأضف الوسائط داخل الأقواس.
الخطوة 3) الحجة 1: أدخل مرجع الخلية التي يجب مطابقة قيمتها مع جدول البحث.
الخطوة 4) الحجة 2: حدد جدول البحث — هنا، عمودا الكمية والخصم.
الخطوة 5) الحجة 3: أدخل رقم العمود في جدول البحث الذي تريد إرجاع القيمة المطابقة منه.
الخطوة 6) الحجة 4: اضبط الوسيط الأخير على الحقيقة للحصول على نتائج تقريبية.
الخطوة 7) اضغط على مفتاح الإدخال (Enter). سيتم تطبيق الصيغة الآن على الخلية. عند إدخال أي كمية، سيعرض برنامج Excel نطاق الخصم بناءً على القيمة التقريبية.
NOTE: إذا تركت الوسيط الرابع فارغًا، فسيستخدم Excel القيمة الافتراضية TRUE (مطابقة تقريبية). وللحصول على مطابقة تقريبية، يجب فرز عمود البحث بترتيب تصاعدي.
يتم تطبيق وظيفة Vlookup بين ورقتين مختلفتين موضوعتين في نفس المصنف
والآن، لنفترض وجود مصنف يحتوي على ورقتين. الورقة الأولى تسرد الموظفين Codeالاسم والوظيفة؛ الصفحة 2 تسرد الموظف Code وراتب الموظف.
ورقة 1:
ورقة 2:
الهدف هو دمج جميع البيانات الموجودة في الورقة 1، كما هو موضح أدناه:
يمكن لدالة VLOOKUP تجميع البيانات بحيث يتمكن الموظف من Codeيظهر الاسم والراتب معاً في ورقة واحدة.
نبدأ بالورقة الثانية لأنها توفر حجتين - عمود راتب الموظف موجود هنا، و رقم العمود هو 2.
نريد إيجاد الراتب الذي يناسب كل موظف Code.
تمتد البيانات من A2 إلى B25 - وهذا هو مصفوفة الجدول لدينا.
الخطوة 1) انتقل إلى الورقة 1 وأدخل العناوين الموضحة.
الخطوة 2) انقر على الخلية المجاورة لـ "راتب الموظف" - الخلية F3 - حيث ستوضع صيغة VLOOKUP.
أدخل دالة VLOOKUP: =VLOOKUP().
الخطوة 3) الحجة 1: أدخل F2 — الخلية التي تحتوي على الموظف Code للمطابقة في جدول البحث.
الخطوة 4) الحجة 2: يوجد جدول البحث في ورقة العمل الأخرى، لذا قم بالإشارة إليه باستخدام اسم ورقة العمل: الورقة 2!A2:B25.
الخطوة 5) الحجة 3: أدخل رقم العمود داخل جدول البحث الذي يحتوي على القيمة المُعادة.
الخطوة 6) الحجة 4: استخدم FALSE للمطابقة التامة لأننا نريد الراتب المحدد الذي يطابق كل موظف Code.
الخطوة 7) اضغط على زر الإدخال. عند إدخال اسم موظف Code، تقوم الخلية بإرجاع الراتب المقابل المستخرج من الورقة 2.
أخطاء شائعة في دالة VLOOKUP وحلولها
حتى المستخدمون ذوو الخبرة يواجهون أخطاء في دالة VLOOKUP. إليكم أكثر الأخطاء شيوعًا وحلولها السريعة:
- # N / A — لم تتمكن دالة VLOOKUP من العثور على قيمة البحث. تحقق من وجود مسافات زائدة، أو أنواع بيانات غير متطابقة (أرقام مخزنة كنص)، أو من وجود القيمة بالفعل في العمود الأول من مصفوفة الجدول.
- #REF! — قيمة col_index_num أكبر من عدد الأعمدة في table_array. قم بتخفيض فهرس العمود أو توسيع النطاق.
- #القيمة! — قيمة col_index_num أقل من 1 أو أن أحد الوسائط غير صالح. تحقق من صيغة الصيغة.
- تم إرجاع نتيجة خاطئة — إذا كانت الوسيطة الرابعة صحيحة أو محذوفة، فإن عمود البحث غير مُرتب. قم بتغييرها إلى خطأ أو رتب العمود تصاعديًا.
- المراجع المغلقة — عند نسخ صيغة إلى أسفل، استخدم المراجع المطلقة (على سبيل المثال، $B$2:$E$25) حتى لا ينحرف table_array.
VLOOKUP مقابل XLOOKUP: أيهما يجب عليك استخدامه؟
Microsoft تم تقديم دالة XLOOKUP في Microsoft يُعدّ برنامج Office 365 وExcel 2021 بديلاً عصرياً لدالة VLOOKUP. فهو يزيل العديد من قيود VLOOKUP، وهو الآن الخيار الموصى به في الإصدارات المدعومة.
| الميزات | VLOOKUP | XLOOKUP |
|---|---|---|
| اتجاه البحث | من اليسار إلى اليمين فقط | أي اتجاه (يسار، يمين، أعلى، أسفل) |
| نوع المطابقة الافتراضي | تقريبي (صحيح) | دقيق |
| معالجة حالة عدم العثور على المنتج | المرتجعات غير متوفر | وسيطة if_not_found المدمجة |
| فهرس الأعمدة | رقم مُبرمج مسبقًا | قم بالإشارة إلى نطاق عمود الإرجاع |
| التوفر | جميع إصدارات الإكسل | Microsoft 365، إكسل 2021، إكسل للويب |
متى تختار دالة VLOOKUP؟ يجب تشغيل المصنف في Excel 2019 أو إصدار أقدم، أو أنك تحتفظ بصيغ قديمة. متى تختار دالة XLOOKUP؟ أنت بصدد إنشاء مصنفات جديدة في برنامج Excel الحديث وترغب في استخدام دوال البحث اليسرى، ومعالجة الأخطاء بشكل أفضل، والمطابقة التامة افتراضيًا. تعرف على المزيد حول دوال البحث في دروس إكسل سلسلة.
خاتمة
توضح السيناريوهات الثلاثة أعلاه كيفية عمل دالة VLOOKUP للبحث عن التطابقات التامة، والتطابقات التقريبية، والمراجع بين الجداول. تدرب على مجموعات البيانات الخاصة بك لتعزيز إتقانك لها. لا تزال دالة VLOOKUP ميزة مهمة في مايكروسوفت اكسل لإدارة البيانات بكفاءة، وتوسع دالة XLOOKUP مجموعة الأدوات هذه في برنامج Excel الحديث.
