Khi làm việc trên Excel thường gặp rất nhiều số liệu. Nếu chúng ta không biết cách định dạng số trong Excel về đúng dạng số, thì rất xảy ra những sai sót trong quá trình tổng hợp và tính toán, cũng như tạo biểu đồ,… Vậy nên, Học Office sẽ hướng dẫn bạn cách định dạng số trong Excel chi tiết để không xảy ra lỗi đáng tiếc khi làm việc.
Bạn đang xem: Lỗi định dạng số trong excel
Các lỗi định dạng số trong Excel
Nguyên nhân dẫn đến sai lỗi định dạng số trong Excel là do tính năng Format Cells đã được cài sẵn định dạng không phải số. Đây không chỉ là tình trạng xảy ra trong Excel 2010 mà còn ở hầu hết các phiên bản Excel hiện tại.
– Trường hợp sai về định dạng số liệu khi có chứa dữ liệu là %, số chó chưa phần thập phân hay dạng số thông thường.– Trường hợp bị lẫn lộn giữa dữ liệu ngày tháng vào các con số.– Trường hợp sai do trong quá trình thao tác nhập liệu có lẫn trong đó vài ký tự text đặc biệt với ký tự dạng số.
Hướng dẫn sửa cách định dạng số trong Excel 2010
Để biết cách định dạng số trong Excel đúng cách điều đầu tiên cần tìm cách phát hiện lỗi!
1. Cách phát hiện lỗi định dạng số trong Excel
Có 2 cách để bạn phát hiện ra lỗi sai định dạng số một cách nhanh chóng:
* Sử dụng Auto
Flilter
Ta sẽ sử dụng Auto
Flilter để lọc ra các giá trị lỗi. Ta sẽ có ví dụ minh họa về cột Lương bị sai định dạng số sau đây để bạn có thể dễ thực hành:
Bước 1: Mở file Excel rồi bôi đen toàn bộ bảng dữ liệu. Chọn Data, rồi chọn mục Sort & Filter và chọn Filter.
Bước 2: Nhấp vào mũi tên ở cột Lương. Lúc này 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 theo đúng thứ tự theo số tăng dần là đã bị lỗi về định dạng số.
* Phát hiện nhờ dữ liệu có thể tính toán
Quy luật chung trong Excel là những dữ liệu số mới có thể tính toán được, còn dữ liệu text thì không.
Vì vậy ta sẽ kiểm tra lỗi bằng thực hiện phép tính cơ bản với dữ liệu nghi ngờ, nếu dữ liệu trả kết quả #Value! thì đó không phải định dạng số.
2. Cách sửa định dạng số trong Excel 2010
Bước 1: Thống nhất về định dạng
– Bôi đen cột dữ liệu, rồi chọn Home và nhấn vào mũi tên chéo xuống ở phần Number sẽ mở hộp thoại Format Cells.
– Chọn mục Number, và chỉnh sửa:
– Nhấn OK và kiểm tra lại các số đã đúng định dạng chưa.
Bước 2: Sửa lỗi bằng sử dụng hàm Value
– Copy toàn bộ file cần sửa ra file hoặc sheet khác để tránh trường hợp mất dữ liệu khi chỉnh sửa trực tiếp.
– Sử dụng Replace để xóa các ký tự thừa trong bảng dữ liệu. Nhấn tổ hợp “Ctr + H” mở hộp thoại Replace, ở Find what nhập dấu phẩy Replace with thì để trống. Nhấn Replace all để xóa toàn bộ dấu phải 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 hàm =VALUE trực tiếp cho các giá trị đó, rồi nhấn Enter hiển thị kết quả.
– Ta sẽ thấy kết quả sau chuyển đổi, dữ liệu ô F9 được đổi thành Number.
– Copy toàn bộ dữ liệu đã chuyển đổi, rồi nhấp chuột vào vị trí giá đầu tiên của cột Lương ban đầu. Rồi chọn Home, Paste và chọn kiểu Value. Nhấn OK dán dữ liệu số.
Cách định dạng tiền VND trong Excel
Các bước định dạng tiền VND trong Excel như sau:
Bước 1: Bôi đen toàn bộ giá trị cần định dạng, chọn Home > Format > Format Cells.Bước 2: Sẽ xuất hiện của sỏ Format Cells, ta chọn Custom > Chọn Type kiểu ,##0 “VND”.
Bước 3: Chọn nút OK để hoàn thành. Vậy là bạn đã có kết quả như mong đợi rồi đấy!
Định dạng số trong Excel bằng dấu chấm
Để định dạng số trong Excel bằng dấu chấm điều đầu tiên chúng ta cần thiết lập trên Excel:
1. Thiết lập trên Excel
– Chọn File và chọn mục Option để mở cài đặt.
– Cửa sổ Excel Option mở ra, ta chọn thẻ Advanced và thiết lập:
Bỏ dấu tích mục Use system separators.Mục Decimal separator điền dấu phẩy “,”Mục Thousands separator điền dấu “.”
Sau đó nhấn OK.
2. Thêm dấu chấp vào dãy số trên Excel
– Bôi đen cột cần thêm dấu chấm và nhấn chuột phải rồi chọn Format Cells.
– Khi một thoại Format Cells xuất hiện, chọn Number và Custom. Tại đây, ta chọn định dạng #.##0 và nhấn OK để thêm dấu chấm.
– Excel sẽ trả cho bạn kết quả dãy số đã được thêm ấu chấm như ví dụ dưới đây:
Với bài viết trên, Học Office đã hướng dẫn bạn cách định dạng số trong Excel để tránh những lỗi Excel cơ bản trong quá trình làm việc hay học tập hàng ngày. Chúc bạn thành công khi áp dụng những thủ thuật này, hãy theo dõi các bài viết tiếp theo của chúng tôi nhé!
Liên hệ ngay với Học Office
gmail.comHastag: #hocoffice, #học_office, #hocofficecom, #excel, #word, #powerpoint, #congnghe, #phancung, #phanmem, #thuthuat
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 cách xử lý lỗi định dạng số trong excel nhé.
Xem nhanh
Bài 4: Hướng dẫn cách xử lý lỗi dữ liệu dạng số (Number) trong Excel4. Cách xử lý, sửa lỗi dữ liệu
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 NumberVậ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ụ Auto
Filter)
Về cách mở công cụ Auto
Filter 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 sắp xếp ngày tháng trong excel
2. Nguyên tắc sắp xếp dữ liệu dạng số trong Auto
Filter
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 Auto
Filter 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 Auto
Filter để 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ệuChọ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
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 đúngPhầ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.
Xem thêm: Một Hải Lý Là Bao Nhiêu Km, 1 Hải Lý Bằng Bao Nhiêu Mét
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
Ngoài ra Học Excel Online xin giới thiệu với các bạn “Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm“. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để đáp ứng yêu cầu công việc tổ chức, quản lý dữ liệu và lập báo cáo trên Excel, đăng ký bằng cách bấm vào ảnh phía dưới: