Bài 277: Tính Hệ Số Tương Quan Trong Excel ? Cách Tính Hệ Số Tương Quan Pearson Trong Excel

QUYẾT ĐỊNH:

1. Tính hệ số tương quan.

Hệ số tương quan là một chỉ báo về ảnh hưởng xác suất lẫn nhau của hai biến ngẫu nhiên. Hệ số tương quan R có thể lấy giá trị từ -1 trước +1. Nếu giá trị tuyệt đối gần với 1, thì đây là bằng chứng kết nối mạnh mẽ giữa các giá trị và nếu gần hơn 0- sau đó, nó chỉ ra một kết nối yếu hoặc không có nó. Nếu giá trị tuyệt đối R bằng một, thì chúng ta có thể nói về mối quan hệ hàm số giữa các đại lượng, nghĩa là, một đại lượng này có thể được biểu diễn dưới dạng đại lượng khác bằng cách sử dụng một hàm toán học.

Trong thực tế, công thức (1.4) thường được sử dụng hơn để tính hệ số tương quan, vì nó đòi hỏi ít tính toán hơn. Tuy nhiên, nếu hiệp phương sai đã được tính toán trước đó cov (X, Y), thì việc sử dụng công thức (1.1) sẽ có lợi hơn vì ngoài giá trị thực của hiệp phương sai, bạn cũng có thể sử dụng kết quả của các phép tính trung gian.

1.1 Tính hệ số tương quan theo công thức (1.4) Vì vậy, chúng tôi tính các giá trị x k 2, y k 2 và x k y k và nhập chúng vào bảng 1.

Bảng 1

1.2.1. x k

x 1 + x 2 + ... + x 26 = 25.20000 + 26.40000 + ... + 25.80000 = 669.500000

1.2.2.

669.50000 / 26 = 25.75000

M x = 25,750000

1.3. Tương tự, ta tính M y.

1.3.1. Hãy thêm tất cả các phần tử theo thứ tự y k

y 1 + y 2 +… + y 26 = 30.80000 + 29.40000 +… + 30.80000 = 793.000000

1.3.2. Chia tổng kết quả cho số phần tử mẫu

793.00000 / 26 = 30.50000

M y = 30.500000

1.4. Tương tự, ta tính M xy.

1.4.1. Chúng tôi thêm tuần tự tất cả các phần tử của cột thứ 6 của bảng 1

776.16000 + 776.16000 + ... + 794.64000 = 20412.830000

1.4.2. Chia tổng kết quả cho số phần tử

20412.83000 / 26 = 785.10885

M xy = 785.108846

1.5. Tính giá trị của S x 2 bằng công thức (1.6.).

1.5.1. Chúng tôi thêm tuần tự tất cả các phần tử của cột thứ 4 của bảng 1

635.04000 + 696.96000 + ... + 665.64000 = 17256.910000

1.5.2. Chia tổng kết quả cho số phần tử

17256.91000 / 26 = 663.72731

1.5.3. Trừ số cuối cùng bình phương của giá trị M x ta được giá trị S x 2

S x 2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481

1.6. Tính giá trị của S y 2 theo công thức (1.6.).

1.6.1. Chúng tôi thêm tuần tự tất cả các phần tử của cột thứ 5 của bảng 1

948.64000 + 864.36000 + ... + 948.64000 = 24191.840000

1.6.2. Chia tổng kết quả cho số phần tử

24191.84000 / 26 = 930.45538

1.6.3. Trừ số cuối cùng bình phương của M y, ta được giá trị của S y 2

S y 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538

1.7. Hãy tính tích của S x 2 và S y 2.

S x 2 S y 2 = 0,66481 0,20538 = 0,136541

1.8. Trích số cuối cùng Căn bậc hai, chúng ta nhận được giá trị S x S y.

S x S y = 0,36951

1.9. Tính giá trị của hệ số tương quan theo công thức (1.4.).

R = (785.10885 - 25,75000 30.50000) / 0,36951 = (785.10885 - 785.37500) / 0,36951 = -0,72028

TRẢ LỜI: Rx, y = -0,720279

2. Chúng tôi kiểm tra ý nghĩa của hệ số tương quan (chúng tôi kiểm tra giả thuyết phụ thuộc).

Vì ước lượng của hệ số tương quan được tính toán trên một mẫu hữu hạn, và do đó có thể sai lệch so với giá trị chung của nó, nên cần phải kiểm tra ý nghĩa của hệ số tương quan. Việc kiểm tra được thực hiện bằng cách sử dụng tiêu chí t:

Giá trị ngẫu nhiên t theo phân phối t của Student và theo bảng phân phối t, cần tìm giá trị tới hạn của tiêu chí (t cr.α) ở mức ý nghĩa cho trước α. Nếu modulo t được tính theo công thức (2.1) nhỏ hơn t cr.α, thì sự phụ thuộc giữa biến ngẫu nhiên X và Y không. Nếu không, dữ liệu thực nghiệm không mâu thuẫn với giả thuyết về sự phụ thuộc của các biến ngẫu nhiên.

Giá trị mong muốn t kr.α nằm ở giao điểm của hàng ứng với số bậc tự do và cột ứng với mức ý nghĩa α cho trước. Trong trường hợp của chúng ta, số bậc tự do là n - 2 = 26 - 2 = 24 và α = 0.05, tương ứng với giá trị tới hạn của tiêu chí t cr.α = 2.064(xem bảng 2)

ban 2 phân phối t

Giá trị tuyệt đối tiêu chí t không nhỏ hơn t = 5,08680, tcr.α = 2,064, do đó dữ liệu thực nghiệm, với xác suất 0,95(1 - α), không mâu thuẫn với giả thuyết vào sự phụ thuộc của các biến ngẫu nhiên X và Y.

3. Chúng tôi tính toán các hệ số của phương trình hồi quy tuyến tính.

Phương trình hồi quy tuyến tính là một phương trình của một đường thẳng mô tả gần đúng (mô tả gần đúng) mối quan hệ giữa các biến ngẫu nhiên X và Y. Nếu chúng ta giả sử rằng X là tự do và Y phụ thuộc vào X, thì phương trình hồi quy sẽ được viết như sau

Hệ số tính theo công thức (3.2) bđược gọi là hệ số hồi quy tuyến tính. Trong một số nguồn một triệu tập hệ số không đổi hồi quy và b theo các biến.

Sai số dự đoán Y cho một giá trị nhất định X được tính theo công thức:

Giá trị σ y / x (công thức 3.4) còn được gọi là độ lệch chuẩn còn lại, nó đặc trưng cho việc Y ra khỏi đường hồi quy được mô tả bởi phương trình (3.1) tại một giá trị cố định (cho trước) của X.

3.3 Tính hệ số b theo công thức (3.2)

b = -0.72028 0.55582 = -0.40035

3.4 Tính hệ số a theo công thức (3.3)

3.5 Ước lượng sai số của phương trình hồi quy.

3.5.1 Chúng tôi trích xuất căn bậc hai từ S y 2 và nhận được:

δy / x = (0,31437 / 30.50000) 100% = 1,03073%

4. Chúng ta xây dựng biểu đồ phân tán (trường tương quan) và đồ thị của đường hồi quy.

4.1. Ta tìm được phần tử nhỏ nhất và lớn nhất của mẫu X lần lượt là phần tử thứ 18 và 15, x min = 22.10000 và x max = 26.60000.

4.2. Ta thấy phần tử nhỏ nhất và lớn nhất của mẫu Y lần lượt là phần tử thứ 2 và thứ 18, y min = 29.40000 và y max = 31.60000.

4.3. Trên trục abscissa, chúng tôi chọn điểm bắt đầu ngay bên trái của điểm x 18 = 22.10000, và tỷ lệ sao cho điểm x 15 = 26.60000 nằm trên trục và các điểm khác được phân biệt rõ ràng.

4.4. Trên trục y, chúng tôi chọn điểm bắt đầu ngay bên trái của điểm y 2 = 29.40000, và tỷ lệ sao cho điểm y 18 = 31.60000 nằm trên trục và các điểm khác được phân biệt rõ ràng.

4.5. Trên trục abscissa, chúng ta đặt các giá trị x k và trên trục tọa độ, chúng ta đặt các giá trị y k.

4.6. Chúng tôi đặt các điểm (x 1, y 1), (x 2, y 2), ..., (x 26, y 26) trên mặt phẳng tọa độ. Chúng tôi nhận được một biểu đồ phân tán (trường tương quan), được hiển thị trong hình bên dưới.

4.7. Hãy vẽ một đường hồi quy.

Để làm điều này, chúng tôi tìm thấy hai các điểm khác nhau với tọa độ (x r1, y r1) và (x r2, y r2) thỏa mãn phương trình (3.6), ta đặt chúng trên mặt phẳng tọa độ và vẽ một đường thẳng qua chúng. Hãy lấy x min = 22.10000 là abscissa của điểm đầu tiên. Thay giá trị của x min vào phương trình (3.6), ta được hoành độ của điểm đầu tiên. Như vậy, chúng ta có một điểm có tọa độ (22.10000, 31.96127). Tương tự, chúng ta thu được tọa độ của điểm thứ hai, đặt giá trị x max = 26.60000 làm abscissa. Điểm thứ hai sẽ là: (26.60000, 30.15970).

Đường hồi quy được hiển thị trong hình bên dưới với màu đỏ

Xin lưu ý rằng đường hồi quy luôn đi qua điểm của các giá trị trung bình của X và Y, tức là với tọa độ (M x, M y).

CÔNG TÁC LAO ĐỘNG

PHÂN TÍCH LỖI TRONGEXCEL

1.1 Phân tích tương quan trong MS Excel

Phân tích tương quan bao gồm việc xác định mức độ kết nối giữa hai biến ngẫu nhiên X và Y. Hệ số tương quan được sử dụng làm thước đo cho mối liên hệ đó. Hệ số tương quan được ước tính từ mẫu khối lượng n của các cặp quan sát liên quan (x i, y i) từ tổng thể chung X và Y. Để đánh giá mức độ quan hệ giữa X và Y được đo bằng thang định lượng, chúng tôi sử dụng hệ số tương quan tuyến tính(Hệ số Pearson), giả sử rằng các mẫu X và Y được phân phối theo luật chuẩn.

Hệ số tương quan thay đổi từ -1 (quan hệ tuyến tính nghịch đảo chặt chẽ) đến 1 (quan hệ tỷ lệ thuận chặt chẽ). Ở giá trị 0, không có mối quan hệ tuyến tính giữa hai mẫu.

Phân loại chung các mối tương quan (theo Ivanter E.V., Korosov A.V., 1992):

Có một số loại hệ số tương quan, tùy thuộc vào các biến X và Y, có thể được đo lường trên các thang đo khác nhau. Chính thực tế này quyết định việc lựa chọn hệ số tương quan thích hợp (xem Bảng 13):

Trong MS Excel, một hàm đặc biệt được sử dụng để tính toán các hệ số tương quan tuyến tính được ghép nối CORREL (array1; array2),

Ví dụ 1: 10 học sinh đã được làm các bài kiểm tra về tư duy bằng lời nói và hình ảnh. Thời gian trung bình để giải quyết các nhiệm vụ thử nghiệm được tính bằng giây. Nhà nghiên cứu quan tâm đến câu hỏi: có mối quan hệ giữa thời gian giải quyết các vấn đề này không? Biến X biểu thị thời gian trung bình để giải các bài kiểm tra trực quan và biến Y biểu thị thời gian trung bình để giải các bài kiểm tra bằng lời nói.

R

Trong hộp thoại Trình hướng dẫn chức năng xuất hiện, hãy chọn một danh mục Thống kê và chức năng CORREL, sau đó bấm OK. Sử dụng con trỏ chuột để nhập phạm vi dữ liệu mẫu X vào trường array1 (A1: A10). Trong trường array2, nhập dải dữ liệu mẫu Y (B1: B10). Bấm OK. Trong ô C1, giá trị của hệ số tương quan sẽ xuất hiện - 0,54119. Tiếp theo, bạn cần nhìn vào số tuyệt đối của hệ số tương quan và xác định loại mối quan hệ (gần, yếu, trung bình, v.v.)

Cơm. 1. Kết quả tính toán hệ số tương quan

Do đó, mối liên hệ giữa thời gian giải quyết các nhiệm vụ trực quan và lời nói của bài kiểm tra vẫn chưa được chứng minh.

Bài tập 1. Dữ liệu có sẵn cho 20 cơ sở nông nghiệp. Để tìm Hệ số tương quan giữa năng suất cây trồng có hạt với chất lượng đất và đánh giá ý nghĩa của nó. Dữ liệu được đưa ra trong bảng.

Bảng 2. Sự phụ thuộc của năng suất cây lương thực vào chất lượng đất

Nhiệm vụ 2. Xác định xem có mối quan hệ nào giữa thời gian hoạt động của máy tập thể dục thể thao (nghìn giờ) và chi phí sửa chữa (nghìn rúp) hay không:

1.2 Tương quan nhiều trong MS Excel

Tại số lượng lớn quan sát, khi các hệ số tương quan cần được tính toán tuần tự cho một số mẫu, để thuận tiện, các hệ số kết quả được tóm tắt trong bảng được gọi là ma trận tương quan.

Ma trận tương quan là một bảng vuông trong đó tại giao điểm của các hàng và cột tương ứng là hệ số tương quan giữa các tham số tương ứng.

Trong MS Excel, quy trình được sử dụng để tính toán ma trận tương quan tương quan từ gói Phân tích dữ liệu. Quy trình này giúp có được một ma trận tương quan chứa các hệ số tương quan giữa các tham số khác nhau.

Để thực hiện quy trình, bạn phải:

1. chạy lệnh Dịch vụ- Phân tíchdữ liệu;

2. trong danh sách xuất hiện Công cụ phân tích chọn dòng Tương quan và nhấn nút ĐƯỢC RỒI;

3. Trong hộp thoại xuất hiện, chỉ định khoảng thời gian đầu vào tức là nhập một liên kết đến các ô chứa dữ liệu được phân tích. Khoảng đầu vào phải chứa ít nhất hai cột.

4. phần nhóm lạiđặt công tắc theo dữ liệu đã nhập (theo cột hoặc theo hàng);

5. chỉ ra ngày nghỉkhoảng thời gian tức là, nhập một tham chiếu đến ô, bắt đầu từ đó kết quả phân tích sẽ được hiển thị. Kích thước của phạm vi đầu ra sẽ được xác định tự động và một thông báo sẽ được hiển thị trên màn hình nếu phạm vi đầu ra có thể trùng lặp với dữ liệu nguồn. Nút ấn ĐƯỢC RỒI.

Một ma trận tương quan sẽ được hiển thị trong phạm vi đầu ra, trong đó tại giao điểm của mỗi hàng và cột có hệ số tương quan giữa các tham số tương ứng. Các ô trong phạm vi đầu ra có cùng tọa độ hàng và cột chứa giá trị 1 vì mỗi cột trong phạm vi đầu vào hoàn toàn tương quan với chính nó

Ví dụ 2 Dữ liệu hàng tháng có sẵn để quan sát điều kiện thời tiết và thăm các viện bảo tàng và công viên (xem Bảng 3). Cần phải xác định xem có mối quan hệ nào giữa tình trạng thời tiết và việc tham dự các viện bảo tàng và công viên hay không.

Bảng 3. Kết quả quan sát

Quyết định. Để thực hiện phân tích tương quan, hãy nhập dữ liệu ban đầu vào phạm vi A1: G3 (Hình 2). Sau đó trên menu Dịch vụ chọn mặt hàng Phân tíchdữ liệu và sau đó thêm dòng Tương quan. Trong hộp thoại xuất hiện, hãy nhập khoảng thời gian đầu vào(A2: C7). Chỉ định rằng dữ liệu được xem xét bởi các cột. Chỉ định phạm vi đầu ra (E1) và nhấn nút ĐƯỢC RỒI.

Trên hình. 33 có thể thấy rằng mối tương quan giữa điều kiện thời tiết và việc tham dự bảo tàng là -0,92, và giữa điều kiện thời tiết và việc đến công viên - 0,97, giữa việc đến công viên và bảo tàng - 0,92.

Do đó, kết quả của việc phân tích, các mối quan hệ phụ thuộc đã được tiết lộ: mối quan hệ tuyến tính nghịch đảo mạnh mẽ giữa việc tham dự bảo tàng và số ngày nắng và mối quan hệ gần như tuyến tính (rất trực tiếp) giữa việc đến công viên và điều kiện thời tiết. Có một mối quan hệ nghịch đảo mạnh mẽ giữa việc tham dự bảo tàng và công viên.

Cơm. 2. Kết quả tính toán ma trận tương quan từ ví dụ 2

Nhiệm vụ 3. 10 nhà quản lý được đánh giá theo phương pháp luận của các chuyên gia đánh giá về đặc điểm tâm lý nhân cách của nhà lãnh đạo. 15 chuyên gia đã đánh giá từng đặc điểm tâm lý theo hệ thống năm điểm (xem Bảng 4). Nhà tâm lý học quan tâm đến câu hỏi mối quan hệ của những đặc điểm này của nhà lãnh đạo với nhau.

Bảng 4. Kết quả nghiên cứu

Với một mối tương quan cùng một giá trị của một thuộc tính tương ứng với các giá trị khác nhau của thuộc tính kia. Ví dụ: có mối tương quan giữa chiều cao và cân nặng, giữa tỷ lệ mắc các khối u ác tính và tuổi tác, v.v.

Có 2 phương pháp tính hệ số tương quan: phương pháp bình phương (Pearson), phương pháp cấp bậc (Spearman).

Chính xác nhất là phương pháp bình phương (Pearson), trong đó hệ số tương quan được xác định theo công thức:, trong đó

r xy là hệ số tương quan giữa chuỗi thống kê X và Y.

d x là độ lệch của mỗi số của chuỗi thống kê X so với giá trị trung bình cộng của nó.

d y là độ lệch của mỗi số của chuỗi thống kê Y so với trung bình cộng của nó.

Tùy thuộc vào độ mạnh của kết nối và hướng của nó, hệ số tương quan có thể nằm trong khoảng từ 0 đến 1 (-1). Hệ số tương quan bằng 0 cho thấy sự thiếu kết nối hoàn toàn. Mức của hệ số tương quan càng gần với 1 hoặc (-1), tương ứng càng lớn, thì trực tiếp hoặc phản hồi được đo bởi nó càng gần. Với hệ số tương quan bằng 1 hoặc (-1), kết nối là hoàn chỉnh, có chức năng.

Sơ đồ ước tính độ mạnh của mối tương quan bằng hệ số tương quan

Để tính toán hệ số tương quan bằng phương pháp bình phương, một bảng gồm 7 cột được biên soạn. Hãy phân tích quá trình tính toán bằng một ví dụ:

XÁC ĐỊNH SỨC MẠNH VÀ BẢN CHẤT CỦA MỐI QUAN HỆ GIỮA

1. Xác định hàm lượng trung bình của iot trong nước (tính bằng mg / l).

2. Xác định tỷ lệ mắc bệnh bướu cổ trung bình theo%.

3. Xác định độ lệch của mỗi V x so với M x, tức là d x.

201–138 = 63; 178–138 = 40, v.v.

4. Tương tự, chúng tôi xác định độ lệch của mỗi V y so với M y, tức là d

0,2–3,8 = -3,6; 0,6–38 = -3,2, v.v.

5. Chúng tôi xác định các sản phẩm của sự sai lệch. Sản phẩm thu được được tổng hợp và thu được.

6. Ta bình phương d x và tổng hợp kết quả, ta được.

7. Tương tự, ta bình phương d y, tổng hợp kết quả, ta được

8. Cuối cùng, chúng tôi thay thế tất cả số tiền nhận được vào công thức:

Để giải quyết vấn đề về độ tin cậy của hệ số tương quan, nó được xác định lỗi trung bình theo công thức:

(Nếu số quan sát nhỏ hơn 30 thì mẫu số là n-1).

Trong ví dụ của chúng tôi

Giá trị của hệ số tương quan được coi là đáng tin cậy nếu nó cao hơn ít nhất 3 lần so với sai số trung bình của nó.

Trong ví dụ của chúng tôi

Do đó, hệ số tương quan không đáng tin cậy, do đó cần phải tăng số lượng quan sát.

