Swyddogaethau Ystadegol Excel: Tiwtorial gydag Enghreifftiau, Rhan Tri

Mae Excel yn darparu ystod eang o swyddogaethau ystadegol sy'n gwneud cyfrifiadau o'r cymedr i swyddogaethau dosbarthiad ystadegol a llinellau tuedd mwy cymhleth.

Yn yr erthygl hon byddwn yn ymchwilio'n ddyfnach i swyddogaethau ystadegol Excel ar gyfer cyfrifo swyddogaethau llinellau tuedd.

Sylwch fod rhai swyddogaethau ystadegol wedi'u cyflwyno mewn fersiynau diweddar o Excel ac felly nid ydynt ar gael mewn fersiynau hŷn.

Amser darllen amcangyfrifedig: 12 minuti

Swyddogaethau llinell duedd

Forecast

Mae swyddogaeth Rhagolwg Excel yn rhagfynegi pwynt yn y dyfodol ar linell duedd linellol sy'n cyd-fynd â set benodol o werthoedd x ac y.

cystrawen

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

pynciau

• x: Gwerth x rhifol yr ydych am ragweld gwerth-y newydd ar ei gyfer.
• known_y's: Amrywiaeth o werthoedd y hysbys
• known_x's: Amrywiaeth o werthoedd x hysbys

Sylwch fod hyd yr amrywiaeth o known_x rhaid iddo fod yr un fath ag o known_y a'r amrywiad o known_x nid oes rhaid iddo fod yn sero.

enghraifft

Yn y daenlen ganlynol, y swyddogaeth FORECAST Defnyddir Excel i ragfynegi pwynt ychwanegol ar hyd y llinell syth sy'n ffitio orau trwy gyfres o werthoedd x ac y hysbys (wedi'u storio yng nghelloedd F2:F7 a G2:G7).

Fel y dangosir yng nghell F7 y daenlen, y ffwythiant i gyfrifo'r gwerth y disgwyliedig ar x=7 yw :=FORECAST( 7, G2:G7, F2:F7 )

Mae hyn yn rhoi'r canlyniad 32.666667 .

Intercept

Ymhlith swyddogaethau rhagweld Excel rydym yn dod o hyd i'r Intercept. Mae ffwythiant Intercept Excel yn cyfrifo rhyngdoriad (y gwerth ar groesffordd yr echelin-y) y llinell atchweliad llinol ar draws set o werthoedd x ac y a roddir.

cystrawen

= INTERCEPT( known_y's, known_x's )

pynciau

• known_y's: Amrywiaeth o werthoedd y hysbys
• known_x's: Amrywiaeth o werthoedd x hysbys

Sylwch fod hyd yr amrywiaeth o known_x rhaid iddo fod yr un fath ag o known_y a'r amrywiad o known_x nid oes rhaid iddo fod yn sero.

enghraifft

Mae'r daenlen ganlynol yn dangos enghraifft o'r swyddogaeth Intercept o Excel a ddefnyddir i gyfrifo'r pwynt lle mae'r llinell atchweliad llinol drwy'r known_x a'r known_y (a restrir yng nghelloedd F2:F7 a G2:G7) yn croestorri'r echelin-y.

Le known_x a'r known_y yn cael eu plotio ar y graff yn y daenlen.

Fel y dangosir yng nghell F9 y daenlen, y fformiwla ar gyfer y swyddogaeth Intercept yw :=INTERCEPT( G2:G7, F2:F7 )

sy'n rhoi'r canlyniad 2.4 .

Slope

Swyddogaeth rhagfynegi diddorol iawn arall yw'r Llethr (Slope) Mae Excel yn cyfrifo llethr y llinell atchweliad llinol trwy set benodol o werthoedd x ac y.

Cystrawen y swyddogaeth yw:

cystrawen

= SLOPE( known_y's, known_x's )

pynciau

• known_y's: Amrywiaeth o werthoedd y hysbys
• known_x's: Amrywiaeth o werthoedd x hysbys

