Articles

Excel formula: Chii chinonzi Excel mafomula uye maitiro ekuashandisa

Izwi rekuti "Excel formula" rinogona kureva chero musanganiswa we vashandisi ve Excel uye/kana Excel mabasa.

Formula yeExcel inopinzwa musero respredishiti nekunyora = chiratidzo, ichiteverwa nevashandisi vanodiwa uye/kana mabasa. Izvi zvinogona kunge zviri nyore sekuwedzera kwekutanga (semuenzaniso "= A1+B1"), kana inogona kunge iri musanganiswa wakaoma weExcel opareta uye akawanda nested Excel mabasa.

Table yeZviri mukati: Rondedzero yevashandisi uye mabasa

Excel operators

Vashandisi veExcel vanoita zviito pahuwandu hwehuwandu, zvinyorwa, kana maseru mareferensi. Kune marudzi mana akasiyana eExcel operators.

Mubvunzo :

Arithmetic operators
Vashandisi vemavara
Vagadziri vekuenzanisa
Reference operators

Ngatitsanangure mhando ina dzevashandisi:

Arithmetic operators

Excel arithmetic operators uye kurongeka kwavanoongororwa kunoratidzwa mune inotevera tafura:

Kutangira kwevashandisi ve arithmetic

Tafura iri pamusoro inoratidza kuti muzana uye vafambisi veexponentiation vane hukuru hwekutanga, hunoteverwa nevanopamhidzira nekupatsanura vafambisi, uyezve kuwedzera nekubvisa. Naizvozvo, pakuwongorora mafomula eExcel ane anopfuura mumwechete arithmetic opareta, iyo percentage uye exponential operators inoongororwa kutanga, ichiteverwa nekuwandisa nekupatsanura vafambisi. Pakupedzisira, kuwedzera uye kubvisa vashandi vanoongororwa.

Kurongeka kwevanoongororwa masvomhu kunoita mutsauko mukuru kune mhedzisiro yeExcel formula. Nekudaro, maparentheses anogona kushandiswa kumanikidza zvikamu zvefomula kuti zviongororwe kutanga. Kana chikamu cheformula chakavharirwa mumaparentheses, chikamu chakasungirirwa chefomula chinova pamberi pevashandisi vese vakanyorwa pamusoro. Izvi zvinoratidzwa mumienzaniso inotevera:

Mienzaniso yevashandi ve arithmetic

Excel text operator

Excel's concatenation operator (inoratidzwa neiyo & chiratidzo) inojoinha tambo dzemavara, kugadzira imwe yekuwedzera tambo yemavara.

Muenzaniso we concatenation operator

Iyi fomula inotevera inoshandisa concatenation opareta kubatanidza tambo dzemavara "SMITH" " uye "John"

Excel kuenzanisa vashandisi

Excel comparison operators inoshandiswa definise mamiriro, senge paunenge uchishandisa basa IF yeExcel. Vashandisi ava vakanyorwa patafura inotevera:

Mienzaniso yevashandisi vekuenzanisa

Maspredishiti ari pasi apa anoratidza mienzaniso yevashandisi vekuenzanisa vakashandiswa nebasa IF yeExcel.

Reference operators

Excel mareferensi evashandisi anoshandiswa painongedza mitsara mukati mespredishiti. Iwo mareferensi operators ndeaya:

Mienzaniso yemareferensi evashandisi

Muenzaniso 1 - Excel range operator

Sero C1 mune rinotevera spreadsheet rinoratidza renji opareta, rinoshandiswa defikupedza nguva A1-B3. Rudzi rwunobva rwapihwa kune basa SUM yeExcel, iyo inowedzera kukosha mumaseru A1-B3 uye inodzorera kukosha 21.

Muenzaniso 2 - Excel union operator

Sero C1 yespredishiti rinotevera rinoratidza mushandisi wemubatanidzwa, anoshandiswa define mutsara unoumbwa nemasero ari muzvikamu zviviri A1-A3 e A1-B1. Iyo mhedzisiro yemhando inobva yapihwa kune iyo basa SUM muExcel, iyo inopfupikisa kukosha muhuwandu hwakasanganiswa uye inodzosera kukosha 12.

