Bạn thường sử dụng công thức SCAN hoặc ARRAYFORMULA + SUMIF + ROW để tính tổng tích lũy (tổng tích luỹ) trong Google Sheets. Tuy nhiên, có thể cần một công thức phức tạp hơn để tính tổng tích lũy hàng tuần và hàng hai tuần cụ thể trong Google Sheets.
Tôi đã viết một công thức mảng như vậy cho bạn sử dụng ngay. Nó có những đặc điểm sau:
- Bạn có thể kiểm soát khoảng thời gian sử dụng một menu thả xuống với hai tùy chọn: Hàng tuần và Hàng hai tuần.
- Bạn có thể chọn ngày bắt đầu tuần từ một menu thả xuống khác với các tùy chọn từ Chủ nhật đến Thứ Bảy.
- Đây là một công thức mảng được đặt trong hàng trên cùng của phạm vi (tiêu đề). Bạn không cần kéo xuống ô chứa công thức hoặc sao chép-dán nó xuống cột.
- Bạn có thể chỉ định các khoảng mở hoặc đóng trong công thức tùy thuộc vào bạn đã hoàn thành dữ liệu hay mong đợi các mục nhập trong tương lai.
Công thức tổng cộng theo tuần / hàng hai tuần yêu cầu bốn giá trị đầu vào:
- Hai phạm vi / mảng một chiều cùng kích thước bằng nhau trỏ đến cột ngày và cột số, ví dụ, A2: A cho ngày và B2: B cho số.
- Hai tham chiếu ô trỏ đến hai menu thả xuống.
- Thả xuống đầu tiên nên chứa các giá trị “Hàng tuần” và “Hàng hai tuần”, và thả xuống thứ hai nên chứa các giá trị “Chủ nhật”, “Thứ Hai”, “Thứ Ba”, “Thứ Tư”, “Thứ Năm”, “Thứ Sáu” và “Thứ Bảy”.
Quan trọng: Trước khi sử dụng công thức của tôi để tính tổng tích lũy hàng tuần và hàng hai tuần, bạn phải sắp xếp hai mảng (cột ngày và cột số) dựa trên cột ngày theo thứ tự tăng dần.
Chuẩn bị: Menu thả xuống để chọn Ngày bắt đầu tuần và Khoảng thời gian tính toán
Chúng ta cần hai menu thả xuống xác thực dữ liệu. Hãy đặt chúng vào ô C1 và C2.
Trước tiên, hãy tạo menu thả xuống trong ô C1 để kiểm soát khoảng thời gian tính toán, tức là hàng tuần hoặc hàng hai tuần.
- Chọn ô C1.
- Điều hướng đến menu Chèn > Thả xuống.
- Trong ô Tùy chọn 1, nhập Hàng tuần.
- Trong ô Tùy chọn 2, nhập Hàng hai tuần.
- Nhấp vào Hoàn tất.
Bây giờ, hãy tạo menu thả xuống thứ hai trong ô C2. Menu thả xuống này sẽ giúp công thức tính tổng tích lũy hàng tuần / hàng hai tuần tính toán lại tại mỗi ngày bắt đầu tuần được chọn trong ô C2.
- Chọn ô C2.
- Điều hướng đến menu Chèn > Thả xuống.
- Trong ô Tùy chọn 1, nhập Chủ nhật.
- Trong ô Tùy chọn 2, nhập Thứ Hai.
- Nhấp vào Thêm một mục khác và nhập Thứ Ba trong trường hiện ra.
- Tiếp tục bước trên và thêm Thứ Tư, Thứ Năm, Thứ Sáu và Thứ Bảy.
- Nhấp vào Hoàn tất.
Sắp xếp các dữ liệu còn lại bên dưới hàng 3, như hình ảnh sau.
Tổng tích lũy hàng tuần trong Google Sheets
Trong ví dụ sau đây, phạm vi ô D5: D chứa số giờ làm ngoài giờ được phân bổ cho một nhân viên, và phạm vi ô B5: B chứa các ngày đã ghi lại. Chúng ta không cần dữ liệu trong cột C4: C.
Công thức sau trong ô E4 trả về tổng tích lũy hàng tuần trong ô E5: E.
Công thức:
=ARRAYFORMULA(LET(r_date,<strong>B5:B</strong>,r_amt,<strong>D5:D</strong>,pick,<strong>C1</strong>,w_d,<strong>C2</strong>,s_d,SWITCH(w_d,"Sun",1,"Mon",2,"Tue",3,"Wed",4,"Thu",5,"Fri",6,"Sat",7),limit,IF(pick="Weekly",ROUNDUP((DAYS(MAX(r_date),MIN(r_date))+1)/7),ROUNDUP((DAYS(MAX(r_date),MIN(r_date))+1)/14)),dt,SEQUENCE(limit,1,XLOOKUP(s_d,WEEKDAY(r_date),r_date,0,1),IF(pick="WEEKLY",7,14)),f_date,IF(pick="Weekly",dt,dt+7),x_f_date,XLOOKUP(r_date,f_date,f_date,,1,1),VSTACK(pick&" CUSUM",IF(r_date="",,MAP(x_f_date,r_amt,LAMBDA(date,amt,SUMIFS(r_amt,x_f_date,date,ROW(r_amt),"<="&ROW(amt))))))))Công thức này chọn các ngày trong ô B5: B và số giờ làm ngoài giờ trong ô D5: D, và trả về tổng tích lũy hàng tuần dựa trên ngày bắt đầu tuần được chọn trong ô C2.
Nếu giá trị đã chọn trong ô C2 là “Chủ nhật”, công thức tính toán lại tổng tích lũy tại mỗi hàng “Chủ nhật”. Tuy nhiên, vì giá trị đã chọn trong ô C2 hiện tại là “Thứ Hai”, công thức tính toán lại tổng tích lũy tại mỗi hàng “Thứ Hai”.
Tổng tích lũy hàng hai tuần trong Google Sheets
Ở đây, không có thay đổi về dữ liệu mẫu và công thức. Theo ví dụ của tôi, bạn có thể chọn “Hàng hai tuần” trong ô C1 để trả về tổng tích lũy hàng hai tuần.
Như vậy, chúng ta có thể chuyển đổi giữa tổng tích lũy hàng tuần và hàng hai tuần trong Google Sheets.
Kết luận
Thường tôi giải thích từng phần của công thức để người dùng học. Tuy nhiên, đã có một số trường hợp tôi không cung cấp giải thích công thức do sự phức tạp của công thức đó.
Dù sao, bạn có thể sử dụng công thức mảng tổng tích lũy hàng tuần / hàng hai tuần ngay trong Google Sheets. Nếu bạn muốn tìm hiểu công thức, hãy để lại bình luận và tôi sẽ chia sẻ bảng mẫu với bạn.
Dưới đây là một số tài nguyên liên quan đến CUSUM:
- Custom Named Function for Running Total by Group (Item, Month, or Year) In Google Sheets.
- Running Total with Monthly Reset in Google Sheets (Array Formula).
- Running Total by Category in Google Sheets (SUMIF Based).
- Reset Running Total at Every Year Change in Google Sheets (SUMIF Based).
