Kui teie Exceli tööleht sisaldab peidetud ridu, filtreeritud andmeid või rühmitatud andmeid, kasutage Exceli vahesumma funktsiooni. Funktsioon SUBTOTAL võib arvutustes hõlmata või välistada peidetud väärtusi. Lisaks andmerühma kogusumma leidmisele saab Excel arvutada teie andmete keskmise, maksimaalse, minimaalse, standardhälbe ja dispersiooni. Siin on, kuidas Excelis vahesummasid sisestada.
Selles artiklis olevad juhised kehtivad Exceli jaoks Microsoft 365, Excel 2019 ja Excel 2016 jaoks.
Funktsiooni VAHEKOGU süntaks
Kasutage Exceli funktsiooni VÄLIKUMMA, et teha töölehe väärtuste kokkuvõte erinevatel viisidel. See on eriti kasulik, kui teie tööleht sisaldab peidetud ridu, mida soovite arvutusse kaasata.
Funktsiooni SUBTOTAL süntaks on: SUBTOTAL(function_num, ref1, ref2, …)
Argument function_num on nõutav ja määrab vahesumma jaoks kasutatava matemaatilise toimingu tüübi. Funktsioon SUBTOTAL saab lisada numbreid, arvutada valitud arvude keskmisi väärtusi, leida vahemiku maksimum- ja miinimumväärtusi, loendada väärtuste arvu valitud vahemikus ja palju muud.
Funktsioon SUBTOTAL ignoreerib lahtreid, mis ei sisalda andmeid, ja mittenumbriliste väärtustega lahtreid.
See argument on arv ja sõltub sellest, kas soovite kaasata tulemusesse peidetud read või jätta tulemusest välja peidetud read. Need read võib käsitsi peita või filtri abil peita.
Argumendid function_num hõlmavad järgmist:
| Funktsiooniülesanne | function_num | function_num |
|---|---|---|
| (sisaldab peidetud väärtusi) | (ei sisalda peidetud väärtusi) | |
| KESKMINE | 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 |
Funktsiooni_numbri viiteargumendid 1 kuni 11 sisaldavad väärtusi ainult peidetud ridadel, kui ridade peitmiseks kasutatakse käsku Peida. Kui kasutate käsku Filter, ei sisalda SUBTOTAL arvutused peidetud filtri tulemusi.
Argument ref1 on nõutav. Need on lahtrid, mida kasutatakse valitud funktsiooni_num argumendi tulemuste arvutamiseks. See argument võib olla väärtus, üks lahter või lahtrite vahemik.
Argumendid ref2, … on valikulised. Need on täiendavad lahtrid, mis on arvutusse kaasatud.
Kasutage peidetud ridadega funktsiooni VÄLIKUMMA
Exceli funktsioone saab sisestada käsitsi või dialoogiboksi Funktsiooni argumendid abil. Et illustreerida, kuidas funktsioon valemiriba abil käsitsi sisestada, kasutatakse järgmises näites argumenti COUNT function_num, et loendada väärtuste arv nähtavatel ja nii nähtavatel kui ka peidetud ridadel.
Funktsiooni SUBTOTAL kasutamiseks töölehe ridade arvu loendamiseks:
- Alustage töölehega, mis sisaldab mitut rida andmeid.
- Valige lahter, mis sisaldab nähtavate ridade arvu.
-
Sisestage funktsiooniribale =SUBTOTAL. Tippimise ajal soovitab Excel funktsiooni. Topeltklõpsake funktsioonil SUBTOTAL.
Funktsiooniargumentide dialoogiboksi kasutamiseks vahesumma funktsiooni sisestamiseks minge Formulas ja valige Math & Trig >SUBTOTAL.
Image -
Ilmuvas rippmenüüs topeltklõpsake argumendil 102 – COUNT function_num.
Image -
Sisestage koma (,).
Image -
Valige töölehel valemisse kaasatavad lahtrid.
Image -
Vajutage Sisestus, et näha tulemust etapis 2 valitud lahtris.
Image - Valige lahter, mis sisaldab nähtavate ja peidetud ridade arvu.
- Sisestage funktsiooniribale =SUBTOTAL. Tippimise ajal soovitab Excel funktsiooni. Topeltklõpsake funktsioonil SUBTOTAL.
-
Ilmuvas rippmenüüs topeltklõpsake argumendil 2 – COUNT function_num, seejärel tippige koma (,).
-
Valige töölehel valemisse kaasatavad lahtrid, seejärel vajutage Sisestus.
Image -
Peida mitu andmerida. Selles näites peideti read, mille müük oli alla 100 000 $.
Image
Kasutage filtreeritud andmetega funktsiooni VÄLIKUMMA
Funktsiooni SUBTOTAL kasutamine filtreeritud andmete puhul ignoreerib andmeid ridades, mille filter on eemaldanud. Iga kord, kui filtri kriteeriumid muutuvad, arvutab funktsioon uuesti, et näidata nähtavate ridade vahesummat.
Funktsiooni SUBTOTAL kasutamiseks andmete filtreerimise ajal arvutustulemuste erinevuste nägemiseks:
-
Looge VAHEKOGU valemid. Näiteks looge valemid filtreeritud andmete vahesumma ja keskmise väärtuse määramiseks.
Pole oluline, kas kasutate argumenti function_num nähtavate või peidetud ridade jaoks. Mõlemad argumendid annavad filtreeritud andmetes sama tulemuse.
Image - Valige andmekogus mis tahes lahter.
-
Avage Kodu, seejärel valige Sortimine ja filtreerimine > Filter.
Image -
Kasutage töölehe andmete filtreerimiseks rippmenüü nooli.
Image -
Pange tähele, kuidas väärtused muutuvad iga kord, kui valite erinevad filtrikriteeriumid.
Image
Kasutage rühmitatud andmetega funktsiooni VÄLIKUMMA
Andmete rühmitamisel on võimalik rakendada igale üksikule rühmale funktsiooni VÄLIKUMMA ja seejärel arvutada kogu andmestiku kogusumma.
- Valige andmekogus mis tahes lahter.
-
Valige Data > Subtotal, et avada dialoogiboks Subtotal.
Image - Valige rippmenüü nool Iga muudatuse juuresja valige rühmitus, mille alusel iga vahesumma arvutatakse.
- Valige rippmenüü nool Use function ja valige funktsiooni_num.
- Valige loendis Lisa vahesummaveerg, millele valem rakendatakse.
-
Valige OK.
Image -
Iga andmerühma jaoks lisatakse vahesummad ja kogusumma lisatakse andmestiku allossa.
Image - Funktsiooni_numbri muutmiseks tõstke andmestiku suvaline lahter esile ja valige Data > Subtotal. Seejärel tehke oma valikud dialoogiboksis Subtotal.
