[góc chia sẻ] (gg sheet & excel 365) Hàm chuyển tiền thành chữ

[nhanly_123]

Xin được phép khai trương bài viết đầu tiên của mình bằng 1 hàm chuyển tiền thành chữ áp dụng cho excel 365 và gg sheet
Mình đã chọn lọc các hàm làm sao để copy từ excel lên gg sheet là sử dụng được liền.
Hàm chi tiết thì mình đã đính kèm trong file
Sau đây mình sẽ phân tích về cách hoạt động của hàm để mọi người cùng đánh giá mức độ hiệu quả và cùng nhau sửa lỗi.
vì 1 cộng đồng excel lớn mạnh:
Bước 1:
mình format số theo định dạng 21 con số (tại sao lại là số 21 hồi sẽ rõ).
B1 = 2385387246
=TEXT(B1;REPT(0;21))
.
Bước 2; tách 3 số thành 1 hàng tính từ trái qua phải (mục đích để cho dễ nối chuỗi lại sau khi hoàn tất)
21 số tương đương với số tối đa là 999 nghìn tỷ và cũng vừa chia hết cho 3


Bước 3: tạo 1 bảng phụ = hàm (sau này đưa vào hàm
ở đây chúng ta có 7 dòng (mặc định)
dòng đầu tương ứng với hàng tỷ, theo sau là triệu, nghìn
=VSTACK(" tỷ ";" triệu ";" nghìn, ";" tỷ ";" triệu ";" nghìn, ";"")
.
Bước 4: xóa các hàng có "000" (sau khi chuyển số thành chữ chúng ta sẽ gộp nó lại như vầy, hàm này tạm thời dừng ở đây)

Bước 5: chơi đùa với 3 con (tạo bảng vlookup)
tạo bảng = text luôn để khi tách số ra khỏi phải chuyển thành số nữa
=HSTACK( VSTACK("1";"2";"3";"4";"5";"6";"7";"8";"9");VSTACK(" một ";" hai ";" ba ";" bốn ";" năm ";" sáu ";" bảy ";" tám ";" chín "))
.
Bước 6: tách hàng trăm
thêm 1 dòng dò "0" vào
=IFNA(VLOOKUP(LEFT(B5#);VSTACK(HSTACK("0";" không ");D15#);2;0);"")&" trăm"
.
Bước 7: làm tới hàng chục
dò hàng chục trong bảng bước 5
Nếu dò đc thì cho ra số nếu ko dò ra = #NA
thì kiểm tra hàng đơn vị có <> "0" và hàng chục = "0" nếu thỏa thì cho chữ "lẻ" vào
=IFNA( VLOOKUP(MID(B5#;2;1);D15#;2;0)&" mươi";IF((MID(B5#;2;1)="0")*(RIGHT(B5#)<>"0");" lẻ ";""))
.
Bước 8: hàng đơn vị
nếu hàng chục = 1 thì vlookup vào bảng D5# bình thường, nếu ko thì thêm "1";" mốt " vào danh sách
11 = mười một (có sẵn trong bảng vlookup)
81 = tám mươi mốt (chữ mốt ko có sẵn trong bảng mình tạo cho nên phải thêm thủ công vào)
nếu lỗi trả về rỗng
=IFNA(IF(MID(B5#;2;1)="1"; VLOOKUP(RIGHT(B5#);D15#;2;0);VLOOKUP(RIGHT(B5#);VSTACK(HSTACK("1";" mốt ");D15#);2;0));"")

.
Bước 9: nối các bước 6 & 7 & 8 & 3 lại thành 1 hàm
=IFNA(VLOOKUP(LEFT(B5#);VSTACK(HSTACK("0";" không ");D15#);2;0);"")&" trăm"&IFNA( VLOOKUP(MID(B5#;2;1);D15#;2;0)&" mươi";IF((MID(B5#;2;1)="0")*(RIGHT(B5#)<>"0");" lẻ ";""))&IFNA(VLOOKUP(RIGHT(B5#);D15#;2;0);"")&VSTACK(" tỷ ";" triệu ";" nghìn, ";" tỷ ";" triệu ";" nghìn, ";"")
.
Bước 10: áp dụng bước 9 vào bước 4 (copy đoạn IF(B5#="000";""; đưa lên trước hàm ifNA rồi enter thôi

Bước 11: nối tất cả lại
=TRIM(TEXTJOIN("";TRUE;IF(B5#="000";"";IFNA(VLOOKUP(LEFT(B5#);VSTACK(HSTACK("0";" không ");D15#);2;0);"")&" trăm"&IFNA(VLOOKUP(MID(B5#;2;1);D15#;2;0)&" mươi";IF((MID(B5#;2;1)="0")*(RIGHT(B5#)<>"0");" lẻ ";""))&IFNA(VLOOKUP(RIGHT(B5#);D15#;2;0);"")&VSTACK(" tỷ, ";" triệu ";" nghìn ";" tỷ, ";" triệu ";" nghìn ";""))))

.
Bước 12: sửa từ, loại bỏ râu ria
xóa cái "không trăm lẻ " đi (nếu có)
=IF(LEFT(E35;LEN("không trăm lẻ "))="không trăm lẻ ";SUBSTITUTE(E35;"không trăm lẻ ";"";1);E35)
đổi các chữ còn lại để đọc cho quen mồm là đc (lồng SUBSTITUTE bên ngoài thôi)
"mươi năm"->"mươi lăm"
"một mươi"->"mười"
"mươi một"->"mười một"
"mươi một"->"mươi mốt"
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(LEFT(E35;LEN("không trăm lẻ "))="không trăm lẻ ";SUBSTITUTE(E35;"không trăm lẻ ";"";1);E35);"mươi năm";"mươi lăm");"một mươi";"mười");"mươi một";"mười một")
.
Bước 13: gộp các bước lại thành 1 hàm hoàn chỉnh
Tính từ bước 12 trở về 1; nếu các bước được nhắc lại quá nhiều thì mình sẽ cho nó vào làm Let (à thêm chữ đồng vào cuối nữa)
gộp công thức tới ô cuối cùng là ngưng dc rồi đó các bạn

Bước 14; đem hàm đi test


có lỗi j thì báo mình sửa nhé

Lỗi 1:

cách sửa tương đối:
VSTACK(" tỷ tỷ, ";" triệu tỷ, ";" nghìn tỷ ";" tỷ, ";" triệu, ";" nghìn ";" ")

=ARRAYFORMULA( LET(monney;C21;bangv;HSTACK( VSTACK("1";"2";"3";"4";"5";"6";"7";"8";"9");VSTACK(" một ";" hai ";" ba ";" bốn ";" năm ";" sáu ";" bảy ";" tám ";" chín ")); tree;MID(TEXT(monney; REPT(0; 21)); SEQUENCE( 7;1;1;3);3); tex; trim(TEXTJOIN("";true; if(tree="000";""; VLOOKUP( LEFT(tree);VSTACK(HSTACK("0";" không "); bangv);2;0)&" trăm "&IF((RIGHT(tree;2)<>"00")+(RIGHT(tree;2)<>"10");IFNA( VLOOKUP(MID(tree;2;1);bangv;2;0)&" mươi";IF((RIGHT(tree)<>"0");" lẻ ";""))&IFNA( VLOOKUP(RIGHT(tree);bangv;2; ) ;"")&VSTACK(" tỷ tỷ, ";" triệu tỷ, ";" nghìn tỷ ";" tỷ, ";" triệu, ";" nghìn ";" "))))); texupper;SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(LEFT(tex;len("không trăm lẻ "))="không trăm lẻ ";REPLACE(tex;1;len("không trăm lẻ ");"");if(LEFT(tex;len("không trăm "))="không trăm ";REPLACE(tex;1;len("không trăm ");"");tex));"một mươi";"mười");"mươi một";"mươi mốt");"mươi năm";"mươi lăm");rmty; SUBSTITUTE( SUBSTITUTE(texupper;LEFT(texupper);UPPER( LEFT(texupper));1)&" đồng";", đồng";" đồng");trim(SUBSTITUTE(rmty;"tỷ";""; COUNTIF( SPLIT(rmty;" ";true);"tỷ,")))))

Số càng cao càng ko chính xác

 

[vanle33]

@nhanly_123 : Tôi nói đến cách đọc số thành tiền theo quy định. Chứ chưa nói đến vấn đề kỹ thuật cách hàm hoạt động.
Tôi thử với số ở A48, dùng code đọc số thì ra kết quả như sau: "Tám trăm ba mươi hai triệu, chín trăm chín mươi bốn nghìn, chín trăm lẻ hai tỷ, ba trăm tám mươi hai triệu, một trăm ba mươi nghìn, chín trăm bốn mươi bốn đồng."
1) Không hiểu có thêm chữ "chín trăm bốn mươi bốn đồng" ở đâu ra?
2) Chữ "một trăm ba mươi nghìn" chứ không tương ứng với số 131.
3) Giữa các nhóm 3 số thì nên có dấu phảy giữa các nhóm chữ tương ứng.

 

[nhanly_123]

@nhanly_123 : Tôi nói đến cách đọc số thành tiền theo quy định. Chứ chưa nói đến vấn đề kỹ thuật cách hàm hoạt động.
Tôi thử với số ở A48, dùng code đọc số thì ra kết quả như sau: "Tám trăm ba mươi hai triệu, chín trăm chín mươi bốn nghìn, chín trăm lẻ hai tỷ, ba trăm tám mươi hai triệu, một trăm ba mươi nghìn, chín trăm bốn mươi bốn đồng."
1) Không hiểu có thêm chữ "chín trăm bốn mươi bốn đồng" ở đâu ra?
2) Chữ "một trăm ba mươi nghìn" chứ không tương ứng với số 131.
3) Giữa các nhóm 3 số thì nên có dấu phảy giữa các nhóm chữ tương ứng.

kiểm tra lại bạn ơi, 131.000 = "một trăm ba mươi mốt nghìn đông"

 

[vanle33]

Hiện tại Add-In tôi sử dụng chỉ đọc đến 14 số. Từ số 15 trở lên là chịu, không đọc thành chữ được.
Số ở A48 có đến 18 số. Ngành nào dùng đến số khủng như vậy? @nhanly_123 ?

 

[vanle33]

kiểm tra lại bạn ơi, 131.000 = "một trăm ba mươi mốt nghìn đông"

ok, tôi nhầm sang cell mà tôi dùng add in VND() để đọc số từ A48.
Còn trên file của bạn thì vẫn là như tôi trích dẫn.

 

[nhanly_123]

Hiện tại Add-In tôi sử dụng chỉ đọc đến 14 số. Từ số 15 trở lên là chịu, không đọc thành chữ được.
Số ở A48 có đến 18 số. Ngành nào dùng đến số khủng như vậy? @nhanly_123 ?

thừa vẫn tốt hơn thiếu,
add-in thì có thể sử dụng cho các phiên bản excel thấp hơn, còn hàm này là hàm mảng bị giới hạn ở các phiên bản mới nên khả năng nó rộng hơn, có thể ko áp dụng dc nhưng có cái để dùng cũng tốt mà

Bài đã được tự động gộp: 7/4/23

ok, tôi nhầm sang cell mà tôi dùng add in VND() để đọc số từ A48.
Còn trên file của bạn thì vẫn là như tôi trích dẫn.

mình thêm dấu phẩy vào rồi ấy, còn j nữa ko bạn

 

[LmoovoenX]

thừa vẫn tốt hơn thiếu,
add-in thì có thể sử dụng cho các phiên bản excel thấp hơn, còn hàm này là hàm mảng bị giới hạn ở các phiên bản mới nên khả năng nó rộng hơn, có thể ko áp dụng dc nhưng có cái để dùng cũng tốt mà

Bài đã được tự động gộp: 7/4/23

mình thêm dấu phẩy vào rồi ấy, còn j nữa ko bạn

Bạn tham khảo, hàm này chắc excel 2016 dùng được.

 

[cantl]

Hy vọng 10 năm nữa có cơ hội dùng, giờ còn đang Office 2021 free nên toàn "#NAME?".
Dù sao thì ngồi gõ =vnd() vẫn nhanh hơn.

 

[vanle33]

add-in thì có thể sử dụng cho các phiên bản excel thấp hơn, còn hàm này là hàm mảng bị giới hạn ở các phiên bản mới nên khả năng nó rộng hơn, có thể ko áp dụng dc nhưng có cái để dùng cũng tốt mà

Giờ mới thấy hàm _xlfn ......... không dùng được cho Excel 2010 32bit.
Thay đổi sang sang số khác trong A48 rồi sang B48 nháy kép / nhấn Enter hay Ctrl+Shift+Enter đều ra lỗi #NAME?

Add - In chắc dùng được cho tất cả các phiên bản Excel trong khoảng 25 năm gần đây.

 

[LmoovoenX]

Hy vọng 10 năm nữa có cơ hội dùng, giờ còn đang Office 2021 free nên toàn "#NAME?".
Dù sao thì ngồi gõ =vnd() vẫn nhanh hơn.

Làm cho vui thôi, cái hàm ở trên là hồi mới vào diễn đàn, thấy hàm mảng hay nên nghiên cứu, viết cái trên để luyện tập.
Hồi xưa cũng làm 1 cái cho mấy chị ở cơ quan, nhưng xài cột phụ nhiều.

 

[huuthang_bd]

Hy vọng 10 năm nữa có cơ hội dùng, giờ còn đang Office 2021 free nên toàn "#NAME?".

Có chuẩn đâu mà dùng

 

[VetMini]

Hiện tại Add-In tôi sử dụng chỉ đọc đến 14 số. Từ số 15 trở lên là chịu, không đọc thành chữ được.
Số ở A48 có đến 18 số. Ngành nào dùng đến số khủng như vậy?

1. Đô chính xác của số trong máy vi tính là 15 chữ số. Add-ins chỉ đọc tới 14 là bị hụt mất 1.
2. Máy nào mà có tới 18 chữ số? Trừ phi 3 chữ số cuối cùng là 0's

Chú: thời buổi hiện nay mấy cái hàm này người ta viết lại với Lambda và Let. Mấy giải thuật đời trước 2020 là xưa rồi.

 

[LmoovoenX]

1. Đô chính xác của số trong máy vi tính là 15 chữ số. Add-ins chỉ đọc tới 14 là bị hụt mất 1.
2. Máy nào mà có tới 18 chữ số? Trừ phi 3 chữ số cuối cùng là 0's

Chú: thời buổi hiện nay mấy cái hàm này người ta viết lại với Lambda và Let. Mấy giải thuật đời trước 2020 là xưa rồi.

Đúng bác. Đã mất công xài bản mới thì tận dụng những hàm mới cho gọn. Còn xài bản cũ cứ add-ins, hoặc cột phụ cho gọn. Sai còn dễ sửa.

 

[cantl]

1. Đô chính xác của số trong máy vi tính là 15 chữ số

Cái này theo tìm hiểu sơ sơ của em thì muốn tính toán số dài hơn 15 số là tất cả quá trình tính toán (+-*/ lũy thừa, căn, ...............) buộc phải thực hiện bằng các loại lập trình phải không bác.
VD: Định dạng text cho cell rồi nhập thì được, nhưng cho hàm text vào nó cũng chả ra thể thống gì.
Em đang định tính thử tài sản của lão Musk đổi ra tờ 1 ZWD rồi cuộn tròn liên tiếp lại xem được bao nhiêu nanomet mà có vẻ khó khăn quá.

 

[VetMini]

Cái này theo tìm hiểu sơ sơ của em thì muốn tính toán số dài hơn 15 số là tất cả quá trình tính toán (+-*/ lũy thừa, căn, ...............) buộc phải thực hiện bằng các loại lập trình phải không bác.

Tìm kiếm theo từ khóa Very Large Integer..

Em đang định tính thử tài sản của lão Musk đổi ra tờ 1 ZWD rồi cuộn tròn liên tiếp lại xem được bao nhiêu nanomet mà có vẻ khó khăn quá.

Có thiện chí, nhưng đi sai đường.
Tài sản lão Musk giúp gì cho kiến thức bạn?
Muốn con số lớn thì tìm con số tổng ngân lưu hằng ngày của The Fed (the US Federal Reserve System), vừa học thêm kiến thức.

 

[vanle33]

@ tác giả topic: Làm sao (công thức, code, hay cách gì gì đó) để dùng được cho tất cả các phiên bản Excel trong khoảng 25 năm gần đây. Thì chắc hợp lý hơn nhỉ?
Trong toàn bộ những người sử dụng Office thì đâu phải tất cả họ sử dụng Office từ phiên bản năm 2020 đến 2023.

 

[VetMini]

Hai quan niệm trái ngược về phát triển phần mềm (software development)

1. Hoài cổ: phần mềm phải phù hợp vói các phiên bản thượng cổ (backward compatibility). Rất tiếc, các phiên bản cũ sẽ giới hạn phần mềm rất nhiều. Điển hình cấu trúc Excel 4 khác hẳn Excel 78, 2003 khác hẳn 2010 và khác 2019.

2. Cấp tiến: phần mềm có những chỗ dễ cập nhật với những thêm thắt mới. Tức con đường MS 365, rất tiếc là MS lỡ mang một đống ứng dụng trên thị truonwgf cho nên bắt buộc phải backward compatibility. Như con ngựa thiên lý mã bị vác thêm chiếc cày.
Nếu một ngày nào đó Google Sheets qua mặt Excel 365 thì phần lớn là ở điểm này. (GS không phải vác chếc cày)

 

[Maika8008]

Hai quan niệm trái ngược về phát triển phần mềm (software development)

1. Hoài cổ: phần mềm phải phù hợp vói các phiên bản thượng cổ (backward compatibility). Rất tiếc, các phiên bản cũ sẽ giới hạn phần mềm rất nhiều. Điển hình cấu trúc Excel 4 khác hẳn Excel 78, 2003 khác hẳn 2010 và khác 2019.

2. Cấp tiến: phần mềm có những chỗ dễ cập nhật với những thêm thắt mới. Tức con đường MS 365, rất tiếc là MS lỡ mang một đống ứng dụng trên thị truonwgf cho nên bắt buộc phải backward compatibility. Như con ngựa thiên lý mã bị vác thêm chiếc cày.
Nếu một ngày nào đó Google Sheets qua mặt Excel 365 thì phần lớn là ở điểm này. (GS không phải vác chếc cày)

GS không vác cày nhưng lại vác theo cái túi.

 

[BuiQuangThuan]

Em có thử áp con chát GPT trong trang tính của Google. Thấy test thử nó tương đối đúng. Hi vọng con Bot này nó không lươn lẹo. Chứ mà đọc tùm lum là mình cũng toi luôn

 

[HeSanbi]

GPT chưa đủ trình độ tách dấu phẩy mỗi cấp số nghìn đơn vị đâu người ơi.
Nó trả lời được là do nó có tài nguyên máy tính siêu mạnh, dữ liệu siêu khủng. Chứ nó không có tri giác và trí tuệ.

Một lần gọi nó, với nhiều tham số. Tiêu hao số tiền đủ cho bạn "ăn cơm cả tháng".

"Chúng ta đang cùng nhau tàn phá trái đất một cách nhanh nhất"