Vykreslování trendových čar v Excelu

Excel poskytuje robustní sadu nástrojů pro ilustraci trendů. Můžete to udělat vynesením trendových linií do grafů aplikace Excel a nabídnout vizuální zobrazení vašich dat . Zde zjistíte, jak vykreslit logaritmické trendové čáry, mocninné trendové čáry a polynomiální trendové čáry v Excelu.

Vykreslení logaritmické trendové linie v Excelu

Logaritmická trend je takový, ve kterém jsou data stoupá nebo klesá velmi rychle na začátku, ale pak se zpomaluje a úrovně off v průběhu času. Příkladem logaritmického trendu je model prodeje vysoce očekávaného nového produktu, který se obvykle prodává ve velkém množství po krátkou dobu a poté se vyrovná.

Pro vizualizaci takového trendu můžete vykreslit logaritmickou trendovou čáru. Toto je zakřivená čára procházející datovými body, kde se rozdíly mezi body na jedné straně čáry a body na druhé straně čáry vzájemně ruší.

Zde jsou kroky, které je třeba dodržet pro vykreslení logaritmické trendové linie v Excelu:

Kliknutím na graf jej vyberte.

Pokud váš graf obsahuje více datových řad, klikněte na řadu, kterou chcete analyzovat.

Vyberte Návrh → Přidat prvek grafu → Spojnice trendu → Další možnosti spojnice trendu.
Zobrazí se podokno Formát trendové linie.

Klepněte na kartu Možnosti spojnice trendu.

Vyberte přepínač Logaritmická.
Excel vykreslí logaritmickou trendovou linii.

(Volitelné) Zaškrtněte políčko Zobrazit rovnici v grafu.
Pokud chcete pouze vidět trendovou linii, můžete přejít přes kroky 6 a 7.

(Volitelně) Zaškrtněte políčko Zobrazit hodnotu R-squared v grafu.

Klepněte na tlačítko Zavřít.

Excel zobrazí regresní rovnici a hodnotu R2. Tento obrázek ukazuje graf s vykreslenou čárou exponenciálního trendu, regresní rovnicí a hodnotou R2.

Graf s logaritmickou trendovou linií.

Když je nejlépe odpovídající trendová čára logaritmická křivka, regresní rovnice má následující obecný tvar:

y = m * ln(x) + b

y je závislá proměnná; x je nezávislá proměnná; b a m jsou konstanty; a ln je přirozený logaritmus, pro který můžete použít funkci Excel LN.

Excel nemá funkci, která by přímo vypočítávala hodnoty b a m. Funkci LINREGRESE však můžete použít, pokud logaritmickou křivku „narovnáte“ pomocí logaritmické stupnice pro nezávislé hodnoty:

{= LINEST ( známý_ys , LN( známý_xs ), konst , statistiky )}

Vykreslení čáry trendu výkonu v Excelu

V mnoha případech regresní analýzy nejlépe odpovídá trend síly, ve kterém se data neustále zvyšují nebo snižují. Takový trend zjevně není exponenciální ani logaritmický, přičemž obojí implikuje extrémní chování, buď na konci trendu (v případě exponenciálního), nebo na začátku trendu (v případě logaritmického). Příklady silových trendů zahrnují výnosy, zisky a marže v úspěšných společnostech, z nichž všechny vykazují rok od roku stabilní nárůst tempa růstu.

Trend výkonu zní lineárně, ale vynesení čáry trendu výkonu ukazuje zakřivenou čáru, která nejlépe odpovídá datovým bodům. Při analýze takových dat je obvykle nejlepší nejprve vyzkoušet lineární trendovou linii. Pokud to nevyhovuje, přejděte na linii trendu výkonu.

Chcete-li v aplikaci Excel vykreslit čáru trendu napájení, postupujte takto:

Kliknutím na graf jej vyberte.

Pokud váš graf obsahuje více datových řad, klikněte na řadu, kterou chcete analyzovat.

Vyberte Návrh → Přidat prvek grafu → Spojnice trendu → Další možnosti spojnice trendu.
Zobrazí se podokno Formát trendové linie.

Klepněte na kartu Možnosti spojnice trendu.

Vyberte přepínač Napájení.
Excel vykreslí čáru trendu výkonu.

(Volitelné) Zaškrtněte políčko Zobrazit rovnici v grafu.
Pokud chcete pouze vidět trendovou linii, přeskočte kroky 6 a 7.

(Volitelně) Zaškrtněte políčko Zobrazit hodnotu R-squared v grafu.

Klepněte na tlačítko Zavřít.

Excel zobrazí regresní rovnici a hodnotu R2 (popsáno níže). Následující obrázek ukazuje graf s vykreslenou čárou trendu výkonu, regresní rovnicí a hodnotou R2.

Graf s čárou trendu moci.

Když je nejlépe odpovídající trendová křivka mocninná křivka, má regresní rovnice následující obecný tvar:

y = m * xb

y je závislá proměnná; x je nezávislá proměnná; a b a m jsou konstanty.

Pro přímý výpočet hodnot b a m není k dispozici žádná funkce listu. Funkci LINREGRESE však můžete použít, pokud výkonovou křivku „narovnáte“ použitím logaritmické stupnice na závislé a nezávislé hodnoty:

{= LINEST (LN( známé_ys ), LN( známé_xs ), konst , statistiky )}

Vykreslení polynomické trendové čáry v Excelu

V mnoha scénářích reálného světa se vztah mezi závislými a nezávislými proměnnými nepohybuje jedním směrem. To by bylo příliš snadné. Například namísto neustálého růstu – stejnoměrně, jako v lineárním trendu, prudce, jako v exponenciálním nebo logaritmickém trendu nebo stabilně, jako v trendu síly – se mohou data jako prodeje jednotek, zisky a náklady pohybovat nahoru a dolů. .

Pro vizualizaci takového trendu můžete vykreslit polynomiální trendovou čáru, což je nejlépe vyhovující čára více křivek odvozených pomocí rovnice, která používá více mocnin x. Počet mocnin x je řádem polynomické rovnice. Obecně platí, že čím vyšší pořadí, tím těsnější křivka odpovídá vašim stávajícím datům, ale tím nepředvídatelnější jsou vaše prognózované hodnoty.

Pokud již máte graf, přidejte do Excelu polynomiální trendovou čáru podle následujících kroků:

Kliknutím na graf jej vyberte.

Pokud váš graf obsahuje více datových řad, klikněte na řadu, kterou chcete analyzovat.

Vyberte Návrh → Přidat prvek grafu → Spojnice trendu → Další možnosti spojnice trendu.
Zobrazí se podokno Formát trendové linie.

Klepněte na kartu Možnosti spojnice trendu.

Vyberte přepínač Polynomial.

Klepnutím na šipky tlačítka Řazení nastavte pořadí požadované polynomické rovnice.

Excel vynese polynomiální trendovou linii.

(Volitelné) Zaškrtněte políčko Zobrazit rovnici v grafu.
Pokud chcete pouze vidět trendovou linii, vynechejte kroky 7 a 8.

(Volitelně) Zaškrtněte políčko Zobrazit hodnotu R-squared v grafu.

Klepněte na tlačítko Zavřít.

Excel zobrazí regresní rovnici a hodnotu R2. Obrázek níže ukazuje graf s vynesenou polynomickou trendovou čárou, regresní rovnicí a hodnotou R2.

Graf s polynomiální trendovou linií.

Když je nejlépe odpovídající trendová čára polynomiální křivka, regresní rovnice má následující podobu:

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

y je závislá proměnná; x je nezávislá proměnná; a b a m n až m1 jsou konstanty.

Chcete-li vypočítat hodnoty b a mn až m1, můžete použít LINREGRESE, pokud zvýšíte hodnoty známé_xs na mocniny od 1 do n pro polynom n -tého řádu:

{= LINEST ( známý_ys , známý_xs ^ {1,2,…, n }, konst , statistiky )}

Případně můžete použít funkci TREND:

{=TREND( známý_ys , známý_xs ^ {1,2,…, n }, nový_x , konst )}