الشرح التفصيلي لأهم دوال البحث في إكسل

جدول المحتوى :

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

سوف نستعرض في مقال اليوم أكثر دوال البحث في إكسل استخداماً وأهمهم:- دالة البحث LOOKUP، دالة البحث العمودي VLOOKUP، دالة البحث الأفقي HLOOKUP، الدالة INDEX، الدالة MATCH، والدالة DGET.

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

يوجد العديد من دوال البحث في إكسل ولكن من أهمها وأكثرها استخداماً ما يلي:-

دالة البحث LOOKUP

تعد دالة البحث LOOKUP من أكثر دوال البحث في إكسل استخداماً، وقد تعتمد هذه الدالة على ثلاث وسطاء، ويتم كتابة الدالة بالشكل التالي:-

LOOKUP (Look-Value, Look-Vector, [Result-Vector])

الوسيط الأول Look-Value:- يعبر عن القيمة المراد البحث عنها في شيت الإكسل.
الوسيط الثاني Look-Vector:- يعبر عن نطاق البحث ويقصد به الخانات المطلوب البحث فيها.
أما الوسيط الثالث Result-Vector:- يعبر عن نطاق النتيجة ويقصد به الخانات المطلوب النتيجة منها.

ملحوظة هامة:- إذا تم البحث عن قيمة رقمية معينة ولكنها غير موجود بالجدول سوف يتم إرجاع القيمة للقيمة التي تجاوزتها؛ معنى ذلك عند البحث عن قيمة 30 ويوجد بالجدول قيمة 25 وقيمة 40 سوف يتم إرجاع القيمة إلى القيمة 25 لأنها تجاوزتها.

دالة البحث العمودي VLOOKUP

دالة البحث VLOOKUP هى دالة البحث العمودي في برنامج إكسل ويتم كتابتها بالشكل التالي:-

VLOOKUP (Look-Value, Table-Array, Column-Index-Num, Range-Lookup)

الوسيط الأول Look-Value:- يمثل القيمة المراد البحث عنها.
ثم الوسيط الثاني Table-Array:- يمثل الجدول المتضمن لعمود البحث.
والوسيط الثالث Column-Index-Num:- يمثل رقم عمود النتيجة بالنسبة للجدول.
والوسيط الرابع Range-Lookup:- يعبر عن مطابقة القيمة ويتم وضع True للحصول على قيمة مقاربة أو False للحصول على القيمة المطابقة.

دالة البحث الأفقي HLOOKUP

دالة البحث HLOOKUP هى دالة البحث الأفقي في برنامج إكسل ويتم كتابتها بالشكل التالي:-

HLOOKUP (Look-Value, Table-Array, Column-Index-Num, Range-Lookup)

الوسيط الأول Look-Value:- يمثل القيمة المراد البحث عنها.
ثم الوسيط الثاني Table-Array:- يمثل الجدول المتضمن لصف البحث.
والوسيط الثالث Column-Index-Num:- يمثل رقم صف النتيجة بالنسبة للجدول.
والوسيط الرابع Range-Lookup:- يعبر عن مطابقة القيمة ويتم وضع True للحصول على قيمة مقاربة أو False للحصول على القيمة المطابقة.

ملحوظة هامة:- في كلا دالتي البحث العمودي والأفقي في حالة ترك الوسيط الأخير وعدم وضعه يتم إظهار النتيجة كما لو وضعنا True أي أنه سوف يظهر القيمة التقريبية للنتيجة.

اقرأ أيضا:- الدوال الحسابية في إكسل.

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

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

الدالة INDEX

تعد الدالة INDEX من دوال البحث في إكسل وتقوم بإرجاع القيمة المطلوبة إلى فهارس الصفوف والأعمدة، بمعنى ترجع القيمة إلى التقاطع بين رقم العمود ورقم الصف ويتم كتابتها بالشكل التالي:-

INDEX (array, raw-num, [column-num])

الوسيط الأول array:- يمثل نطاق الخلايا الطلوب البحث فيها.
الوسيط الثاني raw-num :- يمثل رقم الصف الذي تبحث الدالة فيه عن التقاطع.
ثم الوسيط الثالث column-num:- يمثل رقم العمود الذي تبحث الدالة فيه عن التقاطع.

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

في نتيجة الطلاب إذا كانت العمود B يمثل اسم الطالب والعمود H يمثل نتيجة الدراسات وكنت تبحث عن مجموع الطالب أحمد في مادة الدراسات

إذن يتم كتابة الدالة INDEX (B2:H11, 10,8)

يمثل B2:H11 نطاق الجدول الذي تبحث فيه، الرقم 10 هو صف البيانات، والرقم 8 عمود البيانات، فإن الدالة سوف تبحث عن تقاطع الصف رقم 10 والذي يمثل اسم الطالب أحمد مع العمود رقم 8 والذي يمثل نتيجة الدراسات وتظهر قيمة التقاطع التي توضح نتيجة أحمد في مادة الدراسات.

الدالة MATCH

تعد الدالة MATCH من دوال البحث في إكسل إذ أنها تقوم بالبحث عن قيمة معينة بداخل صف أو عمود ثم تعطيك رقم هذا الصف أو العمود في حالة تطابق قيمة الخلية مع القيمة التي تبحث عنها في الدالة، ويتم كتابة الدالة بالشكل التالي:-

MATCH(Lookup-value, Lookup-array, [match-type])

الوسيط الأول Lookup-value:- ويمثل قيمة البحث المراد معرفة رقم الصف أو العمود المطابق لها.
الوسيط الثاني Lookup-array:- ويمثل نطاق الخلايا المطلوب بحث الدالة بداخلها.
الوسيط الثالث match-type:- وهو نوع المطابقة ويحدد أسلوب التطابق الذي تقوم به الدالة، ويأخذ القيم الآتية:-

القيمة (1) :- للحصول على نتائج قيمتها أكبر من قيمة دالة البحث.
ثم القيمة (0) :- للحصول على نتائج قيمتها مطابقة تماما لقيمة دالة البحث.
أيضا القيمة (1-) :- للحصول على نتائج قيمتها أصغر من قيمة دالة البحث.

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

ويمكننا تطبيق هذه الدالة على المثال السابق لمعرفة رقم الصف الموجود فيه اسم الطالب أحمد:-

(B2:B14)”أحمد”) MATCH=

سوف تبحث الدالة في نطاق الخلايا B2:B14 وترجع لنا رقم الصف الذي تتطابق فيه قيمة دالة البحث مع أول خلية بجدول الإكسل ليظهر رقم الصف 10.

ولمعرفة رقم العمود الذي يحتوي على نتيجة الدراسات:-

(B2:B14)”الدراسات”) MATCH=

سوف تبحث الدالة في نطاق الخلايا B2:B14 وترجع لنا رقم العمود الذي تتطابق فيه قيمة دالة البحث مع أول خلية بجدول الإكسل ليظهر رقم العمود 8.

الدالة DGET

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

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

جدول قواعد البحث وهو ما يميز الدالة DGET عن الدوال الأخرى إذ يمكنك من البحث بدلالة أكثر من معيار(البحث عن شخص وعمره وجنسيته) والبحث باستخدام معايير أحرف البدل(؟ و*)، كما يمكنك استخدام معايير المعاملات المنطقية(>، <، أو =).

ويتم كتابة الدالة كالتالي:-

DGET (database, field, criteria)

الوسيط الأول database قاعدة البيانات:- يمثل نطاق الخلايا التي تتكون منها القائمة أو قاعدة البيانات التي تبحث فيها.
الوسيط الثاني field الحقل:- ويشير إلى العمود الذي تستخدمه الدالة المكتوبة ويتم فيه تسمية العمود بين علامتي إقتباس.
ثم الوسيط الثالث criteria المعيار:- ويمثل نطاق الخلايا الذي يحتوي على الشروط التي يتم إدخالها من قبل المستخدم.

اقرأ أيضا:- الدوال الشرطية في الاكسيل.

ملحوظة هامة:- في حالة تواجد أكثر من نتيجة مطابقة للدالة سوف يتم إرجاع الدالة الخطأ NUM#.

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