‹‹الدرس 7
Microsoft Excel من الصفر للاحتراف · الدرس 7 من 14

دوال البحث: VLOOKUP وXLOOKUP وINDEX/MATCH

بقلم 2 دقائق قراءة282 كلمة

جلب بيانات عميل من جدول آخر برقمه، الفرق بين البحث المطابق والتقريبي، وحل خطأ #N/A.

دوال البحث هي أكثر ما يُسأل عنه في اختبارات Excel للبنوك. الفكرة: لديك رقم عميل، وتريد إحضار اسمه أو رصيده من جدول آخر.

VLOOKUP

=VLOOKUP(القيمة_المبحوث_عنها, الجدول, رقم_العمود, نوع_البحث)

A: رقم العميلB: الاسمC: الفرعD: الرصيد
210045سارة أحمدالمعادي45,300
310046محمود عليالدقي12,750
410047منى حسنالمعادي180,000
مثال: رصيد عميل

في G1 رقم العميل 10046. المطلوب رصيده:
=VLOOKUP(G1,$A$2:$D$4,4,FALSE) = 12,750

  • G1: ما نبحث عنه.
  • $A$2:$D$4: الجدول (مطلق لأننا سننسخ المعادلة).
  • 4: الرصيد في العمود الرابع من الجدول.
  • FALSE: تطابق تام.
VLOOKUP تبحث في أول عمود من الجدول فقط، وتُرجع من عمود على يمينه في الترتيب. ولا تنسَ FALSE وإلا قد تعطيك نتيجة خاطئة بدون رسالة خطأ.

البحث التقريبي (TRUE)

مفيد للشرائح، بشرط أن يكون العمود الأول مرتبًا تصاعديًا:

الحد الأدنىسعر الفائدة
010%
50,00011%
200,00012.5%

=VLOOKUP(150000,F2:G4,2,TRUE) = 11%، لأنه يأخذ أكبر قيمة أقل من أو تساوي 150,000.

XLOOKUP: الأحدث والأسهل

=XLOOKUP(القيمة, عمود_البحث, عمود_النتيجة, [إن لم يوجد])

=XLOOKUP(G1,A2:A4,D2:D4,"غير موجود")

  • تبحث في أي عمود وترجع من أي عمود، حتى لو كان على اليسار.
  • التطابق التام افتراضي.
  • تحتوي على رسالة بديلة بدل #N/A.
  • متوفرة في Microsoft 365 وExcel 2021 وما بعدهما.

INDEX وMATCH

البديل الكلاسيكي المرن قبل XLOOKUP، ويعمل في كل الإصدارات:

  • =MATCH(G1,A2:A4,0) يرجع موضع القيمة (2).
  • =INDEX(D2:D4,2) يرجع العنصر الثاني من العمود.
  • معًا: =INDEX(D2:D4,MATCH(G1,A2:A4,0))

أسباب خطأ #N/A

السببالحل
القيمة غير موجودة فعلًااستخدم IFERROR أو وسيط XLOOKUP الرابع
رقم مخزن كنص في جدول ورقم في الآخروحّد نوع البيانات
مسافات زائدة في النصاستخدم TRIM
الجدول غير مطلق وتحرك عند النسخأضف $

الخلاصة

  • VLOOKUP تبحث في أول عمود، والوسيط الأخير FALSE للتطابق التام.
  • TRUE للشرائح بشرط ترتيب تصاعدي.
  • XLOOKUP أسهل وتبحث في أي اتجاه.
  • INDEX/MATCH بديل مرن يعمل في كل الإصدارات.
الكلمات المفتاحية: