Bạn đã từng tạo báo cáo tổng kết hàng tuần trong Google Sheets như hình dưới đây chưa? Nếu chưa, hãy sẵn sàng! Bạn có thể học cách tạo báo cáo tổng kết hàng tuần trong Google Sheets ở đây.
Báo cáo này trông giống bảng trực quan Pivot Table tổng kết theo tuần trong Google Sheets. Tuy nhiên, với Pivot Table trong Google Sheets, bạn không thể tạo được báo cáo như thế này.
Ở đây, ví dụ này sử dụng một bảng chi tiêu cá nhân hàng ngày gồm ba cột. Từ bảng này, tôi muốn tìm tổng kết chi tiêu hàng tuần theo từng mô tả.
Nếu bạn phân tích cẩn thận báo cáo trên trong khoảng E2:J8 (khoảng được tự động điều chỉnh dựa trên dữ liệu trong cột A2:C), bạn có thể hiểu một điều. Nó có một số điểm tương đồng với báo cáo Pivot Table.
Tôi sẽ sử dụng công thức Query của Google Sheets và tính năng Pivot để tạo báo cáo tổng kết hàng tuần này.
Tôi chắc chắn rằng một số bạn có thể quen thuộc với cách tạo báo cáo tổng kết hàng tháng trong Google Sheets.
Nhưng việc tạo báo cáo tổng kết hàng tuần trong Google Sheets thì khó khăn. Dưới đây là ví dụ về công thức tuyệt vời này.
Dữ liệu mẫu để tạo báo cáo tổng kết hàng tuần trong Google Sheets
Trên một Bảng tính trống, gõ dữ liệu dưới đây. Nó lan truyền chỉ trong vài hàng, nhưng đủ để tạo một báo cáo tổng kết hàng tuần trong Google Sheets.
Trước khi đi vào các bước, hãy thử công thức chính dưới đây trong bảng của bạn. Có thể bạn thấy công thức hơi phức tạp, nhưng nó không phải như vậy. Bạn có thể học nó.
Tôi sẽ cố gắng giải thích từng phần của công thức này một cách chi tiết để bạn có thể tạo báo cáo tổng kết hàng tuần trong Google Sheets dễ dàng.
Công thức Query để Tạo Tổng Kết Hàng Tuần từ Cột Ngày (Công thức chính)
Đầu tiên, áp dụng công thức sau (để tóm tắt dữ liệu trong A2:C) vào ô E2 và xem kết quả. Sau đó, tôi sẽ giải thích cho bạn cách tạo công thức Query này để tổng kết dữ liệu của bạn theo tuần.
={query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Col2,Sum(Col3) where Col1>0 group by Col2 Pivot Col1 Label Col2 'Description'",0);transpose(query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Sum(Col3) where Col1>0 group by Col1 label Sum(Col3) 'Grand Total'",0))}
Bạn chỉ cần dùng phần đậm của công thức để tạo tổng kết hàng tuần.
Phần còn lại của công thức là để tạo hàng tổng kết cuối cùng trong tổng kết.
Bạn có thể đặt câu hỏi là tại sao tôi nói như vậy? Vì tôi muốn thuyết phục bạn rằng công thức không phức tạp như bạn có thể nghĩ.
Để làm cho hướng dẫn này dễ dàng cho bạn, tôi đã tạo một bảng mẫu nơi bạn có thể xem dữ liệu mẫu trên và công thức chính hoạt động.
Ngoài ra, tôi đã chia công thức chính thành công thức_1, công thức_2 và công thức_3 như chi tiết dưới các tiêu đề khác nhau. Tôi đã nhập nó vào bảng được chia sẻ để cho bạn biết cách tạo báo cáo tổng kết hàng tuần trong Google Sheets từng bước một.
Báo cáo hàng tuần bằng công thức Query – Giải thích công thức chính
Từ dữ liệu mẫu trên, không thể tạo báo cáo tổng kết hàng tuần trong Google Sheets. Bạn biết tại sao không?
Lý do là nhập ngày vào cột A. Đầu tiên, bạn phải chuyển đổi ngày trong cột A thành tuần. Đợi đã! Không cần làm gì thủ công.
Công thức chính của chúng tôi xử lý việc này. Đây chỉ là một giải thích về cách công thức chính xử lý việc này.
Công thức 1: Chuyển cột Ngày thành Số Tuần và Giữ Lại các Cột Khác (Dữ liệu của Công thức Query)
Để giải thích, chúng ta có thể chia công thức chính tạo báo cáo tổng kết hàng tuần trong Google Sheets thành ba phần. Chúng là Công thức_1, Công thức_2 và Công thức_3.
Đây là phần giải thích cho phần đầu tiên. Tôi đã áp dụng phần công thức này riêng lẻ trong ô L2 trong bảng được chia sẻ ở trên cho bạn.
={ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}
Hãy xem các giải thích dưới đây.
Công thức Mảng WEEKNUM để Chuyển Đổi Cột Ngày thành Số Tuần
=ARRAYFORMULA(WEEKNUM(A2:A))
Công thức này chuyển đổi ngày trong khoảng A2:A thành Số Tuần.
Mỗi tuần trong một năm có các số nhận dạng khác nhau được gọi là số tuần. Ở đây, tuần bắt đầu từ Chủ Nhật và kết thúc vào Thứ Bảy.
Nếu bạn muốn ngày bắt đầu và kết thúc khác như từ Thứ Hai đến Chủ Nhật, hãy kiểm tra các hàm ngày trong Hướng dẫn về Công thức của tôi. Ở đó bạn có thể xem cách sử dụng hàm WEEKNUM trong Google Sheets chi tiết.
Giữ Lại Các Cột Còn Lại trong Nguồn
Phần khác của công thức trên là phạm vi ô B2:C. Nó chỉ trả về phạm vi các cột được tham chiếu như thế.
=B2:C
Trên bức ảnh chụp màn hình, tôi đã đánh dấu kết quả cá nhân của WEEKNUM trên và tham chiếu phạm vi ô trên. Ngoài ra, bạn cũng có thể thấy tôi đã sử dụng các hàm ARRAYFORMULA, IF và LEN.
Chúng ta phải sử dụng ARRAYFORMULA với WEEKNUM khi sử dụng nó trong một phạm vi. Nếu nó chỉ nằm trong một ô duy nhất thì không cần thiết.
Sau đó là việc sử dụng IF và LEN. Sự kết hợp này phổ biến nhất trong hầu hết các công thức mà phạm vi vô hạn (A2:A) được sử dụng. Nó sẽ loại bỏ các hàng trống trong kết quả.
Nếu phạm vi của chúng tôi là A2:A13, bạn có thể không sử dụng sự kết hợp này. Tôi có một hướng dẫn riêng về việc sử dụng kết hợp IF và LEN ở đây. Nếu bạn muốn, bạn có thể kiểm tra nó.
Công thức 2: Pivot Số Tuần để Tạo Tổng Kết Hàng Tuần trong Bảng
Hãy cuộn lên và kiểm tra công thức chính cho phần đậm.
Trong bảng Google Sheets được chia sẻ, bạn có thể thấy công thức này (phần đậm của công thức chính) đã nhập vào ô P2.
=query({ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C},"Select Col2,Sum(Col3) where Col1>0 group by Col2 Pivot Col1 Label Col2 'Description'",0)
Tôi đã giải thích một phần của công thức này, tức là {ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}, ở trên, nó hoạt động như DỮ LIỆU trong Query.
Phần còn lại thì đơn giản nếu bạn biết cách sử dụng hàm Query trong Bảng tính của Google Doc. Nó tạo ra báo cáo tổng kết hàng tuần.
Ừ! Đây là câu trả lời của tôi cho câu hỏi làm thế nào để tạo báo cáo tổng kết hàng tuần trong Google Sheets. Hãy để tôi giải thích nó. Tôi biết rằng tôi nên giới thiệu bạn trước cú pháp hàm Query.
Cú pháp Query cho Mục đích Giải thích
QUERY(data, query, [headers])
Nếu chúng ta so sánh công thức 2 trên với Cú pháp Query, phần dưới đây chính là “data” của chúng ta trong Query.
{ArrayFormula(if(len(A2:A),(WEEKNUM(A2:A)),)),B2:C}
Chúng ta đã học nó rồi.
Phần còn lại của công thức là “query”. Nó nhóm dữ liệu cột 2 (mô tả) và tổng cột 3 (số tiền).
“Báo cáo tổng kết hàng tuần với Query”
