ورقاتشرح درس الدوال والمراجع ثاني متوسط الفصل الثاني
الدالة صيغة جاهزة في Excel تنفّذ عملية على نطاق من الخلايا، ونوع المرجع (نسبي أو مطلق أو مختلط) يحدد كيف يتغير عنوان الخلية عند نسخ الصيغة.
أهداف الدرس
- يستخدم الدوال SUM وAVERAGE وMAX وMIN وCOUNT في Excel.
- يكتب دالة IF لتحديد النجاح والرسوب.
- يستخدم COUNTIF لعدّ الخلايا التي تحقق شرطًا.
- يميّز بين المرجع النسبي والمطلق والمختلط.
- يتنبأ بأثر نسخ الصيغة بمقبض التعبئة في المراجع.
المفهوم الأساسي
الدالة صيغة جاهزة في Excel تنفّذ عملية على نطاق من الخلايا، ونوع المرجع (نسبي أو مطلق أو مختلط) يحدد كيف يتغير عنوان الخلية عند نسخ الصيغة.
شرح الدرس
أدخلت المعلمة نورة درجات خمسة طلاب من خمسين درجة في العمود B من B2 إلى B6، وتريد معرفة المجموع والمعدل والأعلى والأدنى وعدد الناجحين. تكتب الدوال بدل الجمع اليدوي الطويل.
الدرجات هي: ٤٥ و٣٨ و٥٠ و٢٢ و٤٢
الدوال الأساسية
الصيغة العامة للدالة اسمها ثم قوسان فيهما النطاق، مثل =SUM(B2:B6). والنطاق B2:B6 يعني كل الخلايا من B2 إلى B6.
- =SUM(B2: B6) يجمع الدرجات، والناتج ١٩٧
- =AVERAGE(B2: B6) يحسب المعدل، والناتج ٣٩٫٤
- =MAX(B2: B6) يعيد أكبر قيمة، والناتج ٥٠
- =MIN(B2: B6) يعيد أصغر قيمة، والناتج ٢٢
- =COUNT(B2: B6) يعدّ الخلايا التي فيها أرقام، والناتج ٥
«نكتب أسماء الدوال بالإنجليزية كما هي، ونفصل بين الوسائط بالفاصلة الإنجليزية»
الدالة IF
تختبر IF شرطًا وتعيد قيمة إن تحقق وقيمة أخرى إن لم يتحقق. صيغتها: الشرط ثم القيمة عند التحقق ثم القيمة عند عدم التحقق.
لنفرض أن درجة النجاح ٢٥ من ٥٠ فنكتب في C2 الصيغة =IF(B2>=25,"ناجح","راسب").
- الطالب الأول درجته ٤٥: الشرط محقق فتظهر «ناجح».
- الطالب الرابع درجته ٢٢: الشرط غير محقق فتظهر «راسب».
نضع النص بين علامتي اقتباس مزدوجتين، أما الأرقام فلا تحتاج إلى علامتي اقتباس.
الدالة COUNTIF
لنعرف عدد الناجحين لا نعدّهم بأعيننا، بل نكتب =COUNTIF(B2:B6,">=25") فيعدّ Excel الخلايا التي تساوي ٢٥ أو أكثر، والناتج ٤ — وعدد الراسبين =COUNTIF(B2:B6,"<25") والناتج ١
المرجع النسبي والمطلق والمختلط
تريد نورة حساب نسبة كل طالب بقسمة درجته على الدرجة العظمى ٥٠ المكتوبة في الخلية E1.
- المرجع النسبي (Relative) مثل B2: يتغير عنوانه عند النسخ، فإذا نسخنا الصيغة للأسفل صار B3 ثم B4.
- المرجع المطلق (Absolute) مثل $E$1: علامتا الدولار تثبّتان العمود والصف، فيبقى كما هو عند النسخ.
- المرجع المختلط (Mixed) مثل E$1 أو $E1: يثبّت الصف فقط أو العمود فقط.
نكتب في D2 الصيغة =B2/$E$1. وعند السحب إلى D3 تصبح =B3/$E$1 فتبقى الدرجة العظمى ثابتة وتتغير درجة الطالب.
أما لو كتبنا =B2/E1 ثم نسخناها إلى D3 فتصبح =B3/E2 وتقسم على خلية فارغة فتظهر #DIV/0!.
اضغط المفتاح F4 بعد تحديد عنوان الخلية في شريط الصيغة لتبديل المرجع بين نسبي ومطلق ومختلط.
النسخ بمقبض التعبئة
مقبض التعبئة (Fill Handle) مربع صغير في الزاوية السفلية للخلية المحددة. نسحبه إلى الأسفل لننسخ الصيغة إلى بقية الصفوف، فيعدّل Excel المراجع النسبية تلقائيًا.
نشاط تعليمي
التطبيق الأول
درجات خمسة طلاب في B2:B6 هي ٤٥ و٣٨ و٥٠ و٢٢ و٤٢ اكتب صيغة أدنى درجة وأعلاها.
=MIN(B2:B6) والناتج ٢٢ و=MAX(B2:B6) والناتج ٥٠ — الهدف: أن يستخدم الطالب الدالتين.
المسألة الثانية
اكتب صيغة تعرض «ممتاز» إذا كانت الدرجة في B2 أكبر من أو تساوي ٤٥ وإلا فـ«جيد».
=IF(B2>=45,"ممتاز","جيد").
أن يكتب الطالب دالة شرطية.
الموقف الثالث
نسخت منى الصيغة =B2/E1 إلى الأسفل فظهرت أخطاء.
المرجع E1 نسبي فتحرك، والحل =B2/$E$1.
أن يصلح الطالب مرجعًا نسبيًا خاطئًا.
ملاحظات مهمة
- 1النطاق يكتب بنقطتين رأسيتين بين أول خلية وآخر خلية.
- 2النصوص داخل الدوال تكتب بين علامتي اقتباس.
- 3العلامة $ تثبّت العمود أو الصف عند النسخ.
- 4نختبر الصيغة على خلية واحدة قبل نسخها.
أخطاء شائعة
- ✗ خطأنسيان القوسين أو النطاق في الدالة.✓ الصواب: نكتب =SUM(B2:B6) كاملة بالقوسين.
- ✗ خطأكتابة النص في IF بلا علامتي اقتباس.✓ الصواب: نضع كلمة «ناجح» بين علامتي اقتباس.
+عرض جميع الأخطاء (3) ←إخفاء الأخطاء الإضافية
- ✗ خطأنسخ صيغة فيها مرجع نسبي إلى ثابت.✓ الصواب: نحوّله إلى مرجع مطلق مثل $E$1.
ملخص الدرس
- SUM للمجموع وAVERAGE للمعدل وMAX وMIN للأكبر والأصغر وCOUNT للعدّ.
- IF تختار نتيجة بحسب شرط.
- COUNTIF تعدّ الخلايا التي تحقق شرطًا.
- المرجع النسبي يتغير عند النسخ، والمطلق $E$1 ثابت، والمختلط يثبّت جزءًا منه.
هل أتقنت الدرس؟اختبر نفسك من خلال اختبار قصير من عدة أسئلة.
