ความแปรปรวนร่วมและสหสัมพันธ์ ตัวอย่างการแก้ปัญหาบน Wikimatic

ฟังก์ชัน COVARIATION ใน Excel จะคำนวณค่าสัมประสิทธิ์ความแปรปรวนร่วมของชุดข้อมูลสองชุด (อาร์เรย์หรือช่วงของเซลล์ที่เก็บค่าตัวเลข) ซึ่งเป็นตัวอย่างของช่วงข้อมูลที่สอดคล้องกันและส่งกลับค่าตัวเลขที่สอดคล้องกัน

ฟังก์ชัน COVARIATION.G ใน Excel ใช้ในการคำนวณค่าสัมประสิทธิ์ความแปรปรวนร่วมของประชากรทั้งหมดของช่วงข้อมูลสองช่วง (ประชากร) และส่งกลับค่าที่สอดคล้องกัน

ฟังก์ชัน KOVAR ใน Excel ได้รับการออกแบบมาเพื่อคำนวณค่าสัมประสิทธิ์ความแปรปรวนร่วมของข้อมูลตัวเลขสองชุดที่เป็นประชากร

การใช้ฟังก์ชัน COVAR, COVARIATION.B และ COVARIATION.G ใน Excel

สเปรดชีต Excel มีข้อมูลสองช่วง ช่วงแรกแสดงลักษณะจำนวนหนังสือที่อ่านต่อปีโดยนักเรียนแต่ละคนที่เลือกจากเกรดต่างๆ ของโรงเรียน และช่วงที่สองแสดงลักษณะเกรดสุดท้ายในวรรณคดีในระดับ 10 จุด หาค่าสัมประสิทธิ์ความแปรปรวนร่วมระหว่างช่วงข้อมูลสองช่วง

มุมมองตารางต้นทาง:

เนื่องจากเลือกนักเรียนที่มีเกรดต่างกันหลายคนมาวิเคราะห์ ทั้งสองช่วงจึงถือเป็นกลุ่มตัวอย่างจากประชากรทั่วไป ซึ่งรวมถึงนักเรียนชั้นประถมศึกษาปีที่ 9 ทุกคนในโรงเรียนที่กำหนด เราใช้ฟังก์ชันต่อไปนี้:

คำอธิบายของอาร์กิวเมนต์:

• B3: B14 - ช่วงของเซลล์ที่มีข้อมูลเกี่ยวกับจำนวนหนังสือที่อ่าน
• C3: C14 เป็นช่วงของเซลล์ที่มีเกรดสุดท้ายสำหรับหัวเรื่อง

ผลลัพธ์คือ:

ค่าผลลัพธ์บ่งชี้ว่ามีความสัมพันธ์โดยตรงระหว่างค่าของทั้งสองช่วง กล่าวคือสามารถสันนิษฐานได้ว่านักเรียนที่อ่านหนังสือมากขึ้นจะได้รับเกรดที่สูงขึ้นสำหรับวิชานี้

การคำนวณความแปรปรวนร่วมของราคาที่เพิ่มขึ้นและลดลงของหุ้นสองประเภทใน Excel

สเปรดชีต Excel มีข้อมูลเกี่ยวกับการเติบโต (จำนวนบวก) หรือการลดลงของราคา (เชิงลบ) ของหลักทรัพย์สองชนิดที่แตกต่างกันในช่วง 12 เดือนของปีซึ่งสัมพันธ์กับค่าเริ่มต้นที่แน่นอน กำหนดความแปรปรวนร่วมของช่วงข้อมูลทั้งสองและสรุปผล ทำให้รายงานพร้อมใช้งานสำหรับผู้ใช้ Excel 2007

มุมมองตารางต้นทาง:

ในตัวอย่างนี้ ตัวอย่างทั่วไปทั้งหมดจะถูกตรวจสอบ คุณสามารถใช้ฟังก์ชัน COVARIATION.D สำหรับการคำนวณได้ แต่ผลลัพธ์จะไม่สามารถใช้ได้สำหรับผู้ใช้ Excel เวอร์ชันเก่า ลองใช้สูตรต่อไปนี้:

เป็นผลให้เราได้รับ:

ค่านี้บ่งชี้ความสัมพันธ์ที่ค่อนข้างใหญ่ระหว่างค่าที่ศึกษา เนื่องจากตัวเลขเป็นค่าลบ ความสัมพันธ์นี้จึงกลับกัน นั่นคือเมื่อราคาหุ้นหนึ่งเพิ่มขึ้น ราคาหุ้นที่สองจะลดลงและในทางกลับกัน สันนิษฐานได้ว่าหุ้นเหล่านี้เป็นเจ้าของโดยบริษัทที่แข่งขันกันสองแห่ง

การวิเคราะห์ทางสถิติของความแปรปรวนร่วมของตัวบ่งชี้ใน Excel

ตาราง Excel มีข้อมูลเกี่ยวกับความต้องการเครื่องดื่มแอลกอฮอล์ ดัชนีราคา และระดับรายได้ของประชากรในรัฐ วิเคราะห์ความสัมพันธ์ระหว่างข้อมูลที่มีอยู่

มุมมองของตารางข้อมูลเดิม:

ขั้นแรก เราคำนวณความแปรปรวนร่วมระหว่างอุปสงค์และดัชนีราคาโดยใช้สูตร:

ผลลัพธ์คือ:

ในการประเมินระดับความสัมพันธ์ระหว่างช่วงข้อมูลสองช่วง จะสะดวกกว่าในการใช้ค่าสัมประสิทธิ์สหสัมพันธ์ ซึ่งสามารถคำนวณได้โดยไม่ต้องใช้ฟังก์ชัน CORREL ด้วยวิธีต่อไปนี้

B12 / ROOT (DISP.Y (B3: B10) * DISP.Y (C3: C10))

ฟังก์ชัน DISP.G ใช้ในการคำนวณความแปรปรวนของประชากร สูตรข้างต้นแสดงให้เห็นอย่างชัดเจนถึงความสัมพันธ์ระหว่างสัมประสิทธิ์ความแปรปรวนร่วมและสหสัมพันธ์

