Amafomula e-Excel: Ayini amafomula e-Excel nokuthi asetshenziswa kanjani

Igama elithi “Excel formulas” lingabhekisa kunoma iyiphi inhlanganisela ye i-operatori di I-Excel kanye/noma Imisebenzi ye-Excel.

Ifomula ye-Excel ifakwa kuseli lesipredishithi ngokuthayipha okuthi = uphawu, kulandelwe ama-opharetha adingekayo kanye/noma imisebenzi. Lokhu kungase kube lula njengokuhlanganisa okuyisisekelo (isb. “=A1+B1”), noma kungaba inhlanganisela eyinkimbinkimbi yama-opharetha e-Excel kanye nemisebenzi eminingi ye-Excel enezidleke.

Abasebenzisi be-Excel

Ama-opharetha e-Excel enza izenzo ngamavelu ezinombolo, umbhalo, noma ireferensi yeseli. Kunezinhlobo ezine ezihlukene zama-opharetha e-Excel.

Umbuzo:

• Abaqhubi be-Arithmetic
• Ama-opharetha ombhalo
• Ama-opharetha wokuqhathanisa
• Ama-opharetha ayisethenjwa

Ake sichaze izinhlobo ezine zama-opharetha:

Abaqhubi be-Arithmetic

Ama-opharetha ezibalo ze-Excel kanye nokuhleleka kwawo ahlolwa kuboniswe kuleli thebula elilandelayo:

Ukuqala kwabaqhubi be-arithmetic

Ithebula elingenhla libonisa ukuthi amaphesenti kanye nabaqhubi be-exponentiation banokuzandulela okuphezulu kakhulu, okulandelwa ama-opharetha okuphindaphinda nokuhlukanisa, bese kuba ama-opharetha okuhlanganisa nokukhipha. Ngakho-ke, lapho kuhlolwa amafomula e-Excel aqukethe ngaphezu ko-opharetha wezibalo oyedwa, amaphesenti kanye nama-opharetha ama-exponential ayahlolwa kuqala, alandelwe ama-opharetha okuphindaphinda nokuhlukanisa. Okokugcina, ama-opharetha okufaka nokukhipha ayahlolwa.

Ukuhleleka okuhlolwa ngayo o-opharetha bezibalo kwenza umehluko omkhulu kumphumela wefomula ye-Excel. Nokho, abakaki bangasetshenziswa ukuphoqa izingxenye zefomula ukuthi zihlolwe kuqala. Uma ingxenye yefomula ifakwe kubakaki, ingxenye ekubakaki yefomula izoza kuqala kunabo bonke opharetha abasohlwini olungenhla. Lokhu kuboniswa ezibonelweni ezilandelayo:

Izibonelo zama-opharetha ezibalo

I-Excel text operator

U-opharetha wokuhlanganisa we-Excel (okhonjiswa uphawu nophawu) uhlanganisa iyunithi yezinhlamvu zombhalo, ukuze akhe iyunithi yezinhlamvu yombhalo eyodwa eyengeziwe.

Isibonelo se-concatenation operator

Ifomula elandelayo isebenzisa i-opharetha yokuhlanganisa ukuze ihlanganise iyunithi yezinhlamvu zombhalo “SMITH" " futhi "John"

Ama-opharetha wokuqhathanisa we-Excel

Ama-opharetha wokuqhathanisa we-Excel asetshenziselwa definise izimo, njengalapho usebenzisa umsebenzi IF kwe-Excel. Laba opharetha basohlwini lwethebula elilandelayo:

Izibonelo zama-opharetha wokuqhathanisa

Amaspredishithi angezansi abonisa izibonelo zama-opharetha wokuqhathanisa asetshenziswe nomsebenzi IF kwe-Excel.

Ama-opharetha ayisethenjwa

Ama-opharetha angamareferensi e-Excel asetshenziswa lapho kubhekiselwa kububanzi ngaphakathi kwespredishithi. Ama-reference operators yilawa:

Izibonelo zama-reference operators

Isibonelo 1 - Umqhubi webanga le-Excel

Iseli C1 kusipredishithi esilandelayo libonisa i-opharetha yobubanzi, esetshenziselwa defiqeda isikhawu A1-B3. Ububanzi bube sebunikezwa umsebenzi SUM ye-Excel, engeza amanani kumaseli A1-B3 futhi ibuyisela inani 21.

Isibonelo sesi-2 – u-Opharetha wezinyunyana ze-Excel

