معادلات الاكسل

في هذا الدرس سنتعرف على كتابة المعادلات بشكل عميق حيث سنتعرف على كل الموضوعات المتعلقة بكتابة المعادلات في برنامج Excel

سنتعرف في هذا الدرس على الموضوعات التالية :

- أساسيات كتابة المعادلات

- استخدام الدوال في المعادلات

- أخطاء كتابة المعادلات

- استخدام العوامل في المعادلات

- ترتيب العمليات الحسابية في تنفيذ المعادلات

- المرجع النسبي و المرجع المطلق

أساسيات كتابة المعادلات

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

نبدأ بأول فرع وهو فرع القاهرة . لكتابة أي معادلة في برنامج Excel لابد من كتابة علامة = يليها العملية الحسابية التي تريدها . هنا العملية الحسابية التي نريدها هي جمع الأرقام الموجودة في الخلية B2 والخلية C2 والخلية D2 كالتالي :

B2+C2+D2=

بعد كتابة المعادلة اضغط على مفتاح Enter لكي يقوم برنامج Excel بتنفيذ المعادلة وعرض النتيجة .

بنفس الطريقة قم بكتابة باقى المعادلات لحساب اجمالي المبيعات لباقي الفروع كالتالي :

B3+C3+D3=

B4+C4+D4=

B5+C5+D5=

B6+C6+D6=

بذلك قمنا بحساب اجمالي مبيعات الخمسة فروع . لكن تخيل لو كان لديك 1000 فرع هل ستقوم بكتابة 1000 معادلة ؟ بالطبع برنامج Excel أذكي من ذلك . اذا تأملت في الخمسة معادلات التي قمنا بكتابتها ستجد أن الاختلاف الوحيد بينهم هو أن رقم الخلية يزيد كل مرة بمقدار 1

B2+C2+D2=

B3+C3+D3=

B4+C4+D4=

B5+C5+D5=

B6+C6+D6=

لذلك يمكنك كتابة أول معادلة فقط وتقول لبرنامج Excel قم بكتابة باقي المعادلات بدلا مني . كيف يتم ذلك ؟

بعد كتابة أول معادلة قف بالماوس على النقطة الموجودة في الزاوية اليمنى في أسفل الخلية وبعد ما يتحول شكل مؤشر الماوس إلى شكل العلامة +

اضغط واسحب بالماوس حتى تصل إلى أخر خلية ثم قم بترك الماوس .

استخدام الدوال في المعادلات

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

اضغط واسحب بالماوس حتى تصل إلى أخر خلية ثم قم بترك الماوس .

ملحوظة : يمكنك التعرف بالتفصيل على دوال برنامج Excel من خلال هذا الدرس : دوال الاكسل

أخطاء كتابة المعادلات

عند كتابة معادلة بصورة خاطئة سترجع المعادلة أحد الأخطاء السبعة التالية :

الخطأ !VALUE#

سبب الحدوث :

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

مثال :

في الشكل التالي المعادلة ترجع الخطأ !VALUE# لأنها لا تستطيع جمع رقم مع نص .

الخطأ !0/DIV#

سبب الحدوث :

يحدث هذا الخطأ عند قسمة رقم على الصفر .

مثال :

في الشكل التالي المعادلة ترجع الخطأ !0/DIV# لأنها لا تستطيع قسمة الرقم 5 على الرقم 0

الخطأ !NUM#

سبب الحدوث :

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

مثال :

في الشكل التالي المعادلة ترجع الخطأ !NUM# لأنه لا يمكن حساب الجزر التربيعي لرقم سالب .

الخطأ !NULL#

سبب الحدوث :

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

مثال :

في الشكل التالي المعادلة ترجع الخطأ !NULL# لأننا قمنا بكتابة المراجع بصورة خاطئة حيث أننا قمنا بكتابة مسافة بدل الفاصلة في معاملات دالة SUM

الخطأ ?NAME#

سبب الحدوث :

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

مثال :

في الشكل التالي المعادلة ترجع الخطأ ?NAME# لأننا قمنا بكتابة اسم دالة MAX بصورة خاطئة .

الخطأ N/A#

سبب الحدوث :

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

مثال :

في الشكل التالي المعادلة ترجع الخطأ N/A# لأن دالة VLOOKUP ستبحث عن القيمة 6 فلن تجدها .

الخطأ !REF#

سبب الحدوث :

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

مثال :

في الشكل التالي قمنا بكتابة معادلة لجمع القيم الموجودة في الخلية C1 والخلية C2 والخلية C3

اذا قمت بحذف الصف رقم 2

سترجع المعادلة الخطأ !REF# لأنه تم حذف مرجع مكتوب داخل المعادلة .

استخدام العوامل في المعادلات

يحتوي برنامج Excel على العديد من العوامل التي يمكنك استخدامها داخل المعادلات مثل :

العوامل الحسابية Arithmetic Operators

عامل الجمع +

عامل الطرح -

عامل الضرب *

عامل القسمة /

عامل الأس ^

عامل النسبة المئوية %

عوامل المقارنة Comparison Operators

عامل يساوي =

عامل لا يساوي <>

عامل أكبر من <

عامل أكبر من أو يساوي =<

عامل أصغر من >

عامل أصغر من أو يساوي =>

عوامل النصوص Text Operators

عامل دمج النصوص &

ترتيب العمليات الحسابية في تنفيذ المعادلات

يقوم برنامج Excel بتنفيذ المعادلة وفقا لهذا الترتيب :

1- عوامل المرجع ( النقطتان المسافة الفاصلة )

2- اشارة السالب ( - )

3- النسبة المئوية ( % )

4- الرفع إلى الأس ( ^ )

5- الضرب و القسمة ( * / )

6- الجمع والطرح ( + - )

7- عامل دمج النصوص ( & )

8- عوامل المقارنة ( = < > < > =< => )

في الشكل التالي سيتم تنفيذ عملية الضرب ثم عملية الجمع .

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

المرجع النسبي و المرجع المطلق

الجدول التالي يحتوي على مبيعات بعض المنتجات الالكترونية ( الموبايل - الكاميرا - التلفزيون - اللابتوب - التابلت ) .

سنقوم بحساب اجمالي مبيعات كل المنتجات من خلال استخدام دالة SUM

الأن نريد حساب النسبة المئوية لمبيعات كل منتج بالنسبة لإجمالي المبيعات . لحساب النسبة المئوية لمبيعات كل منتج سنستخدم المعادلة التالية :

النسبة المئوية للمنتج = مبيعات المنتج / اجمالي المبيعات لكل المنتجات

الأن قم بسحب المعادلة لأسفل لحساب النسبة المئوية لباقي المنتجات .

عند سحب المعادلة لأسفل قامت باقي المعادلات بإرجاع الخطأ !0/DIV# فما السبب في ذلك ؟

عند سحب المعادلة لأسفل سيقوم برنامج Excel بزيادة كل الخلايا المكتوبة داخل المعادلة بمقدار 1 . طبقا لذلك الخلية B2 ستصبح B3 والخلية B7 ستصبح B8 وهي خلية فارغة ستأخذ القيمة الافتراضية 0 لذا سترجع المعادلة خطأ القسمة على الصفر !0/DIV#

لذلك نحن نحتاج في كل المعادلات القسمة على الخلية B7 بمعنى أخر لا نريد زيادتها عند سحب المعادلة لأسفل . يمكن تعريف ذلك لبرنامج Excel من خلال كتابة الخلية B7 في المعادلة بهذا الشكل :

B2/$B$7=

يمكنك كتابة الخلية B7 بهذا الشكل يدويا أو من خلال كتابة B7 ثم الضغط على مفتاح F4 من لوحة المفاتيح .