Trung bình Excel không bao gồm các ngoại lệ
|
báo trước. Chỉ vì một giá trị là ngoại lệ, điều đó không nhất thiết có nghĩa là nó phải bị loại trừ. Thông thường, một giá trị được xác định là ngoại lệ chỉ nên được loại trừ nếu đó là lỗi. Điều đó nói rằng, nhiều người thực sự tùy tiện loại trừ các ngoại lệ, với mục đích cố gắng xác định một số "trung tâm" của dữ liệu Show
Tôi tình cờ gặp một câu hỏi thú vị vào ngày hôm trước, đó là; Bây giờ, nếu tất cả những gì chúng ta cần là một giá trị trung bình đơn giản đáp ứng các tiêu chí, chúng ta có thể sử dụng công thức AVERAGEIF hoặc AVERAGEIFS Hoặc nếu chúng ta chỉ muốn loại trừ các ngoại lệ, chúng ta có thể sử dụng hàm TRIMMEAN trả về giá trị trung bình (trung bình) của tỷ lệ bên trong của các giá trị Cú pháp là TRIMMEAN(array, percent) Trong đó mảng là phạm vi chứa các giá trị của bạn và phần trăm là số điểm dữ liệu phân số mà bạn muốn loại trừ khỏi đầu và cuối tập dữ liệu của mình Nhưng chúng tôi muốn tìm AVERAGEIF và TRIMMEAN một cách hiệu quả Thật không may, cặp này không hòa hợp lắm trong cùng một ô nên tôi có một giải pháp thay thế và nó không được gọi là TRIMMEANIF…. nhưng có lẽ có một ý tưởng Tự ghi nhận; Ok, hãy lấy danh sách này làm ví dụ của chúng tôi Và giả sử chúng ta muốn tìm Đơn vị trung bình cho Kích thước 8 và loại trừ 40% trên cùng và dưới cùng Ghi chú. thông thường bạn sẽ không loại trừ 40% mẫu của mình nhưng vì mẫu của tôi quá nhỏ (vì vậy nó sẽ dễ dàng vừa với màn hình) nên tôi đã phải tạo tỷ lệ để loại trừ khá lớn Đây là công thức =TRIMMEAN(IF(B2:B12=8,C2:C12),40%) Đây là một công thức mảng nên bạn cần nhập nó bằng CTRL+SHIFT+ENTER Trong tiếng Anh nó đọc. Tìm MEAN (trung bình) của các giá trị trong khoảng C2. C12 , trong đó kích thước nằm trong phạm vi B2. B12 là 8, ồ, và nhân tiện, hãy bỏ qua 40% giá trị trên cùng và dưới cùng. Công thức IF lồng nhau của tôi trả về một mảng Đơn vị có kích thước 8 và nếu chúng ta xem qua cách công thức đánh giá, chúng ta có thể thấy nó trông như thế này Bước 1. Đánh giá kiểm tra logic của công thức IF và mở rộng phạm vi C2. C12 =TRIMMEAN(IF({TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, {2;4;5;6;9;4;10;8;9;4;1}), 40%) Bước 2. tạo một mảng các giá trị đánh giá là TRUE =TRIMMEAN({2;4;5;6;9}, 40%)Bước 3. loại bỏ các ngoại lệ khỏi mảng giá trị =TRIMMEAN({4;5;6})Bước 4. tìm giá trị trung bình (trung bình) của các giá trị còn lại =5 Ghi chú. nếu bạn chạy công thức này thông qua công cụ Đánh giá Công thức, bạn sẽ thấy nó hoạt động theo các bước ở trên 3 giá trị hàng đầu trung bìnhMột thành viên khác của nhóm Trung bình mà bạn có thể thấy hữu ích là công thức AVERAGE LARGE tìm giá trị trung bình của 3 giá trị hàng đầu Hàm TRIMMEAN trong Excel trả về giá trị trung bình (trung bình cộng) sau khi loại trừ một tỷ lệ phần trăm nhất định i. e. loại bỏ khỏi các số cao và thấp trong một tập dữ liệu nhất định cú pháp = TRIMMEAN(mảng, phần trăm) Thông số Mảng – phạm vi ô lấy trung bình Giá trị trả về Trung bình cộng của các số sau khi loại trừ phần trăm khỏi mảng đã cho Ghi chú chính
Ví dụ (Hình trên) Giả sử rằng một danh sách các chữ số nằm trong một cột và người dùng muốn tìm giá trị trung bình của các giá trị số sau khi loại trừ một số số nhất định, công thức có thể là =TRIMMEAN(B5. B15,20%) Đầu tiên nó đếm mảng (B5 đến B15) i. e. 11 thì nó loại trừ 20% của mảng(11) i. e. 2. 2 thì chia 2 cho 1. 1 nên số lớn nhất (15) và nhỏ nhất (4) trong dãy sẽ không được tính. Do đó, hàm trả về trung bình là 8. 89 sau khi trừ số đầu và số cuối Trung bình của bạn không có ý nghĩa? . Điều này sẽ loại bỏ bất kỳ giá trị bất thường nào và đưa ra mức trung bình có ý nghĩa hơn Mục lụcMức trung bình của YouTube không bao gồm các trường hợp ngoại lệMức trung bình trong đó các giá trị ngoại lệ đưa ra mức trung bình không thực tếNhư được hiển thị bên dưới, mức trung bình được hiển thị là 2. 89, nhưng về mặt trực quan, chúng ta có thể thấy rằng 65 đang đưa ra một mức trung bình không thực tế. Chỉ cần nhìn vào các con số, trung bình sẽ nhỏ hơn 2 và có thể ít hơn đáng kể Trong Excel, một cách giải quyết vấn đề này là sử dụng hàm TRIMMEAN Hàm tính giá trị trung bình không bao gồm giá trị ngoại lệ trong MS ExcelVì vậy, dưới đây chúng tôi đã sử dụng hàm TRIMMEAN trong ô B35. Như được hiển thị, hàm chỉ cần biết vị trí cần tìm (B2 đến B29) và phần trăm giá trị ngoại lệ cần loại trừ (chúng tôi đã tham chiếu đến ô D35 để hiển thị logic) Kết quả của trung bình này là 0. 61 thay vì mức trung bình trước đó là 2. 89 có ý nghĩa hơn Hiểu% trong TRIMMEANSự phức tạp thực sự duy nhất trong TRIMMEAN là tỷ lệ phần trăm có nghĩa là gì Nói một cách đơn giản nhất, đó là số lượng giá trị cần loại bỏ, chia đều từ đầu và cuối của phạm vi giá trị. Nhưng có một số điều cần nhận ra về điều này đáng chú ý là % dẫn đến một số số cần loại trừ, NHƯNG số này được làm tròn XUỐNG thành bội số gần nhất của . 2! Vì vậy, bên dưới, khi chúng tôi sử dụng 10% (ô D35), kết quả là 2. 8 (10% trong số 28 giá trị trong B2 đến B29). Excel không thể sử dụng 2. 8 để nó làm tròn XUỐNG đến bội số gần nhất của 2 (ô G35). Bây giờ nó biết loại trừ 2 mục, giá trị lớn nhất và giá trị nhỏ nhất là 65 và bất kỳ mục nào trong số 0 Công thức để loại trừ các ngoại lệ là gì?Bạn có thể làm điều này bằng cách làm theo công thức bên dưới. Giới hạn phạm vi dưới = Q1 – (1. 5* IQR) . Thực chất đây là 1. 5 lần phạm vi liên vùng trừ đi từ phần tư thứ nhất của bạn. Giới hạn phạm vi cao hơn = Q3 + (1. 5*IQR) Đây là 1. 5 lần IQR+ tứ phân vị 3.
Trung bình có bao gồm các ngoại lệ không?Mean, median và mode là thước đo xu hướng trung tâm. Giá trị trung bình là thước đo duy nhất của xu hướng trung tâm luôn bị ảnh hưởng bởi một giá trị ngoại lai . |
