Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parāda, kā lietot Excel SUBTOTAL funkcija programmā Excel, lai aprēķinātu kopsavilkuma statistiku.
FUNKCIJAS DAĻU PĀRSKATS
Funkcija SUBTOTAL Aprēķina datu kopsavilkuma statistiku. Pieejamā statistika ietver, bet neaprobežojas ar vidējo, standarta novirzi, skaitu, min un maks. Pilnu sarakstu skatiet zemāk funkciju ievades sadaļā:
Lai izmantotu Excel darblapas funkciju APBOTOTAL, atlasiet šūnu un ierakstiet:
(Ievērojiet, kā parādās formulas ievades)
FUNKCIJU DAĻU Sintakse un ievades:
| 1 | = SUBTOTAL (function_num, REF1) |
funkcija_numurs - Skaitlis, kas norāda, kuru darbību veikt.
REF1 - Diapazoni vai atsauces, kas satur aprēķināmos datus.
Kas ir SUBTOTAL funkcija?
Starpsumma ir viena no unikālajām izklājlapu funkcijām, jo tā var atšķirt slēptās šūnas no neslēptajām šūnām. Tas var izrādīties diezgan noderīgi, strādājot ar filtrētiem diapazoniem vai ja jums ir jāiestata aprēķini, pamatojoties uz dažādām lietotāja atlasēm. Tā kā tā zina arī, ka no saviem aprēķiniem neņem vērā citas SUBTOTAL funkcijas, mēs to varam izmantot arī lielos apkopotos datos, nebaidoties no dubultas uzskaites.
Pamata kopsavilkums ar SUBTOTAL
Pieņemsim, ka jums bija sakārtotu produktu pārdošanas tabula un vēlaties izveidot kopsummas katram produktam, kā arī izveidot kopējo summu. Varat izmantot rakurstabulu vai ievietot dažas formulas. Apsveriet šo izkārtojumu:
Šūnās B5 un B8 esmu ievietojis dažas SUBTOTAL funkcijas, kas izskatās šādi
| 1 | = KOPSUMMA (9, B2: B4) |
No sintakses pirmajam argumentam varat izmantot dažādus skaitļus. Mūsu konkrētajā gadījumā mēs izmantojam 9, lai norādītu, ka vēlamies veikt summu.
Koncentrēsimies uz šūnu B9. Tam ir šī formula, kas ietver visu B slejas datu diapazonu, bet neietver pārējās starpsummas.
| 1 | = KOPSUMMA (9, B2: B8) |
PIEZĪME. Ja nevēlaties visas kopsavilkuma formulas rakstīt pats, varat doties uz datu lenti un izmantot vedni Kontūra - starpsumma. Tas automātiski ievieto rindas un ievieto formulas.
Atšķirība pirmajos argumentos
Pirmajā piemērā mēs izmantojām 9, lai norādītu, ka vēlamies veikt kādu summu. Atšķirība starp 9 un 109 izmantošanu būtu tā, kā mēs vēlamies, lai funkcija apstrādātu slēptās rindas. Ja izmantojat apzīmējumus 1XX, funkcija neietvers rindas, kas ir manuāli paslēptas vai filtrētas.
Šeit ir mūsu galds no iepriekšējā. Mēs esam pārcēluši funkcijas, lai mēs varētu redzēt atšķirību starp 9 un 109 argumentiem. Ja viss ir redzams, rezultāti ir vienādi.
Ja mēs izmantojam filtru, lai filtrētu vērtību 6 kolonnā B, abas funkcijas paliek nemainīgas.
Ja mēs manuāli slēpjam rindas, mēs redzam atšķirību. Funkcija 109 varēja ignorēt slēpto rindu, bet 9 funkcija to nedarīja.
Mainiet matemātisko darbību ar SUBTOTAL
Jūs varētu vēlēties, lai dažreiz jūs varētu dot savam lietotājam iespēju mainīt aprēķinu veidu. Piemēram, vai viņi vēlas iegūt summu vai vidējo. Tā kā SUBTOTAL kontrolē matemātisko darbību ar argumenta skaitli, varat to ierakstīt vienā formulā. Šeit ir mūsu iestatījumi:
Mēs esam izveidojuši nolaižamo izvēlni D2, kur lietotājs var izvēlēties “Sum” vai “Average”. E2 formula ir šāda:
| 1 | = KOPSUMMA (IF (D2 = "Vidējais", 1, IF (D2 = "Summa", 9)), B2: B4) |
Šeit funkcija IF noteiks, kuru skaitlisko argumentu sniegt starpsummai. Ja A5 ir “Vidējs”, tad tas izvadīs 1, un SUBTOTAL parādīs vidējo vērtību B2: B4. Vai arī, ja A5 ir vienāds ar “Sum”, tad IF izdala 9, un mēs iegūstam atšķirīgu rezultātu.
Šo iespēju varētu paplašināt, izmantojot uzmeklēšanas tabulu, lai uzskaitītu vēl vairāk veidu darbību, kuras vēlaties veikt. Jūsu uzmeklēšanas tabula varētu izskatīties šādi
Pēc tam E2 formulu var mainīt uz
| 1 | = KOPSUMMA (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Nosacījuma formulas ar SUBTOTAL
Lai gan SUBTOTAL var veikt daudzas darbības, tas nevar pats pārbaudīt kritērijus. Tomēr mēs varam to izmantot palīga kolonnā, lai veiktu šo darbību. Ja jums ir datu kolonna, par kuru zināt, ka tā būs vienmēr ja tajā ir datu daļa, varat izmantot SUBTOTALs iespēju atklāt slēptās rindas.
Šeit ir tabula, ar kuru mēs strādāsim šajā piemērā. Galu galā mēs vēlētos, lai mēs varētu apkopot “Apple” vērtības, bet arī ļaut lietotājam filtrēt sleju Daudzums.
Vispirms izveidojiet palīgu kolonnu, kurā atradīsies SUBTOTAL funkcija. C2 formulā ir šāda formula:
| 1 | = KOPSUMMA (103, A2) |
Atcerieties, ka 103 nozīmē, ka mēs vēlamies veikt COUNTA. Es iesaku izmantot COUNTA, jo pēc tam varat aizpildīt A2 atsauces šūnu arī ciparus vai tekstu. Tagad jums būs tabula, kas izskatās šādi:
Sākumā tas nešķiet noderīgi, jo visas vērtības ir tikai 1. Tomēr, ja mēs paslēpsim 3. rindu, šis cipars “1” tiks mainīts uz 0, jo tas norāda uz slēptu rindu. Lai gan nav iespējams izveidot attēlu, kas parāda konkrētās slēptās šūnas vērtību, varat to pārbaudīt, slēpjot rindu un pēc tam uzrakstot šādu pamata formulu, lai to pārbaudītu.
| 1 | = C3 |
Tagad, kad mums ir kolonna, kuras vērtība mainīsies atkarībā no tā, vai tā ir paslēpta, mēs esam gatavi uzrakstīt galīgo vienādojumu. Mūsu SUMIFS izskatīsies šādi
Šajā formulā mēs summēsim vērtības no B slejas tikai tad, ja A sleja ir “Apple”, un vērtība kolonnā C ir 1 (aka, rinda nav slēpta). Pieņemsim, ka mūsu lietotājs vēlas filtrēt 600, jo tas šķiet neparasti augsts. Mēs redzam, ka mūsu formula dod pareizu rezultātu.
Izmantojot šo iespēju, jūs varētu piemērot čeku COUNTIFS, SUMIFS vai pat SUMPRODUCT. Jūs pievienojat iespēju ļaut lietotājiem kontrolēt dažus galda griezējus, un esat gatavs izveidot lielisku informācijas paneli.
SUBTOTAL Google izklājlapās
Funkcija SUBTOTAL Google izklājlapās darbojas tieši tāpat kā programmā Excel:
DAŽĀDI piemēri VBA
VBA varat izmantot arī funkciju SUBTOTAL. Tips:application.worksheetfunction.subtotal (function_num, reh1)
Izpildot šādus VBA paziņojumus
| 1234567891011121314151617 | Diapazons ("C7") = Application.WorksheetFunction.Subtotal (1, diapazons ("C2: C5"))Diapazons ("C8") = Application.WorksheetFunction.Subtotal (2, diapazons ("C2: C5"))Diapazons ("C9") = Application.WorksheetFunction.Subtotal (4, Diapazons ("C2: C5"))Diapazons ("C10") = Application.WorksheetFunction.Subtotal (5, diapazons ("C2: C5"))Diapazons ("C11") = Application.WorksheetFunction.Subtotal (9, Diapazons ("C2: CE5"))Diapazons ("D7") = Application.WorksheetFunction.Subtotal (1, Diapazons ("D2: D5"))Diapazons ("D8") = Application.WorksheetFunction.Subtotal (2, Diapazons ("D2: D5"))Diapazons ("D9") = Application.WorksheetFunction.Subtotal (4, Diapazons ("D2: D5"))Diapazons ("D10") = Application.WorksheetFunction.Subtotal (5, diapazons ("D2: D5"))Diapazons ("D11") = Application.WorksheetFunction.Subtotal (9, Diapazons ("D2: D5"))Diapazons ("E7") = Application.WorksheetFunction.Subtotal (1, Diapazons ("E2: E5"))Diapazons ("E8") = Application.WorksheetFunction.Subtotal (2, Diapazons ("E2: E5"))Diapazons ("E9") = Application.WorksheetFunction.Subtotal (4, Diapazons ("E2: E5"))Diapazons ("E10") = Application.WorksheetFunction.Subtotal (5, diapazons ("E2: E5"))Diapazons ("E11") = Application.WorksheetFunction.Subtotal (9, Diapazons ("E2: E5")) |
radīs šādus rezultātus
Funkcijas argumentiem (funkcija_numurs utt.) Varat tos ievadīt tieši funkcijā vai definēt mainīgos, ko izmantot.
Atgriezieties visu Excel funkciju sarakstā
