دوال البحث المتقدم INDEX وMATCH وDGET في مايكروسوفت إكسل

يُعَد الهدف الأساسي من أَتَمتة الأنظمة هو الحصول على البيانات الصحيحة في الوقت المناسب دون تأخير، وذلك كي لا تَفقِد البيانات أهميتها عند اتخاذ قرار ما؛ لذلك لا بُدّ من طريقةٍ لتحقيق عمليات البحث ضمن البيانات بأساليب مُتقدمة. وسنتعرّف فيما يلي على بعض الدوال والأساليب المُختصة بذلك.

والدوال التي سَنَتطرق إليها هي INDEX وMATCH، وهما دالتان تَتَميزان بالسرعة والقوة والإمكانيات العالية، وبالتالي يُمكن القول أنها تُغني عن دوال البحث الأخرى، وأيضًا سنتعرّف على الدالة DGET وهي إحدى دوال البحث المُتقدِّم.

الدالة INDEX

تُرجِع الدالة INDEX قيمةً ضمن جدول أو نطاق محدد بواسطة فهارس أرقام الصفوف والأعمدة، أي اعتمادًا على رقم الصف ورقم العمود تُرجِع قيمة التقاطع لهما.

البناء العام لصيغة الدالة

INDEX(array, row_num, [column_num])‎

تحتاج الدالة إلى ثلاث معطيات:

المصفوفة Array: ويُمثّل نطاق الخلايا الذي يحتوي البيانات التي تَبحث فيها الدالة، وهذا المُعطى مطلوب في صيغة الدالة.
رقم السطر row_num: ويُمثّل رقم السطر الذي ستبحث فيه الدالة عن التقاطع، وهذا المُعطى مطلوبٌ في صيغة الدالة أيضًا.
رقم العمود column_num: ويُمثّل رقم العمود الذي ستبحث فيه الدالة عن التقاطع.

مثال

لدينا بيانات قروض العملاء الخاصة بإحدى المصارف ونحتاج إلى إيجاد المبلغ المسدد للعميل محمد قاسم.

نضع المؤشر في الخلية التي نُريد إرجاع القيمة إليها، ونكتب الصيغة التالية في الخلية E16:

=INDEX(B2:I14,10,8)‎

يُمثّل الرقم 10 رقم صف البيانات. حيث نبدأ العد من أول صف ضمن المجال B2:I14 وليس من بداية ورقة العمل، كذلك فيما يخض رقم العمود الذي يقابل الرقم 8 في مثالنا.

تفسير عمل الدالة

تبحث الدالة عن قيمة تقاطع الصف رقم 10 الذي يُمثّل العميل محمد قاسم، مع العمود رقم 8 الذي يُمثّل قيمة المبلغ الذي سدده وهي 122466.

بالنظر إلى عمل الدالة نجدها عديمة النفع، حيث يمكنني الحصول على النتيجة مباشرةً بدون الحاجة للدالة كوني من أُحدد رقم الصف ورقم العمود يدويًا، ولكن سيكون الأمر صعبًا عندما يكون عدد سجلّات الجدول (يُقصد بالسجلات الصفوف في الجدول) 1000 سجلّ، وكلما زاد حجم البيانات زادت الصعوبة، وزادت معها أخطاء البحث اليدوي أيضًا، وهنا يأتي دور الدالة الثانية لتكمّل عمل هذه الدالة.

الدالة MATCH

تعمل هذه الدالة على البحث عن قيمةٍ ضمن صف أو عمود، وتُعطينا رقم هذا الصف أو رقم هذا العمود عند تطابق قيمة الخلية فيه مع قيمة البحث في الدالة.

البناء العام لصيغة الدالة

MATCH(lookup_value, lookup_array, [match_type])

تحتاج الدالة إلى ثلاث معطياتٍ argument:

