Ayyukan Lissafi na Excel: Koyawa tare da Misalai, Sashe na Uku

Excel yana ba da ayyuka masu yawa na ƙididdiga waɗanda ke yin ƙididdiga daga ma'ana zuwa ƙarin hadaddun rarraba ƙididdiga da ayyukan layi na zamani.

A cikin wannan labarin za mu zurfafa zurfafa cikin ayyukan ƙididdiga na Excel don ƙididdige ayyukan layin da ake yi.

Lura cewa an gabatar da wasu ayyuka na ƙididdiga a cikin sassan Excel na baya-bayan nan don haka ba a samun su a cikin tsoffin juzu'in.

Kiyasta lokacin karantawa: 12 minti

Ayyukan Trendline

Forecast

Ayyukan Hasashen Excel yana annabta batu na gaba akan layin layi na layi wanda ya dace da ƙayyadaddun sa na ƙimar x da y.

ginin kalma

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

batutuwa

• x: Ƙimar x-lamba wanda kake son yin hasashen sabon ƙimar y.
• known_y's: Tsari na sanannun ƙima y
• known_x's: Tsari na sanannun ƙimar x

Lura cewa tsawon tsararru na known_x dole ne ya zama daidai da na known_y da bambancin known_x ba sai ya zama sifili ba.

misali

A cikin maƙunsar rubutu mai zuwa, aikin FORECAST Ana amfani da Excel don tsinkayar ƙarin batu tare da madaidaiciyar layi mafi dacewa ta hanyar jerin sanannun ƙimar x da y (an adana su a cikin sel F2: F7 da G2: G7).

Kamar yadda aka nuna a cikin tantanin halitta F7 na maƙunsar bayanai, aikin don ƙididdige ƙimar y da ake tsammani a x=7 shine :=FORECAST( 7, G2:G7, F2:F7 )

Wannan yana ba da sakamakon 32.666667 .

Intercept

Daga cikin ayyukan tsinkaya na Excel mun sami Intercept. Ayyukan Intercept na Excel yana ƙididdige tsangwama (ƙimar a tsakar y-axis) na layin koma baya a kan wani sashe na ƙimar x da y.

ginin kalma

= INTERCEPT( known_y's, known_x's )

batutuwa

• known_y's: Tsari na sanannun ƙima y
• known_x's: Tsari na sanannun ƙimar x

Lura cewa tsawon tsararru na known_x dole ne ya zama daidai da na known_y da bambancin known_x ba sai ya zama sifili ba.

misali

Fayil na gaba yana nuna misalin aikin Intercept na Excel da aka yi amfani da shi don ƙididdige wurin inda layin koma baya na layi ta hanyar known_x da kuma known_y (wanda aka jera a cikin sel F2:F7 da G2:G7) sun haɗu da y-axis.

Le known_x da kuma known_y Ana ƙira a kan jadawali a cikin maƙunsar rubutu.

Kamar yadda aka nuna a cell F9 na maƙunsar bayanai, dabarar aikin Intercept ita ce :=INTERCEPT( G2:G7, F2:F7 )

wanda ke ba da sakamakon 2.4 .

Slope

Wani aikin tsinkaya mai ban sha'awa shine gangare (Slope) Excel yana ƙididdige gangaren layin koma baya ta hanyar da aka bayar na ƙimar x da y.

Ma'anar aikin shine:

ginin kalma

= SLOPE( known_y's, known_x's )

batutuwa

• known_y's: Tsari na sanannun ƙima y
• known_x's: Tsari na sanannun ƙimar x

Lura cewa tsawon tsararru na known_x dole ne ya zama daidai da na known_y da bambancin known_x ba sai ya zama sifili ba.

misali

Fayil na gaba yana nuna misalin aikin Slope (slope) na Excel da aka yi amfani da shi don ƙididdige gangaren layin koma baya ta hanyar known_x da kuma known_y, a cikin sel F2:F7 da G2:G7.

Le known_x da kuma known_y Ana ƙira a kan jadawali a cikin maƙunsar rubutu.

Kamar yadda aka nuna a cell F9 na maƙunsar bayanai, dabarar aikin Intercept ita ce :=SLOPE( G2:G7, F2:F7 )

wanda ke ba da sakamakon 4.628571429.

Trend

Wani aiki mai ban sha'awa na hasashen Excel shine GASKIYA Excel (Trend) yana ƙididdige layin layi na layi ta hanyar da aka ba da ƙimar y da (na zaɓi), saitin ƙimar x.

Sa'an nan aikin ya tsawaita layin layi don ƙididdige ƙarin ƙimar y don ƙarin saiti na sabbin ƙimar x.

Ma'anar aikin shine:

ginin kalma

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

batutuwa

