Bạn có thể sử dụng excel để tìm ngoại lệ?
|
Giá trị ngoại lệ là giá trị quan sát nằm cách xa các giá trị khác một cách bất thường trong tập dữ liệu. Các ngoại lệ có thể có vấn đề vì chúng có thể ảnh hưởng đến kết quả phân tích Show
Chúng tôi sẽ sử dụng tập dữ liệu sau trong Excel để minh họa hai phương pháp tìm ngoại lệ  Có liên quan. Cách tính giá trị trung bình không bao gồm giá trị ngoại lệ trong Excel Phương pháp 1. Sử dụng phạm vi liên vùngPhạm vi liên vùng (IQR) là sự khác biệt giữa phân vị thứ 75 (Q3) và phân vị thứ 25 (Q1) trong tập dữ liệu. Nó đo lường sự lây lan của 50% giá trị trung bình Chúng ta có thể định nghĩa một quan sát là một ngoại lệ nếu nó là 1. 5 lần phạm vi giữa các phần tư lớn hơn phần tư thứ ba (Q3) hoặc 1. Nhỏ hơn 5 lần phạm vi giữa các phần tư so với phần tư thứ nhất (Q1) Hình ảnh sau đây cho thấy cách tính phạm vi liên vùng trong Excel Tiếp theo, chúng ta có thể sử dụng công thức được đề cập ở trên để gán “1” cho bất kỳ giá trị nào là ngoại lệ trong tập dữ liệu Chúng tôi thấy rằng chỉ có một giá trị – 164 – hóa ra lại là giá trị ngoại lệ trong tập dữ liệu này Phương pháp 2. Sử dụng điểm số zĐiểm z cho bạn biết có bao nhiêu độ lệch chuẩn mà một giá trị đã cho so với giá trị trung bình. Chúng tôi sử dụng công thức sau để tính điểm z z = (X – μ) / σ ở đâu
Chúng ta có thể định nghĩa một quan sát là ngoại lệ nếu nó có điểm số z nhỏ hơn -3 hoặc lớn hơn 3 Hình ảnh sau đây cho thấy cách tính giá trị trung bình và độ lệch chuẩn cho tập dữ liệu trong Excel Sau đó, chúng tôi có thể sử dụng giá trị trung bình và độ lệch chuẩn để tìm điểm z cho từng giá trị riêng lẻ trong tập dữ liệu Sau đó, chúng tôi có thể gán “1” cho bất kỳ giá trị nào có điểm số z nhỏ hơn -3 hoặc lớn hơn 3 Sử dụng phương pháp này, chúng tôi thấy rằng không có ngoại lệ nào trong tập dữ liệu Ghi chú. Đôi khi điểm số z là 2. 5 được sử dụng thay vì 3. Trong trường hợp này, giá trị riêng của 164 sẽ được coi là giá trị ngoại lệ vì giá trị đó có điểm số z lớn hơn 2. 5. Khi sử dụng phương pháp điểm số z, hãy sử dụng phán đoán tốt nhất của bạn về giá trị điểm số z mà bạn cho là khác biệt Cách xử lý các ngoại lệNếu có một giá trị ngoại lai trong dữ liệu của bạn, thì bạn có một số tùy chọn 1. Đảm bảo giá trị ngoại lệ không phải do lỗi nhập dữ liệu Đôi khi một cá nhân chỉ cần nhập sai giá trị dữ liệu khi ghi dữ liệu. Nếu có giá trị ngoại lệ, trước tiên hãy xác minh rằng giá trị đã được nhập chính xác và đó không phải là lỗi 2. Loại bỏ ngoại lệ Nếu giá trị là một ngoại lệ thực sự, bạn có thể chọn xóa giá trị đó nếu giá trị đó có tác động đáng kể đến phân tích tổng thể của bạn. Chỉ cần đảm bảo đề cập trong báo cáo hoặc phân tích cuối cùng của bạn rằng bạn đã loại bỏ một ngoại lệ 3. Gán một giá trị mới cho ngoại lệ Nếu ngoại lệ là kết quả của lỗi nhập dữ liệu, bạn có thể quyết định gán một giá trị mới cho nó, chẳng hạn như Trong khi xử lý các tập dữ liệu trong Excel, thường phải tìm các giá trị ngoại lệ từ tập dữ liệu. Trong bài viết này, chúng ta sẽ tìm hiểu 5 phương pháp hiệu quả về cách tìm các giá trị ngoại lệ trong Microsoft Excel Mục lục Tải sách bài tập thực hànhTìm ngoại lệ trong Excel. xlsx Các ngoại lệ là gì?Trong khi xử lý các tập dữ liệu, theo từ “Outliers”, chúng tôi muốn nói đến dữ liệu cao hơn hoặc thấp hơn nhiều so với dữ liệu khác trong tập dữ liệu. Các ngoại lệ có thể nhanh chóng làm hỏng phân tích của bạn nếu chúng không được tính đến. Nói một cách dễ hiểu, việc tìm ra các giá trị ngoại lệ là rất quan trọng đối với bất kỳ phân tích nào. Để minh họa, hãy nghĩ về một chiếc xe buýt chở đầy 10 đứa trẻ 8 tuổi. Nhưng tài xế xe buýt là một ông già, 65 tuổi. Bây giờ, nếu chúng ta muốn tính tuổi trung bình của những người trên chiếc xe buýt đó, chúng ta sẽ có khoảng 13 năm, đó là thông tin sai lệch 5 cách để tìm ngoại lệ trong ExcelTrong bài viết này, chúng ta sẽ tìm hiểu 5 cách dễ dàng để tìm các giá trị ngoại lệ trong Excel. Các phương pháp này nhanh chóng, chính xác, dễ sử dụng và phù hợp để xử lý cả tập dữ liệu có kích thước nhỏ và lớn 1. Sử dụng tùy chọn sắp xếp dữ liệu để tìm ngoại lệChỉ cần sắp xếp dữ liệu và duyệt thủ công một số kết quả trên cùng và dưới cùng là một cách tiếp cận dễ dàng để tìm ra các giá trị ngoại lệ trong Excel Hãy để tôi giải thích cho bạn với một ví dụ Trong bảng dưới đây, có dữ liệu của một số Vận động viên bơi lội về Thời gian bơi của họ tính bằng giây. Bây giờ, hãy sắp xếp chúng và cố gắng tìm ra những điểm khác biệt Bước 1. Chọn tiêu đề Đầu tiên, chọn tiêu đề của cột mà bạn muốn sắp xếp. Trong trường hợp này, đó là cột Thời gian (ô C4) Bước 2. Chọn tab Trang chủ Tiếp theo, nhấp vào tab Trang chủ. Nó ở phía trên bên trái màn hình của bạn Bước 3. Chuyển đến tùy chọn Sắp xếp & Lọc Sau đó, nhấp vào Sắp xếp & Lọc. Bạn có thể tìm thấy nó trong nhóm chỉnh sửa ở phía trên bên phải màn hình của bạn Bước 4. Chọn tùy chọn sắp xếp tùy chỉnh Sau đó, nhấp vào Sắp xếp tùy chỉnh từ trình đơn thả xuống Bước 5. Hộp thoại Chỉnh sửa Sắp xếp Trong hộp thoại Sắp xếp, từ trình đơn thả xuống Sắp xếp theo chọn Thời gian (s) Bây giờ, từ trình đơn thả xuống Đơn hàng, chọn Lớn nhất đến Nhỏ nhất Cuối cùng bấm OK Điều này sẽ sắp xếp dữ liệu của bạn từ giá trị cao nhất đến giá trị thấp nhất. Giờ đây, bạn có thể quét các giá trị ngoại lệ ở đầu và cuối cột Ở đây, bạn có thể thấy rằng con số đầu tiên lớn hơn đáng kể so với những con số còn lại và hai con số cuối cùng thấp hơn đáng kể. Vì vậy, chúng ta có thể xác định chúng là Ngoại lệ Ghi chú. Phương pháp này hoạt động tốt nếu bạn đang xử lý một tập dữ liệu nhỏ. Nó không phải là một phương pháp khoa học nhưng nó có thể hoàn thành công việc. Đọc thêm. Cách tìm giá trị ngoại lệ trong phân tích hồi quy trong Excel (3 cách dễ dàng) 2. Áp dụng Hàm QUARTILE trong ExcelPhương pháp này tốt hơn phương pháp trước. Trong thống kê, tứ phân vị có nghĩa là một phần tư của tập dữ liệu. Ví dụ: nếu bạn có 40 điểm dữ liệu trong một tập dữ liệu, thì 10 giá trị dưới cùng sẽ là phần tư thứ nhất (Q1), 10 điểm dữ liệu tiếp theo sẽ là phần tư thứ hai (Q2), v.v. Để tìm các giá trị ngoại lệ, chúng ta cần tính phần tư thứ nhất, phần tư thứ 3 và Phạm vi giữa các phần tư. Hãy tiến hành từng bước Bước 1. Tính phần tư thứ nhất và thứ ba Đầu tiên, chúng tôi sẽ sử dụng để tính toán các phần tư. Nó cần hai mẩu thông tin. Phạm vi dữ liệu bạn muốn chọn và phần tư dữ liệu bạn muốn Để tính Q1, chúng ta có thể sử dụng công thức sau trong ô G4
Sau đó nhấn ENTER và đây là Q1 của chúng tôi Tương tự, chúng ta có thể tìm giá trị phần tư thứ 3 chỉ bằng cách thay đổi số từ 1 thành 3 trong ô G5 Để tính Q3, chúng ta có thể sử dụng công thức sau trong ô G5
Bước 2. Tính phạm vi giữa các phần tư Bây giờ, chúng ta sẽ tìm Phạm vi giữa các phần tư (IQR) bằng cách trừ phần tư thứ nhất khỏi phần tư thứ 3. Vì vậy, trong ô G6, chúng ta có thể sử dụng công thức sau
Nhấn ENTER và chúng tôi sẽ nhận được IQR của mình Bước 3. Tính giới hạn trên và giới hạn dưới Để tính giới hạn trên, chúng ta cần cộng giá trị phần tư thứ 3 với 1. gấp 5 lần IQR. Chúng ta có thể sử dụng công thức sau trong ô G7
Nhấn ENTER và bạn sẽ nhận được Giới hạn trên Để tính Giới hạn dưới, chúng ta cần trừ 1. 5 lần IQR từ Tứ phân vị đầu tiên. Công thức cho giới hạn dưới được đưa ra dưới đây
Bây giờ hãy nhấn ENTER và bạn sẽ nhận được Giới hạn dưới Bước 4. Xác định các ngoại lệ Bây giờ chúng tôi có tất cả dữ liệu cần thiết để tìm ra các ngoại lệ. Chúng ta sẽ sử dụng ở đây. Trong hàm OR, chúng ta cần đưa ra một số điều kiện logic. Nếu các điều kiện này được thỏa mãn, nó sẽ trả về TRUE và nếu các điều kiện không được thỏa mãn, nó sẽ trả về FALSE Bây giờ, bất kỳ giá trị nào lớn hơn Giới hạn trên hoặc nhỏ hơn Giới hạn dưới của chúng tôi đều là giá trị ngoại lệ. Trong ô D5, chúng ta có thể sử dụng công thức sau
Bây giờ hãy nhấn ENTER và chúng ta sẽ nhận được kết quả cho điểm dữ liệu đầu tiên Ghi chú. Chúng ta cần sử dụng tham chiếu ô tuyệt đối khi chọn các ô Giới hạn trên và Giới hạn dưới. Bởi vì nếu không thì tham chiếu ô sẽ thay đổi khi chúng ta kéo công thức để tìm tất cả các giá trị ngoại lệ. Chỉ cần nhấn F4 sau khi chọn ô. Điều này sẽ làm cho nó trở thành một tham chiếu ô tuyệt đối. Bây giờ chỉ cần kéo lên đến cuối tập dữ liệu hoặc chỉ cần nhấp đúp vào nó Các ô trong đó TRUE được hiển thị là Ngoại lệ của chúng tôi Ở đây, thời gian của Jonathan và Peter thấp hơn Giới hạn dưới của chúng tôi và thời gian của Andy cao hơn Giới hạn trên của chúng tôi. Vì vậy, ba giá trị này là Giá trị ngoại lệ Đọc thêm. Cách tính giá trị ngoại lệ trong Excel (5 cách dễ dàng) 3. Sử dụng các hàm LỚN và NHỎ trong Excel để tìm các giá trị ngoại laiĐiều này rất hữu ích khi chúng ta xử lý một tập dữ liệu lớn. Bằng cách sử dụng hàm và SMALL trong Excel, chúng ta có thể trích xuất 3 dữ liệu cao nhất hoặc 3 dữ liệu thấp nhất. Chúng tôi có thể trích xuất thêm dữ liệu nếu chúng tôi muốn. Sau đó, chúng tôi có thể xem xét những dữ liệu đó và xem liệu có bất kỳ ngoại lệ nào không Hàm LARGE cần hai mẩu thông tin. Phạm vi dữ liệu từ nơi trích xuất và lượng dữ liệu bạn muốn trích xuất tại một thời điểm Bước 1. Trích xuất 3 giá trị hàng đầu Chúng ta có thể sử dụng công thức sau trong ô E5. Điều này sẽ mang lại 3 giá trị hàng đầu
Nhấn ENTER và chúng tôi sẽ nhận được 3 giá trị hàng đầu của chúng tôi Ghi chú. Nếu chúng tôi muốn mang lại 5 giá trị hàng đầu, thì ROW($1. 3) sẽ được đổi thành ROW($1. 5) Bước 2. Trích xuất 3 giá trị nhỏ nhất Chúng ta có thể sử dụng công thức sau trong ô F5. Điều này sẽ mang lại 3 giá trị dưới cùng
Nhấn ENTER và chúng tôi sẽ nhận được 3 giá trị dưới cùng của chúng tôi Bước 3. Xác định các ngoại lệ Bây giờ, chúng ta có thể xác định các Ngoại lệ bằng cách xem 3 giá trị trên cùng và dưới cùng của mình 4. Áp dụng trung bình và độ lệch chuẩn để tìm ngoại lệMột cách hiệu quả khác để tìm Giá trị ngoại lệ trong Excel là áp dụng khái niệm Giá trị trung bình và Độ lệch chuẩn Mean chỉ đơn giản là giá trị trung bình của một tập dữ liệu. Mặt khác, Độ lệch chuẩn là thước đo mức độ các điểm trong phân phối lệch khỏi giá trị trung bình của phân phối. Nói chung, một nguyên tắc chung là nếu một giá trị gấp 2 đến 3 lần độ lệch chuẩn của phân phối, thì nó được coi là Ngoại lệ Hãy tiến hành từng bước một Bước 1. Tính trung bình Để tính Mean chúng ta cần sử dụng hàm AVERAGE. Chức năng này chỉ cần một phần thông tin, đó là phạm vi dữ liệu. Chúng ta có thể sử dụng công thức sau tại ô G4 để xác định giá trị trung bình của tập dữ liệu của mình ________số 8_______ Bây giờ nhấn ENTER, và chúng ta sẽ có giá trị trung bình của dữ liệu Bước 2. Tính độ lệch chuẩn Trong Excel để tính độ lệch chuẩn, chúng ta có thể sử dụng STDEV. chức năng S. Nó chỉ yêu cầu phạm vi tập dữ liệu mà chúng tôi muốn tính độ lệch chuẩn. Chúng ta có thể sử dụng công thức sau tại ô G5 để xác định độ lệch chuẩn của phân phối
Bây giờ nhấn ENTER Bước 3. Đánh giá giới hạn trên Công thức để đánh giá Giới hạn trên là Trung bình + (2 * Độ lệch chuẩn). Chúng ta có thể sử dụng công thức sau tại ô G6 để xác định Giới hạn trên
Nhấn ENTER và đây là Giới hạn trên của chúng tôi Bước 4. Đánh giá giới hạn dưới Công thức để đánh giá Giới hạn dưới là Trung bình-(2*Độ lệch chuẩn). Chúng ta có thể sử dụng công thức sau tại ô G7 để xác định Giới hạn dưới =G4-(2*G5) Nhấn ENTER và đây là Giới hạn dưới của chúng tôi Bước 5. Xác định các ngoại lệ Bây giờ, chúng ta sẽ sử dụng lại hàm OR. Vì vậy, logic mà chúng tôi sẽ sử dụng ở đây là bất kỳ giá trị nào cao hơn Giới hạn trên hoặc thấp hơn Giới hạn dưới của chúng tôi đều là Ngoại lệ. Chúng ta có thể sử dụng công thức sau trong ô D5
Ghi chú. Chúng ta cần sử dụng tham chiếu ô tuyệt đối khi chọn các ô Giới hạn trên và Giới hạn dưới. Bởi vì nếu không thì tham chiếu ô sẽ thay đổi khi chúng ta kéo công thức để tìm tất cả các giá trị ngoại lệ. Chỉ cần nhấn F4 sau khi chọn ô. Điều này sẽ làm cho nó trở thành một tham chiếu ô tuyệt đối. Cuối cùng, để lấy các giá trị còn lại, kéo Fill Handle hoặc nhấp đúp vào nó Do đó, các ô đang hiển thị TRUE là các Giá trị ngoại lệ của chúng tôi Đọc thêm. Cách tìm giá trị ngoại lệ với độ lệch chuẩn trong Excel (với các bước nhanh) 5. Sử dụng Z-Score để tìm ngoại lệMột cách hiệu quả khác để tìm các giá trị ngoại lệ trong Excel là sử dụng Z-Score. Giá trị z còn được gọi là Giá trị chuẩn hóa. Một lần nữa chúng ta sẽ cần Giá trị trung bình và Độ lệch chuẩn của tập dữ liệu. Công thức tính điểm Z của dữ liệu được đưa ra dưới đây Z=(X-Mean) / Độ lệch chuẩn Chúng ta cũng có thể sử dụng hàm STANDARDIZE trong Excel để tính trực tiếp chỉ số Z của dữ liệu Bước 1. Tính trung bình Ban đầu, chúng ta có thể sử dụng công thức sau trong ô H4 để tính Giá trị trung bình của tập dữ liệu của mình ________số 8_______ Sau đó nhấn ENTER ta sẽ có Mean Bước 2. Tính độ lệch chuẩn Tiếp theo, chúng ta có thể sử dụng công thức sau trong ô H5 để tính Độ lệch chuẩn của tập dữ liệu của mình
Sau đó nhấn ENTER Bước 3. Đánh giá Điểm Z Chức năng STANDARDIZE cần 3 mẩu thông tin để thực thi. Thông tin thứ nhất là dữ liệu mà chúng tôi đang xác định điểm Z, thông tin thứ 2 là Giá trị trung bình và thông tin thứ 3 là Độ lệch chuẩn Vì vậy, chúng tôi sẽ sử dụng công thức sau trong ô D5 để tính điểm Z
Ghi chú. Chúng ta cần sử dụng tham chiếu ô tuyệt đối khi chọn các ô của Độ lệch chuẩn và Giá trị trung bình. Bởi vì nếu không thì tham chiếu ô sẽ thay đổi khi chúng ta kéo công thức để tìm tất cả các giá trị ngoại lệ. Chỉ cần nhấn F4 sau khi chọn ô. Điều này sẽ làm cho nó trở thành một tham chiếu ô tuyệt đối. Kéo Fill Handle đến cuối tập dữ liệu, bạn sẽ nhận được phần còn lại của Z-scores Bước 4. Xác định ngoại lệ Một lần nữa, chúng ta sẽ sử dụng hàm OR. Về mặt lý thuyết, để một giá trị trở thành Ngoại lệ, Điểm Z của nó phải lớn hơn +3 hoặc nhỏ hơn -3. Nhưng trong thực tế, các giá trị z gần bằng 3 khá xa so với giá trị trung bình và chúng ta có thể coi chúng là Giá trị ngoại lai Vì vậy, logic ở đây là bất kỳ điểm z nào lớn hơn +2. 5 hoặc nhỏ hơn -2. 5 sẽ là một ngoại lệ Chúng ta có thể sử dụng công thức sau trong ô E5
Cuối cùng, sau khi kéo Fill Handle, bạn sẽ nhận được kết quả sau Trong ảnh chụp màn hình ở trên, Giá trị ngoại lệ của chúng tôi là ô trong cột E có giá trị TRUE Đọc thêm. Cách tìm giá trị ngoại lai bằng cách sử dụng Điểm Z trong Excel (với các bước nhanh) Phần kết luậnCảm ơn bạn đã dành thời gian để đọc bài viết này. Tôi hy vọng rằng các quy trình được mô tả ở trên sẽ có thể xử lý mọi khó khăn khi tìm các giá trị ngoại lệ trong tập dữ liệu Excel. Vui lòng để lại nhận xét nếu bạn có bất kỳ câu hỏi hoặc bất kỳ đề xuất nào để cải thiện bài viết Cách tốt nhất để tìm ngoại lệ là gì?Bạn có thể chọn trong số bốn cách chính để phát hiện các giá trị ngoại lai. . Sắp xếp các giá trị của bạn từ thấp đến cao và kiểm tra các giá trị tối thiểu và tối đa Trực quan hóa dữ liệu của bạn bằng biểu đồ hộp và tìm kiếm các giá trị ngoại lai Sử dụng phạm vi liên vùng để tạo hàng rào cho dữ liệu của bạn Sử dụng các thủ tục thống kê để xác định các giá trị cực đoan Excel có thể loại bỏ các ngoại lệ không?Xóa các giá trị ngoại lai
. Bằng cách này, nó sẽ không làm sai lệch phân tích của bạn. Đó là một giải pháp khả thi hơn khi bạn có bộ dữ liệu lớn và việc xóa một vài giá trị ngoại lệ sẽ không ảnh hưởng đến phân tích tổng thể. The easiest way to remove outliers from your data set is to simply delete them. This way it won't skew your analysis. It's a more viable solution when you have large datasets and deleting a couple of outliers won't impact the overall analysis. |