قيمة البحث lookup_value: وهي القيمة التي تُريد مُطابَقتها لإيجاد رقم صفها أو عمودها، وهذا المُعطى مطلوبٌ في صيغة الدالة.
مصفوفة البحث lookup_array: وهو نطاق الخلايا الذي يحتوي البيانات التي تبحث فيها الدالة، وهذا المُعطى مطلوبٌ في صيغة الدالة أيضًا.
نوع المطابقة match_type: وتحدد هذه الوسيطة أسلوب المطابقة الذي ستنفذه الدالة، حيث تأخذ القيم التالية:
- (القيمة 1) إذا أردنا الحصول على نتائج أكبر قِيمةً من قيمة lookup_value أو نتائج مساويةً لها.
- (القيمة 0) إذا أردنا الحصول على نتائج مساوية تمامًا لقيمة lookup_value. وغالبًا نستعمل هذه القيمة لأننا نُريد نتائج مطابقةً لما نبحث عنه.
- (القيمة 1-) إذا أردنا الحصول على نتائج أصغر قيمةً من قيمة lookup_value.

نُطَبق الدالة على المثال السابق، حيث نُريد تطبيق الدالة على الصفوف لنعرف رقم الصف الخاص ببيانات العميل محمد القاسم، فنكتب الصيغة التالية في الخلية E16:

=MATCH("محمد قاسم",B2:B14,0)

ستبحث الدالة ضمن المجال، وتُعِيد لنا رقم الصف عند تطابق قيمة البحث مع القيمة الموجودة في أول خليةٍ مطابقةٍ لقيمة البحث وهو الصف رقم 10.

والآن لنطبّق الدالة على الأعمدة لنعرف رقم العمود الذي يحتوي المبلغ المسدَّد، ونكتب الصيغة التالية في الخلية E16:

=MATCH("المبلغ المسدد",B2:I2,0)

ستبحث الدالة ضمن المجال، وتُعِيد لنا رقم العمود عند تطابق قيمة البحث مع القيمة الموجودة في أول خليةٍ مطابقةٍ لقيمة البحث وهو العمود رقم 8.

كما نُلاحِظ أنه يمكننا إيجاد رقم السطر أو العمود الآن بأسلوبٍ سهل ودقيق، والآن لندمج الدالتين معًا:

INDEX(array,
         MATCH(lookup_value, lookup_array, [match_type]),
         MATCH(lookup_value, lookup_array, [match_type]))‎

وبالعودة إلى المثال السابق يُصبِح شكل الدالة كالآتي:

=INDEX(B2:I14,MATCH("محمد قاسم",B2:B14,0),MATCH("المبلغ المسدد",B2:I2,0))‎

وستكون نفس النتيجة التي حصلنا عليها في المثال الأول وهي 122466.

اقتباس

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

ولزيادة قوة الدالة أكثر، نعتمد خليتين مرجعيتين لأخذ قيم البحث منهم، ونُعدّل صيغة الدالة، حيث نستبدل قيمة البحث "محمد قاسم" بالخلية C17، ونستبدل قيمة البحث "المبلغ المسدد" بالخلية C18، ونضع النصوص داخل هاتين الخليتين لنحصل على الصيغة التالية:

 =INDEX(B2:I14,MATCH(C17,B2:B14,0),MATCH(C18,B2:I2,0))‎

الآن عند تغيير قيمة البحث لدينا إلى عميلٍ آخر، أو للبحث عن عدد دفعات العميل مثلًا، نستطيع ذلك من خلال تبديل القيم في الخلايا C17 وC18.

وبذلك تمكّنّا من البحث عن قيمةٍ ما ضمن البيانات بوجود شرطين مختلفين، أحدهما في الصفوف والآخر في الأعمدة. ويمكننا دمج هذه الدوال مع دوال بحثٍ أُخرى مثل دمج الدالة MATCH مع الدالة VLOOKUP، حيث ستُعيد لنا الدالة MATCH رقم العمود دون أن نعدّه يدويًا من خلال وضع اسم العمود في قيمة البحث لتعمل الدالة VLOOKUP بدقة وبطريقةٍ أكثر تقدمًا.

الدالة DGET

