Hướng Dẫn Chi Tiết Cách Tính Hệ Số Tương Quan (correlation Coefficient) Trong Excel

Có thể bạn quan tâm: Cách Chỉnh Ngay Trên Excel: Hướng Dẫn Chi Tiết Từ A Đến Z Để Làm Việc Hiệu Quả

1. Giới thiệu chung về hệ số tương quan

Hệ số tương quan (correlation coefficient) là một chỉ số thống kê quan trọng giúp chúng ta đo lường mức độ và hướng của mối quan hệ tuyến tính giữa hai biến số. Giá trị của hệ số này nằm trong khoảng từ -1 đến +1, trong đó:

• +1: Hai biến có mối quan hệ tuyến tính hoàn hảo và cùng chiều (khi một biến tăng, biến kia cũng tăng theo cùng tỉ lệ).
• -1: Hai biến có mối quan hệ tuyến tính hoàn hảo nhưng ngược chiều (khi một biến tăng, biến kia giảm).
• 0: Không có mối quan hệ tuyến tính giữa hai biến.

Việc tính toán hệ số tương quan thường được thực hiện trong các lĩnh vực như tài chính (đánh giá mối quan hệ giữa các cổ phiếu), kinh tế (phân tích mối quan hệ giữa thu nhập và tiêu dùng), y tế (đánh giá mối liên quan giữa các chỉ số sinh học), và nhiều ngành khoa học xã hội, tự nhiên khác.

Microsoft Excel là một công cụ mạnh mẽ, dễ sử dụng và phổ biến để thực hiện các phân tích thống kê, bao gồm cả việc tính hệ số tương quan. Bài viết này sẽ hướng dẫn chi tiết cách tính hệ số tương quan trong Excel qua nhiều phương pháp khác nhau, giải thích lý thuyết nền tảng, và cung cấp các ví dụ thực tế.

Có thể bạn quan tâm: Bỏ Đường Line (lưới) Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Kiến thức nền tảng: Công thức tính hệ số tương quan Pearson

Trong thống kê, hệ số tương quan Pearson (Pearson correlation coefficient) là loại phổ biến nhất. Công thức của nó như sau:

r = \frac{\sum_{i=1}^{n} (X_i – \bar{X})(Y_i – \bar{Y})}{\sqrt{\sum_{i=1}^{n} (X_i – \bar{X})^2 \cdot \sum_{i=1}^{n} (Y_i – \bar{Y})^2}}

Trong đó:

• ( X_i, Y_i ) là các giá trị quan sát của hai biến X và Y.
• ( \bar{X}, \bar{Y} ) là giá trị trung bình của X và Y.
• ( n ) là số quan sát (số cặp dữ liệu).

Công thức này có thể được viết lại dưới dạng:

r = \frac{n\sum XY – (\sum X)(\sum Y)}{\sqrt{n\sum X^2 – (\sum X)^2n\sum Y^2 – (\sum Y)^2}}

Cả hai dạng đều cho cùng một kết quả và có thể được triển khai trong Excel bằng các hàm tích hợp sẵn.

Có thể bạn quan tâm: Bỏ Scroll Lock Trong Excel: Hướng Dẫn Chi Tiết, Nguyên Nhân Gây Ra Và Cách Khắc Phục Nhanh Chóng

3. Chuẩn bị dữ liệu trong Excel

Trước khi tính toán, chúng ta cần sắp xếp dữ liệu một cách hợp lý:

• Cột A chứa dữ liệu của biến X.
• Cột B chứa dữ liệu của biến Y.
• Dòng 1 là tiêu đề (header) để dễ nhận diện.

Đảm bảo rằng không có ô trống hoặc giá trị không phải số trong dải dữ liệu, vì chúng có thể gây lỗi khi tính toán.

4. Phương pháp 1: Sử dụng hàm CORREL (đơn giản nhất)

Excel cung cấp hàm CORREL(array1, array2) để trả về hệ số tương quan Pearson giữa hai mảng dữ liệu.

Bước thực hiện

1. Chọn ô nơi bạn muốn hiển thị kết quả (ví dụ: D2).
2. Nhập công thức:

=CORREL(A2:A7, B2:B7)

1. Nhấn Enter. Kết quả sẽ là một số thập phân, ví dụ 0,987 (tùy vào dữ liệu).

Lưu ý

• A2:A7 và B2:B7 là dải ô chứa dữ liệu của X và Y. Nếu dữ liệu của bạn có số lượng quan sát khác, hãy điều chỉnh dải ô cho phù hợp.
• Nếu dải ô có độ dài khác nhau, Excel sẽ trả về lỗi #N/A.

5. Phương pháp 2: Sử dụng hàm PEARSON

Hàm PEARSON trong Excel thực chất là một alias (bí danh) cho hàm CORREL, nhưng một số người dùng vẫn thích dùng nó vì tên gọi dễ nhớ hơn với “Pearson”.

=PEARSON(A2:A7, B2:B7)

Kết quả sẽ giống hoàn toàn với hàm CORREL.

6. Phương pháp 3: Tính thủ công bằng công thức mảng (Array Formula)

Nếu bạn muốn hiểu sâu hơn và không phụ thuộc vào hàm tích hợp, có thể tính hệ số tương quan bằng công thức mảng dựa trên công thức Pearson đã nêu ở mục 2.

Bước thực hiện

1. Tính trung bình của X và Y:

2. Ô C2: =AVERAGE(A2:A7) → trả về (\bar{X})

3. Ô D2: =AVERAGE(B2:B7) → trả về (\bar{Y})

4. Tạo các cột phụ trợ để tính các thành phần cần thiết:

5. Cột E (X – (\bar{X})): =A2-$C$2 kéo xuống tới E7.

6. Cột F (Y – (\bar{Y})): =B2-$D$2 kéo xuống tới F7.
7. Cột G ((X- (\bar{X}))(Y- (\bar{Y}))): =E2F2 kéo xuống tới G7.
8. Cột H ((X- (\bar{X}))^2): =E2^2 kéo xuống tới H7.

9. Cột I ((Y- (\bar{Y}))^2): =F2^2 kéo xuống tới I7.

10. Tính tổng các cột G, H, I:

11. Ô G8: =SUM(G2:G7) → (\sum (X_i-\bar{X})(Y_i-\bar{Y}))

12. Ô H8: =SUM(H2:H7) → (\sum (X_i-\bar{X})^2)

13. Ô I8: =SUM(I2:I7) → (\sum (Y_i-\bar{Y})^2)

14. Áp dụng công thức Pearson:

15. Ô J2 (kết quả cuối cùng): =G8 / SQRT(H8I8)

Kết quả trong J2 chính là hệ số tương quan Pearson.

Ưu điểm của phương pháp này

• Hiểu sâu: Bạn sẽ thấy rõ các thành phần của công thức.
• Kiểm tra lỗi: Nếu có sai lệch, bạn có thể kiểm tra từng bước.
• Linh hoạt: Dễ dàng mở rộng để tính các thống kê phụ trợ khác (ví dụ: hệ số hồi quy).

7. Phương pháp 4: Sử dụng Data Analysis Toolpak

Excel cung cấp một add‑in mạnh mẽ tên là Data Analysis Toolpak. Với công cụ này, bạn có thể thực hiện phân tích hồi quy, thống kê mô tả, và tính hệ số tương quan một cách nhanh chóng.

Kích hoạt Data Analysis Toolpak

1. Vào File → Options → Add‑Ins.
2. Ở mục Manage, chọn Excel Add-ins → Go.
3. Đánh dấu Analysis ToolPak và nhấn OK.

Thực hiện tính hệ số tương quan

1. Chọn tab Data → Data Analysis.
2. Chọn Correlation → OK.
3. Input Range: Chọn dải ô chứa dữ liệu, bao gồm cả tiêu đề (ví dụ: $A$1:$B$7).
4. Grouped By: Chọn Columns (vì dữ liệu được sắp xếp theo cột).
5. Labels in first row: Đánh dấu nếu bạn đã bao gồm tiêu đề.
6. Output Range: Chọn ô nơi bạn muốn hiển thị ma trận tương quan (ví dụ: $D$1).
7. Nhấn OK.

Excel sẽ xuất ra một ma trận tương quan:

• Các giá trị trên đường chéo luôn bằng 1 (tương quan của một biến với chính nó).
• Các giá trị ngoài đường chéo là hệ số tương quan giữa các cặp biến.

Khi nào dùng Data Analysis Toolpak?

• Khi bạn có nhiều biến (hơn 2) và muốn tính ma trận tương quan cho toàn bộ bộ dữ liệu.
• Khi bạn muốn lưu lại kết quả dưới dạng bảng (để báo cáo hoặc phân tích tiếp theo).

8. Kiểm tra tính hợp lệ và ý nghĩa của hệ số tương quan

8.1. Kiểm tra giả thuyết không (null hypothesis)

Trong thực tiễn, chúng ta thường muốn biết hệ số tương quan có khác không một cách thống kê hay không. Excel không cung cấp hàm trực tiếp cho kiểm định này, nhưng bạn có thể tính t‑value và p‑value bằng công thức:

Có thể bạn quan tâm: Cài Đặt Excel Miễn Phí: Hướng Dẫn Chi Tiết Từ A Đến Z

t = r \sqrt{\frac{n-2}{1-r^2}}

Sau đó, sử dụng hàm T.DIST.2T để lấy p‑value:

= T.DIST.2T(ABS(t), n-2)

Nếu p‑value < mức ý nghĩa (thường là 0,05), chúng ta bác bỏ giả thuyết không và kết luận rằng hệ số tương quan là có ý nghĩa.

8.2. Giới hạn của hệ số tương quan Pearson

• Mối quan hệ phi tuyến: Pearson chỉ đo mối quan hệ tuyến tính. Nếu dữ liệu có quan hệ phi tuyến (ví dụ: parabol, logarit), giá trị r có thể gần 0 dù thực tế có mối quan hệ mạnh.
• Ảnh hưởng của ngoại lệ (outliers): Các điểm dữ liệu ngoại lệ có thể làm lệch giá trị r đáng kể.
• Giả định phân phối chuẩn: Đối với các mẫu lớn, giả định này có thể bỏ qua, nhưng với mẫu nhỏ, việc không thỏa mãn phân phối chuẩn có thể làm giảm độ tin cậy.

Nếu dữ liệu không thỏa mãn các giả định trên, bạn có thể cân nhắc dùng hệ số tương quan Spearman (xếp hạng) hoặc Kendall tau.

9. Tính hệ số tương quan Spearman trong Excel

Spearman đo độ tương quan dựa trên thứ hạng thay vì giá trị gốc, vì vậy nó không yêu cầu dữ liệu có dạng tuyến tính hay phân phối chuẩn.

Các bước thực hiện

1. Xếp hạng dữ liệu X và Y:
2. Ô C2: =RANK.AVG(A2, $A$2:$A$7, 1) (đánh số tăng dần)
3. Ô D2: =RANK.AVG(B2, $B$2:$B$7, 1)

4. Kéo công thức xuống tới C7 và D7.

5. Sử dụng hàm CORREL trên các cột thứ hạng:
   =CORREL(C2:C7, D2:D7)

Kết quả là hệ số tương quan Spearman (giá trị trong khoảng -1 đến 1). Nếu dữ liệu không tuyến tính nhưng vẫn có xu hướng tăng/giảm, hệ số Spearman sẽ phản ánh tốt hơn.

10. Ứng dụng thực tế: Phân tích mối quan hệ giữa Giá cổ phiếu và Khối lượng giao dịch

Giả sử bạn là nhà phân tích tài chính và muốn biết liệu khối lượng giao dịch (Volume) có ảnh hưởng đến giá đóng cửa (Close Price) của một cổ phiếu trong 30 ngày gần nhất.

Bước 1: Thu thập dữ liệu

Bạn nhập dữ liệu vào Excel, đặt Close Price ở cột B và Volume ở cột C.

Bước 2: Tính hệ số tương quan Pearson

=CORREL(B2:B31, C2:C31)

Giả sử kết quả là 0,68. Điều này cho thấy mối quan hệ tương đối mạnh và dương: Khi khối lượng giao dịch tăng, giá đóng cửa có xu hướng tăng.

Bước 3: Kiểm định ý nghĩa thống kê

• n = 30
• r = 0,68

Tính t‑value:

=0.68SQRT((30-2)/(1-0.68^2))

Giá trị t ≈ 4,12.

Tính p‑value:

= T.DIST.2T(ABS(4.12), 28)

Giá trị p ≈ 0,0003 (< 0,05) → có ý nghĩa thống kê.

Bước 4: Đánh giá ngoại lệ

Vẽ scatter plot (biểu đồ phân tán) giữa Close Price và Volume để xem có điểm nào nằm xa đường xu hướng không. Nếu có, cân nhắc loại bỏ hoặc phân tích riêng.

11. Tạo biểu đồ Scatter Plot với đường xu hướng (Trendline)

Biểu đồ phân tán giúp trực quan hoá mối quan hệ và xác nhận tính tuyến tính.

1. Chọn dữ liệu Close Price (cột B) và Volume (cột C).
2. Vào Insert → Scatter (X Y) → Scatter with markers.
3. Khi biểu đồ xuất hiện, click phải vào các điểm dữ liệu → Add Trendline.

4. Chọn Linear và tick Display Equation on chart và Display R‑squared value on chart.

5. Equation: y = a + bx (trong đó b là hệ số góc, tương đương với độ dốc của mối quan hệ).

6. R‑squared: Bằng (r^2). Nếu (r = 0,68), thì (R^2 ≈ 0,46), nghĩa là khoảng 46% biến động của giá đóng cửa có thể giải thích bằng khối lượng giao dịch.

12. Tự động hoá tính toán cho nhiều cặp biến bằng công thức mảng

Nếu bạn có một bảng dữ liệu với nhiều biến (ví dụ: A, B, C, D) và muốn tính ma trận tương quan cho tất cả các cặp, bạn có thể dùng công thức mảng MMULT và TRANSPOSE. Dưới đây là một cách tiếp cận:

Giả sử dữ liệu nằm trong dải A2:D101 (4 biến, 100 quan sát).

Bước 1: Chuẩn hoá dữ liệu (z‑score)

1. Tính trung bình và độ lệch chuẩn cho mỗi cột:
2. Trung bình: =AVERAGE(A2:A101) (ô A102), sao cho A103, A104, A105 cho các cột B, C, D.

3. Độ lệch chuẩn: =STDEV.P(A2:A101) (ô A106), tương tự cho các cột khác.

4. Chuẩn hoá:

5. Ô A108 (đầu tiên của ma trận chuẩn hoá): =(A2-$A$102)/$A$106
6. Kéo công thức sang phải và xuống tới D207 (đảm bảo mỗi biến được chuẩn hoá).

Bước 2: Tính ma trận tương quan bằng công thức mảng

Trong một ô trống (ví dụ: F2), nhập công thức mảng:

=MMULT(TRANSPOSE(A108:D207), A108:D207) / (ROWS(A108:D207)-1)

Sau khi nhập, nhấn Ctrl+Shift+Enter (đối với Excel phiên bản trước 365) để biến công thức thành mảng.

Kết quả sẽ là một ma trận 4×4 chứa hệ số tương quan giữa các biến A, B, C, D.

13. Những lỗi thường gặp và cách khắc phục

14. Tổng kết

• Hệ số tương quan Pearson là công cụ cơ bản nhưng mạnh mẽ để đo lường mối quan hệ tuyến tính giữa hai biến.
• Excel cung cấp nhiều cách để tính hệ số này: hàm CORREL/PEARSON, Data Analysis Toolpak, công thức mảng thủ công, và thậm chí tính Spearman cho dữ liệu phi tuyến.
• Việc kiểm định ý nghĩa thống kê (t‑value, p‑value) là bước quan trọng để xác nhận tính hợp lệ của kết quả.
• Khi làm việc với nhiều biến, ma trận tương quan và công thức mảng giúp tiết kiệm thời gian và giảm lỗi.
• Luôn kiểm tra ngoại lệ và đánh giá giả định để tránh đưa ra kết luận sai lầm.

Bằng cách nắm vững các phương pháp trên, bạn có thể tự tin thực hiện các phân tích tương quan trong Excel cho mọi lĩnh vực: tài chính, kinh doanh, y tế, khoa học xã hội, và hơn thế nữa. Hãy áp dụng ngay vào dự án của mình và khám phá những mối liên kết tiềm ẩn trong dữ liệu!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.