Subtotal basa muExcel. Formula, tafura zvinodiwa

Mhedzisiro yepakati inoda kuwanikwa paunenge uchigadzira mishumo inogona kuverengerwa zviri nyore muExcel. Pane sarudzo yakanakira iyi, yatichakurukura zvakadzama munyaya ino.

Zvinodiwa zvinoshanda kumatafura kuitira kuti uwane mhinduro dzepakati

Subtotal basa mukati Excel inokodzera chete kune mamwe marudzi ematafura. Gare gare muchikamu chino, iwe uchadzidza kuti ndeapi mamiriro anofanirwa kusangana kuti ushandise iyi sarudzo.

1. Iyo ndiro haifaniri kunge ine masero asina chinhu, rimwe nerimwe rawo rinofanira kunge rine ruzivo.
2. Musoro unofanira kuva mutsetse mumwe. Mukuwedzera, nzvimbo yaro inofanira kuva yakarurama: pasina kusvetuka uye masero anoputika.
3. Kugadzirwa kwemusoro kunofanira kuitwa zvakasimba mumutsara wepamusoro, kana zvisina kudaro basa racho haringashandi.
4. Tafura pachayo inofanira kumiririrwa nehuwandu hwehuwandu hwemasero, pasina mamwe matavi. Zvinoitika kuti dhizaini yetafura inofanirwa kunge iine rectangle.

Kana ukatsauka kubva pane chinenge chataurwa chinodiwa paunenge uchiedza kushandisa "Intermediate results" basa, zvikanganiso zvinoonekwa musero rakasarudzwa pakuverenga.

Iyo subtotal basa rinoshandiswa sei

Kuti uwane hutsika hunodiwa, unofanirwa kushandisa basa rinoenderana, iro riri pamusoro peMicrosoft Excel sheet pane panhizha yepamusoro.

1. Isu tinovhura tafura inosangana nezvinodiwa zvakataurwa pamusoro apa. Tevere, tinya pane tafura sero, kubva kwatichawana mhedzisiro yepakati. Wobva waenda kune "Data" tab, muchikamu che "Structure", tinya pa "Subtotal".

2. Muhwindo rinovhura, tinoda kusarudza imwe parameter, iyo ichapa mhedzisiro yepakati. Kuti uite izvi, mundima ye "Pakuchinja kwega kwega", unofanirwa kutsanangura mutengo pachikamu chechinhu. Naizvozvo, kukosha kunoiswa pasi "Price". Wobva wadzvanya bhatani rekuti "OK". Ndapota cherechedza kuti mundima ye "Operation", unofanira kuisa "Mari" kuitira kuti uverenge zvakarurama maitiro epakati.

3. Mushure mekudzvanya bhatani rekuti "Zvakanaka" patafura yehukoshi hwega hwega, subtotal icharatidzwa, inoratidzwa mumufananidzo uri pazasi.

Pane chinyorwa! Kana iwe wakatogamuchira huwandu hunodiwa kanopfuura kamwe, saka unofanirwa kutarisa bhokisi rakanzi "Tsvanza huwandu hwazvino". Muchiitiko ichi, data haidzokororwi.

Kana iwe ukaedza kudonhedza mitsara yese nechishandiso chakaiswa kuruboshwe kwendiro, uchaona kuti zvese zvepakati zvinosara. Ndidzo dzawakawana uchishandisa mirairo iri pamusoro.

Subtotals sefomula

Kuti usatarise chishandiso chinodiwa chekushanda mumatabo epaneru yekutonga, unofanirwa kushandisa sarudzo ye "Insert function". Ngationgororei nzira iyi zvakadzama.

1. Inovhura tafura yaunoda kutsvaga zviri pakati nepakati. Sarudza sero iyo yepakati kukosha icharatidzwa.

2. Wobva wadzvanya bhatani rekuti "Insert Function". Muhwindo rinovhura, sarudza chishandiso chinodiwa. Kuti tiite izvi, muchikamu che "Category", tiri kutsvaga chikamu che "Full alphabetical list". Zvadaro, mu "Sarudza basa" hwindo, tinya "SUB.TOTALS", tinya bhatani "OK".

3. Muhwindo rinotevera "Basa nharo" sarudza "Nhamba yebasa". Isu tinonyora pasi nhamba ye9, iyo inoenderana nesarudzo yekugadzirisa ruzivo yatinoda - kuverenga kwehuwandu.

4. Muchikamu chinotevera che data "Reference", sarudza nhamba yemasero aunoda kuwana madiki. Kuti urege kuisa data nemaoko, unogona kungosarudza huwandu hwemasero anodiwa ane cursor, wobva wadzvanya bhatani OK pahwindo.

Nekuda kweizvozvo, musero rakasarudzwa, tinowana mhedzisiro yepakati, iyo yakaenzana nehuwandu hwemasero atakasarudza neakanyorwa nhamba data.. Iwe unogona kushandisa basa racho usingashandisi "Function Wizard", nekuda kweizvi iwe unofanirwa kuisa manyore fomura: = SUBTOTALS(nhamba yekugadziriswa kwedata, maseru anoronga).

Teerera! Paunenge uchiedza kutsvaga maitiro epakati, mushandisi wega wega anofanira kusarudza sarudzo yake, iyo inozoratidzwa semhedzisiro. Inogona kunge isiri iyo nhamba chete, asiwo avhareji, mashoma, hukuru hunokosha.

Kushandisa Basa uye Kugadzirisa Masero Pamaoko

Iyi nzira inosanganisira kushandisa basa nenzira yakasiyana zvishoma. Kushandiswa kwayo kunoratidzwa mune algorithm pazasi:

1. Tanga Excel uye ita shuwa kuti tafura inoratidzwa nenzira kwayo papepa. Wobva wasarudza sero iyo yaunoda kuwana iyo yepakati kukosha kweiyo chaiyo kukosha mutafura. Wobva wadzvanya bhatani riri pasi pepaneru yekudzora "Isa basa".

2. Muhwindo rinoonekwa, sarudza chikamu "10 ichangobva kushandiswa mabasa" uye tsvaga "Yepakati mahota" pakati pavo. Kana pasina basa rakadaro, zvakakodzera kuti unyore imwe chikamu - "Full alphabetical list".

3. Mushure mekuonekwa kweimwe pop-up hwindo kwaunoda kunyora "Function Arguments", tinoisa ipapo data rese rakashandiswa munzira yapfuura. Muchiitiko chakadaro, mugumisiro we "Subtotals" kushanda kuchaitwa nenzira imwecheteyo.

Mune zvimwe zviitiko, kuvanza data rese, kunze kwekukosha kwepakati zvine chekuita nemhando imwe yekukosha muchitokisi, inobvumidzwa kushandisa chishandiso chekuviga data. Nekudaro, iwe unofanirwa kuve nechokwadi chekuti formula kodhi yakanyorwa nemazvo.

Kupfupisa

Subtotal calculations uchishandisa Excel spreadsheet algorithms inogona chete kuitwa uchishandisa chaiyo basa, asi inogona kuwanikwa nenzira dzakasiyana. Mamiriro makuru ndeokuita matanho ose kuitira kuti udzivise kukanganisa, uye kutarisa kana tafura yakasarudzwa inosangana nezvinodiwa zvinodiwa.