Excel Statistical Mabasa: Dzidziso ine Mienzaniso, Chikamu Chetatu

Excel inopa huwandu hwakasiyana hwehuwandu hwemabasa anoita masvomhu kubva pazvinoreva kusvika kune zvakanyanya kuoma manhamba kugovera uye mafambiro emutsara mabasa.

Muchinyorwa chino tichaongorora zvakadzama muExcel's manhamba mabasa ekuverenga maitiro emutsara mabasa.

Ndokumbira utarise kuti mamwe mabasa enhamba akaunzwa mushanduro dzichangoburwa dzeExcel uye nekudaro haawanikwe mumashanduro ekare.

Inofungidzirwa nguva yekuverenga: 12 minuti

Trendline mabasa

Forecast

Excel's Forecast basa rinofanotaura remangwana poindi pane mutsara mutsara wakakodzera kune yakapihwa seti ye x uye y kukosha.

nemarongerwo

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

zvidzidzo

• x: Nhamba x-ukoshi hwaunoda kufanotaura hutsva hwey.
• known_y's: Mutsara wezvinhu zvinozivikanwa y
• known_x's: Mutsara wezvinozivikanwa x tsika

Ziva kuti kureba kwehurongwa hwe known_x inofanira kunge yakafanana neya known_y uye kusiyana kwe known_x haifanire kunge iri zero.

muenzaniso

Mune rinotevera spreadsheet, basa FORECAST Excel inoshandiswa kufanotaura imwe poindi padivi peiyo yakanyatsokodzera mutsara wakatwasuka kuburikidza neakatevedzana anozivikanwa x uye y kukosha (akachengetwa mumasero F2: F7 uye G2: G7).

Sezvinoratidzwa musero F7 yespredishiti, basa rekuverenga kukosha kwe y kunotarisirwa pa x=7 ndiko :=FORECAST( 7, G2:G7, F2:F7 )

Izvi zvinopa mhedzisiro 32.666667 .

Intercept

Pakati pemabasa ekufanotaura kweExcel tinowana iyo Intercept. Basa reExcel's Intercept rinoverengera nhanho (kukosha kuri pamharadzano ye y-axis) yemutsara wekudzoreredza mutsara pane yakapihwa seti ye x uye y kukosha.

nemarongerwo

= INTERCEPT( known_y's, known_x's )

zvidzidzo

• known_y's: Mutsara wezvinhu zvinozivikanwa y
• known_x's: Mutsara wezvinozivikanwa x tsika

Ziva kuti kureba kwehurongwa hwe known_x inofanira kunge yakafanana neya known_y uye kusiyana kwe known_x haifanire kunge iri zero.

muenzaniso

Chinyorwa chinotevera chinoratidza muenzaniso webasa racho Intercept yeExcel yakashandiswa kuverenga poindi uko mutsara wekudzoreredza mutsara kuburikidza ne known_x and the known_y (yakanyorwa mumasero F2:F7 uye G2:G7) inopindirana neiyo y-axis.

Le known_x and the known_y dzakarongwa pagirafu muspredishiti.

Sezvinoratidzwa musero F9 yespredishiti, fomula yeIntercept basa ndeye :=INTERCEPT( G2:G7, F2:F7 )

izvo zvinopa mhedzisiro 2.4 .

Slope

Rimwe rinonakidza rekufanotaura basa ndiro Slope (Slope) Excel inoverenga kutenuka kwemutsara wekudzoreredza mutsara kuburikidza neseti yakapihwa ye x uye y kukosha.

Syntax yebasa ndeiyi:

nemarongerwo

= SLOPE( known_y's, known_x's )

zvidzidzo

• known_y's: Mutsara wezvinhu zvinozivikanwa y
• known_x's: Mutsara wezvinozivikanwa x tsika

Ziva kuti kureba kwehurongwa hwe known_x inofanira kunge yakafanana neya known_y uye kusiyana kwe known_x haifanire kunge iri zero.

muenzaniso

Chinyorwa chinotevera chinoratidza muenzaniso webasa racho Slope (slope) yeExcel inoshandiswa kuverenga kutsetseka kwemutsara wekudzoreredza mutsara kuburikidza ne known_x uye known_y, mumasero F2:F7 uye G2:G7.

Le known_x and the known_y dzakarongwa pagirafu muspredishiti.

Sezvinoratidzwa musero F9 yespredishiti, fomula yeIntercept basa ndeye :=SLOPE( G2:G7, F2:F7 )

izvo zvinopa mhedzisiro 4.628571429.

Trend

Iyo inonakidza kwazvo Excel yekufanotaura basa ndeye TREND Excel (Trend) inoverengera mutsara wetsika mutsara kuburikidza neyakapihwa seti ye y kukosha uye (nekuda), yakapihwa seti ye x kukosha.

Basa racho rinobva rawedzera mutsara wemaitiro mutsara kuti uverenge yakawedzera y kukosha kune imwe seti yezvitsva x kukosha.

