Buzz
Việc nhập số liệu trong Excel là điều phổ biến nhưng đôi khi, chúng ta vô tình nhập sai số hoặc định dạng số, làm cho tính toán trở nên không chính xác. Bài viết này sẽ hướng dẫn cách kiểm tra và sửa lỗi định dạng số để tránh những sai sót trong việc nhập số liệu. Bắt đầu thôi!
I. Lỗi không định dạng được số là gì? Nguyên nhân và cách phát hiện lỗi
1. Lỗi không định dạng được số là gì?
Lỗi không định dạng được số làm cho số liệu của bạn bị sai về định dạng, nhưng vẫn hiển thị có thể hiểu được ý nghĩa của số.
Lỗi này thường gây khó khăn trong tính toán, công thức và việc lọc dữ liệu có thể bị sai vị trí hoặc sai kết quả, hoặc dẫn đến lỗi #Value! cho các hàm.
2. Nguyên nhân gây ra lỗi
Nguyên nhân gây ra lỗi có thể là do tính năng Format Cells của Excel đã được thiết lập với định dạng không phải là số. Hiện tượng này thường xảy ra trong hầu hết các phiên bản Excel hiện tại.
Sai định dạng dữ liệu số khi có phần thập phân, dữ liệu là % hoặc số thông thường. Có nhiều trường hợp dữ liệu ngày tháng bị nhầm lẫn với các con số. Vì dữ liệu ngày tháng cũng thuộc dạng số.
Trong quá trình nhập dữ liệu, có thể lẫn vào đó một số ký tự văn bản (như ký tự đặc biệt, chữ,...) với ký tự số. Do đó, Excel có thể hiểu lầm dữ liệu đó là kiểu văn bản thay vì số.
II. Cách phát hiện lỗi
1. Sử dụng công cụ Bộ lọc tự động
Ở đây, chúng ta sử dụng công cụ Bộ lọc tự động để phát hiện các giá trị lỗi. Công cụ này rất hữu ích trong việc kiểm tra lỗi định dạng số trong Excel. Tính năng của công cụ như sau:
- Các số sẽ được sắp xếp tăng dần từ nhỏ đến lớn, bao gồm cả số âm, không chỉ bắt đầu từ số 0.
- Các ký tự không phải số sẽ được đặt ở cuối danh sách.
Dưới đây là 1 ví dụ, trong đó cột Lương bị sai định dạng số ở vài nơi và sử dụng tính năng Bộ lọc tự động để lọc ra dữ liệu bị sai như sau:
Ví dụ
Bước 1: Chọn toàn bộ bảng dữ liệu > Chọn tab Data > Mục Sắp xếp & Lọc > Chọn Bộ lọc.
Chọn toàn bộ bảng dữ liệu > Chọn tab Data > Mục Sắp xếp & Lọc > Chọn Bộ lọc
Bước 2: Nhấp vào mũi tên ở cột Lương
Dữ liệu bị lỗi định dạng số
2. Phát hiện qua dữ liệu có thể tính toán
Ở đây mình sẽ tuân theo quy tắc chung trong Excel là chỉ có dữ liệu dạng Số (Number) mới có thể tính toán (cộng, trừ, nhân, chia), còn dữ liệu dạng Văn bản thì không thể.
Vì vậy, mình sẽ kiểm tra lỗi bằng cách thực hiện các phép tính cơ bản với dữ liệu nghi ngờ. Nếu kết quả trả về là lỗi #Value! thì dữ liệu không phải là dạng Number và không thể tính toán.
Ở đây, mình sẽ cộng số liệu trong cột Lương với 2 > Nếu có ô nào bị lỗi #Value! thì đó là ô có định dạng số bị sai.
Lỗi #Value xuất hiện do có ô bị sai định dạng số
III. Cách khắc phục lỗi định dạng số trong Excel
1. Thống nhất định dạng dữ liệu
Trong phương pháp này, bạn cần chuyển toàn bộ số liệu trong cột Lương về cùng một định dạng. Đây là cách đơn giản và nhanh chóng để sửa lỗi định dạng số.
Bước 1: Chọn toàn bộ dữ liệu trong cột Lương > Tab Home > Nhấn vào mũi tên xuống ở phần Số để mở hộp thoại Định dạng Ô.
Chọn dữ liệu trong cột Lương > Tab Home > Nhấn mũi tên xuống ở phần Số
Bước 2: Trong hộp thoại hiển thị > Chọn mục Số > Thực hiện các điều chỉnh sau:
- Số thập phân: Điều chỉnh số thập phân sau dấu phẩy (bạn có thể điều chỉnh theo nhu cầu của dữ liệu).
- Tích vào ô Sử dụng dấu phân cách hàng ngàn để hiển thị dấu phân cách hàng nghìn.
Trong hộp thoại hiển thị > Chọn mục Số > Thực hiện các điều chỉnh như hình minh họa
Chỉnh sửa cho dạng Kế toán và Tiền tệ
Bước 3: Nhấn OK và kiểm tra lại tất cả các số đã được định dạng đúng bằng cách kiểm tra như trên.
Tại đây, bạn có thể dựa vào giá trị lớn nhất trong công cụ Bộ lọc tự động để lọc (nếu có số nào lỗi thì phải nhập lại).
- Phần trước đó là các số có định dạng đúng
- Phần sau đó là các số có lỗi định dạng (cần nhập lại số).
Kiểm tra phần bị lỗi
2. Sử dụng hàm Value để sửa lỗi
Ví dụ: Ở đây ô F9 có giá trị là 52, nhưng đang được định dạng là Văn bản nên cần chuyển sang dạng Số để thực hiện tính toán hoặc sử dụng trong các hàm.
Ví dụ
Bước 1: Sao chép toàn bộ tệp cần sửa lỗi ra một tệp Excel khác hoặc một sheet khác để tránh mất dữ liệu khi chỉnh sửa trực tiếp trên tệp gốc.
Bước 2: Sử dụng tính năng Thay thế để loại bỏ các ký tự thừa như dấu phẩy, dấu chấm,... trong bảng dữ liệu số. (Ở trường hợp này, không sử dụng vì các số thập phân đã được ngăn cách bởi dấu phẩy, nếu loại bỏ dấu phẩy thì số liệu sẽ bị sai lệch).
Nhấn Ctrl + H để mở hộp thoại Thay thế > Ở phần Tìm nhập dấu phẩy > Ở phần Thay bằng để trống > Nhấn Thay tất cả để loại bỏ toàn bộ dấu phẩy trong các dữ liệu số > Tiếp tục lặp lại với các dấu khác.
Sử dụng tính năng Thay thế để loại bỏ các dấu phẩy, dấu chấm, ký tự ra khỏi dữ liệu
Bước 3: Ở các định dạng khác (không phải dạng Number nhưng đã đảm bảo chỉ chứa ký tự dạng số) > Sử dụng hàm VALUE trực tiếp cho các giá trị đó > Nhấn Enter để hiển thị kết quả.
Cú pháp hàm: =VALUE(giá trị cần chuyển đổi sang số).
Sử dụng hàm VALUE để chuyển đổi dữ liệu thành Number
Bước 4: Kết quả sau khi chuyển đổi, dữ liệu ở ô F9 đã được chuyển thành Number.
Sau khi hoàn tất chuyển đổi, dữ liệu ở ô F9 đã được biến thành số
a
Sao chép toàn bộ dữ liệu đã chuyển đổi > Nhấp chuột vào ô đầu tiên của cột Lương > Chọn thẻ Home > Dán > Chọn kiểu Giá trị > Nhấn OK
Kết quả cuối cùng sau khi hoàn thành, toàn bộ dữ liệu ở cột Lương đã được chuyển thành định dạng số.
Kết quả cuối cùng sau khi hoàn thành
Và đó là cách khắc phục lỗi không định dạng được số trong Excel một cách đơn giản và hiệu quả. Nếu bạn cần hỗ trợ thêm, hãy để lại bình luận bên dưới. Chúc bạn thành công!
