Obliczanie współczynnika determinacji w programie Microsoft Excel

Jednym ze wskaźników opisujących jakość skonstruowanego modelu w statystykach jest współczynnik determinacji (R ^ 2), zwany również wartością wiarygodności przybliżenia. Za jego pomocą można określić poziom dokładności prognozy. Zobaczmy, jak obliczyć ten wskaźnik za pomocą różnych narzędzi Excel.

Obliczanie współczynnika determinacji

W zależności od poziomu współczynnika determinacji, wspólne dzielenie modeli na trzy grupy:

• 0,8 - 1 - model dobrej jakości;
• 0,5 - 0,8 - model jakości akceptowalnej;
• 0 - 0,5 - model złej jakości.

W tym drugim przypadku jakość modelu wskazuje, że nie można go wykorzystać do prognozowania.

Wybór sposobu obliczania określonej wartości w programie Excel zależy od tego, czy regresja jest liniowa, czy nie. W pierwszym przypadku możesz użyć funkcji KVPIRSON , a w drugiej będziesz musiał użyć specjalnego narzędzia z pakietu analitycznego.

Metoda 1: obliczanie współczynnika determinacji dla funkcji liniowej

Przede wszystkim dowiedzmy się, jak znaleźć współczynnik determinacji dla funkcji liniowej. W tym przypadku wskaźnik ten będzie równy kwadratowi współczynnika korelacji. Obliczymy to za pomocą wbudowanej funkcji Excel na przykładzie konkretnej tabeli, co pokazano poniżej.

1. Wybierz komórkę, w której współczynnik determinacji zostanie wyprowadzony po jej obliczeniu, i kliknij ikonę "Wstaw funkcję" .
2. Uruchomi się Kreator funkcji . Przechodzimy do jego kategorii "Statystyczne" i oznaczamy "KVPIRSON" . Następnie kliknij przycisk "OK" .
3. Otworzy się okno argumentów funkcji KVPIRSON . Ten operator z grupy statystycznej służy do obliczenia kwadratu współczynnika korelacji funkcji Pearsona, czyli funkcji liniowej. I jak pamiętamy, przy funkcji liniowej współczynnik determinacji jest dokładnie równy kwadratowi współczynnika korelacji.

   Składnia tego stwierdzenia jest następująca:

   =КВПИРСОН(известные_значения_y;известные_значения_x)

   Tak więc funkcja ma dwa operatory, z których jeden jest listą wartości funkcji, a drugi z argumentów. Operatory mogą być reprezentowane bezpośrednio w postaci wartości wymienionych za pomocą średnika ( ; ) oraz jako odniesienia do zakresów, w których się znajdują. To ta ostatnia opcja, którą wykorzystamy w tym przykładzie.

   Ustaw kursor w polu "Known y" . Zaciskamy lewy przycisk myszy i wybieramy zawartość kolumny "Y" tabeli. Jak widać, adres określonej tablicy danych jest natychmiast wyświetlany w oknie.

   Podobnie wypełniamy pole "Znane wartości x" . Ustawiamy kursor w tym polu, ale tym razem wybieramy wartości kolumny "X" .

   Po wyświetleniu wszystkich danych w oknie argumentu KVPIRSON , kliknij przycisk "OK" znajdujący się na samym dole.

4. Jak widać, program oblicza współczynnik determinacji i wyprowadza wynik do komórki, która została przydzielona przed wywołaniem kreatora funkcji . W naszym przykładzie wartość obliczonego wskaźnika okazała się 1. Oznacza to, że przedstawiony model jest absolutnie niezawodny, to znaczy eliminuje błąd.

Lekcja: Kreator funkcji w programie Microsoft Excel

Metoda 2: obliczanie współczynnika determinacji w funkcjach nieliniowych

Ale powyższy wariant obliczania pożądanej wartości można zastosować tylko do funkcji liniowych. Co możemy zrobić, aby obliczyć go w funkcji nieliniowej? W Excelu jest też taka możliwość. Można to zrobić za pomocą narzędzia "Regresja" , które jest integralną częścią pakietu "Analiza danych" .

1. Ale przed użyciem określonego narzędzia należy aktywować sam pakiet "Analiza" , który domyślnie jest wyłączony w programie Excel. Przechodzimy do zakładki "Plik" , a następnie przechodzimy do "Opcji" .
2. W oknie, które się otworzy, przechodzimy do sekcji "Dodatki" , przechodząc przez lewe pionowe menu. Pole "Zarządzanie" znajduje się w dolnej części prawej części okna. Z listy dostępnych podsekcji wybierz nazwę "Dodatki Excel ..." , a następnie kliknij przycisk "Przejdź ..." po prawej stronie pola.
3. Otworzy się okno dodatku. W centralnej części znajduje się lista dostępnych dodatków. Zaznacz pole obok pozycji "Pakiet analizy" . Następnie kliknij przycisk "OK" po prawej stronie interfejsu okna.
4. Pakiet narzędzi "Analiza danych" w bieżącym wystąpieniu programu Excel zostanie aktywowany. Dostęp do niego znajduje się na wstążce w zakładce "Dane" . Przejście do określonej karty i kliknięcie przycisku "Analiza danych" w grupie ustawień "Analiza" .
5. Okno "Analiza danych" aktywowane jest listą narzędzi do przetwarzania informacji o profilu. Z tej listy wybieramy pozycję "Regresja" i klikamy przycisk "OK" .
6. Następnie otworzy się okno narzędzia Regresja . Pierwszy blok ustawień to "Dane wejściowe" . Tutaj, w dwóch polach, musisz podać adresy zakresów, w których znajdują się wartości argumentu i funkcji. Umieszczamy kursor w polu "Interwał wejściowy Y" i zaznaczamy zawartość kolumny "Y" na arkuszu. Po wyświetleniu adresu tablicy w oknie "Regresja" , umieść kursor w polu "Interwał wprowadzania Y" i zaznacz komórki w kolumnie "X" w ten sam sposób.

   O parametrach "Label" i "Constant-zero" nie ustawiamy pól wyboru. Pole wyboru można ustawić w pobliżu parametru "Poziom niezawodności" , aw polu obok można określić żądaną wartość odpowiedniego wskaźnika (domyślnie 95%).

   W grupie "Parametry wyjściowe" należy określić, w którym obszarze będzie wyświetlany wynik obliczeń. Istnieją trzy opcje:

   • Obszar na aktualnym arkuszu;
   • Kolejny arkusz;
   • Kolejna książka (nowy plik).

   Zatrzymamy się na pierwszej opcji, aby oryginalne dane i wyniki zostały umieszczone na jednym arkuszu. Przełącz przełącznik wokół parametru "Interwał wyjściowy" . W polu znajdującym się naprzeciw tego elementu ustawiamy kursor. Klikamy lewym przyciskiem myszy na pusty element na arkuszu, który ma stać się lewą górną komórką tabeli wyników wyników obliczeń. Adres tego elementu powinien być podświetlony w polu "Regresja" .

   Grupy parametrów "Remains" i "Normal Probability" są ignorowane, ponieważ nie są ważne dla rozwiązania zadania. Następnie kliknij przycisk "OK" , który znajduje się w prawym górnym rogu okna "Regresja" .

7. Program oblicza na podstawie wcześniej wprowadzonych danych i wyprowadza wynik do podanego zakresu. Jak widać, to narzędzie wyświetla dużą liczbę wyników dla różnych parametrów na arkuszu. Ale w kontekście obecnej lekcji interesuje nas wskaźnik "R-kwadrat" . W tym przypadku jest to 0,947664, który charakteryzuje wybrany model jako model dobrej jakości.

Metoda 3: Współczynnik wyznaczania dla linii trendu

Oprócz powyższych opcji współczynnik determinacji może być wyświetlany bezpośrednio dla linii trendu na wykresie, zbudowanej na arkuszu Excel. Zobaczmy, jak można to zrobić na konkretnym przykładzie.

1. Mamy wykres oparty na tabeli argumentów i wartościach funkcji użytej w poprzednim przykładzie. Zbudujmy dla niego linię trendów. Kliknij dowolne miejsce w obszarze budowy, na którym znajduje się wykres za pomocą lewego przycisku myszy. W takim przypadku na taśmie pojawia się dodatkowy zestaw zakładek - "Praca z diagramami" . Przejdź do zakładki "Układ" . Klikamy przycisk "Trend Line" , który znajduje się w skrzynce narzędziowej "Analiza" . Pojawi się menu pokazujące typ linii trendu. Zatrzymujemy wybór na typ odpowiadający konkretnemu zadaniu. Wybierzmy opcję "Exponential approximation" dla naszego przykładu.
2. Excel buduje linię trendu bezpośrednio na płaszczyźnie kreślarskiej w postaci dodatkowej czarnej krzywej.
3. Teraz naszym zadaniem jest pokazanie rzeczywistego współczynnika determinacji. Kliknij prawym przyciskiem myszy linię trendu. Menu kontekstowe jest aktywne. Zatrzymujemy zaznaczenie w pozycji "Format linii trendu ..." .

   Aby wykonać przejście do okna formatu linii trendu, możesz wykonać alternatywną akcję. Wybierz linię trendu, klikając na nią lewym przyciskiem myszy. Przechodzimy do zakładki "Układ" . Klikamy przycisk "Linia trendu" w bloku "Analiza" . Na otwartej liście kliknij najnowszy element listy działań - "Dodatkowe parametry linii trendu ..." .

4. Po wykonaniu jednej z dwóch powyższych czynności, uruchamiane jest okno formatu, w którym można wprowadzić dodatkowe ustawienia. W szczególności, aby wykonać nasze zadanie, musimy zaznaczyć pole obok "Umieść wartość przybliżonej dokładności (R ^ 2) na schemacie" . Znajduje się na samym dole okna. Oznacza to, że w ten sposób uwzględniamy mapowanie współczynnika determinacji na obszar konstrukcji. Następnie nie zapomnij kliknąć przycisku "Zamknij" u dołu bieżącego okna.
5. Wartość niezawodności aproksymacji, czyli wartość współczynnika determinacji, zostanie wyświetlona na arkuszu w obszarze budowy. W tym przypadku ta wartość, jak widzimy, wynosi 0,9242, co charakteryzuje aproksymację, jako model dobrej jakości.
6. W ten sposób można ustawić wyświetlanie współczynnika determinacji dla dowolnego innego typu linii trendu. Możesz zmienić typ linii trendu, przechodząc przez przycisk na wstążce lub menu kontekstowym w oknie parametrów, jak pokazano powyżej. Następnie możesz przejść do innego typu w grupie "Linia trendu" w samym oknie. Nie zapominamy kontrolować, na przykład, że zaznaczone jest pole wyboru obok "Umieść wartość dokładności aproksymacji" na schemacie . Po wykonaniu powyższych czynności kliknij przycisk "Zamknij" w prawym dolnym rogu okna.
7. W przypadku typu liniowego linia trendu ma już wartość pewności zbliżenia równą 0,9477, która charakteryzuje ten model, jako jeszcze bardziej niezawodną niż linia trendu typu wykładniczego, którą rozważaliśmy wcześniej.
8. Tak więc, przełączając się pomiędzy różnymi typami linii trendu i porównując ich wartości wiarygodności przybliżenia (współczynnik determinacji), można znaleźć wariant, którego model najdokładniej opisuje przedstawiony wykres. Wariant z najwyższym wskaźnikiem współczynnika determinacji będzie najbardziej wiarygodny. Na jego podstawie możesz zbudować najdokładniejszą prognozę.

   Na przykład dla naszego przypadku udało nam się ustalić z doświadczenia, że ​​najwyższy poziom niezawodności ma wielomianowy typ linii trendu drugiego stopnia. Współczynnik determinacji w tym przypadku wynosi 1. Wskazuje to, że model ten jest absolutnie wiarygodny, co oznacza całkowite wyeliminowanie błędów.

   Jednocześnie nie oznacza to wcale, że ten typ linii trendu jest najbardziej niezawodny dla innego wykresu. Optymalny wybór rodzaju linii trendu zależy od typu funkcji, na podstawie której został zbudowany wykres. Jeśli użytkownik nie ma wystarczającej wiedzy, aby "odgadnąć" najlepszą opcję, jedynym sposobem na ustalenie lepszej prognozy jest porównanie współczynników determinacji, jak pokazano w powyższym przykładzie.

Czytaj także:
Budowanie linii trendu w Excelu
Aproksymacja w programie Excel

W Excelu istnieją dwie główne opcje obliczania współczynnika determinacji: użycie operatora KVPIRSON i użycie narzędzia "Regresja" z pakietu narzędziowego "Analiza danych" . W takim przypadku pierwsza z tych opcji jest przeznaczona do użycia tylko w procesie przetwarzania funkcji liniowej, a inna opcja może być stosowana w prawie wszystkich sytuacjach. Ponadto możliwe jest wyświetlanie współczynnika determinacji dla linii trendu wykresów jako wartości wiarygodności przybliżenia. Za pomocą tego wskaźnika można określić typ linii trendu o najwyższym poziomie ufności dla danej funkcji.

• ← Zmiana rozmiaru komórek w programie Microsoft Excel
• Wyznaczanie współczynnika korelacji wielokrotnej w MS Excel →