Vykresľovanie trendových čiar v Exceli

Excel poskytuje robustnú sadu nástrojov na znázornenie trendov. Môžete to urobiť zakreslením trendových čiar do grafov v Exceli, aby ste získali vizuál svojich údajov . Tu zistíte, ako vykresliť logaritmické trendové čiary, mocninové trendové čiary a polynomické trendové čiary v Exceli.

Vykreslenie logaritmickej trendovej čiary v Exceli

Logaritmické trend je taký, v ktorom sú dáta stúpa alebo klesá veľmi rýchlo na začiatku, ale potom sa spomaľuje a úrovne off v priebehu času. Príkladom logaritmického trendu je model predaja vysoko očakávaného nového produktu, ktorý sa zvyčajne predáva vo veľkých množstvách na krátky čas a potom sa vyrovná.

Na vizualizáciu takéhoto trendu môžete vykresliť logaritmickú trendovú čiaru. Ide o zakrivenú čiaru cez dátové body, kde sa rozdiely medzi bodmi na jednej strane čiary a bodmi na druhej strane čiary navzájom rušia.

Tu sú kroky, ktoré treba dodržať pri vykreslení logaritmickej trendovej čiary v Exceli:

Kliknutím na graf ho vyberiete.

Ak má váš graf viacero radov údajov, kliknite na rad, ktorý chcete analyzovať.

Vyberte Návrh → Pridať prvok grafu → Trendová čiara → Ďalšie možnosti trendovej čiary.
Zobrazí sa tabla Formátovať trendovú čiaru.

Kliknite na kartu Možnosti trendovej čiary.

Vyberte prepínač Logaritmický.
Excel vykreslí logaritmickú trendovú čiaru.

(Voliteľné) Začiarknite políčko Zobraziť rovnicu v grafe.
Ak chcete len vidieť trendovú čiaru, pokojne prejdite cez kroky 6 a 7.

(Voliteľné) Začiarknite políčko Zobraziť v grafe štvorcovú hodnotu R.

Kliknite na tlačidlo Zavrieť.

Excel zobrazí regresnú rovnicu a hodnotu R2. Tento obrázok ukazuje graf s vykreslenou čiarou exponenciálneho trendu, regresnou rovnicou a hodnotou R2.

Graf s logaritmickou trendovou čiarou.

Keď je najlepšou trendovou čiarou logaritmická krivka, regresná rovnica má nasledujúcu všeobecnú formu:

y = m * ln(x) + b

y je závislá premenná; x je nezávislá premenná; b a m sú konštanty; a ln je prirodzený logaritmus, pre ktorý môžete použiť funkciu Excelu LN.

Excel nemá funkciu, ktorá priamo vypočítava hodnoty b a m. Funkciu LINREGRESE však môžete použiť, ak logaritmickú krivku „vyrovnáte“ pomocou logaritmickej stupnice pre nezávislé hodnoty:

{=LINEST( známy_ys , LN( známy_xs ), konšt. , štatistiky )}

Vykreslenie silovej trendovej čiary v Exceli

V mnohých prípadoch regresnej analýzy najlepšie vyhovuje trend sily, v ktorom sa údaje neustále zvyšujú alebo znižujú. Takýto trend zjavne nie je exponenciálny ani logaritmický, pričom obe znamenajú extrémne správanie, či už na konci trendu (v prípade exponenciálneho), alebo na začiatku trendu (v prípade logaritmického). Príklady silových trendov zahŕňajú výnosy, zisky a marže v úspešných spoločnostiach, pričom všetky vykazujú stabilné zvyšovanie miery rastu z roka na rok.

Trend výkonu znie lineárne, ale vykreslenie krivky trendu výkonu ukazuje zakrivenú krivku, ktorá najlepšie zodpovedá údajovým bodom. Pri analýze takýchto údajov je zvyčajne najlepšie najskôr vyskúšať lineárnu trendovú čiaru. Ak to nevyhovuje, prejdite na silovú trendovú čiaru.

Ak chcete vykresliť čiaru trendu výkonu v Exceli, postupujte podľa týchto krokov:

Kliknutím na graf ho vyberiete.

Ak má váš graf viacero radov údajov, kliknite na rad, ktorý chcete analyzovať.

Vyberte Návrh → Pridať prvok grafu → Trendová čiara → Ďalšie možnosti trendovej čiary.
Zobrazí sa tabla Formátovať trendovú čiaru.

Kliknite na kartu Možnosti trendovej čiary.

Vyberte prepínač napájania.
Excel zobrazuje silovú trendovú čiaru.

(Voliteľné) Začiarknite políčko Zobraziť rovnicu v grafe.
Ak chcete len vidieť trendovú čiaru, preskočte kroky 6 a 7.

(Voliteľné) Začiarknite políčko Zobraziť v grafe štvorcovú hodnotu R.

Kliknite na tlačidlo Zavrieť.

Excel zobrazí regresnú rovnicu a hodnotu R2 (popísané nižšie). Nasledujúci obrázok ukazuje graf s vykreslenou trendovou čiarou výkonu, regresnou rovnicou a hodnotou R2.

Graf s trendovou čiarou výkonu.

Keď je najlepšou trendovou čiarou výkonová krivka, regresná rovnica má nasledujúcu všeobecnú formu:

y = m * xb

y je závislá premenná; x je nezávislá premenná; a b a m sú konštanty.

Nie je k dispozícii žiadna funkcia pracovného hárka na priamy výpočet hodnôt b a m. Funkciu LINREGRESE však môžete použiť, ak výkonovú krivku „vyrovnáte“ použitím logaritmickej stupnice na závislé a nezávislé hodnoty:

{=LINEST(LN( známy_ys ), LN( známy_xs ), konšt. , štatistiky )}

Vykreslenie polynomickej trendovej čiary v Exceli

V mnohých scenároch reálneho sveta sa vzťah medzi závislými a nezávislými premennými nepohybuje jedným smerom. To by bolo príliš jednoduché. Napríklad namiesto neustáleho zvyšovania – rovnomerne, ako v lineárnom trende, prudko, ako v exponenciálnom alebo logaritmickom trende alebo stabilne, ako v silovom trende – sa údaje ako predaj jednotiek, zisky a náklady môžu pohybovať hore a dole. .

Na vizualizáciu takéhoto trendu môžete vykresliť polynomickú trendovú čiaru, ktorá je najvhodnejšou čiarou viacerých kriviek odvodených pomocou rovnice, ktorá používa niekoľko mocnín x. Počet sily x je poradie polynómu rovnice. Vo všeobecnosti platí, že čím vyššie poradie, tým užšia krivka zodpovedá vašim existujúcim údajom, ale tým nepredvídateľnejšie sú vaše prognózované hodnoty.

Ak už máte graf, postupujte podľa týchto krokov na pridanie polynomickej trendovej čiary v Exceli:

Kliknutím na graf ho vyberiete.

Ak má váš graf viacero radov údajov, kliknite na rad, ktorý chcete analyzovať.

Vyberte Návrh → Pridať prvok grafu → Trendová čiara → Ďalšie možnosti trendovej čiary.
Zobrazí sa tabla Formátovať trendovú čiaru.

Kliknite na kartu Možnosti trendovej čiary.

Vyberte prepínač Polynomial.

Kliknutím na šípky tlačidla Poradie nastavte poradie požadovanej polynómovej rovnice.

Excel vykreslí polynomickú trendovú čiaru.

(Voliteľné) Začiarknite políčko Zobraziť rovnicu v grafe.
Ak chcete len vidieť trendovú čiaru, vynechajte kroky 7 a 8.

(Voliteľné) Začiarknite políčko Zobraziť v grafe štvorcovú hodnotu R.

Kliknite na tlačidlo Zavrieť.

Excel zobrazí regresnú rovnicu a hodnotu R2. Obrázok nižšie ukazuje graf s vykreslenou trendovou čiarou polynómu, regresnou rovnicou a hodnotou R2.

Graf s polynomickou trendovou čiarou.

Keď je najlepšou trendovou čiarou polynomická krivka, regresná rovnica má nasledujúcu formu:

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

y je závislá premenná; x je nezávislá premenná; a b a m n až m1 sú konštanty.

Na výpočet hodnôt b a mn až m1 môžete použiť funkciu LINREGRESE, ak zvýšite hodnoty známeho_xs na mocniny od 1 do n pre polynóm n -tého rádu:

{=LINEST( známy_ys , známy_xs ^ {1,2,…, n }, const , štatistiky )}

Prípadne môžete použiť funkciu TREND:

{=TREND( známe_y , známe_x ^ {1,2,…, n }, nové_x , konšt. )}