Статыстычныя функцыі Excel: падручнік з прыкладамі, частка трэцяя

Excel забяспечвае шырокі спектр статыстычных функцый, якія выконваюць разлікі ад сярэдняга да больш складанага статыстычнага размеркавання і функцый лініі трэнду.

У гэтым артыкуле мы паглыбімся ў статыстычныя функцыі Excel для разліку функцый лініі трэнду.

Звярніце ўвагу, што некаторыя статыстычныя функцыі былі прадстаўлены ў апошніх версіях Excel і таму недаступныя ў старых версіях.

Разліковы час чытання: 12 хвілін

Функцыі лініі трэнду

Forecast

Функцыя прагнозу Excel прагназуе будучую кропку на лінейнай лініі трэнду, якая адпавядае зададзенаму набору значэнняў x і y.

сінтаксіс

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

прадметы

• x: лікавае значэнне x, для якога вы хочаце прадказаць новае значэнне y.
• known_y's: масіў вядомых значэнняў y
• known_x's: масіў вядомых значэнняў х

Звярніце ўвагу, што даўжыня масіва known_x павінна быць такім жа, як у known_y і дысперсія known_x ён не павінен быць роўным нулю.

прыклад

У наступнай электроннай табліцы функцыя FORECAST Excel выкарыстоўваецца для прагназавання дадатковай кропкі ўздоўж найлепшай прамой праз шэраг вядомых значэнняў x і y (захоўваюцца ў ячэйках F2:F7 і G2:G7).

Як паказана ў ячэйцы F7 электроннай табліцы, функцыя для разліку чаканага значэння y пры x=7 :=FORECAST( 7, G2:G7, F2:F7 )

Гэта дае вынік 32.666667 .

Intercept

Сярод функцый прагназавання Excel мы знаходзім Intercept. Функцыя Excel Intercept вылічае перасячэнне (значэнне на скрыжаванні восі y) лініі лінейнай рэгрэсіі праз зададзены набор значэнняў x і y.

сінтаксіс

= INTERCEPT( known_y's, known_x's )

прадметы

• known_y's: масіў вядомых значэнняў y
• known_x's: масіў вядомых значэнняў х

Звярніце ўвагу, што даўжыня масіва known_x павінна быць такім жа, як у known_y і дысперсія known_x ён не павінен быць роўным нулю.

прыклад

Наступная электронная табліца паказвае прыклад функцыі Intercept Excel выкарыстоўваецца для вылічэння кропкі, праз якую праходзіць лінія лінейнай рэгрэсіі known_x і known_y (пералічаны ў ячэйках F2:F7 і G2:G7) перасякае вось у.

Le known_x і known_y наносяцца на графік у табліцу.

Як паказана ў ячэйцы F9 электроннай табліцы, формула для функцыі Intercept такая :=INTERCEPT( G2:G7, F2:F7 )

што дае вынік 2.4 .

Slope

Яшчэ адна вельмі цікавая функцыя прагназавання - нахіл (Slope) Excel разлічвае нахіл лініі лінейнай рэгрэсіі праз зададзены набор значэнняў x і y.

Сінтаксіс функцыі:

сінтаксіс

= SLOPE( known_y's, known_x's )

прадметы

• known_y's: масіў вядомых значэнняў y
• known_x's: масіў вядомых значэнняў х

Звярніце ўвагу, што даўжыня масіва known_x павінна быць такім жа, як у known_y і дысперсія known_x ён не павінен быць роўным нулю.

прыклад

Наступная электронная табліца паказвае прыклад функцыі Slope (нахіл) у Excel, які выкарыстоўваецца для разліку нахілу лініі лінейнай рэгрэсіі праз known_x і known_y, у ячэйках F2:F7 і G2:G7.

Le known_x і known_y наносяцца на графік у табліцу.

Як паказана ў ячэйцы F9 электроннай табліцы, формула для функцыі Intercept такая :=SLOPE( G2:G7, F2:F7 )

што дае вынік 4.628571429.

Trend

Вельмі цікавая функцыя прагнозу Excel - гэта TREND Excel (Trend) разлічвае лінейную лінію трэнду праз зададзены набор значэнняў y і (неабавязкова) зададзены набор значэнняў x.

Затым функцыя пашырае лінейную лінію трэнду, каб вылічыць дадатковыя значэнні y для дадатковага набору новых значэнняў x.

Сінтаксіс функцыі:

сінтаксіс

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

прадметы

• known_y's: масіў вядомых значэнняў y
• [known_x's]: адзін або некалькі масіваў вядомых значэнняў х. Гэта неабавязковы аргумент, які, калі падаецца, павінен быць той жа даўжыні, што і набор known_y's. Калі прапушчана, набор [known_x's] прымае значэнне {1, 2, 3, …}.
• [новы_х]: Неабавязковы аргумент, які забяспечвае адзін або некалькі масіваў лікавых значэнняў, якія прадстаўляюць набор новых значэнняў x, для якіх вы хочаце вылічыць адпаведныя новыя значэнні y. Кожны масіў [новыя_х] павінна адпавядаць масіву [known_x's]. Калі аргумент [новыя_х] апушчаны, ён роўны [known_x's] .
• [кошт]: Неабавязковы лагічны аргумент, які вызначае, ці ёсць канстанта "b" у лінейным ураўненні y = m x + b , павінна быць роўная нулю. Я [каштаваць] мае значэнне TRUE (або калі гэты аргумент апушчаны) канстанта b разглядаецца нармальна;
• Я [каштаваць] ХЛУСНЯ, канстанта b усталёўваецца ў 0, і ўраўненне прамой становіцца такім y = mx .

прыклад

У наступнай электроннай табліцы функцыя Excel Trend выкарыстоўваецца для пашырэння шэрагу значэнняў x і y, якія ляжаць на прамой лініі y = 2x + 10. Вядомыя значэнні x і y захоўваюцца ў ячэйках A2-B5 табліцы, а таксама адлюстроўваюцца ў графіцы электроннай табліцы.

Заўважце, што неабавязкова, каб прыведзеныя кропкі дакладна адпавядалі прамой лініі y = 2x + 10 (хоць у гэтым прыкладзе гэта так). Функцыя Excel Trend знойдзе найбольш прыдатны радок для любога набору значэнняў, якія вы падаеце.

Функцыя Trend выкарыстоўвае метад найменшых квадратаў, каб знайсці найлепшую лінію, а затым выкарыстоўвае яго для разліку новых значэнняў y для новых значэнняў x.

У гэтым прыкладзе значэнні [новыя_х] захоўваюцца ў ячэйках A8-A10, а функцыя Excel Trend выкарыстоўвалася ў ячэйках B8-B10 для пошуку новых адпаведных значэнняў y. Як паказана ў радку формул, формула ёсць := ТРЭНД( B2:B5, A2:A5, A8:A10)

Вы бачыце, што функцыя Trend у радку формул заключана ў дужкі { }. Гэта азначае, што функцыя была ўведзена як формула масіва .

Growth

Сярод функцый прагназавання Excel мы знаходзім Growth. Функцыя Growth Excel разлічвае крывую экспанентнага росту праз зададзены набор значэнняў y і (неабавязкова) адзін або некалькі набораў значэнняў x. Затым функцыя пашырае крывую, каб вылічыць дадатковыя значэнні y для дадатковага набору новых значэнняў x.

Сінтаксіс функцыі:

сінтаксіс

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

прадметы

• known_y's: масіў вядомых значэнняў y
• [known_x's]: адзін або некалькі масіваў вядомых значэнняў х. Гэта неабавязковы аргумент, які, калі падаецца, павінен быць той жа даўжыні, што і набор known_y's. Калі прапушчана, набор [known_x's] прымае значэнне {1, 2, 3, …}.
• [новы_х]: Набор новых значэнняў х, для якіх функцыя вылічвае адпаведныя новыя значэнні y. Калі апусціць, мяркуецца, што набор [новыя_х] роўна таму [known_x's] і функцыя вяртае значэнні y, якія ляжаць на разлічанай крывой экспанентнага росту.
• [кошт]: Неабавязковы лагічны аргумент, які вызначае, ці ёсць канстанта "b" у лінейным ураўненні y = b * m^x , павінна быць роўная 1. Калі [каштаваць] мае значэнне TRUE (або калі гэты аргумент апушчаны) канстанта b разглядаецца нармальна; Я [каштаваць] ХЛУСНЯ, канстанта b усталёўваецца ў 1, і ўраўненне прамой становіцца такім y = mx .

прыклад

У наступнай электроннай табліцы функцыя росту Excel выкарыстоўваецца для пашырэння шэрагу значэнняў x і y, якія ляжаць на крывой экспанентнага росту y = 5 * 2^x. Яны захоўваюцца ў ячэйках A2-B5 электроннай табліцы і таксама адлюстроўваюцца ў дыяграме электроннай табліцы.

Функцыя Growth разлічвае крывую экспанентнага росту, якая найлепшым чынам адпавядае вядомым прадстаўленым значэнням x і y. У гэтым простым прыкладзе лепш за ўсё падыходзіць экспанентная крывая y = 5 * 2^x.

Пасля таго, як Excel вылічыць ураўненне экспанентнай крывой росту, ён можа выкарыстоўваць яго для разліку новых значэнняў y для новых значэнняў x, прадстаўленых у ячэйках A8-A10.

У гэтым прыкладзе значэнні [new_x's] захоўваюцца ў ячэйках A8-A10 і функцыі Growth Excel быў устаўлены ў ячэйкі B8-B10. Як паказана ў радку формул, формула для гэтага:=Growth(B2:B5, A2:A5, A8:A10)

Вы бачыце, што функцыя росту ў радку формул заключана ў дужкі {}. Гэта азначае, што функцыя была ўведзена як формула масіва .

Звярніце ўвагу, што хаця кропкі ў прыведзеным вышэй прыкладзе дакладна падыходзяць уздоўж крывой y = 5 * 2^x, гэта не істотна. Функцыя Growth Excel знойдзе найбольш прыдатную крывую для любога набору значэнняў, якія вы падаеце.

Фінансавыя функцыі

Эфект

Функцыя Effect Excel вяртае эфектыўную гадавую працэнтную стаўку для зададзенай намінальнай працэнтнай стаўкі і зададзенай колькасці перыядаў налічэння ў год.

Эфектыўная гадавая працэнтная стаўка

Эфектыўная гадавая працэнтная стаўка - гэта мера працэнтаў, якая ўключае капіталізацыю працэнтаў і часта выкарыстоўваецца для параўнання фінансавых пазык з рознымі ўмовамі капіталізацыі.

Эфектыўная гадавая працэнтная стаўка разлічваецца з дапамогай наступнага ўраўнення:

голуб nominal_rate гэта намінальная працэнтная стаўка e npery - гэта колькасць перыядаў складання ў год.

Сінтаксіс функцыі:

сінтаксіс

= EFFECT( nominal_rate, npery )

прадметы

• nominal_rate: намінальная працэнтная стаўка (павінна быць лікавым значэннем ад 0 да 1)
• npery: Колькасць перыядаў складання ў год (павінна быць натуральным лікам).

прыклад

Наступная электронная табліца паказвае тры прыклады функцыі Excel Effect:

Калі вынік функцыі Effect адлюстроўваецца як дзесятковы або паказвае 0%, абедзве гэтыя праблемы, верагодна, звязаны з фарматаваннем ячэйкі, якая змяшчае функцыю Effect.

Таму праблему можна вырашыць шляхам фарматавання ячэйкі ў працэнтах з дзесятковымі знакамі.

Каб зрабіць гэта:

1. Выберыце ячэйкі для фарматавання ў працэнтах.
2. Адкрыйце дыялогавае акно «Фарматаваць вочкі» адным з наступных спосабаў:
   • Пстрыкніце правай кнопкай мышы выбраную ячэйку або дыяпазон і абярыце опцыю Фарматаваць ячэйкі… з кантэкстнага меню;
   • Націсніце кнопку запуску дыялогавага акна ў групоўцы нумароў на ўкладцы Галоўная Стужка Excel;
   • Выкарыстоўвайце спалучэнне клавіш CTRL-1 (г.зн. абярыце клавішу CTRL і, утрымліваючы яе, абярыце клавішу «1» (адзін).
3. У дыялогавым акне «Фарматаваць вочкі»:
   • Пераканайцеся, што карта Лік у верхняй частцы дыялогавага акна выбрана.
   • выбраць Працэнт са спісу Катэгорыя у левай частцы дыялогавага акна .У правым баку сцяжка з'явяцца дадатковыя параметры, якія дазваляюць выбраць колькасць знакаў пасля коскі, якія вы хочаце паказаць.
   • Пасля таго, як вы выбралі колькасць знакаў пасля коскі, якія вы хочаце паказаць, націсніце OK .

Nominal

Функцыя Nominal Excel вяртае намінальную працэнтную стаўку для зададзенай эфектыўнай працэнтнай стаўкі і зададзенай колькасці перыядаў налічэння ў год.

Сінтаксіс функцыі:

сінтаксіс

= NOMINAL( effect_rate, npery )

прадметы

• effect_rate: эфектыўная працэнтная стаўка (лікавае значэнне ад 0 да 1).
• npery: Колькасць перыядаў складання ў год (павінна быць натуральным лікам).

прыклад

У наступнай электроннай табліцы функцыя Nominal Excel выкарыстоўваецца для разліку намінальнай працэнтнай стаўкі трох крэдытаў з рознымі ўмовамі.

Ercole Palmeri