كيفية استخدام دالة VLOOKUP في الاكسيل مع الامثلة
ما هو VLOOKUP في Excel؟
A VLOOKUP ، اختصار لـ 'بحث عمودي' هي صيغة في Microsoft Excel لمطابقة البيانات من قائمتين. بدلاً من القفز بين جداول البيانات وكتابة بيانات المطابقة ، يمكنك كتابة صيغة VLOOKUP لأتمتة العملية.
يُعد دمج القائمتين وضعًا مثاليًا لاستخدام VLOOKUP.على اليسار (في الصورة أعلاه) ، لدينا معلومات حول تغيير الموظفين. نحن نريد أن نضيف اللقب الوظيفي للموظف إلى بيانات التحول. مع قائمة منفصلة من الموظفين وألقابهم الوظيفية ، يمكننا كتابة صيغة VLOOKUP لسحب العنوان من قائمة البحث.
يحتاج VLOOKUP الناجح إلى ثلاثة أشياء:مفتاح أساسي في كل قائمة يمكنك استخدامه لمطابقة بياناتك. تحتاج القائمتان إلى مشاركة جزء واحد على الأقل من البيانات المشتركة (في مثال Excel VLOOKUP أعلاه ، هذا هو كود الموظف)
Lookup List ، تحتوي على "قاعدة بيانات" ، أو بشكل أساسي المعلومات (قائمة عناوين الوظائف الخاصة بالموظفين)
معلوماتك ، والتي تريد سحبها إلى (بيانات التحول)
مثال سريع على صيغة Excel VLOOKUP في العمل
VLOOKUP هي صيغة Microsoft Excel ضرورية للعمل مع مجموعات متعددة من البيانات. في هذا البرنامج التعليمي ، سأعلمك كيفية إتقانها واستخدامها.
مثال على صيغة VLOOKUP المستخدمة للبحث عن بيانات الموظف.باستخدام المثال أعلاه ، كتبت الآن صيغة VLOOKUP التي تبحث عن هوية الموظف وتدرج عنوان الوظيفة في بيانات shift. نظرًا لأن كلا الورقتين بهما معرّف الموظف ، يمكن لـ Excel البحث عن عنوان الوظيفة المطابق. أفضل جزء من VLOOKUP هو أنه يمكنني الآن سحب نفس الصيغة إلى أسفل وسيبحث كل عنوان وظيفي فريد.
تحميل جداول البيانات إكسل
قبل أن نتحرك ، تأكد من تنزيل المرفق المجاني للمتابعة معك. إنه مثال عن جدول بيانات تم إنشاؤه ، والذي سنستخدمه من خلال هذا المقال التعليمي.
كيفية استخدام VLOOKUP في Excel:
لنفترض أننا ندير مطعمًا ونقوم بوضع طلباتنا الأسبوعية للموردين. قدم لنا الطهاة قائمة بالمكونات المطلوبة ، ونحن بحاجة إلى إدخال معلومات حول المورد.
هناك ثلاث قطع نحتاج إلى إضافتها لكل طلب:
- اسم المورد
- رقم هاتف المورد
- يوم تسليم المورد
في هذا المصنف ، توجد علامتا تبويب:أوامر المكونات - تحتوي على قائمة طلبات المكونات من طهاتنا.
قائمة الموردين - تحتوي على معلومات حول الموردين ، مثل اسم المورد ورقم الهاتف.
في الجزء العلوي ، لدينا بيانات طلبنا ، وهي موجودة في علامة التبويب "أوامر المكونات". فيما يلي "قائمة الموردين" ، والتي ستكون بمثابة قائمة البحث الخاصة بنا.
الحقل المشترك بين الجدولين هو علامة التبويب المكون. لنستخدمها للبحث عن كل حقل من الحقول الثلاثة وإضافته إلى قائمة الطلبات.
الخطوة 1: بدء تشغيل Excel VLOOKUP المعادلة
في علامة التبويب أوامر المكونات ، لننقر في أول خلية فارغة فارغة ، F5 ، ثم نضغط على علامة يساوي لبدء تشغيل VLOOKUP. ثم اكتب "VLOOKUP (" لبدء الصيغة.
)VLOOKUP=
تذكر أن مفتاحنا الأساسي - جزء البيانات الذي يظهر في كلتا القائمتين - هو عنصر، لذا سنستخدمه في البحث. إما أن تنقر على الخلية B5 ، أو تكتبها في الصيغة. بعد ذلك ، أضف فاصلة بعد "B5" حتى نتمكن من إدخال الجزء التالي من الصيغة.
=,VLOOKUP(B5
الآن ، نحن بحاجة إلى إعطاء صيغة قائمة البحث لدينا. مع استمرار فتح الصيغة ، انقر فوق علامة التبويب قائمة الموردين. الآن ، انقر فوق الخلية A3 ، ثم انقر واسحب لتمييز وتحديد A3 إلى G13 ، جدول البحث بالكامل. تأكد من الضغط على المفتاح F4 على لوحة المفاتيح لجعل الصيغة مرجعًا مطلقًا (المزيد حول هذا لاحقًا). أخيرًا ، أدخل فاصلة أخرى.
قم بتوجيه 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
وصيغتنا تعمل بشكل مثالي ، مع جذب المورد للبطاطس.
والخبر السار هو أننا لا نحتاج إلى إعادة كتابة هذه الصيغة نفسها مرارًا وتكرارًا - فقط انقر نقرًا مزدوجًا في الزاوية السفلية اليمنى من الخلية F5 (يمكنك رؤية المربع الأخضر الصغير في زاوية الخلية) لتوسيع الصيغة أسفل (كما هو موضح أعلاه).
الصيغة تعمل على أكمل وجه! حسنًا ، لننتقل الآن إلى سحب البيانات للحقلين الآخرين: رقم هاتف المورد وتاريخ التسليم.
الخطوة 2: سحب المزيد من البيانات باستخدام VLOOKUP الخاص بنا
نظرًا لأننا استخدمنا مرجعًا مطلقًا ، يمكننا في الأساس إعادة استخدام الصيغة نفسها التي كتبناها مع قرص صغير. دعونا نضيف رقم هاتف المورد التالي.
سنبدأ بنسخ ولصق الخلية F5 (خلية المورد الخاصة بنا) إلى الخلية G5. عادة ما أستخدم اختصارات لوحة المفاتيح Ctrl+C و Ctl+V لنسخ الخلية بأكملها ولصقها. في البداية ، لن يعمل هذا ، وسترى N / A في الخلية.
نسخ ولصق البحث مع التعديلات في 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 الخاصة بنا ونقوم بتحديث البحث لسحبه من العمود السابع.
نحتاج إلى تحديث "2" في صيغة Excel الخاصة بنا إلى "7" لسحبها من العمود السابع في قائمة البحث الخاصة بنا.كل ما نحتاج إليه هو تحديث قسم الأعمدة في قائمة البحث لدينا من "2" إلى "7" ، وهو يعمل الآن بشكل رائع!
لاحظ أن هاتف المورد يعمل بشكل جيد الآن وقد قمنا بتحديث صيغتنا لاستخدام العمود السابع من قائمة البحث. الآن ، اسحب الصيغة لأسفل لتحديثها لجميع الخلايا.
صيغتنا النهائية للبحث عن رقم هاتف المورد:
=(VLOOKUP(B5,'Supplier List'!$A$3:$G$13,7,FALSE
الخطوة 4: إضافة عمود آخر لإنهاء صيغة VLOOKUP الخاصة بنا
أخيرًا ، دعنا نعمل في عمود يوم تسليم المورد. نسخ ولصق الخلية G5 إلى الخلية H5. مرة أخرى ، سنحتاج إلى إصلاح صيغتنا عن طريق تغيير C5 إلى B5 لاستخدام العنصر كمفتاح أساسي. ثم ، قم بتحديث "7" إلى "5" لاستخدام العمود الخامس من جدول البحث الخاص بنا.
تم تطبيق الصيغة النهائية لتسليم يوم تسليم المورد.الصيغة النهائية ليوم تسليم المورد:
=(VLOOKUP(B5,'Supplier List'!$A$3:$G$13,5,FALSE
هذا هو! لقد كتبنا صيغة واحدة وأعدناها في كل جزء من البيانات التي نحتاجها لوضع ترتيبنا التالي.
استكشاف الأخطاء وإصلاحها VLOOKUP
لذلك ، لقد كتبت صيغة VLOOKUP باتباع التعليمات خطوة بخطوة ، ولكنها لا تزال غير فعالة. يقوم Excel بأخذ الأشياء بشكل حرفي تمامًا ، لذا نحتاج إلى توخي الحذر مع البيانات وصيغة VLOOKUP. لنلقِ نظرة على العديد من الأفكار لتصحيح صيغة VLOOKUP التي لا تعمل.
1. تطابقات متعددة
إحدى أكثر المشكلات شيوعًا في VLOOKUP هي عندما تكون هناك عدة مطابقات في قائمة البحث. عندما تستخدم VLOOKUP ، فسوف يتطابق مع العنصر الأول الموجود في القائمة.
تقول VLOOKUP أن كاري ريتشارد هي مديرة التسويق لدينا ، لكننا نعرف أنها رُقِيت مؤخراً إلى رئيس الشركة. لماذا لا يعمل بحثنا؟
عفوًا ، يبدو أن هناك قائمتين لـ Carrie Richard في قائمة البحث - واحدة لمدير التسويق ، وواحدة للرئيس. بمجرد حذف سطر "مدير التسويق" من قائمة البحث ، تكون البيانات الخاصة بنا صحيحة.
تتمثل نقطة VLOOKUP في البحث عن عنصر مطابق مقابل إحدى القوائم. يجب ألا تحتوي قائمة البحث على نسخ مكررة للمفتاح الأساسي ، العنصر الذي تستخدمه للمطابقة. خلاف ذلك ، يحصل خلط Excel ويظهر لك أول مباراة.
2. المساحة الزائدة
هناك مشكلة أخرى شائعة وهي أن بياناتنا قد لا تتطابق فعليًا في عيون Excel. البيانات التي تحتوي على مسافة قبل أن يقال إنها تحتوي على مساحة "زائدة" ، بينما تحتوي البيانات على مسافة بعد أن تحتوي على "مساحة زائدة".
هذا VLOOKUP مكتوب بشكل مثالي ، لكنه لا يعمل. يمكنك مشاهدة الاسم بوضوح في قائمة البحث ، ولكن لا يقوم Excel بإرجاع مطابقة. اقرأ لتكتشف لماذا.إذا كانت قطعة البيانات المطابقة لها مسافة قبلها أو بعدها ، يرى Excel أن هذين الجزئين من البيانات مختلف تمامًا ، ولن يعرضان مطابقة. مشاهدات "Excel '"Andrew و "Andrew_" و "_Andrew" و على أنها ثلاثة أجزاء فريدة من البيانات لن تتم مطابقتها في VLOOKUP.
اتضح أن هناك "مساحة زائدة" ، أو مسافة واحدة بعد الاسم في الخلية. من المستحيل أن ترى ، ولكن يمكن أن تكسر VLOOKUP لأن Excel يعامل "Alyssa Reddall" و "(Alyssa Reddall (space" بشكل مختلف. بمجرد حذف المساحة ، ستعمل VLOOKUP بشكل طبيعي.
إذا كانت بياناتك تحتوي على مسافات رائدة أو لاحقة ، استخدم وظيفة مهمة TRIM في Excel لتنظيفها. يجب أن يعمل VLOOKUP بشكل رائع بعد استخدامه لإزالة المسافات البادئة واللاحقة.
3. قفل في خلايا المراجع
كما تعلم ، يمكنك سحب خلية لأسفل باستخدام المقبض للصق الصيغة في خلايا أخرى. ومع ذلك ، فهذا يكسر VLOOKUP في بعض الأحيان لأن الخلية تشير إلى التغيير.
في لقطة الشاشة هذه ، قمت بسحب VLOOKUP إلى أسفل القائمة لتطبيقه على الخلايا الأخرى. ومع ذلك ، لاحظ أن مرجع البحث تغير من A2 إلى B15 ، إلى A16 إلى B29 ، وبالتالي لماذا لا تعمل الصيغة. قائمة بحثي في الواقع في A2 إلى B15 ، لذلك يتم الآن بحث البحث.
أثناء سحب الصيغ لأسفل ، يتم سحب المرجع لجدول البحث خارج المحاذاة. فجأة ، يفتقد جدول البحث الصفوف من جدول البحث ولا يعمل VLOOKUP الخاص بنا.
ويتمثل الإصلاح في جعل الصيغة مرجعًا مطلقًا ، بحيث لا تتغير القائمة التي تشير إليها عند سحب الصيغة لأسفل. انقر في الخلية التي كتبت فيها VLOOKUP ، ثم انقر في أي مكان في مرجع قائمة البحث. ثم اضغط F4. ستلاحظ أن الصيغة تتغير لتشمل علامات الدولار.
لاحظ أن الصيغة (المعروضة في الجزء العلوي من لقطة الشاشة هذه في شريط الصيغة) تتضمن الآن علامات الدولار في مرجع قائمة البحث ، مما يدل على أنها تستخدم مرجعًا مطلقًا. الآن ، عندما أسحب الصيغة لأسفل ، يتم تأمين الصيغة الخاصة بقائمة البحث وتعمل بشكل مثالي.
الخلاصة:
VLOOKUP هي واحدة من تلك الصيغ الأساسية لكونها منتجة Excel اكسل. ببساطة ، ليس هناك وقت كاف للبحث يدويًا عن البيانات وإعادة كتابتها مرارًا وتكرارًا ، لذا من المهم معرفة الصيغ مثل VLOOKUP.
إذا كنت تواجه أي مشاكل في استخدام صيغة VLOOKUP ، فلا تتردد في ترك تعليق أدناه لتحري الخلل وإصلاحه.