Articles

Ka'idojin Excel: Menene dabarun Excel da yadda ake amfani da su

Kalmar “Excel formulas” na iya nufin kowane haɗin gwiwa aiki di Excel da / ko Ayyukan Excel.

Ana shigar da dabarar Excel a cikin tantanin halitta ta hanyar buga alamar =, tare da masu aiki da/ko ayyuka da ake buƙata. Wannan na iya zama mai sauƙi kamar ƙari na asali (misali "= A1 + B1"), ko kuma yana iya zama haɗaɗɗiyar haɗin gwiwar masu aiki da Excel da ayyuka masu yawa na Excel.

Teburin Abubuwan Ciki: Jerin masu aiki da ayyuka

Masu aiki na Excel

Masu aiki na Excel suna yin ayyuka akan ƙimar lambobi, rubutu, ko nassoshin tantanin halitta. Akwai nau'ikan ma'aikatan Excel guda huɗu daban-daban.

Abin tambaya:

Masu aikin lissafi
Masu aiki da rubutu
Ma'aikatan kwatanta
Ma'aikatan bincike

Bari mu bayyana nau'ikan masu aiki guda huɗu:

Masu aikin lissafi

Ana nuna ma'aikatan lissafin Excel da tsarin da aka tantance su a cikin tebur mai zuwa:

Gabatar da masu aikin lissafi

Teburin da ke sama ya nuna cewa masu aiki da kaso da ƙa'idodi suna da fifiko mafi girma, sai kuma masu aikin ninkawa da rarrabawa, sa'an nan kuma masu aikin ƙari da ragi. Don haka, lokacin da ake kimanta tsarin Excel waɗanda ke ɗauke da ma'aikacin lissafi sama da ɗaya, ana fara tantance kaso da ma'aikata masu fa'ida, sannan kuma masu sarrafa ninka da rarrabawa. A ƙarshe, ana kimanta masu aiki da ƙari da ragi.

Tsarin da ake tantance ma'aikatan lissafi yana ba da babban bambanci ga sakamakon dabarar Excel. Koyaya, ana iya amfani da baƙaƙen ƙira don tilastawa sassan dabara da a fara tantancewa. Idan wani ɓangare na dabara yana ƙunshe a cikin baƙaƙen ƙira, ɓangaren ƙididdiga na ƙirar yana fifiko akan duk masu aiki da aka jera a sama. An kwatanta wannan a cikin misalai masu zuwa:

Misalai na masu aikin lissafi

Mai aiki da rubutu na Excel

Ma'aikacin haɗin gwiwa na Excel (wanda alamar & alama ke nunawa) yana haɗa igiyoyin rubutu, don ƙirƙirar ƙarin saitin rubutu guda ɗaya.

Misali na ma'aikacin haɗin gwiwa

Dabarar mai zuwa tana amfani da ma'aikacin haɗakarwa don haɗa igiyoyin rubutu "SMITH" " kuma "John"

Masu kwatancen Excel

Ana amfani da ma'aikatan kwatancen Excel don definise yanayin, kamar lokacin amfani da aikin IF na Excel. An jera waɗannan masu aiki a cikin tebur mai zuwa:

Misalai na masu aiki da kwatance

Rubutun da ke ƙasa suna nuna misalan masu aiki da kwatancen da aka yi amfani da su tare da aikin IF na Excel.

Ma'aikatan bincike

Ana amfani da ma'aikatan bincike na Excel lokacin da ake magana akan jeri a cikin maƙunsar rubutu. Ma'aikatan bincike sune:

Misalai na masu aiki da tunani

Misali 1 – Mai sarrafa kewayon Excel

Cell C1 a cikin maƙunsar rubutu mai zuwa yana nuna mai sarrafa kewayon, ana amfani dashi don defikawo karshen tazara A1-B3. Sannan ana ba da kewayon zuwa aikin SUM na Excel, wanda ke ƙara ƙima a cikin sel A1-B3 kuma ya mayar da darajar 21.

Misali 2-Ma'aikacin ƙungiyar Excel

Tantanin halitta C1 daga cikin maƙunsar bayanai masu zuwa yana nuna ma'aikacin ƙungiyar, wanda aka yi amfani dashi don define kewayon da ya ƙunshi sel a cikin jeri biyu A1-A3 e A1-B1. Ana ba da kewayon da aka samu zuwa aikin SUM a cikin Excel, wanda ke taƙaita ƙimar a cikin kewayon da aka haɗa kuma ya dawo da ƙimar 12.

Lura cewa ma'aikacin ƙungiyar Excel ba ya dawo da ƙungiyar lissafi ta gaskiya, kamar tantanin halitta A1, wanda aka haɗa a cikin duka jeri A1-A3 e A1-B1 ana kirga sau biyu a lissafin jimlar).

Misali 3 – Mai aiki da hanyar sadarwa ta Excel

Cell C1 a cikin maƙunsar rubutu mai zuwa yana nuna ma'aikacin haɗin gwiwa, wanda aka yi amfani dashi don defikawo karshen kewayon da aka ƙirƙira akan sel a mahadar sahun A1-A3 e A1-B2. Sakamakon kewayon (kewayon A1-A2) sannan aka kawota ga aikin SUM na Excel, wanda ke taƙaita ƙimar a cikin kewayon tsaka-tsaki kuma ya dawo da ƙimar 4.

Ana samun ƙarin bayani game da ma'aikatan Excel akan Gidan yanar gizon Microsoft Office.

Ayyukan Excel

Excel yana ba da babban adadin ginanniyar ayyuka waɗanda za a iya amfani da su don yin takamaiman ƙididdiga ko don dawo da bayanai game da bayanan maƙunsar bayanai. An tsara waɗannan ayyuka zuwa rukuni (rubutu, dabaru, lissafi, kididdiga, da sauransu) don taimaka muku gano aikin da kuke buƙata daga menu na Excel.

A ƙasa muna ba da cikakken jerin ayyukan Excel, waɗanda aka haɗa su ta rukuni. Kowane ɗayan hanyoyin haɗin yanar gizon zai kai ku zuwa shafin da aka keɓe, inda zaku sami bayanin aikin, tare da misalan amfani da cikakkun bayanai akan kurakuran gama gari.

Ayyukan ƙididdiga na Excel:

Ƙididdigewa da Mita

COUNT: Yana mayar da adadin ƙididdige ƙididdigewa a cikin saitin sel ko ƙimar da aka bayar;
COUNTAYana dawo da adadin waɗanda ba su da sarari a cikin saƙon sel ko ƙimar da aka bayar;
COUNTBLANK: yana dawo da adadin sel mara komai a cikin kewayon da aka bayar;
COUNTIF: ya dawo da adadin sel (na kewayon da aka ba), wanda ya gamsar da ma'aunin da aka bayar;
COUNTIFS: ya dawo da adadin sel (na kewayon da aka bayar) waɗanda suka gamsar da ƙayyadaddun ƙayyadaddun ma'auni (Sabo a cikin Excel 2007);
FREQUENCY: yana dawo da tsararru mai nuna adadin ƙima daga tsararrun da aka bayar, waɗanda suka faɗi cikin keɓaɓɓen kewayon;

Neman Mafi Girma da Mafi Girma

MAX: Yana dawo da ƙima mafi girma daga jerin lambobi da aka kawo
MAXAYana dawo da ƙima mafi girma daga jerin ƙimar da aka kawo, kirga rubutu da ƙimar ma'ana FALSE a matsayin ƙimar 0 da ƙirga ƙimar ma'ana TRUE a matsayin darajar 1
MAXIFS: Yana dawo da ƙima mafi girma daga juzu'in dabi'u a cikin ƙayyadadden jeri bisa ma'auni ɗaya ko fiye. (Sabo daga Excel 2019)
MIN: Yana dawo da mafi ƙanƙanta ƙima daga jerin lambobi da aka kawo
MINAYana dawo da mafi ƙarancin ƙima daga lissafin ƙimar da aka kawo, yana ƙirga rubutu da ƙimar ma'ana KARYA a matsayin ƙimar 0 kuma yana ƙirga ƙimar ma'ana GASKIYA azaman ƙimar 1.
MINIFS: Yana dawo da mafi ƙanƙanta ƙima daga juzu'in ƙima a cikin ƙayyadadden jeri bisa ma'auni ɗaya ko fiye. (Abin da ke sabo a cikin Excel 2019)
LARGEYana dawo da mafi girman Kth daga lissafin lambobi da aka kawo, don ƙimar K da aka bayar
SMALLYana dawo da KARAMAR Kth daga jerin lambobin da aka kawo, don ƙimar K

