Hãy tưởng tượng bạn đang cần tìm kiếm công thức tuần lịch trong Google Sheets để kết hợp ngày bắt đầu và kết thúc tuần. Trong bài viết này, tôi sẽ chia sẻ với bạn một công thức đơn giản để làm điều đó.
Định dạng công thức tuần lịch trong Google Sheets
Trong Google Sheets, hàm Weeknum là hàm duy nhất được sử dụng để xác định số tuần trong năm. Tuy nhiên, hàm này chỉ trả về số tuần và không trả về khoảng thời gian của tuần.
Vì vậy, tôi đã tạo ra một công thức tuần lịch riêng trong Google Sheets giúp bạn xác định ngày bắt đầu và kết thúc của mỗi tuần.
Ưu điểm của công thức tuần lịch trong Google Sheets
Công thức tuần lịch của tôi không chỉ giúp bạn xác định ngày bắt đầu và kết thúc của tuần, mà còn có thể sử dụng trong các trường hợp sau:
- Tính tổng doanh số hàng tuần trong khoảng thời gian từ 01/01/2018 đến 16/02/2019 (từ dữ liệu hàng ngày sang trung bình hàng tuần).
- Tính tổng doanh số hàng tuần trong khoảng thời gian kéo dài qua nhiều năm (từ dữ liệu hàng ngày sang tổng hàng tuần).
- Đếm số lượng nhập hàng trong một khoảng thời gian nhất định (từ dữ liệu hàng ngày sang số lượng hàng tuần).
Nếu bạn muốn tóm tắt dữ liệu dựa trên số tuần, các công thức này sẽ rất hữu ích cho bạn. Bạn có thể sử dụng hàm Query và tạo mẫu tóm tắt hàng tuần dựa trên tuần lịch, không chỉ số tuần.
Sử dụng công thức tuần lịch trong Google Sheets
Dưới đây là cách sử dụng công thức tuần lịch trong Google Sheets.
Dữ liệu mẫu
Trong ví dụ này, tôi có một số ngày ngẫu nhiên trong cột A. Các dòng từ A2 đến A29 chứa các ngày từ 01/02/2019 đến 28/02/2019, và các dòng từ A30 đến A58 chứa các ngày từ 01/02/2020 đến 29/02/2020.
Bước 1: Xác định ngày bắt đầu tuần
Đầu tiên, chúng ta sẽ xác định ngày bắt đầu của từng tuần. Để làm điều này, hãy áp dụng công thức sau vào ô B2:
=ARRAYFORMULA(A2:A58-WEEKDAY(A2:A58,2)+1)
Sau đó, hãy áp dụng công thức Unique để loại bỏ các ngày trùng lặp:
=UNIQUE(ArrayFormula(A2:A58-WEEKDAY(A2:A58,2)+1))
Tiếp theo, chúng ta sẽ sắp xếp kết quả theo thứ tự tăng dần bằng cách áp dụng công thức Sort:
=SORT(UNIQUE(A2:A58-WEEKDAY(A2:A58,2)+1))
Bước 2: Xác định ngày kết thúc tuần
Tiếp theo, chúng ta sẽ xác định ngày kết thúc của từng tuần. Để làm điều này, hãy áp dụng công thức sau vào ô C2:
=SORT(UNIQUE(A2:A58-WEEKDAY(A2:A58,2)+1+6))
Công thức trên sẽ thêm 6 ngày vào ngày bắt đầu tuần để có được ngày kết thúc tuần.
Bước 3: Kết hợp ngày bắt đầu và kết thúc tuần
Cuối cùng, chúng ta sẽ kết hợp ngày bắt đầu và kết thúc tuần để tạo cột cho tuần lịch. Chúng ta có thể làm điều này bằng cách sử dụng công thức sau:
=TRANSPOSE(ArrayFormula(TEXT(SORT(UNIQUE(A2:A58-WEEKDAY(A2:A58,2)+1)),"DD/MMM/YY")&" - "&TEXT(SORT(UNIQUE(A2:A58-WEEKDAY(A2:A58,2)+1+6)),"DD/MMM/YY")))
Công thức trên sẽ kết hợp các cột chứa ngày bắt đầu và kết thúc tuần và chuyển chúng sang dạng cột.
Từ đó, bạn có thể sử dụng kết quả này trong các công thức như Sumif, Countifs, Averageifs để tính tổng, đếm, trung bình dựa trên khoảng thời gian trong Google Sheets.
Đó là công thức tuần lịch trong Google Sheets mà tôi muốn chia sẻ với bạn. Hy vọng nó sẽ hữu ích cho công việc của bạn.
Crawlan.com
Để biết thêm thông tin và cách sử dụng các công thức trong Google Sheets, hãy truy cập Crawlan.com để có những bài viết thú vị và hữu ích khác.
