TOP 15 mafomula muExcel

Excel ndiyo imwe yeanonyanya kukosha zvirongwa. Zvakaita kuti hupenyu hwevashandisi vakawanda huve nyore. Excel inobvumidza iwe kuti uite otomatiki kunyange yakanyanya kuoma kuverenga, uye iyi ndiyo mukana mukuru wechirongwa ichi.

Sezvo mutemo, mushandisi akajairwa anoshandisa chete akaganhurirwa seti yemabasa, nepo kune akawanda mafomula anobvumidza iwe kuita iwo mamwe mabasa, asi nekukurumidza zvakanyanya.

Izvi zvinogona kubatsira kana iwe uchigara uchiita zviito zvakawanda zvemhando imwechete inoda huwandu hukuru hwekushanda.

Yakave inofadza? Wobva wagashira kuwongororo yeanonyanya kubatsira 15 Excel mafomula.

Mamwe mazwi

Usati watanga kuongorora mabasa acho zvakananga, unofanirwa kunzwisisa kuti chii. Iyi pfungwa inoreva fomula yakaiswa nevagadziri, zvichienderana nekuti maverengero anoitwa uye imwe mhedzisiro inowanikwa pakubuda. 

Basa rega rega rine zvikamu zviviri zvikuru: zita uye nharo. Formula inogona kuve nebasa rimwechete kana akati wandei. Kuti utange kuinyora, unofanirwa kudzvanya kaviri pane sero inodiwa uye nyora chiratidzo chakaenzana.

Chikamu chinotevera chebasa izita. Chaizvoizvo, izita reformula, iyo inozobatsira Excel kunzwisisa izvo mushandisi anoda. Inoteverwa nenharo dziri mumaparenthesis. Aya ndiwo maparamendi ebasa anotariswa kuita mamwe mabasa. Kune akati wandei marudzi enharo: manhamba, zvinyorwa, zvine musoro. Zvakare, pachinzvimbo chazvo, zvinongedzo kumaseru kana kuti zvakati siyana zvinowanzoshandiswa. Nharo imwe neimwe inoparadzaniswa kubva kune imwe nesemicolon.

Syntax ndiyo imwe yepfungwa huru dzinoratidza basa. Iri izwi rinoreva template yekuisa mamwe maitiro kuitira kuti basa rishande.

Uye zvino ngatitarisei zvese izvi mukuita.

Formula 1: VLOOKUP

Iri basa rinoita kuti zvikwanise kuwana ruzivo rwunodiwa mutafura, uye kuratidza mhedzisiro yakadzoserwa mune chaiyo sero. Chidimburiso chezita rebasa chinomirira "Vertical View".

Syntax

Iyi ndiyo nzira yakaoma yakaoma ine nharo ina, uye kushandiswa kwayo kune zvakawanda.

Iyo syntax ndeiyi:

=VLOOKUP(kutarisisa_kukosha, tafura, column_nhamba, [range_lookup])

Ngatitarisei zvakadzama pamakakatanwa ese:

1. Kukosha kwekutarisa kumusoro.
2. Tafura. Izvo zvinodikanwa kuti pane kukosha kwekutarisa muchikamu chekutanga, pamwe nemutengo unodzoserwa. Iyo yekupedzisira inowanikwa chero kupi. Mushandisi anogona kuzvimiririra kusarudza kwaanoisa mhedzisiro yeformula. 
3. Column nhamba.
4. Interval viewing. Kana izvi zvisingakoshi, saka iwe unogona kusiya kukosha kweiyi nharo. Chirevo che boolean chinoratidza mwero wekurongeka kwemutambo unofanirwa kuwana. Kana iyo parameter "Chokwadi" ichitsanangurwa, ipapo Excel ichatsvaga kukosha kwepedyo kune iyo inotsanangurwa seyo yekutsvaga kukosha. Kana iyo "Nhema" paramende yakatsanangurwa, saka basa rinotsvaga chete kune izvo zvakakosha zviri mukoramu yekutanga.

Mune ino skrini, tiri kuyedza kufunga kuti mangani maonero akagadzirwa kumubvunzo "tenga piritsi" uchishandisa fomula.

Formula 2: Kana

Iri basa rinodikanwa kana mushandisi achida kuseta imwe mamiriro pasi peiyo kukosha kunofanirwa kuverengerwa kana kubuda. Inogona kutora nzira mbiri: chokwadi nenhema.

Syntax

Iyo formula yebasa iri ine nharo huru nhatu, uye inoita seizvi:

=IF(zvinonzwisisika_kutaura, “value_if_true”, “value_if_false”).

Pano, chirevo chine musoro chinoreva fomula inotsanangudza mucherechedzo. Nerubatsiro rwayo, iyo data ichaongororwa kuti inoenderana neimwe mamiriro. Saizvozvo, iyo "value kana inhema" nharo inoitirwa basa rimwechete, nemusiyano chete uri wekuti igirazi rakapesana nezvarinoreva. Mumashoko akajeka, kana mamiriro acho asina kusimbiswa, ipapo purogiramu inoita zvimwe zviito.

Pane imwe nzira yekushandisa basa IF - nested mabasa. Panogona kuve neakawanda mamwe mamiriro pano, anosvika makumi matanhatu nemana. Kana sero A64 yakaenzana nembiri, saka unofanirwa kuratidza kukosha "Hongu". Kana iine kukosha kwakasiyana, saka unofanirwa kutarisa kana sero D2 yakaenzana nembiri. Kana hongu, saka iwe unofanirwa kudzorera kukosha "kwete", kana pano mamiriro acho akashanduka kuva enhema, saka fomura inofanira kudzorera kukosha "zvichida".

Hazvikurudzirwe kushandisa nested mabasa kazhinji, nekuti iwo akaomarara kushandisa, zvikanganiso zvinogoneka. Uye zvichatora nguva yakareba kuti zvigadziriswe. 

basa IF inogona zvakare kushandiswa kuona kana imwe sero isina chinhu. Kuzadzisa chinangwa ichi, rimwe basa rinofanirwa kushandiswa - ISBLANK.

Heino syntax inoti:

=IF(ISBLANK(sero nhamba),"Chisina chinhu","Isina chinhu").

Mukuwedzera, zvinokwanisika kushandisa panzvimbo yebasa ISBLANK shandisa iyo yakajairwa fomula, asi tsanangura kuti uchifungidzira kuti hapana kukosha muchitokisi.

IF - iri nderimwe reanowanzo mashandiro ari nyore kushandisa uye rinokutendera kuti unzwisise kuti ichokwadi sei humwe hunhu, tora mibairo yeakasiyana maitiro, uye zvakare kuona kana imwe sero isina chinhu.

Iri basa ndiro hwaro hwemamwe mafomula. Iye zvino tichaongorora mamwe acho zvakadzama.

Formula 3: SUMIF

basa SUMMESLI inokubvumira kupfupisa iyo data, zvichienderana nekutevedzera kwavo mamwe maitiro.

Syntax

Basa iri, serakapfuura, rine nharo nhatu. Kuti uishandise, unofanirwa kunyora fomula yakadaro, uchitsiva hunhu hunodiwa munzvimbo dzakakodzera.

=SUMIF(renji, mamiriro, [sum_range])

Ngatinzwisise zvakadzama kuti imwe neimwe yenharo chii:

1. Condition. Iyi nharo inobvumira iwe kupfuudza maseru kune basa, ayo ari zvakare pasi pekuita.
2. Summation range. Iyi nharo ndeyekusarudza uye inokutendera kuti utaure maseru ekuunganidza kana mamiriro ari emanyepo.

Saka, mumamiriro ezvinhu aya, Excel yakapfupisa data pamibvunzo iyo iyo nhamba yeshanduko inodarika 100000.

Formula 4: SUMMESLIMN

Kana pane akati wandei mamiriro, ipapo basa rakabatana rinoshandiswa SUMMESLIMN.

Syntax

Formula yebasa iri inoita seizvi:

=SUMIFS(summation_range, condition_range1, condition1, [condition_range2, condition2], …)

Yechipiri neyechitatu nharo dzinodiwa, dzinoti "Range yemamiriro 1" uye "Range yemamiriro 1".

Formula 5: COUNTIF uye COUNTIFS

Iri basa rinoedza kuona huwandu hwemasero asina kuvharwa anofanana nemamiriro akapihwa mukati mechikamu chakapindwa nemushandisi.

Syntax

Kuti upinde basa iri, unofanirwa kutsanangura iyo inotevera fomula:

= COUNTIF (renji, maitiro)

Nharo dzakapihwa dzinorevei?

1. A range is a set of cells pakati payo panofanira kuverengerwa.
2. Criteria - mamiriro akatariswa pakusarudza masero.

Semuenzaniso, mumuenzaniso uyu, purogiramu yakaverenga nhamba yemibvunzo inokosha, apo nhamba yekudzvanya mumajini ekutsvaga inodarika zana rezviuru. Nekuda kweizvozvo, iyo formula yakadzosa nhamba 3, zvinoreva kuti kune matatu mazwi akadaro.

Kutaura nezvebasa rakabatana COUNTIFS, zvino iyo, yakafanana nemuenzaniso wekare, inopa kukwanisa kushandisa maitiro akawanda panguva imwe chete. Formula yaro ndeiyi inotevera:

=COUNTIFS(condition_range1, condition1, [condition_range2, condition2],...)

Uye zvakafanana neyakapfuura, "Condition Range 1" uye "Condition 1" inodiwa nharo, nepo dzimwe dzinogona kusiiwa kana pasina chakadaro. Iyo yakanyanya basa inopa kugona kunyorera kusvika ku127 renji pamwe nemamiriro.

Formula 6: IFERROR

Iri basa rinodzosa kukosha-kwakatsanangurwa nemushandisi kana chikanganiso chasangana pauri kuongorora fomula. Kana kukosha kwacho kuri kwechokwadi, anoisiya.

Syntax

Basa iri rine nharo mbiri. Syntax ndiyo inotevera:

=IFERROR(value;value_if_error)

Tsananguro yenharo

1. Kukosha ndiyo formula pachayo, yakatariswa tsikidzi.
2. Kukosha kana kukanganisa kuri mhedzisiro inooneka mushure mekunge kukanganisa kwaonekwa.

Kana tikataura nezvemienzaniso, saka iyi fomula icharatidza rugwaro "Kukanganisa pakuverenga" kana kupatsanura kusingakwanisi.

Formula 7: LEFT

Basa iri rinoita kuti zvikwanise kusarudza nhamba inodiwa yemavara kubva kuruboshwe rwetambo.

Syntax yaro ndeiyi inotevera:

=KURUboshwe(mavara,[nhamba_machars])

Nharo dzinobvira:

1. Chinyorwa - tambo kubva kwaunoda kuwana chaiyo chidimbu.
2. Kuwanda kwemavara ndiko kwakananga nhamba yemavara achatorwa.

Saka, mumuenzaniso uyu, unogona kuona kuti basa iri rinoshandiswa sei kuona kuti mazita emapeji esaiti achataridzika sei. Ndiko kuti, kana tambo ichakwana mune imwe nhamba yemavara kana kwete.

Formula 8: PSTR

Iri basa rinoita kuti zvikwanise kuwana nhamba inodiwa yemavara kubva muzvinyorwa, kutanga neimwe hunhu muakaundi.

Syntax yaro ndeiyi inotevera:

=MID(zvinyorwa,kutanga_nzvimbo,nhamba_ye_mavara).

Kuwedzera nharo:

1. Chinyorwa itambo ine data inodiwa.
2. Nzvimbo yekutangira ndiyo yakananga nzvimbo yemunhu iyeye, iyo inoshanda sekutanga kwekuburitsa chinyorwa.
3. Nhamba yemavara - nhamba yemavara ayo fomula inofanira kutorwa kubva muzvinyorwa.

Mukuita, basa iri rinogona kushandiswa, semuenzaniso, kurerutsa mazita emazita nekubvisa mazwi ari pakutanga.

Formula 9: PROPISN

Iri basa rinoisa mavara makuru ese ari mune imwe tambo. Syntax yaro ndeiyi inotevera:

= ZVINODIWA(zvinyorwa)

Pane nharo imwe chete - zvinyorwa pachazvo, izvo zvichagadziriswa. Unogona kushandisa cell reference.

Formula 10: PASI

Chaizvoizvo izwi rakatenderedza iro rinodzikisa mavara ese emavara akapihwa kana sero.

Syntax yayo yakafanana, pane nharo imwe chete ine zvinyorwa kana sero kero.

Formula 11: TSVAGA

Iri basa rinoita kuti zvikwanise kuwana chinhu chinodiwa pakati pehuwandu hwemasero uye kupa chinzvimbo chayo.

Iyo template yefomula iyi ndeiyi:

=MATCH(kutarisisa_kukosha, kutarisisa_kurongana, match_type)

Nharo mbiri dzekutanga dzinodiwa, yekupedzisira ndeyekusarudza.

Pane nzira nhatu dzekusangana:

1. Zvishoma pane kana kuenzana na1.
2. Chokwadi - 0.
3. Hukoshi diki, yakaenzana ne kana yakakura pane -1.

Mumuenzaniso uyu, tiri kuedza kuona kuti ndeapi emazwi akakosha anoteverwa nekudzvanya 900, zvinosanganisira.

Formula 12: DLSTR

Basa iri rinoita kuti zvikwanise kuona kureba kwetambo yakapihwa.

Syntax yayo yakafanana neyakapfuura:

=DLSTR(zvinyorwa)

Saka, inogona kushandiswa kuona kureba kwechinyorwa tsananguro kana SEO-kusimudzirwa kwesaiti.

Zvakanakawo kuibatanidza nebasa racho IF.

Formula 13: Batanidza

Basa iri rinoita kuti zvikwanise kuita mitsara yakawanda kubva kune imwe. Uyezve, zvinotenderwa kutsanangura mumagakava ese kero yesero uye kukosha kwayo. Iyo fomula inoita kuti zvikwanise kunyora zvinosvika 255 zvinhu zvine hurefu hwese husingapfuuri 8192 mavara, anokwana kudzidzira.

Iyo syntax ndeiyi:

= CONCATENATE(chinyorwa1,chinyorwa2,chinyorwa3);

Formula 14: PROPNACH

Basa iri rinochinjanisa mavara makuru nemadiki.

Syntax iri nyore kwazvo:

= PROPLAN(zvinyorwa)

Formula 15: PRINT

Iyi fomula inoita kuti zvibvire kubvisa ese asingaonekwe mavara (semuenzaniso, mitsara yekutyora) kubva kuchinyorwa.

Syntax yaro ndeiyi inotevera:

=PRINT(zvinyorwa)

Senharo, unogona kutsanangura kero yesero.

mhedziso

Ehe, aya haasi ese mabasa anoshandiswa muExcel. Taida kuunza zvimwe izvo avhareji yemushandisi wespredishiti haana kumbonzwa nezvazvo kana kusawanzoshandisa. Pachiverengero, mabasa anonyanya kushandiswa ndeekuverenga uye kutora avhareji kukosha. Asi Excel inopfuura kungoita chirongwa chespredishiti. Mariri, unogona automate zvachose chero basa. 

Ndinovimba kuti zvakabudirira, uye wakadzidza zvinhu zvakawanda zvinobatsira iwe pachako.