الدمج السريع للجداول الكبيرة (VLOOKUP2D)

وصف

دعونا نفكر في كيفية الجمع بسرعة بين جدولين كبيرين مع الأعمدة والصفوف، أي إجراء تحديد ليس باستخدام معلمة واحدة (مثل الدالات VLOOKUP أو HLOOKUP)، ولكن بواسطة اثنين في وقت واحد (باستخدام الدالتين INDEX و MATCH).

إذا كنت معتادًا على الوظيفة VLOOKUP أو نظيرتها الأفقية HLOOKUP، فعليك أن تتذكر أن هذه الوظائف الرائعة تبحث عن المعلومات باستخدام معلمة واحدة فقط، أي في صفيف أحادي البعد - حسب الصف أو حسب العمود. وإذا كنا بحاجة إلى تحديد البيانات من جدول ثنائي الأبعاد عن طريق مصادفة معلمتين في وقت واحد - الصف والعمود في نفس الوقت؟ دعونا نفكر في عدة خيارات لدمج الجداول.

1. دمج الجداول باستخدام INDEX وMATCH

لنفترض أننا بحاجة إلى الجمع بين جدولين يعرضان محفظة القروض ومحفظة الضمانات:

يمكننا البدء في استخدام الدالة VLOOKUP لدمج كل عمود على حدة، ولكن إذا كانت جداولنا تحتوي على عدد كبير جدًا من الصفوف والأعمدة، فقد يتحول هذا التمرين إلى ألم حقيقي. ومع ذلك، هناك طريقة بسيطة إلى حد ما للخروج من هذا الموقف، نظرًا لأن Excel تحتوي على وظيفتين ممتازتين، INDEX وMATCH من الفئة المراجع والمصفوفات (Lookup and Reference)، والتي تعمل كزوج ثنائي الأبعاد VLOOKUP.

حسنًا، ما الذي يتعين علينا فعله لربط الجدولين بسرعة؟ لنبدأ بنسخ رأس الجدول الثاني (الذي سننضم إليه) ونلصقه بجوار رأس الجدول الأول. وفقًا لاسم الغطاء، ستمنحنا وظيفة MATCH الرقم التسلسلي للعمود، ووفقًا لرقم الاتفاقية - الرقم التسلسلي للخط الذي نحتاجه.

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

أولاً، نحتاج إلى تحديد رقم السطر المطابق لاتفاقية الائتمان المحددة. سيساعد هذا في جعل الدالة MATCH من الفئة المراجع والمصفوفات (Lookup and Reference). على وجه الخصوص، الصيغة MATCH(A14; K2:K18; 0) سيعطينا النتيجة المرجوة (للمعاملة "№5398" سيكون الرقم الترتيبي 13). الوسيطة الأولى لهذه الوظيفة هي القيمة التي تم البحث عنها (agreement "№5398" من الخلية A14)، والثاني هو نطاق الخلايا التي نبحث فيها عن اتفاقية (العمود الذي يحتوي على أرقام الاتفاقية في الجدول - K2:K18)، وتحدد الوسيطة الثالثة نوع البحث (0 - تطابق الاسم تمامًا، البحث التقريبي محظور).
ثانيا، بطريقة مماثلة تماما، يجب علينا تحديد الرقم التسلسلي للعمود في الجدول بالمحتوى الذي نحتاجه. وظيفة MATCH(F1; J1:M1; 0) سيقوم بذلك ويصدر، على سبيل المثال، القيمة 3 لاسم الضمان الذي حدده المستخدم في الخلية F1.
وأخيرًا، ثالثًا، نحتاج إلى دالة يمكنها إخراج محتويات خلية من جدول حسب رقم الصف والعمود - الدالة INDEX من نفس الفئة المراجع والمصفوفات (Lookup and Reference). الوسيط الأول لهذه الدالة هو نطاق الخلايا (في حالتنا، هو الجدول بأكمله، أي J2:M18)، والثاني هو رقم الصف، والثالث هو رقم العمود (وسوف نحددها باستخدام MATCH المهام).

لذا، بدمج كل ما سبق في صيغة واحدة، نحصل على الصيغة التالية للخلية F14:

=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))

بهذه الطريقة، من خلال توسيع الصيغة على النطاق بأكمله، سنقوم بإرفاق الحقول الإضافية بشكل صحيح. من الضروري أيضًا الانتباه إلى أنه عند تمديد الصيغة، نحتاج إلى إصلاح النطاقات بعلامة الدولار ($) (للإصلاح، يمكنك استخدام المفتاح F4)، للبحث بالغطاء، نصلح السطر فقط (1 دولار فرنك )، للبحث بأرقام المعاملات - العمود فقط (14 دولارًا أمريكيًا ).

2. دمج الجداول باستخدام VLOOKUP

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

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

لذا، فإن صيغة خيارنا الثاني ستكون كما يلي:

=VLOOKUP($A3; $J$3:$M$19; E$1; 0)

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

مقالات حول الموضوع: