Cách tự lập công thức số tiền bằng chữ trong Excel (Phần 1)

Bạn đã biết cách để lập công thức số tiền bằng chữ trong Excel chưa? Hãy cùng tìm hiểu thủ thuật nhanh nhất để tạo công thức số tiền bằng chữ cho file Excel của bạn nhé.

Trong những bài viết trước, Gitiho đã giới thiệu đến các bạn cách dùng công thức trên Excel để đọc số tiền bằng chữ thông qua file mẫu được cung cấp, nhưng nhược điểm của file mẫu này lại là không được sử dụng để đọc nhiều bộ số khác nhau. Bài viết dưới đây sẽ chỉ cho bạn cách để tự lập được công thức lập công thức số tiền bằng chữ trong Excel.

Sự phức tạp của việc đọc số tiền thành chữ trong Tiếng Việt

Trong Tiếng Việt việc đọc số tiền bằng chữ tưởng chừng đơn giản nhưng lại mang tính chất phức tạp như:

1. Đối với những số lớn, lên đến hàng nghìn tỉ và hơn nữa. Việc đọc số tiền bằng chữ phải phân tách các đơn vị (tỉ/ triệu/ trăm/ nghìn,...) không thể dùng chung một công thức mà cho kết quả ngay được. Phải kết hợp các công thức khác nhau, có logic khác biệt.
2. Phiên âm còn phụ thuộc vào địa phương, vùng miền (ví dụ như bảy/ bẩy, nghìn/ ngàn, linh/ lẻ,...). Mỗi nơi có cách đọc khác nhau nhưng nhìn chung đều đúng. Do vậy, công thức cần có sự linh hoạt tùy theo nhu cầu của người sử dụng.
3. Số tiền chẵn/ lẻ có đuôi kết thúc được đọc khác nhau: Với số chẵn, số tiền bằng chữ kết thúc là "đồng chẵn". Với số tiền lẻ, số tiền bằng chữ lại kết thúc là "đồng''. Công thức Excel cần xác định được rõ các số chẵn, lẻ.
4. Có quy định trong các trường hợp đọc ''một/mốt'', ''tư/bốn'', ''năm/lăm'' theo hoàn cảnh nhất định. Việc đọc số tiền bằng công thức lại đòi hỏi biện luận logic phức tạp hơn nhiều so với cách đọc trực tiếp từng số tiền.
5. Hơn thế nữa, ngoại tệ khi đọc bằng Tiếng Việt sẽ "khác'' khi đọc bằng Tiếng Anh. Do đó, cần lưu ý kho đọc các loại ngoại tệ.

Khi đã sáng tỏ các vấn đề sẽ gặp phải khi xây dựng công thức. Bây giờ, chúng ta hãy cùng nhau thực hiện lập công thức số tiền bằng chữ trong Excel. 

Các bước xây dựng công thức số tiền bằng chữ trong Excel

Trong phạm vi bài viết này, chúng ta sẽ tìm hiểu cách đọc số tới hàng trăm tỷ (gồm 12 chữ số). Những số từ hàng nghìn tỷ trở lên các bạn tự phát triển tiếp theo ý tưởng của bài viết.

Bước 1: Chuyển số bất kỳ thành số có 12 chữ số

Một chữ số nguyên bản (ví dụ 1,2,3) là số không có số 0 đằng trước (hoặc có cũng không có ý nghĩa gì). Tuy nhiên, khi muốn phân tách thứ tự của từng vị trí các chữ số trong 1 số, thì số đó cần được hiển thị dưới dạng số có 12 chữ số, cho dù số đó nhỏ hơn hàng trăm tỷ ( thì phải thêm số 0 vào trước).

Cách làm: Lấy số đó cộng với 10 mũ 12 (số có 12 chữ số 0 ở đằng sau).

Cùng hình dung với Gitiho qua ví dụ sau:

Số có 11 chữ số như 10101100011. Khi ta cộng số đó với 10 mũ 12, ta sẽ có được số này dưới dạng số có 12 chữ số như sau: 1.010.101.100.011.

Nếu lấy 12 chữ số từ phải qua trái ta sẽ có được số ban đầu (số có 11 chữ số) nhưng có thêm số 0 ở trước để phản ánh đúng từng con số trong các chữ số nhằm lập công thức số tiền bằng chữ trong Excel

Đọc thêm: Cách dùng NUMWORD Excel Add-in để chuyển Số thành Chữ

Bước 2: Tách từng chữ số ra 12 vị trí trong Excel

Trong hình trên, từ cột C đến N là 12 vị trí tương ứng với 12 chữ số trong số đã cho.

Tại dòng thứ 3, bạn cần sử dụng hàm MID để tách từng vị trí các chữ số trong dãy số ở ô B2 như sau:

=MID(text, start_num, num_chars)

Công thức nhập vào ô C3 sẽ là:

=MID($B$2,C2+1,1)

Trong đó,

• Text: dãy số ở ô B2, nhớ cố định giá trị tham chiếu của ô B2 bằng cách nhấn thêm 1 lần phím F4.
• Start_num: ký tự bắt đầu. Vì số ở ô B2 có 13 chữ số (ký tự), trong khi chúng ta chỉ lấy 12 ký tự tính từ phải sang trái, tức là lệch so với ký tự bắt đầu 1 chữ số. Do đó, Start_num sẽ bằng số tương ứng ở dòng 2 + 1.
• Num_chars: số ký tự cần lấy. Trong trường hợp này, chúng ta cần lấy duy nhất 1 ký tự tương ứng với vị trí chữ số cần tách. 

Lưu ý: Hàm MID trả dữ liệu về dưới dạng text, do đó chúng ta kết hợp hàm VALUE đểu chuyển kết quả dữ liệu về dạng số (Number).

Từ C3:N3 chúng ta sao chép công thức từ C3 sang phải (thao tác Fill Right, phím tắt Ctrl+R)

Kết quả ta thu được như hình sau đây

Bước 3: Lập hàm đọc vị trí bằng chữ để lập công thức số tiền bằng chữ trong Excel

Các vị trí có thể bao gồm các số từ 0 đến 9, trong đó số 0 có cách đọc khá phức tạp, tùy vào trước đó có số 0 nào khác hay không. Do đó, chúng ta cần xem xét 2 yếu tố sau:

1. Phần phía bên trái tính từ vị trí đó trở đi.
2. Số tại vị trí đó.

Dòng 5 trong file Excel như ví dụ, chúng ta sẽ cần lập các công thức Excel để lập công thức số tiền bằng chữ (dòng 4 dành cho mục đích chuyển đổi sang tiếng địa phương/ vùng miền theo yêu cầu).

Hàm Excel đọc chữ số ở vị trí hàng trăm tỷ

Tại ô C5, công thức hàm như sau:

=IF(C3=0,””,CHOOSE(C3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)

Lưu ý: Ô C3 là vị trí đầu tiên trong chuỗi 12 chữ số, nên chúng ta chỉ xét vị trí của chữ số hàng tỷ đó chứ không cần xét phần phía bên trái nữa.

• Nếu C3=0 thì trả kết quả là giá trị rỗng, tức là hàm không đọc chữ số ở ô này nữa.
• Nếu C3 khác 0 thì công thức sẽ trả kết quả là số (từ 1 đến 9) viết bằng chữ kèm theo đơn vị "trăm''.

Trong công thức trên bao gồm các thành phần là:

• IF(C3=0,””: Chúng ta dùng hàm IF trong Excel để xét xem chữ số ở ô C3 có bằng 0 hay không.
• CHOOSE(C3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”): được sử dụng để đọc "chữ số'' ở vị trí tương ứng thành ''chữ''. Phần phiên âm bằng ''chữ'' phải đặt trong ngoặc kép ('') vì chúng ở dạng text.
• &” trăm”: thêm đơn vị cho chữ số hàng trăm vừa đọc bằng dấu và (&), '' trăm'' để hiển thị kết quả dưới dạng text nhằm lập công thức số tiền bằng chữ trong Excel ( bạn nhớ dùng dấu cách trước từ trăm để kết quả hiện ra có khoảng cách giữa chữ số bằng chữ và đơn vị trăm)

Đọc thêm: Hướng dẫn cách chuyển số dương thành số âm trong Excel

Hàm đọc chữ số ở vị trí hàng chục tỷ để lập công thức số tiền bằng chữ trong Excel

Từ vị trí này trở đi, chúng ta phải xét cả 2 yếu tố (như đã nêu ở trên)

Do đó, cần so sánh tổng từ ô C3 đến D3 có bằng 0 không.

Nếu bằng 0, công thức sẽ không đọc số thành chữ, kết quả hiển thị sẽ là rỗng.

Nếu khác 0, chứng tỏ vị trí hàng trăm tỷ hoặc hàng chục tỷ hoặc cả 2 vị trí có dữ liệu là chữ số, khi đó chúng ta cần xem xét chi tiết hơn như sau:

• Nếu vị trí hàng trăm tỷ có dữ liệu là 1 chữ số, hàng chục tỷ và hàng tỷ đều không có chữ số nào (đều bằng 0) thì công thức chỉ đọc hàng trăm tỷ, không đọc 2 vị trí còn lại.
• Nếu hàng chục tỷ bằng 0, hàng tỷ có dữ liệu là 1 chữ số, thì công thức sẽ cho kết quả theo phiên âm ở ô D4 (''linh'' hoặc ''lẻ''), nghĩa là số mà có hàng trăm và hàng đơn vị nhưng không có hàng chục (như số 101) thì hàng chục sẽ được phiên âm (đọc) là ''linh/lẻ'' tùy theo địa phương/vùng miền.

Công thức ở ô D5 được viết như sau:

=IF(SUM(C3:D3)=0,””,IF(AND(D3=0,E3=0),””,IF(AND(D3=0,E3<>0),D4,CHOOSE(D3,”mười”,”hai mươi”,”ba mươi”,”bốn mươi”,”năm mươi”,”sáu mươi”,”bảy mươi”,”tám mươi”,”chín mươi”))))

Lưu ý: Khi đọc chữ số ở hàng đơn vị sẽ khác những vị trí hàng trăm như sau:

• Chữ số 1 đọc là "mười'' không phải ''một'', ''mười'' chứ không phải ''mươi''
• Chữ số từ 2 đến 9 được đọc kèm theo chữ ''mươi'', không phải ghép toàn bộ với chữ ''trăm'' như hàng trăm

Tiếp theo, tại vị trí D4, chúng ta cần thiết lập danh sách bởi tính năng Data Validation như hình sau:

Bạn có thể thấy công thức càng phức tạp, chúng ta càng cần phải dùng nhiều hàm IF hơn, vì phải xử lý các thông tin logic phức tạp hơn. Ngoài ra, bạn có thể sử dụng thêm hàm SUM hoặc AND để xét đồng thời các vị trí nhằm lập công thức số tiền bằng chữ trong Excel

Hàm đọc chữ số ở vị trí hàng tỷ (hàng đơn vị) trong Excel

Tương tự vị trí trên, ta cần xét 2 yếu tố: cần so sánh tổng từ ô C3 đến E3 có bằng 0 không

Nếu bằng 0, công thức sẽ không đọc số thành chữ, kết quả hiển thị sẽ là rỗng.

Nếu khác 0, chứng tỏ 3 vị trí trước đó có thể có dữ liệu là chữ số, khi đó chúng ta cần xem xét chi tiết hơn như sau:

• Nếu hàng tỷ bằng 0 và chữ số hàng trước vị trí đó >0 thì công thức chỉ đọc là "tỷ'' mà không đọc phần chữ số kèm theo. Có thể đọc theo phiên âm địa phương bằng cách sử dụng tính năng Data Validation / List tại ô E4 (như vị trí D4 đã hướng dẫn ở trên).
• Nếu tại vị trí E3 có chứa chữ số, khi đó việc đọc chữ số ở vị trí E3 phụ thuộc vào vị trí các chữ số trước đó (hàng chục). Lưu ý: số 1 và số 5 sẽ có cách đọc khác nhau như sau

                  Nếu vị trí hàng chục chứa chữ số thì hàng đơn vị đọc 1 là ''mốt'' và 5 là ''lăm''

                  Nếu vị trí hàng chục bằng 0 thì hàng đơn vị đọc 1 là ''một'' và 5 là ''năm''

Lưu ý: Chữ số 4 có thể đọc thành ''bốn'' hay ''tư'' tùy thuộc vào từng địa phương. Bạn có thể tạo danh sách cho từ này và tham chiếu ''bốn'' cho giá trị đã chọn đó. 

Tiếp tục, công thức ở ô E5 được hiển thị dưới đây:

=IF(SUM(C3:E3)=0,””,IF(AND(SUM(C3:D3)>0,E3=0),” “&E4,CHOOSE(E3,IF(D3>0,”mốt”,”một”),”hai”,”ba”,”bốn”,IF(D3>0,”lăm”,”năm”),”sáu”,”bảy”,”tám”,”chín”)&” “&E4))

Ta được kết quả lập công thức số tiền bằng chữ như sau:

Xem thêm: Hướng dẫn tự xây dựng công thức đọc số tiền bằng chữ - phần 2

Kết luận

Hy vọng qua bài viết trên, các bạn có thể sử dụng Excel để lập công thức đọc số tiền thành chữ, để tiết kiệm thời gian cũng như dễ dàng hơn trong công việc hàng ngày. Kiến thức được chia sẻ trong bài viết này sẽ rất hữu ích với các bạn làm trong lĩnh vực kế toán, tài chính hay ngân hàng. Ngay bây giờ hãy cùng luyện tập thêm với bộ bài tập Excel có đáp án của Gitiho để nằm lòng tin học văn phòng nhé.

Tuyệt đỉnh Excel: Làm chủ phần mềm Excel sau 16 giờ

Đây là khóa học được xây dựng bởi các chuyên gia tin học văn phòng đã có nhiều năm kinh nghiệm giảng dạy cho nhân viên của các tập đoàn lớn. Trong khóa học, bạn sẽ được cung cấp kiến thức từ cơ bản đến nâng cao, vừa học lý thuyết vừa làm bài tập thực hành để dễ dàng tiếp thu kiến thức. Nếu bạn có thắc mắc gì chưa hiểu về bài học, hãy để lại câu hỏi trong mục Hỏi - Đáp ngay bên dưới mỗi video bài giảng. Các chuyên gia Excel sẽ phản hồi bạn trong vòng 24h nhé. Khóa học cũng có tặng kèm những ebook cực hay về thủ thuật Excel giúp bạn làm việc nhanh chóng và hiệu quả hơn. Chúc các bạn học tập hiệu quả.