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

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

VLOOKUP هي وظيفة قوية تتيح لك البحث عن بيانات مطابقة بين عمودين. تتيح لك الوظيفة البحث عن القيم في عمود واحد يظهر في عمود آخر.

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

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

• أساسيات وظيفة Vlookup
  • وظيفة Vlookup
• تحضير بياناتك لـ VLOOKUP
  • تنسيق البيانات
• كيفية استخدام VLOOKUP لمقارنة عمودين
  • معالجة الأخطاء الأخرى
• بدائل VLOOKUP لمقارنة الأعمدة
  • 1. استخدام وظائف الفهرس والمطابقة
  • 2. توظيف XLOOKUP في برنامج Excel
• خمس نصائح لتحسين أداء VLOOKUP
  • 1. تحديد نطاق البحث الخاص بك
  • 2. استخدم المراجع المطلقة
  • 3. فرز البيانات الخاصة بك
  • 4. استخدم VLOOKUP مزدوج
  • 5. استخدم التنسيق الشرطي
• الاستخدامات المتقدمة لـ VLOOKUP
• كلمتنا الأخيرة

أساسيات وظيفة Vlookup

لنفترض أن لديك جدول بيانات يحتوي على قائمتين من العناصر في العمود A والعمود B. وتريد العثور على العناصر الموجودة في القائمة 1 والتي تظهر أيضًا في القائمة 2.

يمكنك أن تتخيل أن العمل يدويًا من خلال قوائم بآلاف العناصر سيستغرق وقتًا طويلاً. لحسن الحظ ، يقوم Excel بإحضار VLOOKUP إلى الإنقاذ!

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

وظيفة Vlookup

فيما يلي بناء الجملة الأساسي لوظيفة VLOOKUP:

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

هذا تفصيل للعناصر:

• lookup_value : القيمة التي تريد البحث عنها في العمود الأول من table_array.

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

• col_index_num : رقم العمود في table_array الذي تريد إرجاع القيمة منه.

• range_lookup : اختياري. إنها إما TRUE (مطابقة تقريبية) أو خطأ (مطابقة تامة). الافتراضي هو الصحيح.

تحضير بياناتك لـ VLOOKUP

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

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

فيما يلي نموذج البيانات المستخدمة في هذه المقالة:

هذا هو نموذج الجدول مع رأس لإضافته إلى القسم بعنوان "تحضير بياناتك لـ VLOOKUP":

القائمة 1 القائمة 2
البند 1 البند 3
البند 2
البند 4 البند 3 البند 1
البند 4 البند 5

تنسيق البيانات

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

قد تحدث أخطاء المطابقة في النتيجة النهائية إذا كان التنسيق غير متسق.

من المستحسن أيضًا إزالة أي قيم مكررة أو خلايا فارغة لتقليل مخاطر الأخطاء.

لإزالة القيم المكررة:

1. حدد العمود.

2. انتقل إلى علامة التبويب البيانات في الشريط العلوي.

3. انقر فوق إزالة التكرارات في قسم أدوات البيانات.

قد يكون من الصعب بعض الشيء رؤية الزر. ستساعدك هذه الصورة على:

لإزالة الخلايا الفارغة:

1. حدد العمود.

2. انتقل إلى علامة التبويب الصفحة الرئيسية في الشريط العلوي.

3. قم بتوسيع قائمة "بحث وتحديد".

4. اختر "الانتقال إلى خاص".

5. حدد "الفراغات" من الخيارات.

6. انقر فوق "حذف" في قسم الخلايا.

كيفية استخدام VLOOKUP لمقارنة عمودين

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

1. حدد خلية في عمود جديد حيث تريد عرض نتائج المقارنة (على سبيل المثال ، الخلية C2).

2. اكتب الصيغة التالية: = VLOOKUP (A2، B: B، 1، FALSE))

3. اضغط على Enter لتطبيق الصيغة.

4. في حالة وجود قيمة مطابقة ، سيتم عرض القيمة في نفس الصف من عمود النتيجة (على سبيل المثال ، العمود C).

5. اسحب الصيغة لأسفل من C2 لنسخها على أي عدد تريده من الخلايا.

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

لاحظ أن العنصر 2 يظهر كـ # N / A. يخبرنا Excel أن المطابقة غير قابلة للتطبيق ، أي أنه لا يمكن العثور عليها.

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

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

للقيام بذلك ، ادمج وظيفة VLOOKUP مع وظيفة IFNA مثل هذا:

= IFNA (VLOOKUP (A1، B: B، 1، FALSE)، "")

تكتشف الدالة IFNA الخطأ # N / A وتستبدل الإخراج بمسافة فارغة (""). يمكنك أيضًا استخدام دالة ISNA أو الحصول على منطق إضافي مع الدالة IF.

معالجة الأخطاء الأخرى

يمكن أن ينتج عن VLOOKUP أحيانًا أخطاء أخرى في نتائج المقارنة الخاصة بك. # REF! يعد الخطأ في Excel مشكلة حقيقية في بياناتك.

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

تأكد من أن نطاق البحث الذي تشير إليه يغطي جميع القيم التي تريد مقارنتها.

بدائل VLOOKUP لمقارنة الأعمدة

هناك نوعان من وظائف البحث البديلة الرئيسية عند مقارنة عمودين في Excel للعثور على التطابقات.

1. استخدام وظائف الفهرس والمطابقة

بدلاً من استخدام VLOOKUP ، يمكنك مقارنة عمودين في Excel من خلال الجمع بين دالتي INDEX و MATCH.

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

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

= INDEX (return_range، MATCH (lookup_value، lookup_range، 0))

• Return_range : نطاق الخلايا الذي يحتوي على البيانات التي تريد إرجاعها.

• Lookup_value : القيمة التي تريد البحث عنها ضمن نطاق البحث.

• Lookup_range : نطاق الخلايا الذي تريد البحث فيه عن قيمة البحث.

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

= (INDEX (B: B، MATCH (A2، B: B، 0)))

تُرجع الدالة MATCH الموضع النسبي لقيمة البحث ضمن نطاق البحث ، وتُرجع الدالة INDEX القيمة المقابلة من نطاق الإرجاع.

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

يمكنك أيضًا استبدال الخطأ # N / A برسالة مخصصة. فيما يلي مثال على استخدام الدالة IFERROR.

= IFERROR (INDEX (B: B، MATCH (A2، B: B، 0))، "غير موجود")

2. توظيف XLOOKUP في برنامج Excel

بالنسبة للمستخدمين الذين يستخدمون Excel 365 أو Excel 2019 ، يعد XLOOKUP اختبارًا منطقيًا بديلاً لـ VLOOKUP للعثور على قيم مشتركة في عمودين.

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

بناء جملة XLOOKUP هو:

= XLOOKUP (lookup_value، lookup_range، return_range، [if_not_found]، [match_mode]، [search_mode])

• lookup_value : القيمة التي تريد البحث عنها ضمن نطاق البحث.

• lookup_range : نطاق الخلايا الذي تريد البحث فيه عن قيمة البحث.

• return_range : نطاق الخلايا التي تحتوي على البيانات التي تريد إرجاعها.

• أضف قيم الخطأ المخصصة ووضع المطابقة ومعلمات وضع البحث حسب الحاجة.

سيجد XLOOKUP قيمة البحث ضمن نطاق البحث ويعيد القيمة المقابلة من نطاق الإرجاع.

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

فيما يلي مثال بقائمتين في العمود الأول والثالث من جدول البيانات:

تبدو صيغة XLOOKUP كما يلي:

= XLOOKUP (A2، C: C، C: C، "")

تُظهر هذه الصورة النتيجة مع المقارنة في العمود D. القيمة الأولى موجودة ولكن الثانية مفقودة.

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

كمكافأة ، سأوضح لك كيفية استخدام XLOOKUP للمقارنة عبر الصفوف. إذا كانت لديك قائمتان في الصف 1 والصف 2 ، فإن أبسط استخدام للصيغة يبدو كما يلي:

= XLOOKUP (B1، 2: 2، 2: 2، "")

تظهر هذه الصورة النتائج على الصفين.

بفضل وظائفه ومرونته المحسّنة ، يعد XLOOKUP بديلاً ممتازًا لـ VLOOKUP لمقارنة القوائم في Excel لتحديد ما إذا كانت التطابقات موجودة.

خمس نصائح لتحسين أداء VLOOKUP

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

فيما يلي ست نصائح وحيل يمكنك تطبيقها لتحسين أداء VLOOKUP في Excel.

1. تحديد نطاق البحث الخاص بك

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

بدلاً من ذلك ، حاول استخدام النطاق الدقيق المطلوب لبياناتك (على سبيل المثال ، A1: A100). يؤدي ذلك إلى تقليل عدد الخلايا التي تحتاج وظيفة VLOOKUP إلى تقييمها.

2. استخدم المراجع المطلقة

عند تحديد نطاق بيانات (مثل الخلايا من B2 إلى B5) ، استخدم المراجع المطلقة. يضمن ذلك تناسق الصيغة ووجود البيانات عند نسخها عبر خلايا متعددة.

هنا مثال:

= VLOOKUP (A2، $ B $ 2: $ B $ 5، 1، FALSE)

3. فرز البيانات الخاصة بك

إذا كنت تعلم أن البيانات الموجودة في عمود البحث يتم فرزها بترتيب تصاعدي ، فيمكنك استخدام VLOOKUP مع TRUE أو 1 للوسيطة "range_lookup".

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

4. استخدم VLOOKUP مزدوج

يمكنك استخدام وظيفتين VLOOKUP لتسريع عملية البحث في Excel.

سيحدد VLOOKUP الأول ما إذا كانت قيمة البحث موجودة عن طريق تعيين "col_index_num" على 1 و "range_lookup" إلى TRUE.

إذا أعادت TRUE ، فستسترد VLOOKUP الثانية القيمة المطلوبة مع تعيين "range_lookup" على TRUE.

5. استخدم التنسيق الشرطي

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

ستجد قائمة التنسيق الشرطي في مجموعة الأنماط في علامة التبويب الصفحة الرئيسية.

الاستخدامات المتقدمة لـ VLOOKUP

تستخدم العينات المزدوجة اختبارات T لمقارنة وسائل عينتين مرتبطتين. يوضح هذا الفيديو استخدامها في Excel.

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

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

كلمتنا الأخيرة

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

لقد شاهدت أيضًا أمثلة لوظائف البحث والمراجع الأخرى ، مثل XLOOKUP و INDEX و MATCH. ستؤدي إضافة هذه العناصر إلى مهاراتك إلى تعزيز قدرات تحليل البيانات لديك.

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