عمليات البحث ثنائية الاتجاه باستخدام INDEX-MATCH-MATCH

تخيل شبكة لمبيعات شهرية تمتد فيها المناطق نزولًا على اليسار، وتمتد فيها الأشهر عبر الصف العلوي. تريد الرقم الموجود عند تقاطع منطقة مختارة مع شهر مختار.

يعثر البحث العادي على قيمة بالبحث في اتجاه واحد فقط. أما البحث ثنائي الاتجاه فيبحث في الاتجاهين معًا: يعثر على الصف الصحيح والعمود الصحيح، ثم يُرجع القيمة الموجودة عند تقاطعهما.

الأداة التقليدية لذلك هي INDEX مع استدعاءين للدالة MATCH، وتُكتب غالبًا INDEX-MATCH-MATCH.

تُرجع INDEX قيمة من نطاق حسب موضعها. والصيغة الكاملة هي INDEX(array, row_num, column_num).

مرّر إليها كتلة من الخلايا ورقم صف ورقم عمود، فتعيد القيمة الموجودة في ذلك الموضع. مثلًا، في شبكة تبدأ من B2، يؤدي طلب الصف 3 والعمود 2 إلى إرجاع القيمة الموجودة على بُعد 3 صفوف وعمودين داخل تلك الكتلة.

الفكرة الأساسية: تحتاج INDEX إلى مواضع لا إلى تسميات. وهذا بالضبط ما توفره MATCH.

تعثر MATCH على موضع قيمة داخل صف واحد أو عمود واحد. وصيغتها هي MATCH(lookup_value, lookup_array, match_type).

استخدم 0 نوعًا للمطابقة من أجل مطابقة تامة. والنتيجة رقم يحدد موضع القيمة بدءًا من 1.

إذا كانت "East" هي العنصر الثاني في النطاق A2:A5، فستُرجع MATCH القيمة 2. ويمكن أن تصبح هذه القيمة رقم الصف في INDEX.

لإجراء بحث ثنائي الاتجاه، شغّل MATCH مرتين:

• تحدد MATCH الأولى الصف الذي توجد فيه المنطقة.
• تحدد MATCH الثانية العمود الذي يوجد فيه الشهر.

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

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

تصوّر هذا التخطيط. توجد تسميات المناطق في A2:A5 (East وWest وNorth وSouth). وتوجد عناوين الأشهر في B1:D1 (Jan وFeb وMar). أما أرقام المبيعات الفعلية فتملأ النطاق B2:D5.

تتحكم خليتا إدخال في البحث: تحتوي G1 على المنطقة المطلوبة، وتحتوي G2 على الشهر المطلوب.

هدفنا هو صيغة واحدة تقرأ G1 وG2 وتُرجع رقم المبيعات المطابق من B2:D5.

حدد المنطقة أولًا. تبحث MATCH في قائمة التسميات الرأسية A2:A5 عن القيمة المُدخلة في G1.

إذا كانت G1 تحتوي على "North"، وكانت North هي التسمية الثالثة، فستُرجع MATCH القيمة 3.

يخبر هذا الرقم INDEX بالصف الذي يجب قراءته من كتلة البيانات. لاحظ أننا نبحث في A2:A5، أي في التسميات فقط، وليس البيانات، لذا يتوافق الموضع 3 مع صف البيانات الثالث.

حدد الشهر بعد ذلك. تبحث MATCH هذه في صف العناوين الأفقي B1:D1 عن القيمة الموجودة في G2.

إذا كانت G2 تحتوي على "Feb"، وكان Feb هو العنوان الثاني، فستُرجع MATCH القيمة 2.

يصبح هذا الرقم موضع العمود في INDEX. وكما هو الحال مع الصفوف، نبحث في العناوين B1:D1 فقط، حتى يتوافق الموضع مع أعمدة البيانات في B2:D5.

غلّف الآن استدعاءَي MATCH داخل INDEX. تمثل كتلة البيانات B2:D5 المصفوفة، وتوفر MATCH الخاصة بالصف رقم الصف، بينما توفر MATCH الخاصة بالعمود رقم العمود.

عندما تكون G1 هي "North" وG2 هي "Feb"، تُرجع MATCH الخاصة بالصف القيمة 3، وتُرجع MATCH الخاصة بالعمود القيمة 2، لذا تُرجع INDEX القيمة الموجودة في الصف 3 والعمود 2 من B2:D5.

هذه الصيغة الواحدة هي البحث ثنائي الاتجاه كاملًا.

لنفترض أن B2:D5 تحتوي على ما يلي: صف North هو Jan بقيمة 50 وFeb بقيمة 80 وMar بقيمة 65.

• تُرجع MATCH("North", A2:A5, 0) القيمة 3.
• تُرجع MATCH("Feb", B1:D1, 0) القيمة 2.
• تقرأ INDEX(B2:D5, 3, 2) الصف 3 والعمود 2، وتُرجع 80.

غيّر G1 إلى "East" أو G2 إلى "Mar"، فستُعاد حسابات الصيغة كاملةً على الفور. تلك هي قوة تشغيل INDEX باستخدام بحثين من MATCH.

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

تتيح لك طريقة INDEX-MATCH-MATCH اختيار كلٍّ من الصف والعمود ديناميكيًا باستخدام التسمية. يمكنك إعادة ترتيب الأعمدة وإدراج أشهر جديدة، وستظل الصيغة تعمل لأنها تطابق نص العنوان، وليس عددًا ثابتًا.

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

في هذه الحالة، يمتد A2:A5 على 4 صفوف، ويمتد B2:D5 أيضًا على 4 صفوف، لذا فإن نتيجة MATCH التي تساوي 3 تعني فعلًا صف البيانات الثالث. أما إذا بحثت بالخطأ في A1:A5، الذي يتضمن عنوانًا، فستنزاح المواضع بمقدار صف واحد وستحصل على الخلية الخطأ.

اختبر مدى فهمك لنمط البحث ثنائي الاتجاه.

لقد تعلمت نمط البحث ثنائي الاتجاه:

• تُرجع INDEX قيمةً عند موضع صف وعمود داخل كتلة.
• يبحث MATCH الأول عن الصف من خلال البحث في التسميات الرأسية.
• يبحث MATCH الثاني عن العمود من خلال البحث في العناوين الأفقية.

تقرأ الصيغة المدمجة =INDEX(data, MATCH(row), MATCH(col)) مُدخلين، وتُرجع القيمة عند تقاطعهما. احرص على أن تكون نطاقات MATCH بالحجم نفسه لكتلة البيانات لتجنب عدم التطابق.