Xử lý tình huống

Số lưu dạng chữ: SUM ra 0, không cộng được

Cột số bị canh trái, SUM ra 0 dù nhìn toàn là số? Đó là số bị lưu dạng chữ, thường do dấu cách thừa hoặc dấu chấm ngăn nghìn kiểu Việt Nam khi xuất từ phần mềm hoặc chép từ web. Cách nhận ra và đổi thành số thật bằng VALUE, NUMBERVALUE và TRIM.

10 phút2 bài tậpKế toánVăn phòngHành chính – nhân sựBán hàng

Mục lục

Cách làm nhanh
Vấn đề bạn đang gặp
Cách sửa từng bước
Vì sao lỗi này xảy ra
Trên Excel thật
Luyện tập (2 bài tập)
Câu hỏi thường gặp

Cách làm nhanh

Nhận ra số dạng chữ. Số bị canh trái, =SUM(E2:E11) ra 0 và =ISNUMBER(E2) ra FALSE. Đó là chữ, không phải số.
Đếm ký tự để tìm dấu cách ẩn. Gõ =LEN(E2) rồi so với số chữ số nhìn thấy. Dư ký tự nghĩa là có dấu cách ở đầu hoặc cuối.
Đổi chữ thành số ở một cột phụ. Chỉ thừa dấu cách: =VALUE(TRIM(E2)). Số kiểu Việt Nam như 12.500.000: =NUMBERVALUE(E2,",",".").
Điền công thức xuống hết cột và kiểm tra. Kết quả canh phải, SUM ra tổng khác 0. Nhân với Số lượng như bình thường.
Giữ kết quả bằng giá trị. Sao chép cột công thức rồi dán giá trị (Paste Special, Values) đè lên cột gốc nếu muốn bỏ cột phụ.

Vấn đề bạn đang gặp

Bạn xuất bảng bán hàng từ phần mềm (hoặc chép từ một trang web) vào Excel. Cột Đơn giá nhìn như số, nhưng số bị canh trái, thường có một tam giác xanh nhỏ ở góc ô, =SUM(E2:E11) ra 0, và Thành tiền tính ra không đúng. Công thức không báo lỗi.

Bảng bán hàng: cột Đơn giá canh trái và có tam giác xanh ở góc ô, cột Số lượng canh phải, cột Thành tiền còn trống. Chú thích: 1: Đơn giá canh trái: đây là chữ, không phải số. Ô này còn thừa dấu cách ở đầu và cuối, mắt không thấy; 2: Số lượng canh phải: đây là số thật; 3: Thành tiền chỉ tính đúng khi Đơn giá là số thật. — Đơn giá là chữ nên canh trái và có tam giác xanh nhỏ ở góc ô, còn Số lượng là số thật nên canh phải. Vài ô Đơn giá còn thừa dấu cách mà mắt không thấy.

Theo Microsoft, số bị lưu dạng chữ canh trái thay vì canh phải và thường có chỉ báo lỗi (tam giác xanh), còn hàm SUM bỏ qua ô chữ. Vì vậy nếu cả cột là chữ thì tổng ra 0. Hàm =ISNUMBER(E2) trả về TRUE cho số thật và FALSE cho chữ, nên bạn có thể kiểm tra từng ô.

Cách sửa từng bước

Đổi cột Đơn giá (chữ) thành số rồi tính Thành tiền

Làm gì: Ở một ô trống bên cạnh, gõ =ISNUMBER(E2) và =LEN(E2). FALSE nghĩa là ô E2 là chữ. Nếu LEN ra nhiều hơn số chữ số bạn thấy, ô có dấu cách ẩn.
Vì sao: Hàm LEN đếm cả dấu cách, nên số ký tự lệch cho bạn thấy ngay ô nào còn thừa, thay vì đoán bằng mắt.
Làm gì: Ở cột phụ (ví dụ H2), gõ =VALUE(TRIM(E2)) rồi sao chép xuống hết cột.
Vì sao: TRIM bỏ dấu cách thừa ở đầu và cuối, rồi VALUE đổi chữ số thành số thật. Kết quả canh phải.
Chỗ hay sai
Làm gì: Nếu chữ viết kiểu Việt Nam, ví dụ 12.500.000 (dấu chấm ngăn nhóm), dùng =NUMBERVALUE(E2,",","."). Trên lưới DeskLab, =VALUE(E2) cho chữ này ra #VALUE!.
Vì sao: Hai đối số sau của NUMBERVALUE nói rõ dấu thập phân là dấu phẩy và dấu ngăn nhóm là dấu chấm, nên kết quả không đổi theo cài đặt vùng của máy.
Làm gì: Tính Thành tiền bằng cách nhân số thật với Số lượng, ví dụ =VALUE(TRIM(E2))*F2, rồi cộng cột đó bằng =SUM(G2:G11).
Vì sao: Khi mọi số đều là số thật, SUM cộng đủ và ra tổng đúng, không còn ra 0.
Làm gì: Muốn bỏ cột phụ: chọn cột công thức, sao chép, rồi dán giá trị (Paste Special, Values) đè lên cột gốc.
Vì sao: Cột gốc chỉ còn số thật, không còn công thức phụ thuộc cột khác. Bước này không cần cho bài tập, vì bài tập chấm công thức của bạn.

Tự hỏi mình

Vì sao SUM ra 0 mà không báo lỗi gì?
Nếu chỉ vài ô trong cột là chữ, SUM ra tổng thấp hơn mong đợi chứ không ra 0. Vì sao?

Hai cách này làm việc bằng công thức, nên bạn luyện được ngay trên lưới DeskLab. Chữ 12.500.000 dùng dấu chấm ngăn nhóm; theo Microsoft, khi bạn không nói rõ dấu thập phân và dấu ngăn nhóm thì NUMBERVALUE dùng dấu của máy đang mở. Vì vậy hãy luôn nói rõ hai dấu: =NUMBERVALUE(E2,",",".") cho kết quả giống nhau trên mọi máy.

Cột Lương cơ bản của bảng lương: các số viết kiểu Việt Nam với hai dấu chấm ngăn cách, canh trái như chữ. Chú thích: 1: Lương viết kiểu Việt Nam, dấu chấm ngăn cách hàng nghìn: đây là chữ, canh trái; 2: Ngày công là số thật nên canh phải. — Lương viết kiểu 12.500.000 (dấu chấm ngăn nhóm, kiểu Việt Nam) là chữ nên canh trái, trong khi Ngày công là số thật.

Vì sao lỗi này xảy ra

Excel phân biệt số và chữ. Số là giá trị để tính; chữ chỉ là các ký tự đứng cạnh nhau, dù trông giống số. Khi xuất từ phần mềm hoặc chép từ web, số có thể đi kèm dấu cách thừa, ký tự ẩn hoặc dấu ngăn nhóm khiến Excel xem cả ô là chữ.

Microsoft ghi rõ hai nguồn gây lỗi này: dữ liệu nhập từ ngoài vào bị lưu thành chữ, và chữ chứa dấu cách thừa (ký tự 32 và 160) hoặc ký tự không in được. Chữ không được SUM cộng, nên tổng bị thiếu mà SUM không báo lỗi.

Dùng VALUE mà ô ra #VALUE!.
Nguyên nhân
Chữ không đúng dạng số, ngày hoặc giờ mà Excel nhận ra, ví dụ có thêm chữ đ sau số. Theo Microsoft, VALUE báo #VALUE! khi chữ không đúng các dạng đó.
Cách sửa
Với chữ thừa như đ, bỏ nó trước bằng SUBSTITUTE. Với số viết dấu chấm ngăn nhóm kiểu Việt Nam, dùng NUMBERVALUE và nói rõ hai dấu: =NUMBERVALUE(E2,",",".").
Đã bọc TRIM mà số vẫn không cộng được.
Nguyên nhân
Chữ có thể chứa ký tự 160 (dấu cách không ngắt), thường gặp khi chép từ web. Theo Microsoft, riêng TRIM không xóa được ký tự này.
Cách sửa
Thay ký tự 160 trước: =SUBSTITUTE(E2,CHAR(160),""), rồi mới đổi thành số. Lưới DeskLab xem ký tự 160 như dấu cách thường nên không có bài tập cho trường hợp này: hãy thử trên Excel thật.
SUM ra tổng thấp hơn mong đợi chứ không ra 0.
Nguyên nhân
Một phần ô là số thật, phần còn lại là chữ. SUM bỏ qua các ô chữ và chỉ cộng các ô số.
Cách sửa
Đổi cả cột sang số thật, rồi kiểm tra từng ô bằng =ISNUMBER(E2) để chắc không còn ô chữ nào.
Xóa cột gốc xong, cột công thức báo lỗi.
Nguyên nhân
Cột phụ vẫn là công thức trỏ vào cột gốc. Microsoft hướng dẫn sao chép cột công thức và dán giá trị vào chính nó trước khi xóa cột gốc.
Cách sửa
Chọn cột công thức, sao chép, rồi dán giá trị (Paste Special, Values) trước khi xóa cột gốc.

Đọc xong, giờ thử

Luyện tập ngay (2 bài tập)

Làm trực tiếp trên lưới trong trình duyệt. Mỗi lần bấm chấm bài, DeskLab kiểm tra kết quả và gợi ý nếu bạn chưa làm đúng.

Bắt đầu luyện tập

Chấm tự động ngay trên máy bạn. Không cần đăng nhập. Tiến độ lưu trên trình duyệt này.

Cột Đơn giá canh trái và =SUM(E2:E11) ra 0. Điều này cho thấy gì?

Công thức SUM viết saiCác ô là chữ, và SUM bỏ qua ô chữExcel đang bị lỗi tính toán

Dùng công thức nào để đổi chữ 12.500.000 (dấu chấm ngăn nhóm kiểu Việt Nam) thành số, cho kết quả như nhau trên mọi máy?

=VALUE(E2)=TRIM(E2)=NUMBERVALUE(E2,",",".")

Ô chứa 300000 kèm ký tự 160 (dấu cách không ngắt) chép từ web. =TRIM(E2) có đủ để làm sạch ô trên Excel thật không?

Không: theo Microsoft, TRIM không xóa ký tự 160, cần SUBSTITUTE thay ký tự 160 trướcKhông, chỉ cần đổi định dạng ô sang NumberCó, TRIM xóa mọi loại dấu cách

Trên Excel thật

Lưới DeskLab: SUM trên cột chữ ra 0 và =ISNUMBER(E2) ra FALSE, giống như bạn mô tả ở trên. Hai bài tập dùng dấu cách thường (ký tự 32) và chữ kiểu 12.500.000.
Số kiểu Việt Nam: trên lưới DeskLab, =VALUE(E2) với chữ 12.500.000 ra #VALUE!, còn =NUMBERVALUE(E2,",",".") đọc đúng. Theo Microsoft, NUMBERVALUE đọc chữ thành số không phụ thuộc vùng và dùng dấu của máy khi bạn không nói rõ, nên hãy luôn nói rõ hai dấu.
Ký tự 160: trên lưới DeskLab, TRIM, SUBSTITUTE và VALUE đều coi ký tự 160 như dấu cách thường, nên không có bài tập nào kiểm tra phần này. Trên Excel thật, TRIM không xóa ký tự 160 (xem câu hỏi trên), vì vậy hãy làm bước thay ký tự 160 bằng SUBSTITUTE khi dữ liệu đến từ web.
Tam giác xanh và Paste Special: lưới DeskLab cũng vẽ tam giác xanh ở góc ô chữ trông như số (xem hình đầu trang). Hai cách sửa tại chỗ bên dưới là hướng dẫn cho Excel thật; trên lưới DeskLab, hãy dùng công thức như hai bài tập.

Sơ đồ: Sửa ngay tại ô: Convert to Number. Mũi tên đi từ mục lớn đến mục con.

Sơ đồ: Sửa ngay tại ô: nhân với 1. Mũi tên đi từ mục lớn đến mục con.

Câu hỏi thường gặp

Có cách sửa nhanh mà không cần công thức không?

Có, trên Excel thật. Chọn các ô có tam giác xanh ở góc, bấm nút cảnh báo hiện cạnh ô rồi chọn Convert to Number (Chuyển thành số). Hoặc gõ số 1 vào một ô trống, sao chép nó, chọn các ô số dạng chữ rồi dùng Paste Special với phép Multiply (Nhân): mỗi ô được nhân với 1 nên thành số thật. Hai cách này sửa ngay tại ô, không cần cột phụ. Các bài tập của DeskLab luyện cách dùng công thức, nên hãy làm hai cách này trên Excel thật.

TRIM không sửa được, số vẫn không cộng được thì sao?

Số chép từ web thường chứa ký tự 160 (dấu cách không ngắt), và theo Microsoft, riêng TRIM không xóa được ký tự này. Hãy dùng SUBSTITUTE để thay ký tự 160 bằng chữ rỗng, ví dụ SUBSTITUTE(E2,CHAR(160),""), rồi mới đổi thành số. Trên máy Mac, CHAR(160) có thể ra ký tự khác; UNICHAR(160) trả về đúng ký tự 160 của Unicode.