Ковариационна матрица в Excel
Матрицата на ковариацията е квадратна матрица, която показва ковариацията между колоните и дисперсията в колоните. Excel представи вграден инструмент за анализ на данни за определяне на ковариацията между различните набори от данни. Настоящата статия ще обясни изчисляването на ковариационната матрица в Excel, като обхване следните теми, включително
Обяснение
Ковариацията е една от мерките, използвани за разбиране на това как дадена променлива е свързана с друга променлива. Следващата формула се използва за определяне на ковариацията.
COV (X, Y) = ∑ (x - x) (y - y) / n
Ковариационната матрица е квадратна матрица за разбиране на връзките, представени между различните променливи в набор от данни. Лесно и полезно е да се покаже ковариацията между две или повече променливи.
Ковариацията ще има както положителни, така и отрицателни стойности. Положителната стойност показва, че две променливи ще намаляват или се увеличават в една и съща посока. Отрицателната стойност показва, че ако една променлива намалява, друга променлива се увеличава и съществува обратна връзка между тях. Матрицата на ковариацията е представена в следния формат. Триизмерната ковариационна матрица е показана като
За да създадем 3 × 3 квадратна ковариационна матрица, трябва да имаме триизмерни данни. Диагоналните стойности на матрицата представляват дисперсиите на променливите X, Y и Z (т.е. COV (X, X), COV (Y, Y) и COV (Z, Z)). Матрицата на ковариацията е симетрична по отношение на диагонала. Това показва, че COV (X, Y) = COV (Y, X), COV (X, Z) = COV (Z, X) и COV (Y, Z) = COV (Z, Y). Една точка, която трябва да запомните за тази матрица, е резултатът от ковариационната матрица NXN за данни от n-измерение.
Как да използвам ковариантна матрица в Excel?
Ковариационната матрица се използва в различни приложения, включително
- Анализирайки как два вектора се различават един от друг
- Използва се в машинно обучение за определяне на моделите на зависимост между двата вектора
- Матрицата на ковариацията се използва за разказване на връзката между различните измерения на случайни променливи.
- Използва се в стохастичното моделиране във финансовия инженеринг за корелация на случайните променливи
- Основният компонент е друго приложение на ковариационната матрица към оригинални променливи към линейни независими променливи.
- При анализа на данните матрицата на ковариацията има жизненоважна роля.
- Матрицата на ковариацията се използва в съвременната теория на портфейла за оценка на рисковете.
- Мерките на матрицата на ковариацията се използват при прогнозиране на възвръщаемостта на финансовите активи.
Примери за ковариационна матрица в Excel
По-долу са дадени някои от примерите за използване на ковариационната матрица в Excel.
Пример # 1
Извършване на ковариационен анализ на оценките, получени от учениците по различни предмети.
Стъпка 1: Следните данни, включително оценките на учениците по математика, английски език и природни науки, се считат, както е показано на фигурата.
Стъпка 2: Отидете в раздела „Данни“ на лентата и намерете ToolPak „Анализ на данни“ в десния ъгъл.
Ако ToolPak „Анализ на данни“ не е наличен, следвайте тези стъпки.
Стъпка A: Отидете в раздела „Файл“ и след това изберете „опции“.
Ще се отвори следният екран.
Стъпка Б: Отидете на добавки. Под опцията за управление се уверете, че е избрана „добавки на Excel“ и изберете бутона „Go“, както е показано на фигурата.
Стъпка C: Изберете „Analysis-Tool Pak“ и „Analysis-ToolPak VBA“, както е показано на екранната снимка.
След като изпълните тези стъпки, пакетът с инструменти „Анализ на данни“ се добавя към раздела „Данни“.
Стъпка 3: Кликнете върху Анализ на данни. Това отваря диалоговия прозорец „Анализ на данни“. Изберете „Ковариация“, като превъртите нагоре и кликнете върху „OK“.
Той показва диалоговия прозорец „Ковариация“.
Стъпка 5: Изберете обхвата на въвеждане, включително имената на темите, проверете „етикетите в първи ред“ и дайте „изходния обхват“ в съществуващия работен лист. И кликнете върху „ОК“.
Стъпка 6: Ще получим изхода, както следва -
Горната част на диагонала е празна, тъй като матрицата на ковариацията на excel е симетрична спрямо диагонала.
Пример # 2
Извършете изчисление на матрицата на ковариацията, за да определите отклоненията между възвръщаемостта на различните портфейлни запаси
Стъпка 1: За този пример се вземат предвид следните данни, включително възвръщаемостта на акциите.
Стъпка 2: Отваря диалоговия прозорец „Анализ на данни“ и изберете „Ковариация“, като превъртите нагоре и кликнете върху „OK“.
Той показва диалоговия прозорец „Ковариация“.
Стъпка 3: Изберете обхвата на въвеждане, включително заглавките, проверете „етикетите в първи ред“ и дайте „изходния обхват“ в съществуващия работен лист. И кликнете върху „ОК“.
Стъпка 4: Ще получим изхода, както следва -
Горната част на диагонала е празна, тъй като ковариационната матрица е симетрична спрямо диагонала.
Пример # 3
Изчисляване на ковариационната матрица за цените на акциите на корпоративни компании
Стъпка 1: За този пример се вземат предвид следните данни, включително цените на акциите на различни компании.
Стъпка 2: Отваря диалоговия прозорец „Анализ на данни“, изберете „Ковариация“, като превъртите нагоре и кликнете върху „OK“.
Той показва диалоговия прозорец „Ковариация“.
Стъпка 3: Изберете обхвата на въвеждане, включително заглавията, проверете „етикетите в първи ред“ и дайте „обхвата на изхода“ в съществуващия работен лист и кликнете върху „OK“.
Стъпка 4: Ще получим изхода, както следва -
Неща за запомняне
- Инструментът за ковариация, представен от Excel, има някои ограничения, включително определяне само на формула за отклонения на популацията, създаване на матрица само с по-ниски диагонални стойности и разглеждане на формули само за дисперсия.
- Когато стойностите на връщането се променят, това не актуализира автоматично стойностите на матрицата.
- Горната половина на матрицата се показва като празна, тъй като е симетрична, а стойностите на огледални изображения се показват в долния диагонал.
