Giá trị ngoại lệ là giá trị cao hơn hoặc thấp hơn đáng kể so với hầu hết các giá trị trong dữ liệu của bạn. Khi sử dụng Excel để phân tích dữ liệu, các giá trị ngoại lai có thể làm sai lệch kết quả. Ví dụ: giá trị trung bình của một tập dữ liệu có thể phản ánh đúng giá trị của bạn. Excel cung cấp một vài hàm hữu ích để giúp quản lý các giá trị ngoại lai của bạn, vì vậy hãy xem qua
Một ví dụ nhanh
Trong hình bên dưới, các giá trị ngoại lệ khá dễ phát hiện—giá trị 2 được gán cho Eric và giá trị 173 được gán cho Ryan. Trong một tập dữ liệu như thế này, thật dễ dàng để phát hiện và xử lý các giá trị ngoại lệ đó theo cách thủ công
Trong một tập hợp dữ liệu lớn hơn, điều đó sẽ không xảy ra. Việc có thể xác định các giá trị ngoại lệ và loại bỏ chúng khỏi các tính toán thống kê là rất quan trọng—và đó là điều chúng ta sẽ xem xét cách thực hiện trong bài viết này
Cách tìm điểm ngoại lệ trong dữ liệu của bạn
Để tìm các ngoại lệ trong tập dữ liệu, chúng tôi sử dụng các bước sau
- Tính phần tư thứ nhất và thứ 3 [chúng ta sẽ nói một chút về chúng là gì]
- Đánh giá phạm vi liên vùng [chúng tôi cũng sẽ giải thích những điều này sâu hơn một chút]
- Trả về giới hạn trên và dưới của phạm vi dữ liệu của chúng tôi
- Sử dụng các giới hạn này để xác định các điểm dữ liệu bên ngoài
Phạm vi ô ở bên phải của tập dữ liệu nhìn thấy trong hình ảnh bên dưới sẽ được sử dụng để lưu trữ các giá trị này
Bắt đầu nào
Bước một. Tính các phần tư
Nếu bạn chia dữ liệu của mình thành các phần tư, thì mỗi tập hợp đó được gọi là phần tư. 25% số thấp nhất trong phạm vi tạo thành phần tư thứ nhất, 25% tiếp theo tạo thành phần tư thứ 2, v.v. Chúng tôi thực hiện bước này trước vì định nghĩa được sử dụng rộng rãi nhất về giá trị ngoại lệ là điểm dữ liệu lớn hơn 1. 5 phạm vi liên vùng [IQR] bên dưới phần tư thứ nhất và 1. 5 phạm vi liên vùng trên phần tư thứ 3. Để xác định các giá trị đó, trước tiên chúng ta phải tìm ra các phần tư là gì
Excel cung cấp hàm QUARTILE để tính phần tư. Nó yêu cầu hai mẩu thông tin. mảng và quart
=QUARTILE[array, quart]
Mảng là phạm vi giá trị mà bạn đang đánh giá. Và phần tư là một số đại diện cho phần tư mà bạn muốn trả lại [e. g. , 1 cho phần tư thứ nhất, 2 cho phần tư thứ 2, v.v.]
Ghi chú. Trong Excel 2010, Microsoft đã phát hành QUARTILE. INC và QUARTILE. Hàm EXC là cải tiến của hàm QUARTILE. QUARTILE tương thích ngược hơn khi làm việc trên nhiều phiên bản Excel
Hãy quay lại bảng ví dụ của chúng tôi
Để tính phần tư thứ nhất, chúng ta có thể sử dụng công thức sau trong ô F2
=QUARTILE[B2:B14,1]
Khi bạn nhập công thức, Excel sẽ cung cấp một danh sách các tùy chọn cho đối số quart
Để tính phần tư thứ 3, chúng ta có thể nhập một công thức như công thức trước đó vào ô F3, nhưng sử dụng ba thay vì một
=QUARTILE[B2:B14,3]
Bây giờ, chúng tôi đã có các điểm dữ liệu tứ phân vị được hiển thị trong các ô
Bước hai. Đánh giá phạm vi liên vùng
Phạm vi liên vùng [hoặc IQR] là 50% giá trị ở giữa trong dữ liệu của bạn. Nó được tính bằng sự khác biệt giữa giá trị phần tư thứ nhất và giá trị phần tư thứ 3
Chúng ta sẽ sử dụng một công thức đơn giản vào ô F4 để trừ phần tư thứ nhất khỏi phần tư thứ 3
=F3-F2
Bây giờ, chúng ta có thể thấy phạm vi liên vùng của mình được hiển thị
Bước thứ ba. Trả về giới hạn trên và dưới
Giới hạn dưới và trên là giá trị nhỏ nhất và lớn nhất của dải dữ liệu mà chúng tôi muốn sử dụng. Bất kỳ giá trị nào nhỏ hơn hoặc lớn hơn các giá trị giới hạn này đều là giá trị ngoại lệ
Chúng tôi sẽ tính giới hạn dưới trong ô F5 bằng cách nhân giá trị IQR với 1. 5 và sau đó trừ nó khỏi điểm dữ liệu Q1
=F2-[1.5*F4]
Ghi chú. Dấu ngoặc trong công thức này không cần thiết vì phần nhân sẽ tính trước phần trừ, nhưng chúng làm cho công thức dễ đọc hơn
Để tính giới hạn trên trong ô F6, chúng tôi sẽ nhân IQR với 1. 5 một lần nữa, nhưng lần này thêm nó vào điểm dữ liệu Q3
=F3+[1.5*F4]
Bước bốn. Xác định các ngoại lệ
Bây giờ chúng tôi đã thiết lập tất cả dữ liệu cơ bản của mình, đã đến lúc xác định các điểm dữ liệu bên ngoài của chúng tôi—những điểm thấp hơn giá trị giới hạn dưới hoặc cao hơn giá trị giới hạn trên
Chúng tôi sẽ sử dụng hàm OR để thực hiện kiểm tra logic này và hiển thị các giá trị đáp ứng các tiêu chí này bằng cách nhập công thức sau vào ô C2
=OR[B2$F$6]
Sau đó, chúng tôi sẽ sao chép giá trị đó vào các ô C3-C14 của chúng tôi. Giá trị TRUE biểu thị giá trị ngoại lệ và như bạn có thể thấy, chúng tôi có hai giá trị trong dữ liệu của mình
Bỏ qua các ngoại lệ khi tính trung bình trung bình
Sử dụng hàm QUARTILE, chúng ta hãy tính toán IQR và làm việc với định nghĩa được sử dụng rộng rãi nhất về giá trị ngoại lệ. Tuy nhiên, khi tính trung bình cộng cho một dải giá trị và bỏ qua các giá trị ngoại lệ, có một hàm nhanh hơn và dễ sử dụng hơn. Kỹ thuật này sẽ không xác định phần ngoại lệ như trước đây, nhưng nó sẽ cho phép chúng tôi linh hoạt với những gì chúng tôi có thể coi là phần ngoại lệ của mình
Hàm chúng ta cần có tên là TRIMMEAN và bạn có thể xem cú pháp của hàm này bên dưới
=TRIMMEAN[array, percent]
Mảng là phạm vi giá trị bạn muốn tính trung bình. Phần trăm là phần trăm điểm dữ liệu cần loại trừ khỏi đầu và cuối tập dữ liệu [bạn có thể nhập dưới dạng phần trăm hoặc giá trị thập phân]
Chúng tôi đã nhập công thức bên dưới vào ô D3 trong ví dụ của mình để tính giá trị trung bình và loại trừ 20% giá trị ngoại lệ
________số 8Ở đó bạn có hai chức năng khác nhau để xử lý các ngoại lệ. Cho dù bạn muốn xác định chúng cho một số nhu cầu báo cáo hay loại trừ chúng khỏi các phép tính chẳng hạn như giá trị trung bình, thì Excel đều có chức năng phù hợp với nhu cầu của bạn