Spearman Tương Quan Xếp Hạng Trong Excel: Công Thức Và đồ Thị

Khi thực hiện phân tích tương quan trong Excel, trong hầu hết các trường hợp, bạn sẽ xử lý Tương quan Pearson. Nhưng vì hệ số tương quan Pearson chỉ đo mối quan hệ tuyến tính giữa hai biến, nên nó không hoạt động với tất cả các loại dữ liệu – các biến của bạn có thể được liên kết mạnh theo cách phi tuyến tính và vẫn có hệ số gần bằng không. Trong những trường hợp như vậy, bạn có thể thực hiện tương quan xếp hạng Spearman thay vì Pearson.

Spearman tương quan – những điều cơ bản

Các Spearman tương quan là phiên bản không đối xứng của Hệ số tương quan Pearson đo lường mức độ liên kết giữa hai biến dựa trên cấp bậc của chúng.

Các Thời điểm tương quan sản phẩm Pearson kiểm tra tuyến tính mối quan hệ giữa hai biến liên tục. Tuyến tính có nghĩa là một mối quan hệ khi hai biến thay đổi theo cùng một hướng với tốc độ không đổi.

Spearman Xếp hạng Tương quan đánh giá đơn điệu mối quan hệ giữa các giá trị được xếp hạng. Trong một mối quan hệ đơn điệu, các biến cũng có xu hướng thay đổi cùng nhau, nhưng không nhất thiết phải ở một tốc độ không đổi.

Khi nào nên làm tương quan Spearman

Phân tích tương quan Spearman sẽ được sử dụng trong bất kỳ trường hợp nào sau đây khi các giả định cơ bản của tương quan Pearson không được đáp ứng:

1. Nếu dữ liệu của bạn thể hiện một phi tuyến tính mối quan hệ hoặc không được phân phối bình thường.
2. Nếu ít nhất một biến là thứ tự. Nếu các giá trị của bạn có thể được đặt theo thứ tự “thứ nhất, thứ hai, thứ ba”, bạn đang xử lý dữ liệu thứ tự.
3. Nếu có ý nghĩa ngoại lệ. Không giống như tương quan Pearson, tương quan Spearman không nhạy cảm với các ngoại lệ vì nó thực hiện các phép tính trên các cấp bậc, do đó sự khác biệt giữa các giá trị thực tế không có ý nghĩa.

Ví dụ: bạn có thể sử dụng tương quan Pearson để tìm câu trả lời cho các câu hỏi sau:

• Là những người có trình độ học vấn cao hơn quan tâm đến môi trường?
• Là số lượng các triệu chứng bệnh nhân có liên quan đến việc họ sẵn sàng dùng thuốc?

Hệ số tương quan Spearman

Trong thống kê, Hệ số tương quan Spearman được đại diện bởi một trong hai rS hoặc thư Hy Lạp ρ (“rho”), đó là lý do tại sao nó thường được gọi là Spearman’s rho.

Hệ số tương quan xếp hạng Spearman đo lường cả sức mạnh và hướng của mối quan hệ giữa các cấp bậc dữ liệu. Nó có thể là bất kỳ giá trị nào từ -1 đến 1 và giá trị tuyệt đối của hệ số này càng gần 1, mối quan hệ càng bền chặt:

• 1 là một mối tương quan tích cực hoàn hảo
• -1 là một mối tương quan phủ định hoàn hảo
• 0 là không tương quan

Công thức tương quan xếp hạng Spearman

Tùy thuộc vào việc có hoặc không có mối quan hệ nào trong xếp hạng (cùng thứ hạng được gán cho hai hoặc nhiều quan sát), hệ số tương quan Spearman có thể được tính bằng một trong các công thức sau.

Nếu có không bị trói, một công thức đơn giản hơn sẽ làm:

Ở đâu:

• dTôi là sự khác biệt giữa một cặp cấp bậc
• n là số lượng quan sát

Để đối phó với hàng ngũ gắn, phiên bản đầy đủ của công thức tương quan Spearman phải được sử dụng, đây là phiên bản sửa đổi một chút của Pearson’s r:

Ở đâu:

• R (x) và R (y) là các cấp bậc của x và y biến
• R (x) và R (y) là cấp bậc trung bình

Cách tính tương quan Spearman trong Excel với hàm CORREL

Đáng tiếc, Excel không có chức năng sẵn có để tính hệ số tương quan xếp hạng Spearman. Tuy nhiên, điều đó không có nghĩa là bạn sẽ phải cân não bằng các công thức trên. Bằng cách thao tác với Excel một chút, chúng ta có thể đưa ra một cách đơn giản hơn nhiều để thực hiện tương quan Spearman.

Ví dụ, chúng ta hãy cố gắng tìm hiểu xem hoạt động thể chất của chúng ta có liên quan đến huyết áp không. Trong cột B, chúng tôi có số phút mà 10 người đàn ông cùng tuổi dành hàng ngày trong phòng tập thể dục và ở cột C, chúng tôi có huyết áp tâm thu.

Để tìm hệ số tương quan Spearman trong Excel, hãy thực hiện các bước sau:

1. Xếp hạng dữ liệu của bạnVì tương quan Spearman đánh giá mối liên hệ giữa hai biến dựa trên thứ hạng của chúng, bạn cần xếp hạng dữ liệu nguồn của mình. Điều này có thể được thực hiện nhanh chóng bằng cách sử dụng Hàm Excel RANK.AVG.Để xếp hạng biến đầu tiên (hoạt động thể chất), hãy nhập công thức dưới đây vào D2 và sau đó kéo nó xuống D11:=RANK.AVG(B2,$B$2:$B$11,0)Để xếp hạng biến thứ hai (huyết áp), đặt công thức sau vào ô E2 và sao chép nó xuống cột:=RANK.AVG(C2,$C$2:$C$11,0)

   Để các công thức hoạt động chính xác, vui lòng đảm bảo khóa các phạm vi với tài liệu tham khảo tế bào tuyệt đối.

   Tại thời điểm này, dữ liệu nguồn của bạn sẽ trông giống như thế này:

   

2. Tìm hệ số tương quan Spearman Với các cấp bậc được thiết lập, bây giờ chúng ta có thể sử dụng Hàm CORREL của Excel để có được Spearman’s rho:=CORREL(D2:D11, E2:E11)Công thức trả về hệ số -0,7576 (làm tròn đến 4 chữ số), cho thấy mối tương quan tiêu cực khá mạnh và cho phép chúng tôi kết luận rằng một người càng tập thể dục, huyết áp của họ càng thấp.Hệ số tương quan Pearson cho cùng một mẫu (-0,7445) cho thấy mối tương quan yếu hơn một chút, nhưng vẫn có ý nghĩa thống kê:

Cái hay của phương pháp này là nó nhanh chóng, dễ dàng và hoạt động bất kể có quan hệ trong bảng xếp hạng hay không.

Xem thêm:

1. Sửa lỗi Excel ‘Dữ liệu có thể bị mất’
2. 3 thiết lập Excel cơ bản nhưng quan trọng cho người mới bắt đầu
3. Tạo chuỗi ngày tự động điền trong Excel với hàm Sequence
4. Cách chọn và tô sáng các ô trống trong Excel

Tính hệ số tương quan Spearman trong Excel với công thức truyền thống

Nếu bạn không chắc chắn rằng hàm CORREL đã tính đúng rho của Spearman, bạn có thể xác minh kết quả bằng công thức truyền thống được sử dụng trong thống kê. Đây là cách thực hiện:

1. Tìm sự khác biệt giữa mỗi cặp cấp bậc (d) bằng cách trừ một cấp bậc khác:=D2-E2Công thức này đi đến F2 và sau đó được sao chép xuống cột.
2. Tăng mỗi cấp bậc khác biệt lên sức mạnh của hai (d2): =F2^2Công thức này đi đến cột G.
3. Thêm sự khác biệt bình phương:=SUM(G2:G11)Công thức này có thể đi đến bất kỳ ô trống nào, G12 trong trường hợp của chúng tôi.Từ ảnh chụp màn hình sau, có lẽ bạn sẽ hiểu rõ hơn về cách sắp xếp dữ liệu:
4. Tùy thuộc vào việc tập dữ liệu của bạn có bất kỳ thứ hạng ràng buộc nào hay không, hãy sử dụng một trong các những công thức để tính hệ số tương quan Spearman.

Trong ví dụ của chúng tôi, không có mối quan hệ nào, vì vậy chúng tôi có thể đi với một công thức đơn giản hơn:

Với d2 bằng 290 và n (số lượng quan sát) bằng 10, công thức trải qua các biến đổi sau:

Kết quả là, bạn nhận được -0,757575758, điều này hoàn toàn đồng ý với hệ số tương quan Spearman được tính toán trong ví dụ trước.

Trong Microsoft Excel, các tính toán trên có thể được thực hiện với phương trình sau:

=1-(6*G12/(10*(10^2-1)))

Trong đó G12 là tổng của sự khác biệt thứ hạng bình phương (d2).

Cách thực hiện tương quan Spearman trong Excel bằng biểu đồ

Các hệ số tương quan trong Excel chỉ đo các mối quan hệ tuyến tính (Pearson) hoặc đơn điệu (Spearman). Tuy nhiên, các hiệp hội khác là có thể. Vì vậy, bất kể bạn làm tương quan nào, luôn luôn là một ý tưởng tốt để biểu thị mối quan hệ giữa các biến trong biểu đồ.

Để vẽ biểu đồ tương quan cho dữ liệu được xếp hạng, đây là những gì bạn cần làm:

1. Tính thứ hạng bằng cách sử dụng hàm RANK.AVG như được giải thích trong ví dụ này.
2. Chọn hai cột với các cấp bậc.
3. Chèn một Biểu đồ phân tán XY. Đối với điều này, nhấp vào Tiêu tan đồ thị biểu tượng trên Khởi phát trong tab Trò chuyện nhóm.
4. Thêm một đường xu hướng vào biểu đồ của bạn. Cách nhanh nhất là nhấp vào Các yếu tố biểu đồ nút> Thêm Trendline.
5. Hiển thị giá trị bình phương R trên biểu đồ. Bấm đúp vào đường xu hướng để mở ngăn của nó, chuyển sang Tùy chọn đường xu hướng và chọn tab Hiển thị giá trị bình phương R trên biểu đồ cái hộp.
6. Hiển thị nhiều chữ số hơn trong R2 giá trị cho độ chính xác tốt hơn.

Kết quả là, bạn sẽ có được một đại diện trực quan về mối quan hệ giữa các cấp bậc. Ngoài ra, bạn sẽ nhận được Hệ số xác định (R2), căn bậc hai của nó là hệ số tương quan Pearson (r). Nhưng bởi vì bạn đã vẽ các dữ liệu được xếp hạng, r này của Pearson không gì khác ngoài rho của Spearman.

Ghi chú. R bình phương luôn là một số dương, do đó hệ số tương quan xếp hạng Spearman suy ra cũng sẽ luôn dương. Để thêm một dấu hiệu thích hợp, chỉ cần nhìn vào đường trong biểu đồ tương quan của bạn – độ dốc đi lên biểu thị mối tương quan dương (dấu cộng) và độ dốc xuống biểu thị mối tương quan âm (dấu trừ).

Trong trường hợp của chúng tôi, R2 bằng 0,5739210285. Sử dụng Hàm SQRT để tìm căn bậc hai:

=SQRT(0.5739210285)

Bạn và bạn sẽ nhận được hệ số đã quen thuộc là 0,757575758.

Độ dốc xuống trong biểu đồ thể hiện mối tương quan âm, vì vậy chúng tôi thêm dấu trừ và lấy hệ số tương quan Spearman chính xác là -0,757575758.

Đó là cách bạn có thể tính hệ số tương quan xếp hạng Spearman trong Excel. Để xem kỹ hơn các ví dụ được thảo luận trong hướng dẫn này, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi để Spearman Xếp hạng Tương quan trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Dịch vụ kế toán tại Hà Nội – Ketoanmvb vào tuần tới!