Ziva kuti Excel's union operator haidzore mubatanidzwa wemasvomhu chaiwo, sesero A1, iyo inosanganisirwa muzvikamu zviviri A1-A3 e A1-B1 inoverengwa kaviri pakuverenga nhamba).

Muenzaniso 3 - Excel intersection operator

Sero C1 mune rinotevera spreadsheet rinoratidza mharadzano mushandisi, inoshandiswa defigumisa mutsara wakagadzirwa pamaseru pamharadzano yezvikamu A1-A3 e A1-B2. Izvo zvinokonzeresa renji (range A1-A2) inobva yapihwa kubasa re SUM yeExcel, iyo inopfupikisa kukosha mumharadzano renji uye inodzosera kukosha 4.

Rumwe ruzivo nezveExcel operators runowanikwa pa Microsoft Office webhusaiti.

Excel mabasa

Excel inopa huwandu hukuru hweakavakirwa-mukati mabasa anogona kushandiswa kuita chaiwo masvomhu kana kudzosera ruzivo nezve data respredishiti. Aya mabasa akarongwa muzvikamu (zvinyorwa, logic, masvomhu, statistics, etc.) kuti ikubatsire kutsvaga basa raunoda kubva kuExcel menyu.

Pazasi tinopa runyoro rwakakwana rweExcel mabasa, akaunganidzwa nechikamu. Imwe neimwe yebasa rekubatanidza inokuendesa kune yakatsaurirwa peji, kwaunowana tsananguro yebasa, ine mienzaniso yekushandisa uye ruzivo pane zvakajairika zvikanganiso.

Excel statistical mabasa:

Count uye Frequency

COUNT: Inodzosa nhamba yenhamba dzenhamba mune yakapihwa seti yemasero kana kukosha;
COUNTA: Inodzosa nhamba yezvisiri nzvimbo museti yakapihwa yemaseru kana kukosha;
COUNTBLANK: inodzosa nhamba yemasero asina chinhu muchikamu chakapihwa;
COUNTIF: inodzosa nhamba yemasero (echikamu chakapihwa), anogutsa chiyero chakapihwa;
COUNTIFS: inodzosa nhamba yemasero (yemhando yakapihwa) inogutsa yakatarwa seti yemaitiro (Itsva muExcel 2007);
FREQUENCY+

Kutsvaga iyo Maximum uye Minimum

MAX: Inodzosa kukosha kukuru kubva pane rondedzero yenhamba dzakapihwa
MAXA: Inodzosa kukosha hukuru kubva kune runyorwa rwemutengo wakapihwa, kuverenga mavara uye kukosha kunonzwisisika FALSE se kukosha kwe0 uye kuverenga kukosha kunonzwisisika TRUE kukosha kwe1
MAXIFS: Inodzosa kukosha kukuru kubva kune subset yezvikoshi mune yakatarwa runyorwa zvichibva pane imwe kana akawanda maitiro. (Itsva kubva kuExcel 2019)
MIN: Inodzosa kukosha kudiki kubva pane rondedzero yenhamba dzakapihwa
MINA: Inodzosa kukosha kudiki kubva murondedzero yemitengo yakapihwa, kuverenga zvinyorwa uye kukosha kunonzwisisika FALSE seukoshi hwe0 uye kuverenga ukoshi hunonzwisisika TRUE seukoshi hwe1.
MINIFS: Inodzosa kukosha kudiki kubva kune subset yezvikoshi mune yakatarwa runyorwa zvichibva pane imwe kana akawanda maitiro. (Chii Chitsva muExcel 2019)
LARGE: Inodzosa kukosha kweKth KUKURU kubva pane rondedzero yenhamba dzakapihwa, kune yakapihwa K kukosha
SMALL: Inodzosa kukosha kweKth SMALLEST kubva pane rondedzero yenhamba dzakapihwa, kune yakapihwa K kukosha

pakati

