دالة SUBTOTAL في الاكسل: إجماليات تتخطى الفرعية والمصفّاة

تجمع =SUBTOTAL(9,C2:C8) الأرقام في C2:C8، مثل SUM، مع فرقين: تتخطى أي صيغة SUBTOTAL أخرى داخل النطاق، وتتخطى الصفوف التي أخفتها التصفية. والوسيط الأول، 9، يحدد العملية الحسابية المطلوبة.

الصيغإعادة الضبط

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

يغطي الإجمالي الكلي في C8 العمود كله، بما فيه صفوف الإجماليات الفرعية، ومع ذلك يعرض 445: تستبعد SUBTOTAL الخليتين C4 وC7 لأنهما تحتويان على صيغ SUBTOTAL. وتفعل F2 الشيء نفسه بالدالة SUM فتعرض 890، أي كل عملية بيع محتسبة مرتين. ومع وجود SUBTOTAL في كل صف إجمالي، يمكنك إضافة مجموعات أو نقلها دون إعادة كتابة الإجمالي الكلي.

أرقام الدوال في SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], ...)

عندما تكتب =SUBTOTAL(، يعرض الاكسل هذه القائمة، لذا لا تحتاج إلى حفظها. والأكثر استخدامًا هي 9 و109 (SUM)، و1 (AVERAGE)، و103 (عدّ الصفوف الظاهرة).

الصيغإعادة الضبط

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

لا شيء مخفي هنا، لذا يساوي كل سطر الدالة العادية: متوسط 88.33، و6 صفوف، وMAX قيمته 200، وMIN قيمته 30، وSUM قيمته 530. ولا يظهر الفرق إلا عندما تكون هناك صفوف مخفية، وهذا موضوع القسم التالي.

SUBTOTAL 9 مقابل 109، والصفوف المصفّاة

شغّل التصفية من بيانات > تصفية (Ctrl+Shift+L، أو Cmd+Shift+F على Mac)، ثم اختر North من القائمة المنسدلة للعمود Region. تُخفى صفوف المناطق الأخرى:

• تظل =SUM(C2:C7) تجمع الصفوف الستة كلها.
• تجمع =SUBTOTAL(9,C2:C7) و=SUBTOTAL(109,C2:C7) صفوف North الظاهرة فقط.
• تعدّ =SUBTOTAL(103,A2:A7) الصفوف المتبقية على الشاشة: 2، وهو العدد نفسه الذي يظهر في شريط الحالة ("2 من 6 سجلات").

لا تختلف المجموعتان إلا في الصفوف التي تخفيها يدويًا (حدد الصفوف، ثم زر الفأرة الأيمن > إخفاء). يظل 9 يجمع تلك الصفوف؛ ولا يجمعها 109. إذا كان يجب أن يطابق الإجمالي دائمًا ما يظهر على الشاشة، فاستخدم 109. وإذا كنت تخفي الصفوف لترتيب العرض فقط وما زلت تريدها في الإجمالي، فاستخدم 9.

تعمل SUBTOTAL على الصفوف فقط. أما الأعمدة المخفية فتُشمل دائمًا، لذا تجمع =SUBTOTAL(109,B2:G2) على امتداد صف الأعمدة المخفية أيضًا.

أسرع طريقة للحصول على SUBTOTAL هي زر "جمع تلقائي" مع تشغيل التصفية: يكتب الاكسل =SUBTOTAL(9,...) بدلًا من SUM. وتذهب بيانات > إجمالي فرعي أبعد من ذلك: على قائمة مرتبة حسب عمود، تُدرج صف إجمالي تحت كل مجموعة وإجماليًا كليًا، كلها بالدالة SUBTOTAL، إضافة إلى أزرار المخطط التفصيلي لطي المجموعات.

AGGREGATE: دالة SUBTOTAL التي تستطيع تخطي الأخطاء

إذا احتوت خلية واحدة في النطاق على خطأ، تعيد SUM وSUBTOTAL ذلك الخطأ. والدالة AGGREGATE (في Excel 2010 وما بعده) هي SUBTOTAL مع وسيط خيارات إضافي؛ والخيار 6 يتجاهل قيم الأخطاء.

الصيغإعادة الضبط

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تحتوي C3 على #N/A، لذا تعرض F2 الخطأ #N/A أيضًا. وتتجاهله F3 وتجمع القيم الخمس الأخرى: 450. ويستخدم وسيطها الأول أرقام SUBTOTAL نفسها (9 تعني SUM، و4 تعني MAX). وخيارات أخرى: 5 يتجاهل الصفوف المخفية، و7 يتجاهل الصفوف المخفية والأخطاء، و3 يتجاهل الصفوف المخفية والأخطاء وصيغ SUBTOTAL وAGGREGATE المتداخلة. استبدل C3 برقم فتعرض F2 الإجمالي نفسه الذي في F3.

تمرين: إجمالي كلي فوق الإجماليات الفرعية

الصيغإعادة الضبط

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دورك: في القائمة إجمالي فرعي تحت كل منطقة. ضع في C9 إجماليًا كليًا يغطي C2:C8 دون احتساب صفوف الإجماليات الفرعية مرتين.

تحققعرض الإجابة

لماذا يظل إجمالي SUBTOTAL خاطئًا

• إجماليات المجموعات تستخدم SUM. تتخطى SUBTOTAL صيغ SUBTOTAL الأخرى داخل نطاقها، لا صيغ SUM. وإجمالي المجموعة المكتوب على شكل =SUM(C2:C3) يُحتسب مرة أخرى. غيّر كل صف إجمالي إلى SUBTOTAL.
• الصفوف أُخفيت يدويًا ورقم الدالة 9. استخدم 109.
• البيانات في أعمدة لا في صفوف. لا تُتخطى الأعمدة المخفية أبدًا.
• تحتاج إلى شرط لا إلى تصفية. تتبع SUBTOTAL ما تخفيه التصفية. ولجمع North دون تصفية، استخدم SUMIF. ولملخص كل المجموعات دفعة واحدة، يفعل ذلك الجدول المحوري دون صفوف إجمالي داخل البيانات.