matsakaici

AVERAGEYana dawo da matsakaicin lissafin lambobi da aka kawo
AVERAGEAYana dawo da matsakaicin lissafin lambobi da aka kawo, yana ƙirga rubutu da ƙimar ma'ana KARYA a matsayin ƙimar 0, da ƙirga ƙimar ma'ana GASKIYA azaman ƙimar 1.
AVERAGEIF: Yana ƙididdige matsakaita na sel a cikin kewayon da aka bayar, waɗanda suka dace da ma'aunin da aka bayar (Sabo a cikin Excel 2007)
AVERAGEIFS: Yana ƙididdige matsakaicin sel a cikin kewayon da aka bayar, waɗanda suka dace da ma'auni da yawa (Sabo a cikin Excel 2007)
MEDIANYana dawo da matsakaici (ƙimar tsakiya) na lissafin da aka kawo
MODE: Yana ƙididdige yanayin (mafi yawan ƙima) na lissafin da aka bayar (wanda aikin ya maye gurbinsa Mode.Sngl a cikin Excel 2010)
MODE.SNGL: Yana ƙididdige yanayin (mafi yawan ƙima) na jerin lambobi da aka kawo (Sabo a cikin Excel 2010: ya maye gurbin aikin Mode)
MODE.MULTYana dawo da jeri a tsaye na mafi yawan dabi'u a cikin tsararraki ko kewayon bayanai (Sabo a cikin Excel 2010)
GEOMEAN: Yana dawo da ma'anar lissafi na saitin lambobi
HARMEANYana dawo da ma'anar jituwa na saitin lambobi da aka kawo
TRIMMEAN: Yana dawo da matsakaicin ciki na saitin ƙimar da aka bayar

Ƙa'ida

PERMUT: Yana dawo da adadin lamurra don adadin abubuwan da aka bayar
PERMUTATIONA: Yana mayar da adadin permutations don adadin abubuwan da aka bayar (tare da maimaitawa) waɗanda za a iya zaɓa daga jimillar abubuwa (Sabo a cikin Excel 2013)

Tsakanin Amincewa

CONFIDENCE: Yana dawo da tazarar amincewa ga ma'anar yawan jama'a, ta amfani da rarraba ta al'ada (maye gurbin ta da Confidence.Norm function in Excel 2010)
CONFIDENCE.NORMYana dawo da tazarar amincewa ga yawan jama'a, ta amfani da rarraba ta al'ada (Sabo a cikin Excel 2010: ya maye gurbin aikin Amincewa)
CONFIDENCE.T: Yana dawo da tazarar amincewa ga yawan jama'a, ta amfani da t-distribution na Student (Sabo a cikin Excel 2010)

Kashi kashi da Quartiles

