Giả sử bạn có một tổ chức thực hiện hàng trăm giao dịch nhỏ mỗi ngày. Bạn có một số chi nhánh nơi nhân viên kế toán cấp dưới nhập số và ngày tháng vào các phần mềm khác nhau. Cuối tháng rồi, và bạn muốn xem mình đứng ở đâu so với tháng trước
Điều này sẽ yêu cầu bạn vẽ một biểu đồ hàng tỷ đô la, điều này khá khó thực hiện trừ khi bạn có một danh sách các giao dịch theo ngày. Vì các chi nhánh khác nhau có thể đang sử dụng các chương trình khác nhau, nên bạn sẽ cần nhập dữ liệu vào tệp Excel hoặc nhập dữ liệu theo cách thủ công—bạn không cần phải là Einstein để đưa ra lựa chọn tức thì tại đây
Khi bạn nhập dữ liệu từ cơ sở dữ liệu, Excel có thể nhập những ngày đó dưới dạng chuỗi văn bản. Lý do là, giống như Excel, không phải tất cả các chương trình đều lưu trữ ngày tháng dưới dạng số sê-ri. Khi một cái gì đó không phải là số sê-ri, nó không phải là ngày tháng trong Excel. Đây là lý do tại sao chúng tôi sẽ cung cấp cho bạn tóm tắt về một số công thức và phương pháp tiện lợi sẽ giúp bạn tạo ngày tháng trong trang tính Excel của mình
Mục lục
Làm cách nào để xác định nếu ngày được lưu trữ trong văn bản?
Khi bạn cố gắng sử dụng một danh sách ngày nhất định bằng các công cụ Excel như biểu đồ hoặc PivotTable, chúng sẽ không nhận dạng được các điểm dữ liệu là ngày nếu chúng được lưu dưới dạng văn bản. Mặc dù vậy, có một số cách để chỉ cần quan sát và xác định xem danh sách ngày tháng của bạn là ngày tháng hay chuỗi văn bản
- căn chỉnh
Excel căn phải ngày tháng theo mặc định. Nếu danh sách ngày của bạn dựa vào bức tường bên trái của ô, nó có thể làm tắt bóng đèn. Đó không phải là ngày, chúng là chuỗi văn bản
- Định dạng ô
Ô sẽ được định dạng là “Ngày” trong hộp Định dạng Số trong tab Trang chủ Excel cho các điểm dữ liệu được Excel nhận dạng là ngày
- Kiểm tra Thanh trạng thái
Nếu chọn danh sách ngày, bạn sẽ thấy Trung bình, Đếm và Tổng được tính trên thanh trạng thái, trong khi bạn sẽ chỉ thấy Đếm nếu ngày của bạn là chuỗi văn bản
3 cách để chuyển đổi ngày được lưu trữ dưới dạng văn bản
Hãy xem một số cách để chuyển đổi ngày được lưu dưới dạng văn bản thành giá trị ngày mà Excel có thể nhận ra
Phương pháp 1 – Sử dụng hàm DATEVALUE và VALUE
Hàm DATEVALUE là chất xúc tác thay đổi một ngày ở định dạng văn bản thành một số sê-ri mà Excel sẽ xác định là một ngày
Công thức có một đối số duy nhất mà bạn sẽ nhập chuỗi văn bản
=DATEVALUE[A2]
Thay vì thêm tham chiếu ô, bạn cũng có thể thêm chuỗi văn bản theo cách thủ công
Nếu bạn nhận được một số có năm chữ số ngẫu nhiên sau khi sử dụng công thức, đừng hoảng sợ. Đây không phải là số ngẫu nhiên, đây là số sê-ri mà Excel nhận dạng là ngày tháng. Để xem nó dưới dạng ngày, hãy điều hướng đến nhóm Số trong tab Trang chủ Excel và thay đổi định dạng của ô thành Ngày — điều này sẽ khắc phục kết quả đầu ra của bạn
Mặc dù DATEVALUE hoạt động hoàn toàn tốt, hàm VALUE tiến thêm một bước để bao gồm cả thời gian khi trả về. Excel lưu trữ ngày tháng dưới dạng số sê-ri và thời gian dưới dạng giá trị thập phân sau số sê-ri đó. Ví dụ: nếu số sê-ri cho ngày 25 tháng 12 năm 2001 là 37250, thì 37250. 5 sẽ đại diện cho 25 Tháng mười hai 2015, 12. 00 PM, kể từ 0. 5 có nghĩa là nửa ngày
Cú pháp của hàm VALUE tương tự như cú pháp của hàm DATEVALUE
=VALUE[A2]
Lưu ý cách đầu ra cho hai hàng đầu tiên trả về 12. 00 giờ sáng mặc dù không có thời gian nào được chèn vào những ngày đó trong cột NGÀY. Điều này là do Excel đọc giá trị này dưới dạng số nguyên [đương nhiên, vì không có giá trị thập phân] và do đó giả sử thời gian là 12. 00 giờ sáng, tôi. e. 0 giờ vào ngày trả lại
Ở hàng cuối cùng, chúng tôi đã nhập 15. 00 [tôi. e. 3 giờ chiều] theo thời gian. Lưu ý cách hàm VALUE trả về một số sê-ri có giá trị thập phân. Nếu bạn muốn tính toán thời gian theo cách thủ công bằng cách sử dụng giá trị thập phân này, chỉ cần nhân nó với 24 [0. 625 * 24 = 15]
Cách 2 – Sử dụng hàm SUBSTITUTE + VALUE/DATEVALUE
Hàm VALUE là chất xúc tác thay đổi ngày ở định dạng văn bản thành số sê-ri mà Excel sẽ xác định là ngày
Trong ví dụ của chúng tôi, chúng tôi sẽ sử dụng hàm VALUE, nhưng công thức sẽ hoạt động theo cách tương tự ngay cả khi bạn chọn sử dụng hàm DATEVALUE. Điểm khác biệt duy nhất là hàm DATEVALUE sẽ không trả về thành phần thời gian nếu có bất kỳ thành phần nào trong các ô của bạn
Hãy thiết lập một ví dụ. chúng tôi có một danh sách các ngày có dấu phân cách khác với dấu gạch chéo lên [/] hoặc dấu gạch ngang [-]. Excel hiện đang đọc các thành phần của danh sách này dưới dạng chuỗi văn bản. Có hai cách để chuyển đổi các chuỗi văn bản này thành ngày tháng. Chúng tôi có danh sách các ngày sau đây
- Đầu tiên, chúng ta có thể sử dụng công cụ Replace trong Excel để thay thế dấu phân cách
- Vào Trang chủ > Tìm & Chọn > Thay thế [Phím tắt. Ctrl+H]
- Trong hộp thoại Tìm và Thay thế, hãy nhập dấu chấm [. ] trong hộp văn bản Find what và dấu gạch chéo lên [/] trong hộp văn bản Replace with. Xuống bên dưới, nhấn Replace All [i. e. nút ngoài cùng bên trái]
Tuy nhiên, có một cách dễ dàng hơn nếu bạn thường xuyên điền dữ liệu mới vào bảng tính của mình — sử dụng công thức
Từ các ví dụ trước, chúng ta biết rằng hàm VALUE có khả năng chuyển đổi chuỗi văn bản thành ngày tháng. Có một báo trước, mặc dù. Các chuỗi văn bản của chúng tôi có một dấu phân cách ngăn không cho Excel đọc chúng dưới dạng ngày tháng. Vì vậy, chúng tôi sẽ cần cung cấp một chuỗi văn bản có dấu phân cách mà Excel liên kết với một ngày hợp lệ
Đây là công thức chúng ta sẽ sử dụng
=VALUE[SUBSTITUTE[A2, ".", "/"]]
Hàm VALUE thực hiện công việc tương tự ở đây. Nó chuyển đổi một chuỗi văn bản thành một ngày. Chúng tôi đã lồng một hàm SUBSTITUTE để giải quyết vấn đề về dấu phân cách. Nếu chúng ta tham chiếu trực tiếp ô A2 bên trong hàm VALUE, thì nó sẽ trả về #VALUE. lỗi vì chuỗi văn bản này không thể được nhận dạng là ngày hợp lệ trong Excel
Sau đó, tất cả những gì chúng ta cần làm là thay đổi dấu phân cách bằng hàm SUBSTITUTE. Hãy coi hàm SUBSTITUTE như một công thức cho công cụ Tìm & Thay thế mà chúng ta đã sử dụng trong ví dụ trước. Nó có 3 đối số. đối số đầu tiên là tham chiếu ô, đối số thứ hai là ký tự chúng ta muốn thay thế và đối số thứ ba là ký tự chúng ta muốn thay thế bằng
Sau khi thực hiện hàm SUBSTITUTE, công thức của chúng ta sẽ như thế này
=VALUE["12/25/2001"]
Điều này sẽ cung cấp cho chúng tôi kết quả cuối cùng của chúng tôi, một ngày có thể nhận dạng được bằng Excel - 25/12/2001
Phương pháp 3 – Sử dụng công cụ Text to Columns cho chuỗi văn bản phức tạp
Năm ngoái, bạn đã đưa ra một quyết định tồi tệ khi thuê một người quản lý cẩu thả, người không buồn tạo các cột riêng biệt cho ngày và thứ trong tuần. Điều này đã khiến bạn phải bứt tóc khi cố gắng phân tích dữ liệu bán hàng của mình
Đừng băn khoăn, chúng ta sẽ làm việc cùng nhau và giải quyết vấn đề này
Nhân viên cẩu thả đã ghi như vậy
Thứ ba, ngày 25 tháng 12 năm 2001
Để chuyển đổi các chuỗi này thành ngày tháng, chúng tôi sẽ sử dụng quy trình gồm hai bước. Bước 1 liên quan đến việc sử dụng công cụ Excel có tên là “Chuyển văn bản thành cột” và Bước 2 liên quan đến việc sử dụng hàm NGÀY và THÁNG
Bước 1
- Chọn danh sách chuỗi văn bản bạn muốn chuyển đổi sang ngày tháng
- Điều hướng đến nhóm Công cụ dữ liệu trong tab Dữ liệu và nhấp vào Văn bản thành cột
- Chọn Delimited trong hộp thoại mở ra và nhấp vào Next
- Trên màn hình tiếp theo, bạn sẽ chọn các dấu phân cách được sử dụng trong chuỗi văn bản của mình từ danh sách. Điều quan trọng cần lưu ý là ngay cả dấu cách cũng được coi là dấu phân cách. Vì các chuỗi văn bản của chúng tôi có khoảng trắng sau dấu phẩy, chúng tôi sẽ kiểm tra cả hai dấu phân cách trong danh sách
- Ở dưới cùng của hộp thoại, bạn sẽ thấy bản xem trước cách các chuỗi văn bản sẽ được chia sau khi bạn nhấp vào Tiếp theo
Bạn đã chia dữ liệu nhưng vẫn còn một số thứ linh tinh cần chạy qua. Trên màn hình tiếp theo, bạn sẽ có tùy chọn loại trừ bất kỳ cột cụ thể nào khỏi đầu ra của mình. Ví dụ: nếu các ngày trong tuần không liên quan đến bạn, hãy loại trừ chúng bằng cách chọn cột [cột sẽ chuyển sang màu đen khi được chọn] và chọn cột Không nhập [bỏ qua]
Bạn có thể cảm thấy hợp lý khi chọn Ngày trong danh sách Định dạng dữ liệu cột cho các cột còn lại. Tuy nhiên, hãy nhớ rằng chuỗi văn bản của bạn đã được chia thành 3 thành phần và Excel không thể nhận dạng chúng dưới dạng ngày tháng. Vì vậy, bây giờ hãy để chúng được định dạng là Chung
Cuối cùng, chọn một điểm đến mà bạn muốn chèn trả về và nhấp vào Kết thúc
Bước 2
Chúng tôi chia ngày của mình thành ba cột và tất cả những gì chúng tôi cần làm bây giờ là tập hợp chúng lại với nhau và chuyển chúng sang Excel dưới dạng ngày tháng. Mặc dù, vẫn còn một kết thúc lỏng lẻo. Tháng của chúng ta là một chuỗi văn bản, trong khi hàm NGÀY cần một số
Để chuyển tên tháng thành số, chúng ta sẽ lồng hàm MONTH bên trong đối số tháng của hàm DATE. Tuy nhiên, hàm MONTH không thể hoạt động chỉ với một chuỗi văn bản, nó cần một ngày để có thể trả về số tháng một cách hợp lệ. Chúng tôi sẽ quay lại vấn đề này trong giây lát
Chúng tôi sẽ sử dụng công thức DATE này
=DATE[D2, MONTH[1&B2], C2]
Nếu bạn không quen với hàm NGÀY, hãy xem nhanh hướng dẫn về hàm NGÀY của chúng tôi
Các đối số đầu tiên và cuối cùng chỉ là các tham chiếu ô đến các số tương ứng với năm và ngày. Vậy, điều gì đang xảy ra với hàm MONTH?
Chà, đây là những gì chúng ta đang hướng dẫn hàm tháng. Nhìn vào giá trị trong D2 và cho tôi số từ 1 đến 12 của tháng đó
Nếu chúng ta chỉ tham chiếu ô, hàm MONTH không thể nhận ra tháng. Để khắc phục điều này, chúng tôi nối một số 1, có nghĩa là bây giờ là ngày mà hàm MONTH có thể hoạt động với. 1 chỉ là một ngày tùy ý, tất nhiên. Bạn có thể đã nhập bất kỳ số nào từ 1 đến 30/31 và nó vẫn hoạt động
Vì vậy, bây giờ chúng ta đã cung cấp hàm DATE với tất cả 3 đối số của nó. Điều này sẽ cung cấp cho bạn một danh sách chỉ các ngày không có các ngày trong tuần và bạn có thể thao tác các ngày này trong trang tính của mình khi bạn thấy phù hợp.
Làm thế nào và tại sao ngày được lưu trữ dưới dạng số?
Chúng tôi biết rằng Excel lưu trữ ngày tháng dưới dạng số sê-ri. Đương nhiên, Excel không chỉ gán bất kỳ số ngẫu nhiên nào cho ngày tháng; . Chúng ta sẽ nói về mẫu này trong giây lát, nhưng trước tiên hãy xem tại sao Excel lại làm điều này
Tại sao
Có một số hàm trong Excel thao tác ngày trong trang tính, bao gồm các hàm DAYS, DATE, WORKDAY, DATEVALUE, trong số nhiều hàm khác. Các công thức này cần có định dạng chuẩn để chúng có thể nhận ra là ngày tháng
Giả sử rằng bạn đang sử dụng hàm DAYS và đã cung cấp hai ngày. Bạn nhập end_date và start_date và Excel sẽ tính toán số ngày để cung cấp cho bạn kết quả, tôi. e. số ngày giữa những ngày này
Trong trường hợp không có số sê-ri, Excel sẽ phải sử dụng một số phương pháp thay thế để thực hiện phép tính này. Tuy nhiên, vì Excel gán một số sê-ri nên tất cả những gì nó cần làm là trừ hai số sê-ri và số kết quả sẽ là số ngày giữa các ngày này
Làm sao
Nó khá đơn giản. Excel gán '1' cho ngày 1 tháng 1 năm 1900. Nó thêm 1 cho mỗi ngày kể từ đó. Ví dụ: ngày 2 tháng 1 năm 1900 sẽ được gán là '2'
Ngày 1 tháng 1 năm 2021 được gán là 44197, nghĩa là ngày 01/01/2021 xảy ra 44196 ngày sau ngày 01 tháng 01 năm 1900
Điều này đơn giản hóa việc tính toán và thao tác ngày ở một mức độ lớn
2 cách để chuyển đổi ngày được lưu trữ dưới dạng số
Chúng tôi có một số ngày được lưu trữ trong trang tính của mình, nhưng thay vì được lưu trữ dưới dạng ngày tháng, các ô đó đã được định dạng là Chung/Văn bản/v.v.
Có hai cách chúng ta có thể chuyển đổi những con số này thành ngày tháng. Cả hai đều hướng dẫn Excel thực hiện cùng một việc, nhưng theo những cách khác nhau
Phương pháp 1. Hộp thoại định dạng ô
Để chuyển đổi danh sách số sê-ri thành ngày tháng bằng phương pháp này
- Chọn các ô chứa giá trị
- Điều hướng đến nhóm Số trong tab Trang chủ trong Excel
- Chọn Ngày ngắn hoặc Ngày dài từ menu thả xuống. Điều này sẽ chuyển đổi các số thành ngày. Tuy nhiên, nếu bạn muốn có định dạng tùy chỉnh cho ngày của mình, hãy tiếp tục bước tiếp theo
- Thay vì mở menu thả xuống, hãy nhấp vào mũi tên nhỏ ở dưới cùng bên phải của phần Nhóm số. Thao tác này sẽ mở hộp thoại Định dạng ô
- Chọn Ngày trên danh sách xuất hiện ở bên trái hộp thoại
- Bạn sẽ thấy một số định dạng hiển thị ngày được liệt kê trong hộp có tiêu đề Loại
Điều này sẽ cho phép bạn sử dụng định dạng bạn chọn. Tuy nhiên, nếu định dạng mong muốn của bạn không được liệt kê trong hộp, hãy làm theo các bước sau
- Thay vì chọn Ngày từ danh sách, hãy chọn Tùy chỉnh. Bạn sẽ thấy một số Định dạng tùy chỉnh được liệt kê trong hộp có tiêu đề Loại
- Nếu bạn muốn tự tạo định dạng, hãy sử dụng các mã sau
Các mã này cũng sẽ hữu ích khi áp dụng Phương pháp 2. Nói về mà…
Phương pháp 2. Sử dụng hàm TEXT
Lần này, chúng ta sẽ sử dụng hàm TEXT thay vì định dạng ô theo cách thủ công
Công thức chúng ta sẽ sử dụng là
=TEXT[A2, "mm/dd/yyyy"]
Công thức làm một điều rất đơn giản. Nó nhìn vào ô A2 và áp dụng định dạng “mm/dd/yyyy” cho chuỗi văn bản trong ô đó
Về cơ bản, nó giống như những gì chúng ta đã làm với hộp thoại Định dạng Ô, nhưng với một công thức. Nếu bạn muốn một định dạng khác, hãy tham khảo bảng mã ở trên và điều chỉnh công thức của bạn cho phù hợp
Cách chuyển đổi ngày có 8 chữ số thành ngày có thể nhận dạng trên Excel
Đây không phải là số sê-ri gồm 5 chữ số hợp lệ mà Excel có thể nhận dạng là ngày tháng. Do đó, chúng ta không thể thay đổi trực tiếp định dạng của ô để chuyển thành ngày tháng. Chúng tôi sẽ cần một phương pháp thay thế liên quan đến việc phân tích cú pháp chuỗi văn bản và kéo các thành phần khác nhau của nó lại với nhau bằng một số hàm để có được kết quả cuối cùng của chúng tôi
Đây là công thức chúng ta sẽ sử dụng
=DATE[LEFT[A2, 4], MID[A2, 5, 2], RIGHT[A2, 2]]
Hàm DATE sẽ giúp chúng ta tập hợp các thành phần khác nhau của một ngày thông qua các đối số năm, tháng và ngày của nó. Tất cả các đối số này đều có các chức năng khác nhau sẽ chuyển tiếp thông tin đến chúng
Hàm LEFT, MID và RIGHT chọn ra thành phần có liên quan từ chuỗi văn bản và chuyển tiếp số đó đến hàm DATE. Trong ví dụ của chúng ta, hàm LEFT tìm bên trong ô A2 và chuyển tiếp 4 ký tự đầu tiên của chuỗi văn bản có trong ô A2 dưới dạng đối số năm trong hàm DATE
Bạn cũng có thể sử dụng công thức này nếu bạn có các dấu phân cách khác nhau trong chuỗi văn bản phân tách các thành phần năm, tháng và ngày của ngày. Tất cả những gì bạn cần làm là điều chỉnh số lượng ký tự trong các chức năng LEFT, MID và RIGHT
Sửa ngày văn bản với năm có hai chữ số
Trừ khi bạn đã nhập định dạng Tùy chỉnh cho bất kỳ ô nào, ngày tháng có năm có hai chữ số sẽ không phù hợp với Excel. Excel sẽ thêm một mũi tên màu xanh lá cây ở góc trên cùng bên trái của mỗi ô có ngày với năm có hai chữ số
Để giải quyết vấn đề này, hãy di chuột qua ô. Thao tác này sẽ hiển thị nút thả xuống ở bên phải ô có ký hiệu màu vàng chứa dấu chấm than. Dấu hiệu này cho bạn biết rằng có lỗi và bạn sẽ thấy các bản sửa lỗi [và các tùy chọn khác] cho lỗi trong menu thả xuống
Các bản sửa lỗi sẽ bao gồm hai tùy chọn, hỏi tùy chọn của bạn về việc chuyển ngày của bạn thành ngày có bốn chữ số. Tùy chọn đầu tiên sẽ chuyển thành phần năm thành 19XX, tùy chọn thứ hai sẽ chuyển thành 20XX
Cách bật/tắt kiểm tra lỗi hai chữ số
Nếu bạn không thấy cảnh báo lỗi, bạn có thể đã tắt tính năng kiểm tra lỗi. Bạn có thể bật/tắt kiểm tra lỗi bằng cách điều hướng đến Tệp > Tùy chọn > Công thức và chọn/bỏ chọn hộp bên cạnh tùy chọn có nội dung Bật kiểm tra lỗi nền
Bạn cũng sẽ thấy các quy tắc kiểm tra Lỗi cụ thể bên dưới. Để nhận cảnh báo cho năm có hai chữ số, hãy đảm bảo bạn đã chọn hộp bên cạnh tùy chọn có nội dung Ô chứa năm được biểu thị bằng 2 chữ số
Điều này xử lý hầu hết tất cả các phương pháp chuyển đổi văn bản hoặc số thành ngày trong Excel. Một số công thức khác có thể được sử dụng làm phương pháp thay thế vì Excel có một kho công thức mạnh mẽ, nhưng những công thức mà chúng tôi đã thảo luận sẽ giúp bạn vượt qua hầu hết mọi vấn đề liên quan đến chuyển đổi ngày tháng
Khoan những kỹ thuật này bằng cách thực hành chúng. Khi chúng trở thành bản năng thứ hai đối với bạn, chúng tôi sẽ có thêm một số công thức để bạn khám phá