Funkcje statystyczne programu Excel: GROWTH

Podsumowanie

W tym artykule opisano funkcję GROWTH w programie Microsoft Office Excel 2003 i nowszych wersjach programu Excel, przedstawiono sposób użycia funkcji oraz porównaliśmy wyniki funkcji dla programu Excel 2003 i nowszych wersji programu Excel z wynikami wzrostu we wcześniejszych wersjach programu Excel. Wartość GROWTH jest oceniana przez wywołanie powiązanej funkcji LINEST. Obszerne zmiany w usłudze LINEST dla programu Excel 2003 i dla nowszych wersji programu Excel są podsumowane i zanotowane są ich konsekwencje dla wzrostu.

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 w nowszych wersjach programu Excel. Wszelkie informacje w tym artykule opisujące działanie funkcji lub sposób modyfikowania 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 (known_y, known_x, new_x, stała) służy do przeprowadzania analizy regresji, w której jest dopasowana krzywa wykładnicza. Używane jest kryterium najmniejszych kwadratów, a growth stara się znaleźć najlepsze dopasowanie w ramach tego kryterium. Known_y reprezentują dane w "zmiennej zależnej", a known_x reprezentują dane w jednej lub kilku "niezależnych zmiennych". W pliku pomocy GROWTH omówiono rzadkie przypadki pominięcia drugiego lub trzeciego argumentu.

Zakładając, że istnieją zmienne predykcyjne p, funkcja GROWTH zasadniczo wywołuje logest. LOGEST pasuje do równania formularza:

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

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

Jeśli ostatni argument "constant" ma wartość TRUE, chcesz, aby model regresji uwzględnił współczynnik wielowymiarowy b w modelu regresji. Jeśli ustawiono wartość FALSE, wartość b jest wykluczana przez zasadniczo ustawienie jej na wartość 1. Ostatni argument jest opcjonalny; Jeśli argument zostanie pominięty, zostanie on zinterpretowany jako TRUE.

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

Po obliczeniu modelu regresji najlepszego dopasowania (przez zasadniczo 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 wzrost odnosi się do usługi LOGEST, oraz do wskazywania problemów z logest w wersjach programu Excel wcześniejszych niż Excel 2003, które przekładają się na problemy ze wzrostem. Funkcja GROWTH skutecznie wywołuje logest, wykonuje logest, używa współczynników regresji w danych wyjściowych LOGEST w obliczeniach przewidywanych wartości y skojarzonych z każdym wierszem new_x i przedstawia tę kolumnę przewidywanych wartości y. W związku z tym musisz wiedzieć o problemach podczas wykonywania usługi LOGEST. Gdy logest jest wywoływany, to z kolei skutecznie wywołuje LINEST. Chociaż kod dla aplikacji GROWTH i LOGEST nie został przepisany ponownie dla programu Excel 2003 i dla nowszych wersji programu Excel, wprowadzono rozległe zmiany (i ulepszenia) w kodzie LINEST.

Jako suplementy do tego artykułu, poniższy artykuł o LINEST jest bardzo zalecane. Zawiera on kilka przykładów i dokumentów problemów z usługą LINEST w wersjach programu Excel, które są starsze niż program Excel 2003.

Aby uzyskać więcej informacji na temat usługi LINEST, kliknij 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 programie Excel 2004 dla komputerów Mac

Zalecany jest również plik pomocy LINEST poprawiony dla programu Excel 2003.

W poniższym artykule o usłudze LOGEST wyjaśniono, jak usługa LOGEST współdziała z usługą LINEST. Te szczegóły zostały pominięte tutaj.

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

828528 funkcje statystyczne programu Excel: LOGEST

Ponieważ ten artykuł koncentruje się na problemach liczbowych w wersjach programu Excel wcześniejszych niż Excel 2003, ten artykuł nie zawiera wielu praktycznych przykładów użycia narzędzia GROWTH. Plik Pomocy w aplikacji GROWTH zawiera przydatne przykłady.

Składni

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 wierszy m, known_x to kolumny c według wierszy m, gdzie c jest większe lub równe jednej. C to liczba zmiennych predykcyjnych; m to liczba punktów danych. New_x muszą być kolumnami c według wierszy języka r, gdzie są większe lub równe jednej. (Podobne relacje w wymiarach muszą być przechowywane, jeśli dane są układane w wierszach, a nie w kolumnach). Stała jest argumentem logicznym, który musi być ustawiony na wartość PRAWDA lub FAŁSZ (lub 0 lub 1, który program Excel interpretuje odpowiednio jako FALSE lub TRUE). Ostatnie trzy argumenty growth są opcjonalne; zobacz plik pomocy GROWTH, aby zapoznać się z opcjami pominięcia drugiego argumentu, trzeciego argumentu lub obu tych opcji; 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 predykcyjna, drugi argument w tym przykładzie zawiera wiele kolumn. W tym przykładzie istnieje 100 przedmiotów, jedna zależna wartość zmiennej (known_y) dla każdego tematu i pięć zależnych wartości zmiennych (known_x) dla każdego tematu. Istnieje osiem dodatkowych hipotetycznych tematów, w których chcesz użyć funkcji GROWTH do obliczania przewidywanych wartości y.

Przykład użycia

Przedstawiono przykład arkusza programu Excel, aby zilustrować następujące kluczowe pojęcia:

• Jak usługa GROWTH współdziała z usługą LOGEST
• Problemy występujące w programie GROWTH (lub LOGEST i LINEST) z powodu kollinearnych known_x w wersjach programu Excel wcześniejszych niż Excel 2003

Aby zilustrować funkcję GROWTH, utwórz pusty arkusz programu Excel, skopiuj następującą 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.

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 wzrostu dla dwóch różnych modeli zarówno dla wcześniejszych wersji programu Excel, jak i dla 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, gdy zbadasz, jak usługa GROWTH wywołuje logest i jak funkcja GROWTH używa wyników LOGEST.

Funkcje GROWTH i LOGEST można wyświetlić jako wchodzące w interakcje w następujących krokach:

1. Nazywasz elementy GROWTH (known_y, known_x, new_x, constant)
2. GROWTH wywołuje LOGEST (known_y, known_x, stała, PRAWDA)
3. Uzyskuje się współczynniki regresji z tego wywołania logest. Te współczynniki są wyświetlane w pierwszym wierszu tabeli danych wyjściowych usługi 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 zwracać odpowiednie wyniki, usługa LOGEST musi wygenerować odpowiednie wyniki w kroku 3. Ponieważ ocena usługi LOGEST w kroku 3 wymaga wywołania usługi LINEST, ważne jest, aby usługa LINEST zachowywała się prawidłowo. Problemy z programem LINEST w wersjach programu Excel, które są starsze niż program Excel 2003, pochodzą z kolumn predykcyjnego współliniowego. (Istnieją inne problemy z usługami LINEST i LOGEST we wcześniejszych wersjach programu Excel, które występują, gdy ostatni argument na wartość GROWTH ma wartość FALSE. Jednak te problemy nie wpływają na wyniki wzrostu i nie są tutaj omawiane).

Kolumny predykcyjnego (known_x) są współliniowe, jeśli co najmniej jedną kolumnę, c, można wyrazić jako sumę wielokrotności innych, c1, c2 i innych kolumn. Kolumna c jest często nazywana nadmiarowa, ponieważ zawarte w niej informacje mogą być konstruowane na podstawie kolumn c1, c2 i innych kolumn. Podstawową zasadą w istnieniu współliniowości jest to, że wyniki nie powinny mieć wpływu na to, czy nadmiarowa kolumna jest uwzględniona w oryginalnych danych, czy usunięta z oryginalnych danych. Ponieważ usługa LINEST w wersjach programu Excel wcześniejszych niż Excel 2003 nie szukała koliarności, ta zasada została łatwo naruszona. Kolumny predykcyjne są prawie współliniowe, jeśli co najmniej jedna kolumna, c, może być wyrażona jako prawie równa sumie wielokrotności innych, c1, c2 i innych kolumn. W tym przypadku "prawie równe" oznacza niewielką sumę odchyleń kwadratowych wpisów w c z odpowiadających wpisów w sumie ważonej c1, c2 i innych kolumn. Na przykład wartość "Bardzo mała" może być mniejsza niż 10^(-12).

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 WARTOŚĆ TRUE). Następnie program Excel skutecznie wstawia dodatkową kolumnę predykcyjną, 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 odpowiednich wpisów w kolumnach B i D. W związku z tym istnieje współliniowość, ponieważ kolumna C jest sumą wielokrotności następujących elementów:

• Kolumna B
• Dodatkowa kolumna 1s programu Excel, która jest wstawiona, ponieważ trzeci argument logest został pominięty lub TRUE (przypadek "normalny")

Powoduje to takie problemy liczbowe, że wersje programu Excel, które są starsze niż program Excel 2003, nie mogą obliczać 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, który może pomyślnie obsłużyć dowolna wersja programu Excel. Nie ma koliarności, a użytkownik ponownie żąda od programu Excel modelowania stałej. Ten model jest uwzględniony tutaj z następujących powodów:

• Po pierwsze, jest to najbardziej typowe w praktycznych przypadkach: że nie ma współliniowości. Te przypadki są obsługiwane wystarczająco we wszystkich wersjach programu Excel. Powinno być pocieszające, aby wiedzieć, że problemy liczbowe nie mogą wystąpić w najczęstszym praktycznym przypadku, 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ę będącą sumą wielokrotności innych z modelu i ostrzega użytkownika komunikatem takim jak "kolumna C jest liniowo zależna od innych kolumn predykcyjna i została usunięta z analizy".

W programie Excel 2003 i nowszych wersjach programu Excel taki komunikat jest przekazywany nie w alercie ani w ciągu tekstowym, ale w tabeli danych wyjściowych LOGEST. Usługa GROWTH nie ma mechanizmu dostarczania takiego komunikatu użytkownikowi. W tabeli danych wyjściowych LOGEST współczynnik regresji, który jest jednym z nich i którego standardowy błąd 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 danym wyjściowym GROWTH w wierszach od 10 do 16. Wpisy w komórkach I24:I25 pokazują wyeliminowaną nadmiarową kolumnę predykcyjną. W takim przypadku usługa LOGEST zdecydowała się usunąć kolumnę C (współczynniki w komórkach I24, J24, K24 odpowiadają kolumnom C, B i kolumnie stałej programu Excel). W przypadku obecności koliarności można usunąć dowolną kolumnę, a wybór jest dowolny.

W drugim modelu w wierszach od 30 do 35 nie ma koliarności i nie usunięto kolumny. 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, nie zmniejsza dobroci dopasowania wynikowego modelu. Takie kolumny są usuwane właśnie dlatego, że nie reprezentują żadnej wartości dodanej podczas próby znalezienia najlepszego dopasowania najmniejszych kwadratów. Ponadto jeśli przeanalizujesz dane wyjściowe LOGEST w komórkach I23:K35 w programie Excel 2003 i nowszych wersjach programu Excel, zauważysz, że trzy ostatnie wiersze tabel wyjściowych są takie same. Ponadto wpisy w komórkach I31:J32 i komórkach J24:K25 pokrywają się. Pokazuje to, że te same wyniki są uzyskiwane, gdy kolumna C jest uwzględniona w modelu, ale okaże się nadmiarowa (dane wyjściowe w komórkach I24:K28), jak w przypadku wyeliminowania kolumny C przed uruchomieniem protokołu LOGEST (dane wyjściowe w komórkach I31:J35). Spełnia to podstawową zasadę istnienia koliarności.

W komórkach A18:C21 firma Microsoft używa danych z programu Excel 2003 i z nowszych wersji programu Excel, aby zilustrować, jak wzrost pobiera dane wyjściowe LOGEST i oblicza odpowiednie przewidywane wartości y. Badając formuły w komórkach A20:A21 i komórkach 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 (używając kolumn B, C jako predyktorów; używając tylko kolumny B jako predyktora).

Koliarność jest identyfikowana w programie LOGEST w programie Excel 2003 i w nowszych wersjach programu Excel, ponieważ logest wywołuje linest. Usługa LINEST używa innego podejścia do rozwiązywania współczynników regresji. Takie podejście to dekompozycja QR. Artykuł LINEST zawiera przewodnik po algorytmie dekompozycji QR dla małego przykładu.

Podsumowanie wyników we wcześniejszych wersjach programu Excel

Niekorzystny wpływ na wyniki WZROSTu mają wersje programu Excel, które są starsze niż program Excel 2003 z powodu niedokładnych wyników w usłudze LOGEST, które z kolei wynikają z niedokładnych wyników w usłudze LINEST.

Funkcja LINEST została obliczona przy użyciu podejścia, które nie zwracało uwagi na problemy z koliarnością. Istnienie koliarności powodowało zaokrąglenie błędów, nieodpowiednie standardowe błędy współczynników regresji i nieodpowiednie stopnie swobody. Czasami zaokrąglanie problemów jest na tyle poważne, że usługa LINEST wypełniła tabelę danych wyjściowych #NUM!. Jeśli, tak jak w większości przypadków w praktyce, możesz mieć pewność, że nie było kolumn predykcyjnych współliniowych (lub prawie kollinearnych), usługa LINEST ogólnie zapewni akceptowalne wyniki. W związku z tym użytkownicy growth mogą być podobnie uspokojeni, jeśli widzą brak kolumn predykcyjnych kollinearnych (lub prawie kollinearnych).

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

Ulepszenia w usłudze LINEST obejmują przejście na metodę dekompozycji QR określającą współczynniki regresji. Dekompozycja QR ma następujące zalety:

• Lepsza stabilność liczbowa (zazwyczaj mniejsze błędy zaokrąglania)
• Analiza problemów z koliarnoś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 rozwiązane dla programu Excel 2003 i nowszych wersji programu Excel. Te ulepszenia w usłudze LINEST przekładają się na ulepszenia w logest i growth.

Wnioski

Wydajność aplikacji GROWTH została ulepszona, ponieważ usługa LINEST została znacznie ulepszona dla programu Excel 2003 i nowszych wersji programu Excel. Ulepszenia w usłudze LINEST mają również wpływ na logest, ponieważ logest jest wywoływany przez growth. Użytkownicy wcześniejszych wersji programu Excel powinni sprawdzić, czy kolumny predykcyjne nie są współliniowe przed użyciem funkcji GROWTH.

Większość materiałów przedstawionych w tym artykule i w artykule LINEST może początkowo wydawać się alarmem dla użytkowników wersji programu Excel, które są starsze niż Excel 2003. Należy jednak zauważyć, że koliarność jest problemem tylko w niewielkiej części przypadków. Wcześniejsze wersje programu Excel dają akceptowalne wyniki GROWTH, gdy nie ma koliarności.

Na szczęście ulepszenia w usłudze LINEST wpływają również na narzędzie regresji liniowej analysis ToolPak (to narzędzie wywołuje linest) i dwie inne powiązane funkcje programu Excel: LOGEST i TREND.