تم بفضل الله وحمده شرح أهم الدوال في برنامج مايكروسوفت إكسل Microsoft Excel ، تم تجميعهم وكتابتهم بشكل مفهوم ومبسط بحيث تحتوي كل دالة على مثال واحد على الأقل ، وكذلك شرح بعض خفايا تلك الحالات والاستخدام الأنسب لها .
في المقال الأول تم شرح الدوال الحسابية وهم :-
دالة الجمع – SUM ، دالة المعدل – AVERAGE ، القيمة الأعلى – MAX ، القيمة الأصغر – MIN ، القيمة الكبرى – LARGE ، القيمة الصغری – SMALL ، دوال العد – COUNT – COUNTA COUNTBLANK .
في المقال الثاني تم شرح الدالات الشرطية وهم :-
إذا الشرطية IF ، استخدام أكثر من شرط ( IF المتعددة ) ، العد الشرطي – COUNTIF ، العد بشروط متعددة – COUNTIFS ، الجمع الشرطي – SUMIF الجمع بشروط متعددة – SUMIFS ، دالة حساب المعدل بشرط واحد – AVERAGEIF ، دالة حساب المعدل بشروط – AVERAGEIFS ، تعدد الشروط مع AND و OR .
فيم يلي شرح دوال من أهم الدالات الأساسية في برنامج الإكسل “دوال البحث عن قيمة معينة” :-
توجد ببرنامج الإكسل دوال كثيرة تقوم بالبحث عن قيمة معينة .. لنبدأ باستعراض بعض منها ونذكر إيجابياتها ومدى دقتها .
دالة البحث LOOKUP ، دالة البحث العمودي VLOOKUP ، دالة البحث الأفقي HLOOKUP ، البحث باستخدام مزيج الدالتين MATCH – INDEX
دالة البحث LOOKUP :-
= LOOKUP ( lookup_value , lookup_vector , [ result_vector ] )
lookup_value : القيمة المراد البحث عنها .
lookup_vector : نطاق البحث ، وهو نطاق الخانات الذي يقوم بالبحث عن القيمة ضمنها .
result_vector : نطاق النتيجة ، وهو نطاق الخانات التي ستظهر النتيجة منها في حال وجدت القيمة المبحوثة عنها في النطاق الأول أو تجاوزتها .
ملاحظة 1 : في حال قمنا بالبحث عن قيمة رقمية معينة ضمن جدول ، فإن النتيجة سوف يضعها في حال تجاوزت هذه القيمة وليس فقط أن يساويها .
فعلى سبيل المثال لوبحثنا عن القيمة 65 وكان لدينا في الجدول 60 و 70 .. فإنه سوف يضع القيمة المقابلة ال 60 لأنه تجاوزها ولم يصل القيمة الأعلى منها .
ملاحظة 2 : عند البحث عن قيمة رياضية معينة في نطاق ، فإننا يجب أن نأخذ بالحسبان أن يكون هذا النطاق مرتب ترتيب تصاعدیاً حتى تعمل الدالة بشكل صحيح .
إيجابيات معادلة Lookup أنها تقوم بتحديد عمود البحث عن القيمة وعمود النتيجة ، وبالتالي لا نحتاج لتحديد جدول بكامله .. وكذلك تأتينا بنتيجة تقريبية للنتيجة المبحوث عنها .
ولكن من سلبياتها أنه يجب ان تكون القيم في عمود النتيجة مرتبة بشكل تصاعدي ، وكذلك بعض الأحيان نحتاج الدقة بالنتيجة ولا نريد نتيجة تقريبية ، وبالتالي فإن هذه المعادلة لا تفي بالغرض .
دالة البحث العمودي VLOOKUP :-
= VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )
lookup_value : القيمة المراد البحث عنها .
table_array : نطاق الجدول الذي يحتوي على عمود البحث وعمود النتيجة .
col_index_num : رقم عمود النتيجة ، وهو ترتيب العمود الذي يحتوي على النتيجة بالنسبة للجدول المحدد .
range_lookup : وهنا علينا وضع كلمة True في حال أردنا الحصول على قيمة تقريبية ، أو وضع كلمة False في حال أردنا نتيجة مطابقة تماماً .
ملاحظة : بإمكاننا أن نترك الجزء الأخير [range_lookup] بدون إستكمال ، وبهذه الحالة سيقوم البرنامج بإظهار النتيجة كما لو أننا أخترنا القيمة True .
ايجابيات معادلة VLookup أنها أكثر دقة من سابقتها .. وذلك لأننا نستطيع تحديد مدى دقة البحث عن طريق True أو False .
ولكن من سلبياتها أن العمود الذي يحتوي على القيمة المبحوث عنها يجب أن يكون ترتيبه الأول بالنسبة اللجدول الذي قمنا بتحديده ( أي يجب أن يكون بأقصى اليسار في حال جهة الورقة من اليسار لليمين ) .
دالة البحث الأفقي HLOOKUP :-
= HLOOKUP ( lookup_value , table_array , row_index_num , [ range_lookup ] )
ماذا لو كانت البيانات موضوعة بشكل أفقي ؟ .. هنا لن نستطيع استخدام الدالة Vlookup لعمل البحث ونحتاج لدالة Hlookup للقيام بالمهمة ، وهي مطابقة تماما للدالة السابقة مع اختلاف بسيط .
lookup_value : القيمة المراد البحث عنها .
table_array : نطاق الجدول الذي يحتوي على صف البحث وصف النتيجة
row_index_num : رقم صف النتيجة ، وهو ترتيب الصف الذي يحتوي على النتيجة بالنسبة للجدول المحدد .
range_lookup : وهنا علينا وضع كلمة True في حال أردنا الحصول على قيمة تقريبية ، أو وضع كلمة False في حال أردنا نتيجة مطابقة تماما .
بإمكاننا أن نترك الجزء الأخير [ range_lookup ] بدون استكمال ، وبهذه الحالة سيقوم البرنامج بإظهار النتيجة كما لو أننا اخترنا القيمة True .
كما هو الحال في الدالة Vlookup فهنا يجب أخذ الحذر بأن الصف الذي يحتوي على القيمة المبحوث عنها يجب أن يكون ترتيبه الأول عند القيام بتعيين الجدول حتى تكون النتيجة صحيحة ( أي يجب أن يكون في الأعلى ) .
البحث باستخدام مزيج الدالتين MATCH – INDEX :-
الدالة MATCH :-
تقوم الدالة MATCH بالبحث عن قيمة معينة في عمود أو صف وتقوم بإظهار رقم الصف أو العمود الموجود به هذه القيمة .
= MATCH ( lookup_value , lookup_array , [ match_type ] )
lookup_value : القيمة المراد البحث عنها .
lookup_array : العمود أو الصف الذي يحتوي على القيمة المراد البحث عنها .
match_type : نوعية المطابقة :
0 : أو Exact match أي أن يكون البحث مطابق تماما .
1 : أو Greater Than وتعني إذا لم يجد القيمة يضع أقرب قيمة أكبر من القيمة المبحوث عنها .
1- : أو Less than وتعني إذا لم يجد القيمة يضع أقرب قيمة أصغر من القيمة المبحوث عنها .
للوهلة الأولى قد تبين لنا أن دالة Match غير مجدية بشكل كبير .. فهي لا تظهر لنا إلا رقم هذا الصف أو العمود الذي توجد به هذه النتيجة ، ولكن عند استخدامها ضمن دالة INDEX سنجدها مفيدة جدا .
الدالة INDEX :-
تقوم الدالة Index بإظهار النتيجة بعد تحديد الصف والعمود الذي تتواجد به هذه النتيجة
= INDEX ( array , row_num , column_num )
array : نطاق البيانات الحاوي على القيمة المراد البحث عنها .
row_num : رقم الصف الحاوي على القيمة المبحوث عنها .
column_num : رقم العمود الحاوي على القيمة المبحوث عنها .
كذلك الدالة INDEX نجدها لوهلة غير مجدية .. فكيف لنا أن نحدد الصف الواقع به القيمة المراد البحث عنها ، لو علمنا رقم الصف لما قمنا باستعمال الدالة أصلا .
لذلك كانت الدالة MATCH التي تقوم بتحديد الصف أو العمود الذي توجدبه القيمة المبحوث عنها وبالتالي باستعمال الدالتين معاً سينتج لدينا معادلة متميزة !
