Excel RSQ(): Tìm hệ số xác định trong Excel

Nếu bạn làm việc với đường xu hướng hoặc cần đo mức độ liên hệ chặt chẽ giữa hai tập giá trị, hàm RSQ() trong Excel là một công cụ đơn giản cho tác vụ này. RSQ() cho bạn biết bao nhiêu phần biến thiên trong một phạm vi có thể được giải thích bởi phạm vi khác. Về bản chất, nó trả về giá trị R-squared cho hai chuỗi. Điều này hữu ích trong mọi thứ, từ phân tích bán hàng đến thí nghiệm khoa học.

RSQ() làm gì?

RSQ(), viết tắt của “R-squared”, trả về bình phương của hệ số tương quan Pearson giữa hai mảng, mà trong trường hợp này cũng chính là hệ số xác định. Nói đơn giản, nó cho thấy mức độ mà các giá trị trong một phạm vi dự đoán được các giá trị trong phạm vi kia. Nếu kết quả RSQ() là 1, bạn có mối quan hệ tuyến tính hoàn hảo. Nếu là 0, tức là không có mối quan hệ tuyến tính nào.

Mọi người thường dùng RSQ() khi muốn kiểm tra mức độ một đường thẳng khớp với dữ liệu, như trong phân tích hồi quy hoặc khi vẽ đường xu hướng.

Cú pháp và đối số của RSQ()

Cấu trúc của RSQ() như sau:

=RSQ(known_y’s, known_x’s)Được hỗ trợ bởi

• known_y’s: Đây là các giá trị phụ thuộc (những giá trị bạn đang cố dự đoán hoặc giải thích).

• known_x’s: Đây là các giá trị độc lập (những giá trị bạn cho rằng có thể giải thích biến thiên).

Cả hai phạm vi phải có cùng số điểm dữ liệu. Nếu không, RSQ() sẽ báo lỗi.

Lưu ý quan trọng: Nếu bạn quen với các khái niệm về hồi quy tuyến tính đơn nói riêng và hồi quy tuyến tính bình phương tối thiểu (OLS) nói chung, bạn có thể nhận thấy hàm RSQ() chỉ nhận một tập giá trị x. Đó là vì RSQ() không xử lý hồi quy tuyến tính bội.

Ví dụ về RSQ()

Giả sử bạn muốn biết mức chi cho quảng cáo dự đoán doanh số tốt đến đâu.

• Cột A: Chi tiêu quảng cáo
• Cột B: Doanh số

Giả sử dữ liệu của bạn trông như sau:

Nếu dữ liệu của bạn nằm ở A2:A5 cho quảng cáo và B2:B5 cho doanh số, bạn sẽ nhập:

=RSQ(A2:A5, B2:B5)Được hỗ trợ bởi

Công thức này trả về giá trị R-squared, cho thấy bao nhiêu phần thay đổi trong doanh số có thể được giải thích bởi thay đổi trong chi tiêu quảng cáo.

Diễn giải kết quả RSQ()

• RSQ() = 1: Có mối quan hệ tuyến tính hoàn hảo. Một biến dự đoán biến kia mà không có sai số.
• RSQ() = 0: Không tồn tại mối quan hệ tuyến tính giữa hai tập dữ liệu.
• Giá trị giữa 0 và 1: Càng gần 1, mối quan hệ tuyến tính càng mạnh.

Nếu trong ví dụ trên, mọi số ở cột B đều gấp đôi số tương ứng ở cột A, thì hồi quy sẽ nằm trên một đường thẳng, và việc tăng gấp đôi chi tiêu quảng cáo sẽ luôn khiến doanh số tăng gấp đôi. (Sẽ thật tuyệt nếu luôn như vậy!)

Những sai sót thường gặp

Dưới đây là một vài vấn đề bạn có thể gặp với RSQ():

• Kích thước phạm vi không bằng nhau: Nếu hai phạm vi không có cùng số hàng, RSQ() sẽ trả về lỗi #N/A.

• Ô trống hoặc không phải số: Ô trống hoặc văn bản trong phạm vi sẽ bị bỏ qua, nhưng nếu thiếu quá nhiều, kết quả có thể gây hiểu nhầm hoặc phát sinh lỗi. Đây có thể là lỗi tinh vi, nên hãy cẩn trọng!

• Chỉ một điểm dữ liệu: RSQ() cần ít nhất hai cặp số. Nếu không, bạn sẽ nhận lỗi #DIV/0!. Điều này là do phương sai/hiệp phương sai sẽ không xác định với chỉ một giá trị.

Nếu có gì đó không ổn, hãy kiểm tra lại các phạm vi xem có lỗi gõ hoặc thiếu dữ liệu không.

RSQ() so với CORREL()

Bạn có thể tự hỏi tại sao dùng RSQ() thay vì CORREL(). Khác biệt như sau:

• CORREL() trả về hệ số tương quan Pearson (r), đo lường độ mạnh và chiều hướng của mối quan hệ tuyến tính.

• RSQ() trả về R-squared (R²), nên luôn cho giá trị từ 0 đến 1, tập trung vào tỷ lệ phương sai được giải thích.

Nếu bạn chỉ muốn xem các biến có di chuyển cùng nhau (dương hay âm) hay không, CORREL() cho chi tiết hơn. Nếu bạn đo lường bao nhiêu phần thay đổi của một biến được giải thích bởi biến khác, RSQ() là lựa chọn phù hợp.

Tuy vậy, vì chúng ta đang xử lý hồi quy tuyến tính đơn, ta luôn có thể tìm R-squared chỉ bằng cách bình phương kết quả của CORREL():

=CORREL(A2:A5, B2:B5)^2Được hỗ trợ bởi

RSQ() và biểu đồ phân tán

Nếu bạn từng thêm đường xu hướng vào biểu đồ phân tán trong Excel, bạn có thể thấy giá trị R-squared hiển thị ngay trên biểu đồ. Đó chính là giá trị mà RSQ() trả về với các phạm vi dữ liệu của bạn.

Nếu bạn muốn hiển thị R-squared trên trang tính mà không cần thêm biểu đồ, chỉ cần dùng RSQ() trực tiếp trong một ô. Cách này giúp bạn có một con số dễ thao tác hơn.

Nếu bạn chưa thật sự tự tin về cách tạo trực quan đẹp trong Excel, hãy đăng ký khóa học Data Visualization in Excel của chúng tôi, khóa học bao quát chi tiết mọi nội dung quan trọng.

Mẹo thực hành

Để kết thúc phần hướng dẫn kỹ thuật, dưới đây là một vài mẹo thực tế:

• Gắn nhãn dữ liệu rõ ràng để phân biệt X và Y.

• Luôn kiểm tra lại các phạm vi có cùng kích thước trước khi nhập công thức.

• Nếu bạn muốn so sánh nhiều hơn hai biến, hãy cân nhắc dùng các công cụ hồi quy như Data Analysis Toolpak hoặc hàm LINEST(). LINEST() cũng báo cáo các chỉ số mô hình khác như thống kê F và sai số chuẩn.

Kết luận

Như bạn đã thấy, RSQ() là cách đơn giản nhưng hiệu quả để đo mức độ một tập giá trị dự đoán tập giá trị khác trong Excel. Bạn có thể làm được nhiều việc với nó, như phân tích xu hướng hoặc dự báo kết quả. Đừng chỉ đoán mò.

Bước tiếp theo, hãy tham gia các khóa học Data Analysis in Excel và Advanced Excel Functions để tiếp tục nâng cao kỹ năng Excel (và thống kê) của bạn!