Unlimited Powerpoint templates, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Business
  2. Microsoft Excel
Business

كيفية استخدام وظيفة Excel VLOOKUP - مع أمثلة مفيدة

by
Difficulty:BeginnerLength:MediumLanguages:
This post is part of a series called How to Make and Use Excel Formulas (Beginner Bootcamp).
Quick Start: How to Make a Basic Formula in Excel
How to Work With Excel Math Formulas (Guide to the Basics)

Arabic (العربية/عربي) translation by Aisy Abdullah (you can also view the original English article)

ما هو VLOOKUP في Excel؟

A VLOOKUP ، اختصار لـ 'بحث عمودي' هي صيغة في Microsoft Excel لمطابقة البيانات من قائمتين. بدلاً من القفز بين جداول البيانات وكتابة بيانات المطابقة ، يمكنك كتابة صيغة VLOOKUP لأتمتة العملية.

Combining two lists is a a great situation to use VLOOKUP
يُعد دمج القائمتين وضعًا مثاليًا لاستخدام VLOOKUP.

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

يحتاج VLOOKUP الناجح إلى ثلاثة أشياء:

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

مثال سريع على صيغة Excel VLOOKUP في العمل

VLOOKUP هي صيغة Microsoft Excel ضرورية للعمل مع مجموعات متعددة من البيانات. في هذا البرنامج التعليمي ، سأعلمك كيفية إتقانها واستخدامها.

Example VLOOKUP formula used to look up employee data
مثال على صيغة VLOOKUP المستخدمة للبحث عن بيانات الموظف.

باستخدام المثال أعلاه ، كتبت الآن صيغة VLOOKUP التي تبحث عن هوية الموظف وتدرج عنوان الوظيفة في بيانات shift. نظرًا لأن كلا الورقتين بهما معرّف الموظف ، يمكن لـ Excel البحث عن عنوان الوظيفة المطابق. أفضل جزء من VLOOKUP هو أنه يمكنني الآن سحب نفس الصيغة إلى أسفل وسيبحث كل عنوان وظيفي فريد.

تحميل جداول البيانات إكسل

قبل أن نتحرك ، تأكد من تنزيل المرفق المجاني للمتابعة معك. إنه مثال عن جدول بيانات تم إنشاؤه ، والذي سنستخدمه من خلال هذا البرنامج التعليمي.

شاهد وتعلم: VLOOKUP

للحصول على أسرع طريقة لتعلم أساسيات صيغة VLOOKUP ، تحقق من screencast أدناه. يمشي الفيديو عبر عدة أمثلة من صيغة VLOOKUP ، باستخدام المصنف المثال.

استمر في القراءة للتجوّل من خلال التعليمات المكتوبة ، وتعلّم بعض التقنيات الإضافية التي لا تتم تغطيتها في التسجيل الرقمي.

كيفية استخدام VLOOKUP في Excel: المشي من خلال

استخدم علامات التبويب "عناصر المكونات" و "قائمة الموردين" لهذا المثال Excel VLOOKUP.

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

هناك ثلاث قطع نحتاج إلى إضافتها لكل طلب:

  • اسم المورد
  • رقم هاتف المورد
  • يوم تسليم المورد

في هذا المصنف ، توجد علامتا تبويب:

  • أوامر المكونات - تحتوي على قائمة طلبات المكونات من طهاتنا.
  • قائمة الموردين - تحتوي على معلومات حول الموردين ، مثل اسم المورد ورقم الهاتف.
Excel VLOOKUP list ingredient order and supplier list example
في الجزء العلوي ، لدينا بيانات طلبنا ، وهي موجودة في علامة التبويب "أوامر المكونات". فيما يلي "قائمة الموردين" ، والتي ستكون بمثابة قائمة البحث الخاصة بنا.

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

الخطوة 1: بدء تشغيل Excel VLOOKUP المعادلة

في علامة التبويب أوامر المكونات ، لننقر في أول خلية فارغة فارغة ، F5 ، ثم نضغط على علامة يساوي لبدء تشغيل VLOOKUP. ثم اكتب "VLOOKUP (" لبدء الصيغة.

)VLOOKUP=

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

