Lập Dự Toán Xây Dựng Trên Excel

Chào anh em!

Bài viết này mình chia sẻ với anh em một số hàm excel, cả hàm có sẵn cũng như hàm người dùng (hàm người dùng là hàm do người dùng tự xây dựng), nội dung về các hàm xử lý chuỗi như hình minh họa bên trên.

Vấn đề trong bài, nếu chưa gặp hoặc không để ý thì tưởng như đơn giản, có gì đâu, lấy mấy hàm có sẵn trong excel mà sử dụng, nhưng thật không may, các hàm có sẵn trong excel không giải quyết được tình hình trong trường hợp này, kể cả chúng ta có lên Google để tìm, tìm khắp nơi rốt cuộc cũng đành "bó tay", chấp nhận ngồi gõ tay lại từng ô, và khi đó đây là cách mà anh em có thể tham khảo… Trường hợp này ta sẽ sử dụng một số hàm người dùng đơn giản để xử lý.

Bài viết sử dụng hình ảnh minh họa trực quan, và cách diễn đạt đơn giản để anh em không cần có kiến thức sâu về excel hay lập trình vẫn làm được.

* Đầu tiên là 2 hàm loại bỏ chữ và số trong 1 chuỗi:

Giả sử ta có chuỗi text trong 1 ô excel: 12a3bc45x6yz (lẫn lộn chữ và số)

Giờ ta cần loại bỏ số trong chuỗi đó, để chỉ còn chữ, excel không có hàm để làm việc này, muốn vậy ta phải tạo và sử dụng một hàm người dùng.

Hoặc ngược lại, ta cần loại bỏ chữ trong chuỗi đó, để chỉ còn số cũng tương tự.

Hai hàm này được minh họa như hình dưới:

Anh em chỉ cần copy đoạn mã code màu đỏ bên dưới dán vào 1 module trong excel là dùng được mà không cần tìm hiểu gì sâu:

Function get_text(selection)

Dim i As Integer, MyText As String

For i = 1 To Len(selection)

If IsNumeric(Mid(selection, i, 1)) = False Then

MyText = MyText & Mid(selection, i, 1)

End If

Next i

get_text = MyText

End Function

Function get_number(selection)

Dim i As Integer, MyNumber As String

For i = 1 To Len(selection)

If IsNumeric(Mid(selection, i, 1)) = True Then

MyNumber = MyNumber & Mid(selection, i, 1)

End If

Next i

get_number = MyNumber

End Function

Trên excel, bấm Alt+F11 > hiện 1 cửa sổ, sau đó làm tiếp như hình minh họa (2, 3, 4, 5):

Nếu thao tác đúng thì kết quả sẽ như hình sau:

Trở ra màn hình excel, kiểm tra: Ví dụ ô A3 chứa chuỗi "12a3bc45x6yz", nhập vào ô B3: =get_text(A3) > Enter, kết quả ô B3 = “abcxyz”, là ok.

- Ứng dụng thực tế: Loại bỏ số hoặc chữ trong một chuỗi, get_text() là hàm loại bỏ số, get_number() là hàm loại bỏ chữ.

* Tiếp theo là 2 hàm chuyển chữ HOA, chữ thường:

1. Hàm chuyển chữ thường thành chữ HOA:

- Giả sử ta có 1 chuỗi text trong 1 ô excel:"NGÔ anh thư" (lẫn lộn HOA, thường)

Giờ ta cần chuyển chữ HOA cả chuỗi đó để thành "NGÔ ANH THƯ"

Excel có hàm để làm việc này, ta sử dụng hàm UPPER() có sẵn là được.

Ví dụ ô A5 chứa chuỗi "NGÔ anh thư", nhập vào ô B5: =UPPER(A5) > Enter, kết quả ô B5 = "NGÔ ANH THƯ", ok.

2. Hàm chuyển chữ HOA thành chữ thường: Hoàn toàn tương tự nhưng ngược lại, sử dụng hàm LOWPER() có sẵn trong excel.

Nhưng vấn đề xuất hiện khi chuỗi đó có dấu kép (mã kép), ví dụ: "Nguyễn Tuấn Đạt"

Trong chuỗi này, chữ “Nguyễn”, vừa có dấu ê, vừa có dấu ngã, kết quả bị lỗi ngay (nói đúng hơn không phải lỗi mà kết quả không như ý muốn). Chữ Tuấn hay Đạt cũng vậy. (xem hình trên đầu bài, ô A6:B6).

Lúc này ta phải sử dụng các hàm người dùng (chuHOA và chuthuong) để giải quyết tình hình. Hàm đơn giản đến không ngờ.

Hai hàm này mình minh họa như hình dưới:

Anh em chỉ cần copy đoạn mã code màu đỏ bên dưới dán vào 1 module trong excel là dùng được (cách làm như minh họa bên trên):

Function chuHOA(selection)

MyText = UCase(selection)

chuHOA = MyText

End Function

Function chuthuong(selection)

MyText = LCase(selection)

chuthuong = MyText

End Function

Nếu thao tác đúng thì kết quả sẽ như hình sau:

Trở ra màn hình excel, kiểm tra: Ví dụ ô A6 chứa chuỗi "Nguyễn Tuấn Đạt", nhập vào ô B6: =chuHOA(A5) > Enter, kết quả ô B6 = "NGUYỄN TUẤN ĐẠT", là ok.

- Ứng dụng thực tế: Đồng bộ kiểu chữ cho danh sách tên công nhân, nhân viên, học sinh...

* Trường hợp muốn chuyển chữ cái đầu tiên thành chữ hoa: ví dụ: " NGÔ anh thư " > " Ngô Anh Thư " thì sử dụng hàm PROPER() có sẵn trong excel. Hàm này cho kết quả đúng cả khi chuỗi đó có dấu kép (mã kép) nên không cần bổ sung hàm người dùng.

Dưới đây là hàm mà mình thử viết xem sao, chạy ok nhưng vì hàm PROPER() có sẵn trong excel đã chạy đúng nên hàm dưới chỉ để tham khảo:

Function chu_hoa_dau(selection)

Dim i As Integer, MyText As String

Text = LCase(selection)

If InStr(selection, " ") > 0 Then

For i = 0 To Len(Text) - 1

If Mid(Text, Len(Text) - i, 1) = " " Then

chu_cai_dau = Mid(Text, Len(Text) - i + 1, 1)

MyText = UCase(chu_cai_dau)

MyText = MyText & Right(Text, i - 1)

MyText = Left(Text, Len(Text) - i) & MyText

Text = MyText

End If

Next i

End If

chu_cai_dau = UCase(Left(Text, 1))

MyText = chu_cai_dau & Right(Text, Len(Text) - 1)

chu_hoa_dau = MyText

End Function

* Link tải file excel đính kèm: File excel chuyển chữ Hoa - chữ thường.xls

Anh em có thể tạo mới file như bài viết hoặc sử dụng file download để chuyển đổi khi cần. Tạm thời mã code trong file đã khóa không thể view/edit, chỉ sử dụng, để tránh việc chỉnh sửa hay virus có thể gây lỗi, anh em nào cần liên hệ mình mở khóa.

Lưu ý:

Các hàm người dùng chỉ hoạt động được, nếu: (1) file đã có hàm người dùng, và (2) đã kích hoạt macro excel.

Thiếu 1 trong 2 điều kiện trên hàm sẽ không hoạt động được.

Bài viết này chia sẻ một số hàm có sẵn và hàm người dùng để xử lý một số trường hợp về chuỗi trong excel, mong nó có thể giúp ích cho công việc của anh em.

Ngoài ra mình có nhận viết các ứng dụng trên excel, chi phí thỏa thuận. Ví dụ như file sau đây mình vừa làm cho một anh em ở Nha Trang:

Có gì cần trao đổi, vui lòng liên hệ: 0919.410.280 (Zalo). Cảm ơn sự quan tâm và hẹn gặp anh emtrong một chủ đề khác !

* * *

Tuy Hòa, 25/11/2024.