Imisebenzi Yezibalo ze-Excel: Okokufundisa Okunezibonelo, Ingxenye Yesithathu

I-Excel inikeza ububanzi obubanzi bemisebenzi yezibalo eyenza izibalo kusukela encazelweni kuya kweyinkimbinkimbi yokusabalalisa izibalo nemisebenzi yomugqa wethrendi.

Kulesi sihloko sizocubungula ngokujulile imisebenzi yezibalo ye-Excel yokubala imisebenzi yomugqa wethrendi.

Sicela uqaphele ukuthi eminye imisebenzi yezibalo yethulwe ezinguqulweni zakamuva ze-Excel ngakho-ke ayitholakali kuzinguqulo ezindala.

Isikhathi sokufunda esilinganiselwe: 12 imizuzu

Imisebenzi yomugqa wethrendi

Forecast

Umsebenzi Wesibikezelo se-Excel ubikezela iphoyinti lesikhathi esizayo kumugqa wethrendi olingana nesethi ethile yamanani angu-x kanye no-y.

-syntax

= FORECAST( x, known_y's, known_x's )

izifundo

• x: Inani elingu-x lenombolo ofuna ukubikezela ngalo inani elisha lika-y.
• known_y's: Uhlu lwamanani aziwayo ka-y
• known_x's: Uhlu lwamanani aziwayo angu-x

Qaphela ukuthi ubude bamalungu afanayo we known_x kufanele kufane nalokho kwe known_y kanye nokwehluka kwe known_x akumele kube nguziro.

Ngokwesibonelo

Kuspredishithi esilandelayo, umsebenzi FORECAST I-Excel isetshenziselwa ukubikezela iphuzu elingeziwe emugqeni oqondile ofaneleka kakhulu ngochungechunge lwamanani aziwayo okuthi x kanye no-y (agcinwe kumaseli F2:F7 kanye ne-G2:G7).

Njengoba kuboniswe kuseli F7 lesipredishithi, umsebenzi wokubala inani elilindelekile lika-y kokuthi x=7 ungu :=FORECAST( 7, G2:G7, F2:F7 )

Lokhu kunikeza umphumela 32.666667 .

Intercept

Phakathi kwemisebenzi yokubikezela ye-Excel sithola Intercept. Umsebenzi we-Excel's Intercept ubala i-intercept (inani elisempambana we-axis ka-y) yomugqa wokuhlehla womugqa phakathi kwesethi yamanani ethi x kanye no-y.

-syntax

= INTERCEPT( known_y's, known_x's )

izifundo

• known_y's: Uhlu lwamanani aziwayo ka-y
• known_x's: Uhlu lwamanani aziwayo angu-x

Qaphela ukuthi ubude bamalungu afanayo we known_x kufanele kufane nalokho kwe known_y kanye nokwehluka kwe known_x akumele kube nguziro.

Ngokwesibonelo

Isipredishithi esilandelayo sibonisa isibonelo somsebenzi Intercept ye-Excel isetshenziselwa ukubala iphuzu lapho umugqa wokuhlehla womugqa udlulele ku- known_x futhi i known_y (kuhlu kumaseli F2:F7 kanye ne-G2:G7) inqamula i-axis ka-y.

Le known_x futhi i known_y afakwe kugrafu kuspredishithi.

Njengoba kuboniswe kuseli F9 lesipredishithi, ifomula yomsebenzi we-Intercept ithi :=INTERCEPT( G2:G7, F2:F7 )

okunikeza umphumela 2.4 .

Slope

Omunye umsebenzi wokubikezela othakazelisa kakhulu yi-Slope (Slope) I-Excel ibala umthambeka womugqa wokuhlehla womugqa ngokusebenzisa isethi ethile yamanani angu-x kanye no-y.

I-syntax yomsebenzi ithi:

-syntax

= SLOPE( known_y's, known_x's )

izifundo

• known_y's: Uhlu lwamanani aziwayo ka-y
• known_x's: Uhlu lwamanani aziwayo angu-x

