Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodo, kaip naudotis „Excel“ tarpinė funkcija „Excel“, kad apskaičiuotumėte suvestinę statistiką.
SUBTOTAL Funkcijų apžvalga
Funkcija SUBTOTAL Apskaičiuoja duomenų serijos suvestinę statistiką. Turima statistika apima, bet neapsiribojant, vidutinį, standartinį nuokrypį, skaičių, min. Ir maks. Visą sąrašą rasite funkcijų įvesties skyriuje:
Norėdami naudoti SUBTOTAL „Excel“ darbalapio funkciją, pasirinkite langelį ir įveskite:
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
SUBTOTAL Funkcijų sintaksė ir įvestys:
| 1 | = POSUMA (funkcija_num, REF1) |
funkcija_num - Skaičius, nurodantis, kurią operaciją atlikti.
1 nuoroda - Diapazonai ar nuorodos, kuriose yra apskaičiuojami duomenys.
Kas yra SUBTOTAL funkcija?
SUBTOTAL yra viena iš unikalių skaičiuoklių funkcijų, nes ji gali pasakyti skirtumą tarp paslėptų ir neslėptų langelių. Tai gali būti labai naudinga dirbant su filtruotais diapazonais arba kai reikia nustatyti skaičiavimus pagal skirtingus vartotojo pasirinkimus. Kadangi ji taip pat žino, kad neatsižvelgia į kitas SUBTOTAL funkcijas skaičiuodama, mes taip pat galime ją naudoti dideliuose apibendrintuose duomenyse, nebijodami dvigubo skaičiavimo.
Pagrindinė suvestinė su SUBTOTAL
Tarkime, kad turėjote surūšiuotų produktų pardavimo lentelę ir norėjote sukurti kiekvieno produkto sumas bei bendrą sumą. Galite naudoti „PivotTable“ arba įterpti kai kurias formules. Apsvarstykite šį išdėstymą:
Į langelius B5 ir B8 įdėjau keletą SUBTOTAL funkcijų, kurios atrodo taip
| 1 | = POSUMA (9, B2: B4) |
Iš sintaksės pirmam argumentui galite naudoti įvairius skaičius. Mūsų konkrečiu atveju mes naudojame 9, norėdami parodyti, kad norime padaryti sumą.
Sutelkime dėmesį į ląstelę B9. Ji turi šią formulę, kuri apima visą B stulpelio duomenų diapazoną, tačiau neįtraukia kitų tarpinių sumų.
| 1 | = POSUMA (9, B2: B8) |
PASTABA: jei nenorite visų suvestinės formulių rašyti patys, galite pereiti prie duomenų juostos ir naudoti vedlį „Kontūra - tarpinė suma“. Jis automatiškai įterps eilutes ir įdės jums formules.
Pirmųjų argumentų skirtumas
Pirmame pavyzdyje mes panaudojome 9, norėdami nurodyti, kad norime padaryti sumą. Skirtumas tarp 9 ir 109 naudojimo būtų tai, kaip norime, kad funkcija apdorotų paslėptas eilutes. Jei naudojate 1XX žymėjimus, į šią funkciją nebus įtrauktos eilutės, kurios buvo rankiniu būdu paslėptos arba filtruojamos.
Štai mūsų stalas iš anksčiau. Mes perkėlėme funkcijas, kad galėtume pamatyti skirtumą tarp 9 ir 109 argumentų. Kai viskas matoma, rezultatai tie patys.
Jei taikome filtrą, kad išfiltruotume 6 stulpelio B reikšmę, abi funkcijos išliks tos pačios.
Jei rankiniu būdu paslėpsime eilutes, matysime skirtumą. Funkcija 109 galėjo nepaisyti paslėptos eilutės, o 9 funkcija to nepadarė.
Keisti matematikos operaciją su SUBTOTAL
Galbūt norėsite kartais suteikti vartotojui galimybę pakeisti atliekamo skaičiavimo tipą. Pavyzdžiui, ar jie nori gauti sumą ar vidurkį. Kadangi SUBTOTAL kontroliuoja matematinę operaciją argumento skaičiumi, galite tai parašyti vienoje formulėje. Štai mūsų sąranka:
Sukūrėme D2 išskleidžiamąjį meniu, kuriame vartotojas gali pasirinkti „Sum“ arba „Average“. E2 formulė yra tokia:
| 1 | = POSUMA (IF (D2 = "Vidutinis", 1, IF (D2 = "Suma", 9)), B2: B4) |
Čia IF funkcija nustatys, kokį skaičių argumentą pateikti SUBTOTAL. Jei A5 yra „Vidutinis“, jis išves 1, o SUBTOTAL duos B2: B4 vidurkį. Arba, jei A5 yra „suma“, IF išleidžia 9, ir mes gauname kitokį rezultatą.
Šią galimybę galite išplėsti naudodami peržiūros lentelę, kurioje būtų išvardyti dar daugiau tipų operacijų, kurias norite atlikti. Jūsų paieškos lentelė gali atrodyti taip
Tada E2 formulę galite pakeisti į
| 1 | = SUBTOTAL (VLOOKUP (A5, LookupTable, 2, 0), B2: B4) |
Sąlyginės formulės su SUBTOTAL
Nors SUBTOTAL gali atlikti daug operacijų, jis negali pats patikrinti kriterijų. Tačiau mes galime jį naudoti pagalbiniame stulpelyje šiai operacijai atlikti. Kai turite duomenų stulpelį, kurį žinote visada jei turite duomenų, galite naudoti SUBTOTALs galimybę aptikti paslėptas eilutes.
Štai lentelė, su kuria dirbsime šiame pavyzdyje. Galų gale norėtume, kad galėtume apibendrinti „Apple“ vertes, bet taip pat leisti vartotojui filtruoti stulpelį „Kiekis“.
Pirmiausia sukurkite pagalbinį stulpelį, kuriame bus SUBTOTAL funkcija. C2 formulė yra tokia:
| 1 | = POSUMA (103, A2) |
Atminkite, kad 103 reiškia, kad norime padaryti COUNTA. Rekomenduoju naudoti COUNTA, nes tada galite užpildyti savo A2 etaloninį langelį arba skaičius ar tekstą. Dabar turėsite tokią lentelę:
Iš pradžių tai neatrodo naudinga, nes visos reikšmės yra tik 1. Tačiau, jei paslėpsime 3 eilutę, tas „1“ C3 pakeis į 0, nes jis rodo paslėptą eilutę. Nors neįmanoma turėti paveikslėlio, rodančio konkrečios paslėptos ląstelės vertę, galite jį patikrinti paslėpdami eilutę ir parašydami tokią pagrindinę formulę, kaip tai patikrinti.
| 1 | = C3 |
Dabar, kai turime stulpelį, kurio vertė keisis priklausomai nuo to, ar jis paslėptas, ar ne, esame pasirengę parašyti galutinę lygtį. Mūsų SUMIFS atrodys taip
Šioje formulėje sumosime tik B stulpelio reikšmes, kai A stulpelis yra „Apple“, ir C stulpelio vertė yra 1 (dar žinoma, eilutė nėra paslėpta). Tarkime, kad mūsų vartotojas nori išfiltruoti 600, nes jis atrodo neįprastai didelis. Matome, kad mūsų formulė duoda teisingą rezultatą.
Turėdami šią galimybę, galite pritaikyti čekį COUNTIFS, SUMIFS ar net SUMPRODUCT. Pridedate galimybę leisti savo vartotojams valdyti kai kuriuos stalo pjaustytuvus ir esate pasiruošę sukurti nuostabų prietaisų skydelį.
SUBTOTAL „Google“ skaičiuoklėse
Funkcija SUBTOTAL „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
PASKIRTI VBA pavyzdžiai
Taip pat VBA galite naudoti SUBTOTAL funkciją. Tipas:application.worksheetfunction.subtotal (function_num, reh1)
Vykdydami šiuos VBA teiginius
| 1234567891011121314151617 | Diapazonas ("C7") = Application.WorksheetFunction.Subtotal (1, Diapazonas ("C2: C5"))Diapazonas ("C8") = Application.WorksheetFunction.Subtotal (2, diapazonas ("C2: C5"))Diapazonas ("C9") = Application.WorksheetFunction.Subtotal (4, diapazonas ("C2: C5"))Diapazonas ("C10") = Application.WorksheetFunction.Subtotal (5, diapazonas ("C2: C5"))Diapazonas ("C11") = Application.WorksheetFunction.Subtotal (9, diapazonas ("C2: CE5"))Diapazonas ("D7") = Application.WorksheetFunction.Subtotal (1, Diapazonas ("D2: D5"))Diapazonas ("D8") = Application.WorksheetFunction.Subtotal (2, Diapazonas ("D2: D5"))Diapazonas ("D9") = Application.WorksheetFunction.Subtotal (4, Diapazonas ("D2: D5"))Diapazonas ("D10") = Application.WorksheetFunction.Subtotal (5, diapazonas ("D2: D5"))Diapazonas ("D11") = Application.WorksheetFunction.Subtotal (9, Diapazonas ("D2: D5"))Diapazonas ("E7") = Application.WorksheetFunction.Subtotal (1, Diapazonas ("E2: E5"))Diapazonas ("E8") = Application.WorksheetFunction.Subtotal (2, Diapazonas ("E2: E5"))Diapazonas ("E9") = Application.WorksheetFunction.Subtotal (4, Diapazonas ("E2: E5"))Diapazonas ("E10") = Application.WorksheetFunction.Subtotal (5, diapazonas ("E2: E5"))Diapazonas ("E11") = Application.WorksheetFunction.Subtotal (9, Diapazonas ("E2: E5")) |
duos šiuos rezultatus
Funkcijos argumentams (funkcijos_numeris ir kt.) Galite juos įvesti tiesiai į funkciją arba nustatyti kintamuosius, kuriuos naudoti.
Grįžkite į visų „Excel“ funkcijų sąrašą
