# 6.7 SUBTOTAL आणि AGGREGATE

Source: https://ravindrabagale.com/mr/excel/ch06-tables-sorting-and-filtering/6-7-subtotal-and-aggregate.html
Language: mr (Marathi with English technical terms)

Filter लावूनही SUM पूर्ण rangeचा total देतो. Visible rowsचा total हवा असेल तर SUBTOTAL वापरा.

=SUBTOTAL(function_num,ref1,…)

Function numbers

 | function_num (includes manually hidden rows)
 | function_num (ignores manually hidden rows)
 | Function

 | 1
 | 101
 | AVERAGE

 | 2
 | 102
 | COUNT

 | 3
 | 103
 | COUNTA

 | 4
 | 104
 | MAX

 | 5
 | 105
 | MIN

 | 9
 | 109
 | SUM

Filtered-out rows दोन्ही seriesमध्ये वगळल्या जातात. 1–11 manually hidden rows धरतात; 101–111 त्या वगळतात. हे row-based dataसाठी समजा; columns hide केल्याने त्याच प्रकारे values वगळल्या जात नाहीत.

Errors हाताळण्यासाठी AGGREGATE

=AGGREGATE(function_num,options,ref1,…)

AGGREGATEमध्ये 19 functions आहेत; 14 म्हणजे LARGE, 15 म्हणजे SMALL. Optionsपैकी:

 | Option | काय वगळतं?

 | 5 | Hidden rows.

 | 6 | Error values.

 | 7 | Hidden rows आणि error values.

वापरून पाहा

 | Formula | Result

 | =SUBTOTAL(109,tblOrders[Amount]) | Visible Amountची बेरीज.

 | =SUBTOTAL(103,tblOrders[Order ID]) | Visible nonblank IDsचा count; distinct count नाही.

 | =AGGREGATE(9,6,D2:D100) | Errors वगळून Sum; manually hidden rows वगळायच्या असतील तर option 7 तपासा.

 | =AGGREGATE(14,6,tblOrders[Amount],2) | Errors वगळून दुसरी सर्वात मोठी Amount; distinct second-largest असेलच असं नाही.

Error वगळला म्हणजे missing amount recover झाली असं नाही. Error count वेगळा दाखवा. AGGREGATEमध्ये calculated arrays दिल्यास hidden-row handling बदलू शकतं; या examplesमध्ये थेट column references वापरले आहेत.

Filterसोबत बदलणारं heading

="Showing "&SUBTOTAL(103,tblOrders[Order ID])&" rows worth ₹"&TEXT(SUBTOTAL(109,tblOrders[Amount]),"#,##0")

इथे मुद्दाम rows म्हटलं आहे. प्रत्येक row एक unique order असेल, तेव्हाच orders म्हणणं योग्य.

Practice

Tableच्या वर summary box करा: visible row count, Amount total, average Delivery Mins. Averageसाठी SUBTOTALचा 101 code वापरा. एका copyमध्ये #N/A घालून AGGREGATE total आणि error count दोन्ही दाखवा.

रवींद्र बागले यांची tip

Filter-aware total आणि पूर्ण datasetचा total वेगळे label करा. Data missing असताना error लपवून report पूर्ण असल्याचा आभास देऊ नका.