• known_y's: Tsari na sanannun ƙima y
• [known_x's]: Tsari ɗaya ko fiye na sanannun ƙimar x. Wannan hujja ce ta zaɓi wanda, idan an kawo shi, yakamata ya zama tsayi ɗaya da na saitin known_y's. Idan an tsallake, saitin [known_x's] yana ɗaukar ƙimar {1, 2, 3, …}.
• [new_x]: Hujja ta zaɓi, tana ba da jeri ɗaya ko fiye na ƙimar lambobi waɗanda ke wakiltar saitin sabbin ƙimar x, waɗanda kuke son ƙididdige sabbin ƙimar y daidai. Kowane tsari na [new_x] ya kamata ya dace da tsararrun [known_x's]. Idan hujja [new_x] an tsallake shi, an saita shi daidai [known_x's] .
• [farashi]: Tabbatacciyar hujjar ma'ana ta zaɓin da ke ƙayyadaddun ko akai-akai 'b', a cikin ma'auni y = m x + b , dole ne a tilasta masa ya zama daidai da sifili. Kai [kudin] GASKIYA ne (ko kuma idan an bar wannan hujja) akai-akai b ana kula da shi;
• Kai [kudin] KARYA ne akai b an saita shi zuwa 0 kuma madaidaicin layi ya zama y = mx .

misali

A cikin maɓalli mai zuwa, ana amfani da aikin Trend na Excel don tsawaita jerin ƙimar x da y waɗanda ke kan layi madaidaiciya y = 2x + 10. An adana sanannun ƙimar x da y a cikin sel A2-B5 na maƙunsar bayanai kuma ana nuna su a cikin jadawali.

Lura cewa ba mahimmanci ba ne cewa abubuwan da aka bayar sun dace daidai tare da madaidaiciyar layi y = 2x + 10 (ko da yake a cikin wannan misalin suna yin). Ayyukan Trend na Excel zai sami mafi kyawun layi don kowane saiti na ƙimar da kuka bayar.

Ayyukan Trend yana amfani da mafi ƙarancin hanyar murabba'i don nemo mafi kyawun layi mai dacewa sannan yayi amfani da shi don ƙididdige sabbin ƙimar y don sabbin ƙimar x da aka bayar.

A cikin wannan misali, darajar [new_x] ana adana su a cikin sel A8-A10, kuma an yi amfani da aikin Trend na Excel, a cikin sel B8-B10, don nemo sabbin dabi'u y masu dacewa. Kamar yadda aka nuna a mashaya dabara, dabarar ita ce : = TREND ( B2: B5, A2: A5, A8: A10 )

Kuna ganin cewa aikin Trend a cikin ma'aunin dabara yana kewaye da takalmin gyaran kafa {}. Wannan yana nuna cewa an shigar da aikin azaman dabarar tsararru .

Growth

Daga cikin ayyukan tsinkaya na Excel mun sami Growth. Aikin Growth Excel yana ƙididdige juzu'in girma mai faɗi ta hanyar da aka bayar na ƙimar y da (na zaɓi), saiti ɗaya ko fiye na ƙimar x. Sa'an nan aikin ya tsawaita lanƙwasa don ƙididdige ƙarin ƙimar y don ƙarin saitin sabbin ƙimar x.

Ma'anar aikin shine:

ginin kalma

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

batutuwa

• known_y's: Tsari na sanannun ƙima y
• [known_x's]: Tsari ɗaya ko fiye na sanannun ƙimar x. Wannan hujja ce ta zaɓi wanda, idan an kawo shi, yakamata ya zama tsayi ɗaya da na saitin known_y's. Idan an tsallake, saitin [known_x's] yana ɗaukar ƙimar {1, 2, 3, …}.
• [new_x]: Saitin sabbin dabi'u x, wanda aikin ke ƙididdige sabbin ƙimar y masu dacewa. Idan an cire shi, ana ɗauka cewa saitin [new_x] daidai yake da na [known_x's] kuma aikin yana dawo da ƙimar y waɗanda ke kan ƙididdige ƙimar girma.
• [farashi]: Tabbatacciyar hujjar ma'ana ta zaɓin da ke ƙayyadaddun ko akai-akai 'b', a cikin ma'auni y = b * m^x , dole ne a tilasta masa ya zama daidai da 1. Idan [kudin] GASKIYA ne (ko kuma idan an bar wannan hujja) akai-akai b ana kula da shi; Kai [kudin] KARYA ne akai b an saita shi zuwa 1 kuma madaidaicin layi ya zama y = mx .

misali

A cikin maƙunsar bayanai masu zuwa, ana amfani da aikin haɓaka na Excel don tsawaita jerin ƙimar x da y waɗanda ke kan madaidaicin girma y = 5 * 2^x. Ana adana waɗannan a cikin sel A2-B5 na maƙunsar bayanai kuma suna bayyana a cikin ginshiƙi.

Ayyukan Ci gaban yana ƙididdige madaidaicin girman girman da ya fi dacewa da sanannun ƙimar x da y da aka bayar. A cikin wannan misali mai sauƙi, mafi kyawun lanƙwasa mai dacewa shine madaidaicin lanƙwasa y = 5 * 2 ^ x.

Da zarar Excel ya ƙididdige ma'auni na girman girman girma, zai iya amfani da shi don ƙididdige sababbin ƙimar y don sababbin ƙimar x da aka samar a cikin sel A8-A10.

A cikin wannan misali, darajar [new_x's] ana adana su a cikin sel A8-A10 da aikin Growth na Excel an saka shi cikin sel B8-B10. Kamar yadda aka nuna a sandar dabara, dabarar wannan ita ce:=Growth( B2:B5, A2:A5, A8:A10)

Kuna iya ganin cewa aikin Girma a cikin ma'aunin dabara yana kewaye da takalmin gyaran kafa { }. Wannan yana nuna cewa an shigar da aikin azaman dabarar tsararru .

Lura cewa ko da yake maki a cikin misalin da ke sama sun dace daidai tare da lanƙwasa y = 5 * 2 ^ x, wannan ba mahimmanci ba ne. Aikin Growth Excel zai sami mafi kyawun lanƙwasa don kowane saiti na ƙimar da kuka bayar.

Ayyukan kudi

Effect

Aiki Effect Excel yana dawo da ingantaccen adadin ribar shekara-shekara don adadin ribar da aka bayar da kuma adadin adadin lokuta masu haɗawa a kowace shekara.

Ƙimar riba mai tasiri ta shekara

Adadin riba mai tasiri na shekara-shekara shine ma'aunin riba wanda ya haɗa da babban riba kuma galibi ana amfani dashi don kwatanta lamuni na kuɗi tare da sharuɗɗan jari daban-daban.

Ana ƙididdige ƙimar riba mai tasiri ta shekara ta amfani da ma'auni mai zuwa:

kurciya nominal_rate shi ne kudin ruwa na yau da kullun e npery shine adadin lokuta masu haɗuwa a kowace shekara.

Ma'anar aikin shine:

ginin kalma

= EFFECT( nominal_rate, npery )

batutuwa

• nominal_rate: Matsakaicin riba na ƙima (dole ne ya zama ƙimar lamba tsakanin 0 da 1)
• npery: Yawan lokuta masu haɗawa a kowace shekara (dole ne ya zama madaidaicin lamba).

misali

Fayil ɗin mai zuwa yana nuna misalai uku na aikin Tasirin Excel:

Idan sakamakon aikin Effect yana nunawa a matsayin ƙima ko yana nuna 0%, duka waɗannan batutuwan biyun suna iya yiwuwa saboda tsara tantanin halitta da ke ɗauke da aikin. Effect.

Don haka ana iya magance matsalar ta hanyar tsara tantanin halitta a kashi, tare da wurare goma.

Don yin wannan:

1. Zaɓi sel don tsarawa azaman kashi.
2. Bude akwatin maganganu "Format Cells" ta amfani da ɗayan hanyoyin masu zuwa:
   • Danna-dama zaɓin tantanin halitta ko kewayon kuma zaɓi zaɓi Tsara Kwayoyin… daga menu na mahallin;
   • Danna Launcher Akwatin Magana a cikin rukunin Lambobi akan shafin Gida Excel Ribbon;
   • Yi amfani da gajeriyar hanyar madannai Saukewa: CTRL-1 (watau zaɓi maɓallin CTRL kuma, riƙe shi ƙasa, zaɓi maɓallin "1" (ɗaya).
3. A cikin akwatin maganganu "Format Cells":
   • Tabbatar da katin Lambar a saman akwatin maganganu an zaɓi.
   • zabi Kashi daga lissafin Category a gefen hagu na akwatin maganganu .Wannan zai kawo ƙarin zaɓuɓɓuka a gefen dama na akwatin rajistan, ba ku damar zaɓar adadin wuraren goma da kuke son bayyana.
   • Da zarar ka zaɓi adadin wuraren goma da kake son nunawa, danna OK .

Nominal

Aiki Nominal Excel yana dawo da ƙima na ƙima don ƙimar riba mai tasiri da aka bayar da adadin adadin lokuta masu haɗawa a kowace shekara.

Ma'anar aikin shine:

ginin kalma

= NOMINAL( effect_rate, npery )

batutuwa

• effect_rate: Ƙimar riba mai tasiri (ƙimar lamba tsakanin 0 da 1).
• npery: Yawan lokuta masu haɗawa a kowace shekara (dole ne ya zama madaidaicin lamba).

misali

A cikin maƙunsar rubutu mai zuwa, aikin Nominal na Excel ana amfani da shi don ƙididdige ƙididdiga na ƙididdiga na lamuni uku tare da sharuɗɗa daban-daban.

Ercole Palmeri