دوال البحث في Excel

يوجد العديد من دوال البحث في برنامج Excel لذا يعانى الكثير من الناس من عدم الفهم الكافي لهذه الدوال ومتى نستخدم كل منهم , لذا سنتعرف في هذا الدرس على أهم دوال البحث في برنامج Excel وهي ( دالة INDEX - دالة MATCH - دالة HLOOKUP - دالة VLOOKUP )





دالة INDEX


دالة INDEX ترجع قيمة مرجع تقوم بتحديده عن طريق رقم الصف ورقم العمود الموجود فيه داخل مصفوفة ( نطاق ) .





معاملات دالة INDEX


array : المصفوفة ( النطاق )

row_num : رقم الصف

[column_num] : رقم العمود





مثال على دالة INDEX


فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 2 والعمود رقم 3 داخل النطاق B2:D5 وهو المرجع D3

دوال البحث في Excel




فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 3 والعمود رقم 1 داخل النطاق B2:D5 وهو المرجع B4

دوال البحث في Excel




فى الشكل التالى دالة INDEX ترجع قيمة المرجع الموجود فى الصف رقم 2 والعمود رقم 2 داخل النطاق B2:D5 وهو المرجع C3

دوال البحث في Excel






دالة MATCH


دالة MATCH تبحث عن قيمة داخل مصفوفة , ثم ترجع مكان هذه القيمة داخل المصفوفة .




معاملات دالة MATCH


lookup_value : القيمة التى تريد البحث عنها .

lookup_array : المصفوفة التى سيتم البحث داخلها .

[match_type] : يستخدم هذا المعامل لتحديد هل ستقوم دالة MATCH بالبحث عن قيمة مطابقة أم البحث عن قيمة تقريبية , هذا المعامل يأخذ أحد القيم الثلاثة التالية ( القيمة الافتراضية هى 1 ) :



القيمة 0 ( البحث عن قيمة مطابقة )

- دالة MATCH ستقوم بالبحث عن القيمة التى تريدها داخل المصفوفة  .

- اذا وجدتها سترجع دالة MATCH مكان هذه القيمة .

- اذا لم تجدها سترجع دالة MATCH الخطأ N/A#


ملحوظة : ليس من الضرورى أن تكون المصفوفة مرتبة .





القيمة 1 ( البحث عن قيمة تقريبية )

- دالة MATCH ستقوم بالبحث عن القيمة التى تريدها داخل المصفوفة  .

- اذا وجدتها سترجع دالة MATCH مكان هذه القيمة .

- اذا لم تجدها ستقوم دالة MATCH  بالبحث عن أكبر قيمة أصغر من القيمة التى تريدها وترجع مكان هذه القيمة , وفى حالة كان لا يوجد أى قيمة أصغر من القيمة التى تريدها سترجع دالة MATCH الخطأ N/A#


ملحوظة : لابد أن تكون المصفوفة مرتبة تصاعديا للحصول على نتائج صحيحة .





القيمة 1- ( البحث عن قيمة تقريبية )

- دالة MATCH ستقوم بالبحث عن القيمة التى تريدها داخل المصفوفة  .

- اذا وجدتها سترجع دالة MATCH مكان هذه القيمة .

- اذا لم تجدها ستقوم دالة MATCH  بالبحث عن أصغر قيمة أكبر من القيمة التى تريدها وترجع مكان هذه القيمة , وفى حالة كان لا يوجد أى قيمة أكبر من القيمة التى تريدها سترجع دالة MATCH الخطأ N/A#


ملحوظة : لابد أن تكون المصفوفة مرتبة تنازليا للحصول على نتائج صحيحة .





مثال على دالة MATCH


في الشكل التالي دالة MATCH ستقوم بالبحث عن القيمة 1002 داخل النطاق A2:A6 فتجدها وترجع مكان هذه القيمة وهو 3

دوال البحث في Excel




في الشكل التالي دالة MATCH ستقوم بالبحث عن القيمة 1005 داخل النطاق A2:A6 فتجدها وترجع مكان هذه القيمة وهو 2

دوال البحث في Excel




في الشكل التالي دالة MATCH ستقوم بالبحث عن القيمة 1006 داخل النطاق A2:A6 فلن تجدها ولن تقوم بالبحث عن أى قيمة بديلة وترجع الخطأ N/A#

دوال البحث في Excel





دالة 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"

دوال البحث في Excel




فى الشكل التالى دالة HLOOKUP ستقوم بالبحث عن القيمة 102 داخل الصف الأول فى النطاق B1:F2 فتجدها وترجع القيمة المقابلة لها فى الصف رقم 2 وهى "TV"

دوال البحث في Excel




فى الشكل التالى دالة HLOOKUP ستقوم بالبحث عن القيمة 106 داخل الصف الأول فى النطاق B1:F2 فلن تجدها ولن تقوم بالبحث عن قيمة بديلة لذا سترجع الخطأ N/A#

دوال البحث في Excel






دالة VLOOKUP


يمكنك التعرف على دالة VLOOKUP بالتفصيل من خلال هذا الدرس : دالة VLOOKUP