Phân tích dữ liệu kinh doanh thường yêu cầu làm việc với các giá trị ngày tháng trong Excel để trả lời các câu hỏi như “hôm nay chúng ta đã kiếm được bao nhiêu tiền” hoặc “ngày này so với cùng ngày tuần trước như thế nào?”
Thật không may, điều này không phải là bất thường, đặc biệt là khi nhiều người dùng đang nhập thông tin này, sao chép và dán từ các hệ thống khác và nhập từ cơ sở dữ liệu
Trong bài viết này, chúng tôi sẽ mô tả bốn tình huống khác nhau và các giải pháp để chuyển đổi văn bản thành giá trị ngày tháng
Ngày có dấu chấm/dấu chấm hoàn toàn
Có lẽ một trong những lỗi phổ biến nhất mà người mới bắt đầu mắc phải khi nhập ngày tháng vào Excel là sử dụng ký tự dừng hoàn toàn để phân tách ngày, tháng và năm.
Excel sẽ không nhận dạng đây là giá trị ngày và sẽ tiếp tục lưu trữ dưới dạng văn bản. Tuy nhiên, bạn có thể giải quyết vấn đề này bằng công cụ Find and Replace. Bằng cách thay thế các dấu chấm bằng dấu gạch chéo [/], Excel sẽ tự động xác định các giá trị là ngày tháng
Chọn các cột mà bạn muốn thực hiện tìm và thay thế
Nhấp vào Trang đầu > Tìm & Chọn > Thay thế—hoặc nhấn Ctrl+H
Trong cửa sổ Tìm và Thay thế, nhập dấu chấm [. ] trong trường “Tìm gì” và dấu gạch chéo [/] trong trường “Thay thế bằng”. Sau đó, nhấp vào “Thay thế tất cả. ”
Tất cả các dấu chấm được chuyển đổi thành dấu gạch chéo và Excel nhận ra định dạng mới là ngày tháng
Nếu dữ liệu bảng tính của bạn thường xuyên thay đổi và bạn muốn có một giải pháp tự động cho tình huống này, bạn có thể sử dụng hàm SUBSTITUTE
=VALUE[SUBSTITUTE[A2,".","/"]]
Hàm SUBSTITUTE là một hàm văn bản, vì vậy không thể tự chuyển đổi thành ngày tháng. Hàm VALUE sẽ chuyển đổi giá trị văn bản thành giá trị số
Các kết quả được hiển thị dưới đây. Giá trị cần phải được định dạng là một ngày
Bạn có thể thực hiện việc này bằng cách sử dụng danh sách "Định dạng số" trên tab "Trang chủ"
Ví dụ ở đây về dấu phân cách dừng hoàn toàn là điển hình. Nhưng bạn có thể sử dụng kỹ thuật tương tự để thay thế hoặc thay thế bất kỳ ký tự phân cách nào
Chuyển đổi định dạng yyyymmdd
Nếu bạn nhận được ngày ở định dạng hiển thị bên dưới, nó sẽ yêu cầu một cách tiếp cận khác
Định dạng này khá chuẩn trong công nghệ vì nó loại bỏ bất kỳ sự mơ hồ nào về cách các quốc gia khác nhau lưu trữ giá trị ngày của họ. Tuy nhiên, ban đầu Excel sẽ không hiểu nó
Để có giải pháp thủ công nhanh chóng, bạn có thể sử dụng Văn bản thành Cột
Chọn phạm vi giá trị bạn cần chuyển đổi rồi nhấp vào Dữ liệu > Văn bản thành Cột
Trình hướng dẫn Chuyển văn bản thành Cột xuất hiện. Nhấp vào “Tiếp theo” ở hai bước đầu tiên để bạn đang ở bước thứ ba, như trong hình bên dưới. Chọn Ngày rồi chọn định dạng ngày đang được sử dụng trong các ô từ danh sách. Trong ví dụ này, chúng tôi đang xử lý định dạng YMD
Nếu bạn muốn một giải pháp công thức, thì bạn có thể sử dụng hàm Ngày để tạo ngày
Điều này sẽ được sử dụng cùng với các hàm văn bản Trái, Giữa và Phải để trích xuất ba phần của một ngày [ngày, tháng, năm] từ nội dung ô
Công thức bên dưới hiển thị công thức này bằng cách sử dụng dữ liệu mẫu của chúng tôi
=DATE[LEFT[A2,4],MID[A2,5,2],RIGHT[A2,2]]
Sử dụng một trong hai kỹ thuật này, bạn có thể chuyển đổi bất kỳ giá trị số có tám chữ số nào. Ví dụ: bạn có thể nhận được ngày ở định dạng ddmmyyyy hoặc mmddyyyy
Hàm DATEVALUE và VALUE
Đôi khi, sự cố không phải do ký tự phân cách gây ra mà do cấu trúc ngày khó hiểu đơn giản vì nó được lưu trữ dưới dạng văn bản
Dưới đây là danh sách các ngày tháng theo nhiều cấu trúc khác nhau, nhưng chúng ta đều có thể nhận ra chúng là một ngày tháng. Thật không may, chúng đã được lưu trữ dưới dạng văn bản và cần chuyển đổi
Đối với những kịch bản này, có thể dễ dàng chuyển đổi bằng nhiều kỹ thuật khác nhau
Đối với bài viết này, tôi muốn đề cập đến hai chức năng để xử lý các tình huống này. Chúng là DATEVALUE và VALUE
Hàm DATEVALUE sẽ chuyển đổi văn bản thành một giá trị ngày [có thể đã thấy điều đó sắp xảy ra], trong khi hàm VALUE sẽ chuyển đổi văn bản thành một giá trị số chung. Sự khác biệt giữa chúng là tối thiểu
Trong hình trên, một trong các giá trị cũng chứa thông tin về thời gian. Và đó sẽ là minh chứng cho sự khác biệt nhỏ của các chức năng
Công thức DATEVALUE bên dưới sẽ chuyển đổi từng giá trị thành giá trị ngày
=DATEVALUE[A2]
Lưu ý cách thời gian đã bị xóa khỏi kết quả ở hàng 4. Công thức này hoàn toàn chỉ trả về giá trị ngày tháng. Kết quả sẽ vẫn cần được định dạng là một ngày
Công thức sau sử dụng hàm VALUE
=VALUE[A2]
Công thức này sẽ tạo ra kết quả tương tự ngoại trừ hàng 4, trong đó giá trị thời gian cũng được duy trì
Sau đó, kết quả có thể được định dạng là ngày và giờ hoặc dưới dạng ngày để ẩn giá trị thời gian [nhưng không xóa]