Cách xử lý dữ liệu dạng DATE trong Excel cho báo cáo quản trị

Khi xây dựng báo cáo quản trị trong Excel thì chắc chắn các bạn sẽ phải làm việc với dữ liệu dạng DATE. Hãy cùng tìm hiểu cách xử lý dữ liệu dạng DATE trong Excel để có được bản báo cáo chuyên nghiệp nhé.

Dữ liệu dạng Ngày/Tháng (Date) là một trong 3 loại dữ liệu chính trong báo cáo quản trị. Khi các bạn xuất dữ liệu từ các phần mềm khác để đưa vào Excel thì sẽ gặp một số lỗi về định dạng. Trong bài viết này chúng mình sẽ hướng dẫn các bạn các kiểm tra lỗi và xử lý lỗi định dạng cho dữ liệu ngày/tháng trong Excel. 

Lưu ý: Các bạn tải "Tài liệu đính kèm" của bài viết về để có thể hiểu rõ hơn các kiến thức được chia sẻ dưới đây nhé.

Kiểm tra dữ liệu trong Excel

Dùng Filter để kiểm tra dữ liệu

Có nhiều cách để kiểm tra và tìm lỗi định dạng của dữ liệu. Chúng mình sẽ hướng dẫn các bạn cách dùng công cụ Filter để kiểm tra. Lý do chúng ta dùng công cụ này là vì các dữ liệu dạng Date trong Excel sẽ được sắp xếp tự động theo thứ tự thời gian. Đồng thời chúng cũng được chia về các năm, các quý và các tháng. 

Đặc điểm để chúng ta nhận dạng các dữ liệu lỗi là:

• Dữ liệu không được sắp xếp tự động về các Năm/Quý/Tháng là dữ liệu bị lỗi thời gian.
• Nếu đã có dữ liệu bị lỗi thời gian, thì các dữ liệu được sắp xếp cũng chưa chắc được nhận dạng đúng.

Ví dụ: Trong file thực hành mà chúng mình đính kèm bài viết có một bảng dữ liệu được xuất ra thì phần mềm dịch vụ khách hàng. Nếu chỉ quan sát thông thường thì dữ liệu dạng ngày đã được viết đúng. Bây giờ chúng ta sẽ thêm bộ lọc (Filter) để kiểm tra xem dữ liệu dạng này đã đúng định dạng hay chưa. 

 

Trong hình ảnh trên các bạn có thể thấy, bên cạnh dòng năm 2004 và 2003 có một dấu cộng nhỏ. Nếu các bạn bấm vào nó thì sẽ mở ra dữ liệu dạng tháng và ngày của các năm này. Như vậy đây là dữ liệu đúng định dạng. Nhưng còn các dữ liệu ở bên dưới thì không được sắp xếp theo năm tương ứng thì đây là dữ liệu sai.

Xem thêm: Hướng dẫn sử dụng hàm DATE cơ bản trong Excel

Cách xử lý dữ liệu dạng Date trong Excel đang bị lỗi

• Bước 1: Vào Control Panel => chọn Clock & Region => chọn Region để thống nhất về quy tắc hiển thị dữ liệu trong máy tính. Việc các bạn cài đặt chuẩn hiển thị dữ liệu trong mục này không chỉ áp dụng cho Excel mà còn cho tất cả phần mềm trong máy tính.

 

• Bước 2: Các bạn mở file Excel thực hành lên, chọn cả cột ngày tháng rồi bấm Ctrl + 1 để mở hộp thoại Format Cells.

 

Trong hộp thoại Format Cells, các bạn sẽ chọn vào mục Date rồi chọn cách hiển thị theo thứ tự là ngày - tháng - năm. 

 

• Bước 3: Sửa các dữ liệu dạng Date trong Excel bị hiển thị sai. Trước hết, các bạn dùng công cụ filter để tạo bộ lọc, sau đó các bạn bỏ tích ở phần dữ liệu hiển thị đúng thì sẽ lọc ra được dữ liệu hiển thị sai.

Thông thường sẽ có 2 loại lỗi hay gặp trong với dữ liệu dạng Date là:

• Dữ liệu dạng Date trong Excel bị xuất ra dạng Text
• Dữ liệu dạng Date trong Excel hiển thị tháng trước, ngày sau.

Chúng mình sẽ lấy ví dụ cụ thể về 3 cách để xử lý lỗi nêu trên để các bạn nắm rõ hơn.

Ví dụ 1: Dùng hàm VALUE để sửa lỗi dữ liệu dạng Date trong Excel

Các bạn mở sheet VD1 trong file thực hành đính kèm bài viết lên. Cách xử lý của trường hợp này là dùng hàm VALUE để chuyển dữ liệu ngày tháng bị xuất thành dạng Text sang dạng Number rồi chuyến tiếp thành dạng Date.

Đầu tiên, các bạn tạo bộ lọc cho toàn bộ vùng dữ liệu trong trang tính này:

 

Tiếp theo các bạn chọn sắp xếp từ Z - A để đẩy các dòng trống trong bảng dữ liệu xuống dưới. Điều này sẽ giúp việc thực hiện các bước tiếp theo không bị lỗi.

 

Bây giờ các bạn chọn toàn bộ cột ngày tháng rồi chuyển định dạng của nó về Short Date:

 

Sau đó các bạn bỏ tích ở dòng dữ liệu được hiển thị theo năm và tháng đi vì đây là dữ liệu đúng thì chúng ta sẽ còn một bảng chỉ chứa các dữ liệu dạng Date bị lỗi.

 

Lúc này chúng ta có thể dùng ngay hàm VALUE trong Excel để lấy giá trị của dữ liệu dạng Text ở đây:

 

Sau khi áp dụng công thức hàm VALUE cho tất cả các dòng rồi thì bạn chuyển cả cột đã áp dụng hàm đó về dữ liệu dạng Short Date là đã thu được kết quả như mong muốn.

 

Cuối cùng các bạn copy cột đã áp dụng hàm VALUE rồi Paste vào cột ngày tháng nhưng chọn Paste Option là Paste Value (có biểu tượng 123) để chỉ dán giá trị, không dán công thức. Các bạn dùng công cụ Filter để kiểm tra lại thì sẽ không thấy còn tồn tại dữ liệu ngày tháng bị sai nữa:

 

Xem thêm: Hướng dẫn sử dụng hàm EDATE trong Excel

Ví dụ 2: Dùng Text to Column để sửa lỗi dữ liệu dạng Date trong Excel

Các bạn mở sheet VD2 và Copy cột chứa dữ liệu ngày tháng sang một cột trống khác. 

 

Quan sát dữ liệu của cột này thì bạn thấy nó đang hiển thị theo dạng: tháng - ngày - năm và được ngăn cách bởi dấu gạch chéo "/". Bây giờ chúng ta sẽ dùng công cụ Text to Column để tách riêng ngày, tháng, năm ra các cột khác nhau. Các bạn chỉ cần chọn cả cột => vào Data => chọn Text to Column:

 

Sau đó có một hộp thoại hiện lên thì các bạn bấm Next ngay ở bước đầu tiên mà không cần thay đổi gì. Ở bước kế tiếp, các bạn điền dấu gạch chéo vào ô Other rồi bấm Next:

 

Đến bước cuối cùng các bạn bấm Finish là được:

 

Sau khi thu được cột như trong hình ảnh dưới đây, các bạn dùng hàm DATE trong Excel để ghép dữ liệu của 3 cột này lại thành dạng: ngày - tháng - năm. 

 

Cuối cùng các bạn Copy & Paste Value cột dữ liệu đúng này vào cột ngày tháng ban đầu là được.

Xem thêm: Cách sửa nhanh lỗi ngày tháng trong Excel của dữ liệu được trích xuất

Ví dụ 3: Dùng Find & Replace để sửa lỗi dữ liệu dạng Date trong Excel

Hai cách ở ví dụ 1 và ví dụ 2 được áp dụng khi bạn có dữ liệu dạng Date trong Excel bị xuất ra dạng Text. Còn cách mà chúng mình hướng dẫn sau đây được áp dụng khi các bạn có dữ liệu bị sắp xếp ngược tháng trước, ngày sau. Trước hết các bạn chuyển sang sheet VD3.

Các bạn chọn toàn bộ cột ngày tháng => bấm Ctrl + H để mở hộp thoại Find & Replace => chuyển toàn bộ dấu gạch chéo ngăn cách thành dấu chấm:

 

Sau đó các bạn lập lại thao tác này nhưng là thay thế dấu chấm bằng dấu gạch chéo. Khi bạn làm bước này Excel sẽ tự động phát hiện được lỗi và sửa cho bạn. Cuối cùng bạn có thể thêm bộ lọc vào để kiểm tra là sẽ thấy cột ngày tháng đã hiển thị đúng định dạng:

 

Xem thêm: Tổng hợp cách định dạng ngày tháng trong Excel

Kết luận

Hy vọng bài hướng dẫn xử lý dữ liệu dạng Date trong Excel của chúng mình có thể giúp quá trình lập báo cáo quản trị của các bạn được rút ngắn thời gian, nâng cao hiệu quả.