Nzira yekuverenga nhamba yemasero uye kuverenga mavara ari muchitokisi muExcel?

Verenga manhamba muchitokisi uye uverenge masero muExcel

MS Excel chishandiso chakanaka chekuronga uye accounting zvinhu uye chinogona kushandiswa chero level. Izvo hazvidi ruzivo rwakawanda, nekuti chishandiso chiri kuita kuti zvese zviratike zvive nyore. Unogona kuishandisa pakuda kwako wega, uine masimba ane basa senge advanced Vlookup muExcel, tsvaga chinzvimbo chemunhu tambo, kana zvakare kuverenga nhamba yezvirevo zvetambo mune faira.

Icho chikamu chakanakisa chekushandisa iyo MS Excel ndechekuti ine zvakawanda zvezvinhu uye saka iwe unogona kuverenga matokisi uye kuverenga mavara muchitokisi. Kuverenga masero man'a hachisiri chinhu chakagadzikana.

Maitiro ekuverenga nhamba yemasero muExcel

Kutanga, tinotanga nekuverenga masero. Nekuti iyo MS Excel ine huwandu hwakawanda hwekuverenga maitiro, hapana chikonzero chekuti iwe unetseke kana chiri chechokwadi kana chisina kunaka.

# 1 - Dzvanya kuruboshwe pasero pasisina chinhu, kwaunoda kuti mhedzisiro ivele. Kazhinji, izvi zvinoitika paseri kurudyi mushure memutsara kana muzasi pane maseru apedzwa.

# 2 - Iyo fomula tab iri kumusoro kwemasero. Nekuti kutsvaga iwo mabasa kunogona kuve kwakaoma, unogona kungonyora = COUNTA pane iyo fomura tab. Mushure mezwi rinoti '= COUNTA', iwe uchavhura mamwe anotenderera mabereki uye unyore nhamba yemasero kwaunoda kuwana mhedzisiro (i.eC1, C2, C3 nezvimwewo).

Maitiro ekuverenga nhamba yemasero: = COUNTA (C1, C2, C3)

Kana iwe uchida kuverenga masero nemaitiro chaiwo (semuenzaniso nhamba dzakateedzana), iwe unoshandisa basa reCLIF. Iyo inoshanda nenzira imwecheteyo seyakaita seGoogle, asi iwe unofanirwa kuchinja izvo zviri pakati pekutenderera kwemaberekesi.

Sbg Iwe uri kutsvaga nhamba dzakafanana nenhamba iri muC19 cell. Mune iyo tafura yewebhu, iwe unonyora: (C1: C2, C19).

Maitiro ekuverenga nhamba yemasero muExcel coup maitiro: = COUNTIF (C1: C99, X)

Zvakare, pane basa reSEEMIFS iro rinoshanda nemaitiro akawanda.

Maitiro ekuverenga nhamba yemacelles muExcel anoenderana maitiro mazhinji: = NYAYA C1: C2, C19, C24: C32, B21)

Maitiro Ekuverenga Masero MuBudiriro?

Pane nzira nhatu dzakareruka dzekuvhura mitsara mukurongedza kuitira kuti urege.

1. Iyo yekutanga uye iri nyore nzira yekuzvimiririra mitsara mitsara mune exprel SpeetSheet.

Iwe uchafanirwa kupinda pamunhu nhamba mbiri dzekutanga - uye izvi hazvifanirwe kunge zviri 1 uye 2 . Iwe unogona kutanga kuverenga kubva kune chero nhamba. Sarudza masero neiyo lMB uye fambisa iyo kiroti pamusoro pekona yenzvimbo yakasarudzwa. Museve unofanirwa kushandura kune mutema muchinjiro.

Kana iyo curnor ichishanduka kuita muchinjiko, tinya bhatani rekuruboshwe rebushi zvakare uye dhonza kusarudzwa kwenzvimbo iyo yaunoda kuverenga mitsara kana makoramu. Iyo yakasarudzwa renji ichazadzwa nezviitiko zvekuwedzera mukuwedzera, sezviri pakati pehuwandu hwekutanga.

2. Nzira yekuvhura mitsetse uchishandisa formula.

Iwe unogona zvakare kugovera nhamba kumutsara wega wega kuburikidza neiyo yakakosha formula. Sero rekutanga rinofanira kunge riine mbeu. Dzivirira uye uenderere mberi kuchitokisi chinotevera. Zvino isu tinoda basa rinozowedzera imwe (kana imwe yakakodzera nhamba yekuverenga) kune imwe neimwe inotevera kukosha. Zvinotaridzika seizvi:

"= [Sero Nekutanga Kukosha] + 1"

Kwatiri, izvi = A1 + 1. Kuti ugadzire formula, iwe unogona kushandisa iyo Sum bhatani kubva kune yepamusoro menyu - ichaonekwa paunoisa iyo = chiratidzo muchitokisi.

Isu tinodzvanya pamuchinjiko mukona yechitokisi chine formula uye sarudza iyo huwandu kuzadza iyo data. Mitsetse yakaverengerwa otomatiki. 3. Row kuverenga muExcel uchishandisa kufambira mberi.

Iwe unogona zvakare kuwedzera mucherechedzo muchimiro chenhamba uchishandisa kufambira mberi kwebasa - Iyi nzira yakakodzera kushanda nevazhinji zvinyorwa kana tafura iri refu uye ine data yakawanda.

• Muchitokisi chekutanga - isu tine iyo A1 - iwe unofanirwa kuisa nhamba yekutanga.
• Tevere, sarudza iyo inodiwa renji, tora chitokisi chekutanga.
• Mu Imba tab, iwe unofanirwa kuwana iyo zadza basa.
• Wobva wasarudza kufambira mberi. Iyo default parameter yakakodzera kuverengera: Type - Arithmetic, nhanho = 1.
• Dzvanya OK uye iyo yakasarudzwa range ichashanduka mune yakaverengerwa rondedzero.

Maitiro ekuverenga mavara mune muchitokisi muExcel

Kuti uverenge mavara mune imwe sero muExcel, kana kuverenga manhamba museru zvekare, zvese zvaunofanirwa kuita kushandisa basa rimwe chete.

Verenga manhamba muchitokisi: = LEN (C1)

Maitiro ekuverenga mavara mune akawanda masero muExcel

Kuverenga mavara mumasero akati wandei kubva mune zvinyorwa zvakare zvinonakidza uye kwete zvakaoma sekureva kwavangataurwe nevamwe vanhu. Iwe chete unofanirwa kuziva uye kushandisa mamwe mabasa. Zvirinani kuverenga kunogadzirwa nemushini.

Iva excel Pro: Joinha kosi yedu!

Simudza hunyanzvi hwako kubva kuLovice kuenda kuHamba NeGamba Pedu 365 Basics Course, rakagadzirirwa kukuita iwe nyanzvi mune zvikamu zvishoma.

Nyoresa pano

Iko kune maviri mabasa anoshanda eakanaka munhu kuverenga - SUMPRODUCT uye LEN.

# 1 - Iwe unosarudza iyo nhamba yemasero ane iwo mavara iwe aunoda kuverengerwa.

C1, C2, C3, C4 uye C5.

# 2 - Mune iyo fomura tebhu iwe unotanga kunyora iyo SUMPRODUCT basa uye mushure meLEN basa. Nguva dzose mune iyi yekuraira.

Verenga manhamba mumasero: = SUMPRODUCT (LEN (B1: B5))

Ramba uchifunga kuti hapana chikonzero chekuti udzvanyirire space bar pakati pevabereki nemazwi auri kunyora kana zvasvika nezve mabasa.

Zvakare, iwo madhesi maviri anomiririra mutsara: kubva…. ku…. . Kwedu isu: kubva B1 kusvika B5.

Ehe, kune mamwe akawanda mabasa asi uyu ndeaanoshanda nemazvo uye ari nyore kushandisa.

Maverengero ekuverenga hunhu occurence mumasero

Kana iwe uchida kuverenga chaiyo yakatarwa mune yako mavara, iwo mabasa auchazoda kushandisa ndeaya:

SUMPRODUCT, SUBSTITUTE, LEN

Ngatiti iwe unoda kuwona kuti kangani tsamba iyo iri mune yakaderera inooneka mune yako mavara. Uye zvinyorwa zvako zvakaiswa mumasero B2 kusvika B9.

Nzira yauchashandisa nayo mabasa ichave:

Excel kuverenga hunhu kunoitika mumhando: = SUMPRODUCT (LEN (B2: B9) -LEN (SUBSTITUTE (B2: B9, "a", ""))

Kushandisa MS Excel kuita kuverenga manhamba muchitokisi kana kuverenga mavara

Ramba uchifunga kuti pese paunoshandisa basa reMS Excel, iyo yakakomberedza mabereki inofanirwa. Pasina ivo, iyo software yekushandisa haingaiti basa rayo.

Enda zvakare netambo tambo nekushandisa masimba esimba seye advanced Vlookup muExcel, tsvaga chinzvimbo chemunhu tambo, kana zvakare kuverenga nhamba yezvirevo zvetambo mune faira.

Nzira yekuverenga masero ane mavara muExcel uchishandisa fomula?

Kuti uverenge maseru ane mavara muExcel uchishandisa fomula, iwe unofanirwa kuve nekoramu ine huwandu.

Wedzera pazasi basa pane ino data renji, shandura masero kubva kuC2: C9 kuenda kune yako masero, uye shandisa firita pane yemuvara basa.

Mhedzisiro ichave iyo huwandu hwemasero emavara muExcel akaverengerwa uchishandisa fomula.

Maitiro ekuverenga masero ane mavara muExcel uchishandisa fomula: = SUBTOTAL (102, C2: C9)

Mibvunzo Inowanzo Bvunzwa

Ndeapi mafomu kana mabasa anogona kushandiswa muExcel kuti uone huwandu hwehuwandu hwemasero mune huwandu hune data uye kuverenga mavara mukati mechitokisi chaicho?

Kuti uverenge nhamba yemasero asina-asina chinhu mune renji, shandisa iyo `Counta (huwandu )` basa. Kuti uverenge nhamba yemavara muchitokisi, kusanganisira nzvimbo, shandisa iyo `len (cell_reference)` basa. Izvi mabasa anopa nzira dziri nyore dzekuongorora cell zvemukati uye urefu hwe data mukati meExcel Spuraseetsheetsheetsheetsheetsheetsheets

Zadzisa 2019 Excel yeVatanga mune vhidhiyo