Ban đầu được đăng bởi Brennen81Brennen81Tôi đã tự hỏi liệu có ai có thể giúp tôi với một công thức để tính toán độ lệch chuẩn của nhiều cột, không bao gồm các ngoại lệ? Tôi không có một lượng ngoại lệ mong muốn cụ thể để bỏ qua. Có thể là đáy và top 5 hoặc 10%. Tôi cũng đã đọc rằng có một cách để tính toán độ lệch chuẩn trong tập hợp các số trong độ lệch chuẩn 1,2 & 3 đầu tiên của toàn bộ tập dữ liệu?
Trong bảng đính kèm, bạn sẽ thấy 31 cột của các giá trị. Tôi đang tìm cách đặt một công thức lên trên mỗi cái sẽ tự động thay đổi khi tôi đánh lại dữ liệu bên dưới nó.
Rõ ràng, bạn sẽ ghi lại công thức sau [nhấn Ctrl+Shift+Enter thay vì chỉ nhập] vào A1 và sao chép qua AE1:array-enter the following formula [press ctrl+shift+Enter instead of just Enter] into A1 and copy across through AE1:= Stdevp [if [a2: a1000 "", if [a2: a1000> = out1, if [a2: a1000
trong đó "Out1" và "Out3" là giới hạn ngoại lệ thấp hơn và trên [được thảo luận dưới đây].
Công thức cho phép gần 1000 hàng dữ liệu, một số trong đó có thể trống. Điều đó sẽ phù hợp với nhu cầu khác nhau của bạn. Nhưng bạn luôn có thể thay đổi A1000 thành A10000 hoặc bất cứ điều gì.
STDEVP trả về STD thực tế của bộ dữ liệu thực tế. Thay đổi STDEVP thành STDEV nếu bạn có ý định ước tính STD của một tập dữ liệu lý thuyết.-----
In ngầm trong bài đăng của bạn có thể là câu hỏi: Làm thế nào để xác định giới hạn ngoại lệ?
Tôi khuyên bạn nên sử dụng phạm vi liên vùng [IQR], không phải STD Dev, và chắc chắn không phải là một tỷ lệ phần trăm tùy ý của dữ liệu.
Bảng dưới đây là một đoạn trích thể hiện thiết kế IQR, được áp dụng cho hai cột đầu tiên trong ví dụ của bạn [giờ là cột B và C thay vì A và B].
Để giải thích bên dưới, hãy tải xuống tệp tệp "StdDDEV đã loại trừ các Outliers.xlsx" và tham khảo các tệp đính kèm hình ảnh, sử dụng các liên kết bên dưới.
Ngoài ra, tham khảo "Cách tính các ngoại lệ" [bấm vào đây] [1]. Không chỉ là một lời giải thích tốt về phương pháp IQR mà còn thảo luận về sự khác biệt quan trọng giữa
xác định các ngoại lệ và loại trừ chúng. Nhưng trong thực tế, không có gì lạ khi loại trừ chúng tự động, nếu chỉ để cải thiện tính trung tâm của dữ liệu.Xác định một tỷ lệ phần trăm dữ liệu cố định là "ngoại lệ" là một phương pháp kém vì hai lý do. Đầu tiên, không có gì lạ khi có một số lượng lớn các ngoại lệ thấp và cao, như bảng dưới đây thể hiện. Thứ hai và quan trọng hơn, đơn giản là không phải là định nghĩa của "ngoại lệ". Không có ý nghĩa gì để loại trừ bất kỳ dữ liệu nào nếu tất cả dữ liệu "đóng" và tập trung tốt về trung bình.
Phương pháp STD Dev để xác định các ngoại lệ hoạt động tốt khi dữ liệu thường được phân phối [nghĩa là hình chuông]. Nhưng không rõ nó hoạt động tốt như thế nào với phân phối dữ liệu tùy ý.
Ví dụ, trong hình ảnh bên dưới, biểu đồ "Phân phối w/o SD Outliers", được trích từ bảng tính "Distrib 1", chứng minh rằng dữ liệu giảm vẫn không tập trung vào trung bình [đường màu đỏ].
Điều này là do dữ liệu gốc thậm chí không gần được phân phối bình thường; Xem biểu đồ "Phân phối gốc [tất cả dữ liệu]", được trích từ bảng tính "Orig Distrib 1". Do đó, phương pháp STD Dev không loại trừ một số dữ liệu cực đoan ở bên phải.
Ngược lại, phương pháp IQR để xác định các ngoại lệ hoạt động tốt với dữ liệu được phân phối tùy ý và thường. Biểu đồ "Phân phối w/o IQR ngoại lệ" chứng minh rằng dữ liệu giảm là "gần" và tập trung tốt xung quanh trung vị [đường màu đỏ].
Như đã được chứng minh trong bảng dưới đây, các giới hạn ngoại lệ dưới và trên được tính theo yếu tố Q1-IQR*và yếu tố Q3+IQR*, trong đó Q1 và Q3 là tứ phân 25% và 75% của dữ liệu.
Không có thỏa thuận về "yếu tố" IQR nên là gì. Nhiều tác giả đề xuất 1.5. Tôi sử dụng 1.7 vì nó gần hơn với 3 SD, mà nhiều tác giả đề xuất cho dữ liệu được phân phối bình thường.
Đối với các giới hạn bảo thủ hơn [rộng hơn; chấp nhận nhiều dữ liệu cực đoan hơn], tôi sẽ sử dụng 2 IQR, gần 4 SD cho dữ liệu được phân phối bình thường.
"N in" và "std in" là số lượng dữ liệu "không bao gồm dữ liệu" không bao gồm các ngoại lệ.
Một B C 1
N 87 91 2 q1 -0.34% -0.33% 3 q3 0.52% 0.65% 4 iqr 0.87% 0.98% 5 out1 -1.82% -2.00% 6 out3 2.00% 2.33% 7 #out1 6 3 8 #out3 3 7 9 %out 10.34% 10.99% 10 n in 78 81 11 SD trong 0.71% 0.74% 12
13
1 2 14
-1.18% -0.24% 15
0.46% 0.46% 99
0.48% 0.56% 100
1.35% 0.79% 101
-0.13% 102
0.13% 103
-0.18% 104
-0.35% ----- [1]
[1] //www.wikihow.com/Calculate-Outliers. Biểu đồ trong hình ảnh "Orig Distribe" là một bản toán của dữ liệu.
Hướng dẫn excel standard deviation exclude outliers - excel độ lệch chuẩn loại trừ ngoại lệ
Loại bỏ các ngoại lệ bằng cách sử dụng độ lệch chuẩn.Một cách khác, chúng ta có thể loại bỏ các ngoại lệ là tính toán ranh giới trên và ranh giới dưới bằng cách lấy 3 độ lệch chuẩn so với giá trị trung bình của các giá trị [giả sử dữ liệu thường được phân phối/Gaussian].Another way we can remove outliers is by calculating upper boundary and lower boundary by taking 3 standard deviation from the mean of the values [assuming the data is Normally/Gaussian distributed].
Số lượng độ lệch chuẩn được chỉ định được gọi là ngưỡng.Giá trị mặc định là 3. Phương pháp này có thể không phát hiện ra các ngoại lệ vì các ngoại lệ làm tăng độ lệch chuẩn.Càng nhiều ngoại lệ, độ lệch chuẩn càng bị ảnh hưởng.This method can fail to detect outliers because the outliers increase the standard deviation. The more extreme the outlier, the more the standard deviation is affected.