AVERAGE: Inodzosa avhareji yenhamba dzenhamba dzakapihwa
AVERAGEA: Inodzosa avhareji yenhamba dzenhamba dzakapihwa, tichiverenga zvinyorwa uye kukosha kunonzwisisika FALSE seukoshi hwe0, uye kuverenga kukosha kunonzwisisika TRUE seukoshi hwe1.
AVERAGEIF: Inoverengera avhareji yemaseru mune yakapihwa renji, inosangana neyakapihwa chirevo (Itsva muExcel 2007)
AVERAGEIFS: Inoverengera avhareji yemaseru mune yakapihwa renji, inosangana nezvakawanda maitiro (Itsva muExcel 2007)
MEDIAN: Inodzosa yepakati (yepakati kukosha) yerondedzero yenhamba dzakapihwa
MODE: Inoverengera modhi (yakanyanya kukosha) yeiyo yakapihwa runyorwa rwenhamba (yakatsiviwa nebasa Mode.Sngl muExcel 2010)
MODE.SNGL: Inoverengera modhi (yakawanda kazhinji kukosha) yerondedzero yenhamba dzakapihwa (Itsva muExcel 2010: inotsiva basa Mode)
MODE.MULT: Inodzosa mutsara wakatwasuka wezvakawanda zvinowanzoitika muhurongwa kana data renji (Itsva muExcel 2010)
GEOMEAN: Inodzosa zvinoreva geometric yenhamba dzakapihwa
HARMEAN: Inodzosa iyo harmonic zvinoreva seti yenhamba dzakapihwa
TRIMMEAN: Inodzosa avhareji yemukati meseti yakapihwa yemakoshero

Permutations

PERMUT: Inodzosa nhamba yemvumo yenhamba yakapihwa yezvinhu
PERMUTATIONA: Inodzosa nhamba yemvumo yenhamba yakapihwa yezvinhu (nedzokororo) inogona kusarudzwa kubva kune yakazara zvinhu (Zvitsva muExcel 2013)

Nguva Yekutenda

CONFIDENCE: Inodzosa nguva yekuvimba yehuwandu hwehuwandu, ichishandisa kugoverwa kwakajairika (inotsiviwa neConfidence.Norm basa muExcel 2010)
CONFIDENCE.NORM: Inodzosa nguva yekuvimba yehuwandu hunoreva, uchishandisa yakajairwa kugovera (Itsva muExcel 2010: inotsiva iyo Ruvimbo basa)
CONFIDENCE.T: Inodzosa nguva yekuvimba yehuwandu hunoreva, uchishandisa t-kugovera kweMudzidzi (Itsva muExcel 2010)

Percentiles uye Quartiles

PERCENTILE: Inodzosa iyo Kth percentile yema values muchikamu chakapihwa, apo K iri muchikamu 0 - 1 (inosanganisira) (Yatsiviwa nePercentile.Inc basa muExcel 2010)
PERCENTILE.INC: Inodzosa iyo Kth percentile yehunhu mune yakapihwa renji, uko K iri muhuwandu 0 - 1 (inosanganisira) (Itsva muExcel 2010: inotsiva iyo Percentile basa)
PERCENTILE.EXC: Inodzosa iyo Kth percentile yehunhu mune yakapihwa renji, apo K iri muchikamu 0 - 1 (chakasarudzika) (Itsva muExcel 2010)
QUARTILE: Inodzosa quartile yakatarwa yenhamba dzakapihwa, zvichibva pa percentile value 0 – 1 (inosanganisira) (Yatsiviwa neQuartile.Inc basa muExcel 2010)
QUARTILE.INC: Inodzosa iyo yakataurwa quartile yeakapihwa seti yenhamba, zvichibva pane percentile kukosha 0 - 1 (inosanganisira) (Itsva muExcel 2010: inotsiva Quartile basa)
QUARTILE.EXC: Inodzosa iyo quartile yakatsanangurwa yeakapihwa seti yenhamba, zvichibva pane 0 - 1 (yakasarudzika) percentile kukosha (Itsva muExcel 2010)
RANK: Inodzosa nhamba yehuwandu hwehuwandu hwakapihwa, mukati mehuwandu hwakapihwa hwehukoshi (yakatsiviwa neiyo Rank.Eq basa muExcel 2010)
RANK.EQ: Inodzosa iyo modhi (yakanyanya kukosha) yerondedzero yenhamba dzakapihwa (kana inodarika kukosha imwe ine chinzvimbo chakafanana, iyo yepamusoro chinzvimbo cheiyo set inodzoserwa) (Itsva muExcel 2010: inotsiva iyo Rank basa)
RANK.AVG: Inodzosa nhamba yehuwandu hwehuwandu hwakapihwa, mukati mehuwandu hwakapihwa hwehukoshi (kana akawanda ma values aine chinzvimbo chakafanana, avhareji chinzvimbo chinodzoserwa) (Itsva muExcel 2010)
PERCENTRANK: Inodzosa chinzvimbo chemutengo mu data set, sechikamu (0 - 1 inosanganisirwa) (Yatsiviwa nePercentrank.Inc basa muExcel 2010)
PERCENTRANK.INC: Inodzosa chinzvimbo chemutengo museti yedata, seti muzana (0 - 1 inosanganisirwa) (Itsva muExcel 2010: inotsiva iyo Percentrank basa)
PERCENTRANK.EXC: Inodzosa chinzvimbo chemutengo museti yedata, seti muzana (kusingasanganisire 0 - 1) (Itsva muExcel 2010)

