شرح درس مهارات متقدمة في مايكروسوفت إكسل ثالث متوسط الفصل الثاني
يحوّل Excel قوائم البيانات إلى معلومات مفيدة بالفرز والتصفية والتنسيق الشرطي والدوال الشرطية والبحث والجدول المحوري، فتصبح النتائج جاهزة بنقرات قليلة.

أهداف الدرس
- ينسّق نطاق بيانات جدولًا (Format as Table) ويفرزه بأكثر من مفتاح ويصفّيه.
- يطبّق التنسيق الشرطي والتحقق من صحة البيانات بقائمة منسدلة.
- يكتب الدالة IF المتداخلة والدوال COUNTIF وSUMIF وAVERAGEIF ويحسب ناتجها على بيانات حقيقية.
- يستخدم الدالة VLOOKUP للبحث عن قيمة في جدول.
- يصف فكرة الجدول المحوري (PivotTable) وخطوات إنشائه، ويجمّد الأجزاء لتسهيل التصفح.
المفهوم الأساسي
يحوّل Excel قوائم البيانات إلى معلومات مفيدة بالفرز والتصفية والتنسيق الشرطي والدوال الشرطية والبحث والجدول المحوري، فتصبح النتائج جاهزة بنقرات قليلة.
شرح الدرس
يحتفظ معلم الصف الثالث المتوسط بدرجات عشرة طلاب في الرياضيات والعلوم. سنستخدم هذه البيانات في كل أمثلة الدرس، فاعتمد عليها لتتحقق بنفسك من كل ناتج.
بيانات الدرس
تبدأ البيانات من الخلية A1 وتمتد إلى G11، وصف العناوين هو الصف الأول.
- الأعمدة: A رقم الطالب، B الاسم، C الشعبة، D الرياضيات، E العلوم، F المعدل، G التقدير.
- الصف الثاني: الطالب ١٠١ أحمد من الشعبة أ ودرجتاه ٩٥ و٩٠
- الصف الثالث: الطالب ١٠٢ خالد من الشعبة ب ودرجتاه ٨٢ و٧٨
- الصف الرابع: الطالب ١٠٣ سعد من الشعبة أ ودرجتاه ٦٧ و٧٠
- الصف الخامس: الطالب ١٠٤ فهد من الشعبة ب ودرجتاه ٨٨ و٩٢
- الصف السادس: الطالب ١٠٥ سلطان من الشعبة أ ودرجتاه ٤٥ و٥٢
- الصف السابع: الطالب ١٠٦ ماجد من الشعبة ب ودرجتاه ٧٤ و٨١
- الصف الثامن: الطالب ١٠٧ يوسف من الشعبة أ ودرجتاه ٩١ و٨٧
- الصف التاسع: الطالب ١٠٨ عمر من الشعبة ب ودرجتاه ٥٨ و٦٤
- الصف العاشر: الطالب ١٠٩ ناصر من الشعبة أ ودرجتاه ٧٩ و٨٥
- الصف الحادي عشر: الطالب ١١٠ بدر من الشعبة ب ودرجتاه ١٠٠ و٩٦
في العمود F نكتب في الخلية F2 الصيغة =AVERAGE(D2:E2) ونسحبها للأسفل، فيظهر معدل أحمد ٩٢٫٥ ومعدل سلطان ٤٨٫٥
الجداول والفرز والتصفية
- التنسيق كجدول: حدّد البيانات ثم اختر «تنسيق كجدول» (Format as Table) من علامة التبويب «الصفحة الرئيسية» (Home)، فيضيف Excel أسهم التصفية والألوان المتبادلة ويوسّع الجدول تلقائيًا عند إضافة صف.
- الفرز المتعدد: من علامة التبويب «بيانات» (Data) اختر «فرز» (Sort) ثم أضف مستوى ثانيًا. إن رتّبنا حسب الشعبة ثم حسب الرياضيات تنازليًا فتظهر الشعبة أ أولًا وأعلى طالب فيها أحمد ثم يوسف ثم ناصر ثم سعد ثم سلطان.
- التصفية: اضغط سهم العمود، وإن اخترت الشعبة ب مع الرياضيات أكبر من أو تساوي ٨٠ فتظهر ثلاثة طلاب هم خالد وفهد وبدر.
- التصفية المتقدمة (Advanced Filter): خيار في علامة التبويب «بيانات» لتصفية بشروط مكتوبة في نطاق منفصل، ونكتفي بمعرفة فكرته.
«الفرز يرتّب الصفوف والتصفية تخفي ما لا يطابق الشرط»
التنسيق الشرطي والتحقق من صحة البيانات
- التنسيق الشرطي (Conditional Formatting): يغيّر لون الخلية تلقائيًا إذا تحقق شرط. حدّد العمود F ثم اختر «التنسيق الشرطي» من «الصفحة الرئيسية» ثم «قواعد تمييز الخلايا» (Highlight Cells Rules) ثم «أقل من» (Less Than) واكتب ٦٠ فتتلوّن الخلية F6 وحدها لأن معدل سلطان ٤٨٫٥
- التحقق من صحة البيانات (Data Validation): يمنع إدخال قيمة خاطئة. حدّد خلايا الشعبة C2:C11 ثم من علامة التبويب «بيانات» اختر «التحقق من صحة البيانات»، واجعل السماح «قائمة» (List) والمصدر أ ثم فاصلًا ثم ب، فتظهر قائمة منسدلة تقبل أ أو ب فقط.
يعتمد الفاصل بين عناصر القائمة وبين وسائط الدوال على إعدادات اللغة في الجهاز، فقد يكون فاصلة أو فاصلة منقوطة، فإن لم تعمل صيغتك بدّل الفاصل.
الدوال الشرطية
نكتب الصيغ في الخلايا بعد علامة =.
- IF المتداخلة: في G2 نكتب =IF(F2>=90,"ممتاز",IF(F2>=75,"جيد جدًا",IF(F2>=60,"جيد","يحتاج دعمًا"))). معدل أحمد ٩٢٫٥ فالناتج ممتاز، ومعدل سعد ٦٨٫٥ فالناتج جيد، ومعدل سلطان ٤٨٫٥ فالناتج يحتاج دعمًا.
- COUNTIF: تعدّ الخلايا المطابقة. الصيغة =COUNTIF(D2:D11,">=90") ناتجها ٣ لأن ٩٥ و٩١ و١٠٠ فقط بلغت التسعين. والصيغة =COUNTIF(C2:C11,"أ") ناتجها ٥
- SUMIF: تجمع القيم المطابقة. الصيغة =SUMIF(C2:C11,"أ",D2:D11) تجمع رياضيات طلاب الشعبة أ وناتجها ٣٧٧
- AVERAGEIF: تحسب متوسط القيم المطابقة. الصيغة =AVERAGEIF(C2:C11,"ب",D2:D11) ناتجها ٨٠٫٤ وهو متوسط رياضيات الشعبة ب.
الشرط النصي والمقارن يوضع بين علامتي تنصيص مثل ">=90" و"أ".
البحث والجدول المحوري وتجميد الأجزاء
- VLOOKUP: تبحث عن قيمة في العمود الأول من النطاق وتُرجع قيمة من عمود تحدده. الصيغة =VLOOKUP(107,A2:G11,2,FALSE) تبحث عن الطالب ١٠٧ وتُرجع العمود الثاني فالناتج يوسف. والصيغة =VLOOKUP(104,A2:G11,7,FALSE) ناتجها ممتاز لأن معدل فهد ٩٠ — وتعني FALSE مطابقة تامة.
- XLOOKUP: دالة أحدث تؤدي العمل نفسه مثل =XLOOKUP(107,A2:A11,B2:B11) وناتجها يوسف، لكنها موجودة في الإصدارات الحديثة فقط.
- الجدول المحوري (PivotTable): يلخّص البيانات دون كتابة صيغ. حدّد البيانات ثم «إدراج» (Insert) ثم «جدول محوري» (PivotTable)، وضع الشعبة في الصفوف والرياضيات في القيم واختر المتوسط، فتظهر الشعبة أ بمتوسط ٧٥٫٤ والشعبة ب بمتوسط ٨٠٫٤
- تجميد الأجزاء (Freeze Panes): من علامة التبويب «عرض» (View) اختر «تجميد الأجزاء» ثم «تجميد الصف العلوي» ليبقى صف العناوين ظاهرًا أثناء التمرير.
أي تغيير في الدرجات يحدّث نتائج الصيغ تلقائيًا، أما الجدول المحوري فيحتاج إلى «تحديث» (Refresh).
نشاط تعليمي
التطبيق الأول
أنشئ البيانات السابقة في ورقة جديدة، واكتب في G2 الصيغة =IF(F2>=90,"ممتاز",IF(F2>=75,"جيد جدًا",IF(F2>=60,"جيد","يحتاج دعمًا"))) ثم اسحبها للأسفل.
يظهر لفهد التقدير ممتاز ولعمر جيد ولبدر ممتاز.
أن يطبّق الطالب IF المتداخلة على بيانات كاملة.
المسألة الثانية
كم طالبًا في الشعبة ب درجته في الرياضيات ٨٠ فأكثر، وما مجموع علوم الشعبة أ؟
العدد ثلاثة بالتصفية، ومجموع علوم الشعبة أ ٣٨٤ بالصيغة =SUMIF(C2:C11,"أ",E2:E11).
أن يوظّف الطالب الدوال الشرطية في سؤال حقيقي.
الموقف الثالث
سأل معلمك: من صاحب الرقم ١١٠؟ وما رياضياته؟
=VLOOKUP(110,A2:G11,2,FALSE) تُرجع بدر، و=VLOOKUP(110,A2:G11,4,FALSE) تُرجع ١٠٠ — الهدف: أن يستخدم الطالب VLOOKUP لاسترجاع بيانات طالب.
ملاحظات مهمة
- 1تبدأ كل صيغة بعلامة = وتُكتب أسماء الدوال بالإنجليزية.
- 2ضع النص والشروط المقارنة بين علامتي تنصيص داخل الصيغة.
- 3استخدم FALSE في VLOOKUP للمطابقة التامة.
- 4يجب أن يكون عمود البحث أول عمود في نطاق VLOOKUP.
- 5جمّد صف العناوين عندما تكبر القائمة.
أخطاء شائعة
- ✗ خطأنسيان علامة = في أول الصيغة.✓ الصواب: أبدأ كل صيغة بـ = وإلا عدّها Excel نصًّا.
- ✗ خطأكتابة الشرط >=90 بدون علامتي تنصيص داخل COUNTIF.✓ الصواب: أكتبه ">=90".
+عرض جميع الأخطاء (4) ←إخفاء الأخطاء الإضافية
- ✗ خطأاختيار نطاق VLOOKUP يبدأ من عمود غير عمود القيمة المطلوب البحث عنها.✓ الصواب: أبدأ النطاق من العمود الذي يحوي القيمة المبحوث عنها.
- ✗ خطأإهمال تحديث الجدول المحوري بعد تعديل الدرجات.✓ الصواب: أضغط «تحديث» (Refresh).
ملخص الدرس
- الجدول والفرز المتعدد والتصفية ينظّمون القوائم الطويلة.
- التنسيق الشرطي يلوّن الخلايا حسب شرط، والتحقق من صحة البيانات يقيّد المدخلات بقائمة منسدلة.
- IF المتداخلة تختار من بين عدة حالات، وCOUNTIF وSUMIF وAVERAGEIF تعدّ وتجمع وتتوسط بشرط.
- VLOOKUP تبحث في عمود وتُرجع قيمة من عمود آخر، والجدول المحوري يلخّص دون صيغ.
- تجميد الأجزاء يُبقي العناوين ظاهرة.
هل أتقنت الدرس؟اختبر نفسك من خلال اختبار قصير من عدة أسئلة.