Побудова ліній тренду в Excel

Excel надає надійний набір інструментів для ілюстрації тенденцій. Ви можете зробити це, побудувавши лінії тренду на діаграмах Excel, щоб наочно представити ваші дані . Тут ви дізнаєтеся, як побудувати логарифмічні лінії тренду, силові лінії тренду та поліноміальні лінії тренду в Excel.

Побудова логарифмічної лінії тренду в Excel

Логарифмічний тренд є один , в якому дані піднімається або падає дуже швидко на початку , але потім сповільнюється і вирівнюється з плином часу. Прикладом логарифмічної тенденції є модель продажів довгоочікуваного нового продукту, який зазвичай продається у великих кількостях протягом короткого часу, а потім вирівнюється.

Щоб уявити таку тенденцію, можна побудувати логарифмічну лінію тренда. Це крива лінія, що проходить через точки даних, де відмінності між точками з одного боку лінії та з іншого боку компенсують одна одну.

Нижче наведено кроки, які потрібно виконати для побудови логарифмічної лінії тренду в Excel:

Клацніть діаграму, щоб вибрати її.

Якщо ваша діаграма містить кілька рядів даних, клацніть серію, яку потрібно проаналізувати.

Виберіть Дизайн → Додати елемент діаграми → Лінія тренда → Більше параметрів лінії тренда.
З’явиться панель Формат лінії тренда.

Натисніть вкладку Параметри лінії тренду.

Виберіть перемикач Логарифмічний.
Excel відкладає логарифмічну лінію тренду.

(Необов’язково) Установіть прапорець Відображати рівняння на діаграмі.
Якщо ви просто хочете побачити лінію тренду, не соромтеся переходити через кроки 6 і 7.

(Необов’язково) Установіть прапорець Показувати значення R-квадрат на діаграмі.

Натисніть Закрити.

Excel відображає рівняння регресії та значення R2. На цьому зображенні показана діаграма з нанесеною на графік експоненційної лінією тренда, рівнянням регресії та значенням R2.

Діаграма з логарифмічною лінією тренда.

Коли найкращою лінією тренду є логарифмічна крива, рівняння регресії набуває такого загального вигляду:

y = m * ln(x) + b

y — залежна змінна; x – незалежна змінна; b і m — константи; а ln — натуральний логарифм, для якого можна використовувати функцію Excel LN.

Excel не має функції, яка безпосередньо обчислює значення b і m. Однак ви можете використовувати функцію LINEST, якщо ви «випрямите» логарифмічну криву, використовуючи логарифмічну шкалу для незалежних значень:

{=LINEST( known_ys , LN( known_xs ), const , stats )}

Побудова лінії тренду потужності в Excel

У багатьох випадках регресійного аналізу найкраще підходить тенденція потужності, при якій дані постійно збільшуються або зменшуються. Очевидно, що така тенденція не є експоненційною чи логарифмічною, обидва з яких передбачають екстремальну поведінку або в кінці тренду (у випадку експоненціального), або на початку тренду (у випадку логарифмічного). Приклади тенденцій влади включають доходи, прибутки та прибутки в успішних компаніях, усі вони демонструють постійне зростання темпів зростання з року в рік.

Тенденція потужності звучить лінійно, але побудова лінії тенденції потужності показує вигнуту лінію, яка найкраще підходить через точки даних. При аналізі таких даних зазвичай краще спочатку спробувати лінійну лінію тренда. Якщо це не дає належної відповідності, перейдіть до лінії тренду потужності.

Виконайте такі дії, щоб побудувати лінію тенденції потужності в Excel:

Клацніть діаграму, щоб вибрати її.

Якщо ваша діаграма містить кілька рядів даних, клацніть серію, яку потрібно проаналізувати.

Виберіть Дизайн → Додати елемент діаграми → Лінія тренда → Більше параметрів лінії тренда.
З’явиться панель Формат лінії тренда.

Натисніть вкладку Параметри лінії тренду.

Виберіть перемикач живлення.
Excel накреслює лінію тенденції потужності.

(Необов’язково) Установіть прапорець Відображати рівняння на діаграмі.
Якщо ви просто хочете побачити лінію тренду, пропустіть кроки 6 і 7.

(Необов’язково) Установіть прапорець Показувати значення R-квадрат на діаграмі.

Натисніть Закрити.

Excel відображає рівняння регресії та значення R2 (описано нижче). На наступному зображенні показано діаграму з накресленою лінією тенденції потужності, рівнянням регресії та значенням R2.

Діаграма з лінією тренду потужності.

Коли найкращою лінією тренду є крива потужності, рівняння регресії набуває такого загального вигляду:

y = m * xb

y — залежна змінна; x – незалежна змінна; а b і m — константи.

Немає доступної функції робочого аркуша для прямого обчислення значень b і m. Однак ви можете використовувати функцію LINEST, якщо ви «вирівняєте» криву потужності, застосувавши логарифмічну шкалу до залежних і незалежних значень:

{=LINEST(LN( відомі_ys ), LN( відомі_xs ), const , stats )}

Побудова поліноміальної лінії тренду в Excel

У багатьох реальних сценаріях зв’язок між залежною та незалежною змінними не рухається в одному напрямку. Це було б занадто легко. Наприклад, замість того, щоб постійно зростати — рівномірно, як у лінійній тенденції, різко, як у експоненціальному чи логарифмічному тренді, або стабільно, як у тенденції потужності — дані, такі як продажі одиниці продукції, прибутки та витрати, можуть рухатися вгору та вниз .

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

Якщо у вас уже є діаграма, виконайте такі дії, щоб додати поліноміальну лінію тренда в Excel:

Клацніть діаграму, щоб вибрати її.

Якщо ваша діаграма містить кілька рядів даних, клацніть серію, яку потрібно проаналізувати.

Виберіть Дизайн → Додати елемент діаграми → Лінія тренда → Більше параметрів лінії тренда.
З’явиться панель Формат лінії тренда.

Натисніть вкладку Параметри лінії тренду.

Виберіть перемикач Поліном.

Натисніть стрілки кнопки «Порядок обертання», щоб встановити порядок поліноміального рівняння, який ви хочете.

Excel виводить на графік поліноміальну лінію тренду.

(Необов’язково) Установіть прапорець Відображати рівняння на діаграмі.
Якщо ви просто хочете побачити лінію тренду, пройдіть кроки 7 і 8.

(Необов’язково) Установіть прапорець Показувати значення R-квадрат на діаграмі.

Натисніть Закрити.

Excel відображає рівняння регресії та значення R2. На зображенні нижче показано діаграму з нанесеною поліноміальною лінією тренда, рівнянням регресії та значенням R2.

Діаграма з поліноміальною лінією тренда.

Коли лінія тренду, яка найкраще підходить, є поліноміальною кривою, рівняння регресії набуває такого вигляду:

y = m n x n + … + m2x2 + m1x + b

y — залежна змінна; x – незалежна змінна; і b і m n через m1 є константами.

Щоб обчислити значення b і mn до m1, ви можете використовувати LINEST, якщо ви підведете відомі значення_xs в степені від 1 до n для полінома n- го порядку:

{=LINEST( known_ys , known_xs ^ {1,2,…, n }, const , stats )}

Крім того, ви можете використовувати функцію TREND:

{=TREND( known_ys , known_xs ^ {1,2,…, n }, new_xs , const )}