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

Đây là kết quả 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 vấn đề phức tạp trên, chúng ta sẽ thực hiện việc 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 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.

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

Bản chất của 1 con số 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í các số ra tương ứng từng chữ số, chúng ta lại cần phải chuyển con số 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 giả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ố 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, chúng ta có 12 số tương ứng từ 1 đến 12 (dòng 2) giúp nhận biết từng vị trí các chữ số. Tương ứng với các cột này, tại dòng 3 chúng ta sẽ dùng hàm để tách từng vị trí các 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ề dữ liệu là dạng text. Do đó chúng ta kết hợp thêm hàm VALUE để chuyển kết quả về dữ liệu dạng Number (tránh xung đột về bản chất dữ liệu)

C3=MID($B$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ả chúng ta có được từng vị trí các con số tương ứng như sau:

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

Vì các vị trí có thể bao gồm các số từ 0 đến 9, trong đó số 0 lại có cách đọc rất đặc biệt, phụ thuộc vào trước đó có số nào khác 0 không, nên khi đọc số ra chữ 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 phục vụ cho mục đích chuyển đổi các từ mang yếu tố địa phương để tùy biến cách đọc cho phù hợp)

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

Tại vị trí này là vị trí đầu tiên 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”)&” 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 có thể xảy ra với vị trí này bao gồm:
  • 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ó con số 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ị 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: Chú ý: Hàng đơn vị khi đọc các con số 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 danh sách 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 một số logic có sử dụng thêm hàm SUM, hàm AND để xét đồng thời các vị trí.

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

Các trường hợp logic có thể xảy ra với vị trí này bao gồm:
  • 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:

2. Không đúng theo logic 1 thì tức là tại vị trí E3 có con số. Khi đó việc đọc số ở E3 ra chữ phụ thuộc 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ị đọ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ị đọ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ù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: Như vậy 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 các 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ự 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
— Cập nhật: 22-01-2023 — tungchinguyen.com tìm được thêm bài viết Lập công thức số tiền bằng chữ trong Excel thật dễ dàng với cách làm này từ website thuthuatoffice.net cho từ khoá cách tạo công thức doc so tien trong excel.

Excel không có chức năng hay hàm mặc định có sẵn nào để giúp chuyển số sang dạng từ tiếng Việt trong trang tính. Vậy có cách nào để lập công thức số tiền bằng chữ trong Excel không? Cùng ThuthuatOffice đi tìm kiếm câu trả lời cho vấn đề này trong bài viết dưới đây.

Có cách nào để lập công thức số tiền bằng chữ trong Excel không?

Câu trả lời là không và có, không là không có bất kỳ hàm có sẵn nào trong Excel có thể thực hiện việc đổi số thành chữ trong Excel cả. Còn có ở đây là có thể lập công thức số tiền bằng chữ trong Excel thông qua việc tự tạo một hàm đổi số thành chữ trong Excel với ngôn ngữ lập trình VBA (Visual Basic for Applications) và các công cụ được Microsoft tích hợp để viết và chạy các đoạn mã được người dùng tạo ra này.

Bạn nghĩ rằng chúng quá khó để có thể thực hiện được điều này. Điều đó là đúng đối với những người không biết và hiểu lập trình là gì. Thế như trong hướng dẫn sau đây chúng tôi sẽ hướng dẫn bạn cách thêm hàm đổi số thành chữ trong Excel mà thôi, việc viết ra đoạn mã này đã được chúng tôi tìm hiểu và tạo sẵn cho bạn. Theo dõi tiếp phần dưới đây để có thể thực hiện được điều này.

Lập công thức số tiền bằng chữ trong Excel bằng VBE

Sử dụng VBA (Visual Basic for Application) để thêm hàm đổi số thành chữ trong Excel với bất kỳ phiên bản nào từ Excel 2003, 2010 hoặc mới hơn (khả năng tương thích ngược từ phiên bản cao xuống phiên bản thấp hơn là không thể, bạn nên lưu ý vấn đề này khi gửi tệp trang tính Excel của mình cho người khác).

Cách lập công thức số tiền bằng chữ trong Excel được thực hiện một cách dễ dàng nhờ sự hỗ trợ của VBE như sau:

1. Sử dụng phím tắt Alt + F11 để mở Visual Basic Editor (VBE).

  • Bạn có thể truy cập vào trình soạn thảo Visual Basic bằng cách vào mục Developer trên thanh công cụ. Nếu thanh công cụ không xuất hiện mục Developer này có thể làm như sau: File > Options > Customize Ribbon > Bên cột Main Tabs tích chọn hộp đánh dấu Developer.

3. Sao chép đoạn mã sau vào trong trình soạn thảo. Đoạn mã này sẽ tự động hóa tác vụ đổi số thành chữ trong Excel cho trang tính của bạn.

Option Explicit
‘Main Function
Function SpellNumber(ByVal MyNumber)
Dim VND, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = ” Ngan “
Place(3) = ” Trieu “
Place(4) = ” Ti “
Place(5) = ” Ngin Ti “

Function GetTens(TensText)
Dim Result As String
Result = “” ‘ Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ‘ If value between 10-19…
Select Case Val(TensText)
Case 10: Result = “Muoi”
Case 11: Result = “Muoi Mot”
Case 12: Result = “Muoi Hai”
Case 13: Result = “Muoi Ba”
Case 14: Result = “Muoi Bon”
Case 15: Result = “Muoi Nam”
Case 16: Result = “Muoi Sau”
Case 17: Result = “Muoi Bay”
Case 18: Result = “Muoi Tam”
Case 19: Result = “Muoi Chin”
Case Else
End Select

Else ‘ If value between 20-99…
Select Case Val(Left(TensText, 1))
Case 2: Result = “Hai Muoi “
Case 3: Result = “Ba Muoi “
Case 4: Result = “Bon Muoi “
Case 5: Result = “Nam Muoi “
Case 6: Result = “Sau Muoi “
Case 7: Result = “Bay Muoi “
Case 8: Result = “Tam Muoi “
Case 9: Result = “Chin Muoi “
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ‘ Retrieve ones place.
End If
GetTens = Result
End Function

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = “Mot”
Case 2: GetDigit = “Hai”
Case 3: GetDigit = “Ba”
Case 4: GetDigit = “Bon”
Case 5: GetDigit = “Nam”
Case 6: GetDigit = “Sau”
Case 7: GetDigit = “Bay”
Case 8: GetDigit = “Tam”
Case 9: GetDigit = “Chin”
Case Else: GetDigit = “”
End Select
End Function

4. Nhấn Alt + Q để quay trở lại trang tính Excel. Và giờ đây hàm SpellNumber() đã sẵn sàng có trong công thức để sử dụng.

Lưu ý: Chức năng này chỉ hoạt động cho sổ làm việc hiện tại. Để sử dụng chức năng này trong một file Excel khác, bạn phải lặp lại các bước để sao chép và dán mã trong sổ làm việc đó.

Cách sử dụng hàm đổi số thành chữ trong Excel

Ngay trang tính bạn mới vừa thêm hàm SpellNumber() ở trên vào trong VBA, bạn có thể sử dụng được ngay hàm đổi số thành chữ SpellNumber() ngay mà không cần thực hiện thêm một thao tác nào khác.

Công thức tổng quát là: =SpellNumber(số tiền nhập vào ở dạng số)

Bạn có thể nhập thủ công số tiền hoặc có thể đưa giá trị là một ô tính chứa số tiền bạn muốn chuyển đổi “=SpellNumber(A1)“.

Xem cách sử dụng hàm đổi số thành chữ trong Excel thông qua ví dụ minh họa về việc nhập liệu một phiếu thu tiền với thông tin bao gom là số tiền ở dạng cả số và chữ.

Thay vì nhập thủ công ra chữ số tiền nộp trong ô B7 bạn có thể sử dụng hàm SpellNumber bằng cách nhập công thức sau vào ô B8 =SpellNumber(B7).

Cách lưu bảng tính Excel có chứa hàm SpellNumber của bạn

Bạn có thể lưu tệp của mình dưới dạng tệp có hỗ trợ Macro Excel (.xlsm) để giữ tệp của bạn ở định dạng hiện tại.

2. Bấm vào trình đơn thả xuống Save as và chọn Excel Macro-Enabled Workbook.

3. Nhấn Save để lưu lại tệp bảng tính Excel có chứa mã hàm SpellNumber.

Nhược điểm của lập công thức số tiền bằng chữ trong Excel

Trước hết, bạn phải biết VBA để sửa đổi mã theo nhu cầu của bạn. Cần phải dán mã cho mỗi sổ làm việc, nơi bạn có ý định sử dụng hàm đổi số thành chữ trong Excel. Nếu không, bạn sẽ cần tạo tệp mẫu với macro và định cấu hình Excel để tải tệp này mỗi lần bắt đầu.

Bất lợi chính của việc sử dụng macro là nếu bạn gửi sổ làm việc cho người khác, người này sẽ không nhìn thấy văn bản trừ khi Macro được tích hợp vào sổ làm việc. Và ngay cả khi nó được tích hợp sẵn, họ sẽ nhận được cảnh báo rằng có Macro trong sổ làm việc.

Trên đây là bài viết hướng dẫn cách lập công thức số tiền bằng chữ trong Excel thông qua việc tạo hàm đổi số thành chữ trong Excel với tên là SpellNumber. Đôi khi chúng ta cần bỏ ra chút thời gian và công sức để đổi lại hiệu suất làm việc được nâng cao và tốn ít thời gian hơn nếu như bạn đang phải thao tác trong một tệp Excel với dữ liệu lớn.

Bạn có thể xem qua một số bài viết về thủ thuật trên Microsoft Excel khác, giúp bạn nâng cao kiến thức về các hàm hay thao tác sử dụng nhanh trên Excel.

  • Tiết kiệm thời gian hơn với 3 cách kết nối dữ liệu giữa 2 sheet trong Excel
  • Cách in Excel có tiêu đề lặp lại trên nhiều trang mà dân văn phòng nên biết
  • Những cách xuống dòng trong ô Excel mà bạn chưa chắc đã biết. Có cả cách làm trên các thiết bị Android và iOS

Nếu thấy bài viết lập công thức số tiền bằng chữ trong Excel này hữu ích hãy để lại Like & Share cũng như bình luận ở phần bên dưới nếu có bất kỳ thắc mắc cần giải đáp nào khác. Bạn đọc có thể theo dõi thường xuyên các bài viết hữu ích khác của ThuthuatOffice được đăng tải đều đặn trên trang.

Excel –