Iseli C1 kuspredishithi esilandelayo sibonisa isisebenzi senyunyana, esisetshenziselwa definebanga elakhiwe ngamaseli kububanzi obubili A1-A3 e A1-B1. Ububanzi obuwumphumela bese bunikezwa umsebenzi SUM ku-Excel, ehlanganisa amanani ebangeni elihlanganisiwe futhi ibuyisele inani 12.

Qaphela ukuthi u-opharetha wezinyunyana ze-Excel akayibuyiseli inyunyana yezibalo yeqiniso, njengeseli A1, efakwe kuzo zombili izigaba A1-A3 e A1-B1 ibalwa kabili ekubalweni kwesamba).

Isibonelo sesi-3 – u-opharetha wempambanomgwaqo we-Excel

Iseli C1 kusipredishithi esilandelayo libonisa isisebenzisi se-intersection, esisetshenziselwa defiqeda ububanzi obudalwe kumaseli ezimpambanweni zobubanzi A1-A3 e A1-B2. Ububanzi obuwumphumela (ububanzi A1-A2) bese inikezwa umsebenzi we SUM ye-Excel, ehlanganisa amanani ebangeni eliphambanayo futhi ibuyisele inani 4.

Ulwazi olwengeziwe mayelana nama-opharetha e-Excel luyatholakala ku- Iwebhusayithi yeMicrosoft Office.

Imisebenzi ye-Excel

I-Excel inikeza inani elikhulu lemisebenzi eyakhelwe ngaphakathi engasetshenziswa ukwenza izibalo ezithile noma ukubuyisela ulwazi mayelana nedatha yesipredishithi. Le misebenzi ihlelwe yaba izigaba (umbhalo, ingqondo, izibalo, izibalo, njll.) ukukusiza ukuthi uthole umsebenzi owudingayo kumenyu ye-Excel.

Ngezansi sinikeza uhlu oluphelele lwemisebenzi ye-Excel, eqoqwe ngezigaba. Isixhumanisi ngasinye somsebenzi sizokuyisa ekhasini elinikezelwe, lapho uzothola khona incazelo yomsebenzi, nezibonelo zokusetshenziswa nemininingwane yamaphutha ajwayelekile.

Imisebenzi yezibalo ze-Excel:

Bala kanye Nemvamisa

• COUNT: Ibuyisela inombolo yamanani ezinombolo kusethi enikeziwe yamaseli noma amanani;
• COUNTA: Ibuyisela inombolo yokungezona izikhala kusethi enikeziwe yamaseli noma amanani;
• COUNTBLANK: ibuyisela inombolo yamaseli angenalutho ebangeni elinikeziwe;
• COUNTIF: ibuyisela inombolo yamaseli (yebanga elinikeziwe), anelisa umbandela othile;
• COUNTIFS: ibuyisela inombolo yamaseli (yebanga elinikeziwe) anelisa isethi yemibandela ethile (Okusha ku-Excel 2007);
• FREQUENCY: ibuyisela amalungu afanayo abonisa inani lamanani asuka kumalungu afanayo anikeziwe, awela phakathi kobubanzi obushiwo;

Isesha Okukhulu kanye Nokuncane

• MAX: Ibuyisela inani elikhulu kunawo wonke ohlwini lwezinombolo ezinikeziwe
• MAXA: Ibuyisela inani elikhulu kunawo wonke ohlwini lwamanani anikeziwe, ukubala umbhalo kanye nevelu enengqondo FALSE njengenani lika-0 nokubala inani elinengqondo TRUE njengenani elingu-1
• MAXIFS: Ibuyisela inani elikhulu kunawo wonke lisuka kusethi encane yamanani kuhlu olucacisiwe ngokusekelwe kumbandela wokunquma owodwa noma ngaphezulu. (Okusha okuvela ku-Excel 2019)
• MIN: Ibuyisela inani elincane kakhulu ohlwini lwezinombolo ezinikeziwe
• MINA: Ibuyisela inani elincane kakhulu ohlwini lwamanani anikeziwe, ibala umbhalo kanye nenani elinengqondo elithi FALSE njengenani elingu-0 futhi ibala inani elinengqondo elithi TRUE njengenani elingu-1
• MINIFS: Ibuyisela inani elincane kakhulu lisuka kusethi encane yamanani kuhlu olucacisiwe ngokusekelwe kumbandela wokunquma owodwa noma ngaphezulu. (Yini entsha ku-Excel 2019)
• LARGE: Ibuyisela inani le-Kth LARGEST ohlwini lwezinombolo ezinikeziwe, yenani elingu-K elinikeziwe
• SMALL: Ibuyisela inani le-Kth SMALLEST ohlwini lwezinombolo ezinikeziwe, ngenani elingu-K elinikeziwe

I-Medie

• AVERAGE: Ibuyisela isilinganiso sohlu lwezinombolo ezinikeziwe
• AVERAGEA: Ibuyisela isilinganiso sohlu lwezinombolo ezinikeziwe, ibala umbhalo kanye nenani elinengqondo elithi FALSE njengenani elingu-0, futhi ibala inani elinengqondo elithi TRUE njengenani elingu-1
• AVERAGEIF: Ibala isilinganiso samaseli kububanzi obunikeziwe, ahlangabezana nombandela onikeziwe (Okusha ku-Excel 2007)
• AVERAGEIFS: Ibala isilinganiso samaseli kububanzi obunikeziwe, ahlangabezana nemibandela eminingi (Okusha ku-Excel 2007)
• MEDIAN: Ibuyisela i-median (inani eliphakathi) lohlu lwezinombolo ezinikeziwe
• MODE: Ibala imodi (inani elivame kakhulu) yohlu olunikeziwe lwezinombolo (ithathelwe indawo umsebenzi Mode.Sngl ku-Excel 2010)
• MODE.SNGL: Ibala imodi (inani elivame kakhulu) lohlu lwezinombolo ezinikeziwe (Entsha ku-Excel 2010: ingena esikhundleni somsebenzi Mode)
• MODE.MULT: Ibuyisela uhlu oluqondile lwamanani avame kakhulu ohlwini noma ububanzi bedatha (Okusha ku-Excel 2010)
• GEOMEAN: Ibuyisela incazelo yejiyomethri yesethi yezinombolo enikeziwe
• HARMEAN: Ibuyisela incazelo ye-harmonic yesethi yezinombolo ezinikeziwe
• TRIMMEAN: Ibuyisela isilinganiso sangaphakathi sesethi yamanani enikeziwe

Izimvume

• PERMUT: Ibuyisela inombolo yezimvume zenani elinikeziwe lezinto
• PERMUTATIONA: Ibuyisela inombolo yezimvume yenani elinikeziwe lezinto (ngokuphindaphinda) ezingakhethwa entweni ephelele (Okusha ku-Excel 2013)

Izikhawu Zokuzethemba

• CONFIDENCE: Ibuyisela isikhawu sokuzithemba senani labantu, kusetshenziswa ukusabalalisa okuvamile (kufakwe esikhundleni somsebenzi we-Confidence.Norm ku-Excel 2010)
• CONFIDENCE.NORM: Ibuyisela isikhawu sokuzithemba senani labantu, kusetshenziswa ukusabalalisa okuvamile (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi Wokuqiniseka)
• CONFIDENCE.T: Ibuyisela isikhawu sokuzithemba senani labantu, kusetshenziswa ukusabalalisa kuka-t Komfundi (Okusha ku-Excel 2010)

Percentile kanye Quartiles

