يوجد العديد من دوال البحث في برنامج Excel لذا يعانى الكثير من الناس من عدم الفهم الكافي لهذه الدوال ومتى نستخدم كل منهم , لذا سنتعرف في هذا الدرس على أهم دوال البحث في برنامج Excel وهي ( دالة INDEX - دالة MATCH - دالة HLOOKUP - دالة VLOOKUP )
دالة INDEX
دالة INDEX ترجع قيمة مرجع تقوم بتحديده عن طريق رقم الصف ورقم العمود الموجود فيه داخل مصفوفة ( نطاق ) .
معاملات دالة INDEX
array : المصفوفة ( النطاق )
row_num : رقم الصف
[column_num] : رقم العمود
فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 2 والعمود رقم 3 داخل النطاق B2:D5 وهو المرجع D3
فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 3 والعمود رقم 1 داخل النطاق B2:D5 وهو المرجع B4
فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 2 والعمود رقم 2 داخل النطاق B2:D5 وهو المرجع C3
دالة MATCH تبحث عن قيمة داخل مصفوفة , ثم ترجع مكان هذه القيمة داخل المصفوفة .
مثال على دالة INDEX
فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 2 والعمود رقم 3 داخل النطاق B2:D5 وهو المرجع D3
فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 3 والعمود رقم 1 داخل النطاق B2:D5 وهو المرجع B4
فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 2 والعمود رقم 2 داخل النطاق B2:D5 وهو المرجع C3
دالة MATCH
دالة MATCH تبحث عن قيمة داخل مصفوفة , ثم ترجع مكان هذه القيمة داخل المصفوفة .
معاملات دالة MATCH
lookup_value : القيمة التى تريد البحث عنها .
lookup_array : المصفوفة التى سيتم البحث داخلها .
[match_type] : يستخدم هذا المعامل لتحديد هل ستقوم دالة MATCH بالبحث عن قيمة مطابقة أم البحث عن قيمة تقريبية , هذا المعامل يأخذ أحد القيم الثلاثة التالية ( القيمة الافتراضية هى 1 ) :
القيمة 0 ( البحث عن قيمة مطابقة )
- دالة MATCH ستقوم بالبحث عن القيمة التى تريدها داخل المصفوفة .
- اذا وجدتها سترجع دالة MATCH مكان هذه القيمة .
- اذا لم تجدها سترجع دالة MATCH الخطأ N/A#
ملحوظة : ليس من الضرورى أن تكون المصفوفة مرتبة .
القيمة 1 ( البحث عن قيمة تقريبية )
القيمة 1 ( البحث عن قيمة تقريبية )
- دالة MATCH ستقوم بالبحث عن القيمة التى تريدها داخل المصفوفة .
- اذا وجدتها سترجع دالة MATCH مكان هذه القيمة .
- اذا لم تجدها ستقوم دالة MATCH بالبحث عن أكبر قيمة أصغر من القيمة التى تريدها وترجع مكان هذه القيمة , وفى حالة كان لا يوجد أى قيمة أصغر من القيمة التى تريدها سترجع دالة MATCH الخطأ N/A#
ملحوظة : لابد أن تكون المصفوفة مرتبة تصاعديا للحصول على نتائج صحيحة .
القيمة 1- ( البحث عن قيمة تقريبية )
القيمة 1- ( البحث عن قيمة تقريبية )
- دالة MATCH ستقوم بالبحث عن القيمة التى تريدها داخل المصفوفة .
- اذا وجدتها سترجع دالة MATCH مكان هذه القيمة .
- اذا لم تجدها ستقوم دالة MATCH بالبحث عن أصغر قيمة أكبر من القيمة التى تريدها وترجع مكان هذه القيمة , وفى حالة كان لا يوجد أى قيمة أكبر من القيمة التى تريدها سترجع دالة MATCH الخطأ N/A#
دالة HLOOKUP
دالة HLOOKUP تبحث عن قيمة داخل الصف الأول فى جدول وعندما تجدها ترجع القيمة المقابلة لها فى صف أخر .
معاملات دالة HLOOKUP
lookup_value : القيمة التى تريد البحث عنها داخل الصف الأول .
table_array : الجدول الذى يحتوى على البيانات .
row_index_num : رقم الصف الذى يحتوى على القيمة التى سترجعها دالة HLOOKUP وهى القيمة المقابلة للقيمة التى تم ايجادها فى الصف الأول
[range_lookup] : يستخدم هذا المعامل لتحديد هل ستقوم دالة HLOOKUP بالبحث عن قيمة مطابقة Exact match أم البحث عن قيمة تقريبية Approximate match , هذا المعامل يأخذ أحد القيمتين FALSE أو TRUE ( القيمة الافتراضية هى TRUE ) كالتالى :
FALSE للبحث عن قيمة مطابقة
- دالة HLOOKUP ستقوم بالبحث عن القيمة التى تريدها داخل الصف الأول .
- اذا وجدتها سترجع دالة HLOOKUP القيمة المقابلة لها فى الصف الذى حددت رقمه فى المعامل row_index_num
- اذا لم تجدها سترجع دالة HLOOKUP الخطأ N/A#
ملحوظة : ليس من الضرورى أن يكون الصف الأول مرتبا .
TRUE للبحث عن قيمة تقريبية
- دالة HLOOKUP ستقوم بالبحث عن القيمة التى تريدها داخل الصف الأول .
- اذا وجدتها سترجع دالة HLOOKUP القيمة المقابلة لها فى الصف الذى حددت رقمه فى المعامل row_index_num
- اذا لم تجدها ستقوم دالة HLOOKUP بالبحث عن أكبر قيمة أصغر من القيمة التى تريدها وترجع القيمة المقابلة لها فى الصف الذى حددت رقمه فى المعامل row_index_num وفى حالة كان لا يوجد أى قيمة أصغر من القيمة التى تريدها سترجع دالة HLOOKUP الخطأ N/A#
ملحوظة : لابد أن يكون الصف الأول مرتبا تصاعديا للحصول على نتائج صحيحة .
مثال على دالة HLOOKUP
الجدول التالى يحتوى على رقم المنتج واسم المنتج , وسنستخدم دالة HLOOKUP للبحث عن اسم المنتج بدلالة رقم المنتج .
فى الشكل التالى دالة HLOOKUP ستقوم بالبحث عن القيمة 103 داخل الصف الأول فى النطاق B1:F2 فتجدها وترجع القيمة المقابلة لها فى الصف رقم 2 وهى "Mobile"
فى الشكل التالى دالة HLOOKUP ستقوم بالبحث عن القيمة 102 داخل الصف الأول فى النطاق B1:F2 فتجدها وترجع القيمة المقابلة لها فى الصف رقم 2 وهى "TV"
فى الشكل التالى دالة HLOOKUP ستقوم بالبحث عن القيمة 106 داخل الصف الأول فى النطاق B1:F2 فلن تجدها ولن تقوم بالبحث عن قيمة بديلة لذا سترجع الخطأ N/A#