Kapag ang iyong Excel worksheet ay naglalaman ng mga nakatagong row, na-filter na data, o nakapangkat na data, gamitin ang Excel SUBTOTAL function. Ang SUBTOTAL function ay maaaring magsama o magbukod ng mga nakatagong halaga sa mga kalkulasyon. Bilang karagdagan sa paghahanap ng kabuuan ng isang pangkat ng data, maaaring kalkulahin ng Excel ang average, maximum, minimum, standard deviation, at variance ng iyong data. Narito kung paano maglagay ng mga subtotal sa Excel.
Nalalapat ang mga tagubilin sa artikulong ito sa Excel para sa Microsoft 365, Excel 2019, at Excel 2016.
Syntax ng SUBTOTAL Function
Gamitin ang SUBTOTAL function sa Excel upang ibuod ang mga value sa isang worksheet sa iba't ibang paraan. Ito ay lalong nakakatulong kapag ang iyong worksheet ay naglalaman ng mga nakatagong row na gusto mong isama sa pagkalkula.
Ang syntax ng SUBTOTAL function ay: SUBTOTAL(function_num, ref1, ref2, …)
Ang function_num argument ay kinakailangan at tinutukoy ang uri ng mathematical operation na gagamitin para sa subtotal. Ang function na SUBTOTAL ay maaaring magdagdag ng mga numero, kalkulahin ang average na halaga ng mga napiling numero, hanapin ang maximum at minimum na mga halaga sa isang hanay, bilangin ang bilang ng mga halaga sa isang napiling hanay, at higit pa.
Hindi pinapansin ng SUBTOTAL function ang mga cell na walang data at mga cell na may mga hindi numeric na value.
Ang argument na ito ay isang numero at depende sa kung gusto mong isama ang mga nakatagong row sa resulta o ibukod ang mga nakatagong row sa resulta. Ang mga row na ito ay maaaring manu-manong itago o itago ng isang filter.
Ang mga argumento ng function_num ay kinabibilangan ng:
| Function Task | function_num | function_num |
|---|---|---|
| (kasama ang mga nakatagong halaga) | (hindi kasama ang mga nakatagong halaga) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Ang function_num reference arguments 1 hanggang 11 ay nagsasama lamang ng mga value sa mga nakatagong row kapag ginagamit ang Hide command upang itago ang mga row. Kapag ginagamit ang Filter command, hindi kasama sa SUBTOTAL na mga kalkulasyon ang mga nakatagong resulta ng filter.
Ang ref1 argument ay kinakailangan. Ito ang mga cell na ginamit upang kalkulahin ang mga resulta ng napiling function_num argument. Ang argument na ito ay maaaring isang value, isang cell, o isang hanay ng mga cell.
Ang ref2, … na argumento ay opsyonal. Ito ay mga karagdagang cell na kasama sa pagkalkula.
Gamitin ang SUBTOTAL Function na may mga Nakatagong Row
Ang mga function ng Excel ay maaaring ipasok nang manu-mano o sa tulong ng dialog box ng Mga Argumento ng Function. Upang ilarawan kung paano manu-manong ipasok ang function gamit ang formula bar, ang sumusunod na halimbawa ay gumagamit ng COUNT function_num argument upang mabilang ang bilang ng mga value sa mga nakikitang row at sa parehong nakikita at nakatagong mga row.
Upang gamitin ang SUBTOTAL function upang bilangin ang bilang ng mga row sa isang worksheet:
- Magsimula sa isang worksheet na naglalaman ng maraming row ng data.
-
Piliin ang cell na maglalaman ng bilang ng mga nakikitang row.
-
Sa function bar, ilagay ang =SUBTOTAL. Habang nagta-type ka, nagmumungkahi ang Excel ng isang function. I-double click ang SUBTOTAL function.
Para magamit ang dialog box ng Function Arguments para makapasok sa SUBTOTAL function, pumunta sa Formulas at piliin ang Math & Trig >SUBTOTAL.
Image -
Sa lalabas na drop-down na menu, i-double click ang 102 – COUNT function_num argument.
Image -
Mag-type ng kuwit (,).
Image -
Sa worksheet, piliin ang mga cell na isasama sa formula.
Image -
Pindutin ang Enter upang makita ang resulta sa cell na pinili mo sa hakbang 2.
Image - Piliin ang cell na maglalaman ng bilang ng nakikita at nakatagong mga row.
- Sa function bar, ilagay ang =SUBTOTAL. Habang nagta-type ka, nagmumungkahi ang Excel ng isang function. I-double click ang SUBTOTAL function.
- Sa lalabas na drop-down na menu, i-double click ang 2 – COUNT function_num argument, pagkatapos ay mag-type ng kuwit (,).
-
Sa worksheet, piliin ang mga cell na isasama sa formula, pagkatapos ay pindutin ang Enter.
Image -
Itago ang ilang row ng data. Sa halimbawang ito, nakatago ang mga row na may mga benta lang na mababa sa $100,000.
Image
Gamitin ang SUBTOTAL Function na may Na-filter na Data
Paggamit ng SUBTOTAL function sa na-filter na data ay binabalewala ang data sa mga row na inalis ng filter. Sa tuwing nagbabago ang pamantayan ng filter, muling kinakalkula ang function upang ipakita ang subtotal para sa mga nakikitang row.
Upang gamitin ang SUBTOTAL function upang makita ang mga pagkakaiba sa mga resulta ng pagkalkula habang nagfi-filter ng data:
-
Gumawa ng SUBTOTAL na mga formula. Halimbawa, gumawa ng mga formula upang matukoy ang subtotal at average na mga halaga ng na-filter na data.
Hindi mahalaga kung gagamitin mo ang function_num argument para sa nakikita o nakatagong mga row. Ang parehong argumento ay nagbibigay ng parehong resulta sa na-filter na data.
Image - Pumili ng anumang cell sa set ng data.
-
Pumunta sa Home, pagkatapos ay piliin ang Suriin & Filter > Filter.
Image -
Gamitin ang mga drop-down na arrow upang i-filter ang data ng worksheet.
Image -
Pansinin kung paano nagbabago ang mga value sa tuwing pipili ka ng iba't ibang pamantayan ng filter.
Image
Gamitin ang SUBTOTAL Function na may Nakagrupong Data
Kapag ang data ay pinagsama-sama, mayroong isang paraan upang ilapat ang SUBTOTAL function sa bawat indibidwal na pangkat at pagkatapos ay kalkulahin ang kabuuang kabuuan para sa buong set ng data.
- Pumili ng anumang cell sa set ng data.
-
Piliin ang Data > Subtotal upang buksan ang Subtotal dialog box.
Image - Piliin ang Sa bawat pagbabago sa drop-down na arrow at piliin ang pagpapangkat kung saan kakalkulahin ang bawat subtotal.
- Piliin ang Use function drop-down arrow at pumili ng function_num.
- Sa Magdagdag ng subtotal sa na listahan, piliin ang column na ilalapat ang formula.
-
Piliin ang OK.
Image -
Ang mga subtotal ay ipinapasok para sa bawat pangkat ng data, at isang malaking kabuuan ang inilalagay sa ibaba ng set ng data.
Image - Para baguhin ang function_num, i-highlight ang anumang cell sa set ng data at piliin ang Data > Subtotal. Pagkatapos, gawin ang iyong mga pagpipilian sa Subtotal dialog box.
