Excel có thể cộng thiếu hoặc trả về 0 dù các ô trông như số vì một số giá trị đang được lưu dưới dạng văn bản và bị hàm SUM bỏ qua khi cộng một vùng. Hãy kiểm tra một ô nghi ngờ bằng =ISNUMBER(A2) trước khi đổi định dạng hoặc sửa công thức.
Thử trên ba dòng để nhận ra lỗi cộng thiếu
Tạo bảng thử riêng. Nhập 120 vào A2, nhập dấu nháy đơn rồi gõ 80 vào A3, sau đó nhập 50 vào A4. Trên trang tính, cả ba ô đều nhìn giống số. Tuy nhiên, =SUM(A2:A4) trả về 170, trong khi người đọc cộng bằng mắt được 250. Khoản chênh đúng 80 là giá trị đang được giữ dưới dạng văn bản. Dùng =COUNT(A2:A4) sẽ thấy chỉ có hai ô chứa số.
Đổi đúng các ô chứa số tiền
Nếu Excel đánh dấu lỗi ở góc ô và menu có mục Convert to Number, chọn các ô tiền cần chuyển rồi dùng lệnh đó. Trong bảng thử, hãy tính lại SUM và COUNT: hai kết quả cần đạt là 250 và 3. Đừng áp dụng thao tác này cho cột mã khách hoặc điện thoại bên cạnh, vì các ký tự số 0 đứng đầu ở đó có ý nghĩa nhận diện.
Hướng dẫn chuyển số lưu dưới dạng văn bản của Microsoft.

Kiểm tra phạm vi tính tổng trước khi sửa dữ liệu
Bấm vào ô tổng rồi nhìn thanh công thức để biết Excel đang cộng từ dòng nào đến dòng nào. Ví dụ, danh sách tuần trước kết thúc ở dòng 20, tuần này phát sinh năm phiếu thu mới ở các dòng 21 đến 25. Nếu công thức vẫn là SUM(B2:B20), cả năm khoản mới nằm ngoài vùng tính dù từng ô đều chứa số hợp lệ. Khi ấy việc đổi kiểu dữ liệu sẽ không làm kết quả thay đổi. Chỉnh điểm cuối của vùng rồi thử thay một giá trị trong năm dòng mới để xác nhận ô tổng phản ứng đúng.
Kiểm tra cả tên trang tính nếu bảng được sao chép giữa các tháng. Hai trang có bố cục giống nhau có thể khiến một công thức trỏ sang tháng trước trông hoàn toàn hợp lý. Để tìm lỗi trên bản sao, chọn một khoản có mã phiếu dễ nhớ rồi tăng đúng một đơn vị. Nếu tổng của tháng hiện tại đứng yên nhưng tổng ở một trang khác thay đổi, bạn đã tìm được quan hệ cần kiểm tra. Hoàn tác giá trị thử ngay sau khi quan sát để dữ liệu bàn giao giữ đúng chứng từ.
Phân biệt tổng bằng không, tổng thiếu và ô hiện nguyên công thức
Tổng bằng không với cả cột tiền thường gợi ý Excel chưa nhận được số trong phạm vi đang xét. Một tổng dương nhưng thấp hơn chứng từ lại gợi ý chỉ một số dòng bị bỏ qua hoặc vùng cộng kết thúc quá sớm. Nếu ô hiển thị nguyên dòng bắt đầu bằng dấu bằng, hãy kiểm tra chính ô công thức trước. Ba biểu hiện cần những phép thử khác nhau; sửa cả cột bằng một thao tác có thể làm mất manh mối cho biết nguyên nhân ban đầu.
Với ô đang hiện nguyên công thức, hãy xem hiện tượng xảy ra ở một ô hay toàn bộ trang tính. Nếu nhiều ô cùng chuyển sang hiện biểu thức, hãy kiểm tra tùy chọn Show Formulas trong thẻ Formulas. Nếu chỉ một ô gặp vấn đề, xem ô có định dạng Text hoặc có dấu nháy trước dấu bằng hay không. Trên bản sao, đổi ô sang General, bỏ ký tự thừa rồi nhập lại công thức. Chỉ chuyển định dạng có thể chưa khiến biểu thức đã nhập trước đó được đánh giá lại.
Khoanh vùng bằng một cột kiểm tra
Tạo cột phụ bên phải bảng và nhập =ISNUMBER(B2) nếu B2 là ô tiền đầu tiên. Sao chép xuống đến dòng cuối rồi lọc các giá trị FALSE. Lúc này danh sách rút gọn có thể bao gồm số lưu dưới dạng chữ, ô rỗng, ghi chú và công thức trả về chuỗi rỗng. Hãy đọc vài dòng đầu trước khi kết luận tất cả đều sai. Một dòng ghi “chưa báo giá” cần giữ nguyên ý nghĩa, còn một dòng đã có hóa đơn nhưng lưu tiền dưới dạng chữ mới là đối tượng cần chuyển.
Nếu muốn đánh giá nhanh phạm vi ảnh hưởng, ghi riêng số dòng có chứng từ và số ô được COUNT ghi nhận. Đừng lấy toàn bộ số hàng trong bảng làm số khoản phải thu nếu bảng còn có hàng tiêu đề phụ hoặc dòng trống phân nhóm. Trong một bảng có hai mươi phiếu và hai dòng ghi chú, kết quả COUNT bằng hai mươi có thể hoàn toàn đúng. Ngược lại, một dòng ngày tháng nhập nhầm vào cột tiền vẫn có thể được đếm là số, nên bước đối chiếu chứng từ vẫn cần thiết.
Xử lý dấu phân tách hàng nghìn và dấu thập phân
Một giá trị như 1.250 có thể là một nghìn hai trăm năm mươi hoặc một phẩy hai năm không tùy quy ước xuất dữ liệu. Trước khi dùng chức năng thay thế dấu chấm cho cả cột, hãy mở một chứng từ mẫu, xác nhận đơn vị tiền và vài giá trị đã biết. Tìm một dòng có cả phần nghìn và phần lẻ để nhìn ra quy ước. Chuỗi 1.250,50 cùng một phiếu ghi một nghìn hai trăm năm mươi đơn vị rưỡi sẽ cung cấp nhiều thông tin hơn chuỗi 1.250 đứng riêng.
Với dữ liệu dạng chữ dùng dấu phẩy thập phân và dấu chấm phân nhóm, có thể thử =NUMBERVALUE(B2,”,”,”.”) ở một cột mới. Nếu Excel trên máy dùng dấu chấm phẩy để ngăn cách đối số, thay hai dấu phẩy nằm ngoài dấu ngoặc kép bằng dấu chấm phẩy. Những ký tự nằm trong ngoặc kép phải giữ đúng theo quy ước của dữ liệu nguồn. Kiểm tra kết quả bằng phép nhân đôi một giá trị đã biết rồi mới kéo công thức cho toàn nhóm. Xem cách khai báo dấu phân tách của NUMBERVALUE tại Microsoft.
Loại bỏ đơn vị tiền và khoảng trắng đúng chỗ
Dữ liệu chép từ email hoặc trang web có thể chứa cả số tiền và chữ “đồng” trong cùng một chuỗi. Đổi định dạng ô sang Currency chỉ thay cách trình bày giá trị mà Excel đã hiểu, nên chưa đủ để tách số ra khỏi chữ. Hãy quan sát thanh công thức của một dòng lỗi, sao chép nguyên giá trị sang ô thử và xác định chính xác đoạn nào cần bỏ. Nếu mọi dòng đều kết thúc bằng cùng một đơn vị, có thể dùng SUBSTITUTE trong cột phụ để bỏ chuỗi cố định rồi chuyển phần còn lại thành số.
Khoảng trắng trông giống nhau trên màn hình nhưng có thể là ký tự khác nhau. Ví dụ, dữ liệu sao chép từ trang web đôi khi có khoảng trắng không ngắt dòng giữa phần số và ký hiệu tiền. Thử so sánh độ dài chuỗi bằng LEN trước và sau khi làm sạch. Nếu nghi ngờ ký tự có mã 160, có thể thử SUBSTITUTE(B2,CHAR(160),””) trên một ô trước. Hãy giữ nguyên dấu âm và dấu thập phân nếu chúng thuộc giá trị thật; một quy tắc xóa mọi thứ ngoài chữ số sẽ biến khoản hoàn tiền thành khoản thu.
Đừng đổi mã đơn và điện thoại thành số
Trong cùng một tệp xuất từ phần mềm bán hàng, mã đơn và tiền hàng đều có thể chỉ gồm các chữ số. Với tiền hàng, chuyển đúng kiểu giúp tính tổng; với mã đơn, số 0 đầu chuỗi có thể là một phần của mã. Vì vậy hãy chọn đúng cột và dừng ở dòng cuối của vùng cần xử lý. Nếu đang làm trên một bảng gồm nhiều nhóm dữ liệu, hãy đặt tên cột phụ rõ ràng như “Tiền đã chuyển” để tránh ghi đè lên cột mã khi dán kết quả.
Một ví dụ dễ gặp là danh sách gồm mã khách 00125 và số tiền ghi là 125. Hai ô này trông gần giống nhau nếu chỉ nhìn vài ký tự cuối. Sau khi chuyển nhầm cả hai thành số, mã 00125 có thể không còn khớp với phần mềm quản lý. Đối chiếu số lượng bản ghi và một vài mã có số 0 đầu trước khi bàn giao. Nếu lỗi bắt đầu ngay từ lúc mở CSV, hãy xem thêm cách chọn kiểu cột khi nhập CSV vào Excel.
Khi bảng đang lọc và bạn muốn cộng riêng những dòng nhìn thấy
Hãy xác định tổng cần dùng cho toàn bộ danh sách hay riêng nhóm đang hiển thị. Ví dụ, bạn lọc cột chi nhánh để chỉ xem doanh thu của cửa hàng A. Công thức SUM trên cả vùng vẫn có thể bao gồm số của cửa hàng B ở các hàng bị bộ lọc che đi. Đây là vấn đề phạm vi nghiệp vụ: bảng có thể tính đúng toàn bộ số liệu, trong khi người xem đang đọc kết quả như thể đó là doanh thu của riêng chi nhánh đã chọn.
Để tính tổng hiển thị trong ví dụ này, thử =SUBTOTAL(109,B2:B100), với B là cột tiền. Mã 109 dùng phép cộng và bỏ qua cả các hàng bị ẩn thủ công. Kiểm tra bằng ba dòng tiền đơn giản, lọc một dòng ra khỏi danh sách rồi đối chiếu lại với hai dòng còn nhìn thấy. Nếu cột B có số lưu dạng văn bản, bạn vẫn cần sửa kiểu trước. Việc chọn hàm theo bộ lọc và việc chuyển dữ liệu sang số là hai bước độc lập.
Khi số tổng đứng yên dù đã sửa một khoản
Trên bản sao của tệp, thử nhập công thức đơn giản bằng hai ô số mới. Thay một ô từ 10 thành 11 rồi quan sát kết quả. Nếu cả phép thử đơn giản cũng chỉ thay đổi sau khi bạn yêu cầu tính lại, kiểm tra Calculation Options trong thẻ Formulas. Chế độ tính thủ công hữu ích cho một số sổ lớn, nhưng người nhận bảng dễ nhầm một kết quả cũ với kết quả đã cập nhật. Chọn cách tính phù hợp với quy trình thực tế rồi kiểm lại bằng chính cặp số mẫu.
Nếu phép thử đơn giản cập nhật bình thường, quay lại ô tổng đang gặp lỗi. Kiểm tra xem đó thực sự là công thức hay một số cố định được dán từ báo cáo trước. Một ô hiển thị 2.500.000 có thể trông hoàn toàn hợp lý dù người lập bảng đã dùng Paste Values. Khi cần khôi phục công thức, tham khảo dòng hoặc tháng tương ứng nhưng đối chiếu lại phạm vi trước khi sao chép. Cấu trúc dữ liệu của hai tháng có thể lệch nhau vì phát sinh thêm hàng chiết khấu hoặc điều chỉnh.
Khi công thức trả về lỗi thay vì một con số
Nếu một ô trong vùng tham chiếu chứa lỗi như #N/A, lỗi đó có thể lan tới kết quả SUM. Hãy tìm ô lỗi đầu tiên, kiểm tra công thức tạo ra nó và xác định nghiệp vụ mong đợi khi không tìm được dữ liệu. Ví dụ, một mã hàng chưa được khai báo giá khác hoàn toàn với một món hàng được tặng giá bằng không. Thay mọi lỗi bằng số không có thể làm báo cáo trông gọn hơn nhưng đồng thời âm thầm bỏ đi một khoản cần thu.
Một cách thực tế là thêm cột trạng thái để hiện “thiếu giá” cạnh dòng không tra cứu được, rồi xử lý theo từng mã hàng. Khi đã bổ sung giá đúng, chạy lại tổng và đối chiếu khoản chênh với số lượng nhân đơn giá của dòng đó. Nếu chưa tìm được giá, ghi rõ số dòng chưa hoàn tất ở phần bàn giao. Người nhận có thể phân biệt một tổng tạm tính với một tổng đã đối chiếu đầy đủ mà không phải dò từng ô lỗi.
Ví dụ đối chiếu một bảng thu và hoàn tiền
Giả sử có bốn chứng từ với các giá trị lần lượt là 350.000, 120.000, âm 50.000 và 80.000 đồng. Tổng đúng bằng 500.000 đồng. Nếu khoản 120.000 được lưu dạng chữ còn ba khoản khác là số, Excel có thể cho tổng 380.000 đồng. Dùng COUNT cho vùng bốn chứng từ sẽ chỉ thấy ba ô số. Sau khi chuyển đúng khoản 120.000, tổng tăng lên đúng 120.000 đồng và số ô được đếm tăng từ ba lên bốn. Hai dấu hiệu cùng khớp là bằng chứng tốt hơn việc chỉ thấy tổng tăng.
Bây giờ giả sử người sửa vô tình xóa dấu âm khi làm sạch cột. Cả bốn ô vẫn là số và COUNT vẫn cho bốn, nhưng tổng mới thành 600.000 đồng. Chênh lệch 100.000 đồng đúng bằng hai lần khoản hoàn 50.000, bởi một khoản trừ đã biến thành một khoản cộng. Khi xử lý danh sách giao dịch, hãy kiểm tra riêng số chứng từ hoàn và tổng tiền hoàn. Cách tách hai nhóm thu và trả giúp phát hiện những lỗi mà kiểm tra kiểu dữ liệu đơn thuần sẽ bỏ sót.
Kiểm tra kết quả trước khi thay thế cột gốc
Giữ cột dữ liệu ban đầu ở bên trái và kết quả đã chuyển ở bên phải. Chọn một mẫu gồm ít nhất một khoản lớn, một khoản lẻ, một khoản âm và một ô chưa có giá trị nếu danh sách có đủ các trường hợp này. So sánh với nguồn đối chiếu ở mức từng dòng. Với bảng nhiều chi nhánh, thử cộng riêng từng chi nhánh để phát hiện trường hợp hai lỗi trái dấu triệt tiêu nhau, khiến tổng toàn công ty trông đúng dù số của từng đơn vị bị sai.
Trước khi dùng kết quả cho lần xuất tiếp theo, ghi lại nguồn tệp, quy ước dấu thập phân và bước làm sạch đã áp dụng. Nếu hàng tháng bạn nhận cùng một loại tệp, có thể xây dựng bước chuyển đổi trong truy vấn nhập dữ liệu. Hãy dùng mẫu của hai kỳ khác nhau để thử trước khi áp dụng thường xuyên. Nhà cung cấp đổi cách ghi đơn vị hoặc thêm cột có thể khiến một quy trình chạy thành công về mặt kỹ thuật nhưng trả về dữ liệu không đúng ý nghĩa.
Câu hỏi thường gặp khi Excel không cộng được số
Vì sao đổi ô sang Number mà SUM vẫn bỏ qua?
Định dạng Number mô tả cách hiển thị một giá trị số. Nếu dữ liệu bên trong hiện là chuỗi, đổi định dạng không nhất thiết chuyển giá trị thành số thật. Hãy dùng ISNUMBER để kiểm tra sau khi đổi; nếu kết quả vẫn là FALSE, thử Convert to Number hoặc dùng công thức chuyển đổi phù hợp trong cột phụ.
Có nên nhân toàn bộ bảng với một để sửa thật nhanh?
Chỉ nên thử trên bản sao của đúng vùng số cần chuyển và đối chiếu kết quả trước khi thay dữ liệu gốc. Với bảng có mã khách, số điện thoại hoặc công thức nằm xen kẽ, một thao tác trên toàn vùng có thể sửa cả những ô bạn cần giữ nguyên. Cột phụ thường dễ kiểm tra hơn vì bạn đọc được đồng thời giá trị trước và sau.
Hai ô cùng hiển thị 1,2 mà tổng lại là 2,5 thì sao?
Hãy tăng số chữ số thập phân đang hiển thị và xem giá trị thực tế. Ví dụ, hai ô cùng chứa 1,24 có thể đều hiển thị 1,2 nếu chỉ hiện một chữ số lẻ. Tổng thật bằng 2,48 và có thể hiển thị thành 2,5. Nếu quy trình yêu cầu cộng các khoản đã làm tròn từng dòng, hãy xác định độ chính xác cần dùng rồi làm tròn giá trị theo quy ước ấy trước khi cộng.
Có thể dùng căn trái hoặc tam giác xanh để nhận biết mọi ô sai không?
Hai dấu hiệu này giúp chọn ô cần kiểm tra nhưng còn phụ thuộc định dạng và thiết lập cảnh báo. Người lập bảng có thể tự căn trái cả những ô chứa số hợp lệ. Ngược lại, một ô dạng chữ cũng có thể đã được căn phải. Nên dùng ISNUMBER cho ô nghi ngờ, COUNT cho cả vùng và đối chiếu một khoản cụ thể với chứng từ để xác nhận kết luận.
Hỏi đáp nhanh Hỏi gì đáp nấy!