Cách tự xây dựng công thức đọc số tiền bằng chữ trong excel – Phần 2

Học Excel Online tiếp tục tìm hiểu cách tự xây dựng công thức đọc số tiền bằng chữ trong excel. Trong bài viết tại phần 1, Học Excel Online đã tìm hiểu xong về sự phức tạp của việc đọc số tiền bằng chữ theo tiếng việt . Các bước xây dựng hàm đọc số bằng chữ tiếng việt, trong đó đã tìm hiểu tới phần đọc thành chữ cho hàng tỷ. Các bạn có thể xem lại bài viết tại địa chỉ:

Hướng dẫn cách tự xây dựng công thức đọc số tiền bằng chữ trong excel từ A đến Z – Phần 1

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

Xem nhanh

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

Đọc vị trí hàng trăm triệu

Tại vị trí này, việc đọc giống như đọc ở vị trí hàng trăm tỷ. Tuy nhiên có thêm một số yêu cầu:

Phần bên trái (từ vị trí hàng trăm triệu tới hàng trăm tỷ) có số nào lớn hơn 0 không. Nếu không có thì bằng rỗng, nếu có thì xét thêm trường hợp sau:
Toàn bộ hàng triệu có số nào >0 không, nếu không có (toàn bằng 0) tức là không phát sinh hàng triệu, sẽ trả về rỗng tại vị trí hàng trăm triệu
Nếu có bất kỳ số nào hàng triệu, mà tại vị trí F3 (hàng trăm triệu) bằng 0 thì đọc là “không trăm“
Còn lại tùy theo số tại F3 mà đọc thành chữ tương ứng từ “một” đến “chín“, kết hợp thêm chữ ” trăm” (có dấu cách trước chữ trăm)

Công thức đọc số tiền bằng chữ tại vị trí hàng trăm triệu:

F5=IF(SUM(C3:F3)=0,””,IF(SUM(F3:H3)=0,””,IF(F3=0,”không trăm”,CHOOSE(F3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)))

Trong đó:

SUM(C3:F3)=0 để xét phần từ vị trí hàng trăm triệu trở về bên trái (hàng trăm tỷ) có số nào lớn hơn 0 không
SUM(F3:H3)=0 để xét toàn bộ các số thuộc hàng triệu có số nào >0 không

Đọc vị trí hàng chục triệu

Vị trí này đọc giống như hàng chục tỷ. Công thức tại vị trí hàng chục triệu:

G5=IF(SUM(C3:G3)=0,””,IF(AND(G3=0,H3=0),””,IF(AND(G3=0,H3<>0),G4,CHOOSE(G3,”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”))))

Trong đó:

SUM(C3:G3)=0 để xét từ vị trí hàng chục triệu trở về bên trái (hàng trăm tỷ) có số nào lớn hơn 0 không
AND(G3=0,H3=0) xét phần hàng chục triệu và hàng triệu có số nào >0 không, nếu đều =0 thì sẽ không đọc hàng chục triệu
AND(G3=0,H3<>0) nếu không có hàng chục triệu (số 0) mà có hàng triệu (khác 0) thì sẽ đọc theo từ tại vị trí G4 (data validation/ List/ linh, lẻ)
Còn lại thì đọc theo số hàng chục từ “mười” đến “chín mươi“

Đọc vị trí hàng triệu

Vị trí này đọc giống như hàng tỷ. Công thức tại vị trí hàng triệu:

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

Trong đó:

SUM(F3:H3)=0 xét toàn bộ số hàng triệu, nếu đều = 0 thì không đọc hàng triệu
AND(SUM(C3:G3)>0,H3=0) xét khi có phần lớn hơn hàng triệu nhưng tại vị trí hàng triệu thì = 0, thì chỉ đọc chữ “ triệu” chứ không phải đọc là “không triệu”
Còn lại trường hợp có số hàng triệu thì đọc giống như cách đọc hàng tỷ, nhưng thay chữ “tỷ” bằng chữ “triệu” (vị trí H4) có kèm thêm dấu cách.

Như vậy kết quả hàng triệu như sau:

Đọc vị trí hàng nghìn

Các vị trí ở hàng nghìn: trăm nghìn, chục nghìn, nghìn được đọc giống như ở hàng triệu. Điểm chú ý duy nhất là đơn vị ở hàng nghìn có 2 cách đọc: ngàn/ nghìn tùy theo địa phương. Do đó cần tạo danh sách chọn đơn vị này tại dòng 4, cột K (chữ số đơn vị hàng nghìn)

Công thức tại các vị trí:

Hàng trăm nghìn

I5=IF(SUM(C3:I3)=0,””,IF(SUM(I3:K3)=0,””,IF(I3=0,”không trăm”,CHOOSE(I3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)))

Hàng chục nghìn

J5=IF(SUM(C3:J3)=0,””,IF(AND(J3=0,K3=0),””,IF(AND(J3=0,K3<>0),J4,CHOOSE(J3,”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”))))

Hàng nghìn

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

Như vậy kết quả hàng nghìn như sau:

Đọc vị trí hàng đơn vị

Gồm các chữ số thể hiện tiền “lẻ“, gồm trăm đồng, chục đồng, đồng.

Ở hàng đơn vị có phân ra 2 loại là “Lẻ/chẵn” và cách đọc cũng có sự khác nhau.

Có bất kỳ số nào ở hàng đơn vị thì sẽ là “lẻ” và đọc chi tiết từng đồng
Không có bất kỳ số nào ở hàng đơn vị (đều = 0) thì đọc là “đồng chẵn”
Kết thúc đọc tiền bằng chữ là dấu “./.” để thể hiện đã kết thúc đoạn bằng chữ.

Như vậy phần hàng trăm và hàng chục không ảnh hưởng gì, chỉ ảnh hưởng ở hàng đơn vị cuối cùng.

Đọc chữ số hàng trăm đồng:

L5=IF(SUM(C3:L3)=0,””,IF(SUM(L3:N3)=0,””,IF(L3=0,”không trăm”,CHOOSE(L3,”một”,”hai”,”ba”,”bốn”,”năm”,”sáu”,”bảy”,”tám”,”chín”)&” trăm”)))

Đọc chữ số hàng chục đồng:

M5=IF(SUM(C3:M3)=0,””,IF(AND(M3=0,N3=0),””,IF(AND(M3=0,N3<>0),M4,CHOOSE(M3,”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”))))

Đọc chữ số hàng đơn vị đồng:

N5=IF(SUM(C3:N3)=0,”Không “&N4,IF(AND(SUM(C3:K3)>0,SUM(L3:N3)=0),” “&N4&” chẵn”,IF(N3=0,” “&N4,CHOOSE(N3,IF(M3>0,”mốt”,”một”),”hai”,”ba”,”bốn”,IF(M3>0,”lăm”,”năm”),”sáu”,”bảy”,”tám”,”chín”)&” “&N4)))&”./.”

Trong đó:

SUM(C3:N3)=0 là tổng toàn bộ các con số trong dãy số tiền. Nếu bằng 0 tức là không có đồng nào => Đọc là “không đồng”.
AND(SUM(C3:K3)>0,SUM(L3:N3)=0) là có nhiều hơn 1 con số từ hàng tỷ tới hàng nghìn, kèm theo đó là không có số ở hàng đơn vị thì là “đồng chẵn”
N3=0 là khi ngoài 2 logic trên sẽ là đồng lẻ (không đọc chữ “lẻ”, chỉ đọc là “đồng”), số 0 sẽ không đọc.
Các số còn lại thì đọc giống các chữ số hàng đơn vị khác. Kết thúc với dấu “./.“

Kết quả cuối cùng như sau:

Xử lý viết hoa chữ cái bắt đầu

Có 1 nguyên tắc là phải viết hoa chữ cái bắt đầu trong cả dãy số tiền bằng chữ. Tuy nhiên chúng ta phải xác định được tại vị trí nào là bắt đầu, và tại vị trí đó sẽ tách ký tự đầu tiên ra để viết hoa.

Logic trên có thể diễn giải như sau (viết công thức trên dòng 6):

Nếu tại vị trí đọc ra chữ tương ứng là ô trống (giá trị rỗng) thì trả về giá trị rỗng (không xử lý)
Nếu tại vị trí đó không rỗng thì xét tổng số ô trống ở phía trước nó có bằng với vị trí số đó không, nếu đúng thì sẽ xử lý viết hoa chữ cái đầu tiên, nếu không thì trả về giá trị đọc bằng chữ đã có.

Tại C6 ta có công thức:

IF(C5=””,””,IF(COUNTIF(B5:B5,””)=C2,UPPER(LEFT(C5,1))&RIGHT(C5,LEN(C5)-1),C5))

Trong đó:

C5 là vị trí kết quả đã đọc bằng chữ cần xử lý
COUNTIF(B5:B5,””) là đếm số ô trống trong vùng từ B5. Với các vị trí khác sẽ bắt đầu đếm từ B5 và tăng dần lên (sang bên phải)
UPPER(LEFT(C5,1))&RIGHT(C5,LEN(C5)-1) là hàm xử lý viết hoa chữ cái đầu tiên trong nội dung ở C5

Xem lại bài viết: Hướng dẫn cách chỉ viết hoa chữ cái đầu tiên trong Excel

Fillright công thức tại C6 tới N6 (trong đó chú ý vị trí B5 đầu tiên trong hàm COUNTIF phải giữ nguyên, không được thay đổi; có thể cố định vị trí này với phím F4)

Kết quả thu được như sau:

(nên làm bước này trước, bởi làm ở bước sau thì hàm xử lý viết hoa sẽ rất dài, dễ bị rối)

Ghép các từ đã đọc thành kết quả số tiền bằng chữ

Từng vị trí chữ số đã được đọc, việc còn lại chỉ đơn giản làm ghép lại các từ này là xong.

Ở đây chúng ta có 12 vị trí nên sẽ phải ghép theo nguyên tắc sau:

Mỗi vị trí (mỗi ô ở dòng 6) sẽ nối với vị trí bên cạnh nó và phải thêm 1 dấu cách ở giữa 2 chữ này.
Dùng hàm TRIM để loại bỏ những dấu cách thừa trong kết quả ghép được.

Tại ô B2 chúng ta có kết quả như sau:

B2=TRIM(C6&” “&D6&” “&E6&” “&F6&” “&G6&” “&H6&” “&I6&” “&J6&” “&K6&” “&L6&” “&M6&” “&N6)

Nếu việc ghép như trên là dài và mất nhiều công, bạn có thể tìm hiểu về hàm TEXTJOIN ở các phiên bản Excel2016 trở đi, công việc sẽ đơn giản hơn rất nhiều.

Tham khảo bài viết: GHÉP NỐI KÝ TỰ VỚI HÀM CONCAT VÀ TEXTJOIN TRONG EXCEL

Kết luận

Như vậy là chúng ta đã hoàn thành được hàm đọc số bằng chữ trong Excel rồi. Logic tuy dài dòng nhưng hoàn toàn có thể phân tích được, sử dụng những hàm Excel mà ai cũng biết. Bài toán này sẽ giúp các bạn luyện tập rất tốt cho việc phân tích logic, phát triển logic thành công thức, hàm trong Excel.

Ngoài ra tính ứng dụng của hàm này rất đơn giản, chúng ta chỉ cần làm như sau: