agwebrunner.com

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 trung bình của một tập dữ liệu có thể phản ánh thực sự các giá trị của bạn. Excel cung cấp một số hàm hữu ích để giúp quản lý các ngoại lệ của bạn, vì vậy hãy cùng xem.

Một ví dụ nhanh

Trong hình ảnh bên dưới, dễ dàng phát hiện ra các giá trị ngoại lệ - giá trị của hai giá trị được gán cho Eric và giá trị 173 được gán cho Ryan. Trong một tập hợp dữ liệu như thế này, đủ dễ dàng để phát hiện và xử lý các 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. Có thể xác định các ngoại lệ và loại bỏ chúng khỏi các tính toán thống kê là điều quan trọng — và đó là những gì chúng ta sẽ xem xét cách thực hiện trong bài viết này.

Làm thế nào để tìm ra những điểm khác biệt trong dữ liệu của bạn

Để tìm các giá trị ngoại lai trong tập dữ liệu, chúng tôi sử dụng các bước sau:

1. Tính toán phần tư thứ nhất và thứ ba (chúng ta sẽ nói về những thứ đó chỉ sau một chút).
2. Đánh giá phạm vi giữa các phần tư (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).
3. Trả lại giới hạn trên và giới hạn dưới của phạm vi dữ liệu của chúng tôi.
4. 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 được 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 toán phần tư

Nếu bạn chia dữ liệu của mình thành các phần tư, mỗi tập hợp đó được gọi là một 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 là phần tư thứ hai, v.v. Chúng tôi thực hiện bước này trước tiên bởi vì định nghĩa được sử dụng rộng rãi nhất về giá trị ngoại lệ là một điểm dữ liệu có hơn 1,5 phạm vi liên phần tư (IQR) bên dưới phần tư thứ nhất và 1,5 phạm vi liên phần tư trên phần tư thứ ba. Để xác định những giá trị đó, trước tiên chúng ta phải tìm ra tứ phân vị là gì.

Excel cung cấp một hàm QUARTILE để tính toán các phần tư. Nó yêu cầu hai phần thông tin: mảng và phần tư.

= QUARTILE (mảng, phần tư)

Các mảng là phạm vi giá trị mà bạn đang đánh giá. Và quart là một số đại diện cho phần tư bạn muốn trả về (ví dụ: 1 cho phần tư thứ nhất, 2 cho phần tư thứ hai, v.v.).

Ghi chú: Trong Excel 2010, Microsoft đã phát hành hàm QUARTILE.INC và QUARTILE.EXC như những cải tiến cho 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 ta.

Để 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 danh sách các tùy chọn cho đối số phần tư.

Để tính toán phần tư thứ 3, chúng ta có thể nhập một công thức giống 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 phần tư được hiển thị trong các ô.

Bước hai: Đánh giá phạm vi liên phần tư

Phạm vi liên phần tư (hoặc IQR) là 50% giá trị ở giữa trong dữ liệu của bạn. Nó được tính bằng chênh lệch giữa giá trị phần tư thứ nhất và giá trị phần tư thứ ba.

Chúng tôi 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ứ ba:

= F3-F2

Bây giờ, chúng ta có thể thấy phạm vi liên phần tư của chúng ta được hiển thị.

Bước 3: Trả lại vòng dưới và vòng trên

Giới hạn dưới và giới hạn trên là các giá trị nhỏ nhất và lớn nhất của phạm vi 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ị ràng buộc này đều là giá trị ngoại lệ.

Chúng tôi sẽ tính toán giới hạn cậ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 một lần nữa với 1,5, nhưng lần này thêm vào nó đến điểm dữ liệu Q3:

= F3 + (1,5 * F4)

Bước 4: Xác định các yếu tố ngoại lệ

Bây giờ chúng tôi đã thiết lập tất cả dữ liệu cơ bản, đã đế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:

= HOẶC (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 cho biết một 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 giá trị ngoại lai khi tính trung bình trung bình

Sử dụng hàm QUARTILE cho phép chúng tôi 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ề hệ số ngoại lai. Tuy nhiên, khi tính giá trị trung bình trung bình cho một phạm vi 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 lai 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 nó bên dưới:

= TRIMMEAN (mảng, phần trăm)

Các mảng là phạm vi giá trị bạn muốn lấy trung bình. Các 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 chúng tôi để tính giá trị trung bình và loại trừ 20% các trường hợp ngoại lệ.

= TRIMMEAN (B2: B14, 20%)

Ở đó bạn có hai chức năng khác nhau để xử lý các trường hợp 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 tính toán như giá trị trung bình, Excel đều có một hàm để phù hợp với nhu cầu của bạn.