วิธีคำนวณค่าสัมประสิทธิ์สหสัมพันธ์แบบคู่ใน excel วิธีการคำนวณค่าสัมประสิทธิ์สหสัมพันธ์เชิงเส้น

27.09.2019 | การศึกษา

ค่าสัมประสิทธิ์สหสัมพันธ์ (หรือค่าสัมประสิทธิ์สหสัมพันธ์เชิงเส้น) จะแสดงเป็น "r" (ในกรณีที่หายาก จะเรียกว่า "ρ") และแสดงลักษณะของความสัมพันธ์เชิงเส้น (นั่นคือ ความสัมพันธ์ที่กำหนดโดยค่าและทิศทางบางอย่าง) ของตัวแปรตั้งแต่สองตัวขึ้นไป . ค่าของสัมประสิทธิ์อยู่ระหว่าง -1 ถึง +1 นั่นคือความสัมพันธ์สามารถเป็นได้ทั้งบวกและลบ หากค่าสัมประสิทธิ์สหสัมพันธ์เป็น -1 แสดงว่ามีความสัมพันธ์เชิงลบที่สมบูรณ์แบบ หากค่าสัมประสิทธิ์สหสัมพันธ์เป็น +1 แสดงว่ามีความสัมพันธ์เชิงบวกที่สมบูรณ์แบบ ในกรณีอื่นๆ มีความสัมพันธ์เชิงบวก ความสัมพันธ์เชิงลบ หรือไม่มีความสัมพันธ์กันระหว่างตัวแปรทั้งสอง ค่าสัมประสิทธิ์สหสัมพันธ์สามารถคำนวณได้ด้วยตนเอง โดยใช้เครื่องคำนวณออนไลน์ฟรี หรือเครื่องคำนวณกราฟที่ดี

ขั้นตอน

การคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ด้วยตนเอง

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

ตัวอย่างเช่น กำหนดค่าสี่คู่ (ตัวเลข) ของตัวแปร "x" และ "y" คุณสามารถสร้างตารางต่อไปนี้:
- x || ย
- 1 || 1
- 2 || 3
- 4 || 5
- 5 || 7

คำนวณค่าเฉลี่ยเลขคณิต "x"ในการทำเช่นนี้ให้รวมค่าทั้งหมดของ "x" แล้วหารผลลัพธ์ด้วยจำนวนค่า
- ในตัวอย่างของเรา เราได้รับสี่ค่าสำหรับตัวแปร "x" ในการคำนวณค่าเฉลี่ยเลขคณิต "x" ให้เพิ่มค่าเหล่านี้แล้วหารผลรวมด้วย 4 การคำนวณจะเขียนดังนี้:
- μ x = (1 + 2 + 4 + 5) / 4 (\displaystyle \mu _(x)=(1+2+4+5)/4)
- μ x = 12 / 4 (\displaystyle \mu _(x)=12/4)
- μ x = 3 (\displaystyle \mu _(x)=3)
ค้นหาค่าเฉลี่ยเลขคณิต "y"ในการทำเช่นนี้ให้ทำตามขั้นตอนเดียวกันนั่นคือเพิ่มค่าทั้งหมดของ "y" แล้วหารผลรวมด้วยจำนวนค่า
- ในตัวอย่างของเรา เราได้รับสี่ค่าสำหรับตัวแปร "y" เพิ่มค่าเหล่านี้แล้วหารผลรวมด้วย 4 การคำนวณจะถูกเขียนดังนี้:
- μ y = (1 + 3 + 5 + 7) / 4 (\displaystyle \mu _(y)=(1+3+5+7)/4)
- μ y = 16 / 4 (\displaystyle \mu _(y)=16/4)
- μ y = 4 (\displaystyle \mu _(y)=4)
คำนวณส่วนเบี่ยงเบนมาตรฐานของ "x"หลังจากคำนวณค่าเฉลี่ยของ "x" และ "y" แล้ว ให้หา ส่วนเบี่ยงเบนมาตรฐานตัวแปรเหล่านี้ ค่าเบี่ยงเบนมาตรฐานคำนวณโดยใช้สูตรต่อไปนี้:
- σ x = 1 n − 1 Σ (x − μ x) 2 (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(n-1))\Sigma (x-\mu _( x))^(2))))
- σ x = 1 4 − 1 ∗ ((1 − 3) 2 + (2 − 3) 2 + (4 − 3) 2 + (5 − 3) 2) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(4-1))*((1-3)^(2)+(2-3)^(2)+(4-3)^(2)+(5-3) ^(2))))
- σ x = 1 3 ∗ (4 + 1 + 1 + 4) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(4+1+1+4)) ))
- σ x = 1 3 ∗ (10) (\displaystyle \sigma _(x)=(\sqrt ((\frac (1)(3))*(10))))
- σ x = 10 3 (\displaystyle \sigma _(x)=(\sqrt (\frac (10)(3))))
- σ x = 1 , 83 (\displaystyle \sigma _(x)=1.83)
คำนวณส่วนเบี่ยงเบนมาตรฐาน "y"ทำตามขั้นตอนในขั้นตอนก่อนหน้า ใช้สูตรเดียวกัน แต่แทนค่า "y" ลงไป
- ในตัวอย่างของเรา การคำนวณจะถูกเขียนดังนี้:
- σ y = 1 4 − 1 ∗ ((1 − 4) 2 + (3 − 4) 2 + (5 − 4) 2 + (7 − 4) 2) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(4-1))*((1-4)^(2)+(3-4)^(2)+(5-4)^(2)+(7-4) ^(2))))
- σ y = 1 3 ∗ (9 + 1 + 1 + 9) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(9+1+1+9)) ))
- σ y = 1 3 ∗ (20) (\displaystyle \sigma _(y)=(\sqrt ((\frac (1)(3))*(20))))
- σ y = 20 3 (\displaystyle \sigma _(y)=(\sqrt (\frac (20)(3))))
- σ y = 2 , 58 (\displaystyle \sigma _(y)=2.58)
จดสูตรพื้นฐานสำหรับคำนวณค่าสัมประสิทธิ์สหสัมพันธ์สูตรนี้ประกอบด้วยค่าเฉลี่ย ส่วนเบี่ยงเบนมาตรฐาน และจำนวน (n) ของจำนวนคู่ของตัวแปรทั้งสอง ค่าสัมประสิทธิ์สหสัมพันธ์แสดงเป็น "r" (ในกรณีที่ไม่ค่อยเกิดขึ้น ให้แสดงเป็น "ρ") บทความนี้ใช้สูตรคำนวณค่าสัมประสิทธิ์สหสัมพันธ์แบบเพียร์สัน
- ที่นี่และในแหล่งอื่นๆ ปริมาณสามารถแสดงได้หลายวิธี ตัวอย่างเช่น บางสูตรมี "ρ" และ "σ" ในขณะที่สูตรอื่นมี "r" และ "s" หนังสือเรียนบางเล่มให้สูตรอื่น แต่เทียบเท่าทางคณิตศาสตร์ของสูตรข้างต้น
คุณได้คำนวณค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานของตัวแปรทั้งสองแล้ว ดังนั้นคุณจึงสามารถใช้สูตรเพื่อคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ได้ จำไว้ว่า "n" คือจำนวนคู่ของค่าของตัวแปรทั้งสอง มีการคำนวณมูลค่าของปริมาณอื่นก่อนหน้านี้
- ในตัวอย่างของเรา การคำนวณจะถูกเขียนดังนี้:
- ρ = (1 n − 1) Σ (x − μ x σ x) ∗ (y − μ y σ y) (\displaystyle \rho =\left((\frac (1)(n-1))\right) \Sigma \left((\frac (x-\mu _(x))(\sigma _(x)))\right)*\left((\frac (y-\mu _(y))(\sigma _(y)))\ขวา))
- ρ = (1 3) ∗ (\displaystyle \rho =\left((\frac (1)(3))\right)*)[ (1 − 3 1 , 83) ∗ (1 − 4 2 , 58) + (2 − 3 1 , 83) ∗ (3 − 4 2 , 58) (\displaystyle \left((\frac (1-3)( 1.83))\right)*\left((\frac (1-4)(2.58))\right)+\left((\frac (2-3)(1.83))\right) *\left((\ frac (3-4)(2,58))\ขวา))
  + (4 − 3 1 , 83) ∗ (5 − 4 2 , 58) + (5 − 3 1 , 83) ∗ (7 − 4 2 , 58) (\displaystyle +\left((\frac (4-3 )(1.83))\right)*\left((\frac (5-4)(2.58))\right)+\left((\frac (5-3)(1.83))\ right)*\left( (\frac (7-4)(2,58))\ขวา))]
- ρ = (1 3) ∗ (6 + 1 + 1 + 6 4 , 721) (\displaystyle \rho =\left((\frac (1)(3))\right)*\left((\frac (6 +1+1+6)(4,721))\ขวา))
- ρ = (1 3) ∗ 2 , 965 (\displaystyle \rho =\left((\frac (1)(3))\right)*2.965)
- ρ = (2 , 965 3) (\displaystyle \rho =\left((\frac (2,965)(3))\right))
- ρ = 0 . 988 (\displaystyle \rho =0.988)
วิเคราะห์ผลลัพธ์ในตัวอย่างของเรา ค่าสัมประสิทธิ์สหสัมพันธ์คือ 0.988 ค่านี้แสดงลักษณะชุดของตัวเลขที่กำหนดในทางใดทางหนึ่ง ให้ความสนใจกับสัญลักษณ์และขนาดของมูลค่า
- เนื่องจากค่าของสัมประสิทธิ์สหสัมพันธ์เป็นค่าบวก จึงมีความสัมพันธ์เชิงบวกระหว่างตัวแปร "x" และ "y" นั่นคือเมื่อค่าของ "x" เพิ่มขึ้น ค่าของ "y" ก็จะเพิ่มขึ้นด้วย
- เนื่องจากค่าของสัมประสิทธิ์สหสัมพันธ์มีค่าใกล้เคียงกับ +1 มาก ค่าของตัวแปร x และ y จึงมีความสัมพันธ์กันสูง ถ้าใส่จุด ระนาบพิกัดพวกเขาจะตั้งอยู่ใกล้กับเส้นตรงบางเส้น
การใช้เครื่องคิดเลขออนไลน์เพื่อคำนวณค่าสัมประสิทธิ์สหสัมพันธ์
1. ค้นหาเครื่องคิดเลขบนอินเทอร์เน็ตเพื่อคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ค่าสัมประสิทธิ์นี้มักจะคำนวณในทางสถิติ หากมีตัวเลขหลายคู่ แทบจะเป็นไปไม่ได้เลยที่จะคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ด้วยตนเอง ดังนั้นจึงมีเครื่องคำนวณออนไลน์สำหรับคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ ในเครื่องมือค้นหา ให้ป้อน "เครื่องคำนวณค่าสัมประสิทธิ์สหสัมพันธ์" (โดยไม่ต้องใส่เครื่องหมายอัญประกาศ)
2. ป้อนข้อมูลอ่านคำแนะนำบนเว็บไซต์เพื่อป้อนข้อมูลให้ถูกต้อง (คู่ของตัวเลข) เป็นสิ่งสำคัญอย่างยิ่งที่จะต้องป้อนคู่ของตัวเลขที่เหมาะสม มิฉะนั้นคุณจะได้รับผลลัพธ์ที่ผิด โปรดทราบว่าเว็บไซต์ต่างๆ มีรูปแบบการป้อนข้อมูลที่แตกต่างกัน
  - ตัวอย่างเช่น ในเว็บไซต์ http://ncalculators.com/statistics/correlation-coefficient-calculator.htm ค่าของตัวแปร "x" และ "y" จะถูกป้อนในเส้นแนวนอนสองเส้น ค่าจะถูกคั่นด้วยเครื่องหมายจุลภาค นั่นคือในตัวอย่างของเรา ค่าของ "x" ถูกป้อนดังนี้: 1,2,4,5 และค่าของ "y" เป็นดังนี้: 1,3,5,7
  - ในเว็บไซต์อื่น http://www.alcula.com/calculators/statistics/correlation-coefficient/ ข้อมูลจะถูกป้อนในแนวตั้ง ในกรณีนี้ อย่าสับสนระหว่างคู่ของตัวเลขที่ตรงกัน
3. คำนวณค่าสัมประสิทธิ์สหสัมพันธ์หลังจากป้อนข้อมูล เพียงคลิกที่ปุ่ม "คำนวณ", "คำนวณ" หรือปุ่มที่คล้ายกันเพื่อรับผลลัพธ์
  
  การใช้เครื่องคิดเลขกราฟ
  1. ป้อนข้อมูลหยิบเครื่องคิดเลขกราฟ เปลี่ยนเป็นโหมดการคำนวณทางสถิติ แล้วเลือกคำสั่งแก้ไข
    - ในเครื่องคิดเลขที่แตกต่างกัน คุณต้องกดแป้นต่างๆ บทความนี้มุ่งเน้นไปที่เครื่องคิดเลข Texas Instruments TI-86
    - หากต้องการเปลี่ยนเป็นโหมดการคำนวณทางสถิติ ให้กด - Stat (เหนือปุ่ม "+") จากนั้นกด F2 - แก้ไข (แก้ไข)
  2. ลบข้อมูลที่บันทึกไว้ก่อนหน้าเครื่องคิดเลขส่วนใหญ่จะเก็บสถิติที่คุณป้อนไว้จนกว่าคุณจะล้างข้อมูลเหล่านั้น เพื่อหลีกเลี่ยงความสับสนระหว่างข้อมูลเก่ากับข้อมูลใหม่ ให้ลบข้อมูลที่เก็บไว้ก่อน
    - ใช้แป้นลูกศรเพื่อเลื่อนเคอร์เซอร์และไฮไลต์หัวข้อ "xStat" จากนั้นกด Clear และ Enter เพื่อล้างค่าทั้งหมดที่ป้อนในคอลัมน์ xStat
    - ใช้ปุ่มลูกศรเพื่อเน้นหัวข้อ "yStat" จากนั้นกด Clear และ Enter เพื่อล้างค่าทั้งหมดที่ป้อนในคอลัมน์ yStat
  3. ป้อนข้อมูลเริ่มต้นใช้ปุ่มลูกศรเพื่อย้ายเคอร์เซอร์ไปที่เซลล์แรกภายใต้หัวข้อ "xStat" ป้อนค่าแรกแล้วกด Enter ที่ด้านล่างของหน้าจอ "xStat (1) = __" จะแสดงขึ้นพร้อมกับค่าที่ป้อนแทนการเว้นวรรค หลังจากที่คุณกด Enter ค่าที่ป้อนจะปรากฏในตาราง และเคอร์เซอร์จะย้ายไปยังบรรทัดถัดไป นี่จะแสดง "xStat(2) = __" ที่ด้านล่างของหน้าจอ
    - ป้อนค่าทั้งหมดของตัวแปร "x"
    - เมื่อคุณป้อนค่าทั้งหมดสำหรับตัวแปร x แล้ว ให้ใช้ปุ่มลูกศรเพื่อนำทางไปยังคอลัมน์ yStat และป้อนค่าสำหรับตัวแปร y
    - หลังจากใส่ตัวเลขครบทุกคู่แล้ว ให้กด Exit เพื่อล้างหน้าจอและออกจากโหมดการรวม
  4. คำนวณค่าสัมประสิทธิ์สหสัมพันธ์มันบอกลักษณะว่าข้อมูลอยู่ใกล้เส้นตรงแค่ไหน เครื่องคำนวณกราฟสามารถกำหนดเส้นตรงที่เหมาะสมและคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ได้อย่างรวดเร็ว
    - คลิก Stat (สถิติ) - Calc (การคำนวณ) บน TI-86 กด - - .
    - เลือกฟังก์ชัน "การถดถอยเชิงเส้น" ( การถดถอยเชิงเส้น). บน TI-86 ให้กด ซึ่งมีข้อความว่า "LinR" บรรทัด "LinR _" จะแสดงบนหน้าจอพร้อมเคอร์เซอร์กะพริบ
    - ตอนนี้ป้อนชื่อของตัวแปรสองตัว: xStat และ yStat
      - ใน TI-86 เปิดรายชื่อ; เมื่อต้องการทำเช่นนี้ให้กด – – .
      - ตัวแปรที่มีอยู่จะแสดงที่บรรทัดล่างสุดของหน้าจอ เลือก (เป็นไปได้มากที่สุดโดยการกด F1 หรือ F2) ป้อนเครื่องหมายจุลภาค จากนั้นเลือก
      - กด Enter เพื่อประมวลผลข้อมูลที่ป้อน
  5. วิเคราะห์ผลลัพธ์เมื่อกด Enter ข้อมูลต่อไปนี้จะแสดงบนหน้าจอ:
    - y = a + b x (\displaystyle y=a+bx): เป็นฟังก์ชันที่อธิบายเส้นตรง โปรดทราบว่าฟังก์ชันไม่ได้เขียนในรูปแบบมาตรฐาน (y = kx + b)
    - a = (\displaystyle a=). นี่คือพิกัด y ของจุดที่เส้นตัดกับแกน y
    - b = (\displaystyle b=). นี่คือความชันของเส้นตรง
    - คอร์ = (\displaystyle (\text(corr))=). นี่คือค่าสัมประสิทธิ์สหสัมพันธ์
    - n = (\displaystyle n=). นี่คือจำนวนคู่ของตัวเลขที่ใช้ในการคำนวณ

สามารถหาลักษณะเชิงปริมาณของความสัมพันธ์ได้โดยการคำนวณค่าสัมประสิทธิ์สหสัมพันธ์

การวิเคราะห์ความสัมพันธ์ใน Excel

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

แผนภูมิความสัมพันธ์ใน excel

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

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

ถ้าค่าสัมประสิทธิ์เป็น 0 แสดงว่าไม่มีความสัมพันธ์ระหว่างค่าต่างๆ หากต้องการค้นหาความสัมพันธ์ระหว่างตัวแปรและ y ให้ใช้ฟังก์ชันในตัว ไมโครซอฟต์ เอ็กเซล"คอเรล". ตัวอย่างเช่น สำหรับ "Array1" ให้เลือกค่า y และสำหรับ "Array2" ให้เลือกค่า x ผลลัพธ์ที่ได้คือค่าสัมประสิทธิ์สหสัมพันธ์ที่โปรแกรมคำนวณ ถัดไป คุณต้องคำนวณความแตกต่างระหว่างแต่ละ x และ xav และ yav ในเซลล์ที่เลือกเขียน สูตร x-x, ย-. อย่าลืมตรึงเซลล์ด้วยค่าเฉลี่ย ผลลัพธ์ที่ได้จะเป็นค่าสัมประสิทธิ์สหสัมพันธ์ที่ต้องการ

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

ทำได้ด้วยตัวเอง: คำนวณความสัมพันธ์ของสกุลเงินโดยใช้ Excel

ตัวอย่างเช่น เราใช้ Microsoft Excel แต่โปรแกรมอื่น ๆ ที่สามารถใช้สูตรความสัมพันธ์จะทำได้ 7. หลังจากนั้น เลือกเซลล์ที่มีข้อมูลเกี่ยวกับ EUR/USD 9.กด Enter เพื่อคำนวณค่าสัมประสิทธิ์สหสัมพันธ์สำหรับ EUR/USD และ USD/JPY การอัปเดตตัวเลขทุกวันไม่คุ้มค่า (เว้นแต่คุณจะหมกมุ่นอยู่กับความสัมพันธ์ของสกุลเงิน)

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

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

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

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

บทความนี้ออกแบบมาสำหรับความรู้ขั้นสูงของ Microsoft Excel หากคุณไม่มีเวลาอ่านบทความทั้งหมด คุณสามารถดาวน์โหลดไฟล์และจัดการกับมันได้ด้วยตัวเอง

หากคุณพบว่าตัวเองต้องทำอะไรแบบนี้อยู่บ่อยๆฉันขอแนะนำให้คุณพิจารณาซื้อหนังสือ การคำนวณทางสถิติใน Excel.

สิ่งสำคัญที่ต้องรู้เกี่ยวกับความสัมพันธ์

ในการคำนวณความสัมพันธ์ที่เชื่อถือได้ จำเป็นต้องมีตัวอย่างที่เชื่อถือได้ ยิ่งมีขนาดใหญ่ ผลลัพธ์ก็จะยิ่งน่าเชื่อถือมากขึ้นเท่านั้น สำหรับจุดประสงค์ของตัวอย่างนี้ ฉันได้สุ่มตัวอย่างอัตราแลกเปลี่ยนรายวันในช่วง 10 ปีที่ผ่านมา ข้อมูลสามารถใช้ได้ฟรี ฉันเอามาจากเว็บไซต์ http://oanda.com

ฉันทำอะไรลงไป

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

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

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

(4) สีแสดงว่าข้อมูลเหล่านี้อ้างอิงถึงปี 2007, 2008, 2009 แน่นอน! ช่วงเวลาที่เศรษฐกิจสูงสุดและถดถอยมักไม่น่าเชื่อถือทางสถิติ ซึ่งเกิดขึ้นในกรณีนี้ ดังนั้นฉันจึงพยายามแยกช่วงเวลาเหล่านี้ออกจากข้อมูล (สำหรับการตรวจสอบ ฉันตรวจสอบระดับความสัมพันธ์ของข้อมูลในช่วงเวลานี้) ระดับความสัมพันธ์ของข้อมูลเหล่านี้เท่านั้นคือ 0.01% นั่นคือไม่มีอยู่ในหลักการ แต่ถ้าไม่มีข้อมูลเหล่านี้ ข้อมูลจะสัมพันธ์กันประมาณ 81% นี่เป็นความสัมพันธ์ที่ค่อนข้างน่าเชื่อถืออยู่แล้ว นี่คือกราฟที่มีฟังก์ชัน

ขั้นตอนถัดไป

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

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

วิธีคำนวณค่าสัมประสิทธิ์สหสัมพันธ์มี 2 วิธี คือ วิธีกำลังสอง (เพียร์สัน) วิธีอันดับ (สเปียร์แมน)

วิธีที่ถูกต้องที่สุดคือวิธีกำลังสอง (เพียร์สัน) ซึ่งค่าสัมประสิทธิ์สหสัมพันธ์ถูกกำหนดโดยสูตร: , โดยที่

r xy คือค่าสัมประสิทธิ์สหสัมพันธ์ระหว่างอนุกรมทางสถิติ X และ Y

d x คือค่าเบี่ยงเบนของตัวเลขแต่ละตัวในอนุกรมสถิติ X จากค่าเฉลี่ยเลขคณิต

d y คือค่าเบี่ยงเบนของตัวเลขแต่ละชุดทางสถิติ Y จากค่าเฉลี่ยเลขคณิต

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

โครงการประเมินความแข็งแกร่งของความสัมพันธ์ด้วยค่าสัมประสิทธิ์สหสัมพันธ์

ความแข็งแรงของการเชื่อมต่อ	ค่าของสัมประสิทธิ์สหสัมพันธ์ ถ้ามี
ความแข็งแรงของการเชื่อมต่อ	การเชื่อมต่อโดยตรง (+)	ข้อเสนอแนะ (-)
ไม่มีการเชื่อมต่อ
การสื่อสารมีขนาดเล็ก (อ่อนแอ)	จาก 0 ถึง +0.29	0 ถึง -0.29
ค่าเฉลี่ยการสื่อสาร (ปานกลาง)	+0.3 ถึง +0.69	-0.3 ถึง -0.69
สื่อสารขนาดใหญ่ (แข็งแรง)	+0.7 ถึง +0.99	-0.7 ถึง -0.99
การสื่อสารเสร็จสมบูรณ์ (การทำงาน)

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

กำหนดความแข็งแกร่งและลักษณะของความสัมพันธ์ระหว่างกัน

ได้เวลา- เนส คอพอก (วี ย )	ง x= วี x –ม x	ง y= วี ย –ม ย	ง x ง ย	ง x 2	ง ย 2







			Σ -1345 ,0	Σ 13996 ,0	Σ 313 , 47

1. กำหนดปริมาณไอโอดีนเฉลี่ยในน้ำ (เป็น mg / l)

มิลลิกรัม/ลิตร

2. กำหนดอุบัติการณ์เฉลี่ยของโรคคอพอกเป็น%

3. กำหนดค่าเบี่ยงเบนของแต่ละ V x จาก M x เช่น ดีเอ็กซ์

201–138=63; 178–138=40 เป็นต้น

4. ในทำนองเดียวกัน เราพิจารณาค่าเบี่ยงเบนของแต่ละ V y จาก M y เช่น ง

0.2–3.8=-3.6; 0.6–38=-3.2 เป็นต้น

5. เรากำหนดผลิตภัณฑ์ของการเบี่ยงเบน ผลิตภัณฑ์ที่ได้จะถูกสรุปและรับ

6. เรายกกำลังสอง dx และสรุปผลลัพธ์ เราได้

7. ในทำนองเดียวกันเรากำลังสองสรุปผลลัพธ์ที่เราได้รับ

8. สุดท้าย เราแทนที่จำนวนเงินทั้งหมดที่ได้รับในสูตร:

เพื่อแก้ไขปัญหาความน่าเชื่อถือของค่าสัมประสิทธิ์สหสัมพันธ์ ข้อผิดพลาดเฉลี่ยตามสูตร:

(หากจำนวนการสังเกตน้อยกว่า 30 ตัวส่วนจะเป็น n-1)

ในตัวอย่างของเรา

ค่าของสัมประสิทธิ์สหสัมพันธ์ถือว่าเชื่อถือได้หากมีค่าสูงกว่าค่าเฉลี่ยข้อผิดพลาดอย่างน้อย 3 เท่า

ในตัวอย่างของเรา

ดังนั้นค่าสัมประสิทธิ์สหสัมพันธ์จึงไม่น่าเชื่อถือ ซึ่งทำให้จำเป็นต้องเพิ่มจำนวนการสังเกต

ค่าสัมประสิทธิ์สหสัมพันธ์สามารถกำหนดได้ด้วยวิธีที่แม่นยำน้อยกว่า แต่ง่ายกว่ามาก นั่นคือวิธีจัดอันดับ (สเปียร์แมน)

วิธีสเปียร์แมน: P=1-(6∑d 2 /n-(n 2 -1))

สร้างคุณสมบัติการเปรียบเทียบที่จับคู่สองแถวโดยกำหนดแถวที่หนึ่งและสองตามลำดับ x และ y ในขณะเดียวกันให้แสดงแถวแรกของแอตทริบิวต์ในลำดับจากมากไปน้อยหรือจากน้อยไปมาก และวางค่าตัวเลขของแถวที่สองตรงข้ามค่าเหล่านั้นของแถวแรกที่ตรงกัน

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

กำหนดความแตกต่างของอันดับระหว่าง x และ y (d): d = x - y

ยกกำลังสองผลต่างอันดับที่ได้ (d 2)

รับผลรวมของกำลังสองของความแตกต่าง (Σ d 2) และแทนที่ค่าที่ได้รับลงในสูตร:

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

เหตุผลในการเลือกวิธีการ:ในการแก้ปัญหาสามารถเลือกได้เฉพาะวิธีการเท่านั้น ความสัมพันธ์อันดับ, เพราะ แถวแรกของแอตทริบิวต์ "ประสบการณ์การทำงานในปี" มีตัวเลือกเปิด (ประสบการณ์การทำงานสูงสุด 1 ปีและ 7 ปีขึ้นไป) ซึ่งไม่อนุญาตให้ใช้วิธีการที่แม่นยำกว่า - วิธีกำลังสอง - เพื่อสร้างความสัมพันธ์ระหว่าง ลักษณะเปรียบเทียบ.

การตัดสินใจ. ลำดับของการคำนวณอธิบายไว้ในข้อความ ผลลัพธ์จะแสดงในตาราง 2.

