Trong Google Sheets, để đếm số đơn hàng hàng tuần, bạn có thể sử dụng các hàm ngày WEEKNUM hoặc WEEKDAY cùng với QUERY. Hướng dẫn này bao gồm cả hai hàm để giúp bạn:
- Xác định số lượt yêu cầu / đơn hàng nhận được hàng tuần hoặc riêng lẻ cho từng tuần.
- Tính toán số lượng đơn hàng được đặt hàng tuần.
Nếu bạn chọn hàm WEEKNUM, Tổng quan sẽ dựa trên số tuần. Ngược lại, hàm WEEKDAY tạo ra các tuần lịch, chẳng hạn như 28/Jan/19 – 03/Feb/19, thay vì số tuần (ví dụ: 5 cho tuần này).
Hướng dẫn sử dụng Google Spreadsheets này cung cấp cả hai giải pháp, và tôi cũng đã bao gồm hướng dẫn về việc sử dụng IMPORTRANGE. Điều này đặc biệt hữu ích nếu dữ liệu nguồn của bạn, tức là các ngày đã ghi, nằm trong một trang, và bạn muốn đếm theo tuần trong một trang khác.
Hãy làm theo các bước được đề cập trong hướng dẫn, bao gồm dữ liệu mẫu trong các ô A2: B. Bản chụp màn hình minh họa hai loại đầu ra, và tôi đã bao gồm cả các công thức cho cả hai loại. Chọn cái phù hợp nhất với nhu cầu của bạn.
Cách đếm đơn hàng hàng tuần bằng Công thức QUERY sử dụng số tuần
Phương pháp đầu ra #2 (Tham khảo ảnh chụp màn hình ở trên):
Trong (phương pháp đầu ra #2), tôi sử dụng số tuần để đại diện cho số lượng đơn hàng hàng tuần. Do đó, hàm WEEKNUM, kết hợp với QUERY, cho phép theo dõi biên nhận đơn hàng từng tuần.
Bây giờ, hãy đi vào chi tiết cách đếm số đơn hàng hàng tuần trong Google Sheets.
Tham khảo ví dụ đã được cung cấp, tôi đã đặt công thức vào ô D4. Hãy phân tích từng bước trong quá trình phát triển công thức đó.
a. Cách trích xuất số tuần và năm từ các ngày
Mặc dù hàm QUERY không có hàm cục bộ WEEKNUM, nhưng nó bao gồm hàm YEAR. Để trích xuất số tuần từ các ngày và xếp chúng theo chiều ngang cùng với ngày, chúng ta sẽ sử dụng hàm ngày WEEKNUM cơ bản.
Công thức #1: Nhập vào ô D2.
=HSTACK(A2:A, ARRAYFORMULA(WEEKNUM(A2:A,2)))
Dữ liệu nguồn bắt đầu từ hàng số 2, vì vậy hãy nhập công thức này vào ô D2.
Công thức mảng WEEKNUM sau trích xuất số tuần từ các ngày trong A2: A.
=ARRAYFORMULA(WEEKNUM(A2:A, 2))
Tôi đã xem xét thứ Hai-Chủ Nhật là tuần lịch, được chỉ định bởi số 2 trong công thức WEEKNUM. Nếu bạn thích Chủ Nhật-Thứ Bảy, hãy thay đổi số đó thành 1.
Lưu ý rằng các ô trống được coi là 0, và nếu định dạng là ngày, chúng sẽ hiển thị như 30/12/1899. Do đó, công thức có thể trả về 53 cho các ô trống. Điều này không phải vấn đề, vì chúng ta sẽ lọc bỏ các hàng trống đó trong QUERY.
Về năm, không cần tách riêng nó ra khỏi ngày, vì chúng ta có thể đạt được điều đó trong QUERY bằng cách sử dụng hàm con YEAR.
Chúng ta sẽ sử dụng công thức #1 trên như dữ liệu trong QUERY để trả về số lượng hàng tuần bằng Số tuần.
b. Sử dụng QUERY để nhóm số tuần và năm với hàm tổng hợp đếm
Chúng ta có thể sử dụng đầu ra công thức trên làm dữ liệu nguồn trong QUERY. Đó là một bộ dữ liệu hai cột. Đếm bất kỳ cột nào và nhóm chúng theo năm và sau đó theo số tuần. Đây là công thức đó.
Công thức #2 (Công thức chính): Sửa công thức hiện có trong ô D2
=LET(dt, A2:A, QUERY( HSTACK(dt, ARRAYFORMULA(WEEKNUM(dt, 2))), "SELECT YEAR(Col1), Col2, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), Col2 LABEL YEAR(Col1) 'Năm', Col2 'Tuần số', COUNT(Col1) 'Tổng'", 0 ))
Lưu ý:
Thay vì HSTACK(A2:A, ARRAYFORMULA(WEEKNUM(A2:A,2))), chúng ta đã sử dụng HSTACK(dt, ARRAYFORMULA(WEEKNUM(dt,2))) vì chúng ta đã sử dụng hàm LET, gán tên dt cho phạm vi A2:A.
Công thức này tuân theo cú pháp QUERY sau:
QUERY(data, query, [headers])
Trong đó:
- data: HSTACK(dt, ARRAYFORMULA(WEEKNUM(dt, 2)))
- query: “SELECT YEAR(Col1), Col2, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), Col2 LABEL YEAR(Col1) ‘Năm’, Col2 ‘Tuần số’, COUNT(Col1) ‘Tổng'”
- SELECT YEAR(Col1): Trích xuất năm từ cột đầu tiên (cột ngày gốc).
- Col2: Đại diện cho cột thứ hai, là số tuần được tạo ra.
- COUNT(Col1): Đếm số lần xuất hiện của từng kết hợp duy nhất giữa năm và số tuần.
- WHERE Col1 IS NOT NULL: Đảm bảo chỉ có giá trị không null trong cột ngày được xem xét.
- GROUP BY YEAR(Col1), Col2: Nhóm dữ liệu theo năm và số tuần.
- LABEL YEAR(Col1) ‘Năm’, Col2 ‘Tuần số’, COUNT(Col1) ‘Tổng’: Cung cấp nhãn tùy chỉnh cho các cột đầu ra.
header: 0
Đây là phương pháp đầu ra thứ hai để đếm số đơn hàng hàng tuần trong Google Sheets.
Cách đếm đơn hàng hàng tuần bằng WEEKDAY trong hàm QUERY
Phương pháp đầu ra #1 (Tham khảo ảnh chụp màn hình ở trên):
Đây là một giải pháp thay thế để đếm số đơn hàng hàng tuần trong Google Sheets. Trong phương pháp này, cần hai công thức: một cho các tên cột (nhãn trường) và một cho việc tạo số đếm.
a. Tạo tên cột theo từng tuần trong Google Sheets
Trong một hướng dẫn Google Sheets gần đây, tôi đã chi tiết cách tạo lịch tuần bắt đầu và kết thúc như các tên cột thay vì số tuần, sử dụng một kết hợp của hàm WEEKDAY.
Ở đây, tôi sẽ sử dụng một phiên bản cải tiến hơn (hiện đại) của công thức đó trực tiếp mà không cần giải thích. Nếu bạn quan tâm đến việc hiểu công thức này, bạn có thể tham khảo hướng dẫn của tôi có tiêu đề “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.” Tuy nhiên, điều này không bắt buộc, vì tôi có thể hướng dẫn bạn cách sửa công thức phạm vi để phù hợp với phạm vi dữ liệu của bạn.
Công thức #3 (Công thức chính 1):
=ArrayFormula(LET( dt, A2:A, wd, WEEKDAY(DATEVALUE(dt), 2), TOROW( TEXT(SORT(UNIQUE(TOCOL(dt - wd + 1, 3))), "DD/MM/YYYY") &" - "& TEXT(SORT(UNIQUE(TOCOL(dt - wd + 7, 3))), "DD/MM/YYYY") ) ))
Trong ví dụ của tôi, tôi đã nhập công thức trên vào ô D1, nơi nó tạo ra các tên cột.
Công thức trên được thiết kế cho các ngày biên nhận của chúng tôi trong A2: A. Nếu phạm vi dữ liệu của bạn khác, chỉ cần thay đổi tham chiếu A2: A trong công thức. Một điều chỉnh khác mà bạn có thể cần là định dạng ngày, hiện đang được đặt là “DD / MM / YYYY”. Hãy thay đổi thành “MM / DD / YYYY” nếu bạn thích.
Tương tự như đầu ra công thức #1, tôi đã xem xét thứ Hai-Chủ Nhật là tuần lịch, được chỉ định bởi số 2 (loại) được sử dụng trong công thức WEEKDAY. Nếu bạn thích Chủ Nhật-Thứ Bảy, hãy thay đổi số đó thành 1.
b. Số lượng đơn hàng từng tuần trong Google Sheets
Bây giờ, chúng ta muốn công thức đếm số đơn hàng hàng tuần cho phạm vi ngày được cung cấp trong tiêu đề. Vì mục đích đó, chúng ta có thể sử dụng công thức #2 đã đề cập trước đó (Công thức chính) với một số điều chỉnh.
Công thức đã đề cập trả về một đầu ra ba cột: “Năm”, “Tuần số” và “Tổng” là tên cột (tiêu đề).
Trong trường hợp này, chúng ta chỉ muốn cột cuối cùng (Tổng) từ đầu ra đó mà không có tên cột / nhãn. Ngoài ra, chúng ta cần chuyển vị kết quả.
Đây là công thức mà bạn có thể sử dụng trong ô D2.
Công thức #4 (Công thức chính 2):
=TOROW(LET(dt, A2:A, QUERY( HSTACK(dt, ARRAYFORMULA(WEEKNUM(dt,2))), "SELECT COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), Col2 LABEL COUNT(Col1) ''", 0 )))
Đó là tất cả. Sử dụng bất kỳ phương pháp đầu ra nào trong hai phương pháp, bạn có thể đếm số đơn hàng hàng tuần trong Google Sheets.
Đếm số đơn hàng hàng tuần sử dụng Dữ liệu được Nhập
Tôi có thể sử dụng hai phương pháp trên giữa hai tệp Google Sheets không?
Đúng vậy! Giả sử thông tin hàng ngày của bạn (dữ liệu nguồn) nằm trong tệp “receipt order”. Bạn muốn tạo báo cáo tổng cộng hàng tuần trong tệp khác có tên “weekly summary”.
Trong trường hợp đó, bạn có thể sử dụng hàm IMPORTRANGE để nhập dữ liệu vào tệp “weekly summary”. Dưới đây là các bước liên quan.
Ở đây, bạn cũng có thể áp dụng hai phương pháp trên.
Phương pháp đầu ra #2 (Tổng kết số tuần)
Bước:
-
Đầu tiên, nhập công thức IMPORTRANGE sau vào bất kỳ ô nào trong hàng số 2 của tệp “weekly summary” của bạn:
Công thức #5:
=IMPORTRANGE("URL", "Sheet1!A2:A")Trong công thức này, thay thế “URL” bằng URL của tệp “receipt order” của bạn và tên tab bằng tên tab chứa dữ liệu nguồn. Nếu bạn gặp lỗi #REF !, hãy cho phép truy cập.
-
Tiếp theo, cuộn lên trên cùng của trang này và sao chép công thức được cung cấp dưới “Công thức #2 (Công thức chính)”.
-
Thay thế các tham chiếu ô A2: A trong công thức đó bằng công thức IMPORTRANGE ở trên. Nó sẽ trông giống như sau:
Công thức #6 (Công thức chính):
=LET(dt, IMPORTRANGE("URL", "Sheet1!A2:A"), QUERY( HSTACK(dt, ARRAYFORMULA(WEEKNUM(dt, 2))), "SELECT YEAR(Col1), Col2, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), Col2 LABEL YEAR(Col1) 'Năm', Col2 'Tuần số', COUNT(Col1) 'Tổng'", 0 ))Nếu bạn làm đúng mọi thứ, nó sẽ trả về số lượng đúng theo số tuần. Điều này có nghĩa là Đầu ra #2 hiệu quả đếm số đơn hàng hàng tuần bằng dữ liệu được nhập vào trong Google Sheets.
Phương pháp đầu ra #1 (Tổng kết ngày bắt đầu và ngày kết thúc tuần)
Trong phương pháp này, hãy sao chép các công thức “Công thức #3 (Công thức chính 1)” và “Công thức #4 (Công thức chính 2)” và thay đổi tham chiếu ô A2: A bằng công thức IMPORTRANGE.
Tôi sẽ không lặp lại các công thức ở đây. Nếu bạn tuân theo hướng dẫn trên một cách cẩn thận, bạn có thể dễ dàng tạo được báo cáo tổng cộng hàng tuần kiểu này trong Google Sheets.
Đó là tất cả. Chúc bạn thành công!
