Hướng dẫn how do you use excel to solve outliers? - làm thế nào để bạn sử dụng excel để giải quyết các ngoại lệ?

Khi làm việc với dữ liệu trong Excel, bạn sẽ thường gặp các vấn đề xử lý các ngoại lệ trong bộ dữ liệu của mình.

Có các ngoại lệ là khá phổ biến trong tất cả các loại dữ liệu, và điều quan trọng là xác định và xử lý các ngoại lệ này để đảm bảo rằng phân tích của bạn là chính xác và có ý nghĩa hơn.

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách tìm các ngoại lệ trong Excel và một số kỹ thuật mà tôi đã sử dụng trong công việc của mình để xử lý các ngoại lệ này.how to find outliers in Excel, and some of the techniques that I have used in my work to handle these outliers.

• Các ngoại lệ là gì và tại sao điều quan trọng là tìm thấy những điều này là gì?
• Tìm ngoại lệ bằng cách sắp xếp dữ liệu
• Tìm các ngoại lệ bằng cách sử dụng các chức năng tứong
• Tìm các ngoại lệ bằng cách sử dụng các chức năng lớn/nhỏ
• Cách xử lý các ngoại lệ đúng cách

Các ngoại lệ là gì và tại sao điều quan trọng là tìm thấy những điều này là gì?

Tìm ngoại lệ bằng cách sắp xếp dữ liệu

Tìm các ngoại lệ bằng cách sử dụng các chức năng tứong

Tìm các ngoại lệ bằng cách sử dụng các chức năng lớn/nhỏ

Cách xử lý các ngoại lệ đúng cách

Một ngoại lệ là một điểm dữ liệu vượt ra ngoài các điểm dữ liệu khác trong tập dữ liệu. Khi bạn có một ngoại lệ trong dữ liệu, nó có thể làm lệch dữ liệu của bạn có thể dẫn đến các suy luận không chính xác.

Hãy để tôi cho bạn một ví dụ đơn giản.

Hãy nói rằng 30 người đang đi trong một chiếc xe buýt từ đích A đến đích B. Tất cả những người ở trong một nhóm cân nặng và nhóm thu nhập tương tự. Với mục đích của hướng dẫn này, hãy để Lôi xem xét trọng lượng trung bình là 220 pounds và thu nhập trung bình hàng năm là 70.000 đô la.

Bây giờ ở đâu đó giữa tuyến đường của chúng tôi, xe buýt dừng lại và Bill Gates nhảy vào.

Bây giờ, bạn nghĩ điều này sẽ làm gì với trọng lượng trung bình và thu nhập trung bình của người dân trên xe buýt.

Mặc dù trọng lượng trung bình không có khả năng thay đổi nhiều, thu nhập trung bình của người dân trên xe buýt sẽ tăng vọt rất nhiều.

Tìm ngoại lệ bằng cách sắp xếp dữ liệu

Tìm các ngoại lệ bằng cách sử dụng các chức năng tứong

Tìm các ngoại lệ bằng cách sử dụng các chức năng lớn/nhỏ

Cách xử lý các ngoại lệ đúng cách

Một ngoại lệ là một điểm dữ liệu vượt ra ngoài các điểm dữ liệu khác trong tập dữ liệu. Khi bạn có một ngoại lệ trong dữ liệu, nó có thể làm lệch dữ liệu của bạn có thể dẫn đến các suy luận không chính xác.

Hãy để tôi cho bạn một ví dụ đơn giản.

1. Hãy nói rằng 30 người đang đi trong một chiếc xe buýt từ đích A đến đích B. Tất cả những người ở trong một nhóm cân nặng và nhóm thu nhập tương tự. Với mục đích của hướng dẫn này, hãy để Lôi xem xét trọng lượng trung bình là 220 pounds và thu nhập trung bình hàng năm là 70.000 đô la.
2. Bây giờ ở đâu đó giữa tuyến đường của chúng tôi, xe buýt dừng lại và Bill Gates nhảy vào.
   
3. Bây giờ, bạn nghĩ điều này sẽ làm gì với trọng lượng trung bình và thu nhập trung bình của người dân trên xe buýt.
4. Mặc dù trọng lượng trung bình không có khả năng thay đổi nhiều, thu nhập trung bình của người dân trên xe buýt sẽ tăng vọt rất nhiều.
5. Điều đó vì thu nhập của Bill Gates, là một ngoại lệ trong nhóm của chúng tôi và điều đó cho chúng tôi một cách giải thích sai về dữ liệu. Thu nhập trung bình cho mỗi người trên xe buýt sẽ là vài tỷ đô la, vượt quá giá trị thực tế.
6. Khi làm việc với các bộ dữ liệu thực tế trong Excel, bạn có thể có các ngoại lệ theo bất kỳ hướng nào (nghĩa là, một ngoại lệ tích cực hoặc ngoại lệ tiêu cực).

Và để đảm bảo rằng phân tích của bạn là chính xác, bằng cách nào đó bạn cần phải xác định các ngoại lệ này và sau đó quyết định cách đối xử tốt nhất với chúng.

Bây giờ, hãy để Lôi thấy một vài cách để tìm các ngoại lệ trong Excel.

Với các bộ dữ liệu nhỏ, một cách nhanh chóng để xác định các ngoại lệ là chỉ cần sắp xếp dữ liệu và thực hiện thủ công một số giá trị ở đầu dữ liệu được sắp xếp này.

Tìm các ngoại lệ bằng cách sử dụng các chức năng tứong

Tìm các ngoại lệ bằng cách sử dụng các chức năng lớn/nhỏ

Cách xử lý các ngoại lệ đúng cách

Một ngoại lệ là một điểm dữ liệu vượt ra ngoài các điểm dữ liệu khác trong tập dữ liệu. Khi bạn có một ngoại lệ trong dữ liệu, nó có thể làm lệch dữ liệu của bạn có thể dẫn đến các suy luận không chính xác.

Hãy để tôi cho bạn một ví dụ đơn giản.

=QUARTILE.INC($B$2:$B$15,1)

Hãy nói rằng 30 người đang đi trong một chiếc xe buýt từ đích A đến đích B. Tất cả những người ở trong một nhóm cân nặng và nhóm thu nhập tương tự. Với mục đích của hướng dẫn này, hãy để Lôi xem xét trọng lượng trung bình là 220 pounds và thu nhập trung bình hàng năm là 70.000 đô la.

=QUARTILE.INC($B$2:$B$15,3)

Bây giờ ở đâu đó giữa tuyến đường của chúng tôi, xe buýt dừng lại và Bill Gates nhảy vào.

=F3-F2

Bây giờ, bạn nghĩ điều này sẽ làm gì với trọng lượng trung bình và thu nhập trung bình của người dân trên xe buýt.

Mặc dù trọng lượng trung bình không có khả năng thay đổi nhiều, thu nhập trung bình của người dân trên xe buýt sẽ tăng vọt rất nhiều.

Điều đó vì thu nhập của Bill Gates, là một ngoại lệ trong nhóm của chúng tôi và điều đó cho chúng tôi một cách giải thích sai về dữ liệu. Thu nhập trung bình cho mỗi người trên xe buýt sẽ là vài tỷ đô la, vượt quá giá trị thực tế.

=Quartile1 - 1.5*(Inter Quartile Range)

Khi làm việc với các bộ dữ liệu thực tế trong Excel, bạn có thể có các ngoại lệ theo bất kỳ hướng nào (nghĩa là, một ngoại lệ tích cực hoặc ngoại lệ tiêu cực).

=F2-1.5*F4

Và để đảm bảo rằng phân tích của bạn là chính xác, bằng cách nào đó bạn cần phải xác định các ngoại lệ này và sau đó quyết định cách đối xử tốt nhất với chúng.

=Quartile3 + 1.5*(Inter Quartile Range)

mà trong ví dụ của chúng tôi trở thành:

=F3+1.5*F4

Bây giờ chúng tôi có giới hạn trên và dưới trong tập dữ liệu của mình, chúng tôi có thể quay lại dữ liệu gốc và nhanh chóng xác định những giá trị không nằm trong phạm vi này.

Một cách nhanh chóng để làm điều này là kiểm tra mọi giá trị và trả về đúng hoặc sai trong một cột mới.

Tôi đã sử dụng công thức dưới đây hoặc công thức để đúng với những giá trị đó là ngoại lệ.

=OR(B2<$F$5,B2>$F$6)

Bây giờ bạn có thể lọc cột ngoại lệ và chỉ hiển thị các bản ghi trong đó giá trị là đúng.

Ngoài ra, bạn cũng có thể sử dụng định dạng có điều kiện để làm nổi bật tất cả các ô trong đó giá trị là đúng

Lưu ý: Mặc dù đây là một phương pháp được chấp nhận nhiều hơn để tìm các ngoại lệ trong thống kê. Tôi thấy phương pháp này một chút không thể sử dụng trong các kịch bản thực tế. Trong ví dụ trên, giới hạn thấp hơn được tính theo công thức là -103, trong khi bộ dữ liệu chúng ta chỉ có thể dương. Vì vậy, phương pháp này có thể giúp chúng ta tìm ra các ngoại lệ theo một hướng (giá trị cao), nó vô dụng trong việc xác định các ngoại lệ theo hướng khác. While this is a more accepted method to find outliers in statistics. I find this method a bit unusable in real-life scenarios. In the above example, the lower limit calculated by the formula is -103, while the dataset we have can only be positive. So this method can help us find outliers in one direction (high values), it’s useless in identifying outliers in the other direction.

Tìm các ngoại lệ bằng cách sử dụng các chức năng lớn/nhỏ

Nếu bạn làm việc với nhiều dữ liệu (giá trị trong nhiều cột), bạn có thể trích xuất các giá trị lớn nhất và nhỏ nhất 5 hoặc 7 và xem liệu có bất kỳ ngoại lệ nào trong đó không.

Nếu có bất kỳ ngoại lệ nào, bạn sẽ có thể xác định chúng mà không cần phải đi qua tất cả các dữ liệu theo cả hai hướng.

Giả sử chúng ta có bộ dữ liệu dưới đây và chúng ta muốn biết nếu có bất kỳ ngoại lệ nào.

Dưới đây là công thức sẽ cung cấp cho bạn giá trị lớn nhất trong bộ dữ liệu:

=LARGE($B$2:$B$16,1)

Tương tự, giá trị lớn thứ hai sẽ được đưa ra bởi

=LARGE($B$2:$B$16,1)

Nếu bạn không sử dụng Microsoft 365, có các mảng động, bạn có thể sử dụng công thức dưới đây và nó sẽ cung cấp cho bạn năm giá trị lớn nhất từ ​​bộ dữ liệu với một công thức duy nhất:

=QUARTILE.INC($B$2:$B$15,3)

Tương tự, nếu bạn muốn 5 giá trị nhỏ nhất, hãy sử dụng công thức dưới đây:

=QUARTILE.INC($B$2:$B$15,3)

Hoặc những điều sau đây trong trường hợp bạn không có mảng động:

=QUARTILE.INC($B$2:$B$15,3)

Khi bạn có các giá trị này, nó thực sự dễ dàng tìm ra bất kỳ ngoại lệ nào trong tập dữ liệu.

Mặc dù tôi đã chọn để trích xuất 5 giá trị lớn nhất và nhỏ nhất, bạn có thể chọn nhận được 7 hoặc 10 dựa trên mức độ lớn của bộ dữ liệu của bạn.

Tôi không chắc đây có phải là một phương pháp chấp nhận được để tìm các ngoại lệ trong Excel hay không, nhưng đây là phương pháp mà tôi đã sử dụng khi tôi phải làm việc với nhiều dữ liệu tài chính trong công việc của mình vài năm trước. So với tất cả các phương pháp khác được đề cập trong hướng dẫn này, tôi thấy đây là phương pháp hiệu quả nhất.

Cách xử lý các ngoại lệ đúng cách

Cho đến nay, chúng tôi đã thấy các phương pháp sẽ giúp chúng tôi tìm thấy các ngoại lệ trong tập dữ liệu của chúng tôi. Nhưng phải làm gì một khi bạn biết rằng có những ngoại lệ.

Dưới đây là một vài phương pháp mà bạn có thể sử dụng để xử lý các ngoại lệ để phân tích dữ liệu của bạn là chính xác.

Xóa các ngoại lệ

Cách dễ nhất để loại bỏ các ngoại lệ khỏi bộ dữ liệu của bạn là chỉ cần xóa chúng. Bằng cách này, nó đã giành được phân tích của bạn.

Nó có một giải pháp khả thi hơn khi bạn có các bộ dữ liệu lớn và xóa một vài ngoại lệ đã giành được tác động đến phân tích tổng thể. Và tất nhiên, trước khi xóa dữ liệu, hãy đảm bảo bạn tạo một bản sao và đi sâu vào những gì mà gây ra các ngoại lệ này.

Bình thường hóa các ngoại lệ (điều chỉnh giá trị)

Bình thường hóa các ngoại lệ là những gì tôi từng làm khi tôi đang làm việc toàn thời gian. Đối với tất cả các giá trị ngoại lệ, tôi chỉ cần thay đổi chúng thành một giá trị cao hơn một chút so với giá trị tối đa trong tập dữ liệu.

Điều này đảm bảo rằng tôi không xóa dữ liệu nhưng đồng thời tôi không để nó làm lệch dữ liệu của mình.

Để cung cấp cho bạn một ví dụ thực tế, nếu bạn đang phân tích biên lợi nhuận ròng của các công ty, nơi hầu hết các công ty nằm trong phạm vi -10%đến 30%và có một vài giá trị tăng 100%, tôi Chỉ cần thay đổi các giá trị ngoại lệ này thành 30% hoặc 35%.

Vì vậy, đây là một số phương pháp mà bạn có thể sử dụng trong Excel để tìm các ngoại lệ.Excel to find outliers.

Khi bạn đã xác định được các ngoại lệ, bạn có thể đi sâu vào dữ liệu và tìm kiếm những gì mà gây ra những thứ này, đồng thời chọn một trong các kỹ thuật để xử lý các ngoại lệ này (có thể loại bỏ chúng hoặc bình thường hóa chúng bằng cách điều chỉnh giá trị)

Tôi hy vọng bạn tìm thấy hướng dẫn này hữu ích.

Các hướng dẫn Excel khác mà bạn cũng có thể thích:

• Cách tìm phạm vi trong Excel (Công thức dễ dàng)
• Tính trung bình di chuyển trong Excel [Đơn giản, Trọng số và theo cấp số nhân]
• Cách tính độ lệch chuẩn trong Excel

Bạn có thể sử dụng Excel để tìm ngoại lệ không?

Cách nhanh nhất để loại bỏ các ngoại lệ trong Excel là gì?

Công thức tính toán các ngoại lệ là gì?