VLOOKUP بين كتابين: دليل سهل خطوة بخطوة

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

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

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

ستتعلم أيضًا بعض التحسينات على VLOOKUP والعديد من البدائل للوظيفة.

هيا بنا نبدأ!

جدول المحتويات

• ما هو VLOOKUP؟
• كيفية استخدام VLOOKUP بين مصنفين
  • بيانات العينة
  • الخطوة 1: احتفظ بكلا المصنفين مفتوحين
  • الخطوة 2: ابدأ الدخول إلى صيغة VLOOKUP
  • الخطوة 3: كيفية الرجوع إلى المصنف الثاني
• 3 أخطاء VLOOKUP الشائعة وكيفية إصلاحها
  • 1. # N / A خطأ
  • 2. # المرجع! خطأ رقم العمود
  • 3. خطأ في الاقتباسات والأقواس المربعة
• 4 نصائح لإدارة المصنفات المختلفة
• كيفية تحديث البيانات تلقائيًا
• الاستخدامات المتقدمة لوظيفة VLOOKUP
• 2 البدائل الرئيسية لـ VLOOKUP
  • 1. مطابقة الفهرس
  • 2. XLOOKUP
• كيفية استخدام VLOOKUP عبر أوراق متعددة
  • الخطوة 1: تحديد المكونات
  • الخطوة 2: قم بإعداد بياناتك
  • الخطوة 3: استخدم وظيفة VLOOKUP
  • (اختياري) الخطوة 4: استكشاف أخطاء # N / A وإصلاحها
• افكار اخيرة

ما هو VLOOKUP؟

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

VLOOKUP هو أحد الحلول لهذه المهمة. يشير المصطلح إلى "البحث العمودي . "

إنها وظيفة مضمنة تسمح لك بالبحث عن قيمة محددة في عمود وإرجاع قيمة مقابلة في عمود آخر بناءً على الموقع.

يمكن استخدامه مع:

• جدول بيانات واحد

• أوراق متعددة في نفس المصنف

• اثنين من مصنفات Excel

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

كيفية استخدام VLOOKUP بين مصنفين

تُعد وظيفة VLOOKUP مفيدة بشكل خاص عندما تحتاج إلى البحث عن بيانات عبر أوراق عمل أو مصنفات متعددة.

الخطوات الثلاث الرئيسية للقيام بذلك هي:

1. افتح كلا المصنفين.

2. أدخل صيغة VLOOKUP في أول مصنف Excel.

3. قم بتوفير مرجع مؤهل بالكامل للمصنف الثاني.

بيانات العينة

يعمل هذا البرنامج التعليمي البسيط من خلال أمثلة تستند إلى بيانات نموذجية. للمتابعة ، قم بإنشاء مصنف جديد يسمى "المبيعات" وأدخل هذه البيانات في العمودين A و B:

• التفاح ، 40

• البرتقال ، 30

• الكمثرى ، 70

• الموز ، 20

أضف عمودًا ثالثًا للاحتفاظ بمعلومات السعر:

قم بإنشاء مصنف Excel ثانٍ يسمى "المنتج" وأعد تسمية الورقة 1 باسم "الأسعار". أدخل هذه البيانات في العمود A والعمود B:

• التفاح ، 1.50

• البرتقال ، 2.30

• الكمثرى ، 3.60

• الموز ، 2.10

تبدو البيانات كما يلي:

لحساب إيرادات المبيعات في جدول البيانات الأول ، نحتاج إلى البحث عن السعر في جدول البيانات الثاني. VLOOKUP للإنقاذ!

الخطوة 1: احتفظ بكلا المصنفين مفتوحين

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

يمكنك إما فتحهما بشكل منفصل أو استخدام الأمر "فتح" في Excel لفتحهما معًا في وقت واحد. توجد طريقتان لفتح أي من المصنفين:

• انتقل إلى علامة التبويب "ملف" وحدد "فتح".

• استخدم اختصار لوحة المفاتيح "Ctrl + O" .

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

الخطوة 2: ابدأ الدخول إلى صيغة VLOOKUP

إليك البنية الأساسية لوظيفة VLOOKUP:

= VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

• lookup_value : القيمة التي تريد البحث عنها في المصنف الآخر.