Kutsauka uye kusiyana

AVEDEV: Inodzosa avhareji yekutsauswa kwakakwana kwemapoinzi edhata kubva pane zvazvinoreva
DEVSQ: Inodzosa huwandu hwemasikweya ekutsauswa kweseti yemapoinzi edhata kubva kune ayo emuenzaniso zvinoreva
STDEV: Inodzosa kutsauka kwakajairwa kweseti yakapihwa yehukoshi (inomiririra sampuli yehuwandu) (Yatsiviwa neiyo St.Dev basa muExcel 2010)
STDEV.S: Inodzosa yakajairwa kutsauka kweiyo yakapihwa seti yehukoshi (inomiririra sampuli yehuwandu) (Itsva muExcel 2010: inotsiva iyo STDEV basa)
STDEVA: Inodzosa kutsauka kwakajairwa kweiyo yakapihwa seti yehukoshi (inomiririra sampuli yehuwandu), kuverenga zvinyorwa uye kukosha kunonzwisisika FALSE seukoshi hwe0 uye kuverenga kukosha kunonzwisisika TRUE se kukosha kwe1.
STDEVP: Inodzosa yakajairwa kutsauka kweiyo yakapihwa seti yehukoshi (inomiririra huwandu hwese) (Yatsiviwa neStdPDev basa muExcel 2010)
STDEV.P: Inodzosa yakajairwa kutsauka kweiyo yakapihwa seti yehukoshi (inomiririra huwandu hwese) (Itsva muExcel 2010: inotsiva iyo STDEV basa)
STDEVPA: Inodzosa kutsauka kwakajairwa kweiyo yakapihwa seti yehukoshi (inomiririra ruzhinji rwevanhu), tichiverenga zvinyorwa uye zvine musoro kukosha FALSE seukoshi hwe0 uye kuverenga ukoshi hunonzwisisika TRUE se kukosha kwe1.
VAR: Inodzosa musiyano weiyo yakapihwa seti yehukoshi (inomiririra sampuli yehuwandu) (Yatsiviwa neiyo SVar basa muExcel 2010)
VAR.S: Inodzosa mutsauko weiyo yakapihwa seti yehunhu (inomiririra sampuli yehuwandu) (Itsva muExcel 2010 - inotsiva iyo Var basa)
VARA: Inodzosa musiyano weiyo yakapihwa seti yehunhu (inomiririra sampuli yehuwandu), kuverenga zvinyorwa uye zvine musoro kukosha FALSE seukoshi hwe0 uye kuverenga ukoshi hunonzwisisika TRUE semutengo we1.
VARP: Inodzosa musiyano weiyo yakapihwa seti yehukoshi (inomiririra huwandu hwese) (Yatsiviwa neVar.P basa muExcel 2010)
VAR.P: Inodzosa mutsauko weiyo yakapihwa seti yehunhu (inomiririra huwandu hwese) (Itsva muExcel 2010 - inotsiva iyo Varp basa)
VARPA: Inodzosa musiyano weiyo yakapihwa seti yemakoshe (inomiririra ruzhinji rwevanhu), tichiverenga zvinyorwa uye zvine musoro kukosha FALSE seukoshi hwe0, uye kuverenga kukosha kunonzwisisika TRUE sekukosha kwe1.
COVAR: Inodzosa covariance yehuwandu (kureva avhareji yezvigadzirwa zvekumisikidza kune imwe neimwe peya mukati meakapihwa data seti) (Yatsiviwa neCovariance.P basa muExcel 2010)
COVARIANZA.P: Inodzosa covariance yehuwandu (kureva avhareji yezvigadzirwa zvekutsauswa kwepairi yega yega mukati meakapihwa data seti) (Itsva muExcel 2010: inotsiva iyo Covar basa)
COVARIANZA.S: Inodzosa iyo sampuli covariance (kureva avhareji yezvigadzirwa zvekutsaudzira kune imwe neimwe peya mukati meakapihwa data seti) (Itsva muExcel 2010)

