Hàm VBA phân cách định dạng số

Chia sẻ bởi:hands

★★★★★

‹ Đã có File Excel chuyển đổi lương... Xếp lịch tổ chức giải Cầu lông ›

Quảng cáo

Chào ACE!

Em mới bắt đầu với VBA. Em có tạo hàm để chuyển đổi định dạng số sang chữ số có dấu phân cách hàng nghìn và phần thập phân.
Áp dụng hàm cho những ô giá trị số thông thường (như -0,1 hay 111,123 hay -100000,21234 hay 10000231…) thì được.
Tuy nhiên khi áp dụng với ô là kết quả của 1 hàm thì có trục trặc. Ví dụ như khi áp dụng với ô có giá trị như là: =9*1,1 hoặc =5*1000… thì được, nhưng khi áp dụng với =1,1*100 hay 100*1,1 thì báo lỗi value (mặc dù áp dụng với giá trị 110 chạy bình thường).
Code VBA em viết như sau. Mong nhận được sự giúp đỡ của ACE ạ! Em cảm ơn!
Function TEXTNUM(gt As Variant) As String
If gt < 0 Then dau = "-" Else dau = ""
duong = Abs(gt)
nguyen = Application.WorksheetFunction.RoundDown(duong, 0)
If duong = nguyen Then
ptp = ""
ElseIf duong <> nguyen Then
ptp = "," & mid(duong & "_", Application.WorksheetFunction.Find(",", duong & "_", 1) + 1, Application.WorksheetFunction.Find("_", duong & "_", 1) - Application.WorksheetFunction.Find(",", duong & "_", 1) - 1)
End If
a = nguyen
b = Len(a)
pn = ""
Do While b > 3
pn = "." & right(a, 3) & pn
b = b - 3
a = left(a, b)
Loop
If b <= 3 Then pn = a & pn
TEXTNUM = dau & pn & ptp
End Function
Hàm text không chuyển đổi linh hoạt được phần thập phân. Mỗi 1 số lại phải áp dụng 1 hàm text khác nhau (ví dụ 1000000000 thì xài text("#.###"), 1,1 thì xài text("#,#"), 2000,22 thì xài text("#.###,##"), do vậy rất rườm rà mà với biến số thay đổi thì lại phải đổi hàm text).

Vậy bạn thử thay nguyen = Application.WorksheetFunction.RoundDown(duong, 0) bằng

On Error Resume Next
    nguyen = Val(Left(gt, Application.WorksheetFunction.Find(",", gt)))
    If Err.Number Then nguyen = gt

www.giaiphapexcel.com/diendan/threads/h%C3%A0m-vba-ph%C3%A2n-c%C3%A1ch-%C4%91%E1%BB%8Bnh-d%E1%BA%A1ng-s%E1%BB%91.167725/#post-1119302

Khóa học SprinGO phù hợp

Khóa học Power PI – Ứng dung trong Nhân sự

TỔNG QUAN KHÓA HỌC: POWER BI CHO NGÀNH NHÂN SỰ Khóa học Power BI cho Nhân sự được thiết kế dành riêng cho các...

Xem khóa học

★★★★★ 5 ★ 1 👤 5 ▥ 0

‹ Đã có File Excel chuyển đổi lương Net... Xếp lịch tổ chức giải Cầu lông ›

Quảng cáo

Bạn nên đọc

Tìm ngày giờ kết thúc dự kiến

Tổng hợp file Excel tính giá thành sản phẩm cho doanh nghiệp sản xuất, gia công, xây lắp

Hướng dẫn xử lý file Excel chậm

Công thức quy đổi lương NET sang GROSS

Tính thưởng lũy tiến

Cách tách chuỗi trong dấu ngoặc Excel bằng công thức và Flash Fill

5 Responses

hands says:

08/12/2023 at 4:50 pm
Vậy bạn thử thay nguyen = Application.WorksheetFunction.RoundDown(duong, 0) bằng
```
On Error Resume Next
    nguyen = Val(Left(gt, Application.WorksheetFunction.Find(",", gt)))
    If Err.Number Then nguyen = gt
```
Em thử mà k được bác. Sửa vậy lỗi dấu ngăn cách hàng nghìn luôn. Bác có cách nào để lấy riêng toàn bộ phần thập phân k ạ?

Định dạng trực tiếp thì hơi khó chứ dùng hàm thì muốn viết sao cũng được hết.
```
=TEXT(A1,"#,##0"&IF(INT(A1)<>A1,"."&REPT("#",15),""))
```
Reply
hands says:

08/12/2023 at 4:51 pm
Định dạng trực tiếp thì hơi khó chứ dùng hàm thì muốn viết sao cũng được hết.
```
=TEXT(A1,"#,##0"&IF(INT(A1)<>A1,"."&REPT("#",15),""))
```
Em thay như bác mà test thử nó ra vậy. Không biết còn sửa chỗ nào khác nữa không
80848085

Trả kết quả với số nguyên dư phần thập phân bác

Nếu máy bạn thiết lập như vậy thì công thức sửa dấu chấm thành dấu phẩy và ngược lại. Công thức như sau:
```
=TEXT(A1;"#.##0"&IF(INT(A1)<>A1;","&REPT("#";15);""))
```
Reply
hands says:

08/12/2023 at 4:51 pm
Nếu máy bạn thiết lập như vậy thì công thức sửa dấu chấm thành dấu phẩy và ngược lại. Công thức như sau:
```
=TEXT(A1;"#.##0"&IF(INT(A1)<>A1;","&REPT("#";15);""))
```
Dạ trả kết quả ok rồi bác. Em cảm ơn.
Tiếc hàm text này trong vba nó hiểu khác. Bác có cách nào thiết kế được Function cho nó k ạ?

Trong VBA cũng vậy thôi có khác gì đâu bạn.
```
Function TEXTNUM(ByVal So As Double) As String
    Dim DinhDang As String
    DinhDang = "#,##0"
    If So <> Int(So) Then
        DinhDang = DinhDang & "." & String(15, "#")
    End If
    TEXTNUM = Format(So, DinhDang)
    If Application.International(xlDecimalSeparator) = "," Then
        TEXTNUM = Replace(TEXTNUM, ".", "_")
        TEXTNUM = Replace(TEXTNUM, ",", ".")
        TEXTNUM = Replace(TEXTNUM, "_", ",")
    End If
End Function
```
Reply
hands says:

08/12/2023 at 4:51 pm
Trong VBA cũng vậy thôi có khác gì đâu bạn.
```
Function TEXTNUM(ByVal So As Double) As String
    Dim DinhDang As String
    DinhDang = "#,##0"
    If So <> Int(So) Then
        DinhDang = DinhDang & "." & String(15, "#")
    End If
    TEXTNUM = Format(So, DinhDang)
    If Application.International(xlDecimalSeparator) = "," Then
        TEXTNUM = Replace(TEXTNUM, ".", "_")
        TEXTNUM = Replace(TEXTNUM, ",", ".")
        TEXTNUM = Replace(TEXTNUM, "_", ",")
    End If
End Function
```
Tôi có đề cập vụ này vài lần rồi. Thấy VBA dễ quá nên bà con lười học cách dùng bảng tính.
Gặp những người như vậy, bạn không có cách nào thuyết phục đâu. Họ có 1001 cớ để bảo vệ ý của mình.
Lý do người ta đồng bộ định dang cột là để dễ đọc.
Nhất là cột số. Định dạng là để có thể liếc sơ qua cột, biết được đại khái trình trạng dữ liệu chung (điển hình: tỷ lệ số lớn/ số nhỏ, có số nào đáng để ý – quá lớn , hoặc quá nhỏ)

Nếu không cần hoặc không biết cách quản lý dữ liệu thì người có thể đẻ ra hàng tá kiểu mà người ta cho rằng đẹp mắt.

Chú thích: Thằng/Mụ sếp nào đọc nổi cái báo cáo có số trình bày đủ kiểu như này thì tôi cũng phục chúng có thiên tài.

Em ở bên định giá nên sử dụng nhiều kiểu dữ liệu (giá trị tài sản lớn, diện tích không nguyên hay tỉ lệ %…). Cơ bản nhiều loại dữ liệu nên mới muốn làm Function để xài cho tiện vì công việc thôi bác.
Liên quan tới rất nhiều giấy tờ, hợp đồng nên cần sự chính xác cao.

Reply
hands says:

08/12/2023 at 4:52 pm

Em ở bên định giá nên sử dụng nhiều kiểu dữ liệu (giá trị tài sản lớn, diện tích không nguyên hay tỉ lệ %…). Cơ bản nhiều loại dữ liệu nên mới muốn làm Function để xài cho tiện vì công việc thôi bác.
Liên quan tới rất nhiều giấy tờ, hợp đồng nên cần sự chính xác cao.

Bài này hợp lý ấy chứ các bác.
Nhiều khi cần diễn giải công thức mua nhà ở Phố cổ chẳng hạn: =rand()*100*1.000.000.000
Thì có dấu "." sẽ dễ hình dung hơn, và tránh nhập thiếu số.
8086

Có bản hoàn thiện chưa bạn?

Có rồi bác
```
Function TEXTNUM(ByVal So As Double) As String
    Dim DinhDang As String
    DinhDang = "#,##0"
    If So <> Int(So) Then
        DinhDang = DinhDang & "." & String(15, "#")
    End If
    TEXTNUM = Format(So, DinhDang)
    If Application.International(xlDecimalSeparator) = "," Then
        TEXTNUM = Replace(TEXTNUM, ".", "_")
        TEXTNUM = Replace(TEXTNUM, ",", ".")
        TEXTNUM = Replace(TEXTNUM, "_", ",")
    End If
    If right(TEXTNUM, 1) = "," Then TEXTNUM = left(TEXTNUM, Len(TEXTNUM) - 1)
End Function
```
Reply