في هذا العالم المعتمد على البيانات ، يحتاج المرء إلى أدوات مختلفة لإدارة البيانات. البيانات في الوقت الفعلي ضخمة وستكون عملية جلب التفاصيل المتعلقة ببعض أجزاء معينة من البيانات بالتأكيد مهمة شاقة ولكن مع VLOOKUP في اكسل ، يمكن تحقيق هذه المهمة بسطر واحد من الأوامر. في هذه المقالة ، ستتعرف على أحد الأمور المهمة وظائف Excel أي وظيفة VLOOKUP.
قبل الانتقال ، دعنا نلقي نظرة سريعة على جميع الموضوعات التي تمت مناقشتها هنا:
- ما هو VLOOKUP في Excel؟
- كيف يعمل؟
- تطابق تام
- تطابق تقريبي
- المباراة الأولى
- حساسية القضية
- أخطاء
- بحث ثنائي الاتجاه
- استخدام أحرف البدل
- جداول بحث متعددة
ما هو VLOOKUP في Excel؟
في Excel ، VLOOKUP هو ملف وظيفة مدمجة يُستخدم للبحث وجلب بيانات محددة من ورقة Excel. يرمز V إلى Vertical ومن أجل استخدام وظيفة VLOOKUP في Excel ، يجب ترتيب البيانات عموديًا. تكون هذه الوظيفة مفيدة للغاية عندما يكون لديك كمية هائلة من البيانات وسيكون من المستحيل عمليًا البحث يدويًا عن بعض البيانات المحددة.
كيف يعمل؟
تأخذ الدالة VLOOKUP قيمة ، أي قيمة البحث وتبدأ في البحث عنها في العمود الموجود في أقصى اليسار. عند العثور على التكرار الأول لقيمة البحث ، يبدأ في التحرك مباشرة في هذا الصف ويعيد قيمة من العمود الذي تحدده. يمكن استخدام هذه الوظيفة لإرجاع المطابقات التامة والتقريبية (المطابقة الافتراضية هي مطابقة تقريبية).
بناء الجملة:
تكون صيغة هذه الوظيفة كما يلي:
مثال كتلة ثابتة في جافا
VLOOKUP (lookup_value ، table_array ، col_index_num ، [range_lookup])
أين،
- ابحث عن القيمة هي القيمة التي يجب البحث عنها في العمود الأول من الجدول المحدد
- table_index هو الجدول الذي سيتم جلب البيانات منه
- col_index_num هو العمود الذي سيتم جلب القيمة منه
- مجموعة البحث هي قيمة منطقية تحدد ما إذا كانت قيمة البحث يجب أن تكون مطابقة كاملة أم تطابقًا تقريبيًا ( صحيح سوف تجد أقرب تطابق خاطئة يتحقق من المطابقة التامة)
تطابق تام:
عندما تريد أن تبحث وظيفة VLOOKUP عن تطابق تام لقيمة البحث ، فسيتعين عليك تعيين مجموعة البحث القيمة إلى FALSE. ألق نظرة على المثال التالي وهو جدول يتكون من تفاصيل الموظف:
في حال كنت ترغب في البحث عن تعيين أي من هؤلاء الموظفين ، يمكنك القيام بما يلي:
- حدد الخلية حيث تريد عرض الإخراج ثم اكتب علامة '='
- استخدم وظيفة VLOOKUP وقم بتوفير ملف ابحث عن القيمة (هنا سيكون رقم تعريف الموظف)
- ثم قم بتمرير المعلمات الأخرى ، مثل table_array ، col_index_num وضبط مجموعة البحث القيمة إلى FALSE
- لذلك ، ستكون الوظيفة ومعلماتها: = VLOOKUP (104، A1: D8، 3، FALSE)
تبدأ وظيفة VLOOKUP في البحث عن معرف الموظف 104 ثم تتحرك نحو اليمين في الصف حيث توجد القيمة. يستمر حتى col_index_num ويعيد القيمة الموجودة في هذا الموضع.
تطابق تقريبي:
تتيح لك هذه الميزة الخاصة بوظيفة VLOOKUP استرداد القيم حتى عندما لا يكون لديك تطابق تام مع loopup_value. كما ذكرنا سابقًا ، من أجل جعل VLOOKUP يبحث عن تطابق تقريبي ، ستحتاج إلى ضبط مجموعة البحث القيمة إلى TRUE. ألق نظرة على المثال التالي حيث يتم تعيين العلامات جنبًا إلى جنب مع درجاتها والفصل الذي تنتمي إليه.
- تمامًا مثلما فعلت مع المطابقة التامة ، اتبع نفس الخطوات
- بدلاً من قيمة range_lookup ، استخدم TRUE بدلاً من FALSE
- لذلك ، ستكون الوظيفة مع معلماتها: = VLOOKUP (55، A12: C15، 3، TRUE)
في الجدول الذي تم فرزه بترتيب تصاعدي ، يبدأ VLOOKUP في البحث عن تطابق تقريبي ويتوقف عند القيمة الأكبر التالية الأصغر من قيمة البحث التي أدخلتها. ثم ينتقل إلى اليمين في هذا الصف ويعيد القيمة من العمود المحدد. في المثال أعلاه ، تكون قيمة البحث 55 وقيمة البحث التالية الأكبر في العمود الأول هي 40. لذلك ، الإخراج هو الدرجة الثانية.
المباراة الأولى:
في حالة وجود جدول يتكون من قيم بحث متعددة ، يتوقف VLOOKUP عند المطابقة الأولى له ويسترد قيمة من هذا الصف في العمود المحدد.
انظر للصورة ادناه:
يتم تكرار المعرف 105 وعندما يتم تحديد قيمة البحث كـ 105 ، قام VLOOKUP بإرجاع القيمة من الصف الذي يحتوي على أول ظهور لقيمة البحث.
حساسية القضية:
وظيفة VLOOKUP غير حساسة لحالة الأحرف. في حالة وجود قيمة بحث في حالة الأحرف الكبيرة وكانت القيمة الموجودة في الجدول صغيرة ، فستظل VLOOKUP تجلب القيمة من الصف الذي توجد فيه القيمة. انظر للصورة ادناه:
كما ترى ، القيمة التي حددتها كمعامل هي 'RAFA' بينما القيمة الموجودة في الجدول هي 'Rafa' لكن VLOOKUP لا تزال ترجع القيمة المحددة. إذا كان لديك تطابق تام حتى مع الحالة ، فستظل VLOOKUP تُرجع التطابق الأول لقيمة البحث بغض النظر عن الحالة المستخدمة. انظر للصورة ادناه:
الأخطاء:
من الطبيعي أن نواجه أخطاء كلما استخدمنا الوظائف. وبالمثل ، يمكن أن تواجه أخطاء عند استخدام وظيفة VLOOKUP أيضًا وبعض الأخطاء الشائعة هي:
- #اسم
- # غير متاح
- # REF
- #القيمة
خطأ #NAME:
هذا الخطأ في الأساس هو إعلامك بأنك ارتكبت خطأ ما في بناء الجملة. لتجنب الأخطاء النحوية ، من الأفضل استخدام معالج الوظائف الذي يوفره Excel لكل وظيفة. يساعدك معالج الوظائف بالمعلومات المتعلقة بكل معلمة ونوع القيم التي تريد إدخالها. انظر للصورة ادناه:
كما ترى ، يعلمك معالج الوظيفة بإدخال أي نوع من القيمة بدلاً من المعلمة lookup_value كما يقدم وصفًا موجزًا لها. وبالمثل ، عند تحديد المعلمات الأخرى ، سترى معلومات تتعلق بها أيضًا.
# لا يوجد خطأ:
يتم إرجاع هذا الخطأ في حالة عدم العثور على تطابق لقيمة البحث المحددة. على سبيل المثال ، إذا أدخلت 'AFA' بدلاً من 'RAFA' ، فسأحصل على خطأ # N / A.
من أجل تحديد بعض رسائل الخطأ للخطأين أعلاه ، يمكنك الاستفادة من وظيفة IFNA. فمثلا:
برنامج Microsoft SQL التعليمي للمبتدئين
# خطأ REF:
تمت مصادفة هذا الخطأ عند إعطاء إشارة إلى عمود غير متوفر في الجدول.
#VALUE خطأ:
يتم مواجهة هذا الخطأ عند وضع قيم خاطئة للمعلمات أو فقدان بعض المعلمات الإلزامية.
بحث ثنائي الاتجاه:
يشير البحث ثنائي الاتجاه إلى جلب قيمة من جدول ثنائي الأبعاد من أي خلية في الجدول المرجعي. من أجل إجراء بحث ثنائي الاتجاه باستخدام VLOOKUP ، ستحتاج إلى استخدام وظيفة MATCH معها.
صيغة MATCH كما يلي:
MATCH (lookup_value ، lookup_array ، match_type)
- ابحث عن القيمة هي القيمة التي سيتم البحث عنها
- lookup_array هو نطاق الخلايا الذي يتكون من قيم البحث
- نوع مباراة يمكن أن يكون رقمًا ، أي إما 0 أو 1 أو -1 يمثل مطابقة تامة ، وأقل من وأكبر من على التوالي
بدلاً من استخدام القيم المشفرة مع VLOOKUP ، يمكنك جعلها تجاوزًا ديناميكيًا في مراجع الخلايا. تأمل المثال التالي:
كما ترى في الصورة أعلاه ، تأخذ دالة VLOOKUP مرجع الخلية كـ F6 لقيمة البحث ويتم تحديد قيمة فهرس العمود بواسطة دالة MATCH. عند إجراء تغييرات على أي من هذه القيم ، سيتغير الناتج أيضًا وفقًا لذلك. ألق نظرة على الصورة أدناه حيث قمت بتغيير القيمة الموجودة في F6 من Chris إلى Leo وتم أيضًا تحديث الإخراج وفقًا لذلك:
العثور على أعلى قيمة في مجموعة جافا
في حالة تغيير قيمة G5 ، أو كلاهما F6 و G5 ، ستعمل هذه الصيغة وفقًا لذلك لعرض النتائج المقابلة.
يمكنك أيضًا إنشاء قوائم منسدلة لتسهيل مهمة تغيير القيم. في المثال أعلاه ، يجب أن يتم ذلك على F6 و G5. إليك كيفية إنشاء قوائم منسدلة:
- حدد البيانات من علامة تبويب الشريط
- من مجموعة أدوات البيانات ، حدد التحقق من صحة البيانات
- افتح جزء الإعدادات ومن السماح ، حدد قائمة
- حدد مصفوفة قائمة المصدر
إليك كيف تبدو بمجرد إنشاء قائمة منسدلة:
باستخدام أحرف البدل:
إذا كنت لا تعرف قيمة البحث الدقيقة ولكن جزء منها فقط ، فيمكنك الاستفادة من أحرف البدل. في Excel ، يمثل الرمز '*' حرف بدل. يُعلم هذا الرمز Excel بأنه يجب البحث عن التسلسل الذي يأتي قبل أو بعد أو فيما بعد ، ويمكن أن يكون هناك أي عدد من الأحرف قبلها أو بعدها. على سبيل المثال ، في الجدول الذي قمت بإنشائه ، إذا أدخلت 'erg' مع أحرف البدل على كلا الجانبين ، فسيعيد VLOOKUP إخراج 'Sergio' كما هو موضح أدناه:
جداول بحث متعددة:
في حالة وجود جداول بحث متعددة ، يمكنك استخدام الدالة IF جنبًا إلى جنب معها للنظر في أي من الجدولين بناءً على شرط معين. على سبيل المثال ، إذا كان هناك جدول يحتوي على بيانات لمتجرين سوبر ماركت وتحتاج إلى معرفة الربح الذي حققه كل منهما بناءً على المبيعات ، فيمكنك القيام بما يلي:
قم بإنشاء الجدول الرئيسي كما يلي:
ثم قم بإنشاء الجدولين اللذين يجب جلب الربح منهما.
بمجرد الانتهاء من ذلك ، قم بإنشاء نطاق مسمى لكل من الجداول التي تم إنشاؤها حديثًا. لإنشاء نطاق مسمى ، اتبع الخطوات الواردة أدناه:
- حدد الجدول الجدول بأكمله الذي تريد تعيين اسم له
- من علامة تبويب الشريط ، حدد الصيغ ثم من مجموعة الأسماء المحددة ، حدد تعريف الاسم
- سترى مربع الحوار التالي
- أعط أي اسم من اختيارك
- انقر فوق موافق
بمجرد القيام بذلك لكلا الجدولين ، يمكنك استخدام هذه النطاقات المسماة في وظيفة IF على النحو التالي:
كما ترى ، أعاد VLOOKUP القيم المناسبة لملء عمود الربح وفقًا للسوبر ماركت الذي ينتمون إليه. بدلاً من كتابة الصيغة في كل خلية من خلايا عمود الربح ، لدي فقط نسخ الصيغة من أجل توفير الوقت والطاقة.
هذا يقودنا إلى نهاية هذه المقالة حول VLOOKUP في Excel. أتمنى أن تكون واضحًا مع كل ما تمت مشاركته معك. تأكد من ممارسة أكبر قدر ممكن وإعادة تجربتك.
لديك سؤال لنا؟ يرجى ذكر ذلك في قسم التعليقات في مدونة “VLOOKUP in Excel” وسنعاود الاتصال بك في أقرب وقت ممكن.
للحصول على معرفة متعمقة حول أي تقنيات تتجه إلى جانب تطبيقاتها المختلفة ، يمكنك التسجيل في البث المباشر مع دعم على مدار الساعة طوال أيام الأسبوع وإمكانية الوصول مدى الحياة.