=,VLOOKUP(B5

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

Select the Excel lookup list
قم بتوجيه Excel إلى قائمة البحث.

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

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,

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

=VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,

أخيرًا ، سنقوم بإضافة FALSE للبحث الدقيق ، ثم إغلاق الأقواس:

=(VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE

Pulldown formula is working perfectly in Excel
وصيغتنا تعمل بشكل مثالي ، مع جذب المورد للبطاطس.

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

الصيغة تعمل على أكمل وجه! حسنًا ، لننتقل الآن إلى سحب البيانات للحقلين الآخرين: رقم هاتف المورد وتاريخ التسليم.

الخطوة 2: سحب المزيد من البيانات باستخدام VLOOKUP الخاص بنا

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

سنبدأ بنسخ ولصق الخلية F5 (خلية المورد الخاصة بنا) إلى الخلية G5. عادة ما أستخدم اختصارات لوحة المفاتيح Ctrl+C و Ctl+V لنسخ الخلية بأكملها ولصقها. في البداية ، لن يعمل هذا ، وسترى N / A في الخلية.

Copying and pasting formula in Excel Lookup Moved
نسخ ولصق البحث مع التعديلات في Excel.

من الأسهل نسخ هذه الصيغة ولصقها في خلية أخرى ، ولكنها تتطلب بعض التغيير والتبديل. في البداية ، سوف يتطلع Excel إلى الخلية C5 للبحث ، ولكننا نحتاج إلى ضبطه على "B5" في شريط الصيغة. وبمجرد قيامنا بذلك ، سيعمل البحث - تقريبًا.

سنحتاج إلى الانتقال إلى شريط الصيغة ، وتغيير الجزء الأول من الصيغة من C5 الظهر إلى B5. عندما نقلنا المعادلة عبر عمود واحد ، قام Excel بتحديث أجزاء أخرى من الصيغة. لقد حصلنا على "N / A" لأن Excel كان يحاول مطابقة الكمية (العمود C) مع قائمة البحث الخاصة بنا ، ولكن قائمة البحث الخاصة بنا لا تتضمن الكمية.

حتى الآن ، يجب أن تكون الصيغة لدينا:

=(VLOOKUP(B5,'Supplier List'!$A$3:$G$13,2,FALSE

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

الخطوة 3: إصلاح مشكلة خطأ VLOOKUP N/A

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

Lookup List Column numbers need to be updated
نحتاج إلى تحديث "2" في صيغة Excel الخاصة بنا إلى "7" لسحبها من العمود السابع في قائمة البحث الخاصة بنا.

كل ما نحتاج إليه هو تحديث قسم الأعمدة في قائمة البحث لدينا من "2" إلى "7" ، وهو يعمل الآن بشكل رائع!

Our Excel vlookup formula is Updated for Supplier Phone
لاحظ أن هاتف المورد يعمل بشكل جيد الآن وقد قمنا بتحديث صيغتنا لاستخدام العمود السابع من قائمة البحث. الآن ، اسحب الصيغة لأسفل لتحديثها لجميع الخلايا.

صيغتنا النهائية للبحث عن رقم هاتف المورد:

=(VLOOKUP(B5,'Supplier List'!$A$3:$G$13,7,FALSE

الخطوة 4: إضافة عمود آخر لإنهاء صيغة VLOOKUP الخاصة بنا

أخيرًا ، دعنا نعمل في عمود يوم تسليم المورد. نسخ ولصق الخلية G5 إلى الخلية H5. مرة أخرى ، سنحتاج إلى إصلاح صيغتنا عن طريق تغيير C5 إلى B5 لاستخدام العنصر كمفتاح أساسي. ثم ، قم بتحديث "7" إلى "5" لاستخدام العمود الخامس من جدول البحث الخاص بنا.

Final supplier delivery day lookup formula applied
تم تطبيق الصيغة النهائية لتسليم يوم تسليم المورد.

الصيغة النهائية ليوم تسليم المورد:

=(VLOOKUP(B5,'Supplier List'!$A$3:$G$13,5,FALSE

هذا هو! لقد كتبنا صيغة واحدة وأعدناها في كل جزء من البيانات التي نحتاجها لوضع ترتيبنا التالي.

استكشاف الأخطاء وإصلاحها VLOOKUP

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

1. مباريات متعددة

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

Troubleshooting Example of Excel VLOOKUP not working
تقول VLOOKUP أن كاري ريتشارد هي مديرة التسويق لدينا ، لكننا نعرف أنها رُقِيت مؤخراً إلى رئيس الشركة. لماذا لا يعمل بحثنا؟
Lookup problem with two listings conflicting in our VLOOKUP
عفوًا ، يبدو أن هناك قائمتين لـ Carrie Richard في قائمة البحث - واحدة لمدير التسويق ، وواحدة للرئيس. بمجرد حذف سطر "مدير التسويق" من قائمة البحث ، تكون البيانات الخاصة بنا صحيحة.

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

2. قيادة أو زائدة المساحات

هناك مشكلة أخرى شائعة وهي أن بياناتنا قد لا تتطابق فعليًا في عيون Excel. البيانات التي تحتوي على مسافة قبل أن يقال إنها تحتوي على مساحة "رائدة" ، بينما تحتوي البيانات على مسافة بعد أن تحتوي على "مساحة زائدة".

Trailing Space VLOOKUP Example Problem
هذا VLOOKUP مكتوب بشكل مثالي ، لكنه لا يعمل. يمكنك مشاهدة الاسم بوضوح في قائمة البحث ، ولكن لا يقوم Excel بإرجاع مطابقة. اقرأ لتكتشف لماذا.

إذا كانت قطعة البيانات المطابقة لها مسافة قبلها أو بعدها ، يرى Excel أن هذين الجزئين من البيانات مختلف تمامًا ، ولن يعرضان مطابقة. مشاهدات "Excel '"Andrew و "Andrew_" و "_Andrew" و على أنها ثلاثة أجزاء فريدة من البيانات لن تتم مطابقتها في VLOOKUP.

Resolving Excel VLOOKUP Trailing Space Issue
اتضح أن هناك "مساحة زائدة" ، أو مسافة واحدة بعد الاسم في الخلية. من المستحيل أن ترى ، ولكن يمكن أن تكسر VLOOKUP لأن Excel يعامل "Alyssa Reddall" و "(Alyssa Reddall (space" بشكل مختلف. بمجرد حذف المساحة ، ستعمل VLOOKUP بشكل طبيعي.

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

3. قفل في خلايا المراجع

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

Bad reference VLOOKUP problem
في لقطة الشاشة هذه ، قمت بسحب VLOOKUP إلى أسفل القائمة لتطبيقه على الخلايا الأخرى. ومع ذلك ، لاحظ أن مرجع البحث تغير من A2 إلى B15 ، إلى A16 إلى B29 ، وبالتالي لماذا لا تعمل الصيغة. قائمة بحثي في ​​الواقع في A2 إلى B15 ، لذلك يتم الآن بحث البحث.

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

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

Updated Excel VLOOKUP Formula with Absolute Reference
لاحظ أن الصيغة (المعروضة في الجزء العلوي من لقطة الشاشة هذه في شريط الصيغة) تتضمن الآن علامات الدولار في مرجع قائمة البحث ، مما يدل على أنها تستخدم مرجعًا مطلقًا. الآن ، عندما أسحب الصيغة لأسفل ، يتم تأمين الصيغة الخاصة بقائمة البحث وتعمل بشكل مثالي.

خلاصة والاحتفاظ التعلم

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

  • إذا كنت ترغب في معرفة مجموعة متنوعة من مهارات Excel المتقدمة ، تحقق من Bob Flisser 12 تقنيات لمستخدمي Power tutorial.
  • بالإضافة إلى ذلك ، يعد مقدمة Bob إلى Introduction to Spreadsheets الدليل المثالي للبدء في Excel
  • في بعض الأحيان ، يساعد على تعلم مادة مشابهة من مورد آخر. تعد الوثائق الرسمية لـ Microsoft Office على صيغة VLOOKUP موردا رائعا آخر لاتقان عملية VLOOKUP.

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

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.