Qaphela ukuthi ubude bamalungu afanayo we known_x kufanele kufane nalokho kwe known_y kanye nokwehluka kwe known_x akumele kube nguziro.

Ngokwesibonelo

Isipredishithi esilandelayo sibonisa isibonelo somsebenzi Slope (umthambeka) we-Excel usetshenziselwa ukubala umthambeka womugqa wokuhlehla womugqa ngokusebenzisa i- known_x kanye ne- known_y, kumaseli F2:F7 kanye ne-G2:G7.

Le known_x futhi i known_y afakwe kugrafu kuspredishithi.

Njengoba kuboniswe kuseli F9 lesipredishithi, ifomula yomsebenzi we-Intercept ithi :=SLOPE( G2:G7, F2:F7 )

okunikeza umphumela 4.628571429.

Trend

Umsebenzi wokubikezela we-Excel othakazelisa kakhulu yi- ITHRENDI I-Excel (Ithrendi) ibala umugqa wethrendi omugqa ngokusebenzisa isethi enikeziwe yamanani angu-y kanye (ngokuzithandela), isethi enikeziwe yamanani angu-x.

Umsebenzi ube usunweba umugqa wethrendi ukuze ubale amanani engeziwe angu-y ngesethi eyengeziwe yamanani amasha angu-x.

I-syntax yomsebenzi ithi:

-syntax

= TREND( known_y's, [known_x's], [new_x's], [const] )

izifundo

• known_y's: Uhlu lwamanani aziwayo ka-y
• [known_x's]: Ilungu elifanayo elilodwa noma ngaphezulu amanani aziwayo x. Lena i-agumenti yokuzikhethela ukuthi, uma ihlinzekiwe, kufanele ilingane nobude besethi ye known_y's. Uma ishiywa, isethi ye- [known_x's] ithatha inani {1, 2, 3, …}.
• [okusha_x]: I-agumenti yokuzikhethela, ehlinzeka ngohlelo olulodwa noma ngaphezulu wenani lezinombolo elimele isethi yamanani amasha angu-x, ofuna ukubala kuwo amanani ka-y amasha ahambisanayo. Uhlu ngalunye lwe-okusha_x] kufanele ifane nohlu lwe [known_x's]. Uma ingxabano [okusha_x] iyeqiwa, isethwe ukuze ilingane [known_x's] .
• [izindleko]: I-agumenti enengqondo ekhethwayo ecacisa ukuthi u-'b' ongaguquki, kuzibalo zomugqa y = m x + b , kumele ziphoqwe ukuthi zilingane noziro. Mina [izindleko] IQINISO (noma uma le mpikiswano ikhishiwe) u-b ongashintshi uphathwa ngendlela evamile;
• Mina [izindleko] uthi AMANGA i-b engaguquki isethwe ukuze ithi 0 bese isibalo somugqa oqondile sibe y = mx .

Ngokwesibonelo

Kuspredishithi esilandelayo, umsebenzi we-Excel Trend usetshenziselwa ukunweba uchungechunge lwamanani angu-x kanye no-y atholakala emugqeni oqondile y = 2x + 10. Amanani aziwayo okuthi x kanye no-y agcinwa kumaseli A2-B5 we i-spreadsheet futhi ziboniswa kugrafu yesipredishithi.

Qaphela ukuthi akubalulekile ukuthi amaphuzu anikeziwe alingane ncamashi nomugqa oqondile y = 2x + 10 (yize kulesi sibonelo ahambisana nawo). Umsebenzi we-Excel's Trend uzothola umugqa ofaneleka kakhulu wanoma iyiphi isethi yamanani owanikezayo.

Umsebenzi we-Trend usebenzisa indlela yezikwele ezincane kakhulu ukuze uthole umugqa ofaneleka kakhulu bese uwusebenzisela ukubala amanani amasha ka-y amanani amasha ka-x anikeziwe.

Kulesi sibonelo, amanani we- [okusha_x] agcinwe kumaseli A8-A10, futhi umsebenzi Wethrendi we-Excel usetshenzisiwe, kumaseli B8-B10, ukuze kutholwe amanani amasha ahambisanayo ka-y. Njengoba kukhonjisiwe kubha yefomula, ifomula ithi := TREND( B2:B5, A2:A5, A8:A10 )

Uyabona ukuthi umsebenzi we-Trend kubha yefomula uvalelwe kuzikaki { }. Lokhu kukhombisa ukuthi umsebenzi ufakwe njenge uhlu lwefomula .

Growth

Phakathi kwemisebenzi yokubikezela ye-Excel sithola Growth. Umsebenzi Growth I-Excel ibala ikhevu yokukhula komchazi ngokusebenzisa isethi ethile yamanani angu-y kanye (ongakukhetha), isethi eyodwa noma ngaphezulu amanani angu-x. Umsebenzi ube usunweba ijika ukuze ubale amanani engeziwe ka-y ngesethi eyengeziwe yamanani amasha ka-x.

I-syntax yomsebenzi ithi:

-syntax

= GROWTH( known_y's, [known_x's], [new_x's], [const] )

izifundo

• known_y's: Uhlu lwamanani aziwayo ka-y
• [known_x's]: Ilungu elifanayo elilodwa noma ngaphezulu amanani aziwayo x. Lena i-agumenti yokuzikhethela ukuthi, uma ihlinzekiwe, kufanele ilingane nobude besethi ye known_y's. Uma ishiywa, isethi ye- [known_x's] ithatha inani {1, 2, 3, …}.
• [okusha_x]: Isethi yamanani amasha angu-x, lapho umsebenzi ubala amanani amasha ahambisanayo ka-y. Uma ishiywa, kucatshangwa ukuthi isethi ye- [okusha_x] ilingana neka [known_x's] futhi umsebenzi ubuyisela amanani ka-y alele kukhevu yokukhula komchazi obaliwe.
• [izindleko]: I-agumenti enengqondo ekhethwayo ecacisa ukuthi u-'b' ongaguquki, kuzibalo zomugqa y = b * m^x , kumele ziphoqwe ukuthi zilingane no-1. Uma [izindleko] IQINISO (noma uma le mpikiswano ikhishiwe) u-b ongashintshi uphathwa ngendlela evamile; Mina [izindleko] uthi AMANGA i-b engaguquki isethwe ukuze ithi 1 bese isibalo somugqa oqondile sibe y = mx .

Ngokwesibonelo

Kuspredishithi esilandelayo, umsebenzi wokukhula we-Excel usetshenziselwa ukunweba uchungechunge lwamanani okuthi x kanye no-y alele kukhevu yokukhula komchazi y = 5 * 2^x. Lokhu kugcinwa kumaseli A2-B5 wespredishithi futhi avela eshadini lesipredishithi.

Umsebenzi Wokukhula ubala ikhevu yokukhula ehambisana kangcono namanani aziwayo ka-x kanye no-y anikeziwe. Kulesi sibonelo esilula, ijika elifaneleka kakhulu yijika lomchazi y = 5 * 2^x.

Uma i-Excel ibala isibalo sekhevu yokukhula komchazi, ingayisebenzisela ukubala amanani amasha ka-y amanani amasha ka-x anikezwe kumaseli A8-A10.

Kulesi sibonelo, amanani we- [new_x's] agcinwe kumaseli A8-A10 kanye nomsebenzi Growth ye-Excel ifakwe kumaseli B8-B10. Njengoba kukhonjisiwe kubha yefomula, ifomula yalokhu ithi:=Growth( B2:B5, A2:A5, A8:A10 )

Ungabona ukuthi umsebenzi Wokukhula kubha yefomula uvalelwe kuzikaki { }. Lokhu kukhombisa ukuthi umsebenzi ufakwe njenge uhlu lwefomula .

Qaphela ukuthi nakuba amaphuzu akusibonelo esingenhla elingana ncamashi nejika y = 5 * 2^x, lokhu akubalulekile. Umsebenzi Growth I-Excel izothola ijika elifaneleka kakhulu kunoma iyiphi isethi yamanani owanikezayo.

Imisebenzi yezezimali

Effect

Umsebenzi Effect I-Excel ibuyisela inani lenzalo lonyaka elisebenzayo lenani lenzalo elinikeziwe kanye nenani elinikeziwe lezikhathi ezihlanganisiwe ngonyaka.

Izinga lenzalo lonyaka elisebenzayo

Izinga lenzalo lonyaka elisebenzayo liyisilinganiso senzalo esihlanganisa ukwenza imali yenzalo futhi ngokuvamile sisetshenziselwa ukuqhathanisa imali mboleko yezezimali nemibandela ehlukene yokwenza imali.

Izinga lenzalo lonyaka elisebenzayo libalwa kusetshenziswa isibalo esilandelayo:

ijuba nominal_rate isilinganiso senzalo esivamile e npery inombolo yezikhathi zokuhlanganisa ngonyaka.

I-syntax yomsebenzi ithi:

-syntax

= EFFECT( nominal_rate, npery )

izifundo

• nominal_rate: Izinga lenzalo elivamile (kufanele libe inani lenombolo phakathi kuka-0 no-1)
• npery: Inombolo yezikhathi zokuhlanganisa ngonyaka (kumele kube inombolo ephozithivu).

Ngokwesibonelo

Isipredishithi esilandelayo sibonisa izibonelo ezintathu zomsebenzi we-Excel Effect:

Uma umphumela womsebenzi Effect ibonisa njengedesimali noma ibonisa u-0%, zombili lezi zinkinga kungenzeka zibangelwa ukufometha kweseli eliqukethe umsebenzi Effect.

Ngakho-ke inkinga ingaxazululwa ngokufometha iseli ngamaphesenti, ngezindawo zamadesimali.

Ukwenza lokhu:

1. Khetha amaseli ozowafometha njengephesenti.
2. Vula ibhokisi lengxoxo elithi “Fometha Amaseli” usebenzisa enye yezindlela ezilandelayo:
   • Chofoza kwesokudla iseli ekhethiwe noma ububanzi bese ukhetha inketho Fometha Amaseli... kusuka kumenyu yokuqukethwe;
   • Chofoza Isiqalisi Sebhokisi Lengxoxo ekuqoqeni izinombolo kuthebhu Ikhaya I-Excel Ribbon;
   • Sebenzisa isinqamuleli sekhibhodi I-CTRL-1 (okungukuthi khetha ukhiye we-CTRL futhi, uwubambe phansi, khetha ukhiye "1" (owodwa)).
3. Ebhokisini lebhokisi elithi “Fometha Amaseli”:
   • Qinisekisa ikhadi INumero phezulu kwebhokisi lengxoxo kukhethiwe.
   • Khetha Iphesenti ohlwini Isigaba ohlangothini lwesobunxele lwebhokisi lengxoxo .Lokhu kuzoveza izinketho ezengeziwe kwesokudla sebhokisi lokuhlola, okukuvumela ukuthi ukhethe inani lezindawo zamadesimali ofuna ukuvela.
   • Uma usukhethile inombolo yezindawo zamadesimali ofuna ukuzibonisa, chofoza OK .

Nominal

Umsebenzi Nominal I-Excel ibuyisela inani lenzalo elivamile lenani lenzalo elisebenzayo elinikeziwe kanye nenani elinikeziwe lezikhathi ezihlanganisiwe ngonyaka.

I-syntax yomsebenzi ithi:

-syntax

= NOMINAL( effect_rate, npery )

izifundo

• effect_rate: Izinga lenzalo elisebenzayo (inani lenombolo eliphakathi kuka-0 no-1).
• npery: Inombolo yezikhathi zokuhlanganisa ngonyaka (kumele kube inombolo ephozithivu).

Ngokwesibonelo

Kuspredishithi esilandelayo, umsebenzi Nominal ye-Excel isetshenziselwa ukubala izinga lenzalo lemalimboleko emithathu enemibandela ehlukene.

Ercole Palmeri