3 Bước Làm Chủ Hàm NUMBERVALUE Trong Excel 2013 Cho Người Mới

Khi bạn import dữ liệu từ nhiều nguồn khác nhau vào Excel, con số thường xuất hiện dưới dạng văn bản thay vì số thực. Dấu phân cách thập phân là dấu phẩy ở châu Âu nhưng lại là dấu chấm ở Mỹ. Dấu phân cách hàng nghìn cũng vậy. Hàm NUMBERVALUE trong Excel 2013 giải quyết vấn đề này trong ba bước đơn giản.

Mục Lục

Bước 1: Hiểu Cú Pháp Cơ Bản

Hàm NUMBERVALUE chuyển đổi chuỗi văn bản chứa số thành giá trị số thực mà Excel có thể tính toán. Khác với hàm VALUE cũ, NUMBERVALUE cho phép bạn chỉ định rõ dấu phân cách nào được dùng trong dữ liệu gốc.

Cú pháp đầy đủ:

=NUMBERVALUE(văn_bản, [dấu_thập_phân], [dấu_phân_cách_nhóm])

Giải thích các thành phần:

văn_bản: Chuỗi chứa số cần chuyển đổi. Có thể là ô tham chiếu hoặc văn bản trong dấu ngoặc kép
dấu_thập_phân: Tùy chọn. Ký tự phân tách phần nguyên và phần thập phân. Mặc định Excel dùng cài đặt của hệ thống
dấu_phân_cách_nhóm: Tùy chọn. Ký tự phân tách hàng nghìn, triệu. Mặc định Excel dùng cài đặt của hệ thống

XEM THÊM: Tại Sao Hàm PHONETIC Trả Về #N/A? Nguyên Nhân Và Giải Pháp Cụ Thể

Ví dụ cơ bản:

Ô A1 chứa văn bản “1.234,56” với định dạng châu Âu (dấu chấm cho nghìn, dấu phẩy cho thập phân).

=NUMBERVALUE(A1, ",", ".")

Kết quả: 1234.56 dưới dạng số thực mà Excel nhận diện được.

Tôi thường gặp tình huống này khi nhận báo cáo từ chi nhánh ở Đức. File CSV của họ dùng dấu phẩy cho thập phân nhưng Excel Việt Nam mặc định dùng dấu chấm. Trước khi biết hàm này, tôi phải tìm và thay thế thủ công hàng nghìn dòng.

Bước 2: Xử Lý Các Trường Hợp Thực Tế

Dữ liệu thực tế thường không đơn giản như ví dụ sách giáo khoa. Có khoảng trắng thừa, ký tự đặc biệt, hoặc nhiều định dạng lẫn lộn trong cùng một cột.

Xử Lý Khoảng Trắng

NUMBERVALUE tự động bỏ qua khoảng trắng trong văn bản. Ô A2 chứa “1 000 000” sẽ được chuyển thành 1000000.

=NUMBERVALUE(A2)

Không cần làm gì thêm. Hàm tự xử lý khoảng trắng giữa các nhóm số.

Xử Lý Ký Tự Phần Trăm

Khi văn bản kết thúc bằng ký hiệu phần trăm, hàm tự động chia cho 100. Nhiều ký hiệu phần trăm sẽ chia thêm mỗi lần 100.

Ví dụ với ký tự phần trăm:

=NUMBERVALUE("5%")      → Kết quả: 0,05
=NUMBERVALUE("5%%")     → Kết quả: 0,0005
=NUMBERVALUE("5%%%")    → Kết quả: 0,000005

Tính năng này hữu ích khi import dữ liệu từ báo cáo tài chính có nhiều tỷ lệ phần trăm.

Kết Hợp Với Các Hàm Khác

Trong thực tế, văn bản thường chứa cả chữ lẫn số. Bạn cần tách phần số ra trước khi dùng NUMBERVALUE.

XEM THÊM: Cách Dùng Hàm TEXTSPLIT Trong 5 Phút Để Tách Dữ Liệu Excel Tự Động

Tách số từ văn bản phức tạp:

Ô A3 chứa “Doanh thu: 1.234,56 triệu đồng”

=NUMBERVALUE(MID(A3, 12, 8), ",", ".")

Hàm MID trích xuất 8 ký tự bắt đầu từ vị trí 12, sau đó NUMBERVALUE chuyển thành số. Kết quả: 1234.56

Làm sạch dữ liệu trước khi chuyển đổi:

=NUMBERVALUE(TRIM(SUBSTITUTE(A4, "đ", "")), ",", ".")

SUBSTITUTE loại bỏ ký tự “đ”, TRIM xóa khoảng trắng thừa, rồi NUMBERVALUE mới chuyển đổi.

Bước 3: Xử Lý Lỗi Và Tối Ưu

Hiểu các lỗi phổ biến giúp bạn debug nhanh hơn khi công thức không hoạt động như mong đợi.

Lỗi VALUE Thường Gặp

Hàm trả về lỗi VALUE trong ba trường hợp chính:

Trường hợp 1: Dấu phân cách thập phân xuất hiện nhiều lần

=NUMBERVALUE("1.234.56", ".", ",")    → Lỗi #VALUE!

Excel không biết dấu chấm thứ hai là thập phân hay nghìn. Kiểm tra lại dữ liệu gốc hoặc dùng SUBSTITUTE để làm sạch trước.

Trường hợp 2: Dấu phân cách nhóm xuất hiện sau dấu thập phân

=NUMBERVALUE("12,34.567", ".", ",")    → Lỗi #VALUE!

Thứ tự sai quy tắc. Dấu phân cách nhóm phải ở trước dấu thập phân.

Trường hợp 3: Văn bản không hợp lệ

=NUMBERVALUE("abc123")    → Lỗi #VALUE!

Văn bản chứa ký tự không phải số mà không thể loại bỏ được.

Xử Lý Lỗi Với IFERROR

Khi xử lý hàng nghìn dòng, một vài ô lỗi sẽ làm gián đoạn công việc. Dùng IFERROR để xử lý:

=IFERROR(NUMBERVALUE(A1, ",", "."), 0)

Nếu chuyển đổi thất bại, ô sẽ hiển thị 0 thay vì lỗi. Bạn có thể thay 0 bằng giá trị mặc định khác hoặc để trống với “”.

XEM THÊM: Tránh Lỗi NAME Khi Dùng Hàm CONCATENATE: 5 Lưu Ý Quan Trọng

Tối Ưu Với Bảng Tham Chiếu

Khi có nhiều định dạng khác nhau trong cùng một file, tạo bảng tham chiếu cho dấu phân cách:

Cột A: Dữ liệu Cột B: Dấu thập phân Cột C: Dấu phân cách nhóm Cột D: Kết quả

Công thức ở cột D:

=NUMBERVALUE(A2, B2, C2)

Cách này linh hoạt hơn hardcode dấu phân cách vào công thức. Khi định dạng thay đổi, bạn chỉ cần sửa cột B và C.

So Sánh Với Hàm VALUE

Hàm VALUE cũ vẫn hoạt động trong Excel 2013 nhưng có hạn chế:

VALUE:

Chỉ chấp nhận định dạng số theo cài đặt hệ thống
Không cho phép chỉ định dấu phân cách
Đơn giản hơn cho dữ liệu đồng nhất

NUMBERVALUE:

Linh hoạt với mọi định dạng quốc tế
Chỉ định rõ dấu phân cách
Phức tạp hơn nhưng mạnh mẽ hơn

Dùng VALUE khi dữ liệu đã đúng định dạng hệ thống. Dùng NUMBERVALUE khi import từ nguồn quốc tế hoặc cần kiểm soát chính xác định dạng.

Áp Dụng Vào Công Việc Thực Tế

Hàm NUMBERVALUE xuất hiện từ Excel 2013 và hoạt động tốt trên Excel 2016, 2019, Excel 365. Không khả dụng trên Excel 2010 trở về trước. Nếu file cần tương thích với phiên bản cũ, dùng kết hợp SUBSTITUTE và VALUE thay thế.

Ba bước trên đủ để bạn xử lý phần lớn tình huống chuyển đổi văn bản sang số. Bắt đầu với cú pháp cơ bản, sau đó thêm xử lý lỗi khi làm việc với dữ liệu phức tạp. Tính năng tự động xử lý khoảng trắng và ký tự phần trăm tiết kiệm thời gian đáng kể so với xử lý thủ công.