Thời gian đọc ước tính: 12 phút
Những điểm chính
- Hiểu bản chất Outlier: Giá trị ngoại lai có thể làm sai lệch nghiêm trọng kết quả trung bình và các mô hình dự báo.
- Phương pháp thủ công: Sử dụng tính năng Sắp xếp (Sort) để phát hiện nhanh các giá trị bất thường ở hai đầu dữ liệu.
- Phương pháp thống kê: Ứng dụng hàm QUARTILE và khoảng tứ phân vị (IQR) để xác định giới hạn trên và dưới một cách khoa học.
- Hàm LARGE/SMALL: Cách trích xuất nhanh nhóm giá trị cao nhất hoặc thấp nhất để kiểm tra.
- Xử lý dữ liệu: Các chiến lược an toàn khi đối mặt với dữ liệu nhiễu: xóa bỏ, điều chỉnh (normalization) hoặc giữ nguyên.
Khi làm việc với các tập dữ liệu trong Excel, bạn sẽ thường xuyên gặp phải các giá trị nằm cách xa so với phần còn lại của dữ liệu. Những giá trị này được gọi là Outlier (Giá trị ngoại lai).
Việc xuất hiện các giá trị ngoại lai là điều rất bình thường trong mọi loại dữ liệu, từ báo cáo tài chính, dữ liệu bán hàng đến các chỉ số kỹ thuật. Tuy nhiên, khả năng xác định và xử lý chúng đóng vai trò quyết định đến độ chính xác của bảng phân tích cuối cùng. Nếu bỏ qua bước này, các báo cáo của bạn có thể đưa ra những kết luận sai lệch hoàn toàn so với thực tế.
Bài viết này sẽ hướng dẫn bạn chi tiết cách tìm outlier trong Excel thông qua các công cụ có sẵn, cũng như chia sẻ các kỹ thuật thực tế để xử lý chúng hiệu quả nhất.
Outlier là gì và tại sao việc phát hiện chúng lại quan trọng?
Outlier là một điểm dữ liệu có giá trị khác biệt đáng kể so với các điểm dữ liệu khác trong cùng một tập hợp. Sự hiện diện của một outlier có thể “bẻ cong” các chỉ số thống kê, đặc biệt là giá trị trung bình (Mean), dẫn đến những suy luận không chính xác.
Để dễ hình dung, hãy xem xét một ví dụ thực tế:
Giả sử có 30 hành khách đang di chuyển trên một chiếc xe buýt từ địa điểm A đến địa điểm B. Nhóm người này có mức thu nhập và cân nặng tương đối đồng đều. Trung bình cân nặng là 70kg và thu nhập trung bình hàng năm là 1,6 tỷ VND.
Đột nhiên, xe buýt dừng lại và tỷ phú Bill Gates bước lên xe.
Điều gì sẽ xảy ra với mức cân nặng trung bình và thu nhập trung bình của nhóm người trên xe?
Trong khi cân nặng trung bình có thể không thay đổi nhiều, mức thu nhập trung bình của cả nhóm sẽ tăng vọt lên một con số khổng lồ. Bill Gates chính là một outlier trong tập dữ liệu về thu nhập này. Việc tính toán thu nhập bình quân lúc này sẽ cho ra con số hàng tỷ USD mỗi người, một con số hoàn toàn không phản ánh đúng thực tế của đại đa số hành khách.
Khi làm việc với dữ liệu thực tế trong Excel, outlier có thể xuất hiện ở cả hai hướng:
- Outlier dương: Giá trị quá lớn so với mặt bằng chung.
- Outlier âm: Giá trị quá nhỏ hoặc âm sâu so với phần còn lại.
Để bảo đảm tính toàn vẹn của dữ liệu, bạn cần xác định vị trí của các điểm này và đưa ra quyết định xử lý phù hợp. Dưới đây là các phương pháp cụ thể để thực hiện điều đó.
Cách 1: Tìm Outlier bằng cách sắp xếp dữ liệu (Sorting)
Đối với các tập dữ liệu nhỏ (dưới 100 dòng), phương pháp nhanh nhất để phát hiện giá trị bất thường là sắp xếp lại dữ liệu. Bạn chỉ cần quan sát các giá trị ở đầu và cuối danh sách đã sắp xếp.
Do outlier có thể nằm ở cả hai cực (rất cao hoặc rất thấp), bạn nên thực hiện sắp xếp theo cả hai chiều: tăng dần và giảm dần.
Ví dụ thực hành:
Dưới đây là một tập dữ liệu ghi lại thời lượng cuộc gọi (tính bằng giây) của 15 cuộc gọi dịch vụ khách hàng.
Dữ liệu mẫu chứa các giá trị ngoại lai cần xử lý
Hãy thực hiện theo các bước sau để sắp xếp dữ liệu và “bắt” các outlier:
- Chọn tiêu đề cột mà bạn muốn sắp xếp (trong ví dụ này là ô B1 – Duration).
- Nhấn vào thẻ Home trên thanh công cụ.
Nhấn vào thẻ Home trên thanh công cụ Excel - Trong nhóm công cụ Editing, chọn biểu tượng Sort & Filter.
Chọn biểu tượng Sort and Filter để mở menu sắp xếp - Chọn Custom Sort.
Chọn Custom Sort để tùy chỉnh cách sắp xếp dữ liệu - Trong hộp thoại Sort, tại mục “Sort by” chọn cột ‘Duration’ và tại mục “Order” chọn Largest to Smallest (Lớn nhất đến Nhỏ nhất).
Thiết lập các thông số để sắp xếp dữ liệu theo thứ tự giảm dần - Nhấn OK.
Sau khi thực hiện, cột thời lượng cuộc gọi sẽ được sắp xếp với các giá trị cao nhất nằm trên cùng. Lúc này, bạn có thể quét nhanh bằng mắt thường để xem có giá trị nào bất hợp lý hay không.
Các giá trị ngoại lai hiển thị rõ ràng sau khi sắp xếp dữ liệu
Trong ví dụ trên, hai giá trị đầu tiên (1250 và 1100) cao hơn rất nhiều so với phần còn lại (chỉ tầm 100-200). Tương tự, nếu kéo xuống dưới cùng, bạn có thể thấy hai giá trị rất thấp (12 và 10). Đây chính là những nghi phạm outlier mà bạn cần xem xét.
Lưu ý: Phương pháp này phụ thuộc vào khả năng đánh giá chủ quan và chỉ hiệu quả với lượng dữ liệu ít. Với dữ liệu lớn hàng nghìn dòng, bạn cần một phương pháp khoa học hơn.
Cách 2: Tìm Outlier sử dụng hàm Tứ phân vị (Quartile Function)
Đây là phương pháp mang tính thống kê và khoa học cao, thường được các nhà phân tích dữ liệu chuyên nghiệp sử dụng. Phương pháp này dựa trên nguyên tắc Khoảng tứ phân vị (Interquartile Range – IQR).
Trong thống kê, tứ phân vị chia tập dữ liệu thành 4 phần bằng nhau.
- Q1 (Quartile 1): Điểm mốc mà 25% dữ liệu nhỏ nhất nằm dưới đó.
- Q3 (Quartile 3): Điểm mốc mà 75% dữ liệu nằm dưới đó (hay 25% dữ liệu lớn nhất nằm trên đó).
- IQR: Khoảng cách giữa Q3 và Q1 (IQR = Q3 – Q1), chứa 50% dữ liệu ở giữa.
Chúng ta sẽ xác định Giới hạn trên (Upper Limit) và Giới hạn dưới (Lower Limit). Bất kỳ giá trị nào nằm ngoài vùng giới hạn này đều bị coi là outlier.
Các bước thực hiện:
Giả sử chúng ta sử dụng lại tập dữ liệu ở ví dụ trước.
-
Tính Tứ phân vị thứ nhất (Q1):
Tại ô E2, nhập công thức sau:=QUARTILE.INC($B$2:$B$15,1)
Công thức tính Tứ phân vị thứ nhất Q1 trong Excel -
Tính Tứ phân vị thứ ba (Q3):
Tại ô E3, nhập công thức:=QUARTILE.INC($B$2:$B$15,3)
Công thức tính Tứ phân vị thứ ba Q3 trong Excel -
Tính Khoảng tứ phân vị (IQR):
IQR là hiệu số giữa Q3 và Q1.=F3-F2(Giả sử F3 chứa giá trị Q3 và F2 chứa giá trị Q1)
Công thức tính khoảng tứ phân vị IQR bằng cách lấy Q3 trừ Q1 -
Xác định Giới hạn dưới (Lower Limit):
Công thức tiêu chuẩn trong thống kê là:Lower Limit = Q1 - 1.5 * IQR.
Trong ví dụ của chúng ta:=F2-1.5*F4
Công thức tính giới hạn dưới để xác định outlier âm -
Xác định Giới hạn trên (Upper Limit):
Công thức tiêu chuẩn là:Upper Limit = Q3 + 1.5 * IQR.
Trong ví dụ:=F3+1.5*F4
Công thức tính giới hạn trên để xác định outlier dương
Sau khi có hai giới hạn này, bạn quay lại bảng dữ liệu gốc để kiểm tra. Bất kỳ giá trị nào nhỏ hơn Giới hạn dưới hoặc lớn hơn Giới hạn trên sẽ được đánh dấu là TRUE (là Outlier).
Bạn có thể dùng hàm OR kết hợp so sánh logic tại cột bên cạnh dữ liệu gốc:
=OR(B2<$F$6, B2>$F$7)(Giả sử F6 là Giới hạn dưới, F7 là Giới hạn trên)
Sử dụng hàm OR để trả về kết quả TRUE cho các giá trị là outlier
Sau đó, bạn chỉ cần lọc (Filter) cột kết quả này để lấy các giá trị TRUE.
Lưu ý quan trọng: Trong ví dụ trên, giới hạn dưới tính ra là số âm (-103). Vì thời gian gọi điện không thể là số âm, điều này cho thấy dữ liệu của chúng ta không có outlier ở phía dưới (Low outliers), mà chỉ cần quan tâm đến các giá trị quá cao ở phía trên.
Cách 3: Tìm Outlier bằng hàm LARGE và SMALL
Khi bạn phải làm việc với lượng dữ liệu lớn và nằm rải rác ở nhiều cột, hoặc đơn giản bạn muốn trích xuất “Top 5 giá trị cao nhất/thấp nhất” để kiểm tra nhanh, hàm LARGE và SMALL là giải pháp tối ưu.
Phương pháp này giúp bạn cô lập các giá trị cực đoan mà không cần phải thay đổi cấu trúc hay sắp xếp lại toàn bộ bảng tính gốc.
Giả sử vẫn với tập dữ liệu cũ:
Dữ liệu ban đầu để áp dụng hàm LARGE và SMALL
Để tìm 5 giá trị lớn nhất:
Sử dụng công thức mảng động (nếu dùng Microsoft 365):
=LARGE($B$2:$B$16,ROW($1:5))Hàm ROW($1:5) sẽ tạo ra một mảng {1;2;3;4;5}, yêu cầu Excel trả về 5 giá trị lớn nhất theo thứ tự.
Sử dụng hàm LARGE để tìm 5 giá trị lớn nhất trong tập dữ liệu
Nếu bạn dùng phiên bản Excel cũ hơn, hãy nhập công thức cho từng ô riêng biệt:
- Giá trị lớn nhất:
=LARGE($B$2:$B$16,1) - Giá trị lớn thứ hai:
=LARGE($B$2:$B$16,2)
Để tìm 5 giá trị nhỏ nhất:
Tương tự, bạn sử dụng hàm SMALL:
=SMALL($B$2:$B$16,ROW($1:5))Hoặc nhập thủ công cho từng dòng:
=SMALL($B$2:$B$16,1)Khi đã trích xuất được 5-10 giá trị đứng đầu hai cực, bạn có thể dễ dàng nhận định xem chúng có phải là số liệu bất thường hay không. Đây là cách làm thực dụng mà nhiều chuyên gia phân tích tài chính sử dụng để kiểm tra nhanh (“sanity check”) các báo cáo trước khi đi vào phân tích sâu.
Quy trình xử lý Outlier đúng cách
Sau khi đã tìm ra các “kẻ phá bĩnh”, câu hỏi tiếp theo là: Bạn nên làm gì với chúng?
Việc xử lý outlier không chỉ đơn giản là xóa bỏ. Tùy thuộc vào nguyên nhân và ngữ cảnh dữ liệu, bạn cần áp dụng các chiến lược khác nhau để bảo đảm tính trung thực của phân tích.
1. Xóa bỏ Outlier (Delete)
Đây là cách đơn giản nhất nhưng cần cẩn trọng nhất. Bạn chỉ nên xóa outlier khi xác định chắc chắn rằng đó là lỗi nhập liệu hoặc lỗi hệ thống (ví dụ: tuổi nhân viên là 200, thời gian gọi là số âm).
- Ưu điểm: Làm sạch dữ liệu nhanh, giúp các biểu đồ và tính toán trung bình đẹp hơn.
- Nhược điểm: Nếu xóa nhầm các dữ liệu thực (ví dụ: một đơn hàng đột biến nhưng có thật), bạn sẽ làm mất đi thông tin quý giá về thị trường hoặc xu hướng.
- Lời khuyên: Luôn sao lưu dữ liệu gốc trước khi xóa.
2. Điều chỉnh giá trị (Normalize/Winsorizing)
Thay vì xóa bỏ, bạn có thể “kìm hãm” các giá trị này lại. Kỹ thuật này thường được gọi là Winsorizing. Bạn sẽ thay thế các giá trị outlier bằng giá trị giới hạn chấp nhận được (ví dụ: thay thế tất cả giá trị > 99th percentile bằng giá trị tại 99th percentile).
Ví dụ thực tế:
Khi phân tích biên lợi nhuận ròng của các công ty, phần lớn dao động từ -10% đến 30%. Nếu có một vài công ty đạt 500% (do bán tài sản hay thu nhập bất thường), thay vì xóa, tôi sẽ điều chỉnh chúng về mức trần là 35% hoặc 40%.
- Mục đích: Giữ lại điểm dữ liệu để không làm mất mẫu số (số lượng mẫu), nhưng giảm thiểu tác động cực đoan của nó lên giá trị trung bình.
3. Giữ nguyên và sử dụng phương pháp thay thế
Đôi khi, outlier phản ánh đúng thực tế (ví dụ: thu nhập của Bill Gates). Trong trường hợp này, bạn không nên xóa hay sửa. Thay vào đó, hãy thay đổi phương pháp phân tích:
- Sử dụng Trung vị (Median) thay vì Trung bình cộng (Mean) để làm thước đo xu hướng tập trung. Median ít bị ảnh hưởng bởi outlier hơn rất nhiều.
Câu hỏi thường gặp (FAQ)
Outlier ảnh hưởng thế nào đến biểu đồ trong Excel?
Outlier có thể làm cho biểu đồ của bạn bị méo mó. Ví dụ, trong biểu đồ đường hoặc biểu đồ cột, trục giá trị (Y-axis) sẽ phải mở rộng để chứa outlier, làm cho các biến động của các dữ liệu còn lại trở nên quá nhỏ bé và khó quan sát (trông như một đường thẳng).
Hàm QUARTILE.INC và QUARTILE.EXC khác nhau chỗ nào?
QUARTILE.INC: Bao gồm cả giá trị nhỏ nhất và lớn nhất trong phép tính (Inclusive). Đây là hàm phổ biến và an toàn hơn cho các tập dữ liệu nhỏ.QUARTILE.EXC: Loại trừ các điểm đầu mút (Exclusive). Hàm này thường dùng khi bạn muốn suy rộng ra một quần thể lớn hơn từ mẫu hiện có, nhưng nó sẽ báo lỗi nếu tập dữ liệu quá ít phần tử.
Có nên dùng Conditional Formatting để tô màu Outlier không?
Rất nên. Bạn có thể thiết lập Conditional Formatting dựa trên công thức logic (lớn hơn Upper Limit hoặc nhỏ hơn Lower Limit). Điều này giúp dữ liệu tự động được cảnh báo màu đỏ ngay khi bạn nhập số liệu mới vào bảng.
Nguồn tham khảo
- Microsoft Support: Hàm QUARTILE.INC trong Excel
- Investopedia: Định nghĩa về Outlier và Tác động thống kê
- Khan Academy: Cách xác định Outlier bằng quy tắc IQR
- Corporate Finance Institute: Các phương pháp xử lý dữ liệu bất thường trong tài chính
