دعونا نفكر في كيفية الجمع بسرعة بين جدولين كبيرين مع الأعمدة والصفوف، أي إجراء تحديد ليس باستخدام معلمة واحدة (مثل الدالات VLOOKUP أو HLOOKUP)، ولكن بواسطة اثنين في وقت واحد (باستخدام الدالتين INDEX و MATCH).
إذا كنت معتادًا على الدالة VLOOKUP أو نظيرتها الأفقية HLOOKUP، فعليك أن تتذكر أن هذه الوظائف الرائعة تبحث عن المعلومات باستخدام معلمة واحدة فقط، أي في صفيف أحادي البعد - حسب الصف أو حسب العمود. وإذا كنا بحاجة إلى تحديد البيانات من جدول ثنائي الأبعاد عن طريق مصادفة معلمتين في وقت واحد - الصف والعمود في نفس الوقت؟ دعونا نفكر في عدة خيارات لدمج الجداول.
لنفترض أننا بحاجة إلى الجمع بين جدولين يعرضان محفظة القروض ومحفظة الضمانات:
يمكننا البدء في استخدام الدالة VLOOKUP لدمج كل عمود على حدة، ولكن إذا كانت جداولنا تحتوي على عدد كبير جدًا من الصفوف والأعمدة، فقد يتحول هذا التمرين إلى ألم حقيقي. ومع ذلك، هناك طريقة بسيطة إلى حد ما للخروج من هذا الموقف، نظرًا لأن Excel يحتوي على وظيفتين ممتازتين، INDEX وMATCH من الفئة المراجع والمصفوفات
حسنًا، ما الذي يتعين علينا فعله لربط الجدولين بسرعة؟ لنبدأ بنسخ رأس الجدول الثاني (الذي سننضم إليه) ونلصقه بجوار رأس الجدول الأول. باسم الغطاء، الوظيفة MATCH سيعطينا الرقم التسلسلي للعمود، ووفقًا لرقم المعاملة - الرقم التسلسلي للخط الذي نحتاجه.
في الواقع، نريد إيجاد قيمة خلية من تقاطع صف وعمود معينين في الجدول. من أجل الوضوح، دعونا نقسم المهمة إلى ثلاث مراحل:
لذلك، من خلال الجمع بين كل ما سبق في صيغة واحدة، نحصل على الخلية F14 الصيغة التالية:
=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))
بهذه الطريقة، من خلال توسيع الصيغة على النطاق بأكمله، سنقوم بإرفاق الحقول الإضافية بشكل صحيح. تحتاج أيضًا إلى الانتباه إلى أنه عند تمديد الصيغة، نحتاج إلى تثبيت النطاقات بعلامة الدولار ($) (للإصلاح، يمكنك استخدام المفتاح F4)، للبحث بالحرف الكبير، قم بإصلاح السطر فقط ( 1 دولار فرنك )، للبحث بأرقام المعاملات - العمود فقط ( 14 دولارًا أمريكيًا ).
دعونا نعدل قليلاً في المثال السابق ونفترض أن لدينا جداول متشابهة، ولكن تم دمج رؤوسها، وبالتالي فإن الدالة MATCH لن تساعدنا في تحديد الرقم التسلسلي للعمود بشكل صحيح.
في هذه الحالة، يمكننا إنشاء حقل تقني أعلى الجدول، وإدراج أرقام الأعمدة يدويًا. بعد ذلك، يمكننا استخدام الدالة VLOOKUP المألوفة بالفعل.
لذا، فإن صيغة خيارنا الثاني ستكون كما يلي:
=VLOOKUP($A3; $J$3:$M$19; E$1; 0)
مرة أخرى، لا تنس وضع علامة على النطاق وحقول البحث المقابلة له $ ، بحيث تعمل الصيغة بشكل صحيح ولا تعطي خطأ عند تغيير النطاقات.