Cách sửa lỗi định dạng số trong Excel chi tiết, có ví dụ minh họa
Khánh Duy 01/11/2021
Việc nhập dữ liệu số trong Excel là 1 việc rất quen thuộc với mọi người. Tuy nhiên, trong 1 vài trường hợp chúng ta vô tình nhập sai số hay định dạng số làm cho việc tính toán trở nên không chính xác. Chính vì vậy, ở bài viết sau đây mình sẽ hướng dẫn bạn cách kiểm tra và sửa lỗi định dạng số để tránh các sai xót trong việc nhập dữ liệu nhé. Hãy bắt đầu thôi nào!
Bài viết được thực hiện trên laptop hệ điều hành Windows với phiên bản Excel 2016, ngoài ra bạn cũng có thể thực hiện trên các phiên bản Excel 2003, 2007, 2010, 2013, 2019 với thao tác tương tự.
I. Lỗi không định dạng được số là gì? Nguyên nhân và cách phát hiện ra lỗi
1. Lỗi không định dạng được số là gì?
Lỗi không định dạng được số là 1 lỗi làm cho số liệu của bạn bị sai về phần định dạng, nhưng khi hiển thị bạn vẫn có thể hiểu được ý nghĩa của chữ số bị sai định dạng đó.
Lỗi này thường hay gây khó khăn cho bạn khi thực hiện các phép tính, các công thức hay khi bạn thực hiện lọc dữ liệu sẽ bị sai vị trí hoặc sai số kết quả, hay tệ hơn là dẫn đến lỗi #Value! cho 1 hàm.
2. Nguyên nhân gây ra lỗi
Nguyên nhân dẫn đến lỗi này có thể là do tính năng Format Cells của Excel đã được cài sẵn với định dạng không phải số. Tình trạng này thường xảy ra ở hầu hết các phiên bản Excel hiện tại.
Sai về định dạng dữ liệu số khi có chứa phần thập phân, dữ liệu là % hay dạng số thông thường. Có nhiều trường hợp bị lẫn lộn giữa dữ liệu ngày tháng vào trong các con số. Vì bản chất dữ liệu ngày tháng cũng thuộc dạng số.
Trong quá trình thao tác nhập dữ liệu có lẫn trong đó vài ký tự của text [như ký tự đặc biệt, chữ,...] với ký tự dạng số. Từ đó Excel tưởng nhầm dữ liệu đó là kiểu Text chứ không phải Number.
II. Cách phát hiện ra lỗi
Dưới đây là 2 cách có thể giúp bạn phát hiện nhanh lỗi sai định dạng số, bạn có thể dùng cách này trong bất kỳ trường hợp nào mà bạn nghi là dữ liệu nhập vào đang bị sai định dạng hoặc kiểm tra lại sau khi nhập dữ liệu hoàn thành.
1. Sử dụng công cụ AutoFilter
Ở đây mình sẽ dùng công cụ AutoFilter để lọc ra các giá trị lỗi. Vì công dụng của công cụ này khá là phù hợp trong việc kiểm tra lỗi bị sai định dạng số trong Excel. Và tính năng của 2 công cụ này như sau:
- Dữ liệu số sẽ tăng dần từ nhỏ đến lớn nhất kể cả số âm chứ không chỉ bắt đầu từ số 0.
- Những ký tự không phải dạng số sẽ được xếp ở cuối danh sách.
Mình có 1 ví dụ như sau, ở đây cột Lương đã bị sai định dạng số ở 1 vài chỗ và mình sẽ sử dụng tính năng AutoFilter để lọc ra dữ liệu bị sai như sau:
Bước 1: Bôi đen toàn bộ bảng dữ liệu > Chọn tab Data > Mục Sort & Filter > Chọn Filter.
Bước 2: Click vào mũi tên ở cột Lương > Sẽ có 1 cột dữ liệu số được hiện ra và sắp xếp theo thứ tự từ thấp đến cao và những dữ liệu nào không sắp xếp đúng thứ tự theo số tiền tăng dần [hoặc các ký tự số nhỏ ở cuối] là đã bị lỗi về định dạng số.
2. Phát hiện nhờ dữ liệu dạng số có thể tính toán
Ở đây mình sẽ áp dụng quy luật chung trong Excel là những dữ liệu dạng Số [Number] mới có thể tính toán [cộng, trừ, nhân, chia] được, còn dữ liệu dạng Text thì lại không.
Chính vì vậy nên ở đây mình sẽ kiểm tra lỗi bằng việc thực hiện các phép tính cơ bản với dữ liệu bị nghi ngờ, nếu dữ liệu trả về là lỗi #Value! thì dữ liệu không phải là định dạng Number và không thể tính toán.
Ở đây mình sẽ cộng các dữ liệu ở cột Lương với 2 > Nếu ô nào bị lỗi #Value! sẽ là ô 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 về định dạng dữ liệu
Ở cách này bạn sẽ phải thực hiện đưa toàn bộ các số trong cột Lương về lại thành 1 định dạng duy nhất. Cách này là cách khá đơn giản và nhanh chóng giúp bạn sửa lỗi định dạng số.
Bước 1: Bôi đen toàn bộ dữ liệu ở cột Lương > Chọn thẻ Home > Nhấn vào mũi tên chéo xuống ở phần Number để mở hộp thoại Format Cells.
Bước 2: Ở hộp thoại hiện lên > Chọn mục Number > Bạn thực hiện các chỉnh sửa sau:
- Decimal places: Chỉnh phần số thập phân sau dấu phẩy [bạn có thể tùy ý chỉnh theo yêu cầu của dữ liệu bạn cần nhập].
- Tích chọn vào ô Use 1000 separator để hiển thị dấu phân cách hàng ngàn.
Lưu ý:
- Đối với dữ liệu kiểu Accounting hay Currency thì bạn cần phải thực hiện giống như hình bên dưới nhé.
- Ở bước này các bạn thống nhất chúng về cách hiển thị dữ liệu và định dạng tránh việc hiển thị không đồng đều.
Bước 3: Nhấn OK và kiểm tra lại tất cả các số đã đúng định dạng số chưa bằng các cách kiểm tra phía trên nhé.
Ở đây các bạn có thể căn cứ vào giá trị lớn nhất trong công cụ AutoFilter để lọc [nếu những số nào có lỗi thì phải thực hiện nhập lại].
- Phần ở phía trước là những số có định dạng đúng
- Phần ở phía sau là các số có lỗi định dạng sai [phải nhập lại số].
2. Sử dụng hàm Value để sửa lỗi
Lưu ý: Cách này được sử dụng khi bạn muốn chuyển đổi các dữ liệu ở dạng khác dạng Number thành dạng Number nhé.
Ví dụ: Ở đây ô F9 có được điền vào số 52, tuy nhiên số này đang được định dạng là Text cho nên cần phải đổi lại thành đạng Number để có thể thực hiện các thao tác tính toán hoặc sử dụng trong các hàm.
Bước 1: Copy toàn bộ file cần sửa lỗi ra 1 file Excel hoặc 1 sheet khác để tránh trường hợp bị mất dữ liệu khi chỉnh sửa trực tiếp trên file chính.
Bước 2: Sử dụng chức năng Replace để xóa 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 thì mình không sử dụng vì các số thập phân đang được ngăn cách bởi dấu phẩy, nếu như mình bỏ đi dấu phẩy thì số liệu sẽ bị sai sót].
Bạn nhấn Ctrl + H để mở hộp thoại Replace > Ở phần Find what nhập dấu phẩy > Ở phần Replace with để trống > Nhấn Replace all để xóa 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.
Bước 3: Ở 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ố] > 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ố].
Bước 4: Kết quả sau khi chuyển đổi, dữ liệu ở ô F9 đã được đổi thành Number.
Bước 5: Sau khi có kết quả > Copy toàn bộ dữ liệu đã chuyển đổi > Click chuột vào vị trí giá đầu tiên của cột Lương ban đầu > Chọn thẻ Home > Paste > Chọn kiểu Value
Kết quả cuối cùng sau khi hoàn thành, toàn bộ dữ liệu ở cột Lương đã chuyển thành định dạng Number.
Và đó là những cách khắc phục lỗi không định dạng được số trong Excel cực kỳ đơn giản và hiệu quả. Nếu bạn có thắc mắc về cách làm thì hãy để lại bình luận bên dưới nhé. Hy vọng bài viết sẽ hữu ích với bạn. Chúc bạn thành công!
16.187 lượt xem