Hướng dẫn sửa lỗi định dạng số trong Excel đầy đủ, minh họa chi tiết

Việc nhập dữ liệu số trong Excel là một việc rất quen thuộc với mọi người. Nhưng trong một vài trường hợp bạn nhập sai số hay định dạng số làm cho công việc tính toán không đúng. Vì thế ở bài viết này mygear.vn sẽ cùng bạn tìm hiểu phương pháp sửa lỗi định dạng số trong Excel nhé!

I. Tìm hiểu lỗi không định dạng được số? Lí do và phương pháp phát hiện lỗi

1. Tìm hiểu lỗi không định dạng được số?

Lỗi định dạng số là lỗi khiến dữ liệu bị sai về phần định dạng, nhưng khi hiển thị bạn vẫn hiểu ý nghĩa của chữ số bị sai định dạng ấy.

Lỗi này thường hay gây ảnh hưởng cho bạn khi thực hiện các phép tính, công thức hay khi thực hành lọc số liệu sẽ bị sai hoặc dẫn tới lỗi #Value! cho công thức.

2. Lí do gây ra lỗi

Do chức năng Format Cells của Excel đã cài sẵn định dạng không phải số. Nó thường xảy ra ở bản Excel hiện tại.

Do số liệu có chứa phần thập phân, số liệu là % hay dạng số thông thường. Hoặc bị lộn dữ liệu ngày tháng vào trong các con số.

Do dữ liệu bị lẫn vài ký tự của text (như ký tự đặc biệt, chữ,…) với ký tự dạng số.

II. Phương pháp phát hiện lỗi

Gồm 2 cách như sau:

1. Dùng công cụ AutoFilter

Công cụ AutoFilter khá thích hợp trong việc kiểm tra lỗi bị sai định dạng số trong Excel. Và chức năng của 2 công cụ này như sau:

Số liệu sẽ tăng dần từ nhỏ đến lớn kể cả số âm chứ không chỉ bắt đầu từ số 0.
Các ký tự không phải dạng số sẽ được xếp tại cuối danh sách.

Ví dụ: Cột Lương đã bị sai định dạng số tại một vài chỗ và mình sẽ dùng chức năng AutoFilter để lọc ra thông tin bị sai như sau:

Ví dụ

Bước 1: Bôi đen tất cả bảng số liệu > Chọn tab Data > Mục Sort & Filter > Chọn Filter.

Bôi đen tất cả bảng số liệu

Bước 2: Bấm vào mũi tên tại cột Lương > Xuất hiện một cột dữ liệu và sắp đặt từ thấp đến cao. Số liệu bị lỗi định dạng số thường được xếp ở cuối.

Số liệu bị lỗi định dạng số

2. Phát hiện lỗi định dạng số nhờ tính toán

Vận dụng quy tắc chung trong Excel là các thông tin dạng Số (Number) mới có thể tính toán (cộng, trừ, nhân, chia) được, còn thông tin 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 những phép tính cơ bản với số liệu bị nghi ngờ, nếu thông tin trả về là lỗi #Value! thì số liệu không phải là định dạng số.

Tại đây mình sẽ cộng các số liệu tại cột Lương với số 2 > Nếu ô nào bị lỗi #Value! thì là ô bị sai định dạng số.

Lỗi Value

III. Hướng dẫn sửa lỗi định dạng số trong Excel

1. Đồng nhất về định dạng dữ liệu

Ở phương pháp này phải đưa tất cả số trong cột Lương về 1 định dạng duy nhất. cách này là cách tương đối đơn thuần và nhanh chóng giúp bạn sửa lỗi định dạng số.

Bước 1: Bôi đen tất cả dữ liệu ở cột Lương > Chọn thẻ Home > Nhấn vào mũi tên chéo xuống tại phần Number để mở hộp thoại Format Cells.

Bôi đen cột Lương > Home > Bấm vào mũi tên ở phần Number

Bước 2: Xuất hiện hộp thoại > Chọn Number > Rồi chỉnh sửa như sau:

• Decimal places: Điều chỉnh phần số thập phân sau dấu phẩy (Có thể tùy chỉnh theo yêu cầu).
• Bấm vào ô Use 1000 separator để hiện dấu phân cách hàng ngàn.

Hiện hộp thoại > Chọn Number > Thực hiện chỉnh sửa

Lưu ý:

• Đối với dữ liệu kiểu Accounting hay Currency thì cần làm giống như mẫu dưới đây nhé.
• Tại bước này giúp hiển thị dữ liệu đồng đều.

Chỉnh sửa dữ liệu dạng Accounting và Currency

Bước 3: Bấm OK và rà soát lại toàn bộ số liệu đã đúng định dạng số chưa nhé.

Trong công cụ AutoFilter, phần số phía trên giá trị lớn nhất là số có định dạng đúng, phần ở phía dưới giá trị lớn nhất là số có định dạng sai (nhập lại số).

Rà soát phần bị lỗi

2. Dùng hàm Value để sửa lỗi

Lưu ý: Phương pháp này được dùng khi bạn muốn chuyển đổi những dữ liệu ở dạng khác dạng Number thành dạng Number nhé.

Ví dụ: Tại ô F9 được nhập vào số 52, nhưng 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ể thao tác tính toán hoặc dùng hàm.

Ví dụ

Bước 1: Để tránh mất dữ liệu, bạn sao chép tất cả File lỗi ra 1 File khác hoặc Sheet khác để chỉnh sửa.

Bước 2: Dùng tính năng Replace để xóa bỏ các ký tự thừa như dấu phẩy, dấu chấm,... trong bảng. (Nhưng ở đây thì không nên dùng vì số thập phân được ngăn bởi dấu phẩy, khi bỏ dấu phẩy thì số liệu sẽ bị sai sót).

Bạn bấm Ctrl + H để bật hộp thoại Replace > Tại ô Find what nhập dấu phẩy và để trống ô Replace with > Sau đó bấm Replace all để xóa tất cả dấu phẩy trong bảng > Sau đó lặp lại với những dấu khác.

Dùng chức năng Replace để các ký tự không cần thiết

Bước 3: Số liệu có định dạng khác (không phải kiểu Number nhưng chỉ tất cả đều là kiểu ký tự số) > Dùng hàm VALUE cho các số đó > Ấn Enter để hiện kết quả.

Cú pháp hàm: =VALUE(ô cần chuyển đổi sang số).

Dùng hàm VALUE để chuyển định dạng số

Bước 4: Kết quả ô F9 đã được đổi thành Number.

Kết quả

Bước 5: Khi có kết quả > Sao chép tất cả dữ liệu > Bấm chuột vào ô đầu tiên của cột Lương ban đầu > Chọn thẻ Home > Paste > Chọn dạng Value > Ấn OK.

Sao chép tất cả dữ liệu đã chuyển đổi sang bảng ban đầu

Sau khi hoàn tất, thông tin tại cột Lương đã chuyển sang định dạng Number.

Kết quả

Và mình đã chia sẻ và hướng dẫn sửa lỗi định dạng số trong Excel khá đầy đủ và chi tiết. Hy vọng bạn sẽ áp dụng thành công vào công việc của mình. Nếu có góp ý thắc mắc bạn hãy bình luận ở dưới nhé, mình sẽ hỗ trợ giải đáp!