ตารางที่ 2

ประสบการณ์การทำงานในปี	จำนวนผู้บาดเจ็บ	เลขลำดับ (อันดับ)	ความแตกต่างของอันดับ	ความแตกต่างของอันดับยกกำลังสอง
ประสบการณ์การทำงานในปี	จำนวนผู้บาดเจ็บ		ง(x-y)	ง 2

แต่ละแถวของเครื่องหมายคู่จะแสดงด้วย "x" และ "y" (คอลัมน์ 1-2)

ค่าของสัญญาณแต่ละรายการจะถูกแทนที่ด้วยหมายเลขอันดับ (ซีเรียล) ลำดับของการกระจายอันดับในชุด "x" มีดังนี้: ค่าต่ำสุดของแอตทริบิวต์ (ประสบการณ์สูงสุด 1 ปี) ถูกกำหนดเป็นหมายเลขซีเรียล "1" ตัวแปรที่ตามมาของชุดแอตทริบิวต์เดียวกันตามลำดับ ตามลำดับที่เพิ่มขึ้นของหมายเลขลำดับที่ 2, 3, 4 และ 5 - อันดับ (ดูคอลัมน์ 3) ลำดับที่คล้ายกันนี้สังเกตได้เมื่อกระจายอันดับไปยังคุณลักษณะที่สอง "y" (คอลัมน์ 4) ในกรณีที่มีหลายรุ่นที่มีขนาดเดียวกัน (เช่น ในงานมาตรฐาน มีการบาดเจ็บ 12 และ 12 ครั้งต่อพนักงาน 100 คนที่มีประสบการณ์ 3-4 ปี และ 5-6 ปี) หมายเลขซีเรียลจะถูกระบุ โดยจำนวนเฉลี่ยจากผลรวมของหมายเลขซีเรียล ข้อมูลเหล่านี้เกี่ยวกับจำนวนผู้บาดเจ็บ (การบาดเจ็บ 12 ครั้ง) ในการจัดอันดับควรอยู่ในอันดับที่ 2 และ 3 ดังนั้นจำนวนเฉลี่ยของการบาดเจ็บคือ (2 + 3) / 2 = 2.5 ) ควรกระจายหมายเลขอันดับเดียวกัน - "2.5" (คอลัมน์ 4)

กำหนดความแตกต่างในอันดับ d = (x - y) - (คอลัมน์ 5)

กำลังสองของผลต่างอันดับ (d 2) และรับผลรวมกำลังสองของผลต่างอันดับ Σ d 2 (คอลัมน์ 6)

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

โดยที่ n คือจำนวนคู่ตัวเลือกที่ตรงกันในแถว "x" และแถว "y"

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

การใช้สหสัมพันธ์

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

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

จะทำความสัมพันธ์ใน Excel ได้อย่างไร?

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

เลือกเซลล์ว่างที่จะแสดงผลการคำนวณ
คลิกรายการ "สูตร" ในเมนูหลักของ Excel
ในบรรดาปุ่มที่จัดกลุ่มใน "Function Library" ให้เลือก "Other Functions"
ในรายการแบบหล่นลง เลือกฟังก์ชันการคำนวณความสัมพันธ์ (สถิติ - CORREL)
Excel เปิดแผงอาร์กิวเมนต์ของฟังก์ชัน "Array 1" และ "Array 2" คือช่วงของข้อมูลที่กำลังเปรียบเทียบ หากต้องการกรอกข้อมูลในฟิลด์เหล่านี้โดยอัตโนมัติ คุณสามารถเลือกเซลล์ตารางที่ต้องการได้
คลิก ตกลง เพื่อปิดหน้าต่างอาร์กิวเมนต์ของฟังก์ชัน ค่าสัมประสิทธิ์สหสัมพันธ์ที่คำนวณได้จะปรากฏอยู่ในเซลล์

ความสัมพันธ์อาจเป็นแบบตรง (หากค่าสัมประสิทธิ์มากกว่าศูนย์) และแบบผกผัน (จาก -1 ถึง 0)

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

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

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