Kovarianzmatrix in Excel

Kovarianzmatrix in Excel

Die Kovarianzmatrix ist eine quadratische Matrix, um die Kovarianz zwischen den Spalten und die Varianz in Spalten anzuzeigen. Excel wird mit dem integrierten Tool "Datenanalyse" vorgestellt, um die Kovarianz zwischen den verschiedenen Datensätzen zu bestimmen. In diesem Artikel wird die Berechnung der Kovarianzmatrix in Excel anhand der folgenden Themen erläutert

Erläuterung

Die Kovarianz ist eine der Maßnahmen, mit denen verstanden wird, wie eine Variable einer anderen Variablen zugeordnet ist. Die folgende Formel wird zur Kovarianzbestimmung verwendet.

COV (X, Y) = ∑ (x - x) (y - y) / n

Die Kovarianzmatrix ist eine quadratische Matrix, um die Beziehungen zu verstehen, die zwischen den verschiedenen Variablen in einem Datensatz dargestellt werden. Es ist einfach und nützlich, die Kovarianz zwischen zwei oder mehr Variablen zu zeigen.

Die Kovarianz hat sowohl positive als auch negative Werte. Ein positiver Wert zeigt an, dass zwei Variablen in derselben Richtung abnehmen oder zunehmen. Ein negativer Wert zeigt an, dass, wenn eine Variable abnimmt, eine andere Variable zunimmt und eine umgekehrte Beziehung zwischen ihnen besteht. Die Kovarianzmatrix wird im folgenden Format dargestellt. Die dreidimensionale Kovarianzmatrix ist dargestellt als

Um die 3 × 3-Quadrat-Kovarianzmatrix zu erstellen, benötigen wir dreidimensionale Daten. Die Diagonalwerte der Matrix repräsentieren die Varianzen der X-, Y- und Z-Variablen (dh COV (X, X), COV (Y, Y) und COV (Z, Z)). Die Kovarianzmatrix ist bezüglich der Diagonale symmetrisch. Dies zeigt an, dass COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) und COV (Y, Z) = COV (Z, Y). Ein Punkt, an den Sie sich bei dieser Matrix erinnern sollten, ist das Ergebnis der NXN-Kovarianzmatrix für n-dimensionale Daten.

Wie verwende ich eine Kovarianzmatrix in Excel?

Die Kovarianzmatrix wird in verschiedenen Anwendungen verwendet, einschließlich

  • Analysieren, wie sich zwei Vektoren voneinander unterscheiden
  • Wird beim maschinellen Lernen verwendet, um die Abhängigkeitsmuster zwischen den beiden Vektoren zu bestimmen
  • Die Kovarianzmatrix wird verwendet, um die Beziehung zwischen den verschiedenen Dimensionen von Zufallsvariablen zu bestimmen
  • Wird in der stochastischen Modellierung im Financial Engineering verwendet, um die Zufallsvariablen zu korrelieren
  • Die Hauptkomponente ist eine weitere Anwendung der Kovarianzmatrix auf Originalvariablen auf lineare unabhängige Variablen
  • Bei der Datenanalyse spielt die Kovarianzmatrix eine wichtige Rolle.
  • Die Kovarianzmatrix wird in der modernen Portfoliotheorie zur Abschätzung von Risiken verwendet.
  • Die Kennzahlen der Kovarianzmatrix werden verwendet, um die Renditen der finanziellen Vermögenswerte zu antizipieren

Beispiele für die Kovarianzmatrix in Excel

Im Folgenden sind einige Beispiele für die Verwendung der Kovarianzmatrix in Excel aufgeführt.

Sie können diese Covariance Matrix Excel-Vorlage hier herunterladen - Covariance Matrix Excel-Vorlage

Beispiel 1

Durchführen einer Kovarianzanalyse der Noten, die von Schülern in verschiedenen Fächern erhalten wurden.

Schritt 1: Die folgenden Daten, einschließlich der Noten von Studenten in Mathematik, Englisch und Naturwissenschaften, werden wie in der Abbildung gezeigt betrachtet.

Schritt 2: Gehen Sie zur Registerkarte "Daten" in der Multifunktionsleiste und suchen Sie das ToolPak "Datenanalyse" in der rechten Ecke.

Wenn das ToolPak "Datenanalyse" nicht verfügbar ist, führen Sie die folgenden Schritte aus

Schritt A: Gehen Sie zur Registerkarte "Datei" und wählen Sie die "Optionen".

Der folgende Bildschirm wird geöffnet.

Schritt B: Gehen Sie zu Add-Ins. Stellen Sie unter der Option Verwalten sicher, dass 'Excel-Add-Ins' ausgewählt ist, und klicken Sie auf die Schaltfläche 'Los', wie in der Abbildung gezeigt.

Schritt C: Wählen Sie das „Analysis-Tool Pak“ und das „Analysis-ToolPak VBA“ aus, wie im Screenshot gezeigt.

Nach Abschluss dieser Schritte wird das Toolpaket "Datenanalyse" zur Registerkarte "Daten" hinzugefügt.

Schritt 3: Klicken Sie auf Datenanalyse. Dies öffnet das Dialogfeld "Datenanalyse". Wählen Sie die "Kovarianz" aus, indem Sie nach oben scrollen und auf "OK" klicken.

Es wird das Dialogfeld "Kovarianz" angezeigt.

Schritt 5:  Wählen Sie den Eingabebereich einschließlich der Betreffnamen aus, überprüfen Sie die „Beschriftungen in der ersten Zeile“ und geben Sie den „Ausgabebereich“ im vorhandenen Arbeitsblatt an. Und klicken Sie auf "OK".

Schritt 6:  Wir erhalten die Ausgabe wie folgt:

Der obere Teil der Diagonale ist leer, da die Excel-Kovarianzmatrix zur Diagonale hin symmetrisch ist.

Beispiel 2

Berechnung der Kovarianzmatrix zur Bestimmung von Abweichungen zwischen den Renditen verschiedener Portfoliobestände.

Schritt 1: In diesem Beispiel werden die folgenden Daten einschließlich der Aktienrenditen berücksichtigt.

Schritt 2: Öffnet das Dialogfeld "Datenanalyse" und wählen Sie die "Kovarianz" aus, indem Sie nach oben scrollen und auf "OK" klicken.

Es wird das Dialogfeld "Kovarianz" angezeigt.

Schritt 3: Wählen Sie den Eingabebereich einschließlich der Überschriften aus, überprüfen Sie die „Beschriftungen in der ersten Zeile“ und geben Sie den „Ausgabebereich“ im vorhandenen Arbeitsblatt an. Und klicken Sie auf "OK".

Schritt 4: Wir erhalten die Ausgabe wie folgt -

Der obere Teil der Diagonale ist leer, da die Kovarianzmatrix zur Diagonale hin symmetrisch ist.

Beispiel 3

Berechnung der Kovarianzmatrix für Aktienkurse von Unternehmen

Schritt 1: In diesem Beispiel werden die folgenden Daten einschließlich der Aktienkurse verschiedener Unternehmen berücksichtigt.

Schritt 2: Öffnet das Dialogfeld "Datenanalyse", wählen Sie die "Kovarianz" aus, indem Sie nach oben scrollen und auf "OK" klicken.

Es wird das Dialogfeld "Kovarianz" angezeigt.

Schritt 3: Wählen Sie den Eingabebereich einschließlich der Überschriften aus, überprüfen Sie die „Beschriftungen in der ersten Zeile“ und geben Sie den „Ausgabebereich“ im vorhandenen Arbeitsblatt an und klicken Sie auf „OK“.

Schritt 4: Wir erhalten die Ausgabe wie folgt -

Dinge, an die man sich erinnern sollte

  • Das von Excel vorgestellte Kovarianz-Tool weist einige Einschränkungen auf, darunter die Bestimmung nur der Populationsvarianzformel, die Erstellung einer Matrix mit nur niedrigeren Diagonalwerten und die Berücksichtigung von Formeln nur für eine Varianz.
  • Wenn Rückgabewerte geändert werden, werden die Werte der Matrix nicht automatisch aktualisiert.
  • Die obere Hälfte der Matrix wird als leer angezeigt, da sie symmetrisch ist und Spiegelbildwerte in der unteren Diagonale angezeigt werden.