Record Macro: Sửa đổi dữ liệu ngày giờ sai trong Excel với VBA

Chuyên mục:
Excel cơ bản∙Excel nâng cao∙Excel VBA∙Góc chia sẻ∙VBA

Ở bài trước, mình có giới thiệu về Quick Access Toolbar cùng chức năng Record Macro, để bạn có thể ghi lại macro để thực hiện nhanh thao tác Paste Value & Transpose. Hôm nay, mình xin giới thiệu một vài thủ thuật giúp bạn sửa nhanh ngày tháng bị sai, chưa đúng chuẩn dữ liệu.

Xem nhanh

Quy tắc về dữ liệu: TEXT (Chữ) vs NUMBER (Số)

Trong Excel, nếu bạn để ý một chút sẽ thấy điều này. Nếu ô không được chọn canh lề sẵn thì khi chúng ta nhập vào Số (Number) thì nó ngay lập tức nằm ngay góc phải. Còn nếu là Chữ (Text) thì nó sẽ nằm ngay bên trái của ô. Với quy tắc này, bạn có thể nhận định nhanh dữ liệu đang là TEXT hay là NUMBER. Ngoài ra, bạn có thể sử dụng hàm ISTEXT hoặc ISNUMBER để kiểm tra dữ liệu lần nữa. Nếu dữ liệu căn giữa thì là dạng Logic (BOOLEAN: True/False) hoặc lỗi (#Error: #NA, #VALUE…)

Lỗi dữ liệu từ máy chấm công, hoặc các dữ liệu ngày tháng năm không đúng

Và đây là dữ liệu kết xuất từ máy chấm công mà các bạn thường hay gặp phải. Với dữ liệu dạng TEXT như thế này, thì chúng ta không thể làm phép tính, hoặc dò tìm tham chiếu chính xác theo đúng chuẩn dữ liệu. Và như đã nói ở trên, các bạn có thể kiểm tra đối chiếu lại dựa theo các quy tắc trên:

Dữ liệu ô B2 có đặc điểm, dù là ngày tháng năm nhưng dữ liệu lại căn lề trái, và dù đang chọn Format theo định dạng Number, nhưng nó cũng chẳng phải là NUMBER vì khi dùng hàm ISNUMBER lại trả về kết quả là FALSE. Điều đó có nghĩa là, dữ liệu trong ô B2 là TEXT.

Cách chuyển đổi dữ liệu ngày tháng năm đúng chuẩn với TEXT TO COLUMN

Vậy làm thế nào để chuyển đổi dữ liệu từ chữ ngày tháng năm sang dạng số đúng chuẩn dữ liệu. Có nhiều cách để sửa ngày tháng năm sai, nhưng ở đây, mình sẽ giới thiệu cùng các bạn phương pháp TEXT TO COLUMN và đồng thời chúng ta sẽ thực hiện ứng dụng việc ghi lại Macro VBA để về sau khi cần dùng chúng ta chỉ việc dùng phím tắt hoặc chọn nút được thêm vào trên Quick Access Toolbar. Cách làm tuần tự như sau:

Bước 1: Thực hiện việc chọn Ghi Macro (Record Macro), đặt tên, gán phím tắt…

Bạn chọn thẻ Developer, chọn [x] Use Relative References*
Chọn tiếp Record Macro để thực hiện việc ghi Macro.
Nhập vào tên Macro bạn muốn đặt, gán phím tắt** cho macro, và chọn nơi lưu là Personal Macro Workbook***.

Ghi chú:

* Use Relative References: Mục đích để khi bạn gọi macro từ vị trí ô khác, nó sẽ lấy vị trí ô được chọn, thay vì cứ phải cố định là ô B2.

** Gán phím tắt: Bạn có thể gán phím tùy ý bạn. Tuy nhiên tránh trường hợp bị trùng (ví dụ: CTRL+SHIFT+D sẽ là phím tắt hệ thống tự chèn ngày giờ vào ô, hay phím CTRL+D dùng để sao chép ô bên trên xuống, nên bạn không được đặt trùng)

*** Personal Macro Workbook: Là file macro lưu cá nhân của bạn sẽ chạy khi Excel được mở lên, và từ đó bạn có thể gọi ra các Macro được lưu trong file này.

Bước 2: Thực hiện thao tác Text To Column chỉ cho 1 ô B2.

Bạn chọn thẻ Data, chọn Text To Column.
Cửa sổ Convert Text to Columns Wizard hiện ra với 3 bước, bạn chọn [x] Delimited – dùng ký tự phân cách.
Phần Delimited, bạn bỏ chọn tất cả.
Ở Step 3 of 3, bạn chọn lại phần Column data format theo dạng [x] DATE: và chọn lại DMY.
Chọn Finish, sau đó chọn trở lại thẻ Developer, chọn Stop Recording để dừng việc ghi macro.

Lưu ý: Sau khi chuyển xong, nếu bạn thấy dữ liệu của mình dạng số như 43252, thì cũng đừng lo lắng, vì đây là dữ liệu đúng, việc còn lại là chọn Format theo dạng Short Date là được.

Thao tác nâng cao (không áp dụng ở các bước này, dùng tham khảo), bạn có thể áp dụng thêm cho macro khác để tự động chuyển đổi sốvới “General”:

Nếu dữ liệu của bạn là dạng số, nhưng do dấu thập phân và dấu phân cách hàng nghìn của bạn khác với hệ thống, nên dữ liệu là TEXT. Để chuyển đổi với TEXT TO COLUMN sửa dấu thập phân, dấu hàng nghìn bạn có thể chọn ở Step 3 of 3 bên trên, chọn vào phần Advanced, và khai báo dấu tương ứng để Excel tự nhận. Sau đó thực hiện chuyển đổi.

Bước 3: Chỉnh sửa một chút nội dung code VBA.

Bạn chọn thẻ Developer, chọn Visual Basic (hoặc nhấn ALT+F11) để mở cửa sổ lập trình VBA. Bạn chọn menu View => Project Explorer (hoặc nhấn CTRL+R) để xem danh sách các VBAProject của mình.
Bạn sẽ thấy VBAProject có tên là PERSONAL.XLSB, bạn mở cây thư mục phần Modules, tìm tới Module mới nhấn sẽ thấy Macro của mình vừa ghi nằm trong đó. Trong ví dụ của mình sẽ nằm trong Module3.
Sau đó bạn chọn những đoạn code dư thừa, và xóa đi. Nội dung: ActiveCell.Select, bạn sẽ phải xóa vì dư thừa, không cần thiết, đoạn lệnh thực hiện việc chọn lại ô đã được chọn.
Sau đó bạn chọn Save (File -> Save, hoặc nhấn CTRL+S để lưu lại).

Ghi chú: Nếu ở bước chọn Record Macro, bạn chưa chọn Use Relative References… thì bạn sẽ cần kiểm tra thêm phần code phải là: “Selection.TexToColumns Destination:=ActiveCell”, nếu code của bạn ghi rõ địa chỉ Range(“…”) thì bạn cần sửa lại như trong ảnh.

Và để tránh chuyển sai, chúng ta ràng thêm 1 dòng nhỏ ngay trước Selection.TextToColumns để chọn định dạng (Format) dữ liệu theo định dạng dd/mm/yyyy cho những dữ liệu đã chuyển đổi trước đó. Và do Text To Column chỉ áp dụng trên 1 cột, nên chúng ta cần kiểm tra số cột của vùng được chọn, nếu lớn hơn 1 thì chúng ta thoát Sub.

Kết quả ta có như trong ảnh sau đây:

Sub ChuyendoiNgay()
'
' ChuyendoiNgay Macro
' - Macro name: ChuyendoiNgay - Gán phím t?t: CTRL+SHIFT+Q - Luu macro trong: Personal Macro Workbook
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
 If Selection.Columns.Count > 1 Then Exit Sub
 Selection.NumberFormat = "dd/mm/yyyy"
 Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
 Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
 :=Array(1, 4), TrailingMinusNumbers:=True
End Sub

Bước 4: Chèn nút lệnh vào Quick Access Toolbar, hoặc đổi phím tắt nếu muốn.

Bạn click phải vào phần Quick Access trên góc trái trên cùng của thanh tiêu đề, và chọn Customize Quick Access Toolbar…
Bạn chọn lại Choose command from: Macros
Chọn tên macro cần thêm vào thanh Quick Access Toolbar (Thanh truy cập nhanh).
Chọn Add >> để thêm vào.
Chọn Modify nếu muốn thay đổi biểu tượng.
Và OK để hoàn tất.

Cuối cùng bạn đã có thêm công cụ để chuyển đổi nhanh cột Ngày tháng năm có định dạng dd/mm/yyyy sang chuẩn dữ liệu ngày tương ứng. Kết hợp phím tắt và nút trên Quick Access Toolbar, bạn có thể chuyển đổi nhanh chóng dữ liệu rồi phải không? Nếu vẫn không làm được, bạn đừng ngại đưa ra câu hỏi tại nhóm hỗ trợ cộng đồng trên Facebook qua địa chỉ: https://www.facebook.com/groups/hocexcel.online/

Cách chuyển đổi dữ liệu đúng chuẩn với PASTE SPECIALS…

Ngoài cách Text To Column, chúng ta có 1 cách khác nữa là sử dụng tính năng Paste Specials, tương tự, chúng ta cũng có thể làm Macro cho cách này, bạn có thể tham khảo các bước thực hiện như sau:

**Lưu ý: Cách làm này, phụ thuộc vào thiết REGION trong Control Panel của bạn. Khi bạn thiết lập mặc định là: mm/dd/yyyy, thì việc áp dụng sẽ chuyển đổi theo định dạng như vậy và ngược lại (nếu là dd/mm/yyyy, thì kết quả sẽ ra theo đúng định dạng dd/mm/yyyy).

Bạn nhập số 0 vào ô trống bất kỳ, và nhấn CTRL+C để copy số 0 đó.
Sau đó chọn vùng dữ liệu cần chuyển đổi, click phải chọn Paste Special…
Ở cửa sổ Paste Special…, bạn chọn Paste: [x] Values và Operation: [x] Add (cộng) hoặc Subtract (trừ).
Chọn OK, thế là xong.

Tương tự, bạn có thể tham khảo đoạn code VBA như sau, ở đây mình làm thao tác gán giá trị 0 vào ô cuối cùng của cột A, sau đó COPY, và PASTE SPECIALS với vùng dữ liệu được chọn để chuyển đổi. Sau khi làm xong, mình xóa đi dữ liệu đã thêm ở ô cuối cùng.

Sub ConvertDate()
'
' Keyboard Shortcut: Ctrl+Shift+T
'
 Range("A" & Rows.Count) = 0
 Range("A" & Rows.Count).Copy
 Range(Selection, Selection.End(xlDown)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:= _
 False, Transpose:=False
 Range("A" & Rows.Count).Clear
End Sub