Статистичні функції 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: масив відомих значень 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. Функція Excel Intercept обчислює перетин (значення на перетині осі 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. Кожен масив [new_x] має відповідати масиву [known_x's]. Якщо аргумент [new_x] опущено, встановлюється рівним [known_x's].
• [вартість]: необов’язковий логічний аргумент, який визначає, чи є константа «b» у лінійному рівнянні y = m x + b , потрібно примусово дорівнювати нулю. себе [коштувати] має значення ІСТИНА (або якщо цей аргумент опущено), константа b обробляється нормально;
• себе [коштувати] має значення FALSE, константа b встановлюється рівною 0, і рівняння прямої стає таким y = mx .

приклад

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

Зауважте, що не обов’язково, щоб наведені точки точно відповідали прямій лінії y = 2x + 10 (хоча в цьому прикладі це так). Функція Excel Trend знайде найкращу лінію для будь-якого набору значень, які ви надаєте.

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

У цьому прикладі значення [new_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]: один або кілька масивів відомих значень x. Це необов’язковий аргумент, який, якщо надається, має бути такої ж довжини, як і набір known_y's. Якщо опущено, набір [known_x's] набуває значення {1, 2, 3, …}.
• [new_x's]: набір нових значень x, для яких функція обчислює відповідні нові значення y. Якщо опущено, передбачається, що набір [new_x] дорівнює [known_x's] і функція повертає значення y, які лежать на розрахованій експоненціальній кривій зростання.
• [вартість]: необов’язковий логічний аргумент, який визначає, чи є константа «b» у лінійному рівнянні y = b * m^x , повинен дорівнювати 1. Якщо [коштувати] має значення ІСТИНА (або якщо цей аргумент опущено), константа 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. У діалоговому вікні «Формат клітинок»:
   • Переконайтеся, що карта Numero у верхній частині діалогового вікна вибрано.
   • вибрати Відсоток зі списку Категорія у лівій частині діалогового вікна .У правій частині прапорця з’являться додаткові параметри, які дозволять вибрати кількість десяткових знаків, які потрібно відображати.
   • Вибравши кількість десяткових знаків, які потрібно відобразити, натисніть OK .

Nominal

Функція Nominal Excel повертає номінальну відсоткову ставку для даної ефективної відсоткової ставки та заданої кількості періодів нарахування на рік.

Синтаксис функції:

синтаксис

= NOMINAL( effect_rate, npery )

предметів

• effect_rate: ефективна відсоткова ставка (числове значення від 0 до 1).
• npery: кількість періодів нарахування на рік (має бути додатним цілим числом).

приклад

У наведеній нижче електронній таблиці функція Nominal Excel використовується для розрахунку номінальної процентної ставки за трьома кредитами з різними умовами.

Ercole Palmeri