Predictive functions

FORECAST: Inofanotaura nezveramangwana pane mutsara mutsara wakarongedzwa kune yakapihwa seti ye x uye y kukosha (yakatsiviwa nebasa FORECAST.LINEAR muExcel 2016)
FORECAST.ETS: Inoshandisa exponential smoothing algorithm kufanotaura kukosha kweramangwana pamusoro penguva yakatarwa, zvichibva pane zvakatevedzana zviripo (Nyowani muExcel 2016 - isingawanikwe muExcel 2016 yeMac)
FORECAST.ETS.CONFINT: Inodzosa nguva yekuvimba yehuwandu hwekufanotaura pane yakatarwa zuva rakatarwa (Itsva muExcel 2016 - isingawanikwe muExcel 2016 yeMac)
FORECAST.ETS.SEASONALITY: Inodzosa kureba kweiyo nzira yekudzokorora yakaonekwa neExcel yenguva yakatarwa (Nyowani muExcel 2016 - isingawanikwe muExcel 2016 yeMac)
FORECAST.ETS.STAT: Inodzosa kukosha kwehuwandu nezve fungidziro yenguva (Itsva muExcel 2016 - isingawanikwe muExcel 2016 yeMac)
FORECAST.LINEAR: Inofanotaura nezveramangwana pane mutsara mutsara wakarongedzwa kune yakapihwa seti ye x uye y kukosha (Itsva muExcel 2016 (kwete Excel 2016 yeMac) - inotsiva iyo Forecast basa)
INTERCEPT: Inoverengera yakanyatsokodzera mutsara wekudzosera, kuburikidza nenhevedzano ye x uye y kukosha, inodzosa kukosha uko mutsetse uyu unobata y axis.
LINEST: Inodzosa ruzivo rwenhamba runotsanangura mafambiro emutsetse wemhando yepamusoro, kuburikidza nenhevedzano ye x uye y kukosha
SLOPE: Inodzosa kutsetseka kwemutsara wekudzoreredza mutsara kuburikidza neseti yakapihwa ye x uye y kukosha
TREND: Inoverengera mutsara wemaitiro kuburikidza neyakapihwa seti ye y kukosha uye inodzosera yakawedzera y kukosha kune yakapihwa seti yezvitsva x kukosha
GROWTH: Inodzosa nhamba mune exponential yekukura maitiro, zvichibva pane seti ye x uye y kukosha kwakapihwa
LOGEST: Inodzosa maparamendi eexponential trend kune yakapihwa seti ye x uye y kukosha
STEYX: Inodzosa chikanganiso chakafanotaurwa cheukoshi hwe y hwega hwega x mumutsara wekudzoreredza kune seti yakapihwa yemitengo ye x uye y