Maitiro Ekuverenga Nhamba Dzese muExcel

Pfupiso chinhu chakakosha pakuongorora data, kungave kuita masvomhu akareruka kana akaomarara. Panyaya yeExcel, iyi spreadsheet yakakurumbira inopa mabasa akasiyana-siyana uye mafomula anotibvumira kuita masvomhu nekukurumidza uye nemazvo. Muchikamu chino, tichaongorora maitiro ekuita sums muExcel, tichitsanangura nhanho nhanho nzira dzakasiyana uye maitiro aripo. Kubva pakuwedzera kwekuwedzera kune inomisikidzwa yekuwedzera uye nekushandisa epamberi mabasa, isu tichaona nzira yekuwana zvakanyanya kubva pane iyi ine simba math basa muExcel. Kana iwe uri kutsvaga kuwedzera ruzivo rwako mukugadzirisa maspredishiti uye nekugonesa ongororo yako yenhamba, chinyorwa ichi chinokupa ruzivo rwese rwunodiwa kuti uwedzere zvinobudirira muExcel.

1. Nhanganyaya yekuwedzera muExcel: pfungwa uye kushandiswa

Sums muExcel chishandiso chinobatsira kwazvo pakuita maverengero anokurumidza uye anoshanda mumaspredishiti. Muchinyorwa chino, isu tichaongorora iyo pfungwa uye mashandisirwo epfupiso muExcel, tichipa nhanho-ne-nhanho gwara remashandisiro azvo mumamiriro akasiyana.

Nhamba muExcel inokubvumira kuti uwedzere nekukurumidza ruzhinji rwemasero yakasarudzwa, pasina kuita nemaoko oparesheni yega yega yekuwedzera. Izvi zvinonyanya kubatsira kana uchishanda nemahombe manhamba data seti.

Kuti ushandise sums muExcel, ingosarudza huwandu hwemasero pauri iwe waunoda kuita sum uye shandisa iyo SUM basa mubhari yeformula. Iwe unogona zvakare kushandisa iyo AutoSum sarudzo, iri paKumba tebhu, kuti uwedzere otomatiki iyo SUM basa kusvika kumagumo kwekoramu yakasarudzwa kana mutsara. Neidzi nzira, iwe unozokwanisa kuita sums nekukurumidza uye nemazvo muExcel. Pamusoro pezvo, iwe unogona kushandisa masvomhu mafomula uye vashandisi kuita mamwe akaomarara kuverenga.

2. Basic mabasa ekuita mawedzero muExcel

Iwo maturusi anobatsira kwazvo anotitendera kuti tikurumidze uye nemazvo kuverenga huwandu hweseti yezvikoshi. Aya mabasa anoita kuti basa rive nyore paunenge uchiita masvomhu akaoma kunzwisisa, kudzivirira kukosha kwekuita mabasa aya nemaoko.

Pane mabasa akasiyana anogona kushandiswa kuita mawedzero muExcel, mamwe anonyanya kuzivikanwa anosanganisira SUM, SUMIF, uye SUMIF. Basa reSUM rinoshandiswa kuwedzera hunhu hwemunhu kana huwandu hwemasero. Kune rimwe divi, iyo SUMIF uye SUMIF SET mashandiro anoshandiswa kuwedzera kukosha zvichienderana nemamwe maitiro akatsanangurwa.

Kuti ushandise mabasa aya, iwe unofanirwa kuziva manyorerwo uye maitiro ekuisa iwo echokwadi nharo. Zvakakosha kuziva kuti nharo dzinoparadzaniswa nemakoma uye kuti dzimwe dzacho dzinodiwa. Kana tangosarudza sero kwatinoda kuti mhedzisiro yehuwandu hwedu ioneke, isu tichashandisa basa rinoenderana uye totaura mitsara kana maitiro atinoda kuwedzera.

3. Mienzaniso yekuwedzera uchishandisa iyo SUM basa muExcel

Basa reSUM muExcel chishandiso chine simba chinokutendera kuti uverenge huwandu hwenhevedzano yehunhu musero. kana huwandu hwemasero. Pazasi pane matatu.

Muenzaniso 1: Kuti upedze kukosha mune yakatarwa koramu, sarudza sero yaunoda kuti mhedzisiro ioneke. Tevere, isa iyo fomula "= SUM(renji)" ichitsiva "renji" nehuwandu hwemasero aunoda kuunganidza. Semuenzaniso, kana uine kukosha mumasero A1 kusvika kuA10, fomula yacho ingave "= SUM (A1: A10)". Pakupedzisira, dzvanya Enter uye iwe uchawana mhedzisiro yehuwandu.

Muenzaniso 2: Zvinokwanisika kuwedzera akawanda masero emasero mune imwe chete formula uchishandisa iyo SUM basa. Ingopatsanura mutsara wega wega necomma. Semuenzaniso, kana iwe uchida kuwedzera kukosha mumasero A1:A10 uye B1:B10, fomula yacho ingave "=SUM(A1:A10, B1:B10)." Izvi zvinokupa huwandu hwese hwematanho ese ari maviri.

Muenzaniso 3: Kana iwe uchida kuunganidza kukosha mumutsara pachinzvimbo chekoramu, ingoshandisa iyo SUM basa nenzira imwechete senge mumienzaniso yapfuura, asi sarudza yakachinjika sero renji pane yakatwasuka. Semuenzaniso, kana iwe uchida kuwedzera kukosha mumasero A1 kusvika E1, fomula yacho ingave "= SUM(A1: E1)". Izvi zvichaverengera huwandu hwehuwandu mumutsara wakatarwa.

4. Mashandisiro ekusarudza maitiro muhuwandu muExcel

Kuti tishandise maitiro ekusarudza muhuwandu muExcel, tinogona kushandisa iyo SUMIF basa kana iyo SUMIF SET basa. Aya mabasa anotitendera kuti tiite zvisungo zvekuwedzera, ndiko kuti, kuwedzera chete izvo zvakakosha zvinosangana nemamwe maitiro. Heino nzira yekushandisa nzira mbiri:

1. Kuti tishandise basa reSUMIF, tinofanira kutanga tasarudza sero apo tinoda kuti mugumisiro wehuwandu hwehuwandu huoneke. Zvadaro, tinofanira kuisa fomula "= SUMIF(criterion_range, criterion, sum_range)" mubhari yeformula, apo:

• criterion_range ndiwo huwandu hwemaseru umo hunhu hunofanirwa kuongororwa hunowanikwa.
• chiyero Ndiyo kukosha kana mamiriro ayo data inofanira kusangana kuti iwedzere.
• sum_range ndiwo mutsara wemaseru ane hunhu hunozowedzerwa kana vakasangana nematanho akatarwa.

2. Kana tichida kushandisa SUMIF SET basa, maitiro akafanana. Chekutanga, tinosarudza sero kwatinoda kuti mubairo wemuwandu uoneke tobva taisa fomula "=SUMIF.SET(criterion_range1, criterion1, criterion_range2, criterion2, sum_range)" mubhari yeformula. Musiyano chete ndewekuti isu tinogona kushandisa akawanda maitiro emhando uye maitiro.

5. Conditional sums muExcel: kushandisa iyo SUMIF basa

Basa reSUMIF muExcel rinobatsira zvakanyanya kana iwe uchida kuita zvekuwedzera papepa yekuverenga. Iri basa rinokutendera iwe kuti uwedzere kukosha kweiyo renji inosangana neimwe mamiriro. Nekushandisa iyo SUMIF basa, unogona kuwana chaiyo uye nekukurumidza mibairo paunenge uchiverenga muExcel.

Kuti ushandise iyo SUMIF basa muExcel, iwe unofanirwa kutanga wasarudza iyo data renji yaunoda kuita iyo inogadziriswa mari. Mamiriro acho anofanirwa kutsanangurwa uchishandisa maopareta akadai seanopfuura, mashoma pane, kana akaenzana, uye imwe chiyero kana kukosha kunogona kuverengerwa. Pamusoro pezvo, iyo SUMIF basa rinogona kushandiswa neanopfuura chimiro chimwe uchishandisa zvine musoro maopareta se AND kana OR.

Muenzaniso wekushandiswa kweSUMIF basa riri muspredishiti rine runyorwa rwekutengesa kwechigadzirwa. Ngatiti iwe unoda kuwana mari yese yekutengesa yechigadzirwa zvakananga. Uchishandisa iyo SUMIF basa, unogona kusarudza huwandu hwemitengo yekutengesa uye tsanangura mamiriro ekuti zita rechigadzirwa rakaenzana nechigadzirwa chaunoda. Mushure mekushandisa basa racho, iyo inogadziriswa mari yekutengeswa kwechigadzirwa chakasarudzwa ichawanikwa.

6. Zvinyorwa zvepamusoro muExcel: SUMPRODUCT uye SUMIF SET mabasa

MuExcel, mapfupi epamberi chishandiso chinobatsira kwazvo pakuita maverengero akaomarara. Maviri eanonyanya kushandiswa mabasa ndeye SUMPRODUCT uye SUMIFSET. Aya mabasa anobvumira kuverenga kunoenderana nemaitiro uye kuwanda kwemasero.

Basa reSUMPRODUCT rinokubvumira kuti uwedzere masero emasero uye kuwedzera migumisiro. Kuti ushandise basa iri, fomati inotevera inofanira kuteverwa: =SUMPRODUCT(range1, range2, …). Rudzi rwega rwega runofanira kuva nenhamba yakafanana yemitsara nemakoramu. Izvo zvakakosha kuti uzive kuti basa iri rinongowanza ma values ​​anoenderana nenzvimbo imwechete mune yega yega.

Kune rimwe divi, iyo SUMIF SET basa rinokutendera iwe kuti uwedzere kukosha kweiyo renji inosangana nezvakawanda maitiro. Maumbirwo echinhu ichi ndeaya anotevera: =SUMIFSET(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). Zvinokwanisika kuwedzera huwandu hwakawanda uye criterion pairs sezvinodiwa. Izvo zvakakosha kuti uzive kuti maitiro anogona kuve mataurirwo, manhamba kana zvinyorwa zvinofanirwa kuvharirwa mumakotesheni.

7. Maitiro ekuverengera mapfupi eakasefa siyana muExcel

Kuverengera huwandu hweakasefa marengeti muExcel inogona kuve basa rinobatsira kana iwe uchida kuwana chaiwo mhedzisiro kubva kune data set. Neraki, Excel inopa basa rinonzi "SUBTOTALS" rinoita kuti kuverenga uku kuve nyore. Pano isu tinokuratidza matanho ekuverenga huwandu hweakasefa mazinga muExcel.

Kutanga kwazvose, unofanira kusarudza iyo column kana huwandu hwe data yaunoda kuverengera. Tevere, enda kune "Data" tebhu muExcel menyu bar uye tinya "Sefa." Izvi zvinokutendera kuti kusefa iyo data zvichienderana neyako chaiyo maitiro. Kana wangoshandisa mafirita aunoda, data chete rinosangana neaya maitiro ndiro richaratidzwa.

Tevere, kuverenga huwandu hwe data yakasefa, enda kuchitokisi kwaunoda kuti mhedzisiro ioneke uye shandisa iyo "SUBTOTAL" basa. Iri basa rine nharo mbiri: nharo yekutanga nhamba yekushanda kwaunoda kuita (semuenzaniso, 9 yekuwedzera), uye yechipiri nharo ndeyehuwandu hwemasero aunoda kushandisa pairi. Semuyenzaniso, kana iwe uchida kupeta zvakasefa mukoramu A, gakava rinenge riri "= SUBTOTALS(9,A:A)". Izvi zvinoverengera huwandu hwe data yakasefa uye kuratidza mhedzisiro musero rakatarwa.

8. Kushandisa matrix formula kuita zvinyorwa zvakaoma muExcel

Chimwe chezvimiro zvine simba zveExcel kugona kuita zviwedzere zvakaoma uchishandisa array formula. Aya mafomula anotitendera kuti tiite maverengero epamusoro nemaseti edata mune imwechete fomula, kuchengetedza nguva uye kuderedza zvikanganiso. Pazasi pane nhanho-ne-nhanho gwara rekushandisa aya mafomula muExcel.

Usati washandisa matrix mafomula, zvakakosha kumisa iyo "Matrix Formulas" sarudzo muExcel. Kuti uite izvi, enda ku "Faira"> "Sarudzo"> "Formulas" uye tarisa bhokisi re "Matrix Formulas". Izvi zvinobvumira Excel kuita masvomhu nemazvo.

Kana iyo sarudzo yemafomula yaitwa, tinogona kutanga kushandisa aya mafomula kuita maomero ekuwedzera muExcel. Semuyenzaniso, ngatitii tine runyoro rwekutenga kwemwedzi nemwedzi kwezvigadzirwa zvakasiyana muspredishiti uye tinoda kuverenga huwandu hwese hwekutengesa kwemwedzi mitatu yapfuura. Tinogona kushandisa matrix formula =SUMA(SI(A2:A100=»Producto1″, B2:B100)) kuti uwane mhedzisiro yaunoda. Iyi fomula inongowedzera mutengo wekutengesa unoenderana ne "Chigadzirwa1".

9. Pfupiso muExcel pivot matafura: kuwedzera uye kupfupisa data

Kuwedzera uye kupfupisa data muExcel pivot matafura, unogona kushandisa iyo kuita basa. Huwandu hunokutendera iwe kuti uwane iyo yakazara huwandu hweiyo kukosha mune yakatarwa koramu kana mutsara mune yako pivot tafura. Izvi zvinonyanya kubatsira kana iwe uchida kuita masvomhu nekukurumidza uye kuwana zvakaunganidzwa mhinduro.

Kuti uwedzere mari patafura yepivot, tevera matanho aya:

• Sarudza sero paunoda kuti mari ioneke.
• Enda kune "PivotTable Zvishandiso" tebhu muExcel menyu bar.
• Dzvanya "Wedzera Munda" uye sarudza koramu kana mutsara waunoda kuwedzera.
• Ita shuwa kuti basa rekubatanidza rakaiswa ku "Sum."

Kana uchinge wawedzera huwandu, Excel inozoverenga otomatiki huwandu hwemakoshi anoenderana mukoramu yakasarudzwa kana mutsara. Kana iwe uchida kupfupisa iyo data nenzira yakadzama, unogona kushandisa kusefa uye nemapoka sarudzo mutafura yako yepivot. Izvi zvinokutendera iwe kutonga kwakawanda pamusoro pekuti data rinoratidzwa sei uye kupfupikiswa mukuongorora kwako.

10. Maitiro ekuwedzera akasiyana kukosha muExcel uchishandisa iyo SUMIF SET basa

MuExcel, iyo SUMIF SET basa chishandiso chinobatsira kwazvo chekuwedzera yakasarudzika tsika mukoramu.. Iri basa rinotibvumira kuwedzera kukosha kwekoramu kana mamwe mamiriro akazadzikiswa. Kushandisa basa iri kunonyanya kubatsira kana tikashanda nemaseti makuru edata uye tichida kuwana mari dzakatarwa zvichienderana nedzimwe nzira.

Kuti tishandise iyo SUMIF SET basa, isu tinofanira kutanga tatsanangura huwandu hwehuwandu hwekuongorora uye mamiriro kana chiyero chinofanira kusangana. Tevere, isu tinosarudza huwandu hwehuwandu hunozowedzerwa kana mamiriro asangana. Izvo zvakakosha kuti uyeuke kuti basa racho rinongoita summary kukosha izvo zvakasarudzika mukati meiyo data renji.

Semuyenzaniso, ngatitii tine koramu ine mazita echigadzirwa uye imwe koramu ine huwandu hwakatengeswa chigadzirwa chimwe nechimwe. Kana isu tichida kuwedzera huwandu hwakatengeswa hwechimwe chigadzirwa, tinogona kushandisa iyo SUMIF SET basa. Kuti tiite izvi, tinotsanangura huwandu hwemazita echigadzirwa sechiyero uye huwandu hwehuwandu hunotengeswa sehuwandu hwemari. Nenzira iyi, tichawana huwandu hwehuwandu hunotengeswa hwechigadzirwa chakasarudzwa.

11. Kushandisa Solver tool kuita mawedzero pasi pezvirambidzo muExcel

Iyo Solver chishandiso muExcel ibasa rinokutendera kuti uite zvekuwedzera pasi pezvirambidzo, zvinonyanya kubatsira kugadzirisa matambudziko optimization. Solver inoshandisa masvomhu algorithms kuti iwane mhinduro yakakwana kudambudziko, ichifunga nezvezvipingaidzo zvakamiswa.

Kuti ushandise Solver, unofanirwa kuve uine vhezheni yeExcel iyo inosanganisira iyo (kazhinji inyanzvi kana bhizinesi shanduro). Kana tave nekuwana Solver, tinofanira kutevedzera zvinotevera:

• Sarudza chisero chatiri kuda kukwenenzvera.
• Tsanangura masero anochinja achave ari pasi pezvirambidzo uye izvo zvinokanganisa mhedzisiro yerongedzo sero.
• Gadzira zvipingamupinyi, zvese zvipingamupinyi zvekuenzana uye zvipingaidzo zvekusaenzana.

Kana dambudziko ragadziriswa, tinogona kumhanya Solver kuti tiwane mhinduro. Excel inowana iwo akanyanya kukosha kune akasiyana maseru, achifunga nezve akatsanangurwa zvipingaidzo, uye ratidza mhedzisiro mune yakanangana sero. Uye zvakare, Solver inogona kuburitsa yakadzama rondedzero ine ruzivo nezve mhinduro yakawanikwa.

12. Nzira yekuwedzera sei-non-contiguous masero muExcel

Kuti uwedzere masero asina-contiguous muExcel, kune nzira dzakasiyana dzaungashandisa zvichienderana nezvaunoda uye rudzi rwe data rauri kubata. Pazasi, tinopa mamwe matanho uye mienzaniso ichakubatsira kugadzirisa dambudziko iri:

1. ":"" nzira yevashandisi (colon): Nzira iri nyore yekuita uwandu hwemasero asina kubatana ndeyekushandisa colon operator. Semuenzaniso, kana iwe uchida kuwedzera maseru B1, C1, F1, uye G1, ingonyora fomura rinotevera musero risina chinhu: =SUM(B1:C1, F1:G1). Iyi fomula ichawedzera zviri mukati memasero mana otomatiki.

• Zano: Shandisa "," (comma) opareta kuparadzanisa maseru akasiyana.
• Muenzaniso: =SUMA(A1:A5, C1:D5, F1:G5)

2. Nzira yekushandisa iyo SUMIF SET basa: Imwe nzira ndeyokushandisa SUMIF SET basa, iyo inokubvumira kuwedzera seti yemasero asina kuwirirana anosangana nemamwe maitiro. Semuyenzaniso, kana uchida kuwedzera maseru ese ari muchikamu A akakura kupfuura gumi uye asingasviki makumi maviri, unogona kushandisa formula inotevera: =SUMIFSET(A10:A20, «>1», A10:A10, « <1 "). Formula iyi ichawedzera maseru ese anosangana nemamiriro aya.

• Zano: Unogona kushandisa kuenzanisa mashandiro akasiyana, akadai se«>» (zvikuru pane), «<" (zvishoma pane), ">=» (zvikuru pane kana zvakaenzana ne), «<=" (zvishoma pane kana kuenzana), pakati vamwe.
• Muenzaniso: =SUMIFSET(A1:A10, «>=50», B1:B10, «<=100")

3. Nzira yekushandisa SUMPRODUCT basa: Basa reSUMPRODUCT rinobatsirawo pakuwedzera masero asina kukwana muExcel. Iri basa rinokutendera kuti uite masvomhu mashandiro pakati pezvikamu zvakasiyana zvemasero wobva wawedzera mhedzisiro. Somuenzaniso, kana uchida kuwedzera masero B1, C1, F1 uye G1 akapetwa ne2, unogona kushandisa fomula inotevera: = SUMPRODUCT((B1:C1, F1:G1)*2). Iyi fomula ichawedzera maseru ne2 uye wobva wawedzera mhinduro.

• Zano: Iwe unogona kushandisa akasiyana masvomhu maitiro, akadai sekuwanza (*), kupatsanura (/), kuwedzera (+), kubvisa (-), pakati pevamwe.
• Muenzaniso: =SUMARPRODUCTO((A1:A5*2) + (B1:B5/2))

13. Chengetedza nguva: Kuzadzisa otomatiki kwehuwandu muExcel nemapfupi ekhibhodi

Kune avo vari kutsvaga kuchengetedza nguva vachiita zvekuwedzera muExcel, kupedzisa nekhibhodi mapfupi chinhu chinobatsira kwazvo. Panzvimbo yekuti unyore nemaoko sero rega kana kushandisa iyo SUM basa, unogona kushandisa mapfupi ekhibhodi kukurumidza kupedzisa huwandu mumutsara kana mutsetse.

Danho rekutanga nderekusarudza sero paunoda kuti mhedzisiro yehuwandu ioneke. Wobva wadzvanya kiyi ye "Alt" uye, uchiibata, dzvanya chiratidzo chakaenzana (=) kuti uite autocomplete. Tevere, nyora muchidimbu formula, semuenzaniso, «= SUM(«.

Zvino, sarudza huwandu hwemasero aunoda kuwedzera uye wodzvanya kiyi ye "Enter" kuti uwane mhedzisiro yehuwandu. Kana iwe uchida kukopa fomula kune mamwe maseru, ingosarudza sero ine mhedzisiro, tinya "Ctrl+C" makiyi kuti uikope uye wosarudza masero ekuenda uye wodzvanya "Ctrl+V" makiyi kuti unamate fomula. Nenzira iyi, unogona kuchengetedza nguva paunenge uchiita zvekuwedzera muExcel uchishandisa mapfupi ekhibhodi.

14. Mazano uye mazano ekuvandudza kushanda kana uchiita sums muExcel

Kuti uvandudze mashandiro paunenge uchiita mawedzero muExcel, pane akatevedzana e mazano nemazano izvo zvinogona kubatsira zvakanyanya. Zvimwe zvacho zvichatsanangurwa pazasi:

1. Shandisa mafomula anoshanda: Zvakakosha kushandisa mafomula akakodzera kuita sums. Semuenzaniso, pachinzvimbo chekushandisa iyo SUM formula, zviri nani kushandisa chaiwo mabasa akadai SUMIF kana SUMIFSET, izvo zvinoderedza kuverenga nguva.

2. Dzivisa zviverengero zvisina basa: Kana iwe uine data risingakodzeri kuhuwandu, zvinokurudzirwa kudzivirira kuisanganisira mumaseru ari kuwedzerwa. Izvi Zvinogona kuwanikwa kushandisa mafirita kana maconditionals kusabvisa yakataurwa data uye nekudaro mhanyisa maitiro ekuverenga.

3. Shandisa mazita ane mazita: Paunenge uchishanda nemaseti makuru e data, zvinogona kubatsira kugovera mazita kumatanho achawedzerwa. Izvi zvinogonesa zvakanyanya basa rekuita mawedzero, sezvo iwe uchigona kunongedzera mazita ane mazita pachinzvimbo chekusarudza nemaoko maseru aunoda kuwedzera.

Muchidimbu, Excel mapfupi chishandiso chakakosha kune avo vanoshanda nemavhoriyamu makuru e data uye vanoda kuwana nekukurumidza uye kwakaringana mhinduro. Kuburikidza nemafomula akareruka uye mabasa chaiwo, akadai seSUM neSUMIF, zvinokwanisika kuita masvomhu akaomarara pasina matambudziko.

Nekudaro, zvakakosha kuti titarise zvimwe zvakakosha kuti tidzivise kukanganisa mukuverenga kwedu. Kusarudzwa kwakaringana kwezvikamu, kushandiswa kwakakodzera kwemaitiro mumabasa ehuwandu hwemamiriro ezvinhu uye kugarosimbisa mhedzisiro maitiro akakosha.

Excel inotipa akawanda sarudzo uye maturusi anotibvumira kukwidziridza maitiro edu ekuita uye kurerutsa mabasa edu ekuongorora data. Kubva pakushandiswa kwematafura epivot kusvika pakuita otomatiki kwemabasa uchishandisa macros, chikuva chinomira semubatsiri ane simba kune avo vari kutsvaga kunyatsoita uye nemazvo.

Chekupedzisira, nekubatanidzwa kwakaringana kweruzivo rwehunyanzvi uye kugara tichidzidzira, isu tichakwanisa kuita zvakanyanya mapfupi muExcel uye tine simba rekuverenga uye rekuongorora chishandiso mumaoko edu.

Usazeze kuenderera mberi uchidzidzira uye kudzidza nezve epamberi maficha eExcel kutora hunyanzvi hwako kune inotevera nhanho! Master mapfupi uye shamisa nekuongorora kwako data.