14. تمرين - الدوال COUNTIF و COUNTIFS و SUMIF و SUMIFS
المتطلبات الأساسية
ينبغي قراءة دوال أكسل 2016 إلى غاية الدرس الدوال COUNTIF و COUNTIFS و SUMIF و SUMIFS لإنجاز هذا التمرين.
السؤال
لنعتبر المقتطف من جدول البيانات التالي الذي يمثل قائمة للمشتركين في جمعية ما:
السؤال 1
في الورقة الثانية من نفس الملف ، يتم حساب عدد المشتركين حسب المدينة من جهة وحسب المدينة و السنة من ناحية أخرى:
اكتب الصيغة في الخلية B3 لحساب عدد الأعضاء لكل مدينة من القائمة في الورقة الأولى. يجب ملء النطاق B4:B9 بتقنية التعبئة التلقائية من الصيغة في الخلية B3.
اكتب الصيغة في الخلية C3 لحساب عدد الأعضاء حسب المدينة والسنة من القائمة في الورقة الأولى. يجب تعبئة النطاق C3:F9 بتقنية التعبئة التلقائية من الصيغة في الخلية C3.
السؤال 2
في الورقة الثالثة من نفس الملف ، يتم حساب مجموع التبرعات حسب المدينة من جهة وحسب المدينة و القطاع من ناحية أخرى:
اكتب الصيغة في الخلية B3 لحساب مجموع التبرعات حسب المدينة من القائمة في الورقة الأولى. يجب ملء النطاق B4:B9 بتقنية التعبئة التلقائية من الصيغة في الخلية B3.
اكتب الصيغة في الخلية C3 لحساب مجموع التبرعات حسب المدينة والقطاع من القائمة في الورقة الأولى. يجب تعبئة النطاق C3:H9 بتقنية التعبئة التلقائية من الصيغة في الخلية C3.
يمكنك تنزيل الملف لهذا التمرين هنا.
إشارات لحل التمرين - الدوال COUNTIF و COUNTIFS و SUMIF و SUMIFS
السؤال 1
الحل:
الصيغة في الخلية B3 من الورقة 2 ، ليتم نسخها في النطاق B4:B9
=COUNTIF(Sheet1!H$2:H$1475, A3)
الصيغة في الخلية C3 من الورقة 2 ، ليتم نسخها في النطاق C3:F9
=COUNTIFS(Sheet1!$H$2:$H$1475, $A3, Sheet1!$I$2:$I$1475, C$2)
الصيغة في I2 من الورقة 1 ، ليتم نسخها في النطاق I2:I1475
=YEAR(F2)
الشروح:
فيما يتعلق بالصيغة الموجودة في B3 من الورقة 2 ، فإن الدالة COUNTIF تحسب عدد تواجد القيمة في الخلية A3 وهي "الرياض" في النطاق H2:H1475 من الورقة الأولى Sheet1. مرجع الخلية المطلق ضروري بالنسبة لرقم الصف واختياري بالنسبة لرقم العمود لأنه سيتم إجراء النسخ عموديًا.
فيما يتعلق بالصيغة في الخلية C3 من الورقة 2 ، لدينا معياران هما المدينة والسنة. لذلك يجب استعمال الدالة COUNTIFS.
من ناحية أخرى ، نظرًا لأن الورقة الأولى لا تحتوي على عمود لسنة انخراط المشتركين ، فقد أضفنا هذه المعلومة في العمود I. استعملنا لذلك الدالة YEAR التي تُرجع السنة للتاريخ المعطى كوسيطة ، وهو تاريخ انخراط الأعضاء المسجل في العمود F.
السؤال 2
الحل:
الصيغة في الخلية B3 من الورقة 3 ، للنسخ في النطاق B4:B9
=SUMIF(Sheet1!H$2:H$1475, A3, Sheet1!G$2:G$1475)
الصيغة في الخلية C3 من الورقة 3 ، لنسخها في النطاق C3:H9
=SUMIFS(Sheet1!$G$2:$G$1475, Sheet1!$H$2:$H$1475, $A3, Sheet1!$E$2:$E$1475, C$2)
الشروح:
فيما يتعلق بالصيغة في الخلية B3 من الورقة 3 ، تحسب الدالة SUMIF مجموع القيم في العمود G التي تقابلها في العمود H القيمة "الرياض" أي قيمة الخلية A3 من الورقة 3. مرجع الخلية المطلق ضروري لرقم الصف واختياري لرقم العمود لأن النسخ سيكون عموديا.
فيما يتعلق بالصيغة في الخلية C3 من الورقة 3 ، لدينا معياران هما المدينة والسنة. لذلك يجب استعمال الدالة SUMIFS.
يمكنك تنزيل الملف المملوء بهذه الصيغ هنا.
تعليقات القارئ
