การวิเคราะห์สหสัมพันธ์ใน Excel ตัวอย่างการวิเคราะห์สหสัมพันธ์

การวิเคราะห์สหสัมพันธ์เป็นวิธีการวิจัยทั่วไปที่ใช้ในการกำหนดระดับการพึ่งพาค่าที่ 1 ในวันที่ 2 สเปรดชีตมีเครื่องมือพิเศษที่ช่วยให้คุณใช้การวิจัยประเภทนี้ได้

สาระสำคัญของการวิเคราะห์สหสัมพันธ์

จำเป็นต้องกำหนดความสัมพันธ์ระหว่างปริมาณที่แตกต่างกันสองปริมาณ กล่าวอีกนัยหนึ่งก็คือมันแสดงให้เห็นว่าค่าเปลี่ยนแปลงไปในทิศทางใด (เล็กกว่า / ใหญ่กว่า) ขึ้นอยู่กับการเปลี่ยนแปลงในวินาที

วัตถุประสงค์ของการวิเคราะห์สหสัมพันธ์

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

สำคัญ! ที่สัมประสิทธิ์ที่ 0 ไม่มีความสัมพันธ์ระหว่างปริมาณ

การคำนวณค่าสัมประสิทธิ์สหสัมพันธ์

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

วิธีที่ 1: การกำหนดความสัมพันธ์ผ่านตัวช่วยสร้างฟังก์ชัน

CORREL – ฟังก์ชันที่ช่วยให้คุณใช้การวิเคราะห์สหสัมพันธ์ แบบฟอร์มทั่วไป – CORREL(massiv1;massiv2) คำแนะนำโดยละเอียด:

1. จำเป็นต้องเลือกเซลล์ที่วางแผนไว้เพื่อแสดงผลลัพธ์ของการคำนวณ คลิก "แทรกฟังก์ชัน" ที่ด้านซ้ายของช่องข้อความเพื่อป้อนสูตร

2. ตัวช่วยสร้างฟังก์ชันจะเปิดขึ้น ที่นี่คุณต้องพบ คอร์เรล, คลิกที่มัน, จากนั้นบน “ตกลง”.

3. หน้าต่างอาร์กิวเมนต์จะเปิดขึ้น ในบรรทัด "Array1" คุณต้องป้อนพิกัดของช่วงเวลาของค่าที่ 1 ในตัวอย่างนี้ นี่คือคอลัมน์มูลค่าการขาย คุณเพียงแค่ต้องเลือกเซลล์ทั้งหมดที่อยู่ในคอลัมน์นี้ ในทำนองเดียวกัน คุณต้องเพิ่มพิกัดของคอลัมน์ที่สองลงในบรรทัด "Array2" ในตัวอย่างของเรา นี่คือคอลัมน์ต้นทุนการโฆษณา

4. หลังจากป้อนช่วงทั้งหมดแล้วให้คลิกที่ปุ่ม "ตกลง"

ค่าสัมประสิทธิ์ถูกแสดงในเซลล์ที่ระบุในตอนเริ่มต้นของการกระทำของเรา ผลลัพธ์ที่ได้คือ 0,97 ตัวบ่งชี้นี้สะท้อนถึงการพึ่งพาอาศัยกันสูงของค่าแรกในวินาที

วิธีที่ 2: คำนวณสหสัมพันธ์โดยใช้ Analysis ToolPak

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

1. ไปที่ส่วน "ไฟล์"

2. หน้าต่างใหม่จะเปิดขึ้น ซึ่งคุณต้องคลิกที่ส่วน "การตั้งค่า"
3. คลิกที่ "โปรแกรมเสริม"
4. เราพบองค์ประกอบ "การจัดการ" ที่ด้านล่าง ที่นี่คุณต้องเลือก "Excel Add-ins" จากเมนูบริบทและคลิก "OK"

5. หน้าต่างส่วนเสริมพิเศษได้เปิดขึ้น ทำเครื่องหมายถูกข้างองค์ประกอบ "ชุดการวิเคราะห์" เราคลิก "ตกลง"
6. การเปิดใช้งานสำเร็จ ตอนนี้ไปที่ Data บล็อก "การวิเคราะห์" ปรากฏขึ้นซึ่งคุณต้องคลิก "การวิเคราะห์ข้อมูล"
7. ในหน้าต่างใหม่ที่ปรากฏขึ้น เลือกองค์ประกอบ "สหสัมพันธ์" และคลิก "ตกลง"

