Cách tạo báo cáo tổng kết các tháng trước trong Google Sheets

Trong bài viết này, chúng ta sẽ tìm hiểu cách tạo báo cáo tổng kết các tháng trước (tính từ ngày hôm nay) trong Google Sheets. Trong báo cáo tổng kết, chúng ta có thể sử dụng các hàm tổng, trung bình, đếm, lớn nhất, nhỏ nhất hoặc tất cả chúng.

Để tạo báo cáo tổng kết các tháng trước trong Google Sheets, chúng ta cần ít nhất hai cột dữ liệu. Cột đầu tiên là cột ngày và cột thứ hai là cột số.

Dữ liệu mẫu: Dữ liệu mẫu của tôi gồm khoảng 504 dòng. Vì vậy, tôi không thể đăng tải hình ảnh “đầy đủ” hoặc bao gồm nó trong bảng này. Dưới đây là trang đầu tiên để bạn nhanh chóng nhìn qua.

Ngoài ra, còn một lý do nữa để không cung cấp cho bạn dữ liệu mẫu.

Vì tính toán dựa trên các tháng trở lại, ngày hiện tại đóng vai trò quan trọng trong dữ liệu mẫu của tôi. Vì vậy, vào ngày trong tương lai, dữ liệu mẫu của tôi sẽ không phù hợp cho việc thử nghiệm hoặc giải thích dưới đây.

Bạn có thể tạo một dữ liệu mẫu phù hợp để thử nghiệm như trên bằng cách chèn công thức dưới đây vào ô A3, sẽ chèn các ngày trong mảng/phạm vi A3:A503 (phạm vi có thể thay đổi một chút).

=SEQUENCE( days(today(),today()-500)+1, 1, today()-500 )

Sau đó, điền số ngẫu nhiên vào cột tiếp theo (B3:B503).

Bây giờ chúng ta sẽ di chuyển đến các bước để tạo báo cáo tổng kết n tháng trước trong Google Sheets.

Giới thiệu

Giả sử ngày hôm nay là ngày 20 tháng 4 năm 2020 và chúng ta có một bảng trong cột A3:A trong đó A3:A chứa các ngày từ ngày 01 tháng 2 năm 2020 đến ngày 20 tháng 4 năm 2020.

Các ngày nằm trong tháng hai sẽ được tổng kết dưới tháng trước thứ 2, tháng Ba sẽ nằm dưới tháng trước thứ 1 và tháng Tư sẽ nằm dưới tháng trước thứ 0.

Tôi hy vọng bạn có thể hiểu được điều này. Bây giờ câu hỏi ở đây là làm thế nào để xác định số tháng trước khi cuộn trở lại từ một phạm vi ngày như trên trong Google Sheets.

Để tìm số tháng trước cuộn của bất kỳ ngày nào từ ngày hôm nay trở lại, chúng ta có thể sử dụng hàm DATEDIF. Nhưng không theo cách sử dụng tiêu chuẩn.

Đọc thêm: [Hướng dẫn sử dụng các hàm ngày tháng trong Google Sheets].

Tại sao chỉ sử dụng hàm DATEDIF không đủ để xác định số tháng cuộn trước

Giả sử ngày trong ô A3 là ngày 24 tháng 3 năm 2020. Theo ví dụ trước đây của tôi, số tháng cuộn trở lại của ngày này sẽ là 1 vì đó là một trong những ngày từ tháng trước (số tháng hiện tại [tháng Tư] sẽ là 0).

Nhưng công thức DATEDIF sau đây sẽ không trả về 1, thay vào đó nó sẽ trả về 0!

Cú pháp: DATEDIF(ngày_bắt_đầu, ngày_kết_thúc, đơn_vị)

Công thức: =datedif(A3,hôm_nay(),"tháng")

Chú ý: Nếu bạn thử nghiệm công thức này, với cùng một ngày trong ô A3, vào ngày trong tương lai (theo đồng hồ hệ thống của tôi hôm nay là ngày 20 tháng 4 năm 2020), bạn có thể nhận được một số khác. Vì vậy, hãy để kiểm tra nó. Hãy chỉ tập trung vào giải thích của tôi.

Tại sao hàm DATEDIF trả về 0 thay vì 1?

Với DATEDIF, từ ngày 21 tháng 3 năm 2020 đến ngày 20 tháng 4 năm 2020 là tháng # 0 vì nó phụ thuộc vào số tháng hoàn chỉnh giữa ngày_bắt_đầu và ngày_kết_thúc, không phải là tháng theo lịch.

Có cách nào để giải quyết vấn đề này không?

DATEDIF và EOMONTH để trả về số tháng cuộn trước chính xác trong Google Sheets

Nếu bạn biết cách lấy số tháng cuộn trước, sau đó tạo báo cáo tổng kết các tháng cuộn trở lại sẽ rất dễ dàng bằng cách sử dụng hàm Query trong Google Sheets.

Tôi đã giải thích vấn đề DATEDIF ở trên. Vì vậy, hãy tìm cách giải quyết.

Giải pháp: Chuyển đổi tất cả các ngày thành ngày bắt đầu tháng.

Công thức chung như đã đề cập:

=DATEDIF( ngày_bắt_đầu_tháng_của_ngày_cần_tìm_số_tháng_cuộn_trở_lại, ngày_bắt_đầu_tháng_của_ngày_hôm_nay(), "tháng" )

Ô A3 chứa ngày 24 tháng 3 năm 2020. Chuyển đổi nó thành ngày bắt đầu tháng (01 tháng 3 năm 2020) bằng cách sử dụng công thức EOMONTH dưới đây.

=eomonth(A3,-1)+1

Chú ý: Nếu bạn mới làm quen với hàm EOMONTH trên hoặc bất kỳ hàm nào khác được đề cập hoặc sẽ được đề cập trong bài viết này, hãy kiểm tra Hướng dẫn về Các hàm Google Sheets của tôi.

Công thức dưới đây sẽ trả về ngày bắt đầu tháng ‘hiện tại’ (01 tháng 4 năm 2020).

=eomonth(hôm_nay(),-1)+1

Vì vậy, công thức DATEDIF để trả về số tháng cuộn trước đúng của ngày trong ô A3 sẽ như sau.

=DATEDIF( eomonth(A3,-1)+1, eomonth(hôm_nay(),-1)+1, "tháng" )

Đối với toàn bộ dữ liệu trong cột A3:A, chúng ta có thể sử dụng nó như một công thức mảng.

=ArrayFormula( DATEDIF( eomonth(A3:A,-1)+1, eomonth(hôm_nay(),-1)+1, "tháng" ) )

Sử dụng Query để tạo báo cáo tổng kết n tháng cuộn từ hôm nay trong Google Sheets

Chúng ta đã hoàn thành phần “phức tạp” của bài hướng dẫn. Bây giờ chúng ta có thể dễ dàng tạo báo cáo tổng kết các tháng cuộn trở lại trong Google Sheets.

Tôi sẽ sử dụng hàm Query cho việc này và đây là cú pháp.

Cú pháp: QUERY(data, query, [headers])

“Data” của chúng ta nằm trong A3:B. Trong đó, chúng ta nên thay A3:A bằng công thức DATEDIF của chúng tôi và thay B3:B bằng các giá trị thực tế trong B3:B. Vì vậy, chúng ta phải sử dụng A3:B (data) như sau.

={ArrayFormula( DATEDIF( eomonth(A3:A,-1)+1, eomonth(hôm_nay(),-1)+1, "tháng" ) ),B3:B}

Tổng kết các tháng cuộn trở lại (Công thức #1):

Ở đây, công thức Query sử dụng “data” trên để nhóm cột 1, tức là số tháng trước và tổng hợp cột 2.

=QUERY( {ARRAYFORMULA( DATEDIF(eomonth(A3:A,-1)+1,eomonth(hôm_nay(),-1)+1,"tháng") ), B3:B}, "select Col1,SUM(Col2),AVG(Col2),MIN(Col2),MAX(Col2) where Col2 is not null group by Col1 order by Col1 desc label Col1'Tháng cuộn trở lại'" )

Tôi đã sử dụng các hàm tổng, trung bình, đếm, nhỏ nhất và lớn nhất trong công thức trên. Bạn có thể loại bỏ những hàm không cần thiết.

Giới hạn số tháng thành N (Hai phương pháp)

Phương pháp 1: Bằng cách bao gồm một điều kiện bổ sung trong mệnh đề WHERE.

Giả sử bạn muốn tổng kết các tháng cuộn trở lại 12 tháng. Điều đó có nghĩa là ‘n’ ở đây là 12.

Dưới đây là công thức (công thức # 2) để tổng kết tổng kết 12 tháng cuộn trước trong Google Sheets.

=QUERY( {ARRAYFORMULA( DATEDIF(eomonth(A3:A,-1)+1,eomonth(hôm_nay(),-1)+1,"tháng") ), B3:B}, "select Col1,SUM(Col2),AVG(Col2),MIN(Col2),MAX(Col2) where Col2 is not null and Col1<12 group by Col1 order by Col1 desc label Col1'Tháng cuộn trở lại'" )

So với công thức # 1 và Col1<12 là điều kiện bổ sung được sử dụng trong mệnh đề WHERE. Trong đó <12 kiểm soát ‘n’.

Phương pháp 2: Bằng cách lọc các ngày.

Thay vì điều kiện bổ sung trong mệnh đề WHERE, bạn có thể trước tiên trích xuất phạm vi dữ liệu yêu cầu bằng cách lọc nó và sau đó sử dụng công thức # 1 của tôi trong phạm vi đó.

Để lọc, bạn có thể tham khảo hướng dẫn của tôi – Công thức để lọc N ngày | Tháng trong Google Sheets.

Đó là tất cả về việc tổng kết các tháng cuộn trở lại trong Google Sheets. Thành công!

Bảng mẫu

Tài liệu tham khảo: