تم بفضل الله وحمده شرح أهم الدوال في برنامج مايكروسوفت إكسل Microsoft Excel ، تم تجميعهم وكتابتهم بشكل مفهوم ومبسط بحيث تحتوي كل دالة على مثال واحد على الأقل ، وكذلك شرح بعض خفايا تلك الحالات والاستخدام الأنسب لها .
فيما يلي شرح أهم الدوال والمعادلات في برنامج الإكسل وهم :
- الدوال الحسابية
- الدالات الشرطية
- دوال البحث عن قيمة معينة
- دالة الاختيار CHOOSE
- دالة بناء قاعدة بيانات – OFFSET
- دوال إستبدال الأخطاء
- دالة حساب الإجمالي – SUBTOTAL
- دوال الحساب اعتمادا على بناء قاعدة بيانات
- دوال البحث والإستبدال
- دالة التوجيه INDIRECT
- دوال حساب التاريخ والوقت
- الدالات المادية
- عمليات تقريب الأرقام العشرية
- الدوال الرياضية
- دالة عملية الضرب – PRODUCT
- الرفع إلى قوة (الأس) – POWER
- باقي القسمة – MOD
- الرقم الصحية من القسمة – QUOTIENT
- الجذر التربيعي – SQRT
- المعامل العددي – FACT
- القاسم المشترك الأكبر – GCD
- المضاعف المشترك الأصغر – LCM
- العدد السالب والموجب – SIGN
- القيمة المطلقة – ABS
- توليد رقم عشوائي – RAND
- توليد رقم عشوائي بين قيمتين – RANDBETWEEN
- اللوغاريتم العادي – Log
- اللوغاريتم الطبيعي – Ln
- الدالة باي – π
- الدوال المثلثية
- الدوال النصية
شرح دالة الجمع – SUM :-
= SUM ( number 1 , [ number2 ] , …. )
number : الخانة التي تحتوي على رقم ، أي يجب علينا أن نضع الخانات المراد جمعها .
شرح دالة المعدل – AVERAGE :-
= AVERAGE ( number1 , [ number2 ] , …. )
number : الخانة التي تحتوي على رقم ، أي يجب علينا وضع الخانات المراد معرفة المتوسط الحسابي لها .
شرح القيمة الأعلى – MAX :-
=MAX ( number1 , [ number2 ] , …. )
number : الخانة التي تحتوي على رقم ، ويجب علينا وضع الخانات المراد معرفة أعلى قيمة بينها .
القيمة الأصغر – MIN :-
= MIN ( number 1 , [ number2 ] , …. )
number : كذلك الأمر هنا نضع بدل كل منها الخانة التي تحتوي علي رقم ، وبالتالي سيظهر لدينا أقل قيمة فيها .
القيمة الكبرى – LARGE :-
= LARGE ( array , k )
array : نطاق الخانات التي نرغب بمعرفة قيمة كبرى فيها .
k : عدد يشير إلى الترتيب الذي تود أن يظهره ؛ كثاني أكبر قيمة ( نكتب 2 ) أو ثالث أكبر قيمة ( نكتب 3 ) وهكذا .
القيمة الصغری – SMALL :-
= SMALL ( array , k )
array : نطاق الخانات التي نرغب بمعرفة قيمة صغرى فيها .
k : عدد يشير إلى الترتيب الذي نود أن يظهره ؛ كثاني أصغر قيمة ( نكتب 2 ) أو ثالث أصغر قيمة ( نكتب 3 ) وهكذا .
دوال العد – COUNT – COUNTA – COUNTBLANK :-
= COUNT ( number1 , [ number2 ] , …. )
= COUNTA ( number1 , [ number2 ] , …. )
= COUNTBLANK ( number1 , [ number2 ] , …. )
number : الخانات التي سيتم عدها .
بالنسبة لدالة Count فهي تقوم بعدالخانات التي تحتوي على أرقام فقط ، بينما CountA تقوم بعد الخانات الحاوية على قيمة رقمية أو نصية ( أي غير فارغة ) ، أما CountBlank فتقوم بعد الخانات الفارغة .
إذا الشرطية – IF :
= IF ( logical_test , [ value_if_true ] , [ value_if_false ] )
logical_test : الاختبار المنطقي ، أي الشرط المراد التحقق منه .
value_if_true : القيمة التي تظهر في حال تحقق الشرط .
value_if_false : القيمة التي ستظهر في حال لم يتحقق الشرط .
ملاحظة : القيمة ممكن أن تكون معادلة ، أو خلية أو رقمأ ، أو نص يوضع بين علامتي اقتباس .
مثال : وضع كلمة Large إذا كانت القيمة أكبر من 20 ، Small إذا كانت أقل من ذلك :
مثال : حساب الراتب الجديد إذا كانت الزيادة 15 بالمئة لمن يعمل بدوام الكامل (Full Time) و 10 بالمئة لمن يعمل لبقية الموظفين :
استخدام أكثر من شرط ( IF المتعددة ) :-
مثال : وضع كلمة Large إذا كانت القيمة أكبر من 30 ، و Medium إذا كانت القيمة أكبر من 20 ، و Small إذا كانت غير ذلك :
بمعني افحص الخانة A1 في حال كانت أكبر من 30 ضع Large ، أما في حال لم يكن كذلك افتح شرط جديد وتحقق من جديد ، إذا كانت الخلية ذاتها أكبر من 20 ضع Medium وإذا لم يتحقق كل ماسبق ضع Small .
وهنا نغلق أقواس بعدد ال IF التي فتحناها .
مثال : حساب الراتب الجديد إذا كانت الزيادة هي 15 بالمئة لمن يعمل بدوام كامل ( Full Time ) و 10 بالمئة لمن يعمل بدوام جزئي ( Short Time ) و 5 بالمئة لبقية الموظفين :
تعدد الشروط مع AND و OR :-
= AND ( logical1 , [ logical2 ] , …. )
= OR ( logical1 , [ logical2 ] , …. )
AND وهي معادلة تحقق جميع الشروط ، أي في حال أردنا أن تتحقق مجموعة من الشروط نستخدم AND وسوف يعطينا القيمة True في حال تحققها جميعها و False في حال لم يتحقق واحد منها على الأقل .
أما OR فهي معادلة تحقق شرط واحد على الأقل من عدة شروط ، بحيث يعطي True في حال تحقق واحد منها على الأقل ، و False في حال لم يتحقق أي شيء .
مثال : معرفة الأشخاص الذين تجاوزت علاماتهم ال 50 في الامتحانات الأولية والنهائية :
بينما لو قمنا بإستخدام الدالة OR سيقوم بإعطائنا من تجاوزت علامته 50 في إمتحان واحد على الأقل :
العد الشرطي – COUNTIF :-
= COUNTIF ( range , criteria )
range : نطاق الخانات المراد عدها في حال تحقق شرط معين .
criteria : المعيار الذي سيقوم بالعدبناء عليه .
فلو كان لدينا أسماء موظفين مع طبيعة عملهم .. وأردنا أن نعرف عدد الموظفين الذين يكون دوامهم كاملاً .. نختار العمود الذي يحتوي على طبيعة العمل ، ثم نضع معيار العد بأن يكون دوام كاملا .
مثال : حساب عدد الأشخاص الذين يعملون بدوام كامل ( Full Time ) :
بإمكاننا بذالك الأمر أن نضع بدل كلمة Full Time ، الخلية التي تحتوي عليه :
ونقصد هنا ، متى وجدت في النطاق B2:B8 خلية مشابهة للخلية B2 قم بعدها .
أما في حال أردنا أن يقوم بعد القيم التي هي أكبر أو أصغر من قيمة معينة ، فيتوجب علينا أن نضع جزء المعيار كاملة ضمن إشارتي اقتباس .
مثال : عد القيم التي هي أكبر من القيمة 20 :
وبعبارة أخرى نستطيع القول أنه متى ما أردنا أن نضع إشارة مقارنة ضمن جزء المعيار Criteria يتوجب علينا أن نضعهاجميعابين إشارتي اقتباس .
والاشارات تشمل : الأكبر > ، الأصغر < ، اليساوي = ، المختلف ( اللا يساوي ) < >
مثال : حساب عدد الموظفين جميعا باستثناء من يعمل عمل حر Free lance :
العد بشروط متعددة – COUNTIFS :-
= COUNTIFS ( criteria_range1 , criteria 1 , [ criteria_range2 ] , [ criteria2 ] , …. )
criteria_range1 : نطاق الخانات الأول المراد عدها بناء على المعيار الأول
criteria1 : المعيار الأول الذي سيقوم بالعدبناء عليه .
criteria_range2 : نطاق الخانات الثاني المراد عدها بناء على المعيار الثاني .
criteria2 : المعيار الثاني الذي سيقوم بالعدبناء عليه .
ماذا لو أردنا أن نضع أكثر من شرطه ، هنا نحتاج COUNTIFS لأنها تضع أكثر من شرط ، كأن نعد الخانات ذات دوام كامل وأن يكون المرتب أعلى من قيمة معينة .
مثال : حساب عدد الموظفين الذين يعملون بدوام كامل Full Time ومرتبهم يزيد عن ال 1300 :
مثال : حساب عدد الموظفين الذي تتراوح رواتبهم بين 500 و 1300 :
الجمع الشرطي – SUMIF :-
= SUMIF ( range , criteria , [ sum_range ] )
وهي من عائلة الدالة SUM إلا أنها هنا لا تجمع إلا في حال تحقق شرط معين .
range : نطاق الشرط وهو نطاق الخانات الذي سنحدد له معيار ، في حال تحققه سيجمع قيمته المقابلة .
criteria : المعيار المراد تحققه حتى يقوم بالجمع .
sum_range : نطاق الجمع ، وهو نطاق الخانات الحاوية على القيم المراد جمعها في حال تحقق المعيار .
مثال : حساب مجموع رواتب الموظفين الذين يعملون بدوام كامل Full Time
مثال : حساب مجموع رواتب الموظفين الذين تتجاوز مرتباتهم الـ 1000 :
يمكننا الإستغناء عن الجزء الثالث من معادلة SUMIF في حال كان النطاق المشروط هو ذاته نطاق الجمع .
الجمع بشروط متعددة – SUMIFS :-
= SUMIFS ( sum_range , criteria_range1 , criteria1 , [ criteria_range2 ] , [ criteria2 ] , ….. )
وهي أن يتم الجمع في حال كان لدينا أكثر من شرط .
sum_range : نطاق الجمع ، وهو الخانات الذي يقوم بجمعها في حال تحققت الشروط
criteria_range1 : نطاق الخانات المشروط الأول الذي سوف نضع له شرط حتى يقوم بعد القيمة المقابلة
criterial : المعيار الأول المراد تحققه في نطاق الخانات الأول .
criteria_range2 : نطاق الخانات المشروط الثاني الذي سوف نضع له شرط ثاني حتى يقوم بعد القيمة المقابلة
criteria2 : المعيار الثاني المراد تحققه في نطاق الخانات الثاني .
مثال : حساب مجموع رواتب الموظفين الذكور الذين يعملون بدوام كامل Full Time :
مثال : حساب كمية اللابتوبات المباعة من نوع HP :
حساب المعدل عند تواجد شرط معين :-
الدالتان AVERAGEIF و AVERAGEIFS تشبهان بهيكليتهما معادلتي SUMIF و SUMIFS إلا أنها هنا تقوم بحساب المعدل وليس الجمع .
دالة حساب المعدل بشرط واحد – AVERAGEIF :-
= AVERAGEIF ( range , criteria , [ average_range ] )
تقوم بحساب المعدل في حال كان لدينا شرط واحد .
range : نطاق الشرط .
criteria : المعيار المراد تحققه .
average_range : نطاق حساب المعدل .
مثال : حساب معدل رواتب الموظفين الذين يعملون بدوام كامل Full Time
دالة حساب المعدل بشروط – AVERAGEIFS :-
= AVERAGEIFS ( average_range , criteria_range1 , criteria1 , ( criteria_range2 ] , [ criteria2 ] , ….. )
تقوم بحساب المعدل في حال كان لدينا أكثر من شرط .
average_range : نطاق حساب المعدل
criteria_range1 : نطاق الخانات المشروط الأول .
criterial : المعيار الأول .
criteria_range2 : نطاق الخانات المشروط الثاني .
criteria2 : المعيار الثاني .
دالة البحث 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 التي تقوم بتحديد الصف أو العمود الذي توجدبه القيمة المبحوث عنها وبالتالي باستعمال الدالتين معاً سينتج لدينا معادلة متميزة !
دالة الاختيار CHOOSE :-
= CHOOSE ( index_num , value1 , [ value2 ] , … )
index_num : القيمة الرقمية المختارة ( تتراوح بين ال 1 وال 254 ) .
value : القيم التي للبيتم الاختيار منها .
تقوم هذه الدالة باختيار قيمة معينة من بين مجموعة من القيم
يجب أن ننوه إلى أن القيم التي نقوم بكتابتها ضمن هذه المعادلة لا يمكننا الاستغناء عنها بنطاق ، فيجب علينا كتابة كل قيمة من القيم ، أو تحديد خلية عن كل قيمة مع التثبيت حتي تعمل هذه الدالة بشكل صحيح .
نستطيع استغلال هذه المعادلة في العديد من التطبيقات المفيدة التي تمكننا من استبدال أي أمر برقم معين ، فعلى سبيل المثال نستطيع كتابة الأشهر إنطلاقا من التاريخ بمساعدة الدالة Month ..
دالة بناء قاعدة بيانات – OFFSET :-
= OFFSET ( reference , rows , cols , [ height ] , [ width ] )
تعتبر هذه الحالة من الدوال المهمة في البرنامج ، تقوم بشكل أساسي بعمل قاعدة بيانات تستخدم في دوال أخرى كالجمع أو للبحث عن قيمة معينة كما سنشاهد في الأمثلة القادمة .
reference : وهي الخلية الأولية أو نطاق من البيانات نريد أن نجعله مركز لبدء إنشاء البيانات .
rows : عدد الصفوف التي نريد أن ينتقل لها البدء بمركز إنشاء القاعدة ، يكون سالبة لأعلى وموجبا لأسفل .
cols : عدد الأعمدة التي نريد أن ينتقل لها البدء بمركز انشاء القاعدة ، يكون سالبة أو موجبأ حسب الجهة .
height : الارتفاع في عدد الصفوف ، في حال لم نكتبه ستكون قيمته 1 .
width : العرض في عدد الأعمدة ، في حال لم نكتبه ستكون قيمته 1 .
على سبيل المثال لو رأينا المثال التالي وأردنا إنشاء قاعدة بيانات الخلايا المحددة ووضعنا النقطة المركزية هي الخلية B3 كيف لنقوم بكتابة الأمر ؟ .. دعونا نشرح ذلك .
خلية البدء ( reference ) ستكون B3 وهي المركز .
بالنسبة لعدد الصفوف ( rows ) التي نريد أن ينتقل لها المركز لبدء إنشاء القاعدة في الخلية B4 أي 1 وتعني أننا نريد أن يتحرك المركز صفا واحدا .
بالنسبة لعدد الأعمدة ( cols ) التي نريد أن يتحرك إليها المركز فهو عمود واحد كذلك أي 1 .
القاعدة نريدها أن تكون 4 صفوف و أعمدة ، بالتالي ( height ) هي 4 و ( width ) هي 3 .
ما هي استخدامات هذه المعادلة ؟
تستخدم هذه المعادلة لجمع عدد من القيم مع معادلة Sum أو الحصول على معدلهم مع Average كذلك تستخدم في عمليات البحث مع Lookup والكثير من الدوال التي تحتاج لقاعدة بيانات .
دعونا نتابع معأ هذه الأمثلة التوضيحية لاستخدام هذه الحالة .
مثال : إظهار إسم الشخص بناء على رقم صفه :
مثال : القيام بجمع القيم حسب الشهر :
مثال : البحث عن الراتب عند إعطاء الإسم :
المثال الأخير له ميزة قوية جدا وهي أننا أدخلنا المعادلتين Offset والمعادلة Counta ضمن المعادلة Vlookup وهي تفيدنا في حال قمنا بزيادة أسماء إضافية للجدول فلا نحتاج لتعديل المعادلة حتى تقوم بالعمل الصحيح .
دالة استبدال الأخطاء – IFERROR :
= IFERROR ( value , value_if_error )
تختص هذه المعادلة باستبدال الأخطاء التي قد تظهر عند تطبيق المعادلات بقيمة أخرى نعينها لتحسين مخرجاتها .
value : القيمة أو المعادلة التي من الممكن أن تحتوي على أخطاء .
value_if_error : القيمة التي يتم إعطاؤها في حال احتوت القيمة السابقة على أخطاء .
على سبيل المثال لوقمنا بمثالنا الأتي بتقسيم القيم في العمود A على القيم في العمود B سينتج بعض الأخطاء لأنه لا يمكننا التقسيم على الرقم 0 !
لنقوم بتحسين القيم المخرجة نستخدم معادلة IFERROR كي لا تظهر مثل هذة الأخطاء .
حتى تصبح هذه المعادلة أكثر عملية بإمكاننا أن نضع ضمنها إحدى المعادلات القوية والتي قد تظهر أخطاء في حال لم تحصل على نتيجة ، كما في المثال التالي :
مثال : البحث عن إسم معين وفي حالة عدم وجوده نضع القيمة (Not Found) :
ما هي أنواع الأخطاء التي يمكن أن تظهر عند تطبيق المعادلات ؟
يوجد في البرنامج العديد من رموز الخطأ التي يمكن أن تظهر عند تطبيق المعادلات ، فيا هل ترى ما هي أنواع الأخطاء وما هي دلاليتها ؟، دعونا نتعرف عليها سوية .
| الخطأ | الدلالة |
|---|---|
| N/a# | تعني أن القيمة ليست متاحة في هذه المعادلة كالبحث عن قيمة وهي ليست موجودة في مجال البحث |
| !VALUE# | تعني أن هناك قيمة غير صحيحة كنص أو رقم كوضع نص بدلا من رقم في بعض المعادلات التي تتطلب أرقاما |
| !REF# | تعني أن المعادلة تحتوي على مرجعية خاطئة كالقيام بتعيين خلية بدلا من جدول البحث في معادلات البحث |
| !DIV/0 | خطأ ينتج عن القيام بالتقسيم على رقم 0 |
| !NUM# | الرقم المدخل بصيغة خاطئة كوضع رقم سالب عند طلب الحصول على جذر تربيعي |
| ?NAME# | إدخال قيم غير مفهومة بالنسبة للمعادلة كالقيام بتعيين عناصر ليست كمثل عناصر المعادلة |
| !NULL# | خطأ ينتج عن وجود مسافة بين مدى الخلايا كعمل مسافة بدلاً من الفاصلة أو النقطتين في معادلة الجمع |
دالة IFNA :
= IFNA ( value )
في كثير من الأحيان لا نحتاج لإخفاء جميع الأخطاء مع دالة IFERROR لأن بعض الأخطاء مفيدة جدا بإخبارنا عن بعض المدخلات الخاطئة ، لكن نريد فقط إخفاء الخطأ N/A# الخاص بعدم وجود القيمة المطلوبة .
value : القيمة أو المعادلة التي من الممكن أن تحتوي أو تظهر الخطأ N/A# .
هنالك أيضا دوال فحص الأخطاء السابقة :
وهي معادلات تقوم بالبحث عن الأخطاء وإعطاء القيمة True في حال كانت القيمة تحوي أي خطأ .. و False في حال لم يكن هنالك خطأ .
| الدالة | المهمة |
|---|---|
| ISERROR ( value )= | فحص الخلية أو المعادلة الحاوية على أي نوع من الأخطاء |
| ISERR ( value )= | فحص الخلية أو المعادلة الحاوية على أي نوع من الأخطاء باستثناء الخطأ N/A# |
| ISNA ( value )= | فحص الخلية الحاوية على الخطأ N/A# |
| ISREF ( value )= | فحص الخلية الحاوية على الخطأ !REF# |
استخدام هذه المعادلات يكون بالشكل الأمثل مع معادلة ثانية ، على سبيل المثال نستخدمها مع معادلة IF للقيام بعملية تقسيم صحيحة ..
فيما يأتي سنتعرف على بعض دوال الفحص الموجودة في الإكسل :
| الدالة | المهمة |
|---|---|
| ISNUMBER ( value )= | فحص الخلية الرقمية |
| ISTEXT ( value )= | فحص الخلية النصية |
| ISNONTEXT ( value )= | فحص الخلية الغير نصية |
| ISBLANK ( value )= | فحص الخلية الفارغة |
| ISODD ( value )= | فحص الخلية الرقمية ذات الرقم الفردي |
| ISEVEN ( value )= | فحص الخلية الرقمية ذات الرقم الزوجي |
| ISFORMULA ( value )= | فحص الخلية الناتجة عن معادلة |
| ISLOGICAL ( value )= | فحص الخلية التي تحتوي على TRUE أو FLASE |
دالة حساب الإجمالي – SUBTOTAL :
= SUBTOTAL ( function_num , ref1 , … )
function_num : رقم المعادلة التي نريد حساب الإجمالي عبرها .
ref1 : البيانات التي نريد حساب الإجمالي لها .
تقوم هذه الدالة بحساب الإجمالي لمجموعة من البيانات ، كما تمكن هذه الحالة حساب الإجمالي عن طريق عدة دالات مثل دالة الجمع SUM والمعدل AVERAGE والقيمة العليا MAX والصغرى MIN وغيرها .
ويجب الانتباه أن كل المعادلات تحتوي على رقمين حيث أن أحدها يشمل جميع الخانات المخفية والظاهرة والأخرى تختص بالظاهرة فقط .
| الدالة | تشمل الخلايا المخفية | لا تشمل الخلايا المخفية |
|---|---|---|
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
كما هو ملاحظ في المثال السابق أننا استخدمنا دالة الجمع لحساب الإجمالي لتلك البيانات ، وكان الصف السادس مخفيا ولم يقم بحسابه لأن رقم المعادلة المستخدمة كان يخص ألا يشمل الخلايا المخفية ولو قمنا الأن باستخدام الرقم الذي يشمل الخلايا المخفية لاختلفت النتيجة .
بقي أن ننوه أن هذه المعادلة مفيدة بشكل كبير في حال قمنا بالفلترة و أردنا الحصول على المجموع الإجمالي للبيانات مع عدم الأخذ بالبيانات خارج الفلترة ، فالمعادلات العادية كالجمع والمعدل وغيرها لا تتغير مع تغير الفلترة ، وسنقوم بإجراء العملية للجميع ، بينما نجد أن معادلة Subtotal قدجاءت بالحل الأمثل لذلك .
ولكن هنالك ملاحظتين يجب معرفتها عن هذه المعادلة
أولها : الخلايا المخفية بخاصية الفلترة لن تقوم بحسابها بالحالتين ، بالتالي أيا من المعادلات التي سنفوم باستخدامها ستأتي بنفس النتيجة بحيث لن تشمل الخلايا المخفية .
ثانيها : احتساب الخلايا المخفية من عدمه يكون فقط للصفوف ، أي لو كان هنالك أعمدة مخفية ، فإن معادلة Subtotal ستقوم باحتسابها بالحالتين .
الدالة DSUM :
هي دالة جمع ولكنها تقوم بالجمع بناء على قاعدة بيانات ومجموعة شروط تبني ضمن قالب معين ، تفيد في عمل برنامج مبسط والحصول على مجاميع بسرعة وفعالية ، والحرف D هو اختصار لكلمة Databace .
= DSUM ( database , field , criteria )
database : جدول البيانات الذي يحتوي على القيم التي تريد إجراء عمليات الجمع عليها .
field : العمود الذي يحتوي على القيم التي تحتاج جمعها بناء على شروط معينة
criteria : نطاق البيانات الذي يحتوي على صف الرؤوس وصف الشروط .
قامت هذه المعادلة بجمع كافة القيم في العمود C وذلك لأن الشرط Criteria في النطاق E3 : G4 فارغة ، بمعنى أنه لا يوجد شروط هنا .
دعونا نضع شروط ، مثلا تريد مجموع المبيعات للموبايلات من نوع Samsung ..
مثال أخر لو أردنا مجموع المبيعات لماركة LG التي تجاوزت سعرها الألف دولار ..
دوال مشابهة DAVERAGE – DMAX – DMIN – DCOUNT – DCOUNTA :
هي دوال مشابهة لآلية عمل دالة DSUM وكل واحدة منها تقوم بعمل خاص بنوعيتها كدالة DAVERAGE التي تقوم بحساب المعدل بناء على قاعدة البيانات والشروط ودالة DMAX التي نقوم بحساب أعلى قيمة بجدول البيانات و DMIN التي تقوم بحساب أقل قيمة بجدول البيانات و DCOUNT التي تعد القيم الرقمية بجدول البيانات و DCOUNTA التي تقوم بعد الخلايا كاملة بجدول البيانات اعتمادا على قاعدة شروط .
دالة البحث عن قيمة – FIND :
= FIND ( find_text , within_text , [ start_num ] )
find_text : القيمة المراد البحث عنها .
within_text : موقع البحث ، أي الخلية المراد البحث فيها .
start_num : اختيارية ، رقم الحرف المراد بدء البحث منه .
البحث في الدالة Find حساسة لحالة الأحرف ، فيجب أخذ الحيطة عند البحث عن قيمة معينة إن كانت بأحرف كبيرة أم صغيرة .
دوال الإستبدال
تقوم هذه الدوال بالبحث عن قيمة واستبدالها بقيمة جديدة ، ولكل منها ميزاتها .
دالة الإستبدال – SUBSTATUTE :
= SUBSTATUTE ( text , old_text , new_text , [ instance_num ] )
text : القيمة التي نقوم بإجراء الاستبدال عليها .
old_text : النص القديم أو جزء منه الذي نريد استبداله .
new_text : النص الجديد الذي تريد وضعه
instance_num : هو اختياري في حال كان النص يحتوي على شيء مكرر وأردنا استبدال جزء مع بقاء الأجزاء البقية فنضع هنا رقم الجزء الذي نريد استبداله ( على سبيل المثال نضع 1 لاستدال الأول وبقاء البقية و 2 الاستبدال الثاني وبقاء البقية وهكذا ) .
مثال : استبدال الشرطات بين الأسماء بالفراغات :
مثال : إستبدال اليوم 1 باليوم 2 فقط من دون تغيير الرقم 1 ضمن السنة :
دالة الإستبدال – REPLACE :
= REPLACE ( old_text , start_num , num_chars , new_text )
تقوم هذه الدالة باستبدال أحرف معينة من موضع معين بأخرى جديدة ، واختلافها عن الدالة السابقة أنها تقوم بالاستبدال بناء على موضع الحرفا من النص .
old_text : النص القديم أو الخلية التي نريد استبدال شيء فيها .
start_num : رقم يدل على ترتيب الحرف الذي يبدأ الاستبدال منه
num_chars : عدد الأحرف التي نريد استبدالها بدءا من الحرف المعين .
new_text : النص الجديد الذي تريد وضعه .
مثال : استبدال ال Com ب net في عناوين البريد الالكتروني :
دالة INDIRECT :
= INDIRECT ( ref_text , [ a 1 ] )
تقوم هذه الدالة بتوجيه الأمر من خلية أخرى ، أو نقوم بإظهار ما بداخل الأسماء من قيم ( إدارة الأسماء Name Manger الموجودة بقائمة Formulas ) .
ref_text : النص المرجعي الذي تريد توجيهه .
a1 : قيمة منطقية تحدد نوع المرجع الذي تم احتواؤه ، غالب الأحيان لا نحتاج لكتابة هذا الجزء .
مثال : القيام بعملية الجمع وليس لدينا أسماء الخلايا .. لدينا فقط أرقام الصفوف :
هنا قمنا بعملية الجمع مع دالة Sum وقمنا بدمج رقم الصف مع اسم العمود مع الميزة & وهذه جدا مفيدة في عمليات الجمع أو استيراد الخلايا من أكثر من ورقة ، وتستخدم الدالة Indirect أكثر شيء في Data Validation عندما نقوم بعمل سلسلة أو List بناء على قيم سلسلة أخرى .. كعمل قائمة منسدلة في خلايا تحتوي الدول العربية مثلا ، وقائمة منسدلة بخلايا أخرى تحتوي مدن تلك الدول التي تم تحديدها بحيث تتغير المدن عند تغيير الدولة ، وهذا لا يتم إلا عن طريق استخدام الدالة Indirect .
دالة حساب التاريخ – DATE :
= DATE ( year , month , day )
يقوم بتحويل مجموعة من الأرقام لتاريخ يمكن الاستعانة به في المعادلات .
year : الرقم الخاص بالسنة .
month : الرقم الخاص بالشهر .
day : الرقم الخاص باليوم .
دالة حساب الوقت – TIME :
= TIME ( hour , minute , second )
يقوم بتحويل مجموعة من الأرقام لوقت يمكن الاستعانة به في المعادلات .
hour : الرقم الخاص بالسنة .
minute : الرقم الخاص بالدقائق .
second : الرقم الخاص بالثواني .
حساب التاريخ والوقت الحاليين :
= TODAY ( )
= NOW ( )
تقوم معادلة TODAY بوضع التاريخ الحالي في الخلية ، بينما تقوم معادلة NOW بوضع التاريخ مع الوقت الحالييين في الخلية ، هاتين الدالتين لا نحتاج لكتابة شيء ضمن قوسيهما لأننا هنا نريد أن نضع التاريخ والوقت ولا شيء أكثر ، أي لا نريد حساب شيء .
يجدر الإشارة أن هاتين المعادلتين ستقوم بالتحديث أوتوماتيكيا عند أي تغيير في أي خلية وتقوم بتغيير التاريخ والوقت الحالي تلقائيا .
ملاحظة : بإمكاننا إظهار فقط الوقت بمعادلة NOW من خلال تغيير تنسيق هذه الخانة التنسيق وقت فقط Time من خلال قائمة الصفحة الرئيسية HOME .
كما أن هاتين المعادلتين استخدامهما واسع جدا في المعادلات كما مر سيمر معنا لاحقا في معادلة Datedif لحساب فرق التاريخ .
حساب السنوات والشهور والأيام :
= DAY ( serial_number )
= MONTH ( serial_number )
= YEAR ( serial_number )
serial_number : الرقم الذي تريد تحويله .
جميع هذه الدوال تعمل بنفس الطريقة وتقوم بإظهار مخرجات إما الأيام أو الشهور أو السنوات ، فلو كان لدينا تاريخ وأردنا أن نأخذ منه فقط السنوات أو الأيام أو الشهور ، أو حتى رقم وأردنا تحويله لسنوات مثلاً فإننا نستخدم إحدى الدالات السابقة الذكر .
حساب الساعات والدقائق والثواني :
= HOUR ( serial_number )
= MINUTE ( serial_number )
= SECOND ( serial_number )
serial_number : الرقم الذي تريد تحويله .
كذلك الأمر هنا فهذه الدوال تقوم بتحويل رقم معين إلى ساعات أو دقائق أو ثواني ، أو بإمكاننا استخلاص مانرغب به من وقتا مكتوب في إحدى الخلايا .
حساب فرق التاريخ – DATEDIF : –
= DATEDIF ( start_date , end_date , interval )
وهي لحساب الفرق بين تاريخين معينين .
start_date : التاريخ الأقدم
end_date : التاريخ الأحدث
interval : وهو نمط الحساب كالتالي : ” Y ” : حساب الفرق بالسنوات ، ” M ” : لحساب الفرق بالأشهر ، ” D ” : لحساب الفرق بالأيام .
ملاحظة : بإمكاننا الاستفادة من خاصية DATEDIF بالحصول على أعمار موظفين أو أطفال أو متى انتهاء عقد أشخاص كما في المثال التالي بحيث إذا أردنا الحصول على سنوات عمل موظفين من تاريخ مباشرتهم العمل حتى وقتنا الحالي .. وذلك بمساعدة الدالة ( ) TODAY :
دالة PMT :
= PMT ( rate , nper , pv , [ fv ] , [ type ] )
rate : معدل الفائدة للقرض .
nper : عدد دفعات تسديد القرض .
pv : المبلغ الإجمالي الذي نريد لسحبه أو سداده .
fv : القيمة المستقبلية بعد زيادة الفوائد التي نريد دفعها أو سدادها ( اختيارية ) .
type : اختيارية وتعني طريقة السداد ، وهي إما 0 وتعني نهاية الفترة الزمنية ، أو 1 وتعني بداية الفترة الزمنية .
قمنا بتقسيم معدل الفائدة على 12 لأننا نريد الدفعات الشهرية .. أي 12 شهرا .
بينما لم نقم بضرب عدد الدفعات ب 12 لأن عدد الدفعات هي ذاتها موزعة على الشهور .
وهكذا تنتج لدينا الدفعات الشهرية .. ولكن القيمة كانت السالبة .. وحتى نتخلص من الإشارة السالبة نستطيع وضع اشارة – قبل المعادلة
مثال : حساب الدفعة الشهرية عند معرفة مدة التسديد السنوية
دالة PV :
= PV ( rate , nper , pmt , [ fv ] , [ type ] )
هذه المعادلة هي اختصار ل Present Value وتقوم على احتساب أصل المبلغ ( المبلغ الحالي ) لو كان لدينا مقدار الدفع الدوري ( الشهري أو السنوي ) مع الفائدة ومدة السداد .
rate : معدل الفائدة للقرض
nper : عدد دفعات تسديد القرض .
pmt : الدفعة التي يتم تسديدها في كل فترة .
fv : القيمة المستقبلية بعد زيادة الفوائد التي نريد دفعها أو سدادها ( اختيارية ) .
type : اختيارية وتعني طريقة السداد ، وهي إما 0 وتعني نهاية الفترة الزمنية ، أو 1 وتعني بداية الفترة الزمنية
دالة FV :
= FV ( rate , nper , pmt , [ pv ] , [ type ] )
أما هذه المعادلة فهي اختصار ل Future Value وتقوم بحساب القيمة المستقبلية للمبلغ ، أي بعد زيادة الفوائد وتسديد الدفعات ضمن الفترات الزمنية .
rate : معدل الفائدة للقرض .
nper : عدد دفعات تسديد القرض .
pmt : الدفعة التي يتم تسديدها في كل فترة .
pv : المبلغ الإجمالي الذي نريد سحبه أو سداده ( اختيارية ) .
type : اختيارية وتعني طريقة السداد ، وهي إما 0 وتعني نهاية الفترة الزمنية ، أو 1 وتعني بداية الفترة الزمنية .
عمليات تقريب الأرقام العشرية :
وهي عمليات تقريب الأرقام العشرية ( الأرقام ما بعد الفاصلة ) .
= ROUND ( number , num_digits )
number : الرقم الحاوي على أرقام عشرية ( إما نحدد خانة أو نضع رقم مباشرة ) .
num_digits : رقم يدل على عدد الأرقام بعد الفاصلة الذي نريد أن نقربه إليه .
| الدالة | العملية |
|---|---|
| ( ROUNDUP ( number , num_digits = | التقريب لرقم أعلى محدد بعدد num_digits |
| ( ROUNDDOWN ( number , num_digits = | التقريب لرقم أدنى محدد بعدد num_digits |
| الدالة | العملية |
|---|---|
| EVEN ( number ) = | التقريب لرقم زوجي أعلى |
| ODD ( number ) = | التقريب لرقم فردي أعلى |
| الدالة | العملية |
|---|---|
| CEILING ( number , significance ) = | التقريب لرقم أعلى من مضاعات العدد |
| FLOOR ( number , significance ) = | التقريب لرقم أدنى من مضاعات العدد |
| الدالة | العملية |
|---|---|
| MROUND ( number , multiple ) = | التقريب لأقرب رقم من مضاعات العدد multiple |
| الدالة | العملية |
|---|---|
| INT ( number ) = | ويعطي الجزء الصحيح من العدد ( أي بدون تقريب ) |
دالة عملية الضرب – PRODUCT :
= PRODUCT ( number1 , number2 , … )
وهي نفس دالة الجمع SUM إلا أنه هنا عملية الضرب بدل الجمع ، أي تقوم بعملية الضرب للخانات المحددة
الرفع إلى قوة (الأس) – POWER :
= POWER ( number , power )
تقوم بإظهار نتيجة عدد number مرفوع لقوة ( أس ) power .
باقي القسمة – MOD :
= MOD ( number , divisor )
تقوم بإعطاء باقي قسمة الرقم ( number ) على المقسوم عليه ( divisor ) .
الرقم الصحية من القسمة – QUOTIENT :
= QUOTIENT ( numerator , denominator )
تقوم بإعطاء ناتج قسمة البسط ( numerator ) على المقام ( denominator ) مع إهمال الباقي .
الجذر التربيعي – SQRT :
= SQRT ( number )
المعامل العددي – FACT :
= FACT ( number )
تقوم بإعطاء المعامل العددي لعدد معين ، والمعامل العددي هوحاصل ضرب الأرقام من رقم واحد حتي العدد المحدد .
مثال : المعامل العددي للرقم 5 هو : 5x 4 x 3 x 2 x 1 ويساوي 120
القاسم المشترك الأكبر – GCD :
= GCD ( number1 , number2 , …. )
تقوم بإعطاء القاسم المشترك الأكبر لمجموعة من الأرقام المحددة في ( number ) .
المضاعف المشترك الأصغر – LCM :
= LCM ( number1 , number2 , …. )
العدد السالب والموجب – SIGN :
= SIGN ( number )
تقوم بإعطاء القيمة (1) في حال كان الرقم الموضوع في ( number ) موجبا ، و (-1) في حال كان سالباً ، و (0) في حال كان صفراً .
القيمة المطلقة – ABS :
= ABS ( number )
تقوم بإعطاء قيمة الرقم بغض النظر عن قيمته السالبة أو الموجبة .
توليد رقم عشوائي – RAND :
= RAND ( )
تقوم بإعطاء رقم عشوائي لا على التعيين
توليد رقم عشوائي بين قيمتين – RANDBETWEEN :
= RANDBETWEEN ( bottom , top )
تقوم بإعطاء رقم عشوائي بين القيمة السفلى المحددة في bottom والقيمة العليا المحددة في top .
| الدالة | العملية |
|---|---|
| Log ( number ) = | اللوغاريتم العادي |
| Ln ( number ) = | اللوغاريتم الطبيعي |
| ( ) PI = | الرقم π |
الدوال المثلثية :
وهي الحسابات الخاصة بالمثلث القائم الزاوية .
| الدالة | العملية |
|---|---|
| SIN ( number ) = | حساب جيب الزاوية |
| COS ( number ) = | حساب التجب |
| TAN ( number ) = | الظل |
| ASIN ( number ) = | حساب متمم الجيب |
| ACOS ( number ) = | متمم التجب |
| ATAN ( number ) = | متمم الظل |
دمج الخانات – & :
لنقوم بدمج الخانات ( النصية والرقمية ) في خانة واحدة ، علينا فقط أن نضع الخانات مع وصلة & في ما بينها .
لتقوم بالدمج مع وجود فراغات . يكفي أن نضع فراغا بين إشارتي اقتباس بين الخانتين المدموجتين .
دمج الخانات باستخدام دالة – CONCATENATE :
= ECONCATENATE ( text1 , text2 , …. )
هي ذاتها & ولكن هنا كمعادلة ، و text هي الخانات النصية أو الرقمية المراد دمجها .
تكبير الأحرف النصية :
| الدالة | العملية |
|---|---|
| PROPER ( text ) = | تكبير الحرف الأول من كل كلمة |
| UPPER ( text ) = | تكبير جميع الحروف |
| LOWER ( text ) = | تصغير جميع الحروف |
مقارنة الخانات النصية – EXACT :
= EXACT ( text 1 , text 2 , text 3 , … )
يقوم بمقارنة الخانات الحاوية على النصوص المحددة في ( text ) وإعطاء القيمة True في حال كانت متطابقة و False في حال لم تكن متساوية .
دوال استخلاص الأحرف :
تمكننا هذه الدوال من استخلاص عدد من الأحرف نقوم بتحديدها أو حتى استخلاص الفراغات بحيث تسهل علينا من عمليات النسخ واللصق وكذلك عمليات المقارنة .
الاستخلاص من اليمين – RIGHT :
= RIGHT ( text , num_chars )
text : النص الذي نريد أن نقتطع منه .
num_chars : عدد الأحرف التي نريد اقتطاعها بدءا من اليمين .
الاستخلاص من اليسار – LEFT :
= LEFT ( text , num_chars )
text : النص الذي تريد أن تقتطع منه .
num_chars : عدد الأحرف التي نريد اقتطاعها بدءا من اليسار .
الاستخلاص من المنتصف – MID :
= MID ( text , start_num , num_chars )
text : النص الذي تريد أن تقتطع منه .
start_num : رقم الحرف الذي تريد أن تبدأ بالاقتطاع منه .
num_chars : عدد الأحرف التي نريد اقتطاعها بدءاً من الحرف المعين سابقا .
التخلص من الفراغات الزائدة – TRIM :
= TRIM ( text )
text : النص الذي تريد أن تتخلص من الفراغات الزائدة بداخله .
قد تكون هذه المعادلة هي الأكثر استخداما من بقية معادلات الاقتطاع ، فهي تقوم بحل الكثير من المشاكل وخصوصا عندما نقوم بالنسخ من برنامج آخر ، فتظهر بعض الفراغات الإضافية التي يجب التخلص منها ، بحيث تحذف الفراغات الإضافية بداية النص أو نهايته وتبقي على فراغ واحد بين الكلمات .
