دوال البحث المتقدم في إكسل

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

مايكروسوفت إكسل هو أحد الأدوات الأكثر شيوعاً في تحليل البيانات وتنظيمها في العالم المهني والأكاديمي. يحتوي إكسل على مجموعة واسعة من الدوال التي تساعد في استرجاع وتحليل البيانات بشكل دقيق وفعال. من بين هذه الدوال، تعتبر دوال البحث المتقدم مثل INDEX وMATCH وDGET من أكثر الدوال استخداماً في تحليل البيانات المعقدة وإجراء العمليات الحسابية المتقدمة. سنتناول في هذا المقال هذه الدوال الثلاث، مع شرح مفصل لكيفية استخدامها في حل المشاكل المتقدمة وتحقيق النتائج المطلوبة.

أولاً: دالة INDEX في إكسل

تعتبر دالة INDEX إحدى دوال إكسل الرئيسية التي تستخدم لاسترجاع قيمة أو مجموعة من القيم في نطاق معين من الخلايا. تُستخدم دالة INDEX بشكل رئيسي عند الحاجة إلى استرجاع قيم من صفوف أو أعمدة محددة بناءً على أرقام الصفوف أو الأعمدة التي تحددها.

الصيغة الأساسية لدالة INDEX

excel
CopyEdit
INDEX(array, row_num, [column_num])

• array: هو نطاق الخلايا الذي يحتوي على البيانات التي تريد استرجاعها.

• row_num: الرقم الذي يشير إلى الصف الذي يحتوي على القيمة التي تريد استرجاعها.

• column_num (اختياري): الرقم الذي يشير إلى العمود الذي يحتوي على القيمة التي تريد استرجاعها.

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

مثال:

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

excel
CopyEdit
=INDEX(B2:B10, 5)

في هذه الحالة، سيتم استرجاع القيمة الموجودة في الصف الخامس من العمود B.

ثانياً: دالة MATCH في إكسل

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

الصيغة الأساسية لدالة MATCH

excel
CopyEdit
MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: القيمة التي ترغب في العثور عليها في النطاق.

• lookup_array: هو النطاق الذي يحتوي على البيانات التي تبحث فيها.

• match_type (اختياري): نوع المطابقة الذي يمكن أن يكون:

  • 1: للبحث عن القيمة الأقرب التي تكون أقل من أو تساوي lookup_value (يجب ترتيب البيانات بترتيب تصاعدي).

  • 0: للبحث عن القيمة المطابقة تماماً.

  • -1: للبحث عن القيمة الأقرب التي تكون أكبر من أو تساوي lookup_value (يجب ترتيب البيانات بترتيب تنازلي).

مثال:

إذا كان لديك جدول يحتوي على أسماء الموظفين في العمود A وترغب في العثور على موقع اسم الموظف “أحمد” في هذا العمود، يمكنك استخدام دالة MATCH كما يلي:

excel
CopyEdit
=MATCH("أحمد", A2:A10, 0)

سيتم إرجاع رقم الصف الذي يحتوي على “أحمد” داخل النطاق A2:A10.

ثالثاً: دمج دالتي INDEX وMATCH

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

مثال على دمج دالتي INDEX و MATCH:

إذا كان لديك جدول يحتوي على أسماء الموظفين في العمود A والرواتب في العمود B، وتريد معرفة راتب الموظف “أحمد” من خلال دمج دالتي INDEX و MATCH، يمكنك كتابة الصيغة التالية:

excel
CopyEdit
=INDEX(B2:B10, MATCH("أحمد", A2:A10, 0))

هنا، تقوم دالة MATCH بالبحث عن موضع “أحمد” في العمود A، بينما تقوم دالة INDEX باستخدام الرقم الناتج من MATCH لاسترجاع الراتب المقابل له في العمود B.

رابعاً: دالة DGET في إكسل

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

الصيغة الأساسية لدالة DGET

excel
CopyEdit
DGET(database, field, criteria)

• database: هو نطاق البيانات الذي يحتوي على السجلات (الصفوف) والأعمدة.

• field: هو اسم العمود أو الرقم الذي يشير إلى العمود الذي تريد استخراج القيمة منه.

• criteria: هو نطاق يحتوي على المعايير التي يجب أن تنطبق عليها السجلات التي سيتم استخراج القيمة منها.

مثال على استخدام دالة DGET:

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

إذا كنت ترغب في استخراج راتب الموظف الذي يعمل في قسم “الهندسة”، يمكنك استخدام دالة DGET كالتالي:

excel
CopyEdit
=DGET(A1:C4, "الراتب", A6:B7)

حيث يحتوي النطاق A6:B7 على المعايير التي تحدد “الهندسة” كالقسم الذي ترغب في استخراج الراتب له. ستقوم دالة DGET بالبحث في قاعدة البيانات وتسترجع الراتب المطلوب.

خامساً: تطبيقات عملية لدوال INDEX و MATCH و DGET

إلى جانب استخدامات البحث البسيطة، يمكن توظيف دوال INDEX و MATCH و DGET في العديد من التطبيقات العملية المتقدمة. إليك بعض الأمثلة على كيفية الاستفادة من هذه الدوال في بيئات عمل متطورة:

1. البحث عبر الجداول الكبيرة: يمكن دمج INDEX و MATCH في حالات كبيرة من البيانات حيث تكون هناك حاجة إلى البحث السريع داخل جداول تحتوي على مئات أو آلاف السجلات.

2. التحليل المالي: يمكن استخدام دالة DGET لاستخراج بيانات محددة مثل الحسابات المالية أو المرتبات بناءً على معايير معقدة في جداول بيانات كبيرة.

3. إدارة المخزون: تستخدم الشركات دوال مثل INDEX و MATCH لتحليل بيانات المخزون، حيث يمكن استخدامها لاستخراج معلومات دقيقة عن الكميات المتاحة والمبيعات.

سادساً: مقارنة بين دوال VLOOKUP و INDEX/MATCH و DGET

• VLOOKUP: تُستخدم للبحث عن قيمة في العمود الأول من نطاق معين وإرجاع قيمة من عمود آخر. ومع ذلك، VLOOKUP لا يوفر مرونة كافية، حيث يمكنه فقط إجراء البحث من اليسار إلى اليمين.

• INDEX/MATCH: يوفر مرونة أكبر في البحث عبر صفوف وأعمدة مختلفة، ويمكن استخدامه في البحث من اليمين إلى اليسار. كما أنه أكثر كفاءة في حالة التعامل مع جداول بيانات كبيرة.

• DGET: يُستخدم لاستخراج قيمة واحدة استنادًا إلى معايير معينة من قاعدة بيانات تحتوي على العديد من السجلات. يُعتبر خياراً ممتازاً عند التعامل مع البيانات في شكل قاعدة بيانات.

الخاتمة

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