Hướng dẫn cách xử lý lỗi dữ liệu dạng số trong Excel

Mục đích của chúng ta khi học Excel đó là có thể tổ chức dữ liệu tốt, từ đó lập được các báo cáo theo yêu cầu công việc. Thế nhưng chúng ta cũng gặp phải vô vàn những thắc mắc như:

• Tổ chức dữ liệu như thế nào là tốt?
• Làm thế nào để kiểm tra dữ liệu trên Excel để đảm bảo tính chính xác của dữ liệu?
• Làm thế nào để lập được các báo cáo trên Excel?
• Báo cáo thế nào là đẹp, khoa học?
• …

Nhằm giúp các bạn hình thành tư duy tốt trong việc tổ chức dữ liệu và lập báo cáo trên Excel, Học Excel Online xin giới thiệu với các bạn seri bài viết về “Kỹ năng tổ chức, quản lý dữ liệu và lập báo cáo trên Excel”. Bài viết của tác giả Dương Mạnh Quân – Giảng viên của Học Excel Online, dựa trên những kinh nghiệm giảng dạy trong thực tế của thầy liên quan tới nội dung này. Mời các bạn cùng tìm hiểu nhé.

Bài 4: Hướng dẫn cách xử lý lỗi dữ liệu dạng số (Number) trong Excel

Việc sai dữ liệu dạng số có ảnh hưởng rất lớn trong quá trình tính toán và lập báo cáo. Bởi kết quả của báo cáo phụ thuộc rất lớn vào nội dung này. Lỗi về dữ liệu dạng số có thể kể tới như:

• Sai về định dạng dữ liệu: có phần thập phân hay không, dữ liệu là % hay dạng số thông thường, có lẫn lộn giữa dữ liệu dạng ngày tháng (date) vào trong dữ liệu dạng số không (vì dữ liệu dạng ngày tháng về bản chất cũng là dạng số)
• Sai về thao tác nhập dữ liệu: Trong quá trình nhập có lẫn 1 ký tự text vào chung với ký tự số, dẫn tới Excel sẽ coi đó là dữ liệu Text thay vì dạng Number

Vậy làm thế nào để phát hiện những lỗi dữ liệu dạng số đã có trong 1 bảng dữ liệu? Chúng ta cùng tìm hiểu nhé.

1. Phát hiện lỗi dữ liệu dạng số với bộ lọc tự động trong Excel (công cụ AutoFilter)

Về cách mở công cụ AutoFilter thì mình đã hướng dẫn ở trong bài học trước, mời các bạn xem lại bài:

Hướng dẫn cách xử lý lỗi dữ liệu ngày tháng trong Excel

2. Nguyên tắc sắp xếp dữ liệu dạng số trong AutoFilter

Với dữ liệu dạng số sẽ được tự động sắp xếp trong cửa sổ dữ liệu lọc của AutoFilter theo quy tắc:

• Tăng dần từ nhỏ tới lớn (nếu có số âm thì sẽ tính cả từ phần số âm trở đi chứ không phải bắt đầu từ số 0)
• Nếu có chứa ký tự không phải dạng số thì sẽ nằm ở cuối danh sách lọc tự động này.

Trong hình trên chúng ta có thể thấy số 124,235 là số nhỏ hơn 12,489,621 nhưng lại được xếp phía sau => Số đó đang bị sai loại dữ liệu bởi vì có chứa dấu cách

3. Nguyên tắc dữ liệu dạng Number có thể tính toán được

Ngoài cách sử dụng AutoFilter để phát hiện dữ liệu lỗi, chúng ta có thể dựa trên 1 nguyên tắc nữa để phát hiện dữ liệu lỗi:

Dữ liệu dạng Number có thể tính toán (cộng, trừ, nhân, chia) được; còn dữ liệu dạng Text thì không.

Theo nguyên tắc này chúng ta có thể kiểm tra xem dữ liệu nghi ngờ là sai có thể thực hiện các phép toán cơ bản không. Nếu ra lỗi #VALUE! thì không phải dữ liệu dạng Number.

4. Cách xử lý, sửa lỗi dữ liệu

Bước 1: Thống nhất về định dạng dữ liệu

Chọn toàn bộ cột dữ liệu có chứa dữ liệu dạng số(ví dụ ở đây là cột E)

Vào mục Format Cells > Chọn mục Number theo hình sau

• Decimal places: số sau dấu phẩy là mấy chữ số
• Use 1000 separator : Sử dụng dấu ngăn cách hàng nghìn

Tương tự dạng Number có thể sử dụng các dạng Accounting hoặc Currency (nếu không sử dụng ký hiệu loại tiền tệ thì chọn None)

Bước này giúp chúng ta thống nhất về cách hiển thị dữ liệu, định dạng dữ liệu khi nhập vào để tránh việc quy ước hiển thị dữ liệu không đồng nhất trong cùng 1 cột.

Bước 2: Lọc những lỗi ký tự để sửa (cách thứ 1)

Căn cứ vào giá trị lớn nhất trong dãy số đã được sắp xếp tự động trong Auto Filter:

• Phần phía trước số đó: Là những số đã được định dạng đúng
• Phần phía sau số đó: Là những số có lỗi

Sau khi lọc những số có lỗi thì thực hiện nhập lại các số đó

Bước 3: Sử dụng hàm Value để sửa (cách thứ 2)

Ngoài việc sửa trực tiếp vào bảng, có thể tạo cột phụ và sử dụng hàm VALUE trong cột phụ đó để chuyển giá trị trong bảng về đúng dữ liệu dạng số.

Trình tự thực hiện:

• Trước khi sửa lỗi cần copy dữ liệu gốc ra 1 Sheet mới và xử lý trên nội dung copy ở Sheet mới để đảm bảo không bị mất dữ liệu.
• Thừa ký tự: các dấu cách thừa, dấu chấm, dấu phẩy, dấu chấm phẩy, dấu nháy… lẫn vào ký tự dạng số => Sử dụng chức năng Replace để xóa bỏ các ký tự thừa
• Ở các định dạng khác (không phải dạng Number nhưng đã đảm bảo chỉ toàn ký tự dạng số) thì sử dụng hàm Value trực tiếp cho các giá trị đó. Sau khi có kết quả hàm Value thì sử dụng chức năng Copy/Paste special>Value để trả về kết quả vào bảng dữ liệu.

Bước 5: Kiểm tra lại nội dung xem những dữ liệu lỗi đã được sửa thành công chưa

Để cho chắc chắn chúng ta nên có thao tác kiểm tra lại dữ liệu để tránh việc sửa bị sót, thiếu.

Nên lưu trữ 1 bản copy dữ liệu gốc để tránh việc làm mất dữ liệu trong quá trình xử lý.

Bài tiếp theo: Hướng dẫn cách xử lý lỗi dữ liệu dạng Text trong Excel