Hãy bắt đầu với một câu chuyện thú vị về cách chuyển đổi ISOWEEKNUM thành các ngày tương ứng trong Google Sheets. Điều này tương tự như việc chuyển đổi WEEKNUM thành các ngày tương ứng. Với sự trợ giúp của hàm WEEKDAY, chúng ta có thể lấy được một ngày hoặc danh sách các ngày thuộc tuần từ số tuần (WEEKNUM).
Trước tiên, hãy tìm hiểu sự khác nhau và tương đồng giữa hai hàm WEEKNUM và ISOWEEKNUM. Cả hai hàm này đều nhận một ngày và trả về số tuần tương ứng. Tuy nhiên, trong hàm WEEKNUM, bạn có thể chỉ định ‘loại’ (một số đại diện cho ngày bắt đầu của tuần) khác với ngày. Có mười loại, từ 1 đến 17 và 21, trong đó xác định ngày bắt đầu của tuần.
Ví dụ, loại 1 hoặc không sử dụng loại nào đó, xác định tuần từ Chủ nhật đến Thứ Bảy. Còn loại 21, hàm WEEKNUM sử dụng tiêu chuẩn ISO8601 để đánh số các tuần bắt đầu từ Thứ Hai đến Chủ Nhật.
Giờ thì chúng ta đã hiểu sự khác biệt đó, hãy tìm hiểu cách chuyển đổi ISOWEEKNUM thành các ngày tương ứng trong Google Sheets.
Dữ liệu mẫu:
Hãy xem cách tìm ngày bắt đầu và ngày kết thúc thanh toán từ ISOWEEKNUM trong Google Sheets. Sử dụng công thức sau:
Ô F2 (Ngày bắt đầu ISOWEEKNUM): =lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7)
Ô G2 (Ngày kết thúc ISOWEEKNUM): =lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7+6)
Kéo các công thức này xuống, chọn kết quả và áp dụng định dạng (menu) > Số > Ngày.
Bây giờ chúng ta đã có một công thức mảng để chuyển đổi ISOWEEKNUM trong các ô (phạm vi của ô) thành các ngày tương ứng, nhưng chúng ta sẽ bắt đầu với giải pháp không sử dụng công thức mảng.
Cấu trúc của các công thức:
Dưới đây là một số điểm quan trọng.
Trong hệ thống đánh số tuần ISO (hệ thống đánh số tuần của châu Âu), Thứ Hai đầu tiên của Tuần #1 luôn nằm giữa ngày 29 tháng 12 và ngày 4 tháng 1. Có thể bạn đã hiểu cách nó hoạt động.
Vậy làm thế nào chúng ta có thể tìm ra công thức trên trong Google Sheets?
Hãy quên hệ thống đánh số trên và tìm hiểu về Thứ Hai của bất kỳ ngày nào có thể xảy ra.
Thứ Hai của bất kỳ ngày nào: =ngày - ngày_trong_tuần(ngày) + 2
Ngày trả về bởi công thức trên có thể nằm ngay trước hoặc ngay sau ngày đã cho.
Công thức để tìm ra Thứ Hai ngay trước ngày đã cho là =ngày - ngày_trong_tuần(ngày - 2).
Dựa trên logic trên:
Thứ Hai ngay trước 5/1/năm là =ngày(năm,1,5)-ngày_trong_tuần(ngày(năm,1,3))
Công thức trên là đủ để chuyển đổi ISOWEEKNUM thành ngày bắt đầu tuần tương ứng trong Google Sheets. Nhưng chúng ta cũng muốn tìm ra ngày kết thúc tuần.
Vì vậy, chúng ta sẽ tìm ra Thứ Hai cuối cùng của năm trước: =ngày(năm,1,-2)-ngày_trong_tuần(ngày(năm,1,3)) và thêm ISOWEEKNUM*7 ngày để tìm ngày bắt đầu tuần và ISOWEEKNUM*7+6 để tìm ngày kết thúc tuần.
Tham khảo công thức sau:
=lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7)
=lambda(test,if(isoweeknum(test)=D2,test,""))(date(C2,1,-2)-weekday(date(C2,1,3))+D2*7+6)
Tầm quan trọng của mệnh đề IF trong công thức chuyển đổi ISOWEEKNUM thành ngày tương ứng
Hãy thảo luận về mệnh đề IF, đó là if(isoweeknum(test)=D2,test,""). Tên “test” đại diện cho phần được đánh dấu màu xanh lam và màu cam trong công thức. Nó đại diện cho công thức chuyển đổi ISOWEEKNUM thành ngày bắt đầu và ngày kết thúc tuần.
Công thức có thể trả về kết quả không mong muốn khi chúng ta nhập 53 trong ô D2, trong khi năm tương ứng trong ô C2 chỉ có 52 tuần. Mệnh đề IF giải quyết vấn đề đó.
Hàm LAMBDA rút ngắn phần kiểm tra logic bằng cách gán tên “test” cho công thức chuyển đổi ISOWEEKNUM thành ngày.
Nếu chúng ta muốn, chúng ta có thể tránh kéo các công thức F2 và G2 xuống bằng cách chuyển đổi chúng thành các công thức mảng sử dụng hàm MAP.
Đây là thành quả của chúng ta!
Bài viết này đã hướng dẫn cách chuyển đổi ISOWEEKNUM thành các ngày tương ứng trong Google Sheets. Hy vọng rằng bạn đã tìm thấy nó hữu ích. Đừng quên ghé thăm website Crawlan.com để biết thêm các bài viết hữu ích khác nhé!
Nguồn: Crawland.com
