Đếm theo từng tuần với điều kiện trong Google Sheets có thể được thực hiện thông qua công thức Query. Nếu bạn không muốn chỉ định bất kỳ điều kiện nào, bạn có thể sử dụng các hàm Query, Countif hoặc Countifs. Trong trường hợp đó:
- Hàm Countif sẽ đủ để đếm theo tuần nếu bạn không muốn tính cả phần năm trong báo cáo từng tuần.
- Nếu bạn muốn bao gồm cả năm trong báo cáo từng tuần, hãy chọn Countifs.
- Công thức Query hữu ích trong tất cả các trường hợp.
Vì vậy, trong bài hướng dẫn này, chúng ta sẽ tìm hiểu cách sử dụng công thức Query trong việc đếm từng tuần với điều kiện trong Google Sheets.
Tuy nhiên, để làm quen với Countif và Countifs trong việc đếm từng tuần, tôi đã bao gồm chúng trong bài viết này.
Chúng ta có thể sử dụng dữ liệu sản xuất cơ bản sau đây để thử nghiệm:
Countifs trong Đếm Theo Từng Tuần Với Điều Kiện Trong Google Sheets
Hãy bắt đầu với Countif.
Nếu bạn muốn đếm số lượng hàng hóa từng tuần, bạn có thể sử dụng công thức Countif sau đây:
Bước 1: Nhập công thức Unique Array vào ô E2 để lấy các số tuần duy nhất.
=ArrayFormula( unique(weeknum(A2:A18)) )Bước 2: Sau đó, sử dụng công thức Countif sau đây trong ô F2:
=ArrayFormula( countif( weeknum(A2:A19), E2:E6 ) )Trong công thức Countif, chúng ta chỉ được phép sử dụng một điều kiện theo cách “bình thường” (không kết hợp cột và tiêu chí). Điều kiện đó là số tuần chúng ta đã đếm từ cột ngày. Do đó, không có phạm vi để sử dụng điều kiện khác.
Countifs với Một Điều Kiện – Hàng Hóa
Countifs hoạt động trong việc đếm từng tuần với điều kiện, nhưng nó có một số hạn chế. Hãy tìm hiểu chi tiết để bạn hiểu tại sao chúng ta nên sử dụng công thức Query.
Hãy giả sử chúng ta muốn đếm số lượng hàng hóa “Sản phẩm A” từng tuần. Điều kiện ở đây là “Sản phẩm A”.
Ở đây, chúng ta có thể sử dụng công thức Countifs với cú pháp sau đây:
=ArrayFormula( countifs( weeknum(A2:A19),E2:E6, B2:B19,"Product A" ) )Công thức trên là một ví dụ về việc đếm từng tuần với một tiêu chí trong Google Sheets.
Countifs với Nhiều Điều Kiện – Hàng Hóa và Kiểm Tra Chất Lượng
Nếu bạn muốn đếm số lượng hàng hóa “Sản phẩm A” với điều kiện kiểm tra chất lượng “Đạt”, công thức sẽ như sau:
=ArrayFormula( countifs( weeknum(A2:A19), E2:E6, B2:B19,"Product A", C2:C19,"Passed" ) )Ngoài số tuần, chúng ta cũng có thể bao gồm cả năm trong công thức. Để làm điều đó, chúng ta phải thay thế công thức số tuần E2 bằng công thức số tuần và năm sau đây:
=ArrayFormula( UNIQUE({year(A2:A19),weeknum(A2:A19)}) )Kết quả của công thức trên sẽ là hai cột, tức là trong phạm vi E2:F6.
Tương ứng, chúng ta phải sửa công thức đếm từng tuần có điều kiện trên cùng (công thức F2 trước đó) và sử dụng nó trong ô G2. Đây là công thức đó:
=ArrayFormula( countifs( year(A2:A19),E2:E6, weeknum(A2:A19),F2:F6, B2:B19,"Product A", C2:C19,"Passed" ) )Tuy nhiên, để tạo báo cáo tóm tắt số liệu đếm từng tuần với điều kiện, chúng ta có thể sử dụng hàm Query một cách tốt hơn.
Đếm Theo Từng Tuần Theo Điều Kiện Sử Dụng Query
Thực tế, không có hàm Weeknum trong Query của Google Sheets. Vì vậy, ở đây chúng ta cũng nên sử dụng hàm WEEKNUM để lấy số tuần từ ngày.
Khác với Countif và Countifs, chúng ta sẽ sử dụng công thức Weeknum (công thức trong ô E2) trong Query như một “dữ liệu”, không phải trong một cột trợ giúp nào đó.
Cú pháp của Query như sau:
QUERY( data, query, [headers] )Dữ Liệu Cho Query – Năm và Số Tuần
Nếu chúng ta tính cả số tuần và số năm, “dữ liệu” của Query sẽ như sau (bảng có bốn cột):
=ArrayFormula( {YEAR(A2:A19),weeknum(A2:A19),B2:C19} )Ở đây, chúng ta không sử dụng hàm Unique cho “dữ liệu” (hãy tham khảo việc sử dụng hàm Unique trong Countifs ở trên). Bởi vì trong Query, các số tuần và số năm trùng lặp sẽ tự động được loại bỏ trong quá trình nhóm.
Chúng ta sẽ sử dụng công thức trên trong Query.
Dưới đây là công thức Query đầu tiên của tôi cho đếm từng tuần với điều kiện trong Google Sheets (sẽ trả về bảng như dưới đây):
=query( ArrayFormula({YEAR(A2:A19),weeknum(A2:A19),B2:C19}), "Select Col1,Col2,Col3,count(Col3) group by Col1,Col2,Col3 label Col1'Year', Col2'Week',Col3'Item'" )Công thức trên đếm số lượng hàng hóa dựa trên năm và tuần và kết quả sẽ là một bảng như trên.
Trong công thức đếm từng tuần với điều kiện trên trong Google Sheets, tôi sẽ sử dụng một điều kiện khác.
Tôi chỉ muốn đếm số lượng hàng hóa “Đạt” (kiểm tra chất lượng). Vì vậy, trước mệnh đề “Group By”, chèn điều kiện vào mệnh đề “Where” như sau trong Query:
where Col4 ='Passed'Vì vậy, công thức sẽ là:
=query( ArrayFormula({YEAR(A2:A19),weeknum(A2:A19),B2:C19}), "Select Col1,Col2,Col3,count(Col3) where Col4 ='Passed' group by Col1,Col2,Col3 label Col1'Year', Col2'Week',Col3'Item'" )Kết quả:
Quan trọng! Vì sao tôi sử dụng cột 4 thay vì cột 3 (kiểm tra chất lượng là cột thứ ba trong dữ liệu nguồn)? Bởi vì số thứ tự cột trong dữ liệu Query như sau:
- Cột 1: Năm
- Cột 2: Tuần
- Hai cột sau là hai cột cuối cùng trong dữ liệu nguồn của chúng ta:
- Cột 3: Hàng hóa
- Cột 4: Kiểm tra chất lượng
Mẹo Bổ Sung – Dữ Liệu Phạm Vi Mở
Công thức trên là cho phạm vi A2:C19.
Tôi muốn nói rằng, bạn có thể thấy việc sử dụng phạm vi A2:A19, B2:C19, v.v. trong công thức.
Làm thế nào để biến chúng thành mở như A2:A?
Nếu bạn nhập Dữ Liệu Query, tức là ARRAYFORMULA({YEAR(A2:A),weeknum(A2:A),B2:C}), vào bất kỳ ô nào, bạn sẽ thấy vấn đề này. Ngay cả các hàng trống cũng có giá trị như 52 trong cột số tuần và 1899 trong cột năm.
Điều này có thể gây ra vấn đề hoặc không, phụ thuộc vào các tiêu chí sử dụng trong Query.
Vì vậy, trong phạm vi mở, hãy sử dụng công thức sau làm Dữ Liệu Query:
ARRAYFORMULA(if(len(A2:A),{YEAR(A2:A),weeknum(A2:A),B2:C},))Đó là tất cả về việc đếm từng tuần theo điều kiện trong Google Sheets. Chúc bạn thành công!
