Bạn muốn tạo một báo cáo bảng Pivot theo tháng trong Google Sheets? Nếu dữ liệu có cột “Tháng”, chúng ta có thể dễ dàng nhóm cột đó giống như một cột danh mục.
Nhưng nếu dữ liệu của bạn không có cột tháng mà thay vào đó là một cột chứa ngày thì sao?
Đó là vấn đề chúng ta sẽ giải quyết trong bài viết này, cách nhóm cột ngày theo tháng trong bảng Pivot trong Google Sheets.
Chúng ta có thể áp dụng hai phương pháp để nhóm các ngày theo tháng bằng cách sử dụng menu Insert -> Pivot Table trong Google Sheets.
Sử dụng lệnh Pivot Table từ menu Insert
Chúng ta sẽ bắt đầu bằng phương pháp truyền thống sử dụng cột trợ giúp. Dưới đây là dữ liệu mẫu của chúng ta trong khoảng A1:C8.
1. Nhóm các ngày theo tháng trong Bảng Pivot trong Google Sheets: Phương pháp truyền thống
Dưới đây là các bước để tạo một báo cáo bảng Pivot theo tháng sử dụng cột trợ giúp trong Google Sheets.
Tôi sẽ sắp xếp nó thành hai phần: 1) Chuẩn bị dữ liệu và 2) Bảng Pivot.
Chuẩn bị dữ liệu:
Trước tiên, chèn một cột giữa cột A và cột B. Để làm điều đó, nhấp chuột phải vào tiêu đề cột B và chọn “Chèn một cột vào bên trái”.
Chèn công thức sau vào ô B1:
=MảngCôngThức(mảng("Tháng",nếu(lỗi(EOMONTH(A2:A,-1)+1))))
Công thức này sẽ chèn các ngày bắt đầu của tháng của cột A ngày.
Chọn B2:B8 và truy cập menu Định dạng -> Số -> Định dạng số tùy chỉnh và áp dụng định dạng mmm-yy.
Bảng Pivot:
Bây giờ là thời gian để nhóm các ngày theo tháng bằng cách sử dụng Bảng Pivot trong Google Sheets.
Chúng ta sẽ nhóm theo cột trợ giúp tháng mới được thêm vào cột B thay vì cột ngày A.
- Chọn phạm vi A2:D8.
- Chuyển đến menu Insert -> Pivot table.
- Kiểm tra “Existing Sheet” và ô F1 để tạo báo cáo bảng Pivot theo tháng trong Sheet hiện có. Tất nhiên, bạn có thể chọn Sheet và ô để chèn bảng Pivot vào Sheet của bạn.
- Nhấp vào Tạo.
- Trên bảng điều khiển bên, “Thêm” hoặc kéo và thả “Tháng” dưới trường Rows, “Miêu tả Vật liệu” dưới trường Columns và “Số lượng” dưới trường Values, và xong!
Điều này cho phép chúng ta nhóm các ngày theo tháng bằng bảng Pivot trong Google Sheets.
Lưu ý: Nếu bạn không muốn cột “Miêu tả Vật liệu” trong kết quả, đừng thêm nó dưới trường “Columns”.
2. Nhóm các ngày theo tháng trong Bảng Pivot trong Google Sheets: Phương pháp mới
Đây là phương pháp được đề xuất vì nó không yêu cầu một cột trợ giúp.
Dưới đây là các bước để nhóm các ngày theo tháng trong báo cáo bảng Pivot mà không cần cột trợ giúp trong Google Sheets.
- Chọn phạm vi A2:C8 (dữ liệu ‘ban đầu’ mà không có cột trợ giúp được chèn).
- Chuyển đến menu Insert -> Pivot table.
- Kiểm tra “Existing Sheet” và ô F1 (nếu bạn muốn, bạn có thể chọn một Sheet và một ô khác).
- Nhấp vào Tạo.
- Trên bảng điều khiển bên, “Thêm” hoặc kéo và thả “Ngày” dưới trường Rows, “Miêu tả Vật liệu” dưới trường Columns và “Số lượng” dưới trường Values.
- Nhấp chuột phải vào bất kỳ ngày nào trong cột đầu tiên trong báo cáo bảng Pivot và chọn Tạo nhóm ngày Pivot -> Năm-Tháng, và xong!
Phần này dành cho những người ham mê các chức năng của Google Sheets.
Nếu bạn mới làm quen với Query, hãy kiểm tra hướng dẫn từng bước sử dụng chức năng Truy vấn của Google Sheets của tôi. Đó là tài liệu đầy đủ để một người mới học cách sử dụng chức năng mạnh mẽ nhất này trong Google Sheets.
Hãy xem từng bước cách viết công thức Query để tạo báo cáo bảng Pivot theo tháng trong Google Sheets sử dụng cột ngày.
Các bước:
Chúng ta có thể sử dụng hàm số MONTH trong Query để nhóm dữ liệu theo số tháng. Để nhóm theo tên tháng, chúng ta sẽ thực hiện một phương pháp làm việc xung quanh.
Chúng ta sẽ sử dụng một cột trợ giúp. Vì vậy, chúng ta sẽ sử dụng dữ liệu mẫu dưới tiêu đề “1. Group Dates by Month in Pivot Table in Google Sheets: Old-School Approach.”
Lưu ý: Chúng ta có thể tránh sử dụng cột trợ giúp. Nhưng điều đó có thể làm cho công thức trở nên phức tạp hơn.
Chúng ta cần ba công thức Truy vấn (trong dạng lồng nhau) để tạo báo cáo bảng Pivot theo tháng trong Google Sheets.
Trước tiên, chúng ta sẽ sử dụng một công thức Truy vấn để trả về báo cáo bảng Pivot theo tháng.
query_1: Nhóm theo “Tháng” và xoay “Miêu tả Vật liệu.”
=truy vấn(A1:D,"Chọn B,tổng(D) nơi A không null nhóm theo B PIVOT C",1)
Để thêm một hàng tổng ở cuối kết quả của query_1, chúng ta sẽ cần một công thức bổ sung, đây là nó.
query_2: Nhóm theo “Miêu tả Vật liệu” để trả về tổng theo từng mô tả vật liệu. Kết quả được đảo ngược để tạo thành hàng.
=chuyển vị(truy vấn(A1:D,"Chọn tổng(D) nơi A không null nhóm theo C label tổng(D)'Tổng cộng'",1))
query_3: Nhóm theo “Tháng” để trả về tổng theo từng tháng. Để nó, chúng ta sẽ nối dọc tổng cột tháng.
=vstack(truy vấn(A1:D,"Chọn tổng(D) nơi A không null nhóm theo B label tổng(D)'Tổng cộng'",1),tổng(D:D))
Hãy sử dụng các hàm VSTACK và HSTACK để nối ba kết quả trên dọc và ngang.
Cú pháp: hstack(vstack(query_1,query_2),query_3)
=hstack(vstack(truy vấn(A1:D,"Chọn B,tổng(D) nơi A không null nhóm theo B PIVOT C",1),chuyển vị(truy vấn(A1:D,"Chọn tổng(D) nơi A không null nhóm theo C label tổng(D)'Tổng cộng'",1))),vstack(truy vấn(A1:D,"Chọn tổng(D) nơi A không null nhóm theo B label tổng(D)'Tổng cộng'",1),tổng(D:D)))
Đó là tất cả. Cảm ơn bạn đã đọc bài viết. Chúc bạn vui vẻ!