ผลลัพธ์คือ:

อย่างที่คุณเห็น มีความสัมพันธ์ผกผันที่ค่อนข้างแข็งแกร่งระหว่างราคาและอุปสงค์ อย่างไรก็ตาม เพื่อกำหนดระดับอิทธิพลของอุปสงค์ เรากำหนดสัมประสิทธิ์การกำหนด r2 ตามสูตร:

ดีกรี (B13; 2)

ค่าผลลัพธ์ที่แสดงเป็นเปอร์เซ็นต์:

นั่นคือประมาณ 59% ของความต้องการที่ผันแปรในช่วงเวลาที่ทำการศึกษานั้นเกิดจากความผันผวนของราคา ส่วนที่เหลืออีก 41% เป็นปัจจัยอื่นๆ และอีกปัจจัยในตัวอย่างนี้คือระดับของรายได้ มาคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ระหว่างอุปสงค์และรายได้โดยใช้ฟังก์ชันต่อไปนี้:

คอร์เรล (B3: B10; D3: D10)

ผลลัพธ์:

ค่าบวก 0.741 สอดคล้องกับความสัมพันธ์ที่ค่อนข้างแข็งแกร่งระหว่างการเติบโตของรายได้และอุปสงค์ เพื่อหาค่าสัมประสิทธิ์สหสัมพันธ์โดยรวมและหาข้อสรุป เราพบค่าสัมประสิทธิ์สหสัมพันธ์ระหว่างดัชนีราคาและระดับของรายได้:

คอร์เรล (C3: C10; D3: D10)

ผลลัพธ์:

เรามีความสัมพันธ์ผกผันไม่เด่นชัดนัก ตอนนี้ มาคำนวณสัมประสิทธิ์สหสัมพันธ์โดยรวมโดยใช้สูตร:

= (B13-B15 * B16) / ROOT ((1-DEGREE (B15,2)) * (1-DEGREE (B16,2)))

ผลลัพธ์:

การคำนวณแสดงให้เห็นว่าผลกระทบของราคาที่สูงขึ้นต่อระดับความต้องการนั้น "คลี่คลาย" เนื่องจากการเพิ่มขึ้นของระดับรายได้ของประชากร รากที่สองของค่าโมดูโลหลังมีค่าประมาณ 91% ซึ่งแสดงว่าความผันแปรของราคาเป็นตัวกำหนดความแปรผันของความต้องการเครื่องดื่มแอลกอฮอล์ ไม่รวมการแปรผันของรายได้แบบคู่ขนาน

ลักษณะเฉพาะของการใช้ฟังก์ชัน COVAR, COVARIATION.B และ COVARIATION.G ใน Excel

ฟังก์ชัน KOVAR มีไวยากรณ์ต่อไปนี้:

โควาร์ (array1, array2)

COVARIATION.function มีไวยากรณ์ต่อไปนี้:

COVARIATION.In (array1, array2)

ไวยากรณ์สำหรับฟังก์ชัน COVARIATION.G คือ:

COVARIATION.G (array1, array2)

ฟังก์ชันที่พิจารณาทั้งหมดใช้อาร์กิวเมนต์ต่อไปนี้เป็นอินพุต:

• array1 เป็นอาร์กิวเมนต์ที่กำหนดซึ่งกำหนดลักษณะอาร์เรย์หรือช่วงแรกของเซลล์ที่มีข้อมูลตัวเลข ซึ่งเป็นข้อมูลประชากรทั่วไปทั้งหมด (สำหรับฟังก์ชัน COVARIATION.G และ COVAR) หรือส่วนที่เลือก (สำหรับฟังก์ชัน COVARIATION.B)
• array2 เป็นอาร์กิวเมนต์ที่จำเป็นซึ่งกำหนดลักษณะอาร์เรย์ที่สองหรือช่วงของเซลล์ที่มีค่าตัวเลข (ประชากรทั่วไปหรือกลุ่มตัวอย่าง ซึ่งกำหนดทางเลือกของฟังก์ชันสำหรับการคำนวณ)

หมายเหตุ 1:

1. ฟังก์ชันทั้งหมดเหล่านี้ยอมรับอาร์เรย์หรือการอ้างอิงช่วงของเซลล์เป็นอาร์กิวเมนต์ ซึ่งประกอบด้วยข้อความ บูลีน ตัวเลข และข้อมูลประเภทอื่นๆ
2. จำนวนขององค์ประกอบในช่วงหรืออาร์เรย์ที่ส่งผ่านเป็นอาร์กิวเมนต์ array1 และ array2 ต้องตรงกัน มิฉะนั้น ฟังก์ชันที่พิจารณาทั้งหมดจะส่งคืนรหัสข้อผิดพลาด # N / A
3. การคำนวณไม่คำนึงถึงค่าของข้อความ ชื่อ ค่าบูลีน (TRUE, FALSE) ที่อ้างอิงถึงเซลล์ว่าง อย่างไรก็ตาม เซลล์ที่มีค่าตัวเลข 0 (ศูนย์) จะถูกนับ
4. หากฟังก์ชันที่เป็นปัญหาถือเป็นอาร์กิวเมนต์:

• ช่วงของเซลล์ว่าง ผลลัพธ์ของการดำเนินการจะเป็นรหัสข้อผิดพลาด #VALUE! (ใช้เซลล์ว่างหนึ่งเซลล์เป็นอาร์กิวเมนต์แต่ละรายการ) หรือ # DIV / 0! (ใช้เซลล์ว่างหลายเซลล์เป็นอาร์กิวเมนต์);
• อาร์เรย์ที่ประกอบด้วยองค์ประกอบหนึ่งองค์ประกอบหรือหนึ่งเซลล์เป็นอาร์กิวเมนต์แต่ละรายการ ฟังก์ชัน COVARIATION.G และ COVAR จะคืนค่าที่เป็นตัวเลข 0 และฟังก์ชัน COVARIATION.B จะส่งคืน # DIV / 0! รหัสข้อผิดพลาด

หมายเหตุ 2:

1. ความแปรปรวนร่วมคือค่าที่กำหนดลักษณะความสัมพันธ์เชิงเส้นตรงที่สร้างขึ้นระหว่างตัวแปรสุ่มสองชุด X และ Y ซึ่งสอดคล้องกับความคาดหวังทางคณิตศาสตร์ของผลิตภัณฑ์ของการเบี่ยงเบนของ X และ Y จากศูนย์กระจายสินค้า สัมประสิทธิ์ความแปรปรวนร่วมสามารถแสดงเป็นจำนวนลบ บวก และศูนย์ ด้วย:

• หากค่า X เพิ่มขึ้น ยิ่งมีโอกาสเกิดขึ้นของค่า Y จำนวนมากและในทางกลับกัน มีความสัมพันธ์โดยตรงระหว่างสองช่วงดังที่เห็นได้จากค่าบวกของสัมประสิทธิ์ความแปรปรวนร่วม
• ถ้าค่า X เพิ่มขึ้น ค่าของ Y มีแนวโน้มลดลง และในทางกลับกัน ความสัมพันธ์แบบผกผันจะถูกสร้างขึ้น ซึ่งแสดงโดยค่าลบของสัมประสิทธิ์ความแปรปรวนร่วม
• หากมีการสร้างความสัมพันธ์ที่อ่อนแอระหว่าง X และ Y (โดยมีการเปลี่ยนแปลงใน X การเปลี่ยนแปลงใน Y จะไม่สอดคล้องกัน ไม่เป็นระเบียบ) ค่าของสัมประสิทธิ์ความแปรปรวนร่วมมีแนวโน้มเป็นศูนย์

หมายเหตุ 3:

1. ฟังก์ชัน COVAR เป็นฟังก์ชันมาตรฐานสำหรับการคำนวณความแปรปรวนร่วมใน Excel เวอร์ชันก่อนหน้า (2007 และเก่ากว่า) และคงไว้เพื่อความเข้ากันได้ อาจไม่มีอยู่ใน Excel รุ่นที่ใหม่กว่า ดังนั้นจึงขอแนะนำให้คุณใช้ฟังก์ชัน COVARATION.B และ COVARIATION.D
2. ตัวอย่างเป็นส่วนย่อยของค่าของชุดหนึ่งเรียกว่าประชากรทั่วไป กล่าวอีกนัยหนึ่ง ตัวอย่างเป็นผลมาจากการสังเกตคุณลักษณะอย่างใดอย่างหนึ่งหรือหลายอย่างในจำนวนที่จำกัด ตัวอย่างเช่น เมื่อศึกษาระบบการธนาคารของรัฐ ประชากรทั่วไปคือองค์กรการธนาคารทั้งหมดในประเทศ และกลุ่มตัวอย่างคือธนาคารของเมืองเซนต์ปีเตอร์สเบิร์ก
3. ไม่เหมือนกับค่าสัมประสิทธิ์สหสัมพันธ์ ค่าของสัมประสิทธิ์ความแปรปรวนร่วมไม่จำกัดเฉพาะช่วงของตัวเลขตั้งแต่ -1 ถึง 1
4. เมื่อกำหนดสัมประสิทธิ์ความแปรปรวนร่วมของตัวเลขสองช่วงเดียวกัน ฟังก์ชัน COVAR และ COVARIATION.G จะส่งกลับผลลัพธ์เดียวกัน ซึ่งแตกต่างจากค่าตัวเลขที่ส่งคืนโดยฟังก์ชัน COVARIATION.B เนื่องจากใช้อัลกอริธึมการคำนวณที่แตกต่างกัน

ในกรณีของตัวแปรสุ่มหลายมิติ (เวกเตอร์สุ่ม) ลักษณะของการแพร่กระจายของส่วนประกอบและความสัมพันธ์ระหว่างพวกมันคือเมทริกซ์ความแปรปรวนร่วม

เมทริกซ์ความแปรปรวนร่วมถูกกำหนดให้เป็นความคาดหวังทางคณิตศาสตร์ของผลิตภัณฑ์ของเวกเตอร์สุ่มที่มีจุดศูนย์กลางโดยเวกเตอร์เดียวกันแต่มีการทรานสโพส:

ที่ไหน

เมทริกซ์ความแปรปรวนร่วมมีรูปแบบ

โดยที่เส้นทแยงมุมคือความแปรปรวนของพิกัดของเวกเตอร์สุ่ม o n = D Xi, o 22 = D X2, о кк = NS Xk และองค์ประกอบที่เหลือคือความแปรปรวนร่วมระหว่างพิกัด

° 12 = M "x i x 2 j a 1 * = M-jc, **>

เมทริกซ์ความแปรปรวนร่วมเป็นเมทริกซ์สมมาตรเช่น

ตัวอย่างเช่น พิจารณาเมทริกซ์ความแปรปรวนร่วมของเวกเตอร์สองมิติ

เมทริกซ์ความแปรปรวนร่วมจะได้รับในทำนองเดียวกันสำหรับเวกเตอร์มิติใด ๆ / ^

ความแปรปรวนของพิกัดสามารถแสดงเป็น

ที่ Gi, C2, ..., 0? - หมายถึงค่าเบี่ยงเบนกำลังสองของพิกัดของเวกเตอร์สุ่ม

ค่าสัมประสิทธิ์สหสัมพันธ์ ดังที่คุณทราบ อัตราส่วนของความแปรปรวนร่วมต่อผลคูณของส่วนเบี่ยงเบนมาตรฐาน:

หลังจากการทำให้เป็นมาตรฐานโดยอัตราส่วนสุดท้ายของสมาชิกของเมทริกซ์ความแปรปรวนร่วมจะได้รับเมทริกซ์สหสัมพันธ์

ซึ่งมีความสมมาตรและไม่เป็นลบแน่นอน

อะนาล็อกหลายมิติของความแปรปรวนของตัวแปรสุ่มคือความแปรปรวนทั่วไป ซึ่งเข้าใจว่าเป็นค่าดีเทอร์มีแนนต์ของเมทริกซ์ความแปรปรวนร่วม

ลักษณะทั่วไปอีกประการหนึ่งของระดับการกระจายของตัวแปรสุ่มหลายมิติคือการติดตามของเมทริกซ์ความแปรปรวนร่วม

โดยที่ CKK เป็นองค์ประกอบในแนวทแยงของเมทริกซ์ความแปรปรวนร่วม

การแจกแจงแบบปกติมักใช้ในการวิเคราะห์ทางสถิติหลายตัวแปร

ลักษณะทั่วไปของความหนาแน่นของความน่าจะเป็นปกติในกรณีของเวกเตอร์สุ่ม a- มิติคือฟังก์ชัน

โดยที่ q = (pj, q 2, M ^) t - เวกเตอร์คอลัมน์ของความคาดหวังทางคณิตศาสตร์

| X | - ดีเทอร์มิแนนต์ของเมทริกซ์ความแปรปรวนร่วม X;

1 - เมทริกซ์ความแปรปรวนร่วมผกผัน

เมทริกซ์ X -1 ผกผันกับเมทริกซ์ X ของมิติ นเอ็กซ์ น,สามารถรับได้หลายวิธี หนึ่งในนั้นคือวิธี Jordan-Gauss ในกรณีนี้ สมการเมทริกซ์จะถูกรวบรวม

ที่ไหน NS- เวกเตอร์คอลัมน์ของตัวแปรจำนวนซึ่งเท่ากับ i; NS -เวกเตอร์คอลัมน์ i มิติของด้านขวามือ

ให้เราคูณสมการ (6.21) ทางด้านซ้ายด้วยเมทริกซ์ผกผัน XY 1:

เนื่องจากผลคูณของอินเวอร์สและเมทริกซ์ที่กำหนดให้เมทริกซ์เอกลักษณ์ อี,แล้ว

ถ้าแทน NSเอาเวกเตอร์หน่วย

แล้วผลิตภัณฑ์ X -1 -อดีตให้คอลัมน์แรกของเมทริกซ์ผกผัน ถ้าเราหาเวกเตอร์หน่วยที่สอง

แล้วผลิตภัณฑ์ E 1 อี2ให้คอลัมน์แรกของเมทริกซ์ผกผัน ฯลฯ ดังนั้นการแก้สมการอย่างต่อเนื่อง

โดยวิธี Jordan-Gauss เราได้คอลัมน์ทั้งหมดของเมทริกซ์ผกผัน

อีกวิธีหนึ่งในการรับเมทริกซ์ผกผันกับเมทริกซ์ E นั้นเกี่ยวข้องกับการคำนวณการเติมเต็มเชิงพีชคณิต A tJ .= (/= 1, 2,..., NS; เจ = 1, 2, ..., NS)ไปยังองค์ประกอบของเมทริกซ์ E นี้ แทนที่พวกมันแทนองค์ประกอบของเมทริกซ์ E และขนส่งเมทริกซ์ดังกล่าว:

เมทริกซ์ผกผันจะได้รับหลังจากแบ่งองค์ประกอบ ในบนดีเทอร์มีแนนต์ของเมทริกซ์ E:

คุณลักษณะที่สำคัญของการได้เมทริกซ์ผกผันในกรณีนี้คือเมทริกซ์ความแปรปรวนร่วม E ถูกปรับสภาพอย่างอ่อน สิ่งนี้นำไปสู่ความจริงที่ว่าเมื่อทำการกลับเมทริกซ์ดังกล่าว อาจเกิดข้อผิดพลาดร้ายแรงได้ ทั้งหมดนี้ต้องการการรับรองความถูกต้องที่จำเป็นของกระบวนการคำนวณหรือการใช้วิธีการพิเศษในการคำนวณเมทริกซ์ดังกล่าว

ตัวอย่าง.เขียนนิพจน์สำหรับความหนาแน่นของความน่าจะเป็นสำหรับตัวแปรสุ่มสองมิติแบบกระจายตามปกติ (X v X 2)

โดยมีเงื่อนไขว่าความคาดหวังทางคณิตศาสตร์ ความแปรปรวน และความแปรปรวนร่วมของปริมาณเหล่านี้มีความหมายดังต่อไปนี้:

วิธีการแก้.เมทริกซ์ความแปรปรวนร่วมผกผันสำหรับเมทริกซ์ (6.19) สามารถหาได้โดยใช้นิพจน์ต่อไปนี้สำหรับเมทริกซ์ผกผันสำหรับเมทริกซ์ X:

โดยที่ A คือดีเทอร์มีแนนต์ของเมทริกซ์ X

A และ L 12, A 21, A 22- พีชคณิตเติมเต็มองค์ประกอบที่สอดคล้องกันของเมทริกซ์ X

แล้วสำหรับเมทริกซ์] r-! เราได้รับนิพจน์

เนื่องจาก a 12 = 01О2Р และ ° 2i = a 2 a iP> a a i2 a 2i = cyfst | p หมายความว่า

หางาน

ฟังก์ชันความหนาแน่นของความน่าจะเป็นจะถูกเขียนเป็น

แทนข้อมูลเริ่มต้น เราได้รับนิพจน์ต่อไปนี้สำหรับฟังก์ชันความหนาแน่นของความน่าจะเป็น

ทางคณิตศาสตร์ ความแปรปรวนร่วม (ภาษาอังกฤษ ความแปรปรวนร่วม) เป็นตัววัด ความสัมพันธ์เชิงเส้นสองตัวแปรสุ่ม ในทฤษฎีพอร์ตโฟลิโอ ตัวบ่งชี้นี้ใช้เพื่อกำหนดความสัมพันธ์ระหว่างผลตอบแทนจากหลักทรัพย์เฉพาะและผลตอบแทนจากพอร์ตหลักทรัพย์ ในการคำนวณความแปรปรวนร่วมของผลตอบแทน คุณต้องใช้สูตรต่อไปนี้:

ที่ไหน คิ- ผลตอบแทนของหลักทรัพย์ในช่วง i-th;

ผลตอบแทนที่คาดหวัง (โดยเฉลี่ย) ของการรักษาความปลอดภัย

ฉัน- ความสามารถในการทำกำไรของพอร์ตโฟลิโอในช่วงที่ i-th;

ผลตอบแทนของพอร์ตที่คาดหวัง (โดยเฉลี่ย)

NS- จำนวนการสังเกต

ควรสังเกตว่าตัวส่วนของสูตรถูกแทนที่ ( n-1) ถ้าความแปรปรวนร่วมคำนวณจากกลุ่มตัวอย่างจากประชากรทั่วไปของการสังเกต หากการคำนวณคำนึงถึงประชากรทั่วไปทั้งหมด ตัวส่วนจะถูกแทนที่ NS.

ตัวอย่าง... ตารางแสดงการเปลี่ยนแปลงของผลตอบแทนจากหุ้นของบริษัท A และบริษัท B ตลอดจนการเปลี่ยนแปลงของผลตอบแทนจากพอร์ตหลักทรัพย์

หากต้องการใช้สูตรข้างต้นในการคำนวณความแปรปรวนร่วมของผลตอบแทนสำหรับแต่ละหุ้นในพอร์ต คุณต้องคำนวณผลตอบแทนเฉลี่ย ซึ่งจะได้ดังนี้

• สำหรับหุ้นของบริษัท A 4.986%;
• สำหรับหุ้นของบริษัท B 5.031%;
• สำหรับพอร์ตโฟลิโอ 3.201%

ดังนั้น ความแปรปรวนร่วมของหุ้นของบริษัท A ที่มีพอร์ตการลงทุนจะเท่ากับ -0.313 และความแปรปรวนร่วมของหุ้นของบริษัท B เท่ากับ 0.242

โคฟ (k A, k p) = ((5,93-4,986)(2,27-3,201) + (5,85-4,986)(2,39-3,201) + (5,21-4,986)(3,47-3,201) + (5,37-4,986)(3,21-3,201) + (4,99-4,986)(2,95-3,201) + (4,87-4,986)(2,97-3,201) + (4,70-4,986)(3,32-3,201) + (4,75-4,986)(3,65-3,201) + (4,33-4,986)(3,97-3,201) + (3,86-4,986)(3,81-3,201))/(10-1) = -0,313

Cov (k B, k p) = ((4,25-5,031)(2,27-3,201) + (4,47-5,031)(2,39-3,201) + (4,68-5,031)(3,47-3,201) + (4,71-5,031)(3,21-3,201) + (4,77-5,031)(2,95-3,201) + (5,25-5,031)(2,97-3,201) + (5,45-5,031)(3,32-3,201) + (5,33-5,031)(3,65-3,201) + (5,55-5,031)(3,97-3,201) + (5,85-5,031)(3,81-3,201))/(10-1) = 0,242

การคำนวณที่คล้ายกันสามารถทำได้ใน Microsoft Excel โดยใช้ฟังก์ชัน COVARIATION.B สำหรับตัวอย่างจากประชากรทั่วไปหรือฟังก์ชัน COVARIATION.G สำหรับประชากรทั่วไปทั้งหมด

การตีความความแปรปรวนร่วม

ค่าของสัมประสิทธิ์ความแปรปรวนร่วมอาจเป็นค่าลบหรือค่าบวกก็ได้ ค่าลบบ่งชี้ว่าผลตอบแทนของหลักทรัพย์และผลตอบแทนของพอร์ตโฟลิโอแสดงการเคลื่อนไหวหลายทิศทาง กล่าวอีกนัยหนึ่งหากผลตอบแทนจากหลักทรัพย์เพิ่มขึ้น ผลตอบแทนในพอร์ตจะลดลงและในทางกลับกัน ค่าบวกบ่งชี้ว่าผลตอบแทนของหลักทรัพย์และพอร์ตโฟลิโอกำลังเปลี่ยนแปลงไปในทิศทางเดียวกัน

ค่าสัมประสิทธิ์ความแปรปรวนร่วมมีค่าต่ำ (ใกล้ 0) สังเกตได้เมื่อความผันผวนของผลตอบแทนของหลักทรัพย์และผลตอบแทนของพอร์ตเป็นแบบสุ่ม

ลองพิจารณาเทคนิคในการคำนวณความแปรปรวนร่วมและความสัมพันธ์ของผลตอบแทนหลักทรัพย์โดยใช้ตัวอย่าง

ผลผลิตบนกระดาษ X เป็นเวลาห้าปีคือ 20%, 25%, 22%, 28%, 24% ตามลำดับ ผลผลิตบนกระดาษ F: 24%, 28%, 25%, 27%, 23%. กำหนดความแปรปรวนร่วมของผลตอบแทนหลักทรัพย์

ให้เรานำเสนอวิธีแก้ปัญหาในสองวิธี

ก) เราพิมพ์ตามลำดับเวลาในเซลล์ด้วย Al no A5 ค่าของผลผลิตของกระดาษ X และในเซลล์ B1 ถึง B5 - ผลผลิตของกระดาษ F เราได้คำตอบในเซลล์ C1 ดังนั้นเราจึงวางเคอร์เซอร์ บนมันและคลิกเมาส์ เราพิมพ์สูตรในเซลล์ C1:

และกดปุ่ม Enter ในเซลล์ C1 วิธีแก้ปัญหาปรากฏขึ้น - รูปที่ 3.08 เช่น ความแปรปรวนร่วมตัวอย่างสำหรับตัวอย่างของเรา

b) สามารถคำนวณความแปรปรวนร่วมได้โดยใช้โปรแกรม "Function Wizard" ในการดำเนินการนี้ ให้วางเมาส์เหนือไอคอน A บนแถบเครื่องมือแล้วคลิก หน้าต่าง "ตัวช่วยสร้างฟังก์ชัน" จะปรากฏขึ้น ในฟิลด์ด้านซ้าย ("หมวดหมู่") ให้เลื่อนเคอร์เซอร์ไปที่บรรทัด "สถิติ" แล้วคลิก เส้นถูกเน้นเป็นสีน้ำเงิน และรายการฟังก์ชันทางสถิติปรากฏขึ้นในช่องด้านขวาของหน้าต่าง ("ฟังก์ชัน") วางเคอร์เซอร์เหนือบรรทัด "KOVAR" แล้วคลิกปุ่มซ้ายของเมาส์ เส้นถูกเน้นด้วยสีน้ำเงิน วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ หน้าต่าง "KOVAR" ปรากฏขึ้น มีสองบรรทัดในหน้าต่าง ชื่อ "Array 1" และ "Array 2" ในบรรทัดแรก เราป้อนตัวเลขของเซลล์ตั้งแต่ A1 ถึง A5 เมื่อต้องการทำสิ่งนี้ ให้วางเคอร์เซอร์ไว้เหนือเครื่องหมาย 3 ซึ่งอยู่ด้วย ด้านขวาบรรทัดแรกแล้วคลิกเมาส์ หน้าต่าง "KOVAR" กลายเป็นฟิลด์ของบรรทัดแรก วางเคอร์เซอร์เหนือเซลล์ A1 กดปุ่มซ้ายของเมาส์ค้างไว้ เลื่อนเคอร์เซอร์ไปที่เซลล์ A5 แล้วปล่อยคีย์ รายการ A1: A5 ปรากฏในฟิลด์บรรทัด วางเคอร์เซอร์เหนือ ??? และคลิกเมาส์ หน้าต่างขยาย "KOVAR" ปรากฏขึ้น เราป้อนจำนวนเซลล์จาก Bl ถึง B5 ในบรรทัดที่สอง ในการดำเนินการนี้ ให้เลื่อนเคอร์เซอร์ไปที่อักขระ 5J ในบรรทัดที่สองแล้วคลิกเมาส์ วางเคอร์เซอร์เหนือเซลล์ B1 กดปุ่มซ้ายของเมาส์ค้างไว้ เลื่อนเคอร์เซอร์ไปที่เซลล์ B5 ปล่อยคีย์ รายการ B1: B5 ปรากฏในฟิลด์บรรทัด วางเมาส์เหนือปุ่ม 3 | และคลิกเมาส์ หน้าต่างขยาย "KOVAR" ปรากฏขึ้น วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ หมายเลข 3.08 ปรากฏในเซลล์ C1

หาค่าสัมประสิทธิ์สหสัมพันธ์ของผลตอบแทนหลักทรัพย์ตามเงื่อนไขตัวอย่างที่ 1 คำตอบ ให้เรานำเสนอวิธีแก้ปัญหาในสองวิธี

ก) เราพิมพ์ตามลำดับเวลาในเซลล์ด้วย Al no A5 ค่าของผลผลิตของกระดาษ X และในเซลล์ B1 ถึง B5 - ผลผลิตของกระดาษ F เราได้วิธีแก้ปัญหาในเซลล์ C1 ดังนั้นเราจึงวางเคอร์เซอร์ บนมันและคลิกเมาส์ เราพิมพ์สูตรในเซลล์ C1:

และกดปุ่ม Enter ในเซลล์ C1 วิธีแก้ปัญหาปรากฏขึ้น - หมายเลข 0.612114

b) สามารถคำนวณความสัมพันธ์โดยใช้โปรแกรม "Function Wizard" ในการดำเนินการนี้ ให้เลือกไอคอน "l" บนแถบเครื่องมือแล้วคลิกเมาส์ หน้าต่าง "ตัวช่วยสร้างฟังก์ชัน" จะปรากฏขึ้น ในฟิลด์ด้านซ้าย ("หมวดหมู่") เลือกบรรทัด "สถิติ" พร้อมเคอร์เซอร์แล้วคลิก รายการฟังก์ชันทางสถิติปรากฏในช่องด้านขวาของหน้าต่าง ("Function") เลือกบรรทัด "CORREL" ด้วยเคอร์เซอร์แล้วคลิกเมาส์ เส้นถูกเน้นด้วยสีน้ำเงิน วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ หน้าต่าง "CORREL" จะปรากฏขึ้น มีสองบรรทัดในหน้าต่างที่เรียกว่า "Array 1" และ "Array 2" ในบรรทัดแรก เราป้อนตัวเลขของเซลล์ด้วย Al no A5 ในการดำเนินการนี้ ให้เลื่อนเคอร์เซอร์ไปที่เครื่องหมาย ZR ทางด้านขวาของบรรทัดแรกแล้วคลิกเมาส์ หน้าต่าง "CORREL" กลายเป็นฟิลด์ของบรรทัดแรก วางเคอร์เซอร์เหนือเซลล์ A1 กดปุ่มซ้ายของเมาส์ค้างไว้ เลื่อนเคอร์เซอร์ไปที่เซลล์ A5 แล้วปล่อยคีย์ รายการ A1: A5 ปรากฏในฟิลด์บรรทัด วางเคอร์เซอร์เหนือสัญลักษณ์ U อีกครั้งแล้วคลิกเมาส์ หน้าต่างขยาย "CORREL" ปรากฏขึ้น เราป้อนจำนวนเซลล์จาก Bl ถึง B5 ในบรรทัดที่สอง เมื่อต้องการทำเช่นนี้ ให้เลื่อนเคอร์เซอร์ไปที่เครื่องหมาย W ในบรรทัดที่สองแล้วคลิกเมาส์ วางเคอร์เซอร์เหนือเซลล์ B1 กดปุ่มซ้ายของเมาส์ค้างไว้ เลื่อนเคอร์เซอร์ไปที่เซลล์ B5 ปล่อยคีย์ รายการ B1: B5 ปรากฏในฟิลด์บรรทัด วางเคอร์เซอร์เหนือปุ่ม Sh แล้วคลิก หน้าต่างขยาย "CORREL" ปรากฏขึ้น วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ หมายเลข 0.612114 ปรากฏในเซลล์ C1

ในตัวอย่างที่ 1 และ 2 เราคำนวณความแปรปรวนร่วมและความสัมพันธ์ของผลตอบแทนของหลักทรัพย์ทั้งสองในพอร์ต หากพอร์ตโฟลิโอมีหลักทรัพย์จำนวนมาก ความแปรปรวนร่วมและความสัมพันธ์ของผลตอบแทนสามารถคำนวณเป็นคู่ตามที่อธิบายไว้ข้างต้น แต่วิธีนี้เป็นวิธีที่ใช้เวลานานในการแก้ปัญหา Excel มีแพ็คเกจพิเศษ "การวิเคราะห์ข้อมูล" ซึ่งช่วยให้คุณแก้ปัญหานี้ได้อย่างรวดเร็วสำหรับเอกสารจำนวนมาก ลองพิจารณาการคำนวณความแปรปรวนร่วมและสหสัมพันธ์โดยใช้มัน

คุณรู้หรือไม่ว่า:โบรกเกอร์ฟอเร็กซ์ "NPBFX" นำธุรกรรมทั้งหมดของลูกค้าไปยังผู้ให้บริการสภาพคล่อง (ในตลาดระหว่างธนาคาร) ดำเนินการต่อไป เทคโนโลยี STP / NDD(การประมวลผลโดยตรง - การประมวลผลธุรกรรมแบบ end-to-end / Non Dealing Desk)

ไม่สามารถติดตั้ง Analysis Pack จากนั้นจะต้องติดตั้ง ในการดำเนินการนี้ ให้วางเคอร์เซอร์เหนือเมนู "บริการ" แล้วคลิกปุ่มซ้ายของเมาส์ เมนูแบบเลื่อนลงปรากฏขึ้น ด้วยเคอร์เซอร์ ให้เลือกคำสั่ง "Add-Ins" แล้วคลิกปุ่มซ้ายของเมาส์ กล่องโต้ตอบ Add-In จะปรากฏขึ้น วางเคอร์เซอร์เหนือหน้าต่างทางด้านซ้ายของบรรทัด "ชุดการวิเคราะห์" แล้วคลิกปุ่มซ้ายของเมาส์ เครื่องหมายถูก (เครื่องหมายถูก) ปรากฏขึ้นในหน้าต่าง วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ ติดตั้ง "แพ็คเกจการวิเคราะห์" แล้ว ลองพิจารณาคำจำกัดความของความแปรปรวนร่วมและความสัมพันธ์ของหลักทรัพย์หลายตัวโดยใช้ตัวอย่าง

ตัวอย่างที่ 3 การคำนวณความแปรปรวนร่วม

มีตัวอย่างข้อมูลผลตอบแทนของหลักทรัพย์ B, C และ D เป็นระยะเวลา 10 งวด เราพิมพ์ค่าผลตอบแทนสำหรับกระดาษ B ลงในเซลล์ตั้งแต่ B1 ถึง B10 กระดาษ C จาก C1 ถึง CU และกระดาษ D จาก D1 ถึง D10 ดังแสดงในรูปที่ 1.8. วางเคอร์เซอร์เหนือเมนู "บริการ" แล้วคลิกปุ่มซ้ายของเมาส์ เมนูแบบเลื่อนลงปรากฏขึ้น วางเคอร์เซอร์เหนือบรรทัด "การวิเคราะห์ข้อมูล" แล้วคลิกปุ่มซ้ายของเมาส์ หน้าต่างการวิเคราะห์ข้อมูลจะปรากฏขึ้น วางเคอร์เซอร์เหนือเส้น "ความแปรปรวนร่วม" แล้วคลิกปุ่มซ้ายของเมาส์ เส้นถูกเน้นด้วยสีน้ำเงิน วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ หน้าต่าง "ความแปรปรวนร่วม" ปรากฏขึ้น (ดูรูปที่ 1.10)

วางเคอร์เซอร์เหนือเครื่องหมาย 3 ทางด้านขวาของฟิลด์บรรทัด "ช่วงป้อนข้อมูล" แล้วคลิกเมาส์ หน้าต่างความแปรปรวนร่วมยุบลงในช่องแถว วางเคอร์เซอร์เหนือเซลล์ B1 กดปุ่มซ้ายของเมาส์ค้างไว้ แล้วลากไปที่เซลล์ D10 รายการ $ B $ 1: $ D $ 10 ปรากฏในบรรทัด วางเคอร์เซอร์เหนือป้ายอีกครั้งแล้วคลิก หน้าต่าง "ความแปรปรวนร่วม" ที่ขยายออกจะปรากฏขึ้น เราจัดกลุ่มข้อมูลตามคอลัมน์ ดังนั้น หากไม่มีจุดในหน้าต่างกลมทางด้านซ้ายของป้ายกำกับ "ตามคอลัมน์" ให้เลื่อนเคอร์เซอร์ไปไว้เหนือหน้าต่างนั้นแล้วคลิกปุ่มซ้ายของเมาส์ จุดปรากฏขึ้นในหน้าต่าง ด้านล่างเป็นบรรทัด "ช่วงเอาต์พุต" ควรมีจุดในหน้าต่างกลมทางด้านซ้ายของป้ายกำกับ หากไม่มี ให้เลื่อนเคอร์เซอร์ไปที่บรรทัดนี้แล้วคลิกปุ่มซ้ายของเมาส์ จุดปรากฏขึ้นในหน้าต่าง วางเคอร์เซอร์เหนือเครื่องหมาย 3 ทางด้านขวาของฟิลด์บรรทัด "ช่วงเอาต์พุต" แล้วคลิกเมาส์ หน้าต่างความแปรปรวนร่วมได้กลายเป็นฟิลด์สตริง ใช้เซลล์ A12 เป็นจุดเริ่มต้นของช่วงเอาต์พุต ดังนั้นเราจึงเลื่อนเคอร์เซอร์ไปที่มันแล้วกดปุ่มซ้ายของเมาส์ รายการ $ A $ 12 ปรากฏในฟิลด์บรรทัด วางเคอร์เซอร์เหนือเครื่องหมาย 3 อีกครั้งแล้วคลิกเมาส์ หน้าต่างความแปรปรวนร่วมถูกขยาย วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ วิธีแก้ไขปัญหาปรากฏบนแผ่นงานดังแสดงในรูปที่ 1.11. บล็อกจาก B13 ถึง D15 แสดงเมทริกซ์ความแปรปรวนร่วม บนเส้นทแยงมุมนั่นคือ ในเซลล์ B13, C14 และ B15 คือความแปรปรวนของหลักทรัพย์ B, C และ D ตามลำดับในเซลล์ที่เหลือ - ความแปรปรวนร่วมของผลตอบแทนของหลักทรัพย์: ในเซลล์ B14 ความแปรปรวนร่วมของผลตอบแทนของหลักทรัพย์ B และ C ใน B15 - หลักทรัพย์ B และ D ใน C15 - หลักทรัพย์ C และ D ...

ตัวอย่างที่ 4 การคำนวณสหสัมพันธ์

มีตัวอย่างข้อมูลผลตอบแทนของหลักทรัพย์ 3 ตัว ได้แก่ B, C และ D เป็นระยะเวลา 10 งวด ในงานที่ 3 เราพิมพ์ค่าผลตอบแทนสำหรับกระดาษ B ลงในเซลล์ตั้งแต่ B1 ถึง B10 กระดาษ C จาก C1 ถึง C10 และกระดาษ D จาก D1 ถึง D10 (รูปที่ 1.9) วางเคอร์เซอร์เหนือเมนู "บริการ" แล้วคลิกปุ่มซ้ายของเมาส์ เมนูแบบเลื่อนลงปรากฏขึ้น วางเคอร์เซอร์เหนือบรรทัด "การวิเคราะห์ข้อมูล" แล้วคลิกปุ่มซ้ายของเมาส์ หน้าต่างการวิเคราะห์ข้อมูลจะปรากฏขึ้น วางเคอร์เซอร์เหนือบรรทัด "สหสัมพันธ์" แล้วคลิกปุ่มซ้ายของเมาส์ เส้นถูกเน้นด้วยสีน้ำเงิน วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ หน้าต่างสหสัมพันธ์ปรากฏขึ้น (คล้ายกับโครงสร้างในหน้าต่าง "ความแปรปรวนร่วม") วางเคอร์เซอร์เหนือเครื่องหมาย 3 ทางด้านขวาของฟิลด์บรรทัด "ช่วงป้อนข้อมูล" แล้วคลิกเมาส์ หน้าต่างสหสัมพันธ์ได้ยุบลงในช่องแถว วางเคอร์เซอร์เหนือเซลล์ B1 กดปุ่มซ้ายของเมาส์ และในขณะที่กดค้างไว้ ให้เลื่อนเคอร์เซอร์ไปที่เซลล์ D10 รายการ $ B $ 1: $ D $ 10 ปรากฏในบรรทัด วางเคอร์เซอร์เหนือป้ายอีกครั้งแล้วคลิก หน้าต่าง "สหสัมพันธ์" ที่ขยายจะปรากฏขึ้น เราจัดกลุ่มข้อมูลตามคอลัมน์ ดังนั้น หากไม่มีจุดในหน้าต่างกลมทางด้านซ้ายของป้ายกำกับ "ตามคอลัมน์" ให้เลื่อนเคอร์เซอร์ไปไว้เหนือหน้าต่างนั้นแล้วคลิกปุ่มซ้ายของเมาส์ จุดปรากฏขึ้นในหน้าต่าง ด้านล่างเป็นบรรทัด "ช่วงเอาต์พุต" ควรมีจุดในหน้าต่างกลมทางด้านซ้ายของป้ายกำกับ หากไม่มี ให้เลื่อนเคอร์เซอร์ไปที่บรรทัดนี้แล้วคลิกปุ่มซ้ายของเมาส์ จุดปรากฏขึ้นในหน้าต่าง วางเคอร์เซอร์เหนือเครื่องหมาย 3 ทางด้านขวาของฟิลด์บรรทัด "ช่วงเอาต์พุต" แล้วคลิกเมาส์ หน้าต่างความสัมพันธ์ได้กลายเป็นกล่องเส้น ใช้เซลล์ A12 เป็นจุดเริ่มต้นของช่วงเอาต์พุต ดังนั้นเราจึงเลื่อนเคอร์เซอร์ไปที่มันแล้วกดปุ่มซ้ายของเมาส์ รายการ $ A $ 12 ปรากฏในฟิลด์บรรทัด วางเคอร์เซอร์เหนือเครื่องหมาย 3 อีกครั้งแล้วคลิกเมาส์ หน้าต่างความสัมพันธ์จะขยายออก วางเคอร์เซอร์เหนือปุ่ม OK แล้วคลิกเมาส์ วิธีแก้ไขปัญหาปรากฏบนแผ่นงานดังแสดงในรูปที่ 1.12 ในบล็อกจาก B13 ถึง D15 มีการนำเสนอเมทริกซ์สหสัมพันธ์ บนเส้นทแยงมุมนั่นคือ มีหน่วยในเซลล์ B13, C14 และ D15 ในเซลล์ที่เหลือมีความสัมพันธ์ของผลตอบแทนของหลักทรัพย์: ในเซลล์ B14 ความสัมพันธ์ของผลตอบแทนของหลักทรัพย์ B และ C ใน B15 - หลักทรัพย์ B และ D ใน C15 - หลักทรัพย์ C และ D.