Các ngoại lệ trong phân tích hồi quy là một vấn đề phổ biến. Trong hầu hết các trường hợp, các ngoại lệ là kết quả của các lỗi thử nghiệm. Có loại ngoại lệ này có thể điều khiển hướng phân tích và tạo ra những quan niệm sai lầm về kết quả. Nếu bạn gặp khó khăn khi tìm ra các ngoại lệ từ kết quả phân tích hồi quy của bạn, bài viết này có thể có ích cho bạn. Ở đây chúng tôi thảo luận về cách bạn có thể tìm thấy các ngoại lệ trong phân tích hồi quy trong Excel một cách hiệu quả. in the Regression Analysis are a common issue. In most cases, Outliers are the result of experimental errors. Having this kind of Outliers can steer the direction of the analysis and create misconceptions about the outcome. If you are baffled to find out the Outliers from your Regression Analysis results, this article may come in handy for you. Here we discuss how you can find Outliers in Regression Analysis in Excel effectively.
Tải xuống Sổ làm việc thực hành
Tải xuống sách bài tập thực hành này dưới đây.
3 cách dễ dàng để tìm ra các ngoại lệ trong phân tích hồi quy trong Excel
Chúng tôi sẽ sử dụng 3 cách riêng biệt để phân biệt các ngoại lệ với phân tích hồi quy dẫn đến Excel. Các phương pháp sẽ liên quan đến việc sử dụng các loại chức năng và biểu đồ khác nhau. Để hiểu rõ hơn, kiến thức trước về phân tích hồi quy và biểu đồ cơ bản được khuyến nghị. Outliers from the Regression Analysis results in Excel. The methods will involve using various types of functions and charts. For better understanding, prior knowledge of the Regression Analysis and basic charts is recommended.
1. Áp dụng công thức kết hợp
Sử dụng chức năng IF, chúng tôi sẽ nhận được các ngoại lệ từ bộ dữ liệu của chúng tôi. Chúng tôi cũng sẽ sử dụng các hàm SQRT, ABS và SUM. Chúng ta có thể tiến hành phân tích hồi quy và trích xuất các ngoại lệ từ đầu ra.the IF function we will get the Outliers from our dataset. We will also use the SQRT, ABS,and SUM functions. We can conduct the Regression Analysis and extract the Outliers from the output.
Các bước
- Ban đầu, chúng ta cần tiến hành phân tích hồi quy của bộ dữ liệu của chúng ta.Regression Analysis of our dataset.
- Để làm điều này, hãy chuyển đến phân tích dữ liệu> dữ liệu.Data > Data analysis.
- Sau đó, trong hộp thoại mới có tên phân tích dữ liệu, chọn hồi quy tùy chọn.Data Analysis, select the option Regression.
- Sau đó bấm OK.OK.
- Trong hộp phạm vi Y đầu vào, chọn phạm vi của các ô C4: C15.Input Y Range box, select the range of cells C4:C15.
- Sau đó, trong hộp Range X Range đầu vào, chọn phạm vi của các ô B4: B15.Input X Range box, select the range of cells B4:B15.
- Đánh dấu tiếp theo trên nhãn & nbsp; hộp.Labels box.
- Sau đó, trong nhóm tùy chọn đầu ra, chọn phạm vi đầu ra và sau đó chọn ô E2 trong hộp phạm vi.Output options group, select the Output Range and then select cell E2 in the range box.
- Sau đó, kiểm tra phần dư, chuẩn hóa phần dư và các lô phù hợp với dòng & nbsp; hộp.Residuals, Standardize Residuals, and Line Fit Plots boxes.
- Bấm OK sau này.OK after this.
- Sau đó, bạn sẽ nhận thấy rằng kết quả phân tích hồi quy hiện được trình bày trong bảng Excel bắt đầu từ các tế bào E2 & NBSP;Regression Analysis results are now presented in the Excel sheet starting from the E2 cells.
Bây giờ chúng ta sẽ cần giá trị tổng bình phương của phần dư và sau đó chúng ta sẽ sử dụng giá trị đó để tính độ lệch chuẩn của phần dư.Squared Sum value of the residuals and then we will use that value to calculate the Standard Deviation of the residuals.
- Chọn ô J26 và nhập công thức sau:J26, and enter the following formula:
- Nhập công thức này sẽ tính toán bình phương của phần dư tại ô H26.H26.
- Kéo tay cầm điền vào ô J36.Fill Handle to the cell J36.
- Làm điều này sẽ lấp đầy phạm vi của các ô J26: J36 với giá trị bình phương của phần dư.J26:J36 with the squared value of the Residuals.
- Sau đó chọn ô K37 và nhập công thức sau:K37 and enter the following formula:
- Làm điều này sẽ tính toán tổng của phạm vi của các ô K26: K36, nói cách khác, chúng tôi có tổng số dư bình phương [SSE].K26:K36, in other words, we got the Sum of the Squared Residuals[SSE].
- Tiếp theo, để tính [các] độ lệch chuẩn, chúng tôi chọn ô N26 và nhập công thức sau:Standard Deviation[S], we select the cell N26 and enter the following formula:
- Giá trị này là giá trị của độ lệch chuẩn của phần dư của bộ dữ liệu đã cho.
- Bây giờ chúng tôi chọn ô L26 và nhập công thức sau:L26 and enter the following formula:
=IF[ABS[H26]>$N$26,"Outlier","Not Outlier"]
- Công thức này sẽ kiểm tra xem giá trị tuyệt đối của phần dư lớn hơn tổng số dư bình phương [SSE] được tính trong bước trước. Nếu phần dư lớn hơn giá trị SSE, thì ô sẽ hiển thị ngoại lệ. Nếu không, nó sẽ hiển thị không phải là ngoại lệ.Sum of the Squared Residuals[SSE] calculated in the earlier step. If the residual is greater than the SSE value, then the cell will show “Outlier”. Otherwise, it will show “Not Outlier”.
- Kéo tay cầm điền vào ô L28 và thực hiện điều này sẽ cho biết liệu giá trị ô có phải là một ngoại lệ hay không trong phạm vi của các ô L26: L36.Fill Handle to the cell L28, and doing this will indicate whether the cell value is an Outlier or not in the range of cells L26:L36.
- Chúng ta có thể quan sát rằng giá trị đầu tiên của phạm vi của ô L26: K36 đang hiển thị ngoại lệ và phần còn lại của chúng không phải là ngoại lệ.L26:K36 is showing Outlier And the rest of them are Not Outliers.
- Tiếp theo, chọn ô D5 và nhập công thức sau:D5 and enter the following formula:
- Sau đó kéo tay cầm điền vào ô D15, sẽ lấp đầy phạm vi của ô D5: D15 với giới hạn trên của giá trị ngoại lệ thương vong. Bất kỳ giá trị nào trên các giá trị sẽ được coi là một ngoại lệ.Fill Handle to cell D15, which will fill the range of cell D5:D15 with the upper limit of the casualties Outlier value. Any value above the values will be considered an Outlier.
- Tương tự, chọn ô E5 và nhập công thức sau:E5 and enter the following formula:
- Sau đó kéo tay cầm điền vào ô E5. Làm điều này sẽ lấp đầy phạm vi của ô E5: E15 với giới hạn thấp hơn của giá trị ngoại lệ của thương vong.Fill Handle to cell E5. Doing this will fill the range of cell E5:E15 with the lower limit of the Outlier value of Casualties.
- Sau đó, chúng tôi đã tạo ra biểu đồ với các giá trị mà chúng tôi thu được cho đến nay. Trong đó tạo thành giới hạn thấp hơn và trên của các giá trị ngoại lệ & nbsp; Outlier values.
Chúng ta có thể thấy rõ từ biểu đồ rằng giá trị đầu tiên nằm trên bờ của giới hạn trên. Vì vậy, giá trị này là ngoại lệ & nbsp; ở đây. Outlier here.
Đọc thêm: Cách tính các ngoại lệ theo Excel [5 cách dễ dàng]How to Calculate Outliers in Excel [5 Easy Ways]
2. Áp dụng phạm vi liên quan
Bằng cách tính toán các bộ tứ thứ nhất và thứ ba của bộ dữ liệu và sau đó ước tính phạm vi liên nhóm giữa chúng, chúng ta có thể tách ngoại lệ khỏi bộ dữ liệu. Chúng tôi sẽ sử dụng sự kết hợp của Quartile.exc và nếu các chức năng để đạt được điều này.Quartiles of a dataset and then estimating the Inter-quartile Range between them, we can separate the Outlier from the dataset. We will use the combination of QUARTILE.EXC and IF functions to achieve this.
Các bước
- Ban đầu, chúng ta cần tiến hành phân tích hồi quy của bộ dữ liệu của chúng ta.F5 and enter the following formula:
- Ban đầu, chúng ta cần tiến hành phân tích hồi quy của bộ dữ liệu của chúng ta.F5 and enter the following formula:
- Để làm điều này, hãy chuyển đến phân tích dữ liệu> dữ liệu.F7 and enter the following formula:
- Sau đó, trong hộp thoại mới có tên phân tích dữ liệu, chọn hồi quy tùy chọn.F8 and enter the following formula:
- Sau đó bấm OK.F9 and enter the following formula:
- Sau đó chọn ô D5 và nhập công thức sau:D5 and enter the following formula:
=IF[C5