Statistické funkce Excelu: GROWTH

Souhrn

Tento článek popisuje funkci GROWTH v Aplikaci Microsoft Office Excel 2003 a novějších verzích aplikace Excel, ukazuje, jak se tato funkce používá, a porovnává výsledky funkce pro Excel 2003 a novější verze aplikace Excel s výsledky funkce GROWTH v dřívějších verzích aplikace Excel. Funkce GROWTH se vyhodnocuje voláním související funkce LINREGRESE. Rozsáhlé změny funkce LINREGRESE pro Excel 2003 a novější verze excelu jsou shrnuty a jejich důsledky pro funkci GROWTH jsou zaznamenány.

Informace o aplikaci Microsoft Excel 2004 for Macintosh

Statistické funkce v Excelu 2004 pro Mac byly aktualizovány pomocí stejných algoritmů, které byly použity k aktualizaci statistických funkcí v Excelu 2003 a novějších verzích Excelu. Všechny informace v tomto článku, které popisují, jak funkce funguje nebo jak byla změněna pro Excel 2003 nebo novější verze Excelu, platí také pro Excel 2004 pro Mac.

Další informace

Funkce GROWTH(known_y, known_x, new_x, konstanta) se používá k provedení regresní analýzy tam, kde je exponenciální křivka fitována. Použije se kritérium nejmenších čtverců a funkce GROWTH se pokusí najít nejvhodnější kritérium. Known_y představují data na "závislé proměnné" a known_x představují data v jedné nebo více "nezávislých proměnných". Soubor nápovědy GROWTH popisuje vzácné případy, kdy může být druhý nebo třetí argument vynechán.

Za předpokladu, že existují proměnné prediktoru p, funkce GROWTH v podstatě volá funkci LOGEST. FUNKCE LOGEST odpovídá rovnici ve formě:

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

Jsou určeny hodnoty koeficientů, b, m1, m2, ..., mp, které nejlépe odpovídají datům y.

Pokud je poslední argument "konstanta" nastaven na hodnotu PRAVDA, chcete, aby regresní model zahrnoval násobitelský koeficient b v regresním modelu. Pokud je nastavená hodnota NEPRAVDA, b se vyloučí tím, že ho v podstatě nastavíte na hodnotu 1. Poslední argument je nepovinný. Pokud je argument vynechán, je interpretován jako PRAVDA.

Pro usnadnění vysvětlení ve zbývající části tohoto článku předpokládejme, že data jsou uspořádaná ve sloupcích tak, aby known_y byla sloupec dat y a known_x je jeden nebo více sloupců dat x. Rozměry (délky) každého z těchto sloupců musí být samozřejmě stejné. New_x se také předpokládá, že jsou uspořádané do sloupců a pro new_x musí být stejný počet sloupců jako pro known_x. Všechna níže uvedená pozorování jsou stejně pravdivá, pokud data nejsou uspořádaná do sloupců, ale je jednodušší prodiskutovat tento jediný (nejčastěji používaný) případ.

Po výpočtu nejvhodnějšího regresního modelu (v podstatě voláním funkce LOGEST v Excelu) vrátí funkce GROWTH predikované hodnoty přidružené k new_x.

Tento článek používá příklady, které ukazují, jak funkce GROWTH souvisí s funkcí LOGEST, a uvádí problémy s funkcí LOGEST ve verzích aplikace Excel starších než Excel 2003, které se překládají na problémy s funkcí GROWTH. Funkce GROWTH efektivně volá funkci LOGEST, spouští funkci LOGEST, používá regresní koeficienty ve výstupu funkce LOGEST ve svém výpočtu předpovídané hodnoty y, které jsou přidružené k jednotlivým řádkům new_x, a zobrazí vám tento sloupec predikovaných hodnot y. Proto musíte vědět o problémech při provádění funkce LOGEST. Když je volána funkce LOGEST, volá funkci LINREGRESE. I když pro excel 2003 a novější verze aplikace Excel nebyl kód pro funkci GROWTH a LOGEST přepsán, byly provedeny rozsáhlé změny (a vylepšení) v kódu LINREGRESE.

Jako dodatky k tomuto článku se důrazně doporučuje následující článek o funkci LINREGRESE. Obsahuje několik příkladů a dokumentů problémy s funkcí LINREGRESE ve verzích aplikace Excel, které jsou starší než Excel 2003.

Další informace o funkci LINREGRESE naleznete v následujícím článku znalostní báze Microsoft Knowledge Base:

828533 Popis funkce LINREGRESE v Excelu 2003 a Excelu 2004 pro Mac

Doporučuje se také soubor nápovědy linrevize linregrese pro Excel 2003.

Následující článek o funkci LOGEST vysvětluje, jak funkce LOGEST komunikuje s funkcí LINREGRESE. Tyto podrobnosti jsou zde vynechány.

Další informace získáte v následujícím článku znalostní báze Microsoft Knowledge Base:

828528 statistické funkce Excelu: LOGEST

Vzhledem k tomu, že se tento článek zaměřuje na číselné problémy ve verzích aplikace Excel, které jsou starší než Excel 2003, neobsahuje tento článek mnoho praktických příkladů použití funkce GROWTH. Soubor nápovědy v aplikaci GROWTH obsahuje užitečné příklady.

Syntaxe

GROWTH(known_y's, known_x's, new_x's, constant)

Argumenty known_y, known_x a new_x musí být matice nebo oblasti buněk, které mají související rozměry. Pokud je known_y jeden sloupec po m řádcích, pak known_x je c sloupců podle m řádků, kde c je větší než nebo rovno jedné. C je počet proměnných prediktoru; m je počet datových bodů. New_x pak musí být sloupce c po řádcích r, kde jsou větší než nebo rovny jedné. (Podobné relace v dimenzích musí být v případě, že jsou data rozložená do řádků místo sloupců.) Konstanta je logický argument, který musí být nastaven na hodnotu PRAVDA nebo NEPRAVDA (nebo 0 nebo 1, které Excel interpretuje jako NEPRAVDA nebo PRAVDA). Poslední tři argumenty funkce GROWTH jsou volitelné; v souboru nápovědy GROWTH najdete možnosti vynechání druhého argumentu, třetího argumentu nebo obou argumentů. Vynechání čtvrtého argumentu se interpretuje jako PRAVDA.

Nejběžnější použití funkce GROWTH zahrnuje dvě oblasti buněk, které obsahují data, například GROWTH(A1:A100; B1:F100; B101:F108; PRAVDA). Vzhledem k tomu, že obvykle existuje více než jedna proměnná předpovědi, druhý argument v tomto příkladu obsahuje více sloupců. V tomto příkladu je 100 předmětů, jedna závislá proměnná hodnota (known_y) pro každý předmět a pět závislých proměnných hodnot (known_x) pro každý předmět. Existuje osm dalších hypotetických předmětů, ve kterých chcete použít funkci GROWTH k výpočtu predikovaných hodnot y.

Příklad použití

K dispozici je příklad excelového listu, který ilustruje následující klíčové koncepty:

• Jak funkce GROWTH komunikuje s logestem
• Problémy, ke kterým dochází s funkcí GROWTH (nebo LOGEST a LINREGRESE) z důvodu kobinárních known_x ve verzích aplikace Excel starších než Excel 2003

Pro ilustraci funkce RŮST vytvořte prázdný excelový list, zkopírujte následující tabulku, vyberte buňku A1 v prázdném excelovém listu a vložte položky tak, aby následující tabulka vyplnila buňky A1:K35 v listu.

Data pro růst jsou v buňkách A1:C8. (Položky v buňkách D2:D6 nejsou součástí dat, ale jsou použity pro ilustraci níže.) V buňkách E10:E16 a I10:116 jsou uvedeny výsledky funkce GROWTH pro obě starší verze Excelu a novější verze Excelu. Výsledky v buňkách A10:A16 budou odpovídat verzi Excelu, kterou používáte. Prozatím se při zkoumání toho, jak funkce GROWTH volá funkci LOGEST a jak funkce GROWTH používá výsledky funkce LOGEST, zaměřte se prozatím na výsledky v Excelu 2003 a novějších verzích Excelu.

Funkce GROWTH a LOGEST se dají zobrazit jako interakce v následujících krocích:

1. Nazýváte FUNKCE GROWTH(known_y, known_x, new_x, konstanta).
2. FUNKCE GROWTH volá funkci LOGEST(known_y, known_x, konstanta, PRAVDA).
3. Z tohoto volání funkce LOGEST jsou získány regresní koeficienty. Tyto koeficienty se zobrazí v prvním řádku výstupní tabulky LOGEST.
4. Pro každý řádek new_x se predikovaná hodnota y vypočítá na základě těchto koeficientů LOGEST a hodnot new_x v daném řádku.
5. Vypočtená hodnota v kroku 4 je vrácena v příslušné buňce pro výstup FUNKCE GROWTH, která odpovídá řádku new_x.

Pokud má funkce GROWTH vrátit odpovídající výsledky, musí funkce LOGEST vygenerovat odpovídající výsledky v kroku 3. Vzhledem k tomu, že vyhodnocení funkce LOGEST v kroku 3 vyžaduje volání funkce LINREGRESE, je nezbytné, aby funkce LINREGRESE byla správně zachovaná. Problémy s funkcí LINREGRESE ve verzích aplikace Excel, které jsou starší než Excel 2003, pocházejí ze sloupců kolineárních prediktorů. (V dřívějších verzích aplikace Excel dochází k dalším problémům s funkcí LINREGRESE a LOGEST, ke kterým dochází, když je poslední argument funkce GROWTH nastaven na hodnotu NEPRAVDA. Tyto problémy však nemají vliv na výsledky funkce GROWTH a nejsou zde popsány.)

Sloupce prediktoru (known_x) jsou kobinované, pokud alespoň jeden sloupec c může být vyjádřen jako součet násobků ostatních sloupců, c1, c2 a dalších sloupců. Sloupec c se často označuje jako redundantní, protože informace, které obsahuje, lze vytvořit ze sloupců c1, c2 a dalších sloupců. Základním principem existence koinearity je, že výsledky by neměly být ovlivněny tím, jestli je redundantní sloupec zahrnut do původních dat nebo odebrán z původních dat. Vzhledem k tomu, že funkce LINREGRESE ve verzích aplikace Excel, které jsou starší než Excel 2003, nehledaly koinearitu, byl tento princip snadno porušen. Sloupce předpovědi jsou téměř kolineární, pokud lze alespoň jeden sloupec c vyjádřit jako téměř stejný součet násobků ostatních sloupců, c1, c2 a dalších sloupců. V tomto případě "téměř shodný" znamená malý součet čtvercových odchylek položek v c od odpovídajících položek ve vážené hodnotě c1, c2 a dalších sloupců. Například hodnota "Velmi malá" může být menší než 10^(-12).

První model v řádcích 10 až 12 používá sloupce B a C jako prediktory a požaduje, aby Excel namodeloval konstantu (poslední argument je nastavený na HODNOTU PRAVDA). Excel pak efektivně vloží další sloupec předpovědi, který vypadá stejně jako buňky D2:D6. Je snadné si všimnout, že položky ve sloupci C v řádcích 2 až 6 se přesně rovnají součtu odpovídajících položek ve sloupcích B a D. Proto existuje koinearita, protože sloupec C je součet násobků následujících položek:

• Sloupec B
• Další sloupec aplikace Excel obsahující 1s, který je vložen, protože byl vynechán třetí argument funkce LOGEST nebo pravda (normální případ).

To způsobuje takové číselné problémy, že verze aplikace Excel starší než Excel 2003 nemohou vypočítat výsledky. Proto je výstupní tabulka GROWTH vyplněna #NUM!.

Druhý model, v řádcích 14 až 16, je model, který dokáže úspěšně zpracovat libovolná verze Excelu. Neexistuje žádná konikarita a uživatel znovu požádá Excel o modelování konstanty. Tento model je zde zahrnut z následujících důvodů:

• Za prvé je nejtypičtější pro praktické případy: že neexistuje žádná koinearita. Tyto případy jsou dostatečně zpracovány ve všech verzích Excelu. Mělo by být uklidňující vědět, že číselné problémy pravděpodobně nenastanou v nejběžnějším praktickém případě, pokud máte starší verzi Excelu.
• Za druhé, tento příklad se používá k porovnání chování Aplikace Excel 2003 a novějších verzí aplikace Excel v obou modelech. Většina hlavních statistických balíčků analyzuje kolinearitu, odebere z modelu sloupec, který je součtem násobků ostatních, a upozorní uživatele zprávou, jako je "sloupec C je lineárně závislý na jiných sloupcích s předpověďmi a byl z analýzy odebrán."

V aplikaci Excel 2003 a novějších verzích aplikace Excel se taková zpráva nesděluje ve výstraze nebo v textovém řetězci, ale ve výstupní tabulce LOGEST. Funkce GROWTH nemá žádný mechanismus pro doručení takové zprávy uživateli. Ve výstupní tabulce LOGEST odpovídá regresní koeficient, který je jeden a jehož standardní chyba je nula, koeficientu pro sloupec, který byl odebrán z modelu. Výstupní tabulky LOGEST jsou zahrnuté v řádcích 23 až 35 odpovídající výstupu GROWTH v řádcích 10 až 16. Položky v buňkách I24:I25 zobrazují odstraněný redundantní sloupec prediktoru. V tomto případě se funkce LOGEST rozhodla odebrat sloupec C (koeficienty v buňkách I24, J24, K24 odpovídají sloupcům C, B a konstantnímu sloupci excelu). Pokud existuje koinearita, je možné odebrat libovolný z příslušných sloupců a volba je libovolná.

V druhém modelu v řádcích 30 až 35 neexistuje žádná kostra a žádný sloupec se neodebere. Vidíte, že predikované hodnoty y jsou v obou modelech stejné. K tomuto problému dochází, protože odebrání redundantního sloupce, který je součtem násobků ostatních, nesnižuje dobré přizpůsobení výsledného modelu. Tyto sloupce jsou odebrány právě proto, že nepředstavují žádnou přidanou hodnotu při hledání co nejlepších čtverců. Pokud také prozkoumáte výstup FUNKCE LOGEST v buňkách I23:K35 v Excelu 2003 a novějších verzích Excelu, všimnete si, že poslední tři řádky výstupních tabulek jsou stejné. Položky v buňkách I31:J32 a J24:K25 se navíc shodují. To ukazuje, že při zahrnutí sloupce C do modelu se získají stejné výsledky, ale zjistí se, že je redundantní (výstup v buňkách I24:K28), jako když byl sloupec C eliminován před spuštěním funkce LOGEST (výstup v buňkách I31:J35). To splňuje základní zásadu existence koinearity.

V buňkách A18:C21 používá Microsoft data z Excelu 2003 a novějších verzí Excelu k ilustraci, jak funkce GROWTH přebírá výstup funkce LOGEST a vypočítá relevantní predikované hodnoty y. Zkoumáním vzorců v buňkách A20:A21 a buňkách C20:C21 můžete zjistit, jak se koeficienty FUNKCE LOGEST kombinují s daty new_x v buňkách B7:C8 pro každý z těchto dvou modelů (pomocí sloupců B, C jako prediktorů; pomocí pouze sloupce B jako predikce).

V aplikaci Excel 2003 a v novějších verzích aplikace Excel je zjištěna kolinearita, protože funkce LOGEST volá funkci LINREGRESE. Funkce LINREGRESE používá jiný přístup k řešení regresních koeficientů. Tento přístup spočívá v rozkladu QR. Článek LINREGRESE obsahuje návod na algoritmus rozkladu QR pro malý příklad.

Souhrn výsledků v dřívějších verzích Excelu

Výsledky FUNKCE GROWTH jsou nepříznivě ovlivněny ve verzích aplikace Excel starších než Excel 2003 kvůli nepřesným výsledkům funkce LOGEST, které pak pocházejí z nepřesných výsledků funkce LINREGRESE.

Funkce LINREGRESE byla vypočítána pomocí přístupu, který nevěnoval pozornost problémům s koinearitou. Existence koinearity způsobila chyby zaokrouhlování, nevhodné standardní chyby regresních koeficientů a nevhodné stupně volnosti. Někdy jsou problémy se zaokrouhlování natolik závažné, že funkce LINREGRESE vyplnila výstupní tabulku #NUM!. Pokud si stejně jako ve většině případů v praxi můžete být jistí, že neexistují kolineární (nebo téměř kolineární) sloupce prediktoru, funkce LINREGRESE by obecně poskytovala přijatelné výsledky. Proto mohou být uživatelé funkce GROWTH podobně klidní, pokud vidí, že neexistují koineární (nebo téměř koineární) sloupce prediktoru.

Souhrn výsledků v Excelu 2003 a novějších verzích Excelu

Vylepšení funkce LINREGRESE zahrnují přepnutí na metodu rozkladu QR určující regresní koeficienty. Rozklad QR má následující výhody:

• Lepší číselná stabilita (obecně menší chyby zaokrouhlení)
• Analýza problémů s koinearitou

Všechny problémy s verzemi aplikace Excel staršími než Excel 2003, které jsou znázorněny v tomto článku, byly opraveny pro aplikaci Excel 2003 a novější verze aplikace Excel. Tato vylepšení funkce LINREGRESE se překládají na vylepšení logestu a RŮSTU.

Závěry

Výkon funkce GROWTH byl vylepšen, protože funkce LINREGRESE byla výrazně vylepšena pro Excel 2003 a novější verze aplikace Excel. Vylepšení funkce LINREGRESE ovlivňují také funkci LOGEST, protože funkce LOGEST je volána nástrojem GROWTH. Uživatelé starších verzí Excelu by měli před použitím funkce GROWTH ověřit, že sloupce s prediktorem nejsou kolineární.

Většina materiálů uvedených v tomto článku a v článku LINREGRESE se může zpočátku zdát alarmující pro uživatele verzí aplikace Excel, které jsou starší než Excel 2003. Je však třeba poznamenat, že koinearita je problémem pouze v malém procentu případů. Dřívější verze Excelu poskytují přijatelné výsledky funkce GROWTH, pokud neexistuje kobinita.

Vylepšení funkce LINREGRESE naštěstí ovlivňují také nástroj lineární regrese analytického nástroje (tento nástroj označuje funkci LINREGRESE) a dvě další související funkce Excelu: LOGEST a TREND.