Excel statisztikai függvények: NÖVEKEDÉS

Összefoglalás

Ez a cikk az Excel GROWTH függvényét ismerteti, bemutatja a függvény használatát, és összehasonlítja az Excel 2003 és az Excel későbbi verzióiban használt függvény eredményeit az Excel korábbi verzióiban a NÖVEKEDÉS eredményével. A NÖVEKEDÉS kiértékelése a kapcsolódó függvény, a LINEST meghívásával történik. Az Excel 2003-hoz és az Excel későbbi verzióihoz készült LINEST-módosítások összegzése és a növekedésre gyakorolt hatásuk is feljegyezve van.

A Microsoft Excel 2004 Macintosh verziójának információi

A Mac Excel 2004 statisztikai függvényeit ugyanazokkal az algoritmusokkal frissítettük, mint az Excel 2003-ban és az Excel későbbi verzióiban. A cikkben található információk, amelyek beírják, hogyan működik egy függvény, illetve hogyan módosították a függvényeket az Excel 2003-hoz vagy az Excel újabb verzióihoz, a Mac Excel 2004-et is érinti.

További információ

A GROWTH függvény (ismeretes Y-értékek, ismeretes X-értékek, új X-értékek, állandó) regressziós analízisre szolgál, ahol exponenciális görbét illesztenek. A rendszer a legkisebb négyzetes feltételt használja, és a GROWTH megpróbálja megtalálni a legjobb illeszkedést az adott feltételben. Known_y a "függő változó" adatait, known_x pedig egy vagy több "független változó" adatait képviselik. A NÖVEKEDÉSI súgófájl azokat a ritka eseteket tárgyalja, amikor a második vagy a harmadik argumentum kihagyható.

Feltételezve, hogy vannak p prediktor változók, a GROWTH lényegében a LOGEST-et hívja meg. A LOGEST egy egyenletet illeszt a következő formában:

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

A b, m1, m2, ..., mp együttható értékei úgy vannak meghatározva, hogy a legjobban illeszkedjenek az y adatokhoz.

Ha az utolsó "állandó" argumentum értéke igaz, akkor a regressziós modellben tartalmaznia kell a b multiplikatív együtthatót. Ha FALSE (HAMIS) értékre van állítva, akkor a b lényegében 1-re van állítva, így kizárásra kerül. Az utolsó argumentum nem kötelező; ha az argumentum nincs megadva, akkor igazként van értelmezve.

A cikk további részében látható elrendezés megkönnyítése érdekében tegyük fel, hogy az adatok oszlopokba vannak rendezve, így known_y y adatoszlop, known_x pedig egy vagy több x adatoszlop. Ezeknek az oszlopoknak a méreteinek (hosszúságainak) egyenlőnek kell lenniük. New_x is feltételezzük, hogy oszlopokba vannak rendezve, és a new_x oszlopainak ugyanannyinak kell lennie, mint known_x. Az alábbi megfigyeléseink egyformán igazak, ha az adatok nem oszlopokba vannak rendezve, de egyszerűbb megvitatni az egyetlen (leggyakrabban használt) esetet.

Miután kiszámította a legjobb illeszkedési regressziós modellt (lényegében az Excel LOGEST függvényének meghívásával), a GROWTH a new_x-hoz társított előrejelzett értékeket adja vissza.

Ez a cikk példákkal szemlélteti, hogyan kapcsolódik a NÖVEKEDÉS a LOGEST-hez, és rámutat a LOGEST-tal kapcsolatos problémákra az Excel 2003-nál korábbi verzióiban, amelyek a NÖVEKEDÉSsel kapcsolatos problémákat okoznak. A GROWTH hatékonyan meghívja a LOGEST-et, végrehajtja a LOGEST-et, regressziós együtthatókat használ a LOGEST-kimenetben a new_x egyes soraihoz társított előrejelzett y értékek kiszámításához, és megjeleníti Önnek az előrejelzett y értékek oszlopát. Ezért ismernie kell a LOGEST végrehajtásával kapcsolatos problémákat. Amikor a LOGEST hívásra kerül, az gyakorlatilag meghívja a LINEST-et. Bár a GROWTH és a LOGEST kódját nem írták át az Excel 2003-hoz és az Excel későbbi verzióihoz, a LINEST-kód jelentős módosításai (és fejlesztései) történtek.

A cikk kiegészítéseként erősen ajánlott a LINEST-ról szóló alábbi cikk. Az Excel 2003-nál korábbi verzióiban számos példát és dokumentumot tartalmaz a LINEST használatával kapcsolatos problémákra.

A LINEST-ről a microsoftos tudásbázisban a következő cikkszámra kattintva tekintheti meg a cikket:

828533 A LINEST függvény leírása az Excel 2003-ban és a Mac Excel 2004-ben

Az Excel 2003-hoz módosított LINEST súgófájl is ajánlott.

A LOGEST-ről szóló alábbi cikk bemutatja, hogy a LOGEST hogyan kommunikál a LINEST-tal. Ezek a részletek itt nincsenek megadva.

További információ: Excel statisztikai függvények: LOGEST.

Mivel ebben a cikkben az Excel 2003-nál korábbi verzióiban előforduló numerikus problémákra összpontosítunk, ez a cikk nem sok gyakorlati példát mutat be a NÖVEKEDÉS használatára. A GROWTH súgófájlja hasznos példákat tartalmaz.

Szemantika

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

Az argumentumoknak, a known_y-knek, a known_x-eknek és a new_x-eknek olyan tömböknek vagy cellatartományoknak kell lenniük, amelyek összefüggő dimenziókkal rendelkeznek. Ha known_y egy oszlop m sor szerint, akkor known_x a c oszlopa m sor szerint, ahol a c nagyobb vagy egyenlő egynél. C a prediktor változók száma; m az adatpontok száma. Az új x-eknek c oszlopból és r sorból kell állniuk, ahol r nagyobb vagy egyenlő, mint egy. (A dimenziók hasonló kapcsolatainak akkor kell tartaniuk, ha az adatok oszlopok helyett sorokba kerülnek.) Az állandó egy logikai argumentum, amelyet IGAZ vagy HAMIS (vagy 0 vagy 1 értékre kell állítani, amelyet az Excel HAMIS vagy IGAZ értékként értelmez). A GROWTH utolsó három argumentuma mindegyike nem kötelező; a második argumentum, a harmadik argumentum vagy mindkettő kihagyásáról a GROWTH súgófájlban talál információt; a negyedik argumentum kihagyása IGAZ értékként van értelmezve.

A NÖVEKEDÉS leggyakoribb használata olyan két cellatartományra vonatkozik, amelyek az adatokat tartalmazzák, mint például a GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Mivel általában több prediktorváltozó is létezik, a példában szereplő második argumentum több oszlopot tartalmaz. Ebben a példában 100 tárgy, egy függő változóérték (known_y) és öt függő változóérték (known_x) van mindegyik tárgyhoz. Nyolc további hipotetikus alany van, amelyekben a NÖVEKEDÉS függvényt szeretné használni az előrejelzett y értékek kiszámításához.

Példa a használatra

Az Excel-munkalapon látható példa a következő fő fogalmakat szemlélteti:

• Hogyan hat kölcsön a NÖVEKEDÉS és a LOGEST
• Az Excel 2003-nál korábbi verzióiban előforduló kollineáris known_x-ok miatt a NÖVEKEDÉS (vagy LOGEST és LINEST) során jelentkező problémák

Megjegyzés:

A LINEST kontextusában a második listajeles elem részletes ismertetését a LINEST-ről szóló cikkben találja.

A NÖVEKEDÉS függvény szemléltetéséhez hozzon létre egy üres Excel-munkalapot, másolja a következő táblázatot, jelölje ki az A1 cellát az üres Excel-munkalapon, majd illessze be a bejegyzéseket úgy, hogy az alábbi táblázat kitöltse az A1:K35 cellákat a munkalapon.

Egy	B	C	D	E	F	G	H	Én	J	K
y:	X:
=EXP(F2)	1	2	1		1
=EXP(F3)	3	4	1		2
=EXP(F4)	4	5	1		3
=EXP(F5)	6	7	1		4
=EXP(F6)	7	8	1		5
új x-ek:	9	11
	12	14
NÖVEKEDÉS a B,C oszlopokkal:				Az Excel 2002 és az Excel korábbi verzióinak értékei:
			Az Excel 2003 és az Excel későbbi verzióinak értékei:
=NÖVEKEDÉS(A2:A6;B2:C6;B7:C8;IGAZ)				#NUM!				472,432432563203
=NÖVEKEDÉS(A2:A6;B2:C6;B7:C8;IGAZ)				#NUM!				3400.16400895377
NÖVEKEDÉS csak a B oszlop segítségével
=NÖVEKEDÉS(A2:A6;B2:B6;B7:B8;IGAZ)				472,432432563203				472,432432563203
=NÖVEKEDÉS(A2:A6;B2:B6;B7:B8;IGAZ)				3400.16400895377				3400.16400895377
A LOGEST eredmények illesztett értékei az Excel 2003-ban és a későbbi Excel verziókban
A B és C oszlopok használata		A B oszlop használata
=EXP(LN(K24)*1 + LN(J24)*B7 + LN(I24)*C7)		=EXP(LN(J31)*1 + LN(I31)*B7)
=EXP(LN(K24)*1 + LN(J24)*B8 + LN(I24)*C8)		=EXP(LN(J31)*1 + LN(I31)*B8)
LOGEST a B,C oszlopokkal:				Az Excel 2002 és az Excel korábbi verzióinak értékei:				Az Excel 2003 és az Excel későbbi verzióinak értékei:
=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)		#NUM!	#NUM!	#NUM!		1	1.9307233720034	1.26724101129183
=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)		#NUM!	#NUM!	#NUM!		0	0.043859649122807	0.206652964726136
=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)		#NUM!	#NUM!	#NUM!		0.986842105263158	0.209426954145848	#N/A
=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)		#NUM!	#NUM!	#NUM!		225	3	#N/A
=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:C6;IGAZ;IGAZ)		#NUM!	#NUM!	#NUM!		9.86842105263158	0.131578947368421	#N/A
LOGEST csak a B oszlopot használja
=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)			1.9307233720034	1.26724101129183			1,9307233720034	1.26724101129183
=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)			0.0438596491228071	0.206652964726136			0.043859649122807	0.206652964726136
=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)			0.986842105263158	0.209426954145848			0.986842105263158	0.209426954145848
=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)			224.999999999999	3			225	3
=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)	=LOGEST(A2:A6;B2:B6;IGAZ;IGAZ)			9.86842105263158	0.131578947368421			9.86842105263158	0.131578947368421

Megjegyzés:

Miután beillesztte ezt a táblázatot az új Excel-munkalapra, válassza a Beillesztés beállításai gombot, majd válassza a Célformátum egyeztetése lehetőséget. Ha a beillesztett tartomány továbbra is ki van jelölve, használja az alábbi eljárások egyikét, a futtatott Excel-verziónak megfelelően:

• A Microsoft Office Excel 2007-ben válassza a Kezdőlap lapot, a Cellák csoportban a Formátum lehetőséget, majd az Oszlopszélesség automatikus méretezése lehetőséget.
• Az Excel 2003-ban mutasson a Formátum menü Oszlop pontjára, majd válassza az Automatikus méretezés kijelölése lehetőséget.

A NÖVEKEDÉS adatai az A1:C8 cellában találhatók. (A D2:D6 cellák bejegyzései nem részei az adatoknak, de az alábbi ábrán láthatók.) A NÖVEKEDÉS két különböző modell esetében az Excel korábbi verzióiban és az Excel későbbi verzióiban az E10:E16 és az I10:116 cellában jelenik meg. Az A10:A16 cella az Excel használt verziójának felel meg. Egyelőre az Excel 2003 és az Excel későbbi verziói eredményeire összpontosíthat, amikor azt vizsgálja, hogy a NÖVEKEDÉS hogyan hívja meg a LOGEST-et, és hogyan használja a NÖVEKEDÉS a LOGEST-eredményeket.

A NÖVEKEDÉS és a LOGEST az alábbi lépésekben tekinthető meg interakciónak:

1. A NÖVEKEDÉS (known_y, known_x, new_x, állandó)
2. A NÖVEKEDÉS meghívja a LOGEST(known_y, known_x, állandó, IGAZ)
3. A LOGEST felé irányuló hívás regressziós együtthatóit a rendszer lekérte. Ezek az együtthatók a LOGEST kimeneti táblázatának első sorában jelennek meg.
4. Az egyes new_x soraiban az előrejelzett y érték kiszámítása ezen LOGEST-együtthatók és a new_x értékei alapján történik.
5. A 4. lépésben megadott számított értéket a rendszer az adott new_x sorának megfelelő NÖVEKEDÉSI kimenetnek megfelelő cellában adja vissza.

Ha a NÖVEKEDÉS a megfelelő eredményeket adja vissza, akkor a LOGEST-nek a 3. lépésben létre kell hoznia a megfelelő eredményeket. Mivel a LOGEST kiértékeléséhez a 3. lépésben sorba kell hívni a LINEST-t, elengedhetetlen, hogy a LINEST megfelelően viselkedjen. Az Excel 2003-nál korábbi verzióiban előforduló LINEST-problémák kollineáris prediktoroszlopokból származnak. (Az Excel korábbi verzióiban a LINEST és a LOGEST egyéb problémákat is tapasztal, amelyek akkor fordulnak elő, ha a NÖVEKEDÉS argumentum utolsó argumentuma HAMIS értékre van állítva. Ezek a problémák azonban nem befolyásolják a NÖVEKEDÉS eredményeit, és itt nem tárgyaljuk.)

A prediktoroszlopok (known_x) kollineárisak, ha legalább egy oszlop( c) más oszlopok, c1, c2 és más oszlopok többszöröseinek összegeként fejezhető ki. A c oszlopot gyakran redundánsnak nevezik, mert a benne található információk a c1, c2 és más oszlopokból hozhatók létre. A kollinearitás meglétének alapelve, hogy az eredményeket nem szabad érinteni azzal, hogy egy redundáns oszlop szerepel-e az eredeti adatokban, vagy eltávolítják-e az eredeti adatokból. Mivel az Excel 2003-nál korábbi verzióiban a LINEST nem a kollinearitást keresi, ezt az elvet könnyen megsértették. A prediktoroszlopok majdnem kollineárisak, ha legalább egy oszlop( c) majdnem egyenlő a többi, c1, c2 és más oszlopok többszöröseinek összegével. Ebben az esetben a "majdnem egyenlő" azt jelenti, hogy a c-re vonatkozó bejegyzések négyzetes eltéréseinek kis összegét kell figyelembe venni a c1, c2 és más oszlopok súlyozott összegében található megfelelő bejegyzésekhez képest. A "Nagyon kicsi" érték például 10^(-12) alatti lehet.

Az első modell a 10–12. sorban a B és a C oszlopot használja előrejelzőként, és kéri az Excelt az állandó modellezésére (az utolsó argumentum értéke IGAZ). Az Excel ezután hatékonyan beszúr egy további prediktoroszlopot, amely ugyanúgy néz ki, mint a D2:D6 cella. Könnyen észreveheti, hogy a 2–6. sor C oszlopának bejegyzései pontosan megegyeznek a B és a D oszlop megfelelő bejegyzéseinek összegével. Ezért kollinearitás van jelen, mert a C oszlop a következő elemek többszöröseinek összege:

• B oszlop
• Az Excel további 1-esek oszlopa, amely azon okból van beszúrva, mert a LOGEST harmadik argumentuma ki lett hagyva, vagy IGAZ volt megadva (a "normál" eset)

Ez olyan numerikus problémákat okoz, amelyek miatt az Excel 2003-nál korábbi verziói nem tudják kiszámítani az eredményeket. Ezért a GROWTH kimeneti táblázat #NUM!-mal van kitöltve.

A második modell a 14–16. sorban olyan, amelyet az Excel bármely verziója sikeresen képes kezelni. Nincs kollinearitás, és a felhasználó ismét az Excelt kéri az állandó modellezésére. Ez a modell a következő okok miatt jelenik meg:

• Először is a gyakorlati esetekre jellemző, hogy nincs kollinearitás. Ezeket az eseteket az Excel minden verziójában megfelelően kezelik. Megnyugtató lehet tudni, hogy a leggyakoribb gyakorlati esetben nem valószínű, hogy numerikus problémák lépnek fel, ha az Excel egy korábbi verziójával rendelkezik.
• Másodszor, ez a példa az Excel 2003 és az Excel későbbi verzióinak viselkedését hasonlítja össze a két modellben. A legtöbb fő statisztikai csomag elemzi a kollinearitást, eltávolít egy olyan oszlopot, amely több más többszörösét tartalmazza a modellből, és egy üzenettel értesíti a felhasználót, például: "A C oszlop lineárisan függ a többi prediktoroszloptól, és el lett távolítva az elemzésből".

Az Excel 2003-ban és az Excel későbbi verzióiban ez az üzenet nem egy riasztásban vagy egy szöveges sztringben, hanem a LOGEST kimeneti táblában jelenik meg. A NÖVEKEDÉS nem rendelkezik olyan mechanizmussal, amely ilyen üzenetet küld a felhasználónak. A LOGEST kimeneti táblában egy regressziós együttható, amely egy, és amelynek a standard hibája nulla, megfelel a modellből eltávolított oszlop együtthatójának. A LOGEST kimeneti táblázatai a 23–35. sorba tartoznak, amelyek a 10–16. sor NÖVEKEDÉSI kimenetének felelnek meg. Az I24:I25 cellában lévő bejegyzések egy kiküszöbölt redundáns prediktoroszlopot mutatnak. Ebben az esetben a LOGEST úgy döntött, hogy eltávolítja a C oszlopot (az I24, J24, K24 cella együtthatói a C, B és Excel állandó oszlopnak felelnek meg). Ha kollinearitás van jelen, az érintett oszlopok bármelyike eltávolítható, és a választás tetszőleges.

A második modellben a 30–35. sorban nincs kollinearitás, és nincs eltávolítva oszlop. Láthatja, hogy az előrejelzett y értékek mindkét modellben megegyeznek. Ez a probléma azért fordul elő, mert egy redundáns oszlop eltávolítása, amely több többszöröst jelent, nem csökkenti az eredményként kapott modell megfelelőségét. Az ilyen oszlopokat pontosan azért távolítják el, mert nem jelentenek hozzáadott értéket a legkisebb négyzetek módszerének optimális illesztéséhez. Ha az Excel 2003 I23:K35 celláiban és az Excel későbbi verzióiban is megvizsgálja a LOGEST-kimenetet, láthatja, hogy a kimeneti táblák utolsó három sora megegyezik. Ezenkívül az I31:J32 és a J24:K25 cella bejegyzései egybeesnek. Ez azt mutatja, hogy ugyanazok az eredmények jelennek meg, amikor a C oszlop szerepel a modellben, de redundánsnak bizonyult (az I24:K28 cellában lévő kimenet), mint amikor a C oszlopot a LOGEST futtatása előtt megszüntették (a kimenet az I31:J35 cellában). Ez megfelel a kollinearitás meglétének alapvető elvének.

Az A18:C21 cellákban a Microsoft az Excel 2003 és az Excel későbbi verzióiból származó adatokat használja annak szemléltetésére, hogy a NÖVEKEDÉS hogyan veszi figyelembe a LOGEST-kimenetet, és hogyan számítja ki a vonatkozó előrejelzett y-értékeket. Az A20:A21 cellában és a C20:C21 cellában található képletek vizsgálatával láthatja, hogy a LOGEST-együtthatók hogyan vannak kombinálva new_x adataival a B7:C8 cellában mindkét modell esetében (a B oszlopot, a C oszlopot prediktorként, csak a B oszlopot használjuk prediktorként).

A kollinearitás az Excel 2003-ban és az Excel későbbi verzióiban a LOGEST-ben van azonosítva, mert a LOGEST a LINEST-t hívja meg. A LINEST más megközelítést használ a regressziós együtthatók megoldásához. Ez a QR felbontási megközelítés. A LINEST-cikk a QR felbontási algoritmusának egy kis példán keresztüli bemutatóját tartalmazza.

Az Excel korábbi verzióiban megjelenő eredmények összegzése

A NÖVEKEDÉSI eredményeket az Excel 2003-nál korábbi verziói hátrányosan érintik a LOGEST pontatlan eredményei miatt, amelyek viszont a LINEST pontatlan eredményeiből erednek.

A LINEST kiszámítása olyan megközelítéssel történt, amely nem fordított figyelmet a kollinearitási problémákra. A kollinearitás megléte kerekítési hibákat, nem megfelelő standard regressziós együtthatókat és nem megfelelő szabadságfokokat okozott. Néha a lekerekítési problémák annyira súlyosak, hogy a LINEST a #NUM! értékekkel töltötte meg a kimeneti táblát. Ha a gyakorlatban a legtöbb esetben is biztos lehet abban, hogy nem voltak kollineáris (vagy majdnem kollineáris) prediktoroszlopok, akkor a LINEST általában elfogadható eredményt adna. Ezért a NÖVEKEDÉS felhasználói hasonlóképpen megnyugodhatnak, ha látják a kollineáris (vagy majdnem kollineáris) prediktoroszlopok hiányát.

Az Excel 2003-ban és az Excel későbbi verzióiban elérhető eredmények összegzése

A LINEST fejlesztései közé tartozik a regressziós együtthatók meghatározására szolgáló QR-felbontási módszerre való váltás. A QR felbontása a következő előnyökkel jár:

• Jobb numerikus stabilitás (általában kisebb kerekítési hibák)
• Kollinearitási problémák elemzése

Az Excel 2003-nál korábbi verzióival kapcsolatos, a cikkben bemutatott problémákat kijavítottuk az Excel 2003-hoz és az Excel későbbi verzióihoz. A LINEST ezen fejlesztései a LOGEST és a GROWTH továbbfejlesztését jelentik.

Összegzés

A NÖVEKEDÉS teljesítménye javult, mivel a LINEST jelentősen fejlődött az Excel 2003-ban és a későbbi verziókban. A LINEST fejlesztései a LOGEST-et is érintik, mivel a LOGEST-et a NÖVEKEDÉS hívja meg. Az Excel korábbi verzióinak felhasználóinak ellenőrizniük kell, hogy a prediktoroszlopok nem kollineárisak-e a GROWTH használata előtt.

A cikkben és a LINEST-cikkben bemutatott anyagok nagy része először az Excel 2003-nál korábbi verzióinak felhasználóit is megriaszthatja. Meg kell azonban jegyezni, hogy a kollinearitás csak kis százalékban okoz problémát. Az Excel korábbi verziói elfogadható NÖVEKEDÉSI eredményt adnak, ha nincs kollinearitás.

Szerencsére a LINEST fejlesztései hatással vannak az Analysis ToolPak lineáris regressziós eszközére (ez az eszköz a LINEST-ot hívja) és két másik kapcsolódó Excel-függvényre is: LOGEST és TREND.