في هذا الدرس سنتعرف على كتابة المعادلات بشكل عميق حيث سنتعرف على كل الموضوعات المتعلقة بكتابة المعادلات في برنامج 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# لأنها لا تستطيع جمع رقم مع نص .
سبب الحدوث :
يحدث هذا الخطأ عند قسمة رقم على الصفر .
مثال :
في الشكل التالي المعادلة ترجع الخطأ !0/DIV# لأنها لا تستطيع قسمة الرقم 5 على الرقم 0
سبب الحدوث :
يحدث هذا الخطأ عند كتابة رقم غير مناسب داخل المعادلة .
مثال :
في الشكل التالي المعادلة ترجع الخطأ !NUM# لأنه لا يمكن حساب الجزر التربيعي لرقم سالب .
سبب الحدوث :
يحدث هذا الخطأ عند كتابة المراجع بصورة خاطئة فى المعادلة .
مثال :
في الشكل التالي المعادلة ترجع الخطأ !NULL# لأننا قمنا بكتابة المراجع بصورة خاطئة حيث أننا قمنا بكتابة مسافة بدل الفاصلة في معاملات دالة SUM
سبب الحدوث :
يحدث هذا الخطأ عند كتابة اسم غير مفهوم فى المعادلة .
مثال :
في الشكل التالي المعادلة ترجع الخطأ ?NAME# لأننا قمنا بكتابة اسم دالة MAX بصورة خاطئة .
سبب الحدوث :
يحدث هذا الخطأ عندما لا تستطيع المعادلة أن ترجع أى قيمة .
مثال :
في الشكل التالي المعادلة ترجع الخطأ N/A# لأن دالة VLOOKUP ستبحث عن القيمة 6 فلن تجدها .
سبب الحدوث :
يحدث هذا الخطأ عندما يتم حذف مرجع مكتوب داخل معادلة .
مثال :
في الشكل التالي قمنا بكتابة معادلة لجمع القيم الموجودة في الخلية C1 والخلية C2 والخلية C3
اذا قمت بحذف الصف رقم 2
سترجع المعادلة الخطأ !REF# لأنه تم حذف مرجع مكتوب داخل المعادلة .
استخدام العوامل في المعادلات
عامل الجمع +
عامل الطرح -
عامل الضرب *
عامل القسمة /
عامل الأس ^
عامل النسبة المئوية %
عامل يساوي =
عامل لا يساوي <>
عامل أكبر من <
عامل أكبر من أو يساوي =<
عامل أصغر من >
عامل أصغر من أو يساوي =>
عامل دمج النصوص &
ترتيب العمليات الحسابية في تنفيذ المعادلات
يقوم برنامج 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 من لوحة المفاتيح .
الأن عند سحب المعادلة لأسفل سيتم زيادة الخلية B2 بمقدار 1 في كل المعادلات , بينما ستظل الخلية B7 ثابتة في كل المعادلات .
في برنامج Excel نطلق على الخلية B2 اسم المرجع النسبي Relative Reference بينما نطلق على الخلية B7 اسم المرجع المطلق Absolute Reference
يمكنك تحويل شكل الأرقام السابقة إلى شكل النسبة المئوية عن طريق الذهاب إلى التبويب Home ثم الضغط على رمز النسبة المئوية .
سيتغير شكل الأرقام إلى شكل النسبة المئوية .
الأن اذا قمت بجمع كل النسب المئوية ستحصل على 100%