Sylwch fod hyd yr amrywiaeth o known_x rhaid iddo fod yr un fath ag o known_y a'r amrywiad o known_x nid oes rhaid iddo fod yn sero.

enghraifft

Mae'r daenlen ganlynol yn dangos enghraifft o'r swyddogaeth Slope (llethr) o Excel a ddefnyddir i gyfrifo llethr y llinell atchweliad llinol trwy'r known_x a'r known_y, mewn celloedd F2:F7 a G2:G7.

Le known_x a'r known_y yn cael eu plotio ar y graff yn y daenlen.

Fel y dangosir yng nghell F9 y daenlen, y fformiwla ar gyfer y swyddogaeth Intercept yw :=SLOPE( G2:G7, F2:F7 )

sy'n rhoi'r canlyniad 4.628571429.

Trend

Swyddogaeth rhagolwg Excel diddorol iawn yw'r RHAGLEN Mae Excel (Trend) yn cyfrifo'r llinell duedd linellol trwy set benodol o werthoedd y ac (yn ddewisol), set benodol o x gwerthoedd.

Yna mae'r swyddogaeth yn ymestyn y llinell duedd llinol i gyfrifo gwerthoedd y ychwanegol ar gyfer set ychwanegol o werthoedd x newydd.

Cystrawen y swyddogaeth yw:

cystrawen

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

pynciau

• known_y's: Amrywiaeth o werthoedd y hysbys
• [known_x's]: Un arae neu fwy o werthoedd x hysbys. Mae hon yn ddadl ddewisol a ddylai, os caiff ei chyflenwi, fod yr un hyd â'r set o known_y's. Os caiff ei hepgor, bydd y set o [known_x's] yn cymryd y gwerth {1, 2, 3, …}.
• [newydd_x's]: Dadl ddewisol, sy'n darparu un neu fwy o araeau o werthoedd rhifol sy'n cynrychioli set o werthoedd-x newydd, yr ydych am gyfrifo'r gwerthoedd-y newydd cyfatebol ar eu cyfer. Mae pob casgliad o [newydd_x's] yn cyfateb i amrywiaeth o [known_x's]. Os bydd y ddadl [newydd_x's] yn cael ei hepgor, mae'n cael ei osod i gyfartal [known_x's].
• [cost]: Dadl resymegol ddewisol yn nodi a yw'r cysonyn 'b', yn yr hafaliad llinol y = m x + b , rhaid eu gorfodi i fod yn hafal i sero. Hunan [costio] yn WIR (neu os caiff y ddadl hon ei hepgor) caiff y cysonyn b ei drin yn normal;
• Hunan [costio] yn ANGHYWIR mae'r cysonyn b wedi'i osod i 0 ac mae'r hafaliad llinell syth yn dod y = mx .

enghraifft

Yn y daenlen ganlynol, defnyddir y swyddogaeth Excel Trend i ymestyn cyfres o werthoedd x ac y sy'n gorwedd ar y llinell syth y = 2x + 10. Mae'r gwerthoedd x ac y hysbys yn cael eu storio yng nghelloedd A2-B5 o y daenlen ac maent hefyd yn cael eu harddangos yn y graff taenlen.

Sylwch nad yw'n hanfodol bod y pwyntiau a roddir yn ffitio'n union ar hyd y llinell syth y = 2x + 10 (er eu bod yn yr enghraifft hon). Bydd swyddogaeth Excel's Trend yn dod o hyd i'r llinell ffit orau ar gyfer unrhyw set o werthoedd a ddarperir gennych.

Mae'r ffwythiant Tuedd yn defnyddio'r dull sgwariau lleiaf i ddod o hyd i'r llinell ffit orau ac yna'n ei defnyddio i gyfrifo'r gwerthoedd y newydd ar gyfer y gwerthoedd x newydd a ddarperir.

Yn yr enghraifft hon, mae gwerthoedd [newydd_x's] yn cael eu storio mewn celloedd A8-A10, a defnyddiwyd swyddogaeth Tueddiadau Excel, yng nghelloedd B8-B10, i ddod o hyd i'r gwerthoedd y cyfatebol newydd. Fel y dangosir yn y bar fformiwla, y fformiwla yw := TUEDD( B2:B5, A2:A5, A8:A10 )

Rydych chi'n gweld bod y ffwythiant Tueddiad yn y bar fformiwla wedi'i amgáu mewn braces { }. Mae hyn yn dangos bod y swyddogaeth wedi'i nodi fel fformiwla arae .

Growth

Ymhlith swyddogaethau rhagweld Excel rydym yn dod o hyd i'r Growth. Y swyddogaeth Growth Mae Excel yn cyfrifo'r gromlin twf esbonyddol trwy set benodol o werthoedd y a (dewisol), un set neu fwy o x werthoedd. Yna mae'r ffwythiant yn ymestyn y gromlin i gyfrifo gwerthoedd y ychwanegol ar gyfer set ychwanegol o werthoedd x newydd.

Cystrawen y swyddogaeth yw:

cystrawen

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

pynciau

• known_y's: Amrywiaeth o werthoedd y hysbys
• [known_x's]: Un arae neu fwy o werthoedd x hysbys. Mae hon yn ddadl ddewisol a ddylai, os caiff ei chyflenwi, fod yr un hyd â'r set o known_y's. Os caiff ei hepgor, bydd y set o [known_x's] yn cymryd y gwerth {1, 2, 3, …}.
• [newydd_x's]: Set o werthoedd x newydd, y mae'r ffwythiant yn cyfrifo'r gwerthoedd y newydd cyfatebol ar eu cyfer. Os caiff ei hepgor, tybir bod y set o [newydd_x's] yn hafal i [known_x's] ac mae'r ffwythiant yn dychwelyd y gwerthoedd y sy'n gorwedd ar y gromlin twf esbonyddol a gyfrifwyd.
• [cost]: Dadl resymegol ddewisol yn nodi a yw'r cysonyn 'b', yn yr hafaliad llinol y = b * m^x , rhaid ei orfodi i fod yn gyfartal i 1. Os [costio] yn WIR (neu os caiff y ddadl hon ei hepgor) caiff y cysonyn b ei drin yn normal; Hunan [costio] yn ANGHYWIR mae'r cysonyn b wedi'i osod i 1 ac mae'r hafaliad llinell syth yn dod y = mx .

enghraifft

Yn y daenlen ganlynol, defnyddir swyddogaeth twf Excel i ymestyn cyfres o werthoedd x ac y sy'n gorwedd ar y gromlin twf esbonyddol y = 5 * 2 ^x. Mae'r rhain yn cael eu storio yng nghelloedd A2-B5 y daenlen ac maent hefyd yn ymddangos yn y siart taenlen.

Mae'r ffwythiant Twf yn cyfrifo'r gromlin twf esbonyddol sy'n cyd-fynd orau â'r gwerthoedd x ac y hysbys a ddarperir. Yn yr enghraifft syml hon, y gromlin ffit orau yw'r gromlin esbonyddol y = 5 * 2 ^x.

Unwaith y bydd Excel yn cyfrifo'r hafaliad cromlin twf esbonyddol, gall ei ddefnyddio i gyfrifo'r gwerthoedd y newydd ar gyfer y gwerthoedd x newydd a ddarperir yng nghelloedd A8-A10.

Yn yr enghraifft hon, mae gwerthoedd [new_x's] yn cael eu storio mewn celloedd A8-A10 a'r swyddogaeth Growth o Excel wedi'i fewnosod i gelloedd B8-B10. Fel y dangosir yn y bar fformiwla, y fformiwla ar gyfer hyn yw:=Growth( B2:B5, A2:A5, A8:A10 )

Gallwch weld bod y ffwythiant Twf yn y bar fformiwla wedi ei amgáu mewn braces { }. Mae hyn yn dangos bod y swyddogaeth wedi'i nodi fel fformiwla arae .

Sylwch, er bod y pwyntiau yn yr enghraifft uchod yn ffitio'n union ar hyd y gromlin y = 5 * 2 ^x, nid yw hyn yn hanfodol. Y swyddogaeth Growth Bydd Excel yn dod o hyd i'r gromlin sy'n ffitio orau ar gyfer unrhyw set o werthoedd a ddarperir gennych.

Swyddogaethau ariannol

Effaith

Y swyddogaeth Effect Mae Excel yn dychwelyd y gyfradd llog flynyddol effeithiol ar gyfer cyfradd llog enwol benodol a nifer penodol o gyfnodau cyfansawdd y flwyddyn.

Cyfradd llog flynyddol effeithiol

Mae’r gyfradd llog flynyddol effeithiol yn fesur o log sy’n ymgorffori cyfalafu llog ac fe’i defnyddir yn aml i gymharu benthyciadau ariannol â thelerau cyfalafu gwahanol.

Cyfrifir y gyfradd llog flynyddol effeithiol gan ddefnyddio’r hafaliad canlynol:

colomen nominal_rate yw'r gyfradd llog enwol e npery yw nifer y cyfnodau cyfansawdd y flwyddyn.

Cystrawen y swyddogaeth yw:

cystrawen

= EFFECT( nominal_rate, npery )

pynciau

• nominal_rate: Y gyfradd llog enwol (rhaid iddo fod yn werth rhifol rhwng 0 ac 1)
• npery: Nifer y cyfnodau cyfansawdd y flwyddyn (rhaid bod yn gyfanrif positif).

enghraifft

Mae'r daenlen ganlynol yn dangos tair enghraifft o swyddogaeth Excel Effect:

Os yw canlyniad y swyddogaeth Effect yn dangos fel degol neu yn dangos 0%, mae'r ddau fater hyn yn debygol oherwydd fformatio'r gell sy'n cynnwys y swyddogaeth Effect.

Felly gellir datrys y broblem trwy fformatio'r gell mewn canran, gyda lleoedd degol.

I wneud hyn:

1. Dewiswch y celloedd i'w fformatio fel canran.
2. Agorwch y blwch deialog “Fformat Cells” gan ddefnyddio un o'r dulliau canlynol:
   • De-gliciwch ar y gell neu'r ystod a ddewiswyd a dewiswch yr opsiwn Fformat celloedd… o'r ddewislen cyd-destun;
   • Cliciwch y Lansiwr Blwch Deialog yn y Grŵp Rhif ar y tab Hafan Rhuban Excel;
   • Defnyddiwch y llwybr byr bysellfwrdd CTRL-1 (h.y. dewiswch yr allwedd CTRL a, gan ei dal i lawr, dewiswch yr allwedd “1” (un).
3. Yn y blwch deialog “Fformat Cells”:
   • Gwnewch yn siŵr y cerdyn Rhif ar frig y blwch deialog yn cael ei ddewis.
   • dewiswch Canran o'r rhestr Categori ar ochr chwith y blwch deialog .Bydd hyn yn dod ag opsiynau ychwanegol i fyny ar ochr dde'r blwch ticio, gan ganiatáu i chi ddewis y nifer o leoedd degol rydych chi am ymddangos.
   • Unwaith y byddwch wedi dewis y nifer o leoedd degol rydych am eu dangos, cliciwch OK .

Nominal

Y swyddogaeth Nominal Mae Excel yn dychwelyd y gyfradd llog enwol ar gyfer cyfradd llog effeithiol benodol a nifer penodol o gyfnodau cyfansawdd y flwyddyn.

Cystrawen y swyddogaeth yw:

cystrawen

= NOMINAL( effect_rate, npery )

pynciau

• effect_rate: Y gyfradd llog effeithiol (gwerth rhifol rhwng 0 ac 1).
• npery: Nifer y cyfnodau cyfansawdd y flwyddyn (rhaid bod yn gyfanrif positif).

enghraifft

Yn y daenlen ganlynol, y swyddogaeth Nominal o Excel yn cael ei ddefnyddio i gyfrifo cyfradd llog enwol tri benthyciad gyda thelerau gwahanol.

Ercole Palmeri