Excel · मराठी आवृत्ती
6.7 SUBTOTAL आणि AGGREGATE
या page मध्ये
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 दोन्ही दाखवा.