Funkce Trend v aplikaci Excel
Funkce Trend v aplikaci Excel je statistická funkce, která vypočítává lineární trendovou čáru na základě dané lineární sady dat. Vypočítá prediktivní hodnoty Y pro dané hodnoty pole X a použije metodu nejmenších čtverců na základě daných dvou datových řad. Funkce Trend v aplikaci Excel vrací čísla v lineárním trendu odpovídajícím známým datovým bodům, což jsou existující data, u nichž trend v aplikaci Excel předpovídá, že hodnoty Y v závislosti na hodnotách X musí být lineárními daty.
Jaká je metoda nejmenších čtverců?
Jedná se o techniku používanou v regresní analýze, která pro danou datovou sadu najde linii nejvhodnější (je to čára procházející bodovým grafem datových bodů, která indikuje především vztah mezi těmito body), která pomáhá vizualizovat vztah mezi daty bodů.
Syntax
Níže je vzorec TRENDU v aplikaci Excel.
Argumenty
Pro danou lineární rovnici y = m * x + c
Known_y’s : It is a required argument that represents the set of y-values that we already have as existing data in a dataset that follows the relationship y = mx + c.
Known_x's: Je to volitelný argument, který představuje sadu hodnot x, které by měly mít stejnou délku jako sada známá_y. Je-li tento argument vynechán, sada argumentů known_x má hodnotu (1, 2, 3… atd.).
New_x's: Je to také volitelný argument. Jedná se o číselné hodnoty, které představují hodnotu new_x. Pokud je argument new_x vynechán, je nastaven tak, aby se rovnal argumentu known_x.
Const: Jedná se o volitelný argument, který určuje, zda je konstantní hodnota c rovna 0. Pokud je const TRUE nebo je vynecháno, c se počítá normálně. Pokud false, c je bráno jako 0 (nula) a hodnoty m jsou upraveny tak, aby y = mx.
Jak používat funkci TREND v aplikaci Excel?
Funkce TREND v aplikaci Excel je velmi jednoduchá a snadno použitelná. Pojďme pochopit fungování funkce TREND na několika příkladech.
Příklad č. 1
V tomto příkladu předpokládejme, že máme data pro výsledky testů s jejich GPA, nyní s využitím těchto daných dat musíme provést predikci pro GPA. Ve sloupci A a B máme existující data, existující hodnoty GPA odpovídající skóre jsou známé hodnoty Y a stávající hodnoty skóre jsou známé hodnoty X. U některých hodnot pro X jsme uvedli hodnoty jako skóre a musíme předpovědět hodnoty Y, což je GPA, na základě stávajících hodnot.
Stávající hodnoty:
Dané hodnoty a hodnoty Y, které se mají předvídat:
Abychom mohli předpovědět hodnoty GPA pro dané výsledky testů v buňce D2, D3 a D4, použijeme v aplikaci Excel funkci TREND.
Vzorec TREND v aplikaci Excel převezme stávající hodnoty známých X a Y a předáme nové hodnoty X pro výpočet hodnot Y v buňce E2, E3 a E4.
Vzorec TREND v aplikaci Excel bude:
= TREND ($ A $ 2: $ A $ 16, $ B $ 2: $ B $ 16, D2)
Opravili jsme rozsah pro známé hodnoty X a Y a předali jsme novou hodnotu X jako referenční hodnotu. Aplikujeme stejný vzorec TRENDU v Excelu na jiné buňky, máme
Výstup:
Takže pomocí funkce TREND v aplikaci Excel výše jsme předpověděli tři hodnoty Y pro dané nové výsledky testů.
Příklad č. 2 - Predikce růstu prodeje
V tomto příkladu tedy máme existující údaje o prodeji společnosti za rok 2017, které se lineárně zvyšují od ledna 2017 do prosince 2017. Musíme zjistit tržby za dané nadcházející měsíce. To znamená, že musíme předpovědět hodnoty prodeje na základě prediktivních hodnot pro data za jeden rok.
Existující data obsahují data ve sloupci A a tržby z prodeje ve sloupci B. Musíme vypočítat odhadovanou hodnotu prodeje pro příštích 5 měsíců. Historická data jsou uvedena níže:
Abychom mohli předpovědět tržby za dané měsíce v příštím roce, použijeme funkci TREND v aplikaci Excel, protože hodnota prodeje se zvyšuje lineárně, dané známé hodnoty Y jsou tržby z prodeje a známé hodnoty X jsou data ukončení měsíce, nové hodnoty X jsou data pro další 3 měsíce, tj. 31. 1. 2018, 28. 2. 2018 a 31. 3. 2018, a musíme vypočítat odhadované hodnoty prodeje na základě historické údaje uvedené v rozsahu A1: B13.
Vzorec TREND v aplikaci Excel převezme stávající hodnoty známých X a Y a předáme nové hodnoty X pro výpočet hodnot Y v buňce E2, E3 a E4.
Vzorec TREND v aplikaci Excel bude:
= TREND ($ B $ 2: $ B $ 13, $ A $ 2: $ A $ 13, D2)
Opravili jsme rozsah pro známé hodnoty X a Y a předali jsme novou hodnotu X jako referenční hodnotu. Použití stejného vzorce TREND v aplikaci Excel na další buňky, které máme,
Výstup:
Pomocí výše uvedené funkce TREND jsme tedy v buňce D2, D3 a D4 předpověděli odhadované hodnoty prodeje pro dané nadcházející měsíce.
Věci k zapamatování
- Existující historická data, která obsahují známé hodnoty X a Y, musí být lineárními daty, která by pro dané hodnoty X měla odpovídat lineární křivce y = m * x + c. Jinak může být výstup nebo předpokládané hodnoty nepřesné.
- Funkce TREND v aplikaci Excel generuje #HODNOTA! Chyba, když dané známé hodnoty X nebo Y jsou nečíselné nebo hodnota nového X je nečíselná a také když argument const není logická hodnota (to je PRAVDA nebo NEPRAVDA)
- Funkce TREND v aplikaci Excel generuje #REF! Chybně známé hodnoty X a Y mají různé délky.
