Iwo akanyanya kukosha eExcel mafomula ekutanga kubva mukutanga senge pro

Kudzidzira Excel kubva mukutanga kuri nyore pane zvazvinoita kana uchiziva pekutangira. Vazhinji vashandisi, kunyangwe avo vanoishandisa zuva nezuva, vanowanzo dzikamisa ivo pachavo kune akanyanya mafomula, nekudaro vachirasikirwa nezvakawanda zvesimba rechirongwa ichi. Kudzidza akakosha eExcel mafomula hakungokuchengetere nguva uye zvikanganiso, asi Ichakubvumidza kuti uongorore, kuronga uye kuona data senge nyanzvi yechokwadi..

Mugwaro iri, iwe unowana akanyanya kukosha mafomula, kubva kune akapusa kusvika kune epamusoro mashandiro ayo anokugadzirira iwe kune chero dambudziko rebasa. Chinangwa ndechekuti iwe, ungave uri wekutanga kana kuti watove neruzivo, kuti ugone kubata chero spreadsheet zviri nyore, kutonga, uye nekubudirira. Pamusoro pe, Mazano anobatsira, mazano anoshanda, uye zviwanikwa zvekuita kuti kudzidza kwako kuve kwakapfava sezvinobvira.

Chii chinonzi formula muExcel uye chinoshandiswa chii?

Formula muExcel inoteedzana inoita maverengero, inogadzirisa data, kana kudzorera kukosha mukati mespredishiti. Mafomu ese anotanga nechiratidzo chakaenzana (=), iyo inoudza Excel kuti iverenge mhedzisiro zvichienderana neakasanganisirwa kukosha, mareferensi, uye vashandisi.

Mafomura anotendera iwe kuti uite zvese kubva nyore masvomhu mashandiro kusvika pakutsvaga, kuongororwa kwedata, kuverenga kwemari, kunyengedza zuva, uye zvimwe zvakawanda. Kusiyana-siyana kwavo kunoita kuti vakoshe mune chero nharaunda, ingave yedzidzo, yebhizinesi kana yekushandisa wega.

Somuenzaniso, kana ukanyora =A1+B1 muchitokisi, Excel inowedzera hunhu huri mumasero A1 uye B1. Asi mikana inoenda zvakanyanya, inokutendera iwe kuti uvake mashandiro akaomarara ane akawanda mamiriro, ane simba mareferensi, nested mabasa, uye otomatiki kuverenga kwemavhoriyamu makuru edata..

Musiyano pakati pemafomula uye mabasa muExcel

MuExcel, iwo mazwi fomula uye basa anowanzoshandiswa zvakasiyana, asi haana kunyatsofanana. Kunzwisisa mutsauko kuchakubatsira iwe kugadzira uye kusanganisa mamwe mabasa akaomarara:

• Fomura: Iyo ine chirevo chakagadzirwa nemaoko uchishandisa kukosha, maseru mareferenzi uye masvomhu vanoshanda (+, -, *, /, ^). Muenzaniso: =A1+B2-3
• Basa: Iro basa rakafanotsanangurwa muExcel rinorerutsa kuverenga kwakaoma. Basa rega rega rine syntax chaiyo uye nharo, izvo zvakavharirwa mumaparentheses. Muenzaniso: =SUM(A1:A10)

Mukuita, mafomula anogona kusanganisira mabasa, uye aya anogona kusanganiswa kugadzira mashandiro ari kuwedzera..

Zvinhu zvefomula muExcel

Mafomu eExcel anoumbwa nezvinhu zvakati wandei zvaunofanira kuziva:

• Constants: Iwo akatarwa kukosha, senge nhamba (50) kana kuti zvinyorwa (“Ndira”).
• Refer: Inoratidza sero kana huwandu hwemasero uko Excel ichatsvaga kukosha kwekushandisa. Muenzaniso: A1, B2: B8.
• Vashandisi: Zviratidzo zveMasvomhu zvekuwedzera (+), kubvisa (-), kuwanza (*), patsanura (/), wedzera (^), concatenate (&) uye enzanisa (=, >, <, <>, etc.).
• Mabasa: Akafanotsanangurwa mazwi akakosha anoita chaiwo kuverenga (SUM, AVERAGE, VLOOKUP, IF, nezvimwewo).

Kudzidza kubatanidza zvinhu izvi ndiyo kiyi yekuvaka mafomula anonyatsoita mutsauko pabasa rako rezuva nezuva..

Mhando dzemafomula muExcel: kubva kune yekutanga kusvika kumberi

Kune akawanda marudzi emafomati muExcel, kubva kune akapusa kusvika kune epamusoro maturusi ekuongorora kwehunyanzvi:

• Mafomula akareruka: Kuwedzera kwakananga, kubvisa, kuwanda uye kupatsanura.
• Mafomula ane mareferensi: Zvishandiso zvinoshandisa data iri mumaseru akasiyana.
• Formula dzine mabasa: Otomatiki sums, mavhareji, kutsvaga, kuverenga, kutenderedza, nezvimwe.
• Conditional formulas: Ivo vanoita masvomhu zvichienderana nekuti mamiriro akazadzikiswa here (KANA, UYE, KANA, KWETE mabasa).
• Mafomula ane maitiro akawanda: Mashandiro ane akawanda anodiwa uchishandisa SUMIFS, COUNTIFS.
• Mari uye manhamba mafomula: Zvekuverengera chikwereti, mhindu, mafambiro, nezvimwe.
• Manyorerwo emafomula: Kubatanidza, kubvisa kana kuchenesa tambo dzemavara.
• Date uye nguva formula: Kuverengera nguva, misiyano yemazuva, kana kubvisa zvinhu zvakaita semwedzi kana gore.
• Array formulas: Kuverengera pane yakakura data seti.

Basic masvomhu mashandiro muExcel

Wese mushandisi weExcel anofanirwa kugona akanyanya kukosha arithmetic mashandiro, sezvo ari iwo hwaro pakavakirwa mamwe akaomarara mafomula. Iwe unogona kuvaisa zvakananga musero kana kusanganisa mareferensi uye mabasa.

• Wedzera: =A1+B1 wedzera kukosha mumasero A1 uye B1.
• Bvisa: =A2-B2 bvisa kukosha kweB2 kubva kune iyo yeA2.
• Wanza: =A3*B3 wedzera kukosha kweA3 uye B3.
• Kupatsanura: =A4/B4 patsanura kukosha kweA4 neiyo yeB4.
• Simba: =A5^2 simudza kukosha kweA5 kune square.

Excel inoremekedza kurongeka kwemasvomhu maitiro: ekutanga masimba, ipapo kuwanda uye kupatsanura, uye pakupedzisira kuwedzera nekubvisa. Kana iwe uchida kudzora kurongeka, shandisa maparentheses: = (A1+B1)*C1.

Essential Excel mafomula uye mashandisiro awo

Pazasi pane akakosha mafomula iwe aunofanira kuziva kuti utange kushanda seyechokwadi Excel nyanzvi:

SUMA: Iyo formula par kugona

SUM ndiro rinonyanya kushandiswa basa uye rinokutendera iwe kukurumidza uye nyore kuwedzera kukosha muhuwandu kana akawanda asina-akabatana maseru.

• Muenzaniso: =SUMA(A1:A10) ichaita chidimbu chese kukosha muhuwandu A1 kusvika A10.
• Uyezve, iwe unogona kuwedzera chaiwo maseru: =SUM(A1,B2,B3)
• Iyo AutoSum ficha, iri paKumba tebhu, inoita kuti basa iri rive nyore.

AVERAGE: Verenga avhareji yedata rako

Basa racho AVERAGE Inodzosa kukosha kweavhareji yeboka renhamba, yakanakira kuongorora mafambiro, kuona maavhareji ekutengesa, mamakisi, mitengo, nezvimwe.

• Muenzaniso: =PROMEDIO(B1:B12) ichaverenga avhareji yehunhu huri pakati peB1 neB12.
• Kune maavhareji emamiriro aripo AVERAGE.IF y AVERAGE KANA JOINT.

COUNT, COUNTIF, uye COUNTIF: Inyanzvi yekuverenga data

NeExcel, unogona kuverenga zvese nhamba uye zvinyorwa, maseru asina chinhu kana maseru ane zvirimo, zvichienderana nebasa rakasarudzwa.

• COUNT: Inoverenga maseru ane manhamba chete. =CONTAR(C1:C10)
• ZVICHAVERENGA: Inoverenga maseru ese ane data (nhamba kana zvinyorwa), ichifuratira chete maseru asina chinhu. =CONTARA(C1:C10)
• COUNTIF: Inoverenga maseru anosangana nechero mucherechedzo. =CONTAR.SI(D1:D20,"Aprobado")
• COUNTIFS: Inobvumira akawanda mamiriro. =CONTAR.SI.CONJUNTO(A1:A10,">10",B1:B10,"Hombre")
• COUNTBLANK: Inoverenga maseru asina chinhu muchikamu. =COUNTBLANK(A2:D2)

MIN uye MAX: Tsvaga zvakanyanyisa mune yako data

Iyo MIN uye MAX mabasa anokurumidza kuona iyo yakaderera kana yepamusoro kukosha muhuwandu, inobatsira pakuongorora mutengo, manotsi, kutengesa kwepamusoro, nezvimwe.

• Minimum muenzaniso: =MIN(A2:A100)
• Muenzaniso mukuru: =MAX(A2:A100)

ROUND, ROUNDUP, uye ROUNDDOWN: Kurongeka mukuverenga

Aya mabasa anotendera iwe kugadzirisa mhedzisiro kune yakatarwa nhamba yenzvimbo dzedesimali kana kutenderedza kumusoro kana pasi zvinoenderana nezvaunoda.

• RUND OUT: =REDONDEAR(A1,2) inogadzirisa kunzvimbo mbiri dzedesimali.
• ROUND.MORE: Gara uchitenderedza. Muenzaniso: =REDONDEAR.MAS(3.14159,2) → 3.15
• ROUND.MINUS: Nguva dzose tenderera pasi. Muenzaniso: =REDONDEAR.MENOS(3.14159,2) → 3.14

Conditional Formula: Kuita Sarudzo Dzakangwara

Kuti uverenge kana kuratidza mhinduro dzakasiyana zvichienderana nemamiriro ezvinhu, sekuti munhu akapasa here kana kuti afoira, unofanirwa kushandisa maconnditional formulas. Anonyanya kushandiswa zvine musoro mabasa ndeaya:

• EHE: Inoongorora mamiriro uye inodzosera imwe kukosha kana iri yechokwadi uye imwe kana iri yenhema.
  Muenzaniso: =SI(B2>=60, "Aprobado", "Reprobado")
• Y: Inodzosa TRUE chete kana zvese zvaitwa.
  Muenzaniso: =SI(Y(A1>10, B1<50), "Ok", "No")
• O: Inodzosa TRUE kana kana chimiro chimwe chete chaitwa.
  Muenzaniso: =SI(O(A1>100, B1>100), "Revisar", "Correcto")
• AIHWA: Inverts the logic value.
  Muenzaniso: =SI(NO(A1=15), "Diferente de 15", "Es 15")
• YES.ERROR: Inokutendera kuti uratidze imwe kukosha kana fomula ikatadza.
  Muenzaniso: =SI.ERROR(A2/B2, "No calculable")

Tsvaga uye kubvisa ruzivo: VLOOKUP, HLOOKUP, INDEX, MATCH uye XLOOKUP

Imwe yeExcel ine simba kwazvo maficha kugona kutsvaga data mumatafura uye kudzorera ruzivo rwakabatana. Kana iwe uchida kuchera zvakadzika mune aya maficha, dzidza zvakawanda negwaro iri mashandisiro eExcel formula.

• VLOOKUP: Inotsvaga kukosha muchikamu chekutanga cherenji uye inodzosa data kubva kune imwe column mumutsara mumwe chete. Muenzaniso: =BUSCARV(102,A2:C10,3,FALSO)
• HLOOKUP: Zvakafanana neVLOOKUP asi inoita zvakachinjika nemitsara.
• INDEX + MATCH: Ivo vanokutendera kuti utsvage mune chero nzira (kwete kurudyi seVLOOKUP) uye inopa yakawanda shanduko.
  Muenzaniso: =ÍNDICE(B2:B20, COINCIDIR("Producto",A2:A20,0))
• XLOOKUP: Basa remazuva ano rinotsiva VLOOKUP neHLOOKUP, richibvumira kutsvaga kune simba uye kuchinjika mumakoramu ese nemitsara.

SUMIF, SUMIFS, uye AVERAGEIFS: Sums uye mavhareji ane maitiro

• wedzera IF: Inopfupisa kukosha muhuwandu hunosangana nemamiriro. Muenzaniso: =SUMAR.SI(A1:A10,">10")
• SUM IF SET: Inobvumira zvakawanda zvinodiwa. =SUMAR.SI.CONJUNTO(C2:C12,B2:B12,">500",D2:D12,"Región1")
• AVERAGEIFS: Inoverengera avhareji yezvikoshi zvinosangana nemaitiro akawanda. Muenzaniso: =AVERAGEIFS(C3:C15, A3:A15, "Manzana", B3:B15, "Sur")

Kushandura zvinyorwa muExcel: CONCAT, CONCATENATE, JOIN, LEFT, RIGHT, MINT, LEN, SPACES, SUBSTITUTE, REPLACE

• CONCAT uye CONCATENATE: Sanganisa zviri mukati memasero akati wandei kuita sero rimwe. Muenzaniso: =CONCAT(A1, " ", B1).
• JOINERS: Inofananidzwa nemagwaro ane muganho chaiwo. Muenzaniso: =UNIRCADENAS(", ", VERDADERO, A1:A3).
• KURUboshwe nekurudyi: Bvisa mavara ekutanga kana ekupedzisira kubva muchinyorwa. Muenzaniso: =IZQUIERDA(B2, 3) inoburitsa mavara matatu ekutanga.
• ZVINOtorwa: Inobvisa nhamba yakatarwa yemavara kubva panzvimbo yaunosarudza. Muenzaniso: =EXTRAE(C3, 2, 5).
• RONGUVA: Inodzosa kureba kwemavara. Muenzaniso: =LARGO(D4).
• ZVINHU: Bvisa nzvimbo dzisina basa kubva pane zvinyorwa. Muenzaniso: =ESPACIOS(E5).
• REPLACE: Tsiva zvikamu zvemashoko nezvimwe. Muenzaniso: =SUSTITUIR(B15, "Madrid", "Barcelona").
• REPLACE: Inotsiva mavara panzvimbo chaiyo. Muenzaniso: =REEMPLAZAR("Feliz Navidad", 6, 8, "Hanukkah").

Zuva uye Nguva Formula: Ronga uye verenga Nguva zviri nyore

Kugadzirisa misi uye nguva kwakakosha kune chero mushumo, chirongwa, kana kuongorora. 

• NHASI: Inodzosa zuva razvino. =HOY()
• ZVINO: Inodzosa zuva razvino uye nguva. =AHORA()
• ZUVA, MWEDZI, GORE: Bvisa zuva, mwedzi, kana gore kubva pazuva. Muenzaniso: =DIA(B2)
• DATE: Inogadzira zuva kubva pagore rakapihwa, mwedzi, uye zuva. Muenzaniso: =FECHA(2025,6,10)
• MAZUVA: Verenga nhamba yemazuva pakati pemazuva maviri. =DIAS(B1,A1)
• IF DATED: Inotarisa musiyano chaiwo pakati pemazuva maviri mumakore, mwedzi, kana mazuva. Muenzaniso: =SIFECHA(A1,B1,"Y")
• WEEKDAY: Inodzosa zuva revhiki rezuva rakapihwa. Muenzaniso: =DIASEM(B2,2)

Maturusi ekuongorora data: Pivot matafura, kuronga uye mafirita, slicer, machati

Kuti uwedzere kuongororwa uye kuona kwe data rako, in ichi chinyorwa Iwe unogona kuwana akanakisa AI-powered Excel maturusi, anosanganisira pivot matafura uye anodyidzana machati.

• Dynamic tables: Vanokutendera kuti uunganidze, kupfupikisa uye kuongorora data yakaoma nenzira ine simba uye yekuona.
• Mabasa akaita seSORT uye UNIQUE: Ivo vanobatsira kubvisa zvinyorwa zvakasiyana kana kuronga data otomatiki.
• Graphics: Ivo vanofambisa kududzirwa kwekuona kwemhedzisiro (makoramu, mabara, mitsetse, nzvimbo, nezvimwewo).
• Segmentations: Mafirita anooneka uye anodyidzana ematafura epivot.

Kubatanidza maturusi aya nemafomula akatsanangurwa anokutendera kuti uwane ruzivo rwakakodzera mumasekonzi uye kuita sarudzo zvichienderana nedata rakasimba.

Yakakosha zvemari uye bhizinesi mafomula

• NPV (Net Present Value): Ongorora mainvestimenti uchifunga nezve kukosha kwazvino kwekuyerera kwemari mune ramangwana.
• IRR (Muyero Wemukati Wekudzoka): Verenga purofiti yekudyara.
• PAYMENT uye PMT: Vanosarudza kubhadhara nguva nenguva kwechikwereti.
• NOMINAL VALUE/NOMINAL RATE: Shanduko pakati pemhando dzakasiyana dzemitengo yemubereko.
• DIAS360: Inobatsira kumaakaundi emari anoshandisa 360-day makore.
• ZVINHU, ZVAKAITIKA: Inobatsira pakufungidzira uye kuongorora maitiro.

Kudzora uye kugadzirisa: Mafomula ekubata kukanganisa uye kutonga data

Kuti uchengetedze maspredishiti ako asina kukanganisa uye kugadzirisa kuvimbika kwekuverenga kwako, iwe unowana matipi ekugadzirisa zvikanganiso muExcel pano.

• IF.ERROR / IFERROR: Vanodzorera imwe kukosha kana fomula yakundikana.
• ZVIKANGANISO: Inoratidza kana sero rine kukanganisa (semuenzaniso, kupatsanura ne zero).
• ISWHITE: Inoona kana sero risina chinhu.
• ISNUMBER/TEXT: Vanoratidza rudzi rwe data iri musero.
• KUREFU/LONGB: Inodzosa nhamba yemavara mumavara, anobatsira pakuongorora data ratorwa kunze kwenyika.
• SERE: Inodzosa ruzivo nezve imwe sero, chimiro chayo, rudzi rwedata, nezvimwe.

Maitiro akanakisa, matipi, uye mapfupi ekugona Excel mafomula

• Shandisa zvidimburira zve keyboard: Kufanana neCtrl+C/V yekukopa/kuisa, Ctrl+1 yefomati yesero, Ctrl+Shift+% muzana, Alt+= yemari inokurumidza.
• Drag formulas: Shandisa mubato wekuzadza (iyo diki diki mukona yesero) kukopa mafomula pasina kuanyora nemaoko.
• Tarisa mareferensi: Ita shuwa kuti unoshandisa absolute references ($A$1) uye hama (A1) nenzira kwayo, kunyanya kana uchikopa mafomula.
• Dzivirira mafomula ako: Kiya maseru ane mafomula akakosha kudzivirira kuti asadzimwa netsaona kana kuchinjwa (Format Cell> Dzivirira> Kiya).
• Viga mafomula kana zvichidikanwa: Unogona kuvanza kuratidzwa kwemafomula kudzivirira vamwe kuti vasaone maverengero ari pasi.
• Chenesa data: Shandisa SPACES, SUBSTITUTE, uye mamwe mabasa kuchenesa ruzivo usati washandisa akakosha mafomula.

Mamwe matipi ekuwedzera yako Excel ruzivo

• Gara uchitanga kuverenga nezviri nyore: Nyasha izvo zvekutanga usati waenda kune epamusoro maturusi.
• Dzidza nemakesi anoshanda: Gadzira ako epaspredishiti kuti utore madhiri, hesitori, zvinyorwa zvekuita, kana kutora-noti.
• Ongorora zviwanikwa zvemahara: Kune akawanda epamhepo makosi, matemplate, maforamu, uye tutorials izvo zvinogona kukubatsira zuva rega rega.
• Ramba uri pamusoro penhau: Shanduro dzichangoburwa dzinosanganisira mabasa akadai seXLOOKUP, matrices ane simba kana Power Query yekuongorora kukuru kwedata.
• Shandisa zvikamu zvepamberi uye mafirita: Kuti uwane ruzivo rwakakura, tora mukana wekugovanisa data uye mafirita kuti uone chete zvine basa.
• Shandisa conditional formatting: Inoisa otomatiki kukosha kwakakosha, mafambiro, kana kukanganisa kuona data yakakosha.

Iyi ruzivo rwepamusoro uye matipi anoshanda achakubatsira iwe kubata zvirinani basa rako rezuva nezuva Excel, otomatiki mabasa, kuderedza zvikanganiso, uye kugadzira mishumo yakarurama. Kugara uchidzidzira uye kuongorora zvinhu zvitsva zvinokura hunyanzvi hwako zvakanyanya, zvichikuisa iwe senyanzvi mumunda. Tinovimba iwe ikozvino wave kuziva akakosha eExcel mafomula ekukubatsira iwe kutanga kubva mukutanga uye kuve nyanzvi.