Macierz kowariancji w programie Excel

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 kowariancji

Przykł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.