اذهب إلى المحتوى

الصيغ والدوال في Microsoft Excel


Huda Almashta

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

functions-excel.thumb.png.f5297e85d90f81

الصيغ في اكسل هي عبارة عن مجموعة من المعاملات، الدوال والأرقام. يجب أن تبدأ أي صيغة بعلامة يساوي (=)، وسيتوضح المفهوم أكثر عن طريق الأمثلة في الفقرات التالية.

الرياضيات الأساسية في اكسل

تقوم معظم العمليات الحسابية المعقدة على أساس الرياضيات البسيطة؛ الجمع، الطرح، الضرب، والقسمة. لنتعلم كيفية القيام بهذه العمليات باستخدام المعاملات (+، -، *، /) أو الدوال (Sum، product، إلخ).

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

566c4572eea62_1-__.thumb.png.68f0f506fdb

الجمع

لكي تقوم بجمع رقمين أو عدد من الأرقام، حدّد الخلية التي تريد ظهور الناتج فيها ثم اكتب صيغة الجمع يدويا:

=العدد الثاني + العدد الأول

566c4573cc5a0_2-_.thumb.png.e74342c4e36a

ثم اضغط Enter من لوحة المفاتيح.

لكن هذه الطريقة ستصبح مطولة ومملة إذا كان عدد الأرقام التي تريد جمعها أكثر من 3. لذلك بدلا من كتابة الأرقام قم بتحديد الخلايا. أي اكتب علامة (=) ثم حدد الخلية الأولى بواسطة الفأرة، ثم علامة (+) ثم حدد الخلية الثانية، وهكذا.

566c457496069_3-___.thumb.png.70d2644fd0

لاحظ أن البرنامج يقوم بتمييز الخلايا مع اسم الخلية في الصيغة بنفس اللون.

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

الطرح

لا تختلف عملية الطرح عن عملية الجمع سوى باستخدام المعامل (-) بدلا من (+). يمكنك طرح مجموعة من الخلايا المتجاورة أو غير المتجاورة بنفس طريقة عملية الجمع.

566c45758035a_4-_.thumb.png.e95353fd43a8

الضرب والقسمة

عمليتا الضرب والقسمة لا تختلفان عن الجمع والطرح. استخدم علامة النجمة (*) (وليس x) لعملية الضرب، وعلامة (/) لعملية القسمة. طريقة كتابة الصيغة هي كما في عمليتي الجمع والطرح.

566c45777831c_5-_.thumb.png.523393e05e96

566c457e488a3_6-_.thumb.png.ae7c1ed8ad90

تذكر أن تكتب علامة = قبل بداية أي صيغة.

استخدام الدوال Functions

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

دالة الجمع

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

SUM(number;number,…)

ويمثل "number" رقم محدد أو اسم خلية.

حدد الخلية التي تريد ظهور الناتج فيها، ويجب أن تكون بعد نطاق الخلايا المتجاورة. مثلا إذا أردت جمع الأرقام في العمود B يجب أن أقوم بتحديد خلية أسفل نطاق الخلايا ضمن العمود B. وإذا أردت جمع الأرقام في الصف 3، يجب أن أقوم بتحديد خلية على يمين نطاق الخلايا في الصف 3 (أو على يسار نطاق الخلايا إذا كان اتجاه الورقة من اليسار إلى اليمين) وهكذا.

سأقوم بجمع الأرقام في العمود B، بتحديد الخلية B8، الذهاب إلى تبويب الصفحة الرئيسية، ثم النقر على زر الجمع التلقائي AutoSum. أو حدد الخلية واضغط على اختصار الجمع التلقائي Alt+ =.

566c457f2ba36_7-_.thumb.png.00f492261ed2

