Статистически функции на Excel: Урок с примери, трета част

Excel предоставя широка гама от статистически функции, които извършват изчисления от средната стойност до по-сложни функции за статистическо разпределение и линия на тенденция.

В тази статия ще се задълбочим в статистическите функции на Excel за изчисляване на функциите на линията на тренда.

Моля, обърнете внимание, че някои статистически функции бяха въведени в последните версии на Excel и следователно не са налични в по-старите версии.

Очаквано време за четене: 12 Minuti

Функции на трендовата линия

Forecast

Функцията Forecast на Excel прогнозира бъдеща точка на линейна тренд линия, съответстваща на даден набор от стойности x и y.

синтаксис

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

субекти

• x: Числова x-стойност, за която искате да предвидите нова y-стойност.
• known_y's: Масив от известни y стойности
• known_x's: Масив от известни x стойности

Имайте предвид, че дължината на масива от 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. Функцията Intercept на Excel изчислява пресечната точка (стойността в пресечната точка на оста y) на линията на линейна регресия през даден набор от стойности x и y.

синтаксис

= INTERCEPT( known_y's, known_x's )

субекти

• known_y's: Масив от известни y стойности
• known_x's: Масив от известни x стойности

Имайте предвид, че дължината на масива от known_x трябва да е същата като тази на known_y и дисперсията на known_x не е задължително да е нула.

пример

Следващата електронна таблица показва пример за функцията Intercept на Excel, използван за изчисляване на точката, където линията на линейната регресия преминава през known_x и known_y (изброени в клетки F2:F7 и G2:G7) пресича оста y.

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: Масив от известни x стойности

Имайте предвид, че дължината на масива от 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 е ТЕНДЕНЦИЯ Excel (Trend) изчислява линията на линейния тренд чрез даден набор от y стойности и (по избор) даден набор от x стойности.

След това функцията разширява линията на линейния тренд, за да изчисли допълнителни y стойности за допълнителен набор от нови x стойности.

Синтаксисът на функцията е:

синтаксис

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

субекти

• known_y's: Масив от известни y стойности
• [known_x's]: Един или повече масиви от известни x стойности. Това е незадължителен аргумент, който, ако бъде предоставен, трябва да бъде със същата дължина като набора от known_y's. Ако е пропуснато, наборът от [known_x's] приема стойност {1, 2, 3, …}.
• [new_x's]: Незадължителен аргумент, предоставящ един или повече масиви от числови стойности, представляващи набор от нови x-стойности, за които искате да изчислите съответните нови y-стойности. Всеки масив от [нови_x] трябва да съответства на масив от [known_x's]. Ако аргументът [нови_x] е пропуснато, зададено е равно на [known_x's] .
• [цена]: Незадължителен логически аргумент, указващ дали константата „b“ в линейното уравнение y = m x + b , трябва да бъде принуден да бъде равен на нула. себе си [цена] е TRUE (или ако този аргумент е пропуснат) константата b се третира нормално;
• себе си [цена] е FALSE, константата b е зададена на 0 и уравнението на правата линия става y = mx .

пример

В следващата електронна таблица функцията Excel Trend се използва за разширяване на поредица от стойности x и y, които лежат на правата линия y = 2x + 10. Известните стойности x и y се съхраняват в клетки A2-B5 на електронната таблица и също се показват в графиката на електронната таблица.

Имайте предвид, че не е от съществено значение дадените точки да пасват точно по правата линия y = 2x + 10 (въпреки че в този пример е така). Функцията Trend на Excel ще намери най-подходящата линия за всеки набор от стойности, които предоставяте.

Функцията Trend използва метода на най-малките квадрати, за да намери най-подходящата линия и след това го използва, за да изчисли новите стойности на y за предоставените нови стойности на x.

В този пример стойностите на [нови_x] се съхраняват в клетки A8-A10, а функцията Trend на Excel е използвана в клетки 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]: Един или повече масиви от известни x стойности. Това е незадължителен аргумент, който, ако бъде предоставен, трябва да бъде със същата дължина като набора от known_y's. Ако е пропуснато, наборът от [known_x's] приема стойност {1, 2, 3, …}.
• [new_x's]: Набор от нови стойности на x, за които функцията изчислява съответните нови стойности на y. Ако е пропуснато, се приема, че наборът от [нови_x] е равно на това на [known_x's] и функцията връща стойностите на y, които лежат на изчислената крива на експоненциален растеж.
• [цена]: Незадължителен логически аргумент, указващ дали константата „b“ в линейното уравнение y = b * m^x , трябва да бъде принуден да бъде равен на 1. Ако [цена] е TRUE (или ако този аргумент е пропуснат) константата b се третира нормално; себе си [цена] е FALSE, константата 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 )

Можете да видите, че функцията Growth в лентата с формули е затворена в скоби { }. Това показва, че функцията е въведена като формула за масив .

Имайте предвид, че въпреки че точките в горния пример пасват точно по кривата 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