Syntax yebasa ndeiyi:

nemarongerwo

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

zvidzidzo

• known_y's: Mutsara wezvinhu zvinozivikanwa y
• [known_x's]: Imwe kana akawanda mitsara yezvinozivikanwa x kukosha. Iyi igakava resarudzo rekuti, kana rapihwa, rinofanira kuenzana nehurefu hwakafanana neseti ye known_y's. Kana ikasiiwa, seti ye [known_x's] inotora kukosha {1, 2, 3, ...}.
• [new_x's]: Nhaurirano yekusarudzika, ichipa imwe kana akawanda mitsara yenhamba yenhamba inomiririra seti yezvitsva x-zvinokosha, izvo zvaunoda kuverengera inoenderana y-tsika. Chikamu chimwe nechimwe che [itsva_x's] inofanira kuenderana nehuwandu hwe [known_x's]. Kana nharo [itsva_x's] yakasiiwa, yakaiswa kuenzana [known_x's] .
• [mutengo]: Tsanangudzo ine musoro inotsanangudza kana iyo inongogara 'b', mumutsara equation y = m x + b , inofanira kumanikidzwa kuenzana ne zero. Ini [mutengo] iCHOKWADI (kana kana gakava iri rikasiiwa) iro rinogara riri b rinobatwa zvakajairika;
• Ini [mutengo] iri FALSE iro rinogara riri b rinoiswa ku 0 uye mutsara wakatwasuka equation wova y = mx .

muenzaniso

Muspredishiti rinotevera, basa reExcel Trend rinoshandiswa kuwedzera nhevedzano ye x uye y tsika dziri pamutsara wakatwasuka y = 2x + 10. Iyo inozivikanwa x uye y kukosha inochengetwa mumasero A2-B5 e iyo spreadsheet uye inoratidzwawo mune spreadsheet girafu.

Ziva kuti hazvina kukosha kuti mapoinzi akapihwa anyatsoenderana nemutsetse wakatwasuka y = 2x + 10 (kunyangwe mumuenzaniso uyu vanoita). Excel's Trend basa inowana yakanakisa-yakakodzera mutsara kune chero seti yemakoshero aunopa.

Iyo Trend basa rinoshandisa nzira shoma yemakwere kuwana yakanakisa mutsara uye wobva waishandisa kuverenga iyo mitsva y kukosha kweiyo itsva x kukosha yakapihwa.

Mumuenzaniso uyu, kukosha kwe [itsva_x's] zvakachengetwa mumaseru A8-A10, uye Excel's Trend function yakashandiswa, mumaseru B8-B10, kutsvaga hutsva hunofambirana na y kukosha. Sezvinoratidzwa mubhawa yeformula, fomula iri := TREND( B2:B5, A2:A5, A8:A10 )

Iwe unoona kuti Trend basa mune fomula bar rakavharirwa mumabhureki {}. Izvi zvinoratidza kuti basa rakaiswa se array formula .

Growth

Pakati pemabasa ekufanotaura kweExcel tinowana iyo Growth. Basa racho Growth Excel inoverengera exponential yekukura curve kuburikidza neyakapihwa seti ye y kukosha uye (inosarudza), imwe kana akawanda seti ye x kukosha. Basa racho rinobva rawedzera curve kuti iverenge yakawedzera y kukosha kune imwe seti yemamwe ma x values.

Syntax yebasa ndeiyi:

nemarongerwo

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

zvidzidzo

• known_y's: Mutsara wezvinhu zvinozivikanwa y
• [known_x's]: Imwe kana akawanda mitsara yezvinozivikanwa x kukosha. Iyi igakava resarudzo rekuti, kana rapihwa, rinofanira kuenzana nehurefu hwakafanana neseti ye known_y's. Kana ikasiiwa, seti ye [known_x's] inotora kukosha {1, 2, 3, ...}.
• [new_x's]: Seti yezvitsva x kukosha, iyo basa rinoverengera inoenderana y values. Kana ikasiiwa, inofungidzirwa kuti seti ye [itsva_x's] yakaenzana neye [known_x's] uye basa racho rinodzosa y kukosha ari pane yakaverengerwa exponential yekukura curve.
• [mutengo]: Tsanangudzo ine musoro inotsanangudza kana iyo inongogara 'b', mumutsara equation y = b * m^x , inofanira kumanikidzwa kuenzana na 1. Kana [mutengo] iCHOKWADI (kana kana gakava iri rikasiiwa) iro rinogara riri b rinobatwa zvakajairika; Ini [mutengo] iri FALSE iro rinogara riri b rinoiswa ku 1 uye mutsara wakatwasuka equation wova y = mx .

muenzaniso

Mune rinotevera spreadsheet, basa rekukura reExcel rinoshandiswa kuwedzera nhevedzano ye x uye y tsika dziri paexponential growth curve y = 5 * 2^x. Izvi zvakachengetwa mumaseru A2-B5 espredishiti uye zvakare zvinoonekwa muchati chespredishiti.

Iyo Kukura basa inoverengera exponential yekukura curve inonyatso kuenderana inozivikanwa x uye y kukosha kwakapihwa. Mumuenzaniso uyu wakapfava, yakanyatsokodzera curve ndiyo exponential curve y = 5 * 2^x.

Kamwe Excel ikaverengera exponential yekukura curve equation, inogona kuishandisa kuverenga iyo mitsva y kukosha kweiyo itsva x kukosha inopihwa mumasero A8-A10.

Mumuenzaniso uyu, kukosha kwe [new_x's] zvakachengetwa mumasero A8-A10 uye basa racho Growth yeExcel yakaiswa mumasero B8-B10. Sezvinoratidzwa mubhawa yeformula, formula yeiyi ndeiyi:=Growth( B2:B5, A2:A5, A8:A10 )

Iwe unogona kuona kuti basa reKukura mune fomula bar rakavharirwa mumabhureki {}. Izvi zvinoratidza kuti basa rakaiswa se array formula .

Ziva kuti kunyangwe mapoinzi ari mumuenzaniso wepamusoro achinyatsoenderana nepakona y = 5 * 2^x, izvi hazvina kukosha. Basa racho Growth Excel inowana yakanakisa-yakakodzera curve kune chero seti yemakoshero aunopa.

Mabasa emari

Migumisiro

Basa racho Effect Excel inodzosa chiyero chegore chinoshanda chemubereko wakapihwa zita uye nhamba yakapihwa yenguva dzekubatanidza pagore.

Chibereko chegore negore chinoshanda

Mubereko wepagore wemubereko unoshanda chiyero chemubereko chinosanganisira mhindu capitalization uye chinowanzo shandiswa kuenzanisa zvikwereti zvemari nemashoko akasiyana emari.

Mubereko wepagore unoshanda unoverengerwa uchishandisa inotevera equation:

njiva nominal_rate chiyero chemubereko chezita e npery ndiyo nhamba yenguva dzekubatanidza pagore.

Syntax yebasa ndeiyi:

nemarongerwo

= EFFECT( nominal_rate, npery )

zvidzidzo

• nominal_rate: Chiyero chemubereko chezita (chinofanirwa kunge chiri kukosha kwenhamba pakati pe0 ne1)
• npery: Huwandu hwenguva dzekubatanidza pagore (inofanira kunge iri positive integer).

muenzaniso

Iri rinotevera spreadsheet rinoratidza mienzaniso mitatu yeExcel Effect basa:

Kana mhedzisiro yebasa Effect inoratidza sedesimali kana kuratidza 0%, zvese zviri zviviri izvi zvingangove zvichikonzerwa nekufomatirwa kwesero rine basa racho Effect.

Naizvozvo dambudziko rinogona kugadziriswa nefomati sero muchikamu, nenzvimbo dzedesimali.

Kuita izvi:

1. Sarudza maseru ekugadzira sechikamu.
2. Vhura bhokisi rebhokisi re "Format Cells" uchishandisa imwe yenzira dzinotevera:
   • Tinya-kurudyi sero rakasarudzwa kana renji uye sarudza sarudzo Fomati Masero... kubva kumenu yezvinyorwa;
   • Dzvanya iyo Dialog Bhokisi Launcher muNhamba yemapoka pane iyo tab Home Excel Ribhoni;
   • Shandisa nzira yekudimbudzira kiibhodhi CTRL-1 (kureva sarudza CTRL kiyi uye, uchiibata pasi, sarudza "1" (imwe) kiyi).
3. Mu "Format Cells" dialog box:
   • Ita shuwa kadhi Nhamba pamusoro pebhokisi rebhokisi rinosarudzwa.
   • sarudza muzana kubva pakurongwa Chikamu kuruboshwe rwebhokisi rebhokisi .Izvi zvinounza dzimwe sarudzo kudivi rekurudyi rebhokisi, zvichikubvumidza kuti usarudze nhamba yenzvimbo dzedesimali dzaunoda kuoneka.
   • Kana uchinge wasarudza nhamba yenzvimbo dzedesimali dzaunoda kuratidza, tinya OK .

Nominal

Basa racho Nominal Excel inodzosa mubereko wezita remubereko wakapihwa unoshanda uye nhamba yakapihwa yenguva dzekubatanidza pagore.

Syntax yebasa ndeiyi:

nemarongerwo

= NOMINAL( effect_rate, npery )

zvidzidzo

• effect_rate: Mubereko unoshanda (ukoshi hwenhamba huri pakati pe0 ne1).
• npery: Huwandu hwenguva dzekubatanidza pagore (inofanira kunge iri positive integer).

muenzaniso

Mune rinotevera spreadsheet, basa Nominal yeExcel inoshandiswa kuverenga iyo yezita mubereko wezvikwereti zvitatu zvine mazwi akasiyana.

Ercole Palmeri