شرح استخدام VLOOKUP في Excel

شرح استخدام VLOOKUP في Excel

Share on facebook
Share on twitter
Share on google
Share on pinterest
Share on reddit
Share on telegram
Share on whatsapp
Share on facebook

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

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

معلمات VLOOKUP في Excel

عندما تبدأ الكتابة = VLOOKUP ( في أي خلية في إكسيل ، سترى نافذة منبثقة تعرض جميع معلمات الوظائف المتاحة.

دعونا نفحص كل من هذه المعلمات وما تعنيه.

  • ابحث عن القيمة: القيمة التي تبحث عنها من جدول البيانات
  • table_array: نطاق الخلايا في الورقة التي تريد البحث فيها
  • col_index_num: العمود الذي تريد سحب النتيجة منه
  • [range_lookup]: وضع المطابقة (TRUE = تقريبي ، FALSE = دقيق)

تتيح لك هذه المعلمات الأربعة إجراء العديد من عمليات البحث المختلفة والمفيدة للبيانات الموجودة داخل مجموعات البيانات الكبيرة جدًا.

مثال بسيط على VLOOKUP Excel

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

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

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

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

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

  1. نوع = VLOOKUP (
  2. حدد مجال المدرسة ، الذي يوجد في هذا المثال I2. اكتب فاصلة.
  3. حدد نطاق الخلايا الذي يحتوي على البيانات التي تريد البحث عنها بالكامل. اكتب فاصلة.

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

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

  1. بعد ذلك ، لاسترداد نتيجة القراءة ، ستحتاج إلى تحديد العمود الثالث من العمود المحدد أقصى اليسار. لذا ، اكتب أ 3 ثم اكتب فاصلة أخرى.
  2. أخيرًا ، اكتب خاطئة لمطابقة تامة ، وأغلق الدالة بعلامة ).

يجب أن تبدو وظيفة VLOOKUP النهائية كما يلي:

=VLOOKUP(I2,B2:G461,3,FALSE)

عندما تضغط لأول مرة على Enter وتنهي الوظيفة ، ستلاحظ أن حقل القراءة سيحتوي على # N / A.

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

كيفية التعامل مع كون VLOOKUP حساس

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

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

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

=TRIM(LOWER(B2))

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

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

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

الآن بعد تنظيف جميع بياناتك في هذا العمود الجديد ، قم بتعديل وظيفة VLOOKUP قليلاً في Excel لاستخدام هذا العمود الجديد بدلاً من العمود السابق من خلال البدء نطاق البحث عند C2 بدلاً من B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

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

VLOOKUP تطابق تقريبي

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

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

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

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

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

في وظيفة VLOOKUP هذه:

  • ابحث عن القيمة: F2 ، الدرجة النهائية المتوسطة
  • table_array: I2: J8 ، نطاق البحث عن درجات الأحرف
  • فهرس_عمود: 2 ، العمود الثاني في جدول البحث
  • [range_lookup]: TRUE ، تطابق تقريبي

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

لاحظ أن وظيفة VLOOKUP في Excel تقوم بالبحث من النهاية السفلية لنطاق التقدير مع درجة الحروف المعينة إلى أعلى نطاق درجة الحرف التالي.

لذا ، يجب أن يكون الحرف “C” هو الحرف المعين للنطاق الأدنى (75) ، ويتم تعيين B إلى الجزء السفلي (الحد الأدنى) من نطاق الأحرف الخاص به. سوف “VLOOKUP” “العثور” على نتيجة 60 (D) كأقرب قيمة تقريبية لأي شيء بين 60 إلى 75.

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

ومع ذلك ، ضع في اعتبارك أن مستخدمي Microsoft الذين لديهم اشتراك شهري في Office 365 يمكنهم الآن الوصول إلى وظيفة XLOOKUP أحدث. هذه الوظيفة لديها المزيد من المعلمات ومرونة إضافية. سيحتاج المستخدمون الذين لديهم اشتراك نصف سنوي إلى انتظار نشر التحديث في يوليو 2020.

Source link

0 0 vote
Article Rating
Subscribe
نبّهني عن
guest
0 تعليقات
Inline Feedbacks
View all comments

شرح استخدام VLOOKUP في Excel