• table_array : نطاق الخلايا في المصنف الآخر الذي يحتوي على البيانات التي تريد استردادها.

• col_index_num : رقم العمود الموجود في النطاق الذي يحتوي على البيانات التي تريد استردادها.

• range_lookup : هذه المعلمة الأخيرة اختيارية. إذا قمت بتعيينها على TRUE أو حذفتها ، فستبحث VLOOKUP عن تطابق تقريبي. إذا قمت بتعيينه على FALSE ، فإن VLOOKUP سيبحث فقط عن تطابق تام.

لنفترض أنك تريد البحث عن سعر التفاح في المصنف الثاني لبيانات العينة. هذه هي الطريقة التي ستفكك بها الصيغة:

• يوجد lookup_value في الخلية A2 من المصنف الأول.

• يوجد صفيف الجدول في نطاق الخلايا A1: B5 للمصنف الثاني.

• العمود col_index_num هو العمود الثاني الذي يحمل السعر.

الجزء الأكثر تعقيدًا من صيغة VLOOKUP هو الرجوع إلى المصنف الثاني بشكل صحيح.

الخطوة 3: كيفية الرجوع إلى المصنف الثاني

لإكمال صيغة VLOOKUP لمصنفين ، ستحتاج إلى استخدام مرجع مؤهل بالكامل يتضمن هذه التفاصيل من المصنف الثاني:

• اسم المصنف

• اسم الورقة

• نطاق الخلايا

هذا يضمن أن Excel يعرف بالضبط مكان العثور على البيانات التي تبحث عنها. يتم استخدام المرجع المؤهل للوسيطة table_array للدالة VLOOKUP.

هذه هي قيم بيانات العينة:

• اسم المصنف: Product.xlsx

• اسم الورقة: الأسعار

• نطاق الخلايا: $ A1: $ B5

للبحث عن سعر العنصر الأول ، أدخل الصيغة التالية في الخلية C2 في المصنف الأول:

= VLOOKUP (A2، [Product.xlsx] الأسعار! $ A $ 1: $ B $ 5، 2، FALSE)

تُظهر هذه الصورة الوظيفة قيد التشغيل في شريط الصيغة للخلية الأولى:

لاحظ أن اسم المصنف محاط بأقواس مربعة ، وأن اسم الورقة متبوع بعلامة تعجب.

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

تستخدم الصيغة القيم المطلقة لنطاق الخلايا (البادئة $). يتيح لك ذلك نسخ الصيغة إلى باقي الصفوف من خلال النقر فوق مقبض التعبئة.

فيما يلي نتائج VLOOKUP:

وهذا كل شيء! أنت تعرف الآن كيفية استخدام VLOOKUP بشكل فعال. لمساعدتك في حالة مواجهة أي أخطاء ، قمنا بتجميع ثلاثة من أكثر أخطاء VLOOKUP شيوعًا في القسم التالي.

3 أخطاء VLOOKUP الشائعة وكيفية إصلاحها

الآن بعد أن تعلمت كيفية استخدام VLOOKUP من مصنف آخر ، فقد تواجه بعض الأخطاء الشائعة.

فيما يلي الأخطاء الثلاثة الأكثر شيوعًا وكيفية إصلاحها.

1. # N / A خطأ

يحدث الخطأ # N / A عندما لا يتم العثور على قيمة البحث في صفيف الجدول. لإصلاح هذا الخطأ ، يمكنك تجربة ما يلي:

• تحقق من هجاء قيمة البحث.

• تأكد من أن قيمة البحث موجودة في العمود الأول من صفيف الجدول.

• تحقق من عدم تنسيق قيمة البحث كنص في الورقتين.

• تأكد من تعيين وسيطة range_lookup على FALSE للعثور على تطابق تام.

فشل المثال أعلاه في العثور على قيمة مطابقة لـ Cherries في المصنف الثاني.

2. # المرجع! خطأ رقم العمود

# المرجع! يحدث الخطأ عندما تكون الوسيطة col_index_num أكبر من عدد الأعمدة في صفيف الجدول.

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

في الصورة أدناه ، تم تعيين col_index_num إلى 3 ولكن لا يوجد سوى عمودين في النطاق المرجعي.

3. خطأ في الاقتباسات والأقواس المربعة

