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

Ở bài trước mình đã giới thiệu về Thanh công cụ Truy cập Nhanh với chức năng Ghi Macro, vì vậy bạn có thể ghi macro để nhanh chóng thực hiện các thao tác Dán Giá trị & Chuyển vị. Hôm nay, mình xin giới thiệu một vài thủ thuật giúp bạn nhanh chóng sửa sai ngày tháng, sai chuẩn dữ liệu.

Quy tắc dữ liệu: TEXT (Văn bản) so với NUMBER (Số)

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

Lỗi dữ liệu từ máy chấm công hoặc dữ liệu ngày không chính xác

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

Dữ liệu ở ô B2 có đặc điểm là dù là ngày tháng nhưng dữ liệu vẫn được căn trái, và dù bạn đang chọn Format ở dạng Number thì nó cũng không phải là NUMBER vì khi sử dụng hàm ISNUMBER, nó sẽ trả về kết quả là FALSE. . Có nghĩa là, dữ liệu trong ô B2 là TEXT.

Tải game crack việt hoá tại: https://daominhha.com

Cách chuyển đổi dữ liệu ngày đúng cách với TEXT TO COLUMN

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

>>> Xem thêm:  Hướng dẫn cách khai báo biến đối tượng trong VBA Excel
Bước 1: Thực hiện lựa chọn Ghi Macro (Record Macro), đặt tên, gán phím tắt …
  1. Bạn chọn thẻ Nhà phát triển, lựa chọn [x] Sử dụng tài liệu tham khảo tương đối *
  2. Chọn tiếp theo Ghi Macro để thực hiện ghi Macro.
  3. Nhập tên của Macro bạn muốn đặt, gán phím tắt** cho macro và chọn nơi lưu dưới dạng Sổ làm việc Macro Cá nhân ***.

Ghi chú:

* Sử dụng tham chiếu tương đối: Mục đích là khi bạn gọi macro từ vị trí ô khác, nó sẽ lấy vị trí ô đã chọn, thay vì phải cố định như ô B2.

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

*** Sổ làm việc Macro Cá nhân: Là tệp macro cá nhân của bạn sẽ chạy khi Excel được mở và từ đó bạn có thể gọi ra các Macro được lưu trữ trong tệp này.

Bước 2: Thực hiện thao tác Text To Column chỉ 1 ô B2.
  1. Bạn chọn tab Dữ liệu, chọn Văn bản thành cột.
  2. Cửa sổ Chuyển đổi văn bản thành cột thuật sĩ xuất hiện với 3 bước, chọn [x] Phân định – sử dụng dấu phân cách.
  3. Phần Delimited, bạn bỏ chọn tất cả.
  4. Ở Bước 3/3, bạn chọn lại Định dạng dữ liệu cột trong biểu mẫu [x] NGÀY: và chọn lại DMY.
  5. Lựa chọn hoàn thành, sau đó chọn trở lại tab Nhà phát triển, chọn Dừng ghi để dừng 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 chính xác, tất cả những gì còn lại là chọn Định dạng dưới hình thức Ngày ngắn hạn là được.

Các thao tác nâng cao (không áp dụng trong các bước này, chỉ để tham khảo), bạn có thể áp dụng các macro khác để tự động chuyển đổi số với “Chung”:

Nếu dữ liệu của bạn là số, nhưng vì 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 bằng TEXT TO COLUMN sửa dấu thập phân, dấu nghìn bạn có thể chọn ở Bước 3/3 trên, chọn phần Nâng cao và khai báo dấu tương ứng để Excel nhận biết. Sau đó, thực hiện chuyển đổi.

>>> Xem thêm:  Một số cách để chuyển số âm thành số dương
Bước 3: Chỉnh sửa một chút nội dung của mã VBA.
  1. Bạn chọn tab 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 bạn.
  2. Bạn sẽ thấy một VBAProject có tên PERSONAL.XLSB, bạn mở phần cây thư mục Mô-đun, tìm Mô-đun mới và nhấp vào nó và bạn sẽ thấy Macro của mình vừa được ghi trong đó. Trong ví dụ của tôi, nó sẽ nằm trong Module3.
  3. Sau đó, bạn chọn mã thừa và xóa nó. Nội dung: ActiveCell.Select, bạn sẽ phải xóa vì thừa, không cần thiết, script thực hiện việc chọn lại ô đã chọn.
  4. Sau đó, bạn chọn Save (File -> Save, hoặc nhấn CTRL + S để lưu).

Lưu ý: Nếu trong bước chọn Ghi Macro, bạn chưa chọn Sử dụng Tài liệu Tham khảo Tương đối… thì bạn sẽ cần kiểm tra mã phải là: “Selection.TexToColumns Destination: = ActiveCell”, nếu mã của bạn ghi rõ dải địa chỉ (“ … ”) thì bạn cần sửa lại như trong hình.

Và để tránh chuyển nhầm, chúng ta thêm một dòng nhỏ ngay trước Selection.TextToColumns để chọn định dạng (Format) của dữ liệu theo định dạng dd / mm / yyyy cho dữ liệu đã chuyển đổi trước đó. Và vì 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 đã chọn, nếu lớn hơn 1 thì chúng ta bỏ Sub.

Kết quả chúng ta có như hình sau:

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 Thanh công cụ Truy nhập Nhanh hoặc thay đổi phím tắt nếu muốn.
  1. Bạn bè click chuột phải Chuyển đến phần Truy cập nhanh ở góc trên cùng bên trái của thanh tiêu đề và chọn Tùy chỉnh thanh công cụ truy cập nhanh…
  2. Bạn chọn lại Chọn lệnh từ: Macro
  3. Chọn tên của macro để thêm vào Thanh công cụ Truy nhập Nhanh.
  4. Lựa chọn Thêm >> Thêm vào.
  5. Lựa chọn Biến đổi nếu bạn muốn thay đổi biểu tượng.
  6. VÂNG hoàn thành.
>>> Xem thêm:  Cách thêm và xoá tiêu đề, tên các trục toạ độ cho biểu đồ Excel

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

Cách chuyển đổi dữ liệu đúng cách với PASTE ĐẶC BIỆT…

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

**Ghi chú: Cách thực hiện điều này, tùy thuộc vào cài đặt REGION trong Bảng điều khiển của bạn. Khi bạn đặt mặc định thành: mm / dd / yyyy, ứng dụng sẽ chuyển đổi theo định dạng như vậy và ngược lại (nếu là dd / mm / yyyy, kết quả sẽ xuất ra ở định dạng dd / mm chính xác). / yyyy).

  1. Bạn nhập số 0 vào bất kỳ ô trống nào và nhấn CTRL + C để sao chép số 0 đó.
  2. Sau đó chọn vùng dữ liệu cần chuyển đổi, nhấp chuột phải chọn Paste Special …
  3. Trong cửa sổ Dán đặc biệt…, hãy chọn Dán: [x] Giá trịHoạt động: [x] cộng (cộng) hoặc Subtract (trừ).
  4. Chọn OK, vậy là xong.

Tương tự, bạn có thể tham khảo đoạn mã VBA sau, ở đây mình thực hiện thao tác gán giá trị 0 cho ô cuối cùng của cột A, sau đó SAO CHÉP, ĐẶC BIỆT với dải dữ liệu đã chọn để chuyển đổi. Sau khi hoàn thành, tôi đã xóa dữ liệu được thêm vào ô 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

Nguồn : Blog hocexcel