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

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

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

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

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

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

الجمع

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

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

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

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

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

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

الطرح

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

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

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

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

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

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

دالة الجمع

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

SUM(number;number,…)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

دالة الضرب

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

PRODUCT(number;number,…)

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

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

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

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

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

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

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

أمثلة:

=((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 إذا لم تجد ما تبحث عنه.

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

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

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

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

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

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

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

تفسير الخطأ الذي حدث هنا هو أنه يوجد نوعان من المراجع للخلايا؛ المراجع النسبية 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 عند تحديد قيمة الخلية المراد تثبيتها. لاحظ كيف تتغير الصيغة في الصورة أدناه (علامة الدولار $ تستخدم للمراجع المطلقة):

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

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

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

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

جمع الوقت

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

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

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

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

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

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

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

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

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

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

طرح الوقت

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

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

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

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

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

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

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

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

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

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

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

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

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

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