ستتحول حدود نطاق الخلايا إلى إطار متقطع متحرك، وستظهر صيغة الجمع (Sum(B3:B7= في الخلية التي سيظهر فيها الناتج. اضغط Enter لإتمام العملية.

في المثال تمثل النقطتين (:) نطاق من الخلايا. وهذه هي الصيغة العامة لجمع أرقام في نطاق من الخلايا المتجاورة.

سأقوم أيضا بجمع نطاق الخلايا في الصف 4 وبنفس الطريقة:

566c45802276f_8-_2.thumb.png.a1d6477f721

لاحظ أنني حصلت على النتيجة "8" من عملية الجمع، وليس "18"، والسبب أنّ أمر الجمع التلقائي يُطبق على الخلايا المتجاورة فقط.

يمكنك استخدام دالة الجمع (وليس زر الجمع التلقائي) إذا كانت الخلايا غير متجاورة أيضا.

حدد أي خلية تريد ظهور النتيجة فيها، واكتب SUM=.

اضغط على المفتاح Ctrl وقم بتحديد العدد الذي تريده من الخلايا (أو نطاق الخلايا) مع الاستمرار بالضغط، ثم أغلق القوس.

566c4581b75ef_9-___.thumb.png.066e330709

لاحظ أيضا أنني قمت بتحديد الخليتين B3، B5 ونطاق الخلايا C5:C7. اضغط Enter للحصول على النتيجة.

أو بطريقة أخرى، استخدم دالة الجمع واكتب أسماء الخلايا التي تريد جمع محتوياتها يدويًا، افصل بين اسم خلية وآخر بفارزة منقوطة (;).

ملاحظة: لمشاهدة محتوى الخلية (أي الصيغة) انقر بشكل مزدوج على الخلية، أو قم بتحديد الخلية وشاهد الصيغة من شريط الصيغة.

لا تمتلك عملية الطرح دالة معرفة كما في الجمع، والطريقة الوحيدة لتنفيذ عملية الطرح هي باستخدام المعامل (-).

يمكنك أن تطرح ناتج دالة جمع من ناتج دالة جمع آخر:

566c4582d5396_10-__.thumb.png.a2503f8bea

دالة الضرب

دالة الضرب تشابه دالة الجمع من حيث طريقة العمل، فيما عدا أنها تقوم بضرب الأرقام بدلا من جمعها. وصيغة دالة الضرب هي:

PRODUCT(number;number,…)

ويمثل "number" رقم محدد أو اسم خلية.

يمكنك استخدام دالة الضرب لإيجاد ناتج الضرب لمجموعة خلايا متجاورة أو غير متجاورة:

566c458bb62b7_11-_.thumb.png.43e37164683

أما عملية القسمة فحالها حال عملية الطرح لا تمتلك دالة معرفة، استخدم المعامل (/) لتنفيذ عملية القسمة.

يمكنك قسمة ناتج دالة ضرب على ناتج دالة جمع مثلا:

566c458d36ff0_12-.thumb.png.581761f2ca18

تتابع العمليات الحسابية

يستخدم اكسل التتابع المعروف لتنفيذ العمليات الحسابية، أي:

  • الأقواس أولا، من الداخل إلى الخارج.
  • الأسس.
  • الضرب والقسمة من اليسار إلى اليمين.
  • وأخيرا الجمع والطرح من اليسار إلى اليمين.

أمثلة:

=((25/5)+(2*3))
=5+6
=11
=45/9*5 (تنفذ العمليات من اليسار إلى اليمين، أي القسمة أولا ثم الضرب)
=5*5
=25
=5-4+3 (تنفذ العمليات من اليسار إلى اليمين، أي الطرح أولا ثم الجمع)
=1+3
=4
=2+(3*(1+5)^2)-6/3
=2+(3*36)-2
=2+108-2
=108

في هذا المثال:

  • يتم إيجاد نتيجة القوس الداخلي (1+5) =6
  • ثم يرفع الناتج للأس (2) = 36 لأن الأسس تتبع الأقواس في تتابع العمليات
  • ثم تضرب النتيجة بالعدد (3) = 108
  • ثم يتم إيجاد ناتج عملية القسمة (6/3) = 2
  • بعدها تصبح الصيغة كالتالي: 2-108+2=
  • والنتيجة النهائية = 108.

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

صيغ متقدمة

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

للوصول إلى مجموعة الدوال اذهب إلى تبويب صيغ Formulas. انقر على زر المزيد من الدوال More Functions إذا لم تجد ما تبحث عنه.

566c458eb232a_13-_.thumb.png.0cf9075219e

لنأخذ المثال التالي لتوضيح المزيد من القواعد المتعلقة بالصيغ والدوال:

566c459036c06_14-.thumb.png.c3a4038ae04f

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

سأقوم أولا بتحويل نسبة الفصل إلى نسبة مئوية بتحديد نطاق الخلايا، ثم من تبويب الصفحة الرئيسية Home، ومن خانة أرقام Numbers انقر على زر %.

566c4591e7143_15-_.thumb.png.e8b2e3eb3c7

في الخلية H11 سأقوم بإيجاد النتيجة النهائية للطالب 1، بكتابة الصيغة كما في الصورة أدناه:

566c459ae6352_16-__.thumb.png.4f5d6da64e

أي سيتم ضرب الدرجة الأولى x 20%، والدرجة الثانية x 20%، والدرجة الثالثة x 60%، ثم تجمع نتائج الضرب الثلاث.

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

566c459ba68b0_17-_.thumb.png.cd81bbbe5bc

لكن لاحظ النتائج التي حصلت عليها:

566c459d42364_18-_.thumb.png.587e97f1ca8

تفسير الخطأ الذي حدث هنا هو أنه يوجد نوعان من المراجع للخلايا؛ المراجع النسبية relative references والمراجع المطلقة absolute references. ويستخدم اكسل المراجع النسبية في الصيغة بشكل افتراضي. عندما اخترت الخلية H11 لإظهار النتيجة كانت الصيغة كالتالي:

=(G8*H8)+(G9*H9)+(G10*H10)

وعندما استخدمت التعبئة لإظهار النتيجة في الخلية التالية إلى اليسار (I11) انتقل اكسل أيضا بالصيغة خطوة إلى اليسار، وأصبحت الصيغة في الخلية I11 كالتالي:

=(H8*I8)+(H9*I9)+(H10*I10)

أي انتقل من G8 إلى H8، ومن H8 إلى I8، وهكذا. أي أن النسب في الخلايا G8، G9، وG10 تم تجاوزها ولم يتم استخدامها. بمعنى أنه تم استخدام الخلايا G8، G9، وG10 كمرجع نسبي.

لذلك يجب التبديل من المراجع النسبية إلى المراجع المطلقة (أي تثبيت الخلايا G8، G9، وG10 في الصيغة واستخدامها كمرجع مطلق) باستخدام المفتاح F4 عند تحديد قيمة الخلية المراد تثبيتها. لاحظ كيف تتغير الصيغة في الصورة أدناه (علامة الدولار $ تستخدم للمراجع المطلقة):

566c459eb8969_19-_.thumb.png.c45e13aed06

وعندما أقوم بالتعبئة التلقائية هذه المرة ستظهر النتائج الصحيحة:

566c459f7f5c9_20-_.thumb.png.6191667d4f3

جمع الوقت أو طرحه

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

يُعرض الوقت في اكسل بعدة تنسيقات كالساعات والدقائق، الساعات والدقائق والثواني، الوقت صباحا (AM)/مساء (PM)، إلخ. يمكنك الوصول إلى هذه التنسيقات عن طريق النقر بزر الفأرة الأيمن على الخلية التي تحتوي على بيانات بصيغة الوقت ثم اختيار تنسيق الخلايا Format Cells.

566c45a6d73c2_21-_.thumb.png.f3618108c54

جمع الوقت

قد تحتاج إلى جمع الوقت إذا كان لديك مشروع مقسم إلى مهام وتريد معرفة الزمن الكلي الذي يستغرقه إنجاز المشروع، كما في المثال التالي:

566c45a7d6be8_22-.thumb.png.d10f869ef773

سأقوم بتحديد الخلية C6 لإظهار نتيجة الجمع فيها ثم استخدم دالة الجمع التلقائي (أو كتابة الصيغة يدويا):

566c45a912515_23-.thumb.png.27885b65193a

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

566c45a9d6245_24-_.thumb.png.e9f34943f6b

سأكون بذلك قد انتهيت من جمع الوقت لولا نتيجة المشروع 3 غير المنطقية على الرغم من كون الصيغة صحيحة، فمن المستحيل أن يكون النتائج 3:55 عند جمع 20:30 ساعة مع 7:25. وتفسير ذلك هو أن نتيجة الجمع أكبر من 24 ساعة، والخلية غير منسقة لتعرض الوقت أكبر من 24 ساعة.

لحل المشكلة، سنقوم بتنسيق الخلية لتعرض الوقت أكبر من 24 ساعة:

  • انقر على الخلية بزر الفأرة الأيمن واختر تنسيق الخلايا Format Cells
  • من قسم مخصص Custom وفي خانة النوع Type اكتب h]:mm]