Hệ số tương quan có thể được xác định theo một cách hơi kém chính xác hơn, nhưng dễ dàng hơn nhiều, phương pháp xếp hạng (Spearman).

Phương pháp Spearman: P = 1- (6∑d 2 / n- (n 2 -1))

tạo thành hai hàng đối tượng địa lý được so sánh được ghép nối, chỉ định hàng thứ nhất và hàng thứ hai, tương ứng, x và y. Đồng thời, trình bày hàng đầu tiên của thuộc tính theo thứ tự giảm dần hoặc tăng dần và đặt các giá trị số của hàng thứ hai đối diện với các giá trị của hàng đầu tiên mà chúng tương ứng với

giá trị của đối tượng địa lý trong mỗi hàng được so sánh phải được thay thế bằng một số sê-ri (thứ hạng). Xếp hạng, hoặc số, cho biết vị trí của các chỉ số (giá trị) của hàng đầu tiên và hàng thứ hai. Trong đó Giá trị kiểu số của thuộc tính thứ hai, các cấp bậc phải được chỉ định theo cùng thứ tự đã được chấp nhận khi phân phối chúng cho các giá trị của thuộc tính đầu tiên. Với các giá trị giống nhau của thuộc tính trong chuỗi, thứ hạng phải được xác định là số trung bình từ tổng số thứ tự của các giá trị này

xác định sự khác biệt về thứ hạng giữa x và y (d): d = x - y

bình phương chênh lệch thứ hạng kết quả (d 2)

lấy tổng bình phương của hiệu (Σ d 2) và thay các giá trị thu được vào công thức:

Ví dụ: sử dụng phương pháp xếp hạng để thiết lập hướng và độ mạnh của mối quan hệ giữa thời gian phục vụ tính theo năm và tần suất chấn thương, nếu thu được dữ liệu sau:

Cơ sở lý luận của việc lựa chọn phương pháp:để giải quyết vấn đề, chỉ có thể chọn phương pháp tương quan thứ hạng, tại vì hàng đầu tiên của thuộc tính "kinh nghiệm làm việc tính theo năm" có các tùy chọn mở (kinh nghiệm làm việc từ 1 năm đến 7 năm trở lên), không cho phép sử dụng phương pháp chính xác hơn - phương pháp bình phương - để thiết lập mối quan hệ giữa so sánh các đặc điểm.

Quyết định. Trình tự các phép tính được mô tả trong văn bản, kết quả được trình bày trong Bảng. 2.

ban 2

Mỗi hàng của các dấu hiệu được ghép nối được ký hiệu bằng "x" và "y" (cột 1-2).

Giá trị của mỗi dấu hiệu được thay thế bằng số thứ hạng (số thứ tự). Thứ tự phân bố các cấp bậc trong chuỗi "x" như sau: giá trị tối thiểu của thuộc tính (kinh nghiệm lên đến 1 năm) được gán cho số sê-ri "1", các biến thể tiếp theo của cùng một chuỗi thuộc tính, tương ứng , theo thứ tự tăng dần của các số thứ tự thứ 2, 3, 4 và 5 - các cấp bậc (xem cột 3). Một thứ tự tương tự được quan sát khi phân phối cấp bậc cho đối tượng địa lý thứ hai "y" (cột 4). Trong trường hợp có một số biến thể có cùng kích thước (ví dụ: trong nguyên công tiêu chuẩn, đây là 12 và 12 thương tích trên 100 công nhân với kinh nghiệm từ 3-4 năm và 5-6 năm), số sê-ri được chỉ ra bằng số trung bình từ tổng các số sê-ri của chúng. Những dữ liệu này về số trường hợp bị thương (12 trường hợp bị thương) trong bảng xếp hạng sẽ chiếm vị trí 2 và 3, vì vậy số trung bình của chúng là (2 + 3) / 2 = 2,5.) nên phân phối các số xếp hạng giống nhau - "2,5" (cột 4).

Xem thêm:

Xác định sự khác biệt về thứ hạng d = (x - y) - (cột 5)

Bình phương sự khác biệt về thứ hạng (d 2) và nhận tổng bình phương của sự khác biệt về thứ hạng Σ d 2 (cột 6).