PERCENTILEYana dawo da kashi Kth na ƙimar a cikin kewayon da aka bayar, inda K ke cikin kewayon 0 - 1 (haɗe) (Masanyawa da aikin Percentile.Inc a cikin Excel 2010)
PERCENTILE.INCYana dawo da ƙimar Kth na ƙimar a cikin kewayon da aka bayar, inda K ke cikin kewayon 0 - 1 (haɗe) (Sabo a cikin Excel 2010: ya maye gurbin aikin Kashi)
PERCENTILE.EXCYana dawo da ƙimar Kth na ƙimar a cikin kewayon da aka bayar, inda K ke cikin kewayon 0 - 1 (keɓe) (Sabo a cikin Excel 2010)
QUARTILEYana dawo da ƙayyadaddun ƙayyadaddun adadin adadin adadin da aka bayar, dangane da ƙimar kaso 0 – 1 (haɗe) (Maye gurbin da aikin Quartile.Inc a cikin Excel 2010)
QUARTILE.INCYana dawo da ƙayyadaddun ƙayyadaddun adadin adadin adadin da aka bayar, dangane da ƙimar kashi 0 – 1 (haɗe) (Sabo a cikin Excel 2010: ya maye gurbin aikin Quartile)
QUARTILE.EXCYana dawo da ƙayyadaddun ƙayyadaddun adadin adadin adadin da aka bayar, dangane da ƙimar 0 - 1 (keɓe) (Sabo a cikin Excel 2010)
RANK: Yana dawo da kididdigar kima na ƙimar da aka bayar, a cikin tsararrun ƙima (wanda aikin Rank.Eq ya maye gurbinsa a cikin Excel 2010)
RANK.EQYana dawo da yanayin (mafi yawan ƙima) na jerin lambobi da aka kawo (idan fiye da ɗaya suna da matsayi iri ɗaya, ana dawo da mafi girman wannan saitin) (Sabo a cikin Excel 2010: ya maye gurbin aikin Rank)
RANK.AVG: Yana dawo da ƙimar ƙididdiga na ƙimar da aka bayar, a cikin tsararrun ƙima (idan ƙimomi masu yawa suna da matsayi iri ɗaya, ana dawo da matsakaicin matsayi) (Sabo a cikin Excel 2010)
PERCENTRANK: Yana dawo da martabar ƙima a cikin saitin bayanai, a matsayin kashi (0 - 1 inclusive) (Masanya Percentrank.Inc a cikin Excel 2010)
PERCENTRANK.INC: Yana mayar da darajar kima a cikin saitin bayanai, a matsayin kashi (0 - 1 inclusive) (Sabo a cikin Excel 2010: ya maye gurbin aikin Percentrank)
PERCENTRANK.EXC: Yana mayar da darajar kima a cikin saitin bayanai, a matsayin kashi (ban da 0 - 1) (Sabo a cikin Excel 2010)

Ba'a da bambance-bambance