هي إحدى دوال البحث المتقدمة كونها تجاوزت الكثير من عيوب دوال البحث الأخرى مثل دالة VLOOKUP، فمن خلال هذه الدالة لن نكون مُلزَمين بأن يكون عمود البحث هو العمود الأول في نطاق البيانات، إذ يُمكن لهذه الدالة البحث على جانِبَي عمود البحث، وتُعَد من دوال قواعد البيانات database، ولهذا يجب أن تكون البيانات لدينا مجدولة، أي أن مصدر البيانات (الجدول الذي نريد إرجاع القيم منه) يجب أن يحتوي على الرؤوس (تسميات الأعمدة)، كما يجب تصميم جدول لتحديد قواعد البحث (خلايا مرجعية للبحث).

سنوضِّح الفكرة أكثر من خلال المثال لاحقًا.

البناء العام لصيغة الدالة

DGET(database, field, criteria)‎

تحتاج الدالة إلى ثلاث معطياتٍ:

قاعدة الببانات Database: وهو نطاق الخلايا الذي تَتَألف منه القائمة أو قاعدة البيانات، وهذا المُعطى مطلوبٌ في صيغة الدالة.
الحقل Field: وتشير إلى العمود الذي تستخدمه الدالة، وهذا المُعطى مطلوبٌ في صيغة الدالة أيضًا. وتتعرف عليه الدالة بإدخال تسمية العمود مع تضمينها بين علامتَي اقتباس مُزدوجتيْن، مثل: "اسم العميل"، أو "رقم العقد"، أو رقم يُمثّل موضع العمود في القائمة (بدون علامات اقتباس) مثل 1 للعمود الأول، و2 للعمود الثاني، وهكذا، أو عن طريق خلية مرجعية.
المعيار Criteria: وهو نطاق الخلايا التي تحتوي على الشروط المحددة من قِبَل المستخدم. يُمكن استخدام أي نطاقٍ لوسيطة المعايير، طالما أن الوسيطة تحتوي على تسمية عمود واحد على الأقل وخلية واحدة على الأقل أسفل تسمية العمود لتحديد شرط للعمود. وهذا المُعطى مطلوبٌ في صيغة الدالة.

مثال

نُريد البحث ضمن البيانات التالية عن نوع العقد والرقم الخاص بالموظف احمد احمد باستعمال الدالة DGET.

نجد أن الشروط لدينا مُحَققة والأعمدة تَحوي على رؤوسٍ، لذا نُصمم الجدول الصغير التالي لإجراء العمليات.

من خلال جدول البيانات السابق، نَجد أن اسم العمود الذي يحوي قيمة البحث (شرط البحث) هو عمود الاسم، وأنّ اسم العمود الذي نُريد إرجاع البيانات منه هو العمود نوع العقد والعمود الرقم، بحيث يكون شرط البحث هنا هو الموظف احمد احمد من العمود الاسم. نطبق الدالة في الخلية I4 ونكتب الصيغة التالية:

=DGET(A1:F12,I3,H3:H4)

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

لنغير الآن قيمة الوسيطة الثانية Field في الدالة، ونضع بدلًا من نوع العقد اسم العمود الأول الرقم، لنجد أن الدالة تعمل جيدًا بغضّ النظر عما إذا كانت البيانات التي نُريد البحث عنها قبل عمود الشرط (الاسم) أو بعده، وبتطبيق المعادلة لِتُرجَع القيمة 605865 التي تقابل رقم الموظف أحمد أحمد، والدالة عَمِلت جيدًا.

يُمكننا التعامل مع المُعطى الثاني Field في هذه الدالة يدويًا إما عن طريق كتابة اسم العمود بين اشارتي تنصيص "اسم العمود"، كما يلي:

=DGET(A1:F12,”نوع العقد”,H3:H4)
=DGET(A1:F12,”الرقم”,H3:H4)

أو يمكننا الاستعاضة عنها برقم العمود كما يلي:

=DGET(A1:F12,5,H3:H4)
=DGET(A1:F12,1,H3:H4)

وكل هذه الصيغ صحيحةٌ وتُعطي نفس النتيجة.

اقتباس

تنويه: عند تطبيق هذه الدالة يجب الانتباه إلى أن تكون القيم الموجودة ضمن عمود الشرط قيمًا فريدةً (لا يحوي بياناتٍ مكررةِ) وإلا فلن تعمل الدالة جيدًا وسَتُرجِع لنا القيمة !NUM#.

خاتمة

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