Cách (và tại sao) để sử dụng Hàm Outliers trong Excel
Một ngoại lệ là một 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 ngoại lệ có thể làm lệch kết quả. Ví dụ: trung bình trung bình của một tập dữ liệu có thể thực sự phản ánh các giá trị của bạn. Excel cung cấp một vài chức năng hữu ích để giúp quản lý các ngoại lệ của bạn, vì vậy hãy xem.
Một ví dụ nhanh
Trong hình ảnh bên dưới, các ngoại lệ rất dễ phát hiện - giá trị của hai được gán cho Eric và giá trị của 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 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à rất quan trọng - và đó là những gì chúng ta sẽ xem xét cách làm trong bài viết này.
Cách tìm các 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 toán các phần tư thứ 1 và thứ 3 (chúng ta sẽ nói về những thứ đó chỉ trong một chút).
- Đá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 một chút nữa).
- 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 bên dưới sẽ được sử dụng để lưu trữ các giá trị này.
Băt đâu nao.
Bước một: Tính toán 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 bộ đó đượ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% phần tư tiếp theo, v.v. Chúng tôi thực hiện bước này trước tiên vì định nghĩa được sử dụng rộng rãi nhất của một ngoại lệ là một điểm dữ liệu có nhiều hơn 1,5 phạm vi liên mã hóa (IQRs) bên dưới phần tư thứ nhất và 1,5 phạm vi giữa các phần tư trên phần tư thứ ba. Để 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 toán các phần tư. Nó đòi hỏi hai phần thông tin: mảng và quart.
= QUARTILE (mảng, quart)
Các mảng là phạm vi của các 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 1thứ phần tư, 2 cho phần tư thứ 2, v.v.).
Chú thích: Trong Excel 2010, Microsoft đã phát hành các hàm QUARTILE.INC và QUARTILE.EXC dưới dạng cải tiến cho chức năng 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 trở lại bảng ví dụ của chúng tôi.
Để tính 1thứ Phần 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ố quart.
Để tính 3lần thứ phần tư, chúng ta có thể nhập một công thức như công thức trước trong ô F3, nhưng sử dụng ba thay vì một.
= QUARTILE (B2: B14,3)
Bây giờ, chúng ta đã 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 vùng
Phạm vi liên vùng (hoặc IQR) là 50% giá trị trung bình trong dữ liệu của bạn. Nó được tính là chênh lệch giữa giá trị phần tư thứ nhất và giá trị phần tư thứ ba.
Chúng ta sẽ sử dụng một công thức đơn giản vào ô F4 trừ đi 1thứ phần tư từ 3lần thứ phần tư:
= F3-F2
Bây giờ, chúng ta có thể thấy phạm vi liên vùng của chúng tôi được hiển thị.
Bước ba: Trả về giới hạn trên và dưới
Các giới hạn dưới và 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à ngoại lệ.
Chúng tôi sẽ tính giới hạn 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)
Chú thích: Các dấu ngoặc trong công thức này là không cần thiết vì phần nhân sẽ tính toán 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 lên 1,5 lần nữa, nhưng lần này thêm vào đến điểm dữ liệu quý 3:
= F3 + (1,5 * F4)
Bước bốn: Xác định các ngoại lệ
Bây giờ chúng ta đã 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 ta - các điểm dữ liệu thấp hơn giá trị ràng buộc thấp hơn hoặc cao hơn giá trị ràng buộc 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 biểu thị ngoại lệ và như bạn có thể thấy, chúng tôi có hai dữ liệu.
Bỏ qua các ngoại lệ 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ề ngoại lệ. Tuy nhiên, khi tính trung bình trung bình cho một phạm vi các giá trị và bỏ qua các 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 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ể xem xét phần ngoại lệ của mình.
Hàm chúng ta cần có tên là TRIMMESE và bạn có thể thấy cú pháp của hàm bên dưới:
= TRIMMESE (mảng, phần trăm)
Các mảng là phạm vi của các giá trị bạn muốn trung bình. Các phần trăm là tỷ lệ phần trăm của các điểm dữ liệu cần loại trừ từ trên cùng và dưới cùng của tập dữ liệu (bạn có thể nhập nó 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 dưới đây vào ô D3 trong ví dụ của chúng tôi để tính trung bình và loại trừ 20% số ngoại lệ.
= TRIMMESE (B2: B14, 20%)
Ở đó 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 hoặc loại trừ chúng khỏi các tính toán như mức trung bình, Excel có một chức năng phù hợp với nhu cầu của bạn.