AVEDEV: Yana dawo da matsakaitan madaidaicin madaidaicin ma'aunin bayanai daga ma'anarsu
DEVSQ: Yana dawo da jimlar murabba'ai na karkatattun saitin bayanai daga ma'anar samfurin sa
STDEVYana dawo da daidaitattun daidaiton saitin dabi'u da aka kawo (wakiltan samfurin yawan jama'a) (Masanin aikin St.Dev a cikin Excel 2010)
STDEV.SYana dawo da daidaitattun daidaitattun saitin ƙimar da aka bayar (wakiltar samfurin yawan jama'a) (Sabo a cikin Excel 2010: ya maye gurbin aikin STDEV)
STDEVAYana dawo da daidaitattun daidaitattun saitin ƙima (wakiltar samfurin yawan jama'a), ƙidayar rubutu da ƙimar ma'ana KARYA azaman darajar 0 da ƙirga ƙimar ma'ana GASKIYA azaman ƙimar 1.
STDEVP: Yana dawo da daidaitattun daidaito na saitin dabi'u da aka bayar (wakiltar duka jama'a) (Masanin aikin StdPDev a cikin Excel 2010)
STDEV.PYana dawo da daidaitattun daidaitattun saitin dabi'u da aka bayar (wakiltar duka jama'a) (Sabo a cikin Excel 2010: ya maye gurbin aikin STDEV)
STDEVPA: Yana dawo da daidaitattun daidaitattun saiti na ƙimar da aka bayar (yana wakiltar gabaɗayan yawan jama'a), yana ƙirga rubutu da ƙimar ma'ana KARYA azaman darajar 0 da ƙirga ƙimar ma'ana GASKIYA azaman ƙimar 1
VARYana dawo da bambance-bambancen saitin dabi'u da aka bayar (wakiltar samfurin yawan jama'a) (Masanin aikin SVar a cikin Excel 2010)
VAR.SYana dawo da bambance-bambancen saitin dabi'u da aka bayar (wakiltar samfurin yawan jama'a) (Sabo a cikin Excel 2010 - ya maye gurbin aikin Var)
VARAYana dawo da bambance-bambancen saitin dabi'un da aka bayar (wakiltar samfurin yawan jama'a), ƙidaya rubutu da ƙimar ma'ana KARYA a matsayin ƙimar 0 da ƙirga ƙimar ma'ana GASKIYA azaman ƙimar 1.
VARPYana dawo da bambance-bambancen saitin dabi'u da aka bayar (wakiltar duka jama'a) (Masanin aikin Var.P a cikin Excel 2010)
VAR.P: Yana dawo da bambance-bambancen saitin dabi'u da aka bayar (wakiltar duka jama'a) (Sabo a cikin Excel 2010 - ya maye gurbin aikin Varp)
VARPAYana dawo da bambance-bambancen saitin dabi'u da aka bayar (wakiltar gaba dayan jama'a), kirga rubutu da ƙimar ma'ana KARYA a matsayin ƙimar 0, da ƙirga ƙimar ma'ana GASKIYA azaman darajar 1.
COVARYana dawo da daidaituwar yawan jama'a (watau matsakaicin samfuran rarrabuwar kawuna ga kowane nau'in biyu a cikin saiti biyu da aka bayar) (Masanin aikin Covariance.P a cikin Excel 2010)
COVARIANZA.PYana dawo da haɗin kai na yawan jama'a (watau matsakaicin samfuran rarrabuwa na kowane nau'in biyu a cikin saiti biyu da aka bayar) (Sabo a cikin Excel 2010: ya maye gurbin aikin Covar)
COVARIANZA.SYana dawo da samfurin haɗin gwiwa (watau matsakaicin samfuran rarrabuwa na kowane nau'in biyu a cikin saitin bayanai guda biyu) (Sabo a cikin Excel 2010)

Ayyukan tsinkaya

FORECAST: Yana tsinkaya batu na gaba akan layin layi na layi wanda ya dace da ƙayyadaddun ƙimar x da y (wanda aikin ya maye gurbinsa. FORECAST.LINEAR a cikin Excel 2016)
FORECAST.ETS: Yana amfani da ma'anar smoothing algorithm don tsinkayar ƙimar nan gaba akan lokaci, dangane da jerin dabi'un data kasance (Sabo a cikin Excel 2016 - babu a cikin Excel 2016 don Mac)
FORECAST.ETS.CONFINTYana dawo da tazarar amincewa don ƙimar tsinkaya a ƙayyadadden ranar da aka yi niyya (Sabo a cikin Excel 2016 - babu a cikin Excel 2016 don Mac)
FORECAST.ETS.SEASONALITYYana dawo da tsawon tsarin maimaitawa da Excel ya gano don takamaiman jerin lokaci (Sabo a cikin Excel 2016 - babu a cikin Excel 2016 don Mac)
FORECAST.ETS.STATYana dawo da ƙimar ƙididdiga game da hasashen jerin lokaci (Sabo a cikin Excel 2016 - babu a cikin Excel 2016 don Mac)
FORECAST.LINEAR: Yana tsinkayar wani batu na gaba akan layin layi na layi wanda ya dace da saiti na ƙimar x da y (Sabo a cikin Excel 2016 (ba Excel 2016 don Mac) - ya maye gurbin aikin Hasashen)
INTERCEPT: Yana ƙididdige layin koma bayan da ya fi dacewa, ta hanyar jerin ƙimar x da y, yana dawo da ƙimar da wannan layin ya saɓa wa axis y.
LINESTYana dawo da bayanan ƙididdiga waɗanda ke bayyana yanayin mafi kyawun layin da ya dace, ta jerin ƙimar x da y
SLOPEYana dawo da gangaren layin koma baya ta hanyar da aka bayar na ƙimar x da y
TREND: Yana ƙididdige layin ci gaba ta hanyar saiti na ƙimar y da aka bayar kuma ya dawo da ƙarin ƙimar y don wani sabin sabbin ƙimar x
GROWTH: Yana dawo da lambobi a cikin yanayin girma mai ma'ana, dangane da saitin ƙimar x da y da aka bayar.
LOGESTYana dawo da ma'auni na yanayin juzu'i don abin da aka bayar na ƙimar x da y
STEYXYana dawo da daidaitaccen kuskure na ƙimar y da aka annabta ga kowane x a cikin layin koma baya don wani sashe na ƙimar x da y