8. หน้าต่างการตั้งค่าการวิเคราะห์ปรากฏบนหน้าจอ ในบรรทัด "ช่วงอินพุต" จำเป็นต้องป้อนช่วงของคอลัมน์ทั้งหมดที่มีส่วนร่วมในการวิเคราะห์ ในตัวอย่างนี้ นี่คือคอลัมน์ "มูลค่าการขาย" และ "ต้นทุนการโฆษณา" การตั้งค่าการแสดงผลลัพธ์เริ่มต้นเป็น แผ่นงานใหม่ ซึ่งหมายความว่าผลลัพธ์จะแสดงบนแผ่นงานอื่น คุณสามารถเปลี่ยนตำแหน่งผลลัพธ์ของผลลัพธ์ได้ หลังจากตั้งค่าทั้งหมดแล้ว ให้คลิกที่ “ตกลง”

คะแนนสุดท้ายออกมาแล้ว ผลลัพธ์จะเหมือนกับวิธีแรก – 0,97

ความหมายและการคำนวณค่าสัมประสิทธิ์สหสัมพันธ์พหุคูณใน MS Excel

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

คำแนะนำโดยละเอียด:

1. ในส่วน "ข้อมูล" เราพบบล็อก "การวิเคราะห์" ที่รู้จักแล้วและคลิก "การวิเคราะห์ข้อมูล"

2. ในหน้าต่างที่ปรากฏขึ้น ให้คลิกที่องค์ประกอบ "สหสัมพันธ์" และคลิก "ตกลง"
3. ในบรรทัด "ช่วงอินพุต" เราขับรถในช่วงเวลาสามคอลัมน์ขึ้นไปของตารางต้นทาง สามารถป้อนช่วงด้วยตนเองหรือเพียงแค่เลือกด้วย LMB และจะปรากฏในบรรทัดที่ต้องการโดยอัตโนมัติ ใน "การจัดกลุ่ม" เลือกวิธีการจัดกลุ่มที่เหมาะสม ใน "พารามิเตอร์เอาต์พุต" ระบุตำแหน่งที่จะแสดงผลความสัมพันธ์ เราคลิก "ตกลง"

4. พร้อม! สร้างเมทริกซ์สหสัมพันธ์

ค่าสัมประสิทธิ์สหสัมพันธ์คู่ใน Excel

มาดูวิธีการวาดค่าสัมประสิทธิ์สหสัมพันธ์คู่อย่างถูกต้องในสเปรดชีต Excel

การคำนวณค่าสัมประสิทธิ์สหสัมพันธ์คู่ใน Excel

ตัวอย่างเช่น คุณมีค่า x และ y

X เป็นตัวแปรตามและ y เป็นตัวแปรอิสระ จำเป็นต้องค้นหาทิศทางและความแข็งแกร่งของความสัมพันธ์ระหว่างตัวชี้วัดเหล่านี้ คำแนะนำทีละขั้นตอน:

1. หาค่าเฉลี่ยโดยใช้ฟังก์ชัน หัวใจ.

2. มาคำนวณกัน х и xavg, у и เฉลี่ย โดยใช้ตัวดำเนินการ «-»

3. เราคูณผลต่างที่คำนวณได้

4. เราคำนวณผลรวมของตัวบ่งชี้ในคอลัมน์นี้ ตัวเศษคือผลลัพธ์ที่พบ

5. คำนวณตัวส่วนของส่วนต่าง х и x เฉลี่ย y и y-กลาง. ในการทำเช่นนี้ เราจะทำการยกกำลังสอง

6. การใช้ฟังก์ชัน ออโตซัมมาให้ค้นหาตัวบ่งชี้ในคอลัมน์ผลลัพธ์ เราทำการคูณ การใช้ฟังก์ชัน ROOT ยกกำลังสองผลลัพธ์

7. เราคำนวณผลหารโดยใช้ค่าของตัวส่วนและตัวเศษ

8. CORREL เป็นฟังก์ชันแบบบูรณาการที่ช่วยให้คุณป้องกันการคำนวณที่ซับซ้อนได้ ไปที่ "Function Wizard" เลือก CORREL และระบุอาร์เรย์ของ indicators х и у. เราสร้างกราฟที่แสดงค่าที่ได้รับ

เมทริกซ์ของค่าสัมประสิทธิ์สหสัมพันธ์แบบคู่ใน Excel

มาวิเคราะห์วิธีการคำนวณสัมประสิทธิ์ของเมทริกซ์ที่จับคู่กัน ตัวอย่างเช่น มีเมทริกซ์ของตัวแปรสี่ตัว

คำแนะนำทีละขั้นตอน:

1. เราไปที่ "การวิเคราะห์ข้อมูล" ซึ่งอยู่ในบล็อก "การวิเคราะห์" ของแท็บ "ข้อมูล" เลือก สหสัมพันธ์ จากรายการที่ปรากฏ
2. เราตั้งค่าที่จำเป็นทั้งหมด “ช่วงอินพุต” – ช่วงของทั้งสี่คอลัมน์ “ช่วงเอาต์พุต” – ตำแหน่งที่เราต้องการแสดงผลรวม เราคลิกที่ปุ่ม "ตกลง"
3. เมทริกซ์สหสัมพันธ์ถูกสร้างขึ้นในสถานที่ที่เลือก จุดตัดของแถวและคอลัมน์แต่ละอันเป็นค่าสัมประสิทธิ์สหสัมพันธ์ หมายเลข 1 จะปรากฏขึ้นเมื่อพิกัดตรงกัน

ฟังก์ชัน CORREL เพื่อกำหนดความสัมพันธ์และสหสัมพันธ์ใน Excel

CORREL – ฟังก์ชันที่ใช้ในการคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ระหว่าง 2 อาร์เรย์ มาดูตัวอย่างความสามารถทั้งหมดสี่อย่างของฟังก์ชันนี้กัน

ตัวอย่างการใช้ฟังก์ชัน CORREL ใน Excel

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

อัลกอริทึมการคำนวณมีลักษณะดังนี้:

คะแนนที่แสดงอยู่ใกล้ 1 ผลลัพธ์:

การหาค่าสัมประสิทธิ์สหสัมพันธ์ของผลกระทบของการกระทำต่อผลลัพธ์

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

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

• =คอร์เรล(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17)

ผลการศึกษา:

จากผลที่ได้รับ เป็นที่ชัดเจนว่า ระดับการสนับสนุนผู้สมัครรายที่ 1 เพิ่มขึ้นตามแต่ละวันของการส่งเสริมโฆษณา ดังนั้น ค่าสัมประสิทธิ์สหสัมพันธ์จึงเข้าใกล้ 1. เมื่อมีการเปิดตัวโฆษณา ผู้ยื่นคำร้องรายอื่นได้รับความไว้วางใจเป็นจำนวนมาก และสำหรับ 5 วันมีแนวโน้มเป็นบวก จากนั้นระดับความน่าเชื่อถือก็ลดลงและในวันที่สิบห้าก็ลดลงต่ำกว่าตัวบ่งชี้เริ่มต้น คะแนนต่ำแสดงว่าโปรโมชันส่งผลกระทบเชิงลบต่อการสนับสนุน อย่าลืมว่าปัจจัยร่วมอื่นๆ ที่ไม่ได้พิจารณาในรูปแบบตารางอาจส่งผลต่อตัวบ่งชี้ได้เช่นกัน

การวิเคราะห์ความนิยมของเนื้อหาตามความสัมพันธ์ของการดูวิดีโอและการรีโพสต์

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

ตอนนี้ จำเป็นต้องกำหนดความสัมพันธ์ระหว่างตัวบ่งชี้ 2 ตัวตามสูตรด้านล่าง:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Strong direct relationship”;”Strong inverse relationship”);”Weak or no relationship”)' class='formula'>

หากสัมประสิทธิ์ผลลัพธ์สูงกว่า 0,7 แสดงว่าควรใช้ฟังก์ชันการถดถอยเชิงเส้นมากกว่า ในตัวอย่างนี้ เราทำ:

ตอนนี้เรากำลังสร้างกราฟ:

เราใช้สมการนี้เพื่อกำหนดจำนวนการดูที่มีการแชร์ 200, 500 และ 1000 ครั้ง: =9,2937*D4-206,12. เราได้รับผลลัพธ์ดังต่อไปนี้:

ฟังก์ชัน การคาดการณ์ ให้คุณกำหนดจำนวนการดูได้ในขณะนั้น หากมี เช่น รีโพสต์สองร้อยห้าสิบครั้ง เราสมัคร: 0,7;PREDICTION(D7;B3:B8;A3:A8);”ค่าไม่เกี่ยวข้อง”)' class='formula'> เราได้รับผลลัพธ์ดังต่อไปนี้:

คุณสมบัติของการใช้ฟังก์ชัน CORREL ใน Excel

ฟังก์ชันนี้มีคุณสมบัติดังต่อไปนี้:

1. เซลล์ว่างจะไม่ถูกนำมาพิจารณา
2. เซลล์ที่มีข้อมูลประเภทบูลีนและข้อความจะไม่ถูกนำมาพิจารณา
3. การปฏิเสธสองครั้ง “-” ใช้เพื่ออธิบายค่าตรรกะในรูปของตัวเลข
4. จำนวนเซลล์ในอาร์เรย์ที่ศึกษาต้องตรงกัน มิฉะนั้น ระบบจะแสดงข้อความ #N/A

การประเมินนัยสำคัญทางสถิติของสัมประสิทธิ์สหสัมพันธ์

เมื่อทดสอบความสำคัญของสัมประสิทธิ์สหสัมพันธ์ สมมติฐานว่างคือตัวบ่งชี้มีค่าเป็น 0 ในขณะที่ทางเลือกไม่มี สูตรต่อไปนี้ใช้สำหรับการตรวจสอบ:

สรุป

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