Statistické funkce Excelu: INTERCEPT

Souhrn

Tento článek popisuje funkci INTERCEPT v Microsoft Excelu, ukazuje, jak tuto funkci používat, a porovnává její výsledky pro Excel 2003 a novější verze aplikace Excel s výsledky v dřívějších verzích aplikace Excel.

Další informace

Funkce INTERCEPT(known_y;known_x) vrátí funkci INTERCEPT lineární regresní přímky, která se používá k predikci hodnot y z hodnot x .

Syntaxe

INTERCEPT(known_y's,known_x's)

Argumenty known_y a known_x musí být matice nebo oblasti buněk, které obsahují stejný počet číselných datových hodnot. Funkce INTERCEPT často zahrnuje 2 oblasti buněk obsahující data, například INTERCEPT(A1:A100; B1:B100).

Příklad použití

Pokud chcete funkci INTERCEPT znázornit, 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:D13 v listu.

• V aplikaci Microsoft Office Excel 2007 klepněte na kartu Domů , klepněte na tlačítko Formát ve skupině Buňkya klepněte na tlačítko Přizpůsobit šířky sloupců.
• V excelu 2003 přejděte v nabídce Formát na Sloupec a potom klikněte na Přizpůsobit výběr.

Buňky B2:B7 můžete naformátovat jako číslo s 0 desetinnými místy a buňky A9:D13 jako číslo se 6 desetinnými místy.

Buňky A2:A7 a B2:B7 obsahují hodnoty y a hodnoty x, které volají INTERCEPT v buňce A10.

Ve verzích aplikace Excel, které jsou starší než Excel 2003, může funkce INTERCEPT vykazovat chyby zaokrouhlování. Excel 2003 a novější verze Excelu zlepšují chování interceptu. INTERCEPT(known_y, known_x) je výsledek vyhodnocení funkce PRŮMĚR(known_y) – SLOPE(known_y, known_x) * PRŮMĚR(known_x). I když kód pro INTERCEPT nebyl přímo změněn pro Excel 2003 a pro novější verze Excelu, chování INTERCEPT je vylepšeno kvůli vylepšenému kódu pro SLOPE.

Pokud máte starší verzi Excelu, můžete pomocí listu, který jste vytvořili dříve, spustit experiment a zjistit, kdy dojde k chybám zaokrouhlování. Přidání kladné konstanty ke každému pozorování v B2:B7 by nemělo mít vliv na hodnotu SLOPE. Pokud vykreslíte dvojice x,y s x na vodorovné ose a y na svislé ose a pak ke každé hodnotě x přidáte kladnou konstantu, data se jednoduše posunou doprava. Regresní přímka, která nejlépe vyhovuje, má stále stejný sklon. Posunutá data ale mají jiný průsečík.

Při výchozí hodnotě 0 v D3 je slope v A9 0,775280899. Buňka A10 zobrazuje hodnotu INTERCEPT a buňka A11 zobrazuje hodnotu výrazu, který se vyhodnotí při výpočtu FUNKCE INTERCEPT:

AVERAGE(known_y) – SKLON(known_y, known_x) * PRŮMĚR(known_x)

Hodnoty v buňkách A9 a A10 se vždy shodují, protože hodnota v buně A10 je přesně to, co funkce INTERCEPT vrací. Sklon by se neměl lišit, protože do known_x přidáte různé kladné konstanty. Buňka A11 zobrazuje průměr(known_y) – 0,775280899 * PRŮMĚR(known_x). Vzhledem k tomu, že hodnota SLOPE by se neměla měnit a hodnota SLOPE je 0,775280899, když D3 = 0, měly by hodnoty tohoto výrazu v buničce A11 také souhlasit s hodnotami v buňkách A9 a A10.

Pokud zvýšíte hodnotu v D3, přidáte do B2:B7 větší konstantu. Pokud D3 <= 7, pak se na prvních 6 desetinných místech funkce SLOPE nezobrazí žádné chyby zaokrouhlování. Pokud ale zkusíte 7,25, 7,5, 7,75 a 8, sklon v A9 se změní. V důsledku toho se hodnoty v buňkách A11 (které souhlasí s A10) a A12 liší. Hodnoty v A11 (nebo A10) a A12 by ale měly být stejné, protože přidání konstanty do known_x by nemělo mít vliv na hodnotu SLOPE.

D7:D13 zobrazí hodnoty, které funkce INTERCEPT vrátí, a hodnoty, které by funkce INTERCEPT měla vrátit, pokud se hodnota SLOPE nezměnila. Tyto dvojice hodnot se zobrazí v případech, kdy D3 = 7,5 a 8. Chyby zaokrouhlit se staly tak závažné, že k dělení 0 dojde, když D3 = 8.

Dřívější verze Excelu v těchto případech poskytují nesprávné odpovědi, protože účinky chyb zaokrouhlování jsou větší s výpočetním vzorcem, který tyto verze používají. Přesto tento experiment ukazuje, že případy, kdy k chybám dochází, jsou extrémní.

Pokud máte Excel 2003 nebo novější verzi Excelu, je mezi společnými hodnotami v A10 a A11 a hodnotou v A12 při pokusu o experiment malý nebo žádný rozdíl. Buňky D7:D13 ale zobrazují chyby zaokrouhlování, které získáte ve starších verzích Excelu.

Výsledky v dřívějších verzích Excelu

Článek o funkci SLOPE popisuje méně číselně robustní vzorec, který používají starší verze. Vzorec vyžaduje pouze jedno předání dat. Pouze nedostatky SLOPE v těchto verzích způsobují, že INTERCEPT v extrémních případech způsobuje chyby zaokrouhlování.

Výsledky v Excelu 2003 a novějších verzích Excelu

Excel 2003 a novější verze excelu používá vylepšený postup pro výpočet sklonu. Výsledkem je zlepšení výkonu interceptu. Vylepšený postup vyžaduje dvě průchody daty. Zlepšení opět popisuje následující článek o slope.

Další informace o vylepšeních funkce SLOPE pro aplikaci Excel 2003 a novější verze aplikace Excel naleznete v následujícím článku znalostní báze Microsoft Knowledge Base:

828142 statistické funkce Excelu: SLOPE

Závěry

Vzhledem k tomu, že Excel 2003 a novější verze Excelu nahrazují přístup s jedním průchodem dvěma průchody, je číselný výkon funkce SLOPE v excelu 2003 a novějších verzích aplikace Excel lepší než ve starších verzích aplikace Excel. Proto je číselný výkon INTERCEPT lepší. Výsledky v Excelu 2003 a novějších verzích excelu nebudou nikdy méně přesné než výsledky v dřívějších verzích Excelu.

Obvykle není rozdíl mezi výsledky v Excelu 2003 a novějších verzích excelu a výsledky v dřívějších verzích excelu, protože data se často nechovají neobvyklým způsobem, který tento experiment ilustruje. Číselná nestabilita se s největší pravděpodobností objeví v dřívějších verzích aplikace Excel, když data obsahují mnoho významných číslic a malé odchylky mezi datovými hodnotami.

Následující postup zjistí součet druhých odchylek o střední hodnotě vzorku:

1. Vyhledání ukázkové střední hodnoty
2. Vypočítejte každou druhou odchylku.
3. Součet druhých odchylek

Tento postup je přesnější než následující alternativní postup (známý také jako "vzorec kalkulačky", protože byl vhodný pro použití na kalkulačce pro malý počet datových bodů):

1. Zjistí součet druhých mocnin všech pozorování, velikost vzorku a součet všech pozorování.
2. Vypočítejte součet druhých mocnin všech pozorování minus ((součet všech pozorování)^2)/velikost vzorku).

Nahrazením této poslední procedury jedním průchodem procedurou dvou průchodů, která najde střední hodnotu vzorku při prvním průchodu a vypočítá součet kvadraických odchylek při druhém průchodu, excel 2003 a novější verze aplikace Excel vylepšují mnoho dalších funkcí. Krátký seznam takových funkcí zahrnuje VAR, VARP, STDEV, STDEVP, DVAR, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ a STEYX. Společnost Microsoft provedla podobná vylepšení v každém ze tří nástrojů analýza odchylek v doplňku Analytické nástroje.