Macierz kowariancji w programie excel
Macierz kowariancji to macierz kwadratowa pokazująca kowariancję między kolumnami i wariancję w kolumnach. Program Excel został zaprezentowany z wbudowanym narzędziem do analizy danych w celu określenia kowariancji między różnymi zestawami danych. W niniejszym artykule wyjaśniono obliczanie macierzy kowariancji w programie Excel, omawiając następujące tematy, w tym
Wyjaśnienie
Kowariancja jest jedną z miar używanych do zrozumienia, w jaki sposób zmienna jest powiązana z inną zmienną. Poniższy wzór służy do wyznaczania kowariancji.
COV (X, Y) = ∑ (x - x) (y - y) / n
Macierz kowariancji to macierz kwadratowa służąca do zrozumienia zależności przedstawionych między różnymi zmiennymi w zbiorze danych. Pokazywanie kowariancji między dwiema lub więcej zmiennymi jest łatwe i użyteczne.
Kowariancja będzie miała zarówno wartości dodatnie, jak i ujemne. Wartość dodatnia oznacza, że dwie zmienne będą się zmniejszać lub zwiększać w tym samym kierunku. Wartość ujemna wskazuje, że jeśli jedna zmienna maleje, druga zmienna zwiększa się i istnieje między nimi odwrotna zależność. Macierz kowariancji jest reprezentowana w następującym formacie. Trójwymiarową macierz kowariancji pokazano jako
Aby utworzyć kwadratową macierz kowariancji 3 × 3, musimy mieć dane trójwymiarowe. Przekątne wartości macierzy reprezentują wariancje zmiennych X, Y i Z (tj. COV (X, X), COV (Y, Y) i COV (Z, Z)). Macierz kowariancji jest symetryczna względem przekątnej. Oznacza to, że COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) i COV (Y, Z) = COV (Z, Y). Należy pamiętać o tej macierzy wynikającej z macierzy kowariancji NXN dla danych n-wymiarowych.
Jak korzystać z macierzy kowariancji w programie Excel?
Macierz kowariancji jest używana w różnych zastosowaniach, w tym
- Analiza różnic między dwoma wektorami
- Używany w uczeniu maszynowym do określania wzorców zależności między dwoma wektorami
- Macierz kowariancji służy do określania relacji między różnymi wymiarami zmiennych losowych
- Używany w modelowaniu stochastycznym w inżynierii finansowej do skorelowania zmiennych losowych
- Składnik zasady to kolejne zastosowanie macierzy kowariancji do oryginalnych zmiennych do liniowych zmiennych niezależnych
- W analizie danych istotną rolę odgrywa macierz kowariancji.
- Macierz kowariancji jest wykorzystywana w nowoczesnej teorii portfela do szacowania ryzyka.
- Miary macierzy kowariancji są wykorzystywane do przewidywania zwrotów z aktywów finansowych
Przykłady macierzy kowariancji w programie Excel
Poniżej podano kilka przykładów wykorzystania macierzy kowariancji w programie Excel.
Możesz pobrać ten szablon Excel macierzy kowariancji tutaj - szablon Excel macierzy kowariancjiPrzykład 1
Analiza kowariancji na ocenach uzyskanych przez studentów z różnych przedmiotów.
Krok 1: Uważa się, że poniższe dane, w tym oceny uczniów z matematyki, języka angielskiego i nauk ścisłych, są przedstawione na rysunku.
Krok 2: Przejdź do zakładki „Dane” na wstążce i znajdź pakiet narzędzi „Analiza danych” w prawym bocznym rogu.
Jeśli „Analiza danych” ToolPak nie jest dostępny, wykonaj następujące kroki
Krok A: Przejdź do zakładki „Plik”, a następnie wybierz „opcje”.
Otworzy się następujący ekran.
Krok B: Przejdź do dodatków. W opcji Zarządzaj upewnij się, że „Dodatki programu Excel” jest zaznaczone i wybierz przycisk „Idź”, jak pokazano na rysunku.
Krok C: Wybierz „Analysis-Tool Pak” i „Analysis-ToolPak VBA”, jak pokazano na zrzucie ekranu.
Po wykonaniu tych czynności pakiet narzędzi „Analiza danych” zostanie dodany do zakładki „Dane”.
Krok 3: Kliknij Analiza danych. Otworzy się okno dialogowe „Analiza danych”. Wybierz „Kowariancję”, przewijając w górę i kliknij „OK”.
Wyświetla okno dialogowe „Kowariancja”.
Krok 5: Wybierz zakres danych wejściowych, w tym nazwy tematów, zaznacz „etykiety w pierwszym wierszu” i podaj „zakres wyjściowy” w istniejącym arkuszu. I kliknij „OK”.
Krok 6: Otrzymamy dane wyjściowe w następujący sposób -
Górna część przekątnej jest pusta, ponieważ macierz kowariancji excela jest symetryczna względem przekątnej.
Przykład nr 2
Obliczanie macierzy kowariancji w celu określenia wariancji między zwrotami różnych akcji portfela.
Krok 1: W tym przykładzie uwzględniono następujące dane, w tym zwroty zapasów.
Krok 2: Otwiera okno dialogowe „Analiza danych” i wybierz „Kowariancję”, przewijając w górę i klikając „OK”.
Wyświetla okno dialogowe „Kowariancja”.
Krok 3: Wybierz zakres danych wejściowych, w tym nagłówki, zaznacz „etykiety w pierwszym wierszu” i podaj „zakres wyjściowy” w istniejącym arkuszu. I kliknij „OK”.
Krok 4: Otrzymamy dane wyjściowe w następujący sposób -
Górna część przekątnej jest pusta, ponieważ macierz kowariancji jest symetryczna względem przekątnej.
Przykład nr 3
Obliczanie macierzy kowariancji dla cen akcji spółek korporacyjnych
Krok 1: W tym przykładzie uwzględniono następujące dane, w tym ceny akcji różnych firm.
Krok 2: Otwiera okno dialogowe „Analiza danych”, wybierz „Kowariancję” przewijając w górę i kliknij „OK”.
Wyświetla okno dialogowe „Kowariancja”.
Krok 3: Wybierz zakres danych wejściowych, w tym nagłówki, zaznacz „etykiety w pierwszym wierszu” i podaj „zakres wyjściowy” w istniejącym arkuszu i kliknij „OK”.
Krok 4: Otrzymamy dane wyjściowe w następujący sposób -
Rzeczy do zapamiętania
- Narzędzie kowariancji przedstawione w Excelu ma pewne ograniczenia, w tym wyznaczanie tylko formuły na wariancje populacji, tworzenie macierzy tylko z mniejszymi wartościami przekątnymi i uwzględnianie wzorów tylko dla wariancji.
- Gdy wartości zwracane są zmieniane, nie aktualizuje automatycznie wartości macierzy.
- Górna połowa macierzy jest wyświetlana jako pusta, ponieważ jest symetryczna, a wartości odbicia lustrzanego są wyświetlane na dolnej przekątnej.