Funkcje statystyczne programu Excel: GROWTH

Podsumowanie

W tym artykule opisano funkcję GROWTH w programie Excel, pokazano, jak jest używana funkcja, i porównuje wyniki funkcji dla programu Excel 2003 i nowszych wersji programu Excel z wynikami funkcji GROWTH we wcześniejszych wersjach programu Excel. FUNKCJA GROWTH jest oceniana przez wywołanie powiązanej funkcji LINEST. Podsumowano obszerne zmiany w funkcji LINEST dla programu Excel 2003 i nowszych wersji programu Excel oraz ich implikacje dla funkcji GROWTH.

Informacje o programie Microsoft Excel 2004 dla komputerów Macintosh

Funkcje statystyczne w programie Excel 2004 dla komputerów Mac zostały zaktualizowane przy użyciu tych samych algorytmów, które zostały użyte do zaktualizowania funkcji statystycznych w programie Excel 2003 i nowszych wersjach programu Excel. Wszelkie informacje zawarte w tym artykule opisujące działanie funkcji lub sposób modyfikacji funkcji dla programu Excel 2003 lub nowszych wersji programu Excel dotyczą również programu Excel 2004 dla komputerów Mac.

Więcej informacji

Funkcja GROWTH(znane_y, znane_x, nowe_x, stała) służy do przeprowadzania analizy regresji, w której dopasowywana jest krzywa wykładnicza. Wykorzystuje się kryterium najmniejszych kwadratów, a WZROST próbuje znaleźć najlepsze dopasowanie według tego kryterium. Known_y reprezentują dane dotyczące "zmiennej zależnej", a known_x dotyczą danych na przynajmniej jednej "zmiennej niezależnej". Plik POMOCY GROWTH omawia rzadkie przypadki, w których może zostać pominięty drugi lub trzeci argument.

Zakładając, że istnieją zmienne predykcyjne p, funkcja GROWTH zasadniczo wywołuje funkcję LOGEST. Funkcja LOGEST dopasowuje równanie do formy:

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

Wartości współczynników, b, m1, m2, ..., mp są określane, które dają najlepsze dopasowanie do danych y.

Jeśli ostatni argument "stała" ma wartość TRUE, chcesz, aby model regresji uwzględniał mnożenie współczynnika b w modelu regresji. Jeśli ustawiono wartość FALSE, b jest zasadniczo wykluczona poprzez ustawienie jej na 1. Ostatni argument jest opcjonalny; jeśli argument zostanie pominięty, jest interpretowany jako PRAWDA.

W celu ułatwienia eksponowania w pozostałej części tego artykułu przyjęto założenie, że dane są rozmieszczone w kolumnach, tak aby known_y to kolumna danych y, a known_x to co najmniej jedna kolumna danych x. Wymiary (długości) każdej z tych kolumn muszą być równe. zakłada się również, że New_x będą rozmieszczone w kolumnach i musi istnieć taka sama liczba kolumn dla new_x co known_x. Wszystkie poniższe obserwacje są równie prawdziwe, jeśli dane nie są rozmieszczone w kolumnach, ale łatwiej jest omówić pojedynczy (najczęściej używany) przypadek.

Po obliczeniu najlepszego modelu regresji dopasowania (przez wywołanie funkcji LOGEST programu Excel) funkcja GROWTH zwraca przewidywane wartości skojarzone z new_x.

W tym artykule użyto przykładów, aby pokazać, jak funkcja GROWTH odnosi się do funkcji LOGEST i wskazać problemy z LOGEST w wersjach programu Excel wcześniejszych niż Excel 2003, które przekładają się na problemy z funkcją GROWTH. FUNKCJA GROWTH efektywnie wywołuje funkcję LOGEST, wykonuje ją oraz używa współczynników regresji w wynikach LOGEST do obliczania przewidywanych wartości y, które są skojarzone z każdym wierszem new_x. Następnie przedstawia tę kolumnę przewidywanych wartości y. W związku z tym należy wiedzieć o problemach podczas wykonywania funkcji LOGEST. Wywołana funkcja LOGEST w efekcie wywołuje funkcję LINEST. Mimo że kod dla funkcji GROWTH i LOGEST nie został przepisany dla programu Excel 2003 i nowszych wersji programu Excel, wprowadzono obszerne zmiany (i ulepszenia) w kodzie LINEST.

Jako uzupełnienie do tego artykułu, zdecydowanie zalecany jest poniższy artykuł o LINEST. Zawiera kilka przykładów i dokumentuje problemy z LINEST w wersjach programu Excel starszych niż Excel 2003.

Aby uzyskać więcej informacji na temat linest, wybierz następujący numer artykułu, aby wyświetlić artykuł w bazie wiedzy Microsoft Knowledge Base:

828533 opis funkcji LINEST w programie Excel 2003 i w programie Excel 2004 dla komputerów Mac

Zalecany jest również plik Pomocy LINEST, zgodnie z zmianą dla programu Excel 2003.

W poniższym artykule na temat usługi LOGEST wyjaśniono, jak usługa LOGEST współdziała z lineST. Te szczegóły zostaną pominięte tutaj.

Aby uzyskać więcej informacji, zobacz Funkcje statystyczne programu Excel: LOGEST.

Ponieważ w tym artykule skupiono się na problemach liczbowych w wersjach programu Excel, które są starsze niż program Excel 2003, ten artykuł nie zawiera wielu praktycznych przykładów użycia funkcji GROWTH. Plik Pomocy w pliku GROWTH zawiera przydatne przykłady.

Składnia

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

Argumenty, known_y, known_x i new_x muszą być tablicami lub zakresami komórek, które mają powiązane wymiary. Jeśli known_y jest jedną kolumną według m wierszy, known_x to kolumny c według wierszy m, w których wartość c jest większa lub równa jednej. C to liczba zmiennych predyktora; m to liczba punktów danych. New_x musi mieć c kolumny i r wierszy, gdzie r jest większe lub równe jeden. (Podobne relacje w wymiarach muszą być zachowane, jeśli dane są rozmieszczone w wierszach zamiast kolumn). Stała jest argumentem logicznym, który musi być ustawiony na wartość logiczną TRUE lub FALSE (lub 0 lub 1, które Excel interpretuje odpowiednio jako FALSE lub TRUE). Trzy ostatnie argumenty funkcji GROWTH są wszystkie opcjonalne; zobacz plik Pomocy dotyczący funkcji GROWTH, aby uzyskać opcje pomijania drugiego argumentu, trzeciego argumentu lub obu tych elementów; pominięcie czwartego argumentu jest interpretowane jako 'PRAWDA'.

Najczęstsze użycie funkcji GROWTH obejmuje dwa zakresy komórek zawierających dane, takie jak GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Ponieważ zwykle istnieje więcej niż jedna zmienna predyktorowa, drugi argument w tym przykładzie zawiera wiele kolumn. W tym przykładzie istnieje 100 podmiotów, jedna wartość zmiennej zależnej (known_y) dla każdego tematu i pięć wartości zmiennych zależnych (known_x) dla każdego tematu. Istnieje osiem bardziej hipotetycznych tematów, w których chcesz użyć funkcji GROWTH do obliczenia przewidywanych wartości y.

Przykład użycia

Przykład arkusza programu Excel przedstawia następujące kluczowe pojęcia:

• Jak FUNKCJA GROWTH współdziała z FUNKCJĄ LOGEST
• Problemy występujące z funkcją GROWTH (lub LOGEST i LINEST) z powodu współliniowych known_x w wersjach programu Excel, które są starsze niż Excel 2003

Uwaga / Notatka

Obszerna dyskusja na temat drugiego elementu punktowanego w kontekście LINEST znajduje się w artykule dotyczącym LINEST.

Aby zilustrować funkcję GROWTH, utwórz pusty arkusz programu Excel, skopiuj poniższą tabelę, wybierz komórkę A1 w pustym arkuszu programu Excel, a następnie wklej wpisy, aby poniższa tabela wypełniała komórki A1:K35 w arkuszu.

A	B	C	D	E	F	G	H	Ja	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
nowe x:	9	11
	12	14
WZROST przy użyciu kolumn B,C:				Wartości programu Excel 2002 i starszych wersji programu Excel:
			Wartości programu Excel 2003 i nowszych wersji programu Excel:
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!				472,432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!				3400.16400895377
WZROST przy użyciu wyłącznie kolumny B
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE)				472,432432563203				472,432432563203
=GROWTH(A2:A6,B2:B6,B7:B8,PRAWDA)				3400.16400895377				3400.16400895377
Dopasowane wartości w wynikach LOGEST w programie Excel 2003 i nowszych wersjach programu Excel
Używanie kolumn B, C		Korzystanie z kolumny B
=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 przy użyciu kolumn B, C:				Wartości programu Excel 2002 i starszych wersji programu Excel:				Wartości programu Excel 2003 i nowszych wersji programu Excel:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		1	1,9307233720034	1.26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		0	0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		0.986842105263158	0.209426954145848	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		225	3	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		9.86842105263158	0.131578947368421	#N/A
LOGEST tylko przy użyciu kolumny B
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			1,9307233720034	1,26724101129183			1,9307233720034	1.26724101129183
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.0438596491228071	0.206652964726136			0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.986842105263158	0.209426954145848			0.986842105263158	0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			224.999999999999	3			225	3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			9.86842105263158	0.131578947368421			9.86842105263158	0.131578947368421

Uwaga / Notatka

Po wklejeniu tej tabeli w nowym arkuszu programu Excel wybierz przycisk Wklej opcje , a następnie wybierz pozycję Dopasuj formatowanie docelowe. Mając nadal zaznaczony wklejony zakres, użyj jednej z poniższych procedur, odpowiednich dla używanej wersji programu Excel.

• W programie Microsoft Office Excel 2007 wybierz kartę Strona główna, wybierz pozycję Formatuj w grupie Komórki, a następnie wybierz pozycję Autodopasuj szerokość kolumny.
• W programie Excel 2003 wskaż kolumnę w menu Format, a następnie wybierz pozycję Autodopasuj zaznaczenie.

Dane dotyczące WZROSTU znajdują się w komórkach A1:C8. (Wpisy w komórkach D2:D6 nie są częścią danych, ale są używane na poniższej ilustracji). Wyniki funkcji GROWTH dla dwóch różnych modeli dla wcześniejszych wersji programu Excel i nowszych wersji programu Excel są prezentowane odpowiednio w komórkach E10:E16 i I10:116. Wyniki w komórkach A10:A16 będą odpowiadać używanej wersji programu Excel. Na razie skoncentruj się na wynikach programu Excel 2003 i nowszych wersji programu Excel, podczas gdy badać będziesz, w jaki sposób funkcja GROWTH wywołuje funkcję LOGEST i jak funkcja GROWTH wykorzystuje wyniki LOGEST.

GROWTH i LOGEST można postrzegać jako wzajemne oddziaływanie w następujących krokach:

1. Wywołujesz funkcję GROWTH(znane_y, znane_x, nowe_x, stała)
2. FUNKCJA GROWTH wywołuje funkcję LOGEST(known_y's, known_x's, constant, TRUE)
3. Uzyskano współczynniki regresji z tego wywołania funkcji LOGEST. Te współczynniki są wyświetlane w pierwszym wierszu tabeli wyjściowej LOGEST.
4. Dla każdego wiersza new_x przewidywana wartość y jest obliczana na podstawie tych współczynników LOGEST i wartości new_x w tym wierszu.
5. Wartość obliczeniowa w kroku 4 jest zwracana w odpowiedniej komórce dla danych wyjściowych GROWTH odpowiadających wierszowi tego new_x.

Jeśli funkcja GROWTH ma zwrócić odpowiednie wyniki, funkcja LOGEST musi wygenerować odpowiednie wyniki w kroku 3. Ponieważ ocena logEST w kroku 3 wymaga wywołania LINEST, ważne jest, aby LINEST zachowywał się dobrze. Problemy z funkcją LINEST w wersjach programu Excel, które są starsze niż Excel 2003, pochodzą z kolumn predyktorów współliniowych. (Istnieją inne problemy z LINEST i LOGEST we wcześniejszych wersjach programu Excel, które występują, gdy ostatni argument wartości GROWTH ma wartość FALSE. Jednak te problemy nie mają wpływu na wyniki WZROSTU i nie są tu omawiane.

Kolumny predyktora (known_x) są współliniowe, jeśli co najmniej jedna kolumna, c, może być wyrażona jako suma wielokrotności innych kolumn, takich jak c1, c2 i inne. Kolumna c jest często wywoływana nadmiarowo, ponieważ informacje, które zawiera, można utworzyć z kolumn c1, c2 i innych kolumn. Podstawową zasadą istnienia kolinearności jest to, że wyniki powinny być niezależne od tego, czy nadmiarowa kolumna jest uwzględniona w oryginalnych danych, czy usunięta z oryginalnych danych. Ponieważ LINEST w wersjach programu Excel wcześniejszych niż Excel 2003 nie uwzględniał kolinearności, ta zasada była łatwo naruszana. Kolumny predykcyjne są prawie liniowo zbieżne, jeśli co najmniej jedna kolumna, c, może być wyrażona jako prawie równa sumie wielokrotności innych kolumn, takich jak c1, c2 i inne. W tym przypadku "prawie równe" oznacza niewielką sumę odchyleń kwadratowych wpisów w c z odpowiednich wpisów w ważonej sumie c1, c2 i innych kolumn. Wartość "Bardzo mała" może być mniejsza niż 10^(-12), na przykład.

Pierwszy model, w wierszach od 10 do 12, używa kolumn B i C jako predyktorów i żąda programu Excel do modelowania stałej (ostatni argument ustawiony na TRUE). Następnie program Excel skutecznie wstawia dodatkową kolumnę predyktora, która wygląda podobnie do komórek D2:D6. Łatwo zauważyć, że wpisy w kolumnie C w wierszach od 2 do 6 są dokładnie równe sumie odpowiadających wpisów w kolumnach B i D. W związku z tym występuje kolizja, ponieważ kolumna C jest sumą wielokrotności następujących elementów:

• Kolumna B
• Dodatkowa kolumna z liczbami 1 w Excelu, która jest wstawiana, ponieważ trzeci argument LOGEST został pominięty lub był równy TRUE (przypadek "normalny")

Powoduje to takie problemy liczbowe, że wersje programu Excel, które są starsze niż program Excel 2003, nie mogą obliczyć wyników. W związku z tym tabela danych wyjściowych GROWTH jest wypełniona #NUM!.

Drugi model, w wierszach od 14 do 16, jest taki, że dowolna wersja programu Excel może pomyślnie obsłużyć. Nie ma kolinearności, a użytkownik ponownie prosi program Excel o modelowanie stałej. Ten model jest uwzględniony w tym miejscu z następujących powodów:

• Po pierwsze, jest to najbardziej typowe dla przypadków praktycznych: że nie ma żadnej kolinearności. Te przypadki są wystarczająco obsługiwane we wszystkich wersjach programu Excel. Powinno być uspokajające wiedzieć, że problemy obliczeniowe prawdopodobnie nie występują w najbardziej typowych przypadkach praktycznych, jeśli masz wcześniejszą wersję programu Excel.
• Po drugie, ten przykład służy do porównywania zachowania programu Excel 2003 i nowszych wersji programu Excel w dwóch modelach. Większość głównych pakietów statystycznych analizuje współliniowość, usuwa kolumnę, która jest wielokrotnością innych, z modelu i ostrzega użytkownika komunikatem, takim jak "kolumna C jest liniowo zależna od pozostałych kolumn predykcyjnych i została usunięta z analizy".

W programie Excel 2003 i nowszych wersjach programu Excel taki komunikat nie jest przekazywany w alercie ani w ciągu tekstowym, ale w tabeli wyjściowej LOGEST. WZROST nie ma mechanizmu dostarczania takiego komunikatu użytkownikowi. W tabeli wyjściowej LOGEST współczynnik regresji, który jest jeden, i którego błąd standardowy wynosi zero, odpowiada współczynnikowi kolumny, która została usunięta z modelu. Tabele wyjściowe LOGEST są uwzględniane w wierszach od 23 do 35 odpowiadających danych wyjściowych GROWTH w wierszach od 10 do 16. Wpisy w komórkach I24:I25 przedstawiają usuniętą zbędną kolumnę predyktora. W tym przypadku funkcja LOGEST zdecydowała się usunąć kolumnę C (współczynniki w komórkach I24, J24, K24 odpowiadają kolumnom C, B i stałej kolumnie programu Excel). Gdy występuje współliniowość, można usunąć dowolną z zaangażowanych kolumn, a wybór jest arbitralny.

W drugim modelu w wierszach od 30 do 35 nie ma kolinearności i nie usunięto żadnej kolumny z modelu. Widać, że przewidywane wartości y są takie same w obu modelach. Ten problem występuje, ponieważ usunięcie nadmiarowej kolumny, która jest sumą wielokrotności innych kolumn, nie zmniejsza dopasowania modelu wynikowego. Takie kolumny są usuwane dokładnie dlatego, że nie wnoszą żadnej wartości dodanej w próbie znalezienia najlepszego dopasowania metodą najmniejszych kwadratów. Ponadto jeśli zbadasz dane wyjściowe LOGEST w komórkach I23:K35 w programie Excel 2003 i nowszych wersjach programu Excel, zauważysz, że ostatnie trzy wiersze tabel wyjściowych są takie same. Ponadto wpisy w komórkach I31:J32 i komórkach J24:K25 zbiegają się. Pokazuje, że te same wyniki są uzyskiwane, gdy kolumna C jest zawarta w modelu, ale okaże się, że jest nadmiarowa (dane wyjściowe w komórkach I24:K28), jak w przypadku wyeliminowania kolumny C przed uruchomieniem logEST (dane wyjściowe w komórkach I31:J35). Spełnia to podstawową zasadę istnienia współliniowości.

W komórkach A18:C21 firma Microsoft używa danych z programu Excel 2003 i z nowszych wersji programu Excel, aby zilustrować, w jaki sposób funkcja GROWTH wykorzystuje dane wyjściowe funkcji LOGEST, aby obliczyć odpowiednie przewidywane wartości y. Sprawdzając formuły w komórkach A20:A21 i C20:C21, można zobaczyć, jak współczynniki LOGEST są łączone z danymi new_x w komórkach B7:C8 dla każdego z dwóch modeli (przy użyciu kolumn B i C jako predyktorów; używając tylko kolumny B jako predyktora).

Kolinearność jest rozpoznawana w funkcji LOGEST w programie Excel 2003 oraz w późniejszych wersjach Excela, ponieważ LOGEST wywołuje funkcję LINEST. LINEST używa innego podejścia do rozwiązywania współczynników regresji. To podejście jest dekompozycją QR. Artykuł LINEST zawiera przewodnik po algorytmie dekompozycji QR dla małego przykładu.

Podsumowanie wyników we wcześniejszych wersjach programu Excel

Wyniki WZROSTU są negatywnie wpływane w wersjach programu Excel, które są starsze niż Excel 2003, z powodu niedokładnych wyników w funkcji LOGEST, które wynikają z niedokładnych wyników w funkcji LINEST.

LINEST został obliczony przy użyciu podejścia, które nie zwracało uwagi na problemy ze współliniowością. Istnienie współliniowości spowodowało błędy zaokrąglenia, niewłaściwe standardowe błędy współczynników regresji i niewłaściwe stopnie swobody. Czasami problemy zaokrąglania są na tyle poważne, że LINEST wypełnił swoją tabelę wyników wartościami #NUM!. Jeśli, jak w większości przypadków, możesz mieć pewność, że nie było kolumn predyktorów współliniowych (lub prawie współliniowych), funkcja LINEST ogólnie zapewnia akceptowalne wyniki. W związku z tym użytkownicy funkcji GROWTH mogą być podobnie uspokajani, jeśli widzą brak współliniowych (lub prawie współliniowych) kolumn predykcyjnych.

Podsumowanie wyników w programie Excel 2003 i nowszych wersjach programu Excel

Ulepszenia w funkcji LINEST obejmują przełączenie na metodę dekompozycji QR do określania współczynników regresji. Dekompozycja QR ma następujące zalety:

• Lepsza stabilność liczbowa (zazwyczaj mniejsze błędy zaokrąglania)
• Analiza problemów z kolinearnością

Wszystkie problemy z wersjami programu Excel, które są starsze niż program Excel 2003, które zostały zilustrowane w tym artykule, zostały poprawione dla programu Excel 2003 i nowszych wersji programu Excel. Te ulepszenia w LINEST przekładają się na ulepszenia w LOGEST i WZROST.

Wnioski

Wydajność funkcji WZROST została poprawiona, ponieważ funkcja LINEST została znacznie ulepszona dla programu Excel 2003 i nowszych wersji. Ulepszenia w LINEST wpływają również na LOGEST, ponieważ funkcja LOGEST jest wywoływana przez WZROST. Użytkownicy wcześniejszych wersji programu Excel powinni sprawdzić, czy kolumny predyktora nie są liniowo zależne przed użyciem funkcji WZROST.

Większość materiałów przedstawionych w tym artykule i w artykule LINEST może początkowo wydawać się alarmować użytkowników wersji programu Excel, które są wcześniejsze niż Excel 2003. Należy jednak zauważyć, że kolinearność jest problemem tylko w niewielkim procencie przypadków. Wcześniejsze wersje programu Excel dają akceptowalne wyniki funkcji GROWTH, gdy nie ma współliniowości.

Na szczęście ulepszenia w narzędziu LINEST mają również wpływ na narzędzie regresji liniowej narzędzia Analysis ToolPak (to narzędzie wywołuje LINEST) i dwie inne powiązane funkcje programu Excel: LOGEST i TREND.