يحدث خطأ علامات الاقتباس والأقواس المربعة عندما تقوم بإجراء VLOOKUP بقيمة تحتوي على علامات اقتباس أو أقواس مربعة.

لإصلاح هذا الخطأ ، يمكنك تجربة ما يلي:

• استخدم علامات الاقتباس المزدوجة حول قيمة البحث.

• استخدم الدالة CHAR (34) لإدراج علامات اقتباس مزدوجة.

• استخدم الدالة IFERROR لإرجاع خلية فارغة إذا كانت قيمة البحث تحتوي على علامات اقتباس أو أقواس مربعة.

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

4 نصائح لإدارة المصنفات المختلفة

عند العمل باستخدام مصنفات متعددة ، من المهم تتبع المصنف الذي يحتوي على البيانات.

فيما يلي أفضل أربع نصائح للحفاظ على سلامتك:

1. استخدم اصطلاحات التسمية لمساعدتك على تذكر جداول بيانات Excel التي تحتوي على البيانات.

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

3. استخدم الدالة IFERROR لمعالجة أي أخطاء # N / A قد تحدث إذا لم يتم العثور على قيم البحث في البيانات المصدر.

4. ضع في اعتبارك تأمين الخلايا في المصنف المرجعي بحيث لا يمكن الكتابة فوقها.

كيفية تحديث البيانات تلقائيًا

عند العمل مع ملفات Excel متعددة ، قد يستغرق تحديث البيانات يدويًا وقتًا طويلاً .

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

إليك الطريقة:

• في صيغة VLOOKUP ، استبدل وسيطة صفيف الجدول بالدالة INDIRECT.

• في الدالة INDIRECT ، استخدم مرجعًا إلى الخلية التي تحتوي على صفيف الجدول في المصنف الآخر.

لتحقيق ذلك باستخدام نماذج المصنفات الخاصة بنا ، اتبع هذه الخطوات.

1. استخدم خلية واحدة في العمود D لإدخال المرجع إلى ورقة "الأسعار" الخاصة بمصنف "المنتج" ونطاق الخلايا A1: B5 مثل هذا: [ Product.xlsx] الأسعار! أ: ب

2. في الخلية C2 من مصنف "المبيعات" ، استخدم هذه الصيغة: = VLOOKUP (A2، INDIRECT ($ D $ 1)، 2، FALSE)

3. انسخ الصيغة لأسفل (تضمن المراجع المطلقة الاحتفاظ بالخلية التي تحتوي على جدول البيانات).

تضمن هذه الإجراءات تحديث التغييرات في الورقة الأصلية تلقائيًا في الخلايا المتصلة.

تُظهر هذه الصورة الوظيفة غير المباشرة مع VLOOKUP:

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

الآن بعد أن تعرفت على قوة VLOOKUP ، دعنا نلقي نظرة على بعض الاستخدامات المتقدمة للوظيفة في القسم!

الاستخدامات المتقدمة لوظيفة VLOOKUP

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

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

يتضمن التحليل المعقد تقنيات مثل اختبارات مربع كاي والعينات المقترنة اختبارات T. فيما يلي مثال على استخدام اختبار chi-square في Excel:

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

ولكن ماذا لو كنت لا تريد استخدام VLOOKUP لأي سبب من الأسباب؟ انت محظوظ! هناك بعض البدائل ، وهو ما سنغطيه في القسم التالي.

2 البدائل الرئيسية لـ VLOOKUP

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

النوعان الأكثر شيوعًا هما:

• مطابقة الفهرس

• XLOOKUP

1. مطابقة الفهرس

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

وإليك كيف يعمل:

1. ترجع الدالة INDEX قيمة من نطاق محدد استناداً إلى رقم الصف والعمود.

2. ترجع الدالة MATCH موضع قيمة البحث في نطاق محدد.

لاستخدام INDEX MATCH ، تحتاج إلى تحديد النطاق الذي تريد البحث فيه ، والصف الذي تريد البحث فيه عن قيمة البحث ، والعمود الذي تريد إرجاع النتيجة فيه.

إليك مثال على الصيغة:

= INDEX (TableRange، MATCH (LookupValue، LookupRange، 0)، ColumnIndex)

لاستخدام هذا مع نماذج جداول البيانات ، هذه هي الصيغة:

