Laadige alla töövihiku näide
See õpetus näitab, kuidas Funktsioon Excel VAHESUMMA Excelis kokkuvõtliku statistika arvutamiseks.
Funktsioonide ülevaade
Funktsioon SUBTOTAL Arvutab andmete seeria kohta kokkuvõtliku statistika. Kättesaadav statistika hõlmab (kuid mitte ainult) keskmist, standardhälvet, arvu, min ja max. Vaadake täielikku nimekirja allpool funktsioonide sisendite jaotises:
Exceli töölehe funktsiooni VAHESUMMA kasutamiseks kasutage lahtrit ja tippige:
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsiooni süntaks ja sisendid:
| 1 | = VAHESUMMA (funktsiooni_arv, VIIDE1) |
funktsiooni_number - number, mis näitab, millist toimingut teha.
VIIDE1 - Vahemikud või viited, mis sisaldavad arvutatavaid andmeid.
Mis on SUBTOTAL funktsioon?
VAHESUMMA on üks arvutustabelite ainulaadsetest funktsioonidest, kuna see suudab eristada peidetud lahtreid varjatud lahtritest. See võib osutuda üsna kasulikuks filtreeritud vahemike käsitlemisel või kui peate seadistama arvutused erinevate kasutajate valikute põhjal. Kuna ta teab oma arvutustes ka teisi VAHESUGU funktsioone ignoreerida, saame seda kasutada ka suurte kokkuvõtlike andmete raames, kartmata kahekordset arvestamist.
Põhikokkuvõte koos VAHESUMMAga
Oletame, et teil oli sorteeritud toodete müügi tabel ja tahtsite luua iga toote kohta kogusummad ning luua ka üldsumma. Võite kasutada PivotTable -liigendtabelit või sisestada mõned valemid. Kaaluge seda paigutust:
Olen paigutanud lahtritesse B5 ja B8 mõned SUBTOTAL funktsioonid, mis näevad välja sellised
| 1 | = VAHESUMMA (9, B2: B4) |
Süntaksist saate esimese argumendi jaoks kasutada erinevaid numbreid. Meie konkreetsel juhul kasutame 9, et näidata, et tahame summat teha.
Keskendume lahtrile B9. Sellel on see valem, mis hõlmab kogu veeru B andmevahemikku, kuid ei sisalda muid vahekokkuvõtteid.
| 1 | = VAHESUMMA (9, B2: B8) |
MÄRKUS. Kui te ei soovi kõiki kokkuvõtlikke valemeid ise kirjutada, võite minna andmeribale ja kasutada viisardi Outline - Vahesumma. See lisab automaatselt read ja asetab valemid teie jaoks.
Esimeste argumentide erinevus
Esimeses näites kasutasime summat 9 -ga, et soovime summat teha. Erinevus 9 ja 109 kasutamise vahel oleks see, kuidas me tahame, et funktsioon peidetud ridu käsitleks. Kui kasutate tähiseid 1XX, ei hõlma funktsioon ridu, mis on käsitsi peidetud või filtreeritud.
Siin on meie laud varasemast ajast. Oleme nihutanud funktsioone, nii et näeme erinevust 9 ja 109 argumendi vahel. Kui kõik on nähtav, on tulemused samad.
Kui rakendame veeru B väärtuse 6 filtreerimiseks filtri, jäävad need kaks funktsiooni samaks.
Kui me ridasid käsitsi peidame, näeme erinevust. Funktsioon 109 suutis peidetud rida ignoreerida, samas kui funktsioon 9 seda ei teinud.
Muutke matemaatika toimingut SUBTOTAL
Võib -olla soovite mõnikord oma kasutajale anda võimaluse muuta, millist tüüpi arvutusi tehakse. Näiteks kas nad tahavad saada summat või keskmist. Kuna SUBTOTAL juhib matemaatilist toimingut argumendi numbri abil, saate selle kirjutada ühte valemisse. Siin on meie seadistus:
Oleme loonud D2 -s rippmenüü, kus kasutaja saab valida kas „Summa” või „Keskmine”. E2 valem on järgmine:
| 1 | = VAHESUMMA (IF (D2 = "Keskmine", 1, IF (D2 = "Summa", 9)), B2: B4) |
Funktsioon IF määrab siin kindlaks, millise arvulise argumendi LÜLESUMMAle anda. Kui A5 on “Keskmine”, annab see tulemuseks 1 ja SUBTOTAL annab keskmise B2: B4. Või kui A5 võrdub summaga, väljastab IF 9 ja saame erineva tulemuse.
Seda võimalust saate laiendada otsingutabeli abil, et loetleda veelgi rohkem toiminguid, mida soovite teha. Teie tabel võib välja näha selline
Seejärel saate muuta E2 valemi selliseks
| 1 | = VAHESUMMA (VLOOKUP (A5, otsingutabel, 2, 0), B2: B4) |
Tingimuslikud valemid VAHESUMMAga
Kuigi SUBTOTALil on palju toiminguid, mida ta saab teha, ei saa ta kriteeriume iseseisvalt kontrollida. Selle toimingu tegemiseks saame seda siiski kasutada abistajaveerus. Kui teil on andmete veerg, mida teate alati Kui teil on andmeid, saate kasutada SUBTOTALs peidetud ridade tuvastamise võimalust.
Siin on tabel, millega selles näites töötame. Lõpuks tahaksime „Apple” väärtused kokku liita, kuid laseme kasutajal ka veeru Kogus filtreerida.
Kõigepealt looge abistajaveerg, mis sisaldab funktsiooni SUBTOTAL. C2 valem on järgmine:
| 1 | = VAHESUMMA (103, A2) |
Pidage meeles, et 103 tähendab, et tahame teha COUNTA. Soovitan kasutada COUNTAd, kuna saate seejärel täita oma A2 võrdluslahtri kas numbreid või teksti. Nüüd on teil tabel, mis näeb välja selline:
See ei tundu esialgu kasulik, sest kõik väärtused on vaid 1. Kui aga peidame rea 3, muutub see C3 -s olev „1” väärtuseks 0, kuna see osutab peidetud reale. Kuigi konkreetse peidetud lahtri väärtust kujutava pildi kuvamine on võimatu, saate seda kontrollida, peites rea ja kirjutades seejärel kontrollimiseks sellise põhivalemi.
| 1 | = C3 |
Nüüd, kui meil on veerg, mille väärtus muutub sõltuvalt sellest, kas see on peidetud või mitte, oleme valmis kirjutama lõpliku võrrandi. Meie SUMIFS näeb välja selline
Selles valemis liidame veeru B väärtused ainult siis, kui veerg A võrdub "Apple", ja veeru C väärtus on 1 (aka rida pole peidetud). Oletame, et meie kasutaja soovib 600 välja filtreerida, kuna see tundub ebanormaalselt kõrge. Näeme, et meie valem annab õige tulemuse.
Selle võimaluse abil saate tšeki rakendada COUNTIFS, SUMIFS või isegi SUMPRODUCT. Lisate võimaluse lubada oma kasutajatel mõnda lauaviilutajat juhtida ja olete valmis looma vinge armatuurlaua.
SUBTOTAL Google'i arvutustabelites
Funktsioon SUBTOTAL töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
Vahesumma näited VBA -s
Funktsiooni SUBTOTAL saate kasutada ka VBA -s. Tüüp:application.worksheetfunction.subtotal (function_num, reh1)
Järgmiste VBA avalduste täitmine
| 1234567891011121314151617 | Vahemik ("C7") = Application.WorksheetFunction.Subtotal (1, vahemik ("C2: C5"))Vahemik ("C8") = Application.WorksheetFunction.Subtotal (2, vahemik ("C2: C5"))Vahemik ("C9") = Application.WorksheetFunction.Subtotal (4, vahemik ("C2: C5"))Vahemik ("C10") = Application.WorksheetFunction.Subtotal (5, vahemik ("C2: C5"))Vahemik ("C11") = Application.WorksheetFunction.Subtotal (9, vahemik ("C2: CE5"))Vahemik ("D7") = Application.WorksheetFunction.Subtotal (1, vahemik ("D2: D5"))Vahemik ("D8") = Application.WorksheetFunction.Subtotal (2, vahemik ("D2: D5"))Vahemik ("D9") = Application.WorksheetFunction.Subtotal (4, vahemik ("D2: D5"))Vahemik ("D10") = Application.WorksheetFunction.Subtotal (5, vahemik ("D2: D5"))Vahemik ("D11") = Application.WorksheetFunction.Subtotal (9, vahemik ("D2: D5"))Vahemik ("E7") = Application.WorksheetFunction.Subtotal (1, vahemik ("E2: E5"))Vahemik ("E8") = Application.WorksheetFunction.Subtotal (2, vahemik ("E2: E5"))Vahemik ("E9") = Application.WorksheetFunction.Subtotal (4, vahemik ("E2: E5"))Vahemik ("E10") = Application.WorksheetFunction.Subtotal (5, vahemik ("E2: E5"))Vahemik ("E11") = Application.WorksheetFunction.Subtotal (9, vahemik ("E2: E5")) |
annab järgmised tulemused
Funktsiooniargumentide (funktsiooni_number jne) jaoks saate need kas otse funktsiooni sisestada või määratleda selle asemel kasutatavad muutujad.
Tagasi kõigi Exceli funktsioonide loendisse
