Mẹo Google Sheets · 9 phút đọc
SUM ra 0 dù cột đầy số gần như luôn có một nguyên nhân: các con số đó đang được lưu dưới dạng chữ, và SUM bỏ qua chữ. Bài này chỉ cách nhận ra trong vài giây (lề trái, ISTEXT, LEN), năm tình huống hay làm số biến thành chữ, và bốn cách sửa từ nhanh tới chắc — kèm cách tương đương trong Excel.
SUM ra 0 dù cột đầy số — sửa trong 10 giây
Xem trên YouTube ↗Google Sheets phân biệt rất rõ số (thứ để cộng trừ) và chữ (thứ để đọc). Chuỗi "1.250.000" nhìn giống số, nhưng nếu nó được lưu là chữ thì hàm SUM coi như ô trống. Cả cột đều là chữ thì SUM ra 0. Chỉ vài ô là chữ thì còn tệ hơn: SUM vẫn ra một con số trông hợp lý, chỉ là thiếu — và không ai để ý.
Điều làm nhiều người bối rối: phép cộng bằng dấu + lại thường ra đúng. =C2+C3 tự đổi chữ sang số trước khi cộng, còn SUM, SUMIFS, AVERAGE thì không. Nên nếu một ô kiểm tra bằng dấu + ra đúng mà SUM ra sai, gần như chắc chắn là số dạng chữ.
=COUNTA(C2:C500) - COUNT(C2:C500)Kết quả khác 0 là có bấy nhiêu ô cần sửa. Công thức này đáng đặt cố định ở đầu các cột tiền quan trọng, nhất là cột nhận dữ liệu dán từ nơi khác.
| Nguyên nhân | Hay gặp khi | Nhìn thấy gì |
|---|---|---|
| Dán từ sao kê, trang web, tin nhắn | Chép số tiền từ internet banking, Zalo, email | Số nằm trái, đôi khi kèm "đ" hoặc "VND" |
| Dấu nháy đơn ở đầu ô | Ai đó gõ '0912… để giữ số 0 đầu, rồi gõ luôn cho cột tiền | Thanh công thức hiện dấu ' trước số |
| Ô định dạng Văn bản thuần tuý | Cột bị đặt Định dạng › Số › Văn bản thuần tuý từ trước | Gõ số nào cũng thành chữ |
| Khoảng trắng không ngắt CHAR(160) | Dán từ trang web hoặc file PDF | Trông bình thường, LEN dư 1–2 ký tự |
| Dấu thập phân sai vùng | File để vùng Việt Nam mà dán số kiểu 12.5 từ nguồn tiếng Anh | Số lẻ thành chữ hoặc bị hiểu sai |
Nguyên nhân cuối liên quan tới cài đặt vùng của file: vùng Việt Nam dùng dấu phẩy cho phần thập phân (12,5) và dấu chấm phân cách nghìn (1.250.000); vùng Hoa Kỳ thì ngược lại. Cùng cài đặt đó cũng làm ngày 03/07 thành 7 tháng 3 — cách kiểm ở bài sửa lỗi ngày tháng trong Google Sheets.
Cách 1 — Phân tách văn bản thành các cột (sửa cả cột trong 10 giây). Bôi đen cột số tiền › Dữ liệu › Phân tách văn bản thành các cột (giao diện cũ ghi "Tách văn bản thành cột"). Google Sheets đọc lại từng ô theo vùng của file và nhận ra đó là số; số nhảy sang phải là xong. Nếu ô chọn dấu phân cách hiện ra, cứ để mặc định. Cách này hợp với số không có ký tự lạ.
Cách 2 — Dọn khoảng trắng. Dữ liệu › Dọn sạch dữ liệu › Cắt bỏ khoảng trắng bỏ khoảng trắng thừa đầu và cuối ô. Làm xong, kiểm lại bằng ISTEXT: còn TRUE thì nhiều khả năng là khoảng trắng không ngắt CHAR(160), dùng cách 3.
Cách 3 — Cột phụ bằng công thức (chắc chắn nhất). Thêm một cột bên cạnh, bỏ hết ký tự gây nhiễu rồi đổi sang số:
=VALUE(SUBSTITUTE(SUBSTITUTE(C2; CHAR(160); ""); "."; ""))=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2; CHAR(160); ""); "."; ""); "đ"; ""); "VND"; ""))=VALUE(SUBSTITUTE(C2; "."; ","))Kéo công thức xuống hết cột, kiểm tổng, rồi chép cột phụ và dán chỉ giá trị đè lên cột gốc (Ctrl+Shift+V, trên Mac là Cmd+Shift+V). Xoá cột phụ. Nếu file của bạn để vùng Hoa Kỳ, đổi mọi dấu ; trong công thức thành dấu phẩy.
Đừng bỏ dấu chấm với số có phần lẻ
Công thức bỏ dấu "." chỉ đúng với số tiền chẵn đồng như 1.250.000. Với số lẻ kiểu 1.250,75 (vùng Việt Nam) thì bỏ dấu chấm vẫn đúng, nhưng với số kiểu tiếng Anh 1,250.75 thì phải bỏ dấu phẩy và đổi dấu chấm thành dấu phẩy. Thử trên 2–3 ô và so bằng mắt trước khi kéo cả cột.
Cách 4 — Định dạng › Số › Số (phòng cho lần sau). Nếu cột từng bị đặt Văn bản thuần tuý, chọn cột › Định dạng › Số › Số để số gõ từ giờ được lưu đúng. Ô nào đã là chữ từ trước thì kiểm lại bằng ISTEXT; còn TRUE thì gõ lại hoặc dùng cách 1, cách 3.
Nếu cột nhận dữ liệu dán từ nơi khác mỗi tháng (sao kê, file máy chấm công) và bạn không muốn sửa tay lần nào nữa, dùng công thức tổng tự đổi chữ sang số. Ô trống hoặc ô chữ thật (như "chưa trả") tính là 0:
=SUM(ARRAYFORMULA(IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(C2:C500; CHAR(160); ""); "."; "")); 0)))Thử nhẩm với bảng ở hình đầu bài: năm ô chữ 1.250.000, 2.400.000, 850.000, 1.900.000, 1.050.000 qua công thức này thành số và cộng ra 7.450.000. Công thức chỉ dùng cho file vùng Việt Nam với số tiền chẵn đồng; vẫn nên sửa dữ liệu gốc cho sạch khi có thời gian, vì SUMIFS, biểu đồ và bảng tổng hợp khác vẫn cần số thật.
Excel đánh dấu số dạng chữ bằng tam giác xanh nhỏ ở góc trên bên trái ô. Chọn các ô đó, bấm biểu tượng dấu chấm than hiện ra bên cạnh, chọn Convert to Number (bản tiếng Việt: Chuyển đổi thành Số). Cách sửa cả cột tương tự Google Sheets: Data › Text to Columns rồi bấm Finish ngay. Các công thức VALUE, SUBSTITUTE, CHAR(160) ở trên dùng y nguyên trong Excel.
Muốn biết Google Sheets và Excel còn khác nhau ở đâu — làm chung, điện thoại, giới hạn dữ liệu — xem bài Google Sheets hay Excel.
Với bảng lương, lỗi này nguy hiểm vì tổng thiếu vẫn "trông được". Nếu bạn tự dựng bảng lương, xem thêm cách tính lương nhân viên bằng Excel; còn với file phòng trọ, các bảng nên tách chỗ gõ tay và chỗ công thức như ở bài cách quản lý phòng trọ bằng Google Sheets.
Dấu + tự đổi chữ thành số trước khi cộng, còn SUM bỏ qua mọi ô là chữ. Kết quả khác nhau như vậy là dấu hiệu chắc chắn của số dạng chữ.
Đổi định dạng chỉ quyết định cách hiển thị và cách lưu số gõ sau đó. Giá trị đã là chữ từ trước có thể vẫn là chữ. Dùng Dữ liệu › Phân tách văn bản thành các cột hoặc cột phụ VALUE để đổi hẳn.
Đó thường là khoảng trắng không ngắt, mã CHAR(160), hay đi kèm số chép từ trang web. Dùng =SUBSTITUTE(C2; CHAR(160); "") rồi bọc VALUE bên ngoài.
Cùng nguyên nhân: cột cần cộng đang là chữ. Ngoài ra kiểm cột điều kiện — mã phòng "A101 " có khoảng trắng cuối sẽ không khớp với "A101".
Có: chọn các ô có tam giác xanh, bấm dấu chấm than và chọn Convert to Number (Chuyển đổi thành Số). Google Sheets không có nút này nên dùng Phân tách văn bản thành các cột.
Mẫu khác liên quan: Chấm công & Tính lương PRO.
Google Sheets hay Excel: so chi phí, làm chung, dùng trên điện thoại, khi mất mạng, giới hạn dữ liệu, Apps Script với VBA và các công thức khác nhau.
Sửa lỗi ngày tháng Google Sheets: gõ 03/07 ra 7 tháng 3, ngày dạng chữ, TODAY lệch ngày. Đổi vùng Việt Nam, múi giờ GMT+7 và công thức sửa cột cũ.
Cách khoá ô công thức trong Google Sheets bằng Bảo vệ trang tính và dải ô: chỉ cảnh báo hay chặn hẳn, khoá cả trang chừa ô nhập, những gì bảo vệ không làm được.
Tạo mã QR chuyển khoản ngân hàng ngay trong ô Google Sheets: hàm IMAGE, link img.vietqr.io, mã BIN, ENCODEURL cho nội dung, và lưu ý dữ liệu đi qua bên thứ ba.
Xem thêm mẹo và video hướng dẫn trên kênh YouTube HiTaVa, hoặc đọc các bài khác trong chủ đề Mẹo Google Sheets.
Cần hỏi thêm?Zalo 0935077673 (Hải Vân)
