دليلك التطبيقي الشامل لاستخدام دوال اكسل وإجراء العمليات الحسابية

تعد دوال اكسل Excel Functions من أكثر الميزات استخداما وشعبية في برنامج إكسل للجداول الممتدة. إذا كنت تستخدم برنامج إكسل بشكل متكرر، فإن استخدام دوال اكسل يساعدك على تحسين كفاءة عملك وتوفير الوقت.

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

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

يتم إدراج دوال اكسل لتطبيقها على خلايا ورقة العمل إما بإدخالها مباشرة في شريط الصيغة Formula Bar أو باستخدام معالج الدوال Function Wizard. لا يفوتك في هذا السياق التعرف على أكثر اختصارات اكسل استخدما لنظام الويندوز للتسريع من الانتاجية ووتيرة العمل .

ما هي دوال برنامج اكسل؟

دوال اكسل Excel Functions هي عبارة عن أوامر برمجية مدمجة في برنامج مايكروسوفت اكسل Microsoft Excel تستخدم للقيام بمهام معينة. يمكن استخدام دوال و معادلات الإكسل لإجراء لأي عملية حسابية أو تحليلية يتم القيام بها في جداول البيانات في برنامج اكسل.

يمكن تصنيف دوال اكسل إلى عدة أنواع، بما في ذلك:

الدوال الحسابية (Mathematical Functions)

وتتضمن العديد من الدوال الرياضية المعروفة مثل SUM و AVERAGE و ROUND وغيرها، والتي يمكن استخدامها للقيام بعمليات حسابية بسيطة أو معقدة في برنامج اكسل excel.

دوال النصوص (Text Functions)

وتتضمن العديد من الدوال المستخدمة لتنسيق النصوص وتحويلها إلى الصيغ الصحيحة، ويشمل ذلك CONCATENATE و LEFT و RIGHT و SUBSTITUTE وغيرها.

دوال التاريخ والوقت (Date and Time Functions)

وتستخدم هذه الدوال للتعامل مع التواريخ والأوقات وتنسيقها وتحويلها، ومن الأمثلة DATE و TIME و NOW وغيرها.

دوال البحث والاستخراج (Lookup and Reference Functions)

وتستخدم هذه الدوال للبحث عن القيم في الجداول والمصفوفات وإرجاع قيم أخرى ذات صلة، ومن الأمثلة VLOOKUP و HLOOKUP و INDEX و MATCH وغيرها.

دوال الإحصاء (Statistical Functions)

وتستخدم هذه الدوال للقيام بتحليلات إحصائية على البيانات، مثل COUNT و AVERAGE و MAX و MIN وغيرها.

كم عدد دوال الاكسل؟

يوجد العديد من الدوال المتاحة في برنامج اكسل excel، وتختلف الأعداد قليلا بين إصدارات البرنامج المختلفة. في الإصدار الأحدث من برنامج اكسل (Office 365) ، يوجد أكثر من 500 دالة مختلفة.

ومن دالات الإكسيل نجد الدوال المدمجة الأساسية مثل SUM و AVERAGE و COUNT و IF و VLOOKUP وغيرها. وتشمل دوال اكسل الأخرى دوال متخصصة لمجالات معينة مثل الرياضيات والإحصاء والتمويل والعلوم والهندسة وغيرها.

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

ما هي أشهر الدوال الحسابية في برنامج اكسيل؟

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

1. دالة SUM: تستخدم لإيجاد إجمالي مجموعة من القيم في خلايا معينة.
2. دالة AVERAGE: تستخدم لحساب المتوسط الحسابي لمجموعة من القيم.
3. دالة MAX: تستخدم لإرجاع القيمة الأكبر في مجموعة من القيم.
4. دالة MIN: تستخدم لإرجاع القيمة الأصغر في مجموعة من القيم.
5. دالة ROUND: تستخدم لتقريب القيم إلى عدد محدد من الأرقام العشرية.
6. دالة COUNT: تستخدم لحساب عدد الخلايا التي تحتوي على قيم في مجموعة معينة.
7. دالة COUNTIF: تستخدم لحساب عدد الخلايا التي تفي بشرط معين.
8. دالة SUMIF: تستخدم لإجمالي مجموعة من القيم التي تفي بشرط معين.
9. دالة PRODUCT: تستخدم لضرب مجموعة من القيم.
10. دالة SQRT: تستخدم لحساب الجذر التربيعي للقيمة.

دالة الجمع  SUM

تستخدم لإيجاد مجموع عدد من القيم لنطاق أو لعدة نطاقات من الخلايا .

صورة الدالة SUM(number1,number2, ...)

حيث Number1 تعني الرقم الأول ويمكن أن تضمن الدالة حتى 30 وسيطا .

خطوات تطبيق دالة الجمع SUM

1. انتقل للخلية التى إدراج الصيغة بها ثم اختر أمر دالة Function من قائمة إدراج  Insert ثم حدد دالة SUM من فئة دوال رياضيات ومثلثات فى صندوق حوار إدراج دالة .
2. انقر زر موافق Ok ليظهر صندوق حوار وسائط الدالة على الشاشة .
3. قم كتابة الأرقام أو مراجع النطاقات المطلوب جمعها فى خانات صندوق الحوار أو قم بتحديدها من على ورقة العمل .
4. انقر زر موافق Ok ليتم إدراج المجموع فى الخلية المحددة على ورقة العمل في اكسل excel.

دالة الجمع المشروط SUM IF

تستخدم هذه الدالة لجمع الخلايا التى تتفق مع معيار أو شرط معين .

صورة الدالة SUMIF (range , criteria , sum_range)

حيث أن range هو النطاق الذى سيتم البحث فيه عن المعيار أو الشرط ، criteria هو المعيار أو الشرط الذى سيتم الجمع على أساسه ، sum_range هو النطاق الذى يتضمن الخلايا الفعلية التى سيتم جمعها .

دالة القيمة المطلقة ABS

تستخدم لإيجاد القيمة المطلقة لرقم (بدون الإشارة السالبة) .

صورة الدالة ABS (Number)

دالة التقريب ROUND

 تستخدم لتقريب الأرقام العشرية إلى عدد خانات رقمية معين .

صورة الدالة Round ( number , num_digits )

حيث Number هو الرقم المطلوب تقريبه ، num_digits هى عدد خانات التقريب .

شرح إدراج دوال اكسل باستخدام شريط الصيغة

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

ويتم ذلك كالتالي:

1- انتقل للخلية التي تريد إدراج الصيغة بها على ورقة العمل ثم اكتب علامة التساوى = من لوحة المفاتيح.

2- قم بكتابة عناوين الخلايا التي تريد تطبيق الصيغة عليه من خلال لوحة المفاتيح أو قم بنقرها بزر الفأرة ليتم كتابة عنوانها تلقائيا.

 3- قم بإضافة معاملات عمليات الجمع (+) والطرح (-) والضرب (*) والقسمة (/) من على لوحة المفاتيح، ويمكن فصل بعض أجزاء الصيغة باستخدام الأقواس.

4- انقر مفتاح الإدخال Enter أو علامة صح الموجودة على شريط الصيغة ليتم إدراج ناتج الصيغة في الخلية المحددة على ورقة العمل.

تحرير دوال اكسل excel

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

 

يتم تغيير الخلايا بمسح عناوينها وكتابتها داخل شريط الصيغة كما يمكن تزويد نطاقات الخلايا بسحب مقبض تعبئة كل خلية مستهدفة من على ورقة العمل.

يتم قبول الصيغة بنقر مفتاح الإدخال Enter من على لوحة المفاتيح أو بنقر علامة صح الموجودة على شريط الصيغة.

تصحيح دوال اكسل excel

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

يمكن الاستدلال على نوع الخطأ الذي حدث من تعبير الخطأ الذي يظهر في الخلية بدلا من النتيجة، فيظهر التعبير #NAME? في الخلية عند كتابة الصيغة أو الدالة بشكل خطأ أو عند عدم كتابة أحد المعاملات، ويظهر التعبير #DIV/0 في الخلية عند القسمة على صفر أو عند تضمين خلية فارغة في الصيغة، ويظهر التعبير #VALUE عند حدوث خطأ في الصيغة مثل إدخال مرجع خلية تتضمن نصوص في عملية حسابية.  

يتم تصحيح الخطأ بتحرير الصيغة من جديد.

تدقيق دوال اكسل

يستخدم اكسل العديد من الأدوات لتدقيق الصيغ واكتشاف أخطائها ومعالجتها تتوفر هذه الأدوات على شريط أدوات تدقيق الصيغة.

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

يستخدم زر تدقيق في الخطأ لتتبع الخلايا التي تحتوي على أخطاء في ورقة العمل ويحدد نوع الخطأ وسببه ويمكن من معالجته. 

ما هو المرجع النسبى؟

المرجع النسبي relative reference في الاكسل هو المرجع الذي لا يحتوي على علامة الدولار $ في إحداثيات الصفوف والأعمدة ، ويستند مرجع الخلية النسبى (مثل A1) إلى الموضع النسبي الذي يتضمن الصيغة والخلية التي يشير إليها المرجع، فإذا تغير موضع الخلية التي تتضمن الصيغة يتغير المرجع وعند نسخ الصيغ عبر الصفوف أو الأعمدة يتم تعديل المرجع تلقائيا.

تستخدم كل صيغة جديدة يتم إنشاؤها مراجع نسبية.

المرجع المطلق

يشير مرجع الخلية المطلق Absolute reference (مثل A$1) في صيغة ما بصفة دائمة إلى خلية ما في موقع محدد وإذا تم تغيير موضع الخلية التي تتضمن الصيغة يظل المرجع المطلق كما هو وعند نسخ الصيغة عبر الصفوف أو الأعمدة لا يتم تعديل المرجع المطلق.

يمكن تبديل المراجع النسبية إلى مراجع مطلقة بوضع العلامة $ قبل رقم الخلية فيصبح مرجع الخلية النسي A1 مطلقا عند كتابته بهذا الشكل A$1.

في المثال التالي سنرى كيف يمكن تحويل المرجع النسبي لمرجع مطلق.

مثال

قم بإدخال البيانات التالية على ورقة عمل جديدة.

قم بتحديد الخلايا من F3 حتى F6 ثم انقر أداة نمط علامة النسبة المئوية على شريط أدوات التنسيق ليتم تطبيق تنسيق نمط أرقام النسبة المئوية على القيم التي ستظهر في هذه الخلايا.

ضع المؤشر في الخلية F3 ثم اكتب الصيغ (=E3/E6) ليتم حساب النسبة المئوية للأجور من الإجمالي العام.

اضغط مفتاح الإدخال من لوحة المفاتيح ليتم إظهار ناتج الصيغة في الخلية.

قم بنسخ الصيغة على الخلية F4 والخلية F5، سيظهر تعبير خطأ القسمة على صفر في هذه الخلايا.

يمكن التعرف على الخطأ باستخدام شريط أدوات تدقيق الصيغة، قم بإظهار شريط أدوات الصيغة ثم ضع المؤشر في الخلية F3 وانقر زر تتبع السابقات على شريط أدوات الصيغة وافعل نفس الشيء مع الخلية F4 والخلية F5 لتظهر أسهم تدل على الخلايا المشتركة في الصيغة.

 

فمع الخلية F3 تشير الأسهم للخلية E3 والخلية E6 وكل منهما يحتوي على قيم، أما الخلية F4 والخلية F5 فتشير الأسهم للخلايا E4 و E7 والخلايا E5 و E8 والخلايا E7 و E8 لا تحتوى على بيانات، حدث ذلك لأنه تم استخدام الخلية E6 في الصيغة الأولى كمرجع نسبى فتم تحريكه عند نسخ الصيغة على خلايا أخرى.

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

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

يمكن الاستدلال على ذلك باستخدام شريط أدوات تدقيق الصيغة.

 

كيفية إدراج دوال اكسل

يستخدم أمر إدراج دالة لإدراج وتطبيق الدوال المختلفة على ورقة عمل اكسل وتتم هذه العملية على خطوتين: 

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

 

يمكن اختيار الدالة بتحديد فئتها من قائمة تحديد فئة ثم اختيارها من قائمة تحديد دالة، كما يمكن اختيار الدالة من فئة قائمة الدالات الأخيرة التي تحتفظ بالدوال التي تم تشغيلها.

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

الخطوة الثانية يتم فيها إدخال وسائط الدالة (قيمها المختلفة) والخلايا والنطاقات المطلوب تضمينها فيها داخل صندوق حوار وسائط الدالة الذي تتغير خاناته تبعا للدالة المختارة.

يتم تسجيل الوسائط إما بكتابة قيمها في الخانات أو بتحديدها من على ورقة العمل .

 

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

بعد ذلك ينقر زر موافق Ok ليتم إظهار ناتج الدالة في الخلية المحددة على ورقة العمل.

سنتناول فيما يلي بالشرح بعض الدوال من أشهر الفئات الدوال الموجودة مع البرنامج.

الدوال المالية في Excel

تشتمل الدوال المالية على مجموعة الدوال المتعلقة بالأعمال المالية والبنكية وسنتناول منها ثلاث دوال هى دالة القيمة الحالية للمدخرات ودالة القيمة المستقبلية للمدخرات ودالة القسط الدوري.

دالة القيمة الحالية للمدخرات (PV)

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

صورة الدالة 

PV (rate , nper , pmt , fv  , type )

حيث أن: Rate هى معدل الفائدة لكل فترة زمنية، على سبيل المثال إذا حصلت على قرض بمعدل فائدة سنوية 10% وكنت ستقوم بسداد الأقساط شهريا، يكون معدل الفائدة لكل شهر هو 10%/12 ويمكن إدخاله 0.83%.

Nper هى عدد مرات السداد في السنة ، فعلى سبيل المثال إذا حصلت على قرض لمدة أربع سنوات وكنت تقوم بسداد الأقساط شهريا ، فتكون قيمة Nper هي 4*12 أو 48.

Pmt هى الأقساط (دفعات السداد) التي يتم تستطيع سدادها كل فترة زمنية ولا يمكن تغييرها خلال مدة الأقساط وهى تحتوى على رأس المال مضافا إليه الفائدة بدون أية مصاريف أخرى كالضرائب.

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

Type تشير إلى متى تستحق دفعات السداد وتكون قيمتها بصفر إذا استحقت في نهاية الفترة الزمنية ، أو 1 إذا استحقت في بداية الفترة الزمنية.

تطبيق الدالة 

سنقوم بتطبيق الدالة من خلال المثال التالي:

ما هى قيمة قرض يتم سداده على أقساط شهرية لمدة عشر سنوات وقيمة القسط الشهرى هى 500 جنيه وكان معدل الفائدة السنوية 8%؟

1- قم بإدخال بيانات الدالة على ورقة العمل ثم ضع المؤشر في الخلية التي تريد إخراج ناتج الدالة بها كما بالشكل التالي.

2- اختر أمر دالة Function من تبويب إدراج Insert ليظهر صندوق حوار إدراج دالة Insert Function على الشاشة.

3- اختر فئة مالية من قائمة تحديد فئة ثم اختر الدالة PV من خانة تحديد دالة.

 4- انقر زر موافق Ok ليظهر صندوق حوار وسائط الدالة على الشاشة.

 5- ادخل قيمة معدل الفائدة وعدد الدفعات وقيمة القسط في خانات صندوق الحوار أو قم بتحديدها على ورقة العمل.

6- انقر زر موافق Ok لتظهر قيمة القرض في الخلية المحددة على ورقة العمل.

ومن الدوال المالية الآخرى :

• دالة القيمة المستقبلية للمدخرات  FV : و تستخدم لحساب القيمة المستقبلية لاستثمار يستند إلى دفعات سداد ثابتة ومعدل فائدة ثابت. 
• دالة القسط الدورى PMT : وتستخدم لحساب أقساط سداد قرض يستند إلى دفعات سداد ثابتة ومعدل فائدة ثابت .

اهم معادلات Excel للمحاسبين

تعد الدوال المحاسبية من الدوال الأساسية في برنامج إكسل والتي تستخدم بشكل كبير في الحسابات المحاسبية والمالية والتي لا يستغنى عنها المحاسب في أعماله . ومن أهم الدوال المحاسبية في الاكسيل :

1. IF: هي دالة شرطية تستخدم لتحليل البيانات واتخاذ القرارات بناءً على شروط محددة.
2. SUMIF: تستخدم لجمع الأرقام في نطاق معين بناءً على شرط محدد.
3. COUNTIF: تستخدم لحساب عدد الخلايا التي تلبي شرطًا محددًا في نطاق معين.
4. AVERAGEIF: تستخدم لحساب المتوسط ​​الحسابي لمجموعة من الأرقام بناءً على شرط محدد.
5. VLOOKUP: تستخدم للبحث عن قيمة في جدول محدد وإرجاع قيمة مقابلها في الجدول.
6. HLOOKUP: تستخدم للبحث عن قيمة في صف محدد وإرجاع قيمة مقابلها في الصف.
7. SUMIF و SUMIFS: تستخدم لجمع القيم التي تفي بشرط أو عدة شروط.
8. AVERAGE و AVERAGEIF و AVERAGEIFS: تستخدم لحساب المتوسط للقيم في نطاق محدد أو للقيم التي تفي بشرط أو عدة شروط.
9. MAX و MIN: تستخدم للحصول على القيمة الأقصى أو الأدنى في نطاق محدد.
10. COUNT و COUNTIF و COUNTIFS: تستخدم لحساب عدد الخلايا التي تحتوي على قيم في نطاق محدد أو الخلايا التي تفي بشرط أو عدة شروط.
11. ROUND و ROUNDUP و ROUNDDOWN: تستخدم لتقريب الرقم إلى عدد معين من الأرقام العشرية أو إلى أقرب عدد صحيح.
12. VLOOKUP و HLOOKUP: تستخدم للبحث عن قيمة محددة في جدول وإرجاع قيمة أخرى ذات صلة.
13. CONCAT و TEXTJOIN: تستخدم لدمج نصوص من مجموعة خلايا.
14. INDEX و MATCH: تستخدم للبحث عن قيمة محددة في جدول وإرجاع قيمة من الصف أو العمود ذو الصلة.
15. DATE و TIME: تستخدم لإنشاء تواريخ وأوقات.
16. NETWORKDAYS و WORKDAY: تستخدم لحساب عدد الأيام العملية بين تواريخ معينة.

وهذه الدوال المحاسبية تساعد على تسهيل عملية إعداد التقارير المالية والمحاسبية و تحليل البيانات المالية بشكل أكثر دقة وكفاءة. 

 الخاتمة

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