566c45aaf277a_25-__24.thumb.png.15a8f112

تستخدم الأقواس المربعة لعرض الوقت أكبر من 24 ساعة، ويمثل حرف h الساعات، وmm الدقائق. بعد كتابة الصيغة انقر موافق OK وستظهر النتيجة الصحيحة.

566c45b25f3e9_26-__.thumb.png.87a5eb14a6

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

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

566c45b39056a_27-_.thumb.png.60d297f349c

يمثل الرقم 3 عدد الساعات، الرقم 15 عدد الدقائق، والرقم 0 عدد الثواني.

طرح الوقت

يمكنك طرح الوقت أيضا باستخدام اكسل بنفس طريقة طرح الأرقام العادية، فيما عدا أنه لا يظهر النتائج السالبة. وكذلك يجب أن تستخدم المعامل (-) لعدم وجود دالة معرفة لعملية الطرح.

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

566c45b45f81e_28-__.thumb.png.48a9ada1db

في الخلية التي تريد إظهار النتيجة فيها قم بكتابة الصيغة التالية (طرح وقت البدء من وقت الانتهاء):

566c45b57122f_29-__.thumb.png.6e6b85e8f3

بعدها قم بعمل تعبئة تلقائية لتطبيق الصيغة على بقية الخلايا. لاحظ النتائج التالية:

566c45b718b0f_30-__.thumb.png.11f5357d89

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

النتيجة الثانية خاطئة لأن تنسيق الخلية لا يظهر الوقت أكبر من 24 ساعة، وسنقوم بتصحيح هذا الخطأ بالنقر بزر الفأرة الأيمن على الخلية، اختيار تنسيق الخلايا Format Cells، ثم اختيار التنسيق الذي قمنا بعمله سابقا لإظهار الوقت أكبر من 24 ساعة (h]:mm]).

566c45be56ac2_31-__.thumb.png.7394b83321

النتيجة الثالثة صحيحة.

إذا كنت ترغب بإظهار النتائج بصيغة أرقام اعتيادية وليس بصيغة وقت (أي 8.5 بدلا من 8:30) اتبع الخطوات التالية:

حدد الخلية التي تريد إظهار الناتج فيها واكتب الصيغة التالية:

566c45c020d8d_32-__.thumb.png.5488db021e

قمنا باستخدام الأقواس لنتأكد من أن عملية الطرح تنفذ قبل عملية الضرب. كما قمنا بالضرب بالرقم 24 لأن الوقت مخزون في اكسل كجزء من اليوم. أي 6 ساعات تعرف في اكسل بربع يوم (0.25)، و12 ساعة تعرف بنصف يوم (0.5) يوم. لذلك استخدمنا الرقم 24 للتحويل من الجزء من اليوم إلى ساعات.