= INDEX ([Product.xlsx] الأسعار! $ B $ 1: $ B $ 5 ، MATCH (A2، [Product.xlsx] الأسعار! $ A $ 1: $ A $ 5 ، 0))

هذا ينقسم إلى:

• تبحث الدالة MATCH عن القيمة الموجودة في الخلية A2 (اسم المنتج) ضمن النطاق A1: A5 في ورقة "الأسعار" في مصنف "المنتج".

• تُرجع الدالة INDEX قيمة من النطاق B1: B5 في ورقة "الأسعار" في مصنف "Product" ، استنادًا إلى رقم الصف الذي توفره دالة MATCH.

تُظهر هذه الصورة الصيغة قيد التنفيذ:

2. XLOOKUP

XLOOKUP هي وظيفة أحدث تم تقديمها في Excel 365 . إنه مشابه لـ VLOOKUP ، لكن مع بعض الميزات الإضافية.

تتمثل إحدى أكبر مزايا XLOOKUP في إمكانية البحث عن القيم الموجودة إلى يسار عمود البحث.

وإليك كيف يعمل:

1. ترجع الدالة XLOOKUP قيمة من نطاق محدد استناداً إلى قيمة بحث.

2. يمكنك تحديد ما إذا كنت تريد المطابقة التامة أو المطابقة التقريبية.

3. يمكنك أيضًا تحديد ما تريد إرجاعه إذا لم يتم العثور على تطابق.

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

إليك مثال على الصيغة:

= XLOOKUP (LookupValue، LookupRange، ReturnRange، "Not Found"، 0)

لاستخدام هذا مع نماذج جداول البيانات ، هذه هي الصيغة:

= XLOOKUP (A2، [Product.xlsx] الأسعار! $ 1 $: $ 5 $ ، [Product.xlsx] الأسعار! $ B $ 1: $ B $ 5)

هذا ينقسم إلى:

• A2: القيمة التي تريد البحث عنها (اسم المنتج).

• [Product.xlsx] الأسعار! $ A $ 1: $ A $ 5: النطاق الذي تريد البحث فيه عن قيمة البحث (أسماء المنتجات في ورقة "الأسعار" في مصنف "Product").

• [Product.xlsx] الأسعار! $ B $ 1: $ B $ 5: النطاق الذي يحتوي على قيم الإرجاع (الأسعار في ورقة "الأسعار" في مصنف "Product").

هذه هي الصيغة في العمل:

كيفية استخدام VLOOKUP عبر أوراق متعددة

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

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

هناك أربع خطوات.

الخطوة 1: تحديد المكونات

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

على سبيل المثال ، إذا كنت تريد البحث عن قيمة في النطاق A2: B6 في ورقة يناير في مصنف Sales_reports.xlsx ، فستبدو الصيغة كما يلي:

= VLOOKUP (A2، [Sales_reports.xlsx] يناير! $ A $ 2: $ B $ 6،2، FALSE)

الخطوة 2: قم بإعداد بياناتك

تأكد من أن بياناتك منظمة بطريقة تسهل الرجوع إليها عبر أوراق متعددة.

قد ترغب في التفكير في إنشاء علامات تبويب مختلفة لبيانات مرجعية محددة.

الخطوة 3: استخدم وظيفة VLOOKUP

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

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

(اختياري) الخطوة 4: استكشاف أخطاء # N / A وإصلاحها

إذا واجهت أخطاء # N / A عند استخدام VLOOKUP عبر أوراق متعددة ، فتحقق جيدًا من الصيغة للتأكد من أنك أشرت بشكل صحيح إلى جميع الأوراق والنطاقات الضرورية.

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

افكار اخيرة

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

تمتد قوة وظيفة VLOOKUP إلى ما هو أبعد من أوراق العمل الفردية ، لتصل عبر المصنفات المختلفة لربط البيانات وتحليلها.

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

من خلال دمج وظائف مثل INDEX-MATCH أو XLOOKUP ، يمكنك تحسين مرونة وفعالية عمليات البحث عن البيانات الخاصة بك.

استمر في التدرب على الإمكانات الهائلة لبرنامج Excel واستكشافها ؛ هذه ليست سوى غيض من فيض. نتمنى لك حظًا سعيدًا في البحث عن البيانات!