Cách Lập Công Thức Đọc Số Tiền Bằng Chữ Trong Excel từ A đến Z

Đọc số tiền thành chữ là một trong số những chủ đề rất được quan tâm trên Excel. Trong hầu hết các loại giao dịch: hợp đồng, hóa đơn… đều yêu cầu phải đọc số tiền  bằng chữ. Trong những bài viết trước đây của Học Excel Online, chúng ta đã biết tới cách sử dụng Add-in trong Excel để làm việc này. Còn trong bài viết này, chúng ta sẽ biết thêm một cách làm khác: Sử dụng hàm, công thức trong Excel để đọc số tiền thành chữ. Hãy cùng tìm hiểu ngay nào.

Đây là tác dụng tất cả chúng ta sẽ đạt được :

Tải về file mẫu tại địa chỉ: http://bit.ly/2Upapa1

Sự phức tạp của việc đọc số tiền bằng chữ theo tiếng việt

Trước khi đi vào tìm hiểu cách làm, xin phép được nhắc lại một số vấn đề khiến cho việc đọc số tiền bằng chữ lại “khó“, khiến nhiều người không làm được:

• Số tiền có thể lớn, lên tới hàng tỷ, nghìn tỷ, thậm chí lớn hơn nữa. Do đó việc phân tách cách thành phần tỷ / triệu / nghìn / trăm khiến không thể dùng 1 công thức mà đạt được ngay. Phải phối hợp nhiều công thức khác nhau, mỗi nhóm lại có logic khác nhau.
• Phiên âm có sự thay đổi không đồng nhất giữa các địa phương. Những từ như “bảy/ bẩy”, “nghìn / ngàn”, “linh / lẻ” đôi khi có thể dùng mỗi nơi một khác, nhưng đều đúng. Do đó cần có sự biến đổi linh hoạt các từ này để phù hợp với nhu cầu sử dụng.
• Số tiền chẵn và lẻ sẽ có đuôi kết thúc khác nhau. Với tiền chẵn sẽ có kết thúc là “đồng chẵn”, còn tiền lẻ chỉ kết thúc với “đồng”. Do đó việc tùy biến phần kết thúc cũng khiến vấn đề trở nên phức tạp khi xác định khi nào là lẻ, khi nào là chẵn (trong khi các cách đọc của tiền USD không quy định việc này)
• Trong chính những từ như “một / mốt”, “bốn / tư”, “năm / lăm” lại thay đổi trong những hoàn cảnh nhất định. Việc xác định khi nào dùng từ nào đòi hỏi phả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ố.
• Ngoài ra còn một phần mở rộng nữa là khi đọc số tiền ngoại tệ theo cách tiếng việt có thể khác với cách đọc của nước ngoài (tiếng anh). Do đó cần lưu ý khi thay đổi cách đọc với các loại ngoại tệ

Với những yếu tố phức tạp trên, tất cả chúng ta sẽ triển khai việc kiến thiết xây dựng hàm đọc số tiền bằng chữ theo từng bước để tránh nhầm lẫn .

Các bước xây dựng hàm đọc số bằng chữ tiếng việt

Trong khoanh vùng phạm vi bài viết này, tất cả chúng ta sẽ khám phá 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 những bạn tự tăng trưởng tiếp theo ý tưởng sáng tạo của bài viết .

Chuyển số bất kỳ về dạng số có trên 12 chữ số

Bản chất của 1 số lượng là không có số 0 ở trước ( hoặc có thì 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í những số ra tương ứng từng chữ số, tất cả chúng ta lại cần phải chuyển số lượng về dạng có đủ 12 chữ số, nếu số đó nhỏ hơn hàng trăm tỷ thì phải thêm số 0 ở trước .Phương pháp đơn thuần để chuyển 1 số về dạng số có trên 12 chữ số là cộng số đó với 10 mũ 12 ( một số ít có 12 chữ số 0 ở phía sau )

Ví dụ:

Số 10.010.001.010 là số có 11 chữ số. Khi đem số này cộng với 10^12 ta có kết quả là: 1.010.010.001.010

Nếu lấy 12 chữ số từ phải qua trái thì ta có kết quả 010.010.001.010. Số này về bản chất giống với số ban đầu với 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ố.

Tách từng chữ số ra 12 vị trí

Từ cột C đến cột N, tất cả chúng ta có 12 số tương ứng từ 1 đến 12 ( dòng 2 ) giúp nhận ra từng vị trí những chữ số .
Tương ứng với những cột này, tại dòng 3 tất cả chúng ta sẽ dùng hàm để tách từng vị trí những chữ số trong dãy số ở ô B2 như sau :

Sử dụng hàm MID để tách từng vị trí trong dãy số, trong đó:

• Text: là dãy số ở ô B2. Khi viết công thức chúng ta chú ý cố định tham chiếu ô B2
• Start_num: là bắt đầu từ ký tự thứ mấy. Vì dãy số ở ô B2 có 13 ký tự, trong khi chúng ta chỉ lấy 12 ký tự bên phả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_char: là số ký tự cần lấy. Ở đây chúng ta chỉ lấy duy nhất 1 ký tự tương ứng với vị trí chữ số cần tách.

Tuy nhiên hàm MID chỉ trả về tài liệu là dạng text. Do đó tất cả chúng ta phối hợp thêm hàm VALUE để chuyển tác dụng về tài liệu dạng Number ( tránh xung đột về thực chất tài liệu )
C3 = MID ( USD B USD 2, C2 + 1,1 )

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ả tất cả chúng ta có được từng vị trí những số lượng tương ứng như sau :

Hàm đọc từng vị trí bằng chữ

Vì những vị trí hoàn toàn có thể gồm có những số từ 0 đến 9, trong đó số 0 lại có cách đọc rất đặc biệt quan trọng, phụ thuộc vào vào trước đó có số nào khác 0 không, nên khi đọc số ra chữ tất cả chúng ta phải xét 2 phần :

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

Các hàm sẽ viết ở dòng 5 ( dòng 4 Giao hàng cho mục tiêu quy đổi những từ mang yếu tố địa phương để tùy biến cách đọc cho tương thích )

Đọc vị trí hàng trăm tỷ

Tại vị trí này là vị trí tiên phong trong chuỗi 12 chữ số, do đó không xét phần bên trái mà xét ngay tại vị trí đó là ô C3

• Nếu  C3=0 thì trả về giá trị rỗng, tức không đọc gì cả
• Nếu C3 khác 0 thì sẽ đọc từ một đến chín, kèm theo đơn vị là “trăm” (hàng trăm tỷ)

Hàm tại C5 được viết như sau :
IF ( C3 = 0, ” ”, CHOOSE ( C3, ” một ”, ” hai ”, ” ba ”, ” bốn ”, ” năm ”, ” sáu ”, ” bảy ”, ” tám ”, ” chín ” ) và ” trăm ” )

Trong đó:

• Hàm IF để xét logic tại C3 có bằng 0 hay không.
• Hàm CHOOSE để lấy tương ứng vị trí số ở C3 với các từ phiên âm bằng chữ của số đó. Các từ phiên âm bằng chữ đặt trong dấu nháy kép vì là dữ liệu dạng text.
• Nối hàm CHOOSE với từ “trăm” bởi dấu &, trước từ “trăm” có thêm dấu cách là ” trăm” để tạo khoảng cách giữa từ phiên âm với đơn vị. Toàn bộ phần này đặt trong mệnh đề value_if_false của hàm IF. Do đó phía sau mệnh đề này sẽ đóng ngoặc để kết thúc hàm IF.

Đọc vị trí hàng chục tỷ

Các trường hợp logic hoàn toàn có thể xảy ra với vị trí này gồm có :

• Phần từ vị trí hàng chục tỷ trở về bên trái (từ C3:D3) có vị trí nào có số liệu không, hay nói cách khác là so sánh tổng C3:D3 có =0 hay không. Nếu =0 thì tức là không có nội dung gì. Khi đó sẽ không cần đọc vị trí này, kết quả là rỗng.
• Nếu logic trên là sai, tức là có thể có 1 trong 2 vị trí ở hàng trăm tỷ hoặc chục tỷ (hoặc cả 2) thì sẽ xét chi tiết hơn, bao gồm:

1. Hàng chục tỷ và hàng tỷ không có số lượng nào ( đều bằng 0 ) thì sẽ không đọc phần hàng chục tỷ ( dù có số hàng trăm tỷ thì chỉ đọc hàng trăm, không đọc hàng chục )
2. Hàng chục tỷ = 0 nhưng hàng tỷ thì có ( khác 0 ) thì sẽ đọc theo phiên âm được chọn ở D4, là “ linh ” hoặc “ lẻ ” ( tức là có hàng trăm, có hàng đơn vị chức năng nhưng không có hàng chục thì sẽ đọc hàng chục là linh / lẻ tùy theo tiếng địa phương )
Công thức tại ô D5 được viết như sau :
D5 = 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 ” ) ) ) )
Chú ý : Hàng đơn vị chức năng khi đọc những số lượng bằng chữ sẽ khác với hàng trăm ở những vị trí :

• 1 đọc là mười thay vì một, không phải đọc là mươi mà là mười
• từ 2 đến 9 đọc có kèm theo chữ mươi chứ không phải toàn bộ ghép với chữ mươi như với hàng trăm (đoạn &” trăm”).

Tại vị trí D4 thiết lập list chọn theo Data Validation / List :

Có thể thấy công thức xét nhiều hàm IF hơn, bởi logic phức tạp hơn. Trong 1 số ít logic có sử dụng thêm hàm SUM, hàm AND để xét đồng thời những vị trí .

Đọc vị trí hàng tỷ (hàng đơn vị)

Các trường hợp logic hoàn toàn có thể xảy ra với vị trí này gồm có :

• Phần từ vị trí hàng tỷ trở về bên trái (từ C3:E3) có vị trí nào có số liệu không, hay nói cách khác là so sánh tổng C3:E3 có =0 hay không. Nếu =0 thì tức là không có nội dung gì. Khi đó sẽ không cần đọc vị trí này, kết quả là rỗng.
• Nếu logic trên là sai, tức là có thể có 1 trong 3 vị trí thì sẽ xét chi tiết hơn, bao gồm:

1. Tại vị trí hàng tỷ = 0 và trước đó có số > 0 thì chỉ đọc là “ tỷ ” mà không thèm theo phần đọc chữ số. Có thể phiên âm là “ tỷ ” hay “ tỉ ” tùy theo tiếng địa phương. Vậy hoàn toàn có thể thiết lập data validation / list chọn tại ô E4 để lấy theo vị trí E4
2. Không đúng theo logic 1 thì tức là tại vị trí E3 có số lượng. Khi đó việc đọc số ở E3 ra chữ nhờ vào theo chữ số ở phía trước nó ( hàng chục ). Chú ý sẽ đọc khác ở vị trí số 1 và số 5 như sau :
Nếu có phần hàng chục thì hàng đơn vị chức năng đọc là 1 = mốt và 5 = lăm
Nếu không có phần hàng chục thì hàng đơn vị chức năng đọc là 1 = một và 5 = năm

Lưu ý: Ngoài ra có thể tùy biến ở vị trí số 4 đọc là “bốn” hay “tư” tùy theo cách đọc từng địa phương. Có thể tạo danh sách chọn riêng cho từ này và tham chiếu ở vị trí “bốn” cho giá trị được chọn đó.

Công thức tại ô E5 được viết như sau :
E5 = IF ( SUM ( C3 : E3 ) = 0, ” ”, IF ( AND ( SUM ( C3 : D3 ) > 0, E3 = 0 ), ” “ và 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 ” ) và ” “ và E4 ) )
Như vậy tất cả chúng ta đã đọc xong phần hàng tỷ. Kết quả như sau :

Nội dung bài viết đã dài, mời những bạn xem tiếp nội dung ở phần 2 của bài viết này tại địa chỉ :
Hướng dẫn cách tự kiến thiết xây dựng công thức đọc số tiền bằng chữ trong excel – Phần 2

Cảm ơn các bạn đã chú ý theo dõi!

Tham khảo :
Add-in đọc số thành chữ từ Học Excel Online

Cách viết số tiền bằng chứ trong excel theo cách đơn giản nhất

Source: http://wp.ftn61.com
Category: Thủ Thuật