بعد تنفيذ الصيغة انقر بزر الفأرة الأيمن على النتيجة، اختر تنسيق الخلايا Format Cells، ثم اذهب إلى قسم رقم Number. حدد عدد المراتب العشرية التي تريد إظهارها بعد الفارزة ثم انقر موافق OK.

566c45c178a6f_33-__.thumb.png.1e9a8dee74

سيتحول الناتج من صيغة وقت إلى أرقام عشرية.

566c45c22d892_34-__.thumb.png.0967fb6cf2

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

566c45c2dcada_35-___.thumb.png.7390c3edd

يمثل الرقم 2 الساعات، الرقم 45 الدقائق، والرقم 0 الثواني.


تفاعل الأعضاء

أفضل التعليقات



اعتذر عن الاطالة  

سأجد صعوبة في شرح الصورة وسيطول الكلام فعذرا 

هي مجموعة عمليات مترتبة على بعض

 1-  (B1 )  قيمة الدخل الصافي مثلا     مبلغ    3500

 2-   (B2)  الدخل السنوي      المبلغ    3500 *12  = 42000   عمل دالة ضرب المبلغ *12

3-    (B3)  الشريحة المعفاه (24000)  المبلغ  42000 -24000 = 18000  تم عمل دالة خصم 24000 من الدخل السنوي

4-   (B4)  الشريحة 2   اخذ  مبلغ  15000 من 18000 وضربه *2.5%   المطلوب هنا اذا كان المبلغ في الشريحة الثانية 18000 مثلا يتم طرح 15000 ويتم ضربهم في نسبة 2.5% و3000 يتم اضافتهم للشريحة الثالثة   (B5)  ويتم ضربهم (3000)في 10% 

5-      (B5)    الشريحة الثالثة   3000* 10% 

6-   (B6 )   المبلغ النهائي  

 

 

601dadaa3bd75_Screenshot2021-02-0522_33_51.thumb.png.da892a0c17a687948319d0e96638233a.png    

 

رابط هذا التعليق
شارك على الشبكات الإجتماعية

بتاريخ 9 ساعات قال Mohamed Elgaml:

حياك الله اخي 

هل حانت لى فرصة لمعرفة المطلوب

مرحباً @Mohamed Elgaml 

لا أدري ما المُشكلة التي تواجهها في فعل المطلوب لأن الأمر بسيط للغاية:

بما أن الشريحة التي تحتوي على القيمة 3500 يتم إدخالها من طرفك فلا يوجد أي تعديل او دوال فيها لنفترض أنك وضعتها في الخانة H4 بمعنى العمود H و الصف الرابع.

بالتالي H5 التي ستعرض بها الدخل السنوي ستكون تساوي H4 * 12 و هي الشريحة الثانية لديك.

أما الشريحة الثالثة ستكون في الخانة H6 مثلاً و ستحتوي على H5 - 24000 بما أن القيمة 24000 قيمة ثابتة.

الشريحة الرابعة ستكون قيمتها ثابتة في هذه الحالة لأن كل من القيمتين 15000 و 2,5% ثابتتين و ستحتوي القيمة 375 في كل الأحوال. ستكون القيمة ضمن الخانة H7

الشريحة الخامسة ستكون قيمتها متغيرة حسب قيمة H6 و سنضعها في الخانة H8 و ستحتوي على:

(H6 - 15000) * 10%

بخصوص الشريحة الأخيرة أنت تقول أنها تحتوي المبلغ النهائي لكن لا أدري ماذا تقصد بذلك لكن إذا فهمت الأمر فهو بسيط يُمكنك القيام بكل سهولة.
هذا ملف يحتوي على كافة الشرائح ما عدا الشريحة الأخيرة لأني لا أعلم ماذا سيكون محتواها: Simple.xlsx

رابط هذا التعليق
شارك على الشبكات الإجتماعية




انضم إلى النقاش

يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.

زائر
أضف تعليق

×   لقد أضفت محتوى بخط أو تنسيق مختلف.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   جرى استعادة المحتوى السابق..   امسح المحرر

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • أضف...