Ja jūsu Excel darblapā ir slēptas rindas, filtrēti dati vai grupēti dati, izmantojiet Excel funkciju SUBTOTAL. Funkcija SUBTOTAL var iekļaut vai izslēgt aprēķinos slēptās vērtības. Papildus datu grupas kopsummas atrašanai programma Excel var aprēķināt jūsu datu vidējo, maksimālo, minimālo, standarta novirzi un dispersiju. Tālāk ir norādīts, kā programmā Excel ievietot starpsummas.
Šajā rakstā sniegtie norādījumi attiecas uz Excel for Microsoft 365, Excel 2019 un Excel 2016.
Funkcijas SUBTOTAL sintakse
Izmantojiet funkciju SUBTOTAL programmā Excel, lai dažādos veidos apkopotu darblapas vērtības. Tas ir īpaši noderīgi, ja darblapā ir slēptās rindas, kuras vēlaties iekļaut aprēķinā.
Funkcijas SUBTOTAL sintakse ir: SUBTOTAL(funkcijas_numurs, ref1, ref2, …)
Ir nepieciešams arguments function_num, un tas norāda starpsummai izmantojamās matemātiskās darbības veidu. Funkcija SUBTOTAL var pievienot skaitļus, aprēķināt atlasīto skaitļu vidējo vērtību, atrast maksimālās un minimālās vērtības diapazonā, saskaitīt vērtību skaitu atlasītajā diapazonā un veikt citas darbības.
Funkcija SUBTOTAL ignorē šūnas, kurās nav datu, un šūnas ar vērtībām, kas nav skaitliskas.
Šis arguments ir skaitlis, un tas ir atkarīgs no tā, vai vēlaties iekļaut slēptās rindas rezultātā vai izslēgt slēptās rindas no rezultāta. Šīs rindas var būt manuāli paslēptas vai paslēptas ar filtru.
Function_num argumenti ietver:
| Funkcijas uzdevums | function_num | function_num |
|---|---|---|
| (ietver slēptās vērtības) | (izņemot slēptās vērtības) | |
| VIDĒJS | 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 |
Atsauces argumenti function_num no 1 līdz 11 ietver vērtības tikai slēptās rindās, ja rindu paslēpšanai tiek izmantota komanda Slēpt. Izmantojot komandu Filtrs, SUBTOTAL aprēķinos nav iekļauti slēptie filtra rezultāti.
Ir nepieciešams arguments ref1. Šīs ir šūnas, ko izmanto, lai aprēķinātu atlasītā argumenta function_num rezultātus. Šis arguments var būt vērtība, viena šūna vai šūnu diapazons.
Argumenti ref2, … nav obligāti. Šīs ir papildu šūnas, kas ir iekļautas aprēķinā.
Izmantojiet funkciju SUBTOTAL ar slēptām rindām
Excel funkcijas var ievadīt manuāli vai ar dialoglodziņa Function Arguments palīdzību. Lai ilustrētu, kā manuāli ievadīt funkciju, izmantojot formulu joslu, tālāk sniegtajā piemērā tiek izmantots arguments COUNT function_num, lai uzskaitītu vērtību skaitu redzamajās un gan redzamajās, gan slēptajās rindās.
Lai izmantotu funkciju SUBTOTAL, lai saskaitītu rindu skaitu darblapā:
- Sāciet ar darblapu, kurā ir vairākas datu rindas.
- Atlasiet šūnu, kurā būs redzamo rindu skaits.
-
Funkciju joslā ievadiet =SUBTOTAL. Rakstīšanas laikā programma Excel iesaka funkciju. Veiciet dubultklikšķi uz funkcijas SUBTOTAL.
Lai izmantotu dialoglodziņu Function Arguments, lai ievadītu funkciju SUBTOTAL, dodieties uz Formulas un atlasiet Math & Trig >SUBTOTAL.
Image -
Atvērtajā nolaižamajā izvēlnē veiciet dubultklikšķi uz argumenta 102 – COUNT function_num.
Image -
Ierakstiet komatu (,).
Image -
Darblapā atlasiet šūnas, kuras iekļaut formulā.
Image -
Nospiediet Enter, lai redzētu rezultātu šūnā, kuru atlasījāt 2. darbībā.
Image - Atlasiet šūnu, kurā būs redzamo un slēpto rindu skaits.
- Funkciju joslā ievadiet =SUBTOTAL. Rakstīšanas laikā programma Excel iesaka funkciju. Veiciet dubultklikšķi uz funkcijas SUBTOTAL.
-
Atvērtajā nolaižamajā izvēlnē veiciet dubultklikšķi uz argumenta 2 – COUNT function_num, pēc tam ierakstiet komatu (,).
-
Darblapā atlasiet šūnas, kuras iekļaut formulā, un pēc tam nospiediet Enter.
Image -
Paslēpt vairākas datu rindas. Šajā piemērā tika paslēptas rindas, kuru pārdošanas apjoms ir mazāks par 100 000 ASV dolāru.
Image
Izmantojiet funkciju SUBTOTAL ar filtrētiem datiem
Izmantojot funkciju SUBTOTAL filtrētiem datiem, tiek ignorēti dati rindās, kuras filtrs ir noņēmis. Katru reizi, kad mainās filtra kritēriji, funkcija pārrēķina, lai parādītu redzamo rindu starpsummu.
Lai izmantotu funkciju SUBTOTAL, lai redzētu atšķirības aprēķinu rezultātos datu filtrēšanas laikā:
-
Izveidot SUBTOTAL formulas. Piemēram, izveidojiet formulas, lai noteiktu filtrēto datu starpsummas un vidējās vērtības.
Tam nav nozīmes, vai izmantojat argumentu function_num redzamām vai slēptām rindām. Abi argumenti nodrošina vienādu rezultātu filtrētajos datos.
Image - Atlasiet jebkuru šūnu datu kopā.
-
Dodieties uz Home, pēc tam atlasiet Sort & Filter > Filtrs.
Image -
Izmantojiet nolaižamās bultiņas, lai filtrētu darblapas datus.
Image -
Ņemiet vērā, kā vērtības mainās katru reizi, kad atlasāt dažādus filtra kritērijus.
Image
Izmantojiet SUBTOTAL funkciju ar grupētiem datiem
Kad dati tiek grupēti, ir veids, kā katrai atsevišķai grupai lietot funkciju SUBTOTAL un pēc tam aprēķināt kopējo summu visai datu kopai.
- Atlasiet jebkuru šūnu datu kopā.
-
Atlasiet Data > Subtotal, lai atvērtu dialoglodziņu Subtotal.
Image - Atlasiet Pie katras izmaiņasnolaižamajā izvēlnē un izvēlieties grupu, kurai tiks aprēķināta katra starpsumma.
- Atlasiet nolaižamo bultiņu Use function un izvēlieties function_num.
- Sarakstā Pievienot starpsummuatlasiet kolonnu, kurai tiks piemērota formula.
-
Atlasiet OK.
Image -
Starpsummas tiek ievietotas katrai datu grupai, un kopsumma tiek ievietota datu kopas apakšā.
Image - Lai mainītu funkciju_num, iezīmējiet jebkuru šūnu datu kopā un atlasiet Data > Subtotal. Pēc tam izdariet izvēli dialoglodziņā Subtotal.