• PERCENTILE: Ibuyisela iphesenti le-Kth lamanani ebangeni elinikeziwe, lapho u-K esebangeni elingu-0 – 1 (okuhlanganisiwe) (Kufakwe esikhundleni somsebenzi we-Percentile.Inc ku-Excel 2010)
• PERCENTILE.INC: Ibuyisela iphesenti le-Kth lamanani ebangeni elinikeziwe, lapho u-K esebangeni elingu-0 – 1 (okuhlanganisiwe) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi we-Percentile)
• PERCENTILE.EXC: Ibuyisela iphesenti le-Kth lamanani ebangeni elinikeziwe, lapho u-K esebangeni elingu-0 – 1 (okukhethekile) (Okusha ku-Excel 2010)
• QUARTILE: Ibuyisela i-quartile eshiwo yesethi yezinombolo enikeziwe, ngokusekelwe kunani lephesenti elingu-0 – 1 (lihlanganisiwe) (Kufakwe esikhundleni somsebenzi we-Quartile.Inc ku-Excel 2010)
• QUARTILE.INC: Ibuyisela i-quartile eshiwo yesethi yezinombolo enikeziwe, ngokusekelwe kunani lephesenti 0 – 1 (lihlanganisiwe) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi we-Quartile)
• QUARTILE.EXC: Ibuyisela i-quartile eshiwo yesethi yezinombolo enikeziwe, ngokusekelwe kunani lephesenti elingu-0 – 1 (okukhethekile) (Okusha ku-Excel 2010)
• RANK: Ibuyisela izinga lezibalo yenani elinikeziwe, ngaphakathi kwamanani afanayo anikeziwe (athathelwe indawo umsebenzi we-Rank.Eq ku-Excel 2010)
• RANK.EQ: Ibuyisela imodi (inani elivame kakhulu) lohlu lwezinombolo ezinikeziwe (uma inani elingaphezu kwelilodwa linezinga elifanayo, izinga eliphezulu laleyo sethi liyabuyiswa) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi Wezinga)
• RANK.AVG: Ibuyisela izinga lezibalo yenani elinikeziwe, ngaphakathi kwamanani afanayo anikeziwe (uma amanani amaningi enezinga elifanayo, izinga elimaphakathi liyabuyiswa) (Okusha ku-Excel 2010)
• PERCENTRANK: Ibuyisela izinga yenani kusethi yedatha, njengephesenti (0 – 1 lihlanganisiwe) (Kufakwe esikhundleni somsebenzi we-Percentrank.Inc ku-Excel 2010)
• PERCENTRANK.INC: Ibuyisela izinga yenani kusethi yedatha, njengephesenti (0 – 1 lihlanganisiwe) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi we-Percentrank)
• PERCENTRANK.EXC: Ibuyisela izinga yenani kusethi yedatha, njengephesenti (ngaphandle kuka-0 – 1) (Okusha ku-Excel 2010)

Ukuchezuka nokuhluka

• AVEDEV: Ibuyisela isilinganiso sokuchezuka okuphelele kwamaphoyinti edatha ukusuka kuncazelo yawo
• DEVSQ: Ibuyisela isamba sezikwele zokuchezuka kwesethi yamaphoyinti edatha ukusuka kuncazelo yayo yesampula
• STDEV: Ibuyisela ukuchezuka okujwayelekile kwesethi yamanani enikeziwe (emele isampuli yabantu) (Kushintshwe umsebenzi we-St.Dev ku-Excel 2010)
• STDEV.S: Ibuyisela ukuchezuka okujwayelekile kwesethi yamanani enikeziwe (emele isampula lenani labantu) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi we-STDEV)
• STDEVA: Ibuyisela ukuchezuka okujwayelekile kwesethi yamanani enikeziwe (emele isampula lenani labantu), ibala umbhalo kanye nenani elinengqondo elithi FALSE njengenani elingu-0 futhi ibala inani elinengqondo elithi TRUE njengenani elingu-1
• STDEVP: Ibuyisela ukuchezuka okujwayelekile kwesethi yamanani enikeziwe (emele sonke isibalo sabantu) (Kufakwe esikhundleni somsebenzi we-StdPDev ku-Excel 2010)
• STDEV.P: Ibuyisela ukuchezuka okujwayelekile kwesethi yamanani enikeziwe (emele sonke isibalo sabantu) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi we-STDEV)
• STDEVPA: Ibuyisela ukuchezuka okujwayelekile kwesethi yamanani enikeziwe (emele sonke isibalo sabantu), ibala umbhalo kanye nenani elinengqondo elithi FALSE njengenani elingu-0 futhi ibala inani elinengqondo elithi TRUE njengenani elingu-1
• VAR: Ibuyisela ukuhluka kwesethi yamanani enikeziwe (emele isampula lenani labantu) (Kufakwe esikhundleni somsebenzi we-SVar ku-Excel 2010)
• VAR.S: Ibuyisela ukuhluka kwesethi yamanani enikeziwe (emele isampula yabantu) (Okusha ku-Excel 2010 - kungena esikhundleni somsebenzi we-Var)
• VARA: Ibuyisela ukuhluka kwesethi yamanani enikeziwe (emele isampula lenani labantu), ibala umbhalo kanye nenani elinengqondo elithi FALSE njengenani elingu-0 futhi ibala inani elinengqondo elithi TRUE njengenani elingu-1
• VARP: Ibuyisela ukuhluka kwesethi yamanani enikeziwe (emele sonke isibalo sabantu) (Kufakwe esikhundleni somsebenzi we-Var.P ku-Excel 2010)
• VAR.P: Ibuyisela ukuhluka kwesethi yamanani enikeziwe (emele sonke isibalo sabantu) (Okusha ku-Excel 2010 - kungena esikhundleni somsebenzi we-Varp)
• VARPA: Ibuyisela ukuhluka kwesethi yamanani enikeziwe (emele sonke isibalo sabantu), ibala umbhalo kanye nenani elinengqondo elithi FALSE njengenani elingu-0, futhi ibala inani elinengqondo elithi TRUE njengenani elingu-1
• COVAR: Ibuyisela i-covariance yabantu (okungukuthi isilinganiso semikhiqizo yokuchezuka kwepheya ngayinye phakathi kwamasethi edatha amabili anikeziwe) (Kufakwe esikhundleni somsebenzi we-Covariance.P ku-Excel 2010)
• COVARIANZA.P: Ibuyisela i-covariance yabantu (okungukuthi isilinganiso semikhiqizo yokuchezuka kwepheya ngayinye phakathi kwamasethi edatha amabili anikeziwe) (Okusha ku-Excel 2010: kungena esikhundleni somsebenzi we-Covar)
• COVARIANZA.S: Ibuyisela isampula ye-covariance (okungukuthi isilinganiso semikhiqizo yokuchezuka kwepheya ngayinye phakathi kwamasethi edatha amabili anikeziwe) (Okusha ku-Excel 2010)

Imisebenzi yokubikezela

• FORECAST: Ibikezela iphoyinti lesikhathi esizayo kumugqa wethrendi owumugqa ofakwe kusethi enikeziwe yamanani angu-x kanye no-y (okuthathelwa indawo umsebenzi FORECAST.LINEAR ku-Excel 2016)
• FORECAST.ETS: Isebenzisa i-algorithm ye-exponential smoothing ukubikezela inani lesikhathi esizayo phezu komugqa wesikhathi, ngokusekelwe ochungechungeni lwamanani akhona (Okusha ku-Excel 2016 - akutholakali ku-Excel 2016 ye-Mac)
• FORECAST.ETS.CONFINT: Ibuyisela isikhawu sokuzithemba senani lesibikezelo ngedethi ecacisiwe eqondiwe (Okusha ku-Excel 2016 – akutholakali ku-Excel 2016 ye-Mac)
• FORECAST.ETS.SEASONALITY: Ibuyisela ubude bephethini ephindayo etholwe yi-Excel yochungechunge lwesikhathi olushiwo (Okusha ku-Excel 2016 - akutholakali ku-Excel 2016 ye-Mac)
• FORECAST.ETS.STAT: Ibuyisela inani lezibalo mayelana nesibikezelo sochungechunge lwesikhathi (Okusha ku-Excel 2016 – akutholakali ku-Excel 2016 ye-Mac)
• FORECAST.LINEAR: Ibikezela iphoyinti lesikhathi esizayo kumugqa wethrendi ofakwe kusethi yamanani angu-x kanye no-y (Okusha ku-Excel 2016 (hhayi i-Excel 2016 ye-Mac) - ingena esikhundleni somsebenzi Wesibikezelo)
• INTERCEPT: Ibala umugqa wokuhlehla ofaneleka kakhulu, ngochungechunge lwamanani angu-x kanye no-y, ibuyisela inani lapho lo mugqa unqamula khona i-eksisi ka-y.
• LINEST: Ibuyisela ulwazi lwezibalo oluchaza ithrendi yomugqa olingana kahle kakhulu, ngochungechunge lwamanani angu-x kanye no-y
• SLOPE: Ibuyisela umthambeka womugqa wokuhlehla womugqa ngokusebenzisa isethi ethile yamanani angu-x kanye no-y
• TREND: Ibala umugqa wethrendi ngesethi ethile yamanani angu-y bese ibuyisela amanani engeziwe ka-y ngesethi enikeziwe yamanani amasha angu-x
• GROWTH: Ibuyisela izinombolo kuthrendi yokukhula komchazi, ngokusekelwe kusethi yamanani angu-x kanye no-y anikeziwe
• LOGEST: Ibuyisela amapharamitha wethrendi ye-exponential yesethi ethile yamanani angu-x kanye no-y
• STEYX: Ibuyisela iphutha elijwayelekile levelu engu-y ebikezelwe ku-x ngamunye emugqeni wokuhlehla kusethi enikeziwe yamanani angu-x kanye no-y

Ercole Palmeri