Số lưu dạng chữ là những ô trông như số nhưng Excel coi là văn bản, nên hàm SUM bỏ qua chúng mà không báo lỗi gì; tổng tiền ra thấp hơn thực tế và bạn chỉ phát hiện khi đối chiếu với sổ khác. Cùng họ với lỗi này là mã hàng nhìn giống hệt nhau giữa hai bảng mà tra cứu vẫn không thấy, do một bên là số, một bên là chữ, hoặc một bên dính ký tự vô hình.
Bài này giúp bạn nhận ra các ô sai kiểu, chuyển chúng về số đúng giá trị kể cả khi số tiền được ghi theo kiểu Việt Nam có dấu chấm và chữ "đ", rồi xử lý mã hàng để hai bảng khớp nhau. Mọi con số trong bài là ví dụ giả định, không phải giá thật.
Vì sao số lại thành chữ
Có bốn đường quen thuộc khiến số biến thành chữ. Đường thứ nhất là định dạng vùng không khớp: Excel trên máy đặt vùng kiểu Mỹ coi dấu chấm là dấu thập phân, nên chuỗi "1.250.000" có hai dấu chấm không thể là số, nó giữ lại dạng chữ. Ngược lại, trên máy vùng Việt Nam, "1,5" kiểu Mỹ cũng thành chữ.
Đường thứ hai là ký tự lẫn vào: chữ "đ", "VNĐ", "k", dấu cách giữa các nhóm số, hay khoảng trắng không ngắt dòng khi sao chép từ trang web. Chỉ một ký tự không phải số là cả ô thành chữ.
Đường thứ ba là dữ liệu xuất từ phần mềm ghi mọi thứ dưới dạng chữ cho an toàn, hoặc ô được định dạng Text từ trước rồi mới nhập số vào. Đường thứ tư là dấu nháy đơn ở đầu do ai đó gõ để giữ số 0, nay áp nhầm cho cột tiền.
Với mã hàng thì ngược lại: mã nên là chữ nhưng bị biến thành số. Mã "00125" mở trong Excel thành 125; mã dài nhiều chữ số hiện dạng khoa học. Khi một bảng giữ "00125" dạng chữ còn bảng kia giữ 125 dạng số, hàm tra cứu coi là hai giá trị khác nhau và trả về lỗi #N/A.
Nhận ra ô sai kiểu
Tam giác xanh ở góc ô. Excel đánh dấu ô "số lưu dạng văn bản" bằng tam giác nhỏ màu xanh ở góc trên bên trái. Chọn ô sẽ thấy biểu tượng cảnh báo với lựa chọn Convert to Number. Dấu hiệu này có thể bị tắt trong tuỳ chọn, nên đừng dựa hoàn toàn vào nó.
So COUNT với COUNTA. Trên cột tiền, =COUNTA(C2:C500) đếm mọi ô có dữ liệu, =COUNT(C2:C500) chỉ đếm ô là số thật. Hai con số chênh nhau bao nhiêu thì có bấy nhiêu ô đang là chữ. Đây là phép kiểm nhanh nhất cho cả cột.
ISTEXT và ISNUMBER từng ô. Ở cột phụ, =ISTEXT(C2) đánh dấu từng ô chữ để lọc ra xem chúng có điểm chung gì: cùng nguồn, cùng có chữ "đ", cùng có dấu chấm.
Đo độ dài để bắt ký tự ẩn. Một mã hàng năm ký tự mà =LEN(A2) trả về 6 là có ký tự thừa, thường là dấu cách ở cuối. Muốn biết chính xác ký tự đó là gì, dùng =UNICODE(RIGHT(A2,1)) để xem mã của ký tự cuối: 32 là dấu cách thường, 160 là khoảng trắng không ngắt dòng.
Chuyển chữ về số theo từng kiểu
Chọn cách chuyển theo kiểu dữ liệu bạn gặp. Công thức viết theo Excel bản tiếng Anh, dấu phẩy; máy vùng Việt Nam thường dùng chấm phẩy.
| Kiểu trong ô | Cách chuyển | Ghi chú |
|---|---|---|
| Số thuần, chỉ bị lưu dạng chữ | Convert to Number, hoặc =VALUE(C2), hoặc Paste Special nhân với 1 | Text to Columns rồi bấm Finish ngay cũng chuyển được cả cột |
| Số kiểu Việt Nam có dấu chấm ngăn nghìn, dấu phẩy thập phân | =NUMBERVALUE(C2,",",".") | Hàm có từ Excel 2013; hai đối số sau là dấu thập phân và dấu ngăn nghìn của chuỗi gốc |
| Có chữ đ, VNĐ, dấu cách | =NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(TRIM(C2),"VNĐ",""),"đ",""),",",".") | Thay "VNĐ" trước "đ"; thêm SUBSTITUTE cho CHAR(160) nếu có |
| Viết tắt kiểu 500k, 1tr2, 1,5 triệu | Nhờ AI viết công thức riêng, hoặc đánh dấu để sửa tay | Cách viết tắt rất đa dạng, dễ chuyển sai bậc nghìn, triệu |
| Số âm trong ngoặc kiểu kế toán | Thay "(" bằng "-" và bỏ ")" rồi chuyển | Kiểm lại dấu của tổng sau khi chuyển |
Cột số tiền viết tắt kiểu "1tr2" là nơi dễ sai nhất. Nếu số dòng ít, sửa tay và ghi chú; nếu nhiều, nhờ AI viết công thức nhưng thử trên bộ ca khó do bạn dựng: "1tr", "1tr2", "1tr25", "1,2tr", "500k", "1.500k". Nếu một ca bất kỳ cho kết quả sai bậc, đừng dùng công thức đó cho cả cột.
Làm cho mã hàng giữa hai bảng khớp nhau
Ví dụ giả định: bảng tồn kho của cửa hàng và bảng giá của nhà cung cấp cùng có cột mã hàng, nhưng tra mã từ bảng này sang bảng kia thì một phần ba báo #N/A. Quy trình xử lý:
- Chọn một kiểu chung cho mã: dạng chữ. Mã không dùng để tính toán, lưu dạng chữ giữ được số 0 đầu và mã dài.
- Làm sạch cả hai bên bằng cùng một công thức ở cột phụ: =UPPER(TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),""),CHAR(10),""))). Công thức bỏ khoảng trắng không ngắt dòng, ký tự xuống dòng, khoảng trắng thừa và đưa chữ về hoa để thống nhất.
- Với mã đã bị Excel đổi thành số, bù số 0 đầu bằng TEXT theo độ dài chuẩn, ví dụ =TEXT(A2,"00000") cho mã năm chữ số. Chỉ làm khi bạn chắc mọi mã đều có cùng độ dài.
- Tra cứu bằng cột phụ đã làm sạch của cả hai bên, không dùng cột gốc. Nếu vẫn còn mã không khớp, lọc riêng ra và xem từng mã.
- Với các mã còn lại, kiểm những ký tự dễ nhầm: chữ O với số 0, chữ I hoặc l với số 1, gạch ngang thường với gạch ngang dài khi sao chép từ văn bản soạn thảo. Hàm UNICODE trên từng ký tự giúp phân biệt.
- Nếu hai bên dùng hệ mã khác hẳn nhau, dựng bảng quy đổi hai cột "mã nhà cung cấp" và "mã cửa hàng", giữ ở trang tính riêng, cập nhật khi có hàng mới.
Hàm tra cứu của Excel không phân biệt chữ hoa chữ thường, nên "ab123" và "AB123" vẫn khớp. Nếu hệ mã của bạn phân biệt hoa thường, dùng hàm EXACT để so cho đúng.
Nhập đúng kiểu ngay từ tệp xuất
Phần lớn số lưu dạng chữ đến từ tệp xuất của phần mềm bán hàng, sao kê tải về, hay bảng nhận từ đối tác. Thay vì mở tệp rồi sửa, nhập tệp qua Data, From Text/CSV và Transform Data để vào Power Query. Ở đó, nhấp phải tiêu đề cột tiền, chọn Change Type, Using Locale, chọn kiểu số thập phân hoặc số nguyên với vùng Vietnamese (Vietnam) nếu tệp ghi số kiểu Việt Nam. Power Query hiểu dấu chấm là ngăn nghìn và dấu phẩy là thập phân theo vùng đã chọn.
Cột mã hàng, mã khách, số tài khoản thì làm ngược lại: đặt kiểu Text ngay trong Power Query để không bao giờ mất số 0 đầu hay bị đổi sang dạng khoa học. Thêm bước Replace Values để bỏ chữ "đ" hay "VNĐ" nếu nguồn luôn ghi kèm.
Điểm mạnh của cách này là lặp lại được. Tuần sau nhận tệp mới cùng cấu trúc, bạn chỉ cần thay tệp nguồn và bấm Refresh, mọi bước chuyển kiểu chạy lại y hệt. Nếu chưa quen giao diện Power Query, mô tả cho AI cấu trúc tệp bằng tên cột và kiểu dữ liệu mong muốn, nhờ giải thích từng bước cần bấm; không cần gửi dòng dữ liệu nào.
Với Google Sheets, khi nhập tệp CSV qua File, Import, có lựa chọn tự đổi chuỗi thành số và ngày. Thiết lập vùng của bảng tính quyết định cách đọc dấu chấm, dấu phẩy, nên đặt vùng đúng trước khi nhập.
Nhờ AI chẩn đoán vì sao không khớp
Khi đã thử các bước trên mà vẫn còn mã không khớp, AI giúp được ở phần chẩn đoán. Cách làm: chọn năm cặp mã không khớp, chạy =LEN() và =UNICODE(MID(A2,n,1)) cho từng ký tự, rồi đưa kết quả cho AI kèm mô tả. Ví dụ câu lệnh: "Hai mã hàng hiển thị giống nhau trong Excel nhưng XLOOKUP không khớp. Mã thứ nhất dài 7 ký tự với mã Unicode từng ký tự là [dãy số], mã thứ hai dài 6 ký tự với [dãy số]. Ký tự nào gây khác biệt và công thức nào để làm sạch cả cột?"
Cách hỏi này cho AI đủ dữ kiện để chỉ ra ký tự gây lỗi mà bạn không cần dán danh mục hàng. Danh mục hàng kèm giá nhập, chiết khấu riêng với nhà cung cấp là bí mật kinh doanh, không nên đưa nguyên vào chatbot dùng tài khoản cá nhân.
AI cũng hữu ích khi bạn nhận tệp từ nhiều nguồn và cần một công thức làm sạch chung. Mô tả từng nguồn hay mắc lỗi gì, nhờ AI gộp thành một công thức hoặc một chuỗi bước Power Query, rồi kiểm trên mẫu đại diện của từng nguồn. Đừng để AI tự đổi mã dựa trên "đoán ý": một mã bị sửa nhầm sang mã gần giống sẽ ghép sai giá, sai tồn kho mà không ai nhận ra.
Kiểm sau khi chuyển
Với cột tiền, so tổng trước và sau. Trước khi chuyển, tổng bằng SUM chỉ tính các ô số thật; sau khi chuyển, tổng phải tăng đúng bằng phần các ô chữ vừa được chuyển. Một cách kiểm độc lập là so tổng mới với tổng trên phần mềm bán hàng hoặc sổ quỹ trong cùng kỳ. Chênh lệch còn lại cần được giải thích, không được bỏ qua.
Kiểm giá trị cực đoan bằng MIN và MAX. Một ô chuyển sai bậc từ nghìn sang triệu sẽ nổi lên ngay ở giá trị lớn nhất. Lọc những dòng lớn hơn hẳn mức bình thường của cột và xem lại chuỗi gốc.
Với mã hàng, đếm số mã tra cứu thành công trước và sau làm sạch, và đếm số mã còn lỗi. Danh sách mã còn lỗi nên được giữ lại ở trang tính riêng, kèm lý do đã biết, để người phụ trách danh mục xử lý.
Cuối cùng, dán giá trị cho các cột đã chuyển, giữ cột gốc bên cạnh hoặc ở bản sao, và ghi vào nhật ký công thức đã dùng. Bảng có kiểu dữ liệu đúng là điều kiện để mọi phép tính và báo cáo phía sau đáng tin; phần đọc ý nghĩa của các con số đó dành cho AI Phân Tích.
Câu hỏi thường gặp
Vì sao hàm SUM không cộng một số ô trong cột tiền?
Vì những ô đó đang lưu dạng văn bản, và SUM bỏ qua văn bản mà không báo lỗi. So COUNT với COUNTA trên cột để biết có bao nhiêu ô như vậy, rồi chuyển chúng về số bằng VALUE hoặc NUMBERVALUE.
Số tiền ghi kiểu có dấu chấm ngăn nghìn chuyển thành số thế nào?
Dùng hàm NUMBERVALUE với đối số dấu thập phân là dấu phẩy và dấu ngăn nghìn là dấu chấm. Hàm này có từ Excel 2013 và không phụ thuộc thiết lập vùng của máy, nên ổn định hơn VALUE.
Mã hàng nhìn giống nhau mà VLOOKUP báo #N/A là do đâu?
Thường do một bên là số, một bên là chữ; hoặc một bên có khoảng trắng thừa, khoảng trắng không ngắt dòng, ký tự xuống dòng ẩn. Làm sạch cả hai bên bằng cùng một công thức và đưa mã về cùng dạng chữ trước khi tra.
Có nên nhờ chatbot sửa trực tiếp cả danh mục mã hàng không?
Không nên. Danh mục hàng thường đi kèm giá và chiết khấu là bí mật kinh doanh, và chatbot có thể đổi mã theo kiểu đoán ý. Hãy đưa vài cặp mã lỗi đã đo độ dài và mã ký tự để AI chẩn đoán, rồi tự áp dụng công thức.
Mã hàng bị mất số 0 đầu thì khôi phục được không?
Được nếu mọi mã có cùng độ dài chuẩn: dùng hàm TEXT với định dạng nhiều số 0, ví dụ năm số 0 cho mã năm chữ số. Nếu độ dài mã không cố định, phải lấy lại từ danh mục gốc.
Có cách chuyển cả cột chữ thành số mà không cần công thức không?
Có. Chọn cột, dùng Convert to Number từ biểu tượng cảnh báo, hoặc Text to Columns rồi bấm Finish ngay, hoặc Paste Special chọn Multiply với một ô chứa số 1. Các cách này chỉ dùng được khi chuỗi là số thuần, không lẫn chữ.