Kai „Excel“darbalapyje yra paslėptų eilučių, filtruotų duomenų arba sugrupuotų duomenų, naudokite „Excel“SUBTOTAL funkciją. Funkcija SUBTOTAL gali įtraukti arba neįtraukti paslėptų verčių skaičiavimuose. „Excel“gali ne tik rasti duomenų grupės bendrą sumą, bet ir apskaičiuoti vidutinį, didžiausią, mažiausią, standartinį nuokrypį ir jūsų duomenų dispersiją. Štai kaip „Excel“įterpti tarpines sumas.
Šiame straipsnyje pateiktos instrukcijos taikomos „Excel“, skirta „Microsoft 365“, „Excel 2019“ir „Excel 2016“.
Funkcijos SUBTOTAL sintaksė
Naudokite „Excel“funkciją SUBTOTAL, kad įvairiais būdais apibendrintumėte darbalapio reikšmes. Tai ypač naudinga, kai darbalapyje yra paslėptų eilučių, kurias norite įtraukti į skaičiavimą.
Funkcijos SUBTOTAL sintaksė yra: SUBTOTAL(function_num, ref1, ref2, …)
Argumentas function_num yra būtinas ir nurodo matematinės operacijos tipą, naudojamą tarpinei sumai. Funkcija SUBTOTAL gali pridėti skaičius, apskaičiuoti vidutinę pasirinktų skaičių reikšmę, rasti didžiausią ir mažiausią reikšmes diapazone, suskaičiuoti reikšmių skaičių pasirinktame diapazone ir dar daugiau.
Funkcija SUBTOTAL nepaiso langelių, kuriuose nėra duomenų, ir langelių su neskaitinėmis reikšmėmis.
Šis argumentas yra skaičius ir priklauso nuo to, ar į rezultatą norite įtraukti paslėptas eilutes, ar iš rezultato neįtraukti paslėptų eilučių. Šios eilutės gali būti paslėptos rankiniu būdu arba paslėptos filtro.
Function_num argumentai apima:
| Funkcinė užduotis | function_num | function_num |
|---|---|---|
| (įskaitant paslėptas reikšmes) | (išskyrus paslėptas vertes) | |
| VIDUTINIS | 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 |
Function_num nuorodos argumentai nuo 1 iki 11 apima reikšmes tik paslėptose eilutėse, kai eilėms paslėpti naudojama komanda Slėpti. Kai naudojate komandą Filtras, SUBTOTAL skaičiavimai neapima paslėptų filtrų rezultatų.
Argumentas ref1 būtinas. Tai yra langeliai, naudojami skaičiuojant pasirinkto argumento function_num rezultatus. Šis argumentas gali būti reikšmė, vienas langelis arba langelių diapazonas.
Argumentai ref2, … yra neprivalomi. Tai yra papildomi langeliai, kurie įtraukiami į skaičiavimą.
Naudokite funkciją SUBSUMMA su paslėptomis eilutėmis
Excel funkcijas galima įvesti rankiniu būdu arba naudojant dialogo langą Funkcijos argumentai. Norėdami parodyti, kaip rankiniu būdu įvesti funkciją naudojant formulių juostą, toliau pateiktame pavyzdyje naudojamas argumentas COUNT function_num, kad būtų skaičiuojamas verčių skaičius matomose ir matomose bei paslėptose eilutėse.
Jei norite naudoti funkciją SUBTOTAL skaičiuojant eilučių skaičių darbalapyje:
- Pradėkite nuo darbalapio, kuriame yra kelios duomenų eilutės.
- Pasirinkite langelį, kuriame bus matomų eilučių skaičius.
-
Funkcijų juostoje įveskite =SUBTOTAL. Kai rašote, „Excel“siūlo funkciją. Dukart spustelėkite funkciją SUBTOTAL.
Jei norite naudoti dialogo langą Funkcijų argumentai funkcijai SUBSUMMA įvesti, eikite į Formulas ir pasirinkite Math & Trig >SUBTOTAL.
Image -
Parodomame išskleidžiamajame meniu dukart spustelėkite 102 – COUNT function_num argumentą.
Image -
Įveskite kablelį (,).
Image -
Darbalapyje pasirinkite langelius, kuriuos norite įtraukti į formulę.
Image -
Paspauskite Enter, kad pamatytumėte rezultatą langelyje, kurį pasirinkote atlikdami 2 veiksmą.
Image - Pasirinkite langelį, kuriame bus matomų ir paslėptų eilučių skaičius.
-
Funkcijų juostoje įveskite =SUBTOTAL. Kai rašote, „Excel“siūlo funkciją. Dukart spustelėkite funkciją SUBTOTAL.
- Parodomame išskleidžiamajame meniu dukart spustelėkite 2 – COUNT function_num argumentą, tada įveskite kablelį (,).
-
Darbo lape pasirinkite langelius, kuriuos norite įtraukti į formulę, tada paspauskite Enter.
Image -
Paslėpkite kelias duomenų eilutes. Šiame pavyzdyje buvo paslėptos eilutės, kuriose pardavimas mažesnis nei 100 000 USD.
Image
Naudokite SUBTOTAL funkciją su filtruotais duomenimis
Naudojant funkciją SUBTOTAL filtruotiems duomenims, nepaisoma duomenų eilutėse, kurias pašalino filtras. Kiekvieną kartą, kai keičiasi filtro kriterijai, funkcija iš naujo apskaičiuoja, kad parodytų matomų eilučių tarpinę sumą.
Jei norite naudoti funkciją SUBSUMMA, kad pamatytumėte skaičiavimo rezultatų skirtumus filtruojant duomenis:
-
Sukurkite SUBTOTAL formules. Pavyzdžiui, sukurkite formules, kad nustatytumėte filtruotų duomenų tarpinę ir vidutines reikšmes.
Nesvarbu, ar argumentą function_num naudosite matomoms ar paslėptoms eilutėms. Abu argumentai pateikia tą patį rezultatą filtruotuose duomenyse.
Image - Pasirinkite bet kurį duomenų rinkinio langelį.
-
Eikite į Pagrindinis, tada pasirinkite Rūšiuoti ir filtruoti > Filtras.
Image -
Naudokite išskleidžiamąsias rodykles darbalapio duomenims filtruoti.
Image -
Atkreipkite dėmesį, kaip vertės keičiasi kiekvieną kartą, kai pasirenkate skirtingus filtro kriterijus.
Image
Naudokite SUBTOTAL funkciją su sugrupuotais duomenimis
Kai duomenys grupuojami, yra būdas kiekvienai atskirai grupei pritaikyti funkciją SUBSUMMA ir tada apskaičiuoti bendrą viso duomenų rinkinio sumą.
- Pasirinkite bet kurį duomenų rinkinio langelį.
-
Pasirinkite Data > Subtotal, kad atidarytumėte dialogo langą Subtotal.
Image - Pasirinkite Atliekant kiekvieną pakeitimąišskleidžiamajame meniu ir pasirinkite grupavimą, pagal kurį bus skaičiuojama kiekviena tarpinė suma.
- Pasirinkite Use function ir pasirinkite function_num.
- Sąraše Pridėti tarpinę sumą prie pasirinkite stulpelį, kuriam bus taikoma formulė.
-
Pasirinkite Gerai.
Image -
Tarpinės sumos įterpiamos kiekvienai duomenų grupei, o bendra suma įterpiama duomenų rinkinio apačioje.
Image - Norėdami pakeisti funkcijos_numerį, pažymėkite bet kurį duomenų rinkinio langelį ir pasirinkite Data > Subtotal. Tada pasirinkite dialogo lange Subtotal.
