Hướng dẫn tạo công cụ chuyển đổi số sang chữ trong excel bằng tiếng Việt – Phần 2

Siêu Marketing sẽ tiếp tục bàn luận về cách tạo ra công thức chuyển đổi số tiền sang chữ trong excel. Trong phần đầu tiên của loạt bài viết, Siêu Marketing đã giải thích những khó khăn trong việc biến đổi số tiền sang chữ trong tiếng Việt và cách thiết lập từng bước của hàm để đọc số bằng chữ, cụ thể là phần cho hàng tỷ. Độc giả có thể tham khảo lại bài viết qua đường link sau:

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

Download bản mẫu tại: http://bit.ly/2Upapa1

Quy trình xây dựng hàm đọc số tiền thành chữ

Khi đọc số ở hàng trăm triệu

Tại hàng trăm triệu, phương pháp đọc không khác biệt so với hàng trăm tỷ, nhưng có thêm điều kiện như sau:

  • Xác định phía bên trái (khoảng từ hàng trăm triệu đến hàng trăm tỷ) liệu có chứa chữ số lớn hơn 0 hay không. Nếu không thì trả về rỗng, ngược lại cần kiểm tra điều sau:
  • Đoạn hàng triệu liệu có chứa bất kỳ số nào lớn hơn 0? Nếu không (tất cả đều là 0) tức là không có số hàng triệu, và sẽ trả về rỗng cho phần hàng trăm triệu.
  • Nếu hàng triệu có chứa số bất kỳ và vị trí F3 (hàng trăm triệu) là 0 thì được đọc là “không trăm“.
  • Trong trường hợp còn lại, phụ thuộc vào số ở vị trí F3, số đó sẽ được đọc từ “một” đến “chín“, cùng với từ ”trăm” (có một khoảng trống trước).

Biểu thức cho việc đọc số tiền bằng chữ tại 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”)))

Giải thích các điểm chính:

  • SUM(C3:F3)=0 kiểm tra phần từ hàng trăm triệu trở về bên trái (tới hàng trăm tỷ) để xem có chữ số nào lớn hơn 0.
  • SUM(F3:H3)=0 để đánh giá toàn bộ các số trong hàng triệu xem có số nào lớn hơn 0.

Khi đọc số ở hàng chục triệu

Phần đọc số ở hàng chục triệu cũng tương tự như hàng chục tỷ. Công thức cho 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 về phía bên trái (hàng trăm tỷ) xem có số nào lớn hơn 0 hay không.
  • AND(G3=0,H3=0) kiểm tra hàng chục triệu và hàng triệu xem có số nào lớn hơn 0 không. Nếu đều bằng 0 thì không đọc hàng chục triệu.
  • AND(G3=0,H3<>0) nếu không có số ở hàng chục triệu (là 0) nhưng lại có số ở hàng triệu (khác 0) thì đọc theo từ tại vị trí G4 (lựa chọn từ danh sách: linh, lẻ).
  • Nếu không thì đọc số hàng chục từ “mười” đến “chín mươi“.

Khi đọc số ở hàng triệu

Phần đọc số ở hàng triệu cũng như là hàng tỷ. Công thức cho 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 để xem xét toàn bộ dãy số hàng triệu nếu đều bằng 0 thì không đọc phần hàng triệu.
  • AND(SUM(C3:G3)>0,H3=0) để kiểm tra nếu có số lớn hơn hàng triệu nhưng chữ số hàng triệu lại bằng 0, chỉ cần đọc từ “triệu” mà không cần “không triệu”.
  • Trường hợp còn lại có chữ số ở hàng triệu thì đọc theo cách của hàng tỷ, nhưng thay “tỷ” với “triệu” (ở vị trí H4) và thêm dấu cách phía trước.

Kết quả khi đọc hàng triệu sẽ như sau:

Khi đọc số ở hàng nghìn

Các số tại các vị trí hàng nghìn: hàng trăm nghìn, hàng chục nghìn, nghìn được đọc tương đồng với hàng triệu. Chú ý duy nhất là chỉ có hai cách đọc cho đơn vị hàng nghìn là:ngàn/ nghìn phụ thuộc vào từng vùng miền. Vì vậy, chúng ta cần thiết lập một danh sách lựa chọn cho đơn vị này ở ô K dòng 4 (đơn vị hàng nghìn).

Áp dụng công thức như sau cho các vị trí:

  • Ở 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”)))

  • Cho vị trí 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”))))

  • Đối với vị trí 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))

Kết quả của hàng nghìn sẽ như này:

Khi đọc các số ở hàng đơn vị

Đây là các chữ số thường dùng để chỉ tiền “lẻ”, bao gồm trăm đồng, chục đồng, đồng.

Tùy vào hàng đơn vị, ta có thể phân biệt là “Lẻ/chẵn” và có cách đọc tương ứng khác nhau.

  • Nếu xuất hiện bất kỳ số nào trong hàng đơn vị thì đó sẽ được xem là “lẻ” và cần đọc chi tiết từng đồng
  • Trường hợp không có số nào ở hàng đơn vị (tất cả = 0), ta đọc là “đồng chẵn”
  • Cuối cùng, kết thúc việc đọc số tiền bằng cách sử dụng dấu “./.” để biểu thị kết thúc phần đọc chữ.

Do đó, cả hàng trăm lẫn hàng chục sẽ không ảnh hưởng, chỉ phần hàng đơn vị cuối cùng mới có sự khác biệt.

Để đọ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”)))

Về việc đọ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 đọ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 biểu thị tổng toàn bộ số lượng trong chuỗi số tiền. Nếu tổng là 0, nghĩa là không có đồng nào => Ta đọc là “không đồng”.
  • AND(SUM(C3:K3)>0,SUM(L3:N3)=0) chỉ ra rằng từ hàng tỷ đến hàng nghìn có số lớn hơn 1, nhưng không có số nào ở hàng đơn vị thì sẽ là “đồng chẵn”
  • N3=0 ám chỉ rằng nếu không thuộc hai logic trên thì sẽ đọc là đồng lẻ (không đọc từ “lẻ”, chỉ đọc “đồng”), số 0 sẽ không được đọc.
  • Các số còn lại sẽ được đọc giống như ở hàng đơn vị khác. Kết thúc với dấu “./.

Kết quả cuối cùng sẽ trình bày như sau:

Cách xử lý việc viết hoa chữ cái đầu câu

Quy tắc cơ bản là chữ cái đầu tiên cần được viết hoa trong bất kỳ dãy số tiền viết bằng chữ nào. Tuy vậy, việc xác định được vị trí bắt đầu của câu rất quan trọng để tách riêng chữ cái đầu và viết hoa nó.

Công thức có thể được diễn giải qua logic như sau (với công thức viết trên dòng 6):

  • Nếu một vị trí tương ứng trống không (giá trị rỗng), thì sẽ không xử lý gì và trả về giá trị trống.
  • Nếu không phải là giá trị trống thì kiểm tra số lượng ô trống ở trước đó có bằng với vị trí của số đó không, nếu đúng thì thực hiện việc viết hoa chữ cái đầu tiên, nếu
    • Nếu không có giá trị, hệ thống sẽ trả lại giá trị đã được chuyển đổi sang dạng chữ.

    Ở ô C6, chúng ta sử dụng công thức sau:

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

    Dưới đây là thành phần chi tiết của công thức:

    • Ô C5 chứa chuỗi cần biến đổi.
    • COUNTIF(B5:B5,””) dùng để đếm số ô trống từ ô B5 trở đi. Ô đếm sau sẽ tăng lên dần theo chiều ngang.
    • Hàm UPPER(LEFT(C5,1))&RIGHT(C5,LEN(C5)-1) có tác dụng biến chữ cái đầu tiên của chuỗi tại ô C5 thành chữ hoa.

    Bạn có thể xem lại hướng dẫn tại bài viết: Cách chỉnh sửa chỉ viết hoa chữ cái đầu trong Excel

    Kéo dài công thức này từ C6 đến N6. Lưu ý giữ nguyên vị trí ô B5 trong hàm COUNTIF và sử dụng phím F4 để cố định nó.

    Chúng ta sẽ nhận được kết quả như hình sau:

    (Việc này nên được thực hiện đầu tiên để tránh việc công thức viết hoa sau này trở nên phức tạp và khó hiểu)

    Nhập từng từ đã chuyển đổi để tạo thành kết quả cuối cùng bằng chữ

    Chúng ta chỉ cần nối từng từ đã chuyển đổi ở 12 vị trí khác nhau theo quy tắc dưới đây:

    • Liên kết mỗi từ (mỗi ô tại dòng 6) với từ tiếp theo và thêm một dấu cách giữa hai từ.
    • Sử dụng hàm TRIM để loại bỏ bất kỳ khoảng trống thừa nào.

    Ở ô B2, kết quả sẽ hiển thị như sau:

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

    Nếu cảm thấy cách ghép như vậy quá mất thời gian và phức tạp, bạn có thể tìm hiểu hàm TEXTJOIN trong Excel phiên bản 2016 trở lên để đơn giản hóa quy trình.

    Đọc thêm bài viết: Nối ký tự với hàm CONCAT và TEXTJOIN trong Excel

    Kết thúc

    Qua đó, chúng ta đã thực hiện xong hàm đọc số thành chữ trong Excel. Mặc dù logic có phần dài dòng nhưng hoàn toàn có khả năng phân tích, dựa trên các hàm mà hầu hết mọi người đều biết sử dụng. Bài toán này sẽ giúp cải thiện kỹ năng phân tích logic và áp dụng chúng để tạo ra công thức, hàm trong Excel.

    Để áp dụng hàm này vào thực tế, bạn chỉ cần thực hiện các bước sau:

    • Nhập số cần đọc vào trường Nhập số.
    • Đưa ra kết quả đọc số tiền bằng chữ tại vị trí kết quả mong muốn.

    Chúc bạn áp dụng thành công những kiến thức này vào công việc!

    Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop