Hướng dẫn how to find outliers in regression analysis excel - cách tìm ra các giá trị ngoại lai trong excel phân tích hồi quy

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


• Sau đó kéo tay cầm điền vào ô D11.Fill Handle to cell D11.

• Làm điều này sẽ lấp đầy phạm vi của ô D5: D11 với văn bản cho biết giá trị có phải là một ngoại lệ hay không.D5:D11 with text indicating whether the value is an Outlier or not.

Khi chúng tôi tạo ra một biểu đồ phân tán của biểu đồ, chúng tôi có thể dễ dàng phân biệt các ngoại lệ với phần còn lại của bộ dữ liệu trong Excel trong khi tiến hành phân tích hồi quy.Outliers from the rest of the dataset in Excel while conducting regression analysis.

Đọc thêm: Cách hiển thị các ngoại lệ trong biểu đồ Excel (2 phương pháp hiệu quả)How to Show Outliers in Excel Graph (2 Effective Methods)

3. Sử dụng biểu đồ để tìm các ngoại lệ

Sử dụng biểu đồ là cách nhanh nhất để tách các ngoại lệ trong kết quả phân tích hồi quy. Mặc dù họ có thể chính xác 100 phần trăm và có thể thay đổi từ người này sang người khác. Nó có thể được sử dụng như một phần của quá trình sàng lọc chính. Outliers in Regression Analysis results. Although they can’t be 100 percent accurate and can vary from person to person. It can be used as a part of the primary screening process.

Các bước

• Đối với phương pháp này, chúng ta cần tạo biểu đồ phân tán.Scatter chart.
• Để làm điều này, hãy chuyển đến tab Chèn và sau đó nhấp vào phân tán trong nhóm biểu đồ.Insert tab and then click on the Scatter in the charts group.

• Sau đó nhấp chuột phải vào chuột và nhấp vào dữ liệu chọn.Select Data.

• Sau này, sẽ có một hộp thoại mới có tên là nguồn dữ liệu.Select Data Source.
• Trong hộp đó, nhấp vào nút Thêm.Add button.

• Sau đó, trong hộp thoại Chỉnh sửa loạt, nhấp vào hộp Range giá trị Series X và chọn phạm vi của các ô B5: B11.Edit Series dialog box, click on the Series X values range box and select the range of cells B5:B11.
• Sau đó nhấp vào hộp Range giá trị loạt Y và chọn phạm vi của ô C5: C11.Series Y value range box and select the range of cells C5:C11.
• Bấm OK sau này. OK after this.

• Sau đó, bạn sẽ nhận thấy rằng biểu đồ trong bảng tính hiện có dữ liệu được vẽ.
• Để vẽ một đường xu hướng, chúng ta cần nhấp vào biểu tượng Plus ở phía bên phải của biểu đồ.Trendline, we need to click on the plus icon on the right side of the chart.
• Sau đó chọn và đánh dấu vào xu hướng & nbsp; hộp.Trendline box.
• Sau đó sẽ có một xu hướng.Trendline after then.

• Nhấp đúp chuột vào xu hướng để chọn nó và sau đó nhấp chuột phải vào nó.Trendline to select it and then right-click on it.
• Từ menu ngữ cảnh, nhấp vào xu hướng định dạng.Format Trendline.

• Trên menu bảng điều khiển bên, đánh dấu vào phương trình hiển thị trên biểu đồ và giá trị hiển thị r bình phương & nbsp; hộp.Display Equation on chart and the Display R Squared value boxes.

• Phương trình và giá trị R-bình phương của các phương trình hiện được trình bày trong biểu đồ.

• Từ biểu đồ, nó khá rõ ràng giá trị thứ ba trong cơ sở dữ liệu là ngoại lệ, vì nó là một cách từ đường xu hướng.Outlier, as it is quite a way from the Trendline.

Lưu ýNote

Phương pháp trên chỉ hoạt động khi bộ dữ liệu nhỏ. Nơi bạn có thể quét giá trị và biểu đồ nhanh chóng. Trong trường hợp của một bộ dữ liệu lớn, bạn nên chọn phương pháp thứ hai và thứ nhất.

Và đây là cách chúng tôi tìm ra các ngoại lệ trong phân tích hồi quy trong Excel bằng biểu đồ phân tán. Outliers in regression analysis in Excel using the scatter chart.

Đọc thêm: Cách tìm các ngoại lệ với độ lệch chuẩn trong Excel (với các bước nhanh)How to Find Outliers with Standard Deviation in Excel (with Quick Steps)

Sự kết luận

Tóm lại, câu hỏi Làm thế nào để tìm ra các ngoại lệ trong phân tích hồi quy Excel ‘được trả lời ở đây trong 3 bước riêng biệt với các giải thích phức tạp.Outliers in Regression Analysis Excel‘ is answered here in 3 separate steps with elaborate explanations.

Đối với vấn đề này, một sổ làm việc có sẵn để tải xuống nơi bạn có thể thực hành các phương pháp này.

Hãy hỏi bất kỳ câu hỏi hoặc phản hồi thông qua phần bình luận. Bất kỳ đề xuất nào cho sự cải thiện của cộng đồng Exceldemy sẽ được đánh giá caoExceldemy community will be highly appreciable

Những bài viết liên quan

• Cách tính trung bình không bao gồm các ngoại lệ trong Excel (4 phương pháp dễ dàng)
• Loại bỏ các ngoại lệ trong biểu đồ phân tán excel (3 phương pháp dễ dàng)
• Cách tìm các ngoại lệ bằng điểm Z trong Excel (với các bước nhanh)