Cách thực hiện hồi quy trong Excel: Hướng dẫn đầy đủ

Để thực hiện hồi quy trong Excel, hãy bật Analysis ToolPak, sau đó đi tới Data > Data Analysis và chọn Regression. Chọn vùng Y và vùng X của bạn, chọn vị trí đầu ra và nhấn OK. Excel sẽ trả về các hệ số, R Square và các số liệu thống kê khác trong một bảng mới.
Hướng dẫn này bao gồm ba cách để chạy hồi quy trong Excel: Analysis ToolPak, hàm LINEST và đường xu hướng (trendline) trên biểu đồ. Bài viết cũng đi sâu vào một ví dụ đầy đủ, giải thích cách đọc kết quả đầu ra và chỉ ra cách nhanh hơn để kiểm tra các mối quan hệ trong các tệp lớn. Các chi tiết được tham khảo từ các trang hỗ trợ của Microsoft, truy cập vào ngày 12 tháng 11 năm 2026.
Hồi quy cho bạn biết điều gì
Hồi quy đo lường mức độ thay đổi của một giá trị khi một giá trị khác thay đổi. Giá trị bạn muốn giải thích là biến phụ thuộc, hoặc Y. Các giá trị mà bạn nghĩ là tác nhân thúc đẩy nó là các biến độc lập, hoặc X.
Microsoft mô tả công cụ Regression như sau. Nó "thực hiện phân tích hồi quy tuyến tính bằng cách sử dụng phương pháp "bình phương tối thiểu" để khớp một đường thẳng qua một tập hợp các quan sát."
Đường thẳng đó có một phương trình đơn giản. Microsoft viết nó dưới dạng y = mx + b, trong đó m là độ dốc và b là điểm giao cắt. Với nhiều hơn một biến X, phương trình trở thành y = m1x1 + m2x2 + ... + b.
Trong thuật ngữ kinh doanh, hồi quy trả lời các câu hỏi như:
- Doanh số tăng bao nhiêu cho mỗi tháng tăng trưởng thêm?
- Chi tiêu quảng cáo liên quan thế nào đến số lượng đăng ký?
- Bao nhiêu phần trăm sự thay đổi về chi phí được giải thích bởi khối lượng đơn hàng?
Ví dụ riêng của Microsoft là về các vận động viên. Nó nói rằng bạn có thể phân tích "hiệu suất của một vận động viên bị ảnh hưởng như thế nào bởi các yếu tố như tuổi tác, chiều cao và cân nặng."
Hồi quy cho thấy sự liên kết, không phải bằng chứng về nguyên nhân. Một đường thẳng mạnh mẽ giữa hai giá trị vẫn có thể đến từ một yếu tố thứ ba mà bạn không đo lường.
Thiết lập dữ liệu của bạn trong Excel
Hồi quy cần các cặp số sạch và khớp nhau. Mỗi hàng nên chứa một quan sát, với Y trong một cột và mỗi X trong cột riêng của nó.
Dưới đây là dữ liệu từ ví dụ hồi quy tuyến tính đơn giản của Microsoft. Nó liệt kê doanh số của sáu tháng, tính bằng đô la.
| Tháng | Doanh số |
|---|---|
| 1 | 3,100 |
| 2 | 4,500 |
| 3 | 4,400 |
| 4 | 5,400 |
| 5 | 7,500 |
| 6 | 8,100 |
Đặt Tháng vào cột A và Doanh số vào cột B, với các tiêu đề ở hàng 1. Các ví dụ bên dưới sử dụng bố cục này. Dữ liệu sau đó nằm ở các hàng từ 2 đến 7, đó là lý do tại sao các công thức bên dưới sử dụng các phạm vi đó.
Một vài quy tắc giúp kết quả đáng tin cậy hơn:
- Không có ô trống. Mỗi hàng cần cả giá trị Y và X.
- Chỉ dùng số. Các giá trị được lưu dưới dạng văn bản sẽ gây ra lỗi.
- Đủ số lượng hàng. Sáu hàng có thể dùng để demo, nhưng phân tích thực tế cần nhiều hơn thế.
- Một hàng cho mỗi quan sát. Không trộn lẫn các hàng theo tháng và theo tuần trong cùng một phạm vi.
Bật Analysis ToolPak
Công cụ Regression nằm trong Analysis ToolPak, một tiện ích bổ sung đi kèm với Excel. Bạn có thể cần phải bật nó lên trước.
Các bước của Microsoft cho Excel trên Windows là:
- Chọn File, sau đó chọn Options, và sau đó chọn Add-Ins.
- Trong hộp Manage, chọn Excel Add-ins và sau đó chọn Go.
- Trong hộp Add-Ins, chọn hộp kiểm Analysis ToolPak, và sau đó chọn OK.
Trên máy Mac, Microsoft hướng dẫn đi tới Tools > Excel Add-ins từ menu tệp. Nếu ToolPak không được liệt kê, hãy chọn Browse để tìm nó.
Sau khi được bật, bạn sẽ thấy Data Analysis trên tab Data. Microsoft lưu ý rằng nếu lệnh này bị thiếu, "bạn cần tải và kích hoạt chương trình bổ trợ Analysis ToolPak."
Cách 1: Chạy hồi quy với Analysis ToolPak
Đây là phương pháp đầy đủ nhất. Nó cung cấp một bảng tóm tắt đầy đủ chỉ trong một bước.
- Đi tới tab Data và chọn Data Analysis.
- Chọn Regression từ danh sách và nhấn OK.
- Trong Input Y Range, chọn cột Doanh số, bao gồm cả tiêu đề.
- Trong Input X Range, chọn cột Tháng, bao gồm cả tiêu đề.
- Tích chọn Labels, vì cả hai phạm vi đều bao gồm tiêu đề.
- Chọn một Output Range hoặc một New Worksheet Ply.
- Chọn OK.
Excel xây dựng một bản tóm tắt với ba khối: thống kê hồi quy, bảng ANOVA và bảng hệ số. Phần tiếp theo sẽ giải thích cách đọc chúng.
Đối với hồi quy đa biến, hãy chọn nhiều cột X liền kề trong bước 4. Microsoft lưu ý công cụ này có thể phân tích "một biến phụ thuộc duy nhất bị ảnh hưởng như thế nào bởi các giá trị của một hoặc nhiều biến độc lập."
Về bản chất, công cụ này sử dụng một hàm trang tính. Microsoft cho biết "Công cụ Regression sử dụng hàm trang tính LINEST."
Cách 2: Sử dụng hàm LINEST
LINEST cung cấp các con số tương tự dưới dạng công thức. Nó phù hợp với những người muốn kết quả cập nhật khi dữ liệu thay đổi.
Cú pháp của Microsoft là LINEST(known_y's, [known_x's], [const], [stats]). Known_y's là phạm vi Doanh số, và known_x's là phạm vi Tháng.
Đối với dữ liệu doanh số, hãy nhập công thức này vào một ô trống:
=LINEST(B2:B7, A2:A7)
Hàm trả về hai giá trị: độ dốc, sau đó là điểm giao cắt. Microsoft cho biết mảng mà LINEST trả về là "{mn,mn-1,...,m1,b}," vì vậy điểm giao cắt luôn đứng cuối cùng.
Để có thêm số liệu thống kê, hãy đặt đối số cuối cùng thành TRUE:
=LINEST(B2:B7, A2:A7, TRUE, TRUE)
Với stats được đặt thành TRUE, LINEST trả về sai số tiêu chuẩn, R bình phương, thống kê F, bậc tự do và tổng bình phương. Microsoft lưu ý hàm này "phải được nhập dưới dạng công thức mảng," vì nó trả về nhiều giá trị cùng một lúc.
Trang của Microsoft cũng hiển thị một dự báo trong một ô. Công thức bên dưới ước tính doanh số tháng 9 từ tháng 1 đến tháng 6:
=SUM(LINEST(B2:B7, A2:A7)*{9,1})
Kết quả của Microsoft cho ví dụ này là 11,000.
Cách 3: Thêm đường xu hướng vào biểu đồ phân tán
Đường xu hướng (trendline) hiển thị đường hồi quy trên biểu đồ. Đây là cách nhanh nhất để xem liệu một đường thẳng có khớp hay không.
- Chọn cả hai cột dữ liệu.
- Chèn một biểu đồ phân tán (scatter chart).
- Chọn biểu đồ, sau đó thêm một Trendline từ menu các thành phần biểu đồ.
- Mở ngăn Format Trendline và giữ tùy chọn Linear.
- Tích vào các ô để hiển thị phương trình (equation) và giá trị R-squared trên biểu đồ.
Biểu đồ sau đó sẽ in phương trình, chẳng hạn như y = 1000x + 2000, bên cạnh đường thẳng. Điều đó khớp với kết quả LINEST cho dữ liệu doanh số.
Đường xu hướng cũng có thể dự báo tương lai. Microsoft cho biết bạn có thể "Đặt một giá trị trong các trường Forward và Backward để dự phóng dữ liệu của bạn vào tương lai."
Đường xu hướng tốt cho việc xem nhanh hoặc trình chiếu. Đối với sai số tiêu chuẩn, giá trị p, hoặc nhiều biến X, hãy sử dụng Cách 1 hoặc Cách 2.
Nên sử dụng phương pháp nào
Mỗi phương pháp phù hợp với một công việc khác nhau. Hãy chọn dựa trên những gì bạn cần trình bày và ai sẽ đọc nó.
| Phương pháp | Tốt nhất cho | Cung cấp giá trị p | Cập nhật theo dữ liệu |
|---|---|---|---|
| Analysis ToolPak | Một báo cáo đầy đủ về một mô hình | Có | Không, phải chạy lại công cụ |
| LINEST | Các mô hình bên trong một bảng tính đang hoạt động | Không, nhưng cung cấp các số liệu để tính toán chúng | Có |
| Đường xu hướng phân tán | Kiểm tra trực quan nhanh hoặc trình chiếu | Không | Có |
Sử dụng ToolPak khi bạn cần bảo vệ một kết quả trong một cuộc họp. Bảng tóm tắt của nó có mọi con số mà người đánh giá có thể hỏi, bao gồm cả giá trị p cho mỗi hệ số.
Sử dụng LINEST khi mô hình cung cấp dữ liệu cho các công thức khác, chẳng hạn như một trang dự báo. Vì nó là một công thức, độ dốc và điểm giao cắt sẽ thay đổi ngay khi có các hàng mới.
Sử dụng đường xu hướng khi khán giả cần nhìn thấy quy luật. Một biểu đồ với đường thẳng và phương trình của nó dễ đọc hơn một bảng biểu. Nhiều nhà phân tích sử dụng cả ba: đường xu hướng để khám phá, LINEST để xây dựng và ToolPak để báo cáo.
Một ví dụ về hồi quy
Dưới đây là những gì ba phương pháp trả về cho sáu tháng doanh số.
| Số liệu thống kê | Giá trị | Ý nghĩa |
|---|---|---|
| Độ dốc (m) | 1,000 | Doanh số tăng khoảng 1,000 mỗi tháng |
| Điểm giao cắt (b) | 2,000 | Giá trị của đường thẳng tại tháng 0 |
| R bình phương | 0.93 | Khoảng 93% sự biến động của doanh số tuân theo xu hướng hàng tháng |
| Ước tính tháng 9 | 11,000 | 1,000 × 9 + 2,000 |
Bạn có thể kiểm tra độ dốc bằng tay. Tháng trung bình là 3.5 và giá trị doanh số trung bình là 5,500. Độ dốc là tổng các tích chéo của các độ lệch chia cho tổng bình phương các độ lệch của tháng. Kết quả là 17,500 chia cho 17.5, bằng 1,000.
Điểm giao cắt sau đó sẽ theo sau. Nó là giá trị doanh số trung bình trừ đi độ dốc nhân với tháng trung bình: 5,500 trừ đi 3,500, bằng 2,000.
R bình phương cho bạn biết đường thẳng khớp tốt như thế nào. Microsoft mô tả nó là một giá trị "nằm trong khoảng từ 0 đến 1." Tại 1, đường thẳng khớp với mọi điểm. Tại 0, Microsoft nói "phương trình hồi quy không giúp ích trong việc dự đoán giá trị y."
Cách đọc kết quả hồi quy
Kết quả đầu ra của Analysis ToolPak có vẻ dày đặc. Hầu hết các quyết định dựa trên một vài con số chính.
- Multiple R. Hệ số tương quan giữa giá trị thực tế và giá trị dự đoán. Càng gần 1 nghĩa là độ khớp càng chặt chẽ.
- R Square. Tỷ lệ biến động của Y được giải thích bởi X. Ở đây là khoảng 0.93.
- Adjusted R Square. R Square được điều chỉnh cho số lượng biến X. Sử dụng nó khi so sánh các mô hình có số lượng đầu vào khác nhau.
- Standard Error. Khoảng cách điển hình giữa các điểm thực tế và đường thẳng.
- Coefficients. Điểm giao cắt và độ dốc cho mỗi X. Những giá trị này xây dựng nên phương trình của bạn.
- P-value. Khả năng nhìn thấy một hệ số lớn như thế này nếu hiệu ứng thực sự bằng không. Nhiều nhà phân tích coi các giá trị dưới 0.05 là có ý nghĩa thống kê.
- F và Significance F. Một bài kiểm tra cho toàn bộ mô hình. Significance F nhỏ cho thấy mô hình giải thích được nhiều hơn là sự ngẫu nhiên.
Microsoft thêm một lưu ý về độ chính xác. Nó nói "Dữ liệu càng tuyến tính, mô hình LINEST càng chính xác."
Đọc các hệ số theo đơn vị thông thường. Độ dốc 1,000 có nghĩa là mỗi tháng tăng thêm sẽ cộng thêm khoảng 1,000 vào doanh số, tính trung bình, trong giai đoạn này.
Thực hiện hồi quy trong Excel với AI
Khi bạn có nhiều cột và không chắc cột nào quan trọng, việc kiểm tra từng cặp bằng tay sẽ tốn thời gian. Một công cụ AI có thể kiểm tra các mối quan hệ trên toàn bộ tập dữ liệu và giải thích chúng.
Dưới đây là phương pháp gồm ba bước, sử dụng Powerdrill Bloom.
Bước 1: Tải lên tệp Excel của bạn
Tải lên bảng tính có cột Y và các cột X tiềm năng của bạn. Trang Excel Calculations yêu cầu bạn tải lên bảng tính trước. Nó nói thêm: "Đảm bảo dữ liệu của bạn được định dạng chính xác để cho phép tính toán suôn sẻ."
Giữ một hàng cho mỗi quan sát và tiêu đề cột rõ ràng. AI sử dụng các tiêu đề để hiểu ý nghĩa của từng cột.
Bước 2: Yêu cầu phân tích bằng ngôn ngữ tự nhiên
Mô tả những gì bạn muốn kiểm tra. Ví dụ: "Chạy hồi quy tuyến tính của doanh số theo tháng và chi tiêu quảng cáo, và giải thích cái nào quan trọng hơn."
Trang tương tự nói rằng bạn có thể "Nhập yêu cầu tính toán mong muốn của bạn bằng văn bản." Đặt các câu hỏi tiếp theo, chẳng hạn như liệu một mối quan hệ có còn giữ nguyên khi bạn loại bỏ một tháng có giá trị ngoại lai hay không.
Bước 3: Kiểm tra kết quả và lập biểu đồ
Kiểm tra độ dốc so với LINEST trong bảng tính của riêng bạn. Sau đó hiển thị mối quan hệ một cách trực quan. Scatter Graph Creator được xây dựng để "trực quan hóa các mối quan hệ dữ liệu với các biểu đồ phân tán tương tác và có thể tùy chỉnh."
Đối với các dự phóng vượt ra ngoài một đường thẳng, công cụ AI Forecasting xây dựng các biểu đồ dự báo với các dải tin cậy. Để biết thêm thông tin về chính phương pháp này, hãy xem bài giải thích của chúng tôi về phân tích hồi quy.
Các lỗi phổ biến cần tránh
Đây là những lỗi thường gặp nhất khi mọi người chạy hồi quy trong Excel.
- Nhầm lẫn giữa X và Y. Y là giá trị bạn muốn giải thích. Việc hoán đổi chúng sẽ làm thay đổi độ dốc.
- Quên hộp Labels. Nếu phạm vi của bạn bao gồm tiêu đề, hãy tích chọn Labels, nếu không Excel sẽ đọc tiêu đề như dữ liệu.
- Chạy hồi quy trong Excel cho web. Microsoft cho biết bạn "không thể tạo phân tích hồi quy" ở đó, vì công cụ Regression không khả dụng. Hãy sử dụng ứng dụng trên máy tính.
- Chỉ đọc R Square. R Square cao không chứng minh được nguyên nhân, và nó có thể che giấu một quy luật dạng đường cong. Luôn nhìn vào biểu đồ phân tán.
- Sử dụng quá ít hàng. Sáu điểm có thể tạo ra một đường thẳng đẹp mắt một cách ngẫu nhiên. Nhiều dữ liệu hơn sẽ cho kết quả đáng tin cậy hơn.
- Nội suy quá xa. Một đường thẳng khớp với các tháng từ 1 đến 6 có thể không còn đúng cho tháng 24.
- Bỏ qua các giá trị ngoại lai. Một tháng bất thường có thể kéo toàn bộ đường thẳng. Kiểm tra biểu đồ để tìm các điểm nằm xa phần còn lại.
- Thêm nhiều cột X có liên quan với nhau. Các đầu vào biến động cùng nhau làm cho các hệ số không ổn định. Microsoft lưu ý LINEST "kiểm tra tính cộng tuyến" và loại bỏ các cột X dư thừa.
Khi bạn đã sẵn sàng kiểm tra các mối quan hệ trên toàn bộ tập dữ liệu, hãy thử Powerdrill Bloom với các số liệu hàng tháng của năm ngoái.
Các câu hỏi thường gặp
Làm thế nào để thực hiện hồi quy tuyến tính đơn giản trong Excel?
Bật Analysis ToolPak, đi tới Data > Data Analysis và chọn Regression. Chọn vùng Y và X của bạn, tích chọn Labels nếu bạn bao gồm tiêu đề và nhấn OK. Bạn cũng có thể sử dụng =LINEST(vùng Y, vùng X).
Tôi có thể thực hiện hồi quy trong Excel cho web không?
Không hoàn toàn. Microsoft cho biết bạn có thể xem kết quả hồi quy trong Excel cho web, nhưng bạn không thể tạo phân tích hồi quy ở đó. Hãy mở tệp trong ứng dụng máy tính để chạy nó.
R bình phương có nghĩa là gì trong hồi quy Excel?
R bình phương cho biết đường thẳng giải thích được bao nhiêu phần trăm sự biến động của Y. Nó nằm trong khoảng từ 0 đến 1. Giá trị 0.93 có nghĩa là khoảng 93% sự biến động tuân theo đường thẳng đã khớp.
Làm thế nào để thực hiện hồi quy đa biến trong Excel?
Đặt mỗi biến X vào cột liền kề riêng của nó. Trong công cụ Regression, chọn tất cả các cột X làm Input X Range. LINEST cũng chấp nhận nhiều cột X.
Sự khác biệt giữa LINEST và công cụ Regression là gì?
Chúng đưa ra các hệ số giống nhau. Microsoft cho biết công cụ Regression sử dụng LINEST. Công cụ này viết một bảng tóm tắt đầy đủ, trong khi LINEST trả về các con số dưới dạng công thức cập nhật theo dữ liệu của bạn.
Nguồn: Microsoft Support, Sử dụng Analysis ToolPak để thực hiện phân tích dữ liệu phức tạp · Microsoft Support, Hàm LINEST · Microsoft Support, Thực hiện phân tích hồi quy · Microsoft Support, Thêm đường xu hướng hoặc đường trung bình động vào biểu đồ.