Như chúng ta đã biết, tạo một báo cáo tổng kết theo tháng trong Google Sheets không phải là một nhiệm vụ khó khăn. Có một số cách tốt để làm điều này, chẳng hạn như Bảng tổng hợp, hàm SUMIF và hàm TRUY VẤN.
Trong Excel, chúng ta có rất nhiều tùy chọn. Chúng ta có thể sử dụng các lệnh trong menu như Data > Subtotal và Insert > Pivot Table và sử dụng các hàm như SUMIF, SUMIFS, v.v.
Trong Google Sheets cũng vậy, bạn có thể áp dụng tất cả các phương pháp này, ngoại trừ Subtotal. Bên cạnh đó, chúng ta cũng có thể sử dụng hàm TRUY VẤN, hàm linh hoạt nhất trong Google Sheets.
Vậy, trong bài hướng dẫn này, chúng ta hãy tìm hiểu cách tạo một báo cáo tổng kết theo tháng trong Google Sheets với sự trợ giúp từ hàm TRUY VẤN.
Chuẩn bị dữ liệu mẫu:
Chúng ta sẽ tạo một tổng kết theo tháng của mỗi sản phẩm bằng cách nhóm cột ngày và cột sản phẩm.
Hãy làm theo hướng dẫn từng bước dưới đây.
Bước 1: Nhóm theo tháng
Công thức: TRUYVAN(data, truyvan, [tiêu đề])
Công thức TRUY VẤN sau đây có thể được sử dụng để nhóm cột ngày theo tháng và tổng các cột khác trong Google Sheets:
=TRUYVAN(A1:F,"CHỌN THÁNG(A), TỔNG(F) NHÓM THEO THÁNG(A)",1)
Công thức này có ba đối số:
- Phạm vi dữ liệu cần truy vấn, trong trường hợp này là
A1:F. - Câu lệnh truy vấn, trong trường hợp này là
CHỌN THÁNG(A), TỔNG(F) NHÓM THEO THÁNG(A), được bao quanh bằng dấu ngoặc kép. - Số hàng tiêu đề, trong trường hợp này là
1.
Câu lệnh truy vấn chọn các tháng của các ngày trong cột A và tổng các giá trị trong cột F, nhóm kết quả theo tháng.
Tuy nhiên, kết quả tổng kết theo tháng ở trên có ba vấn đề:
- Mã tháng hiển thị không chính xác vì hàm MONTH trong TRUY VẤN trả về mã tháng dưới dạng số nguyên bắt đầu từ 0. Một được hiển thị là 0, Hai là 1, v.v.
- Có hàng trống trong kết quả.
- Nhãn không gọn gàng.
Hãy khắc phục những vấn đề này trước tiên.
Công thức sau sẽ điều chỉnh mã tháng:
=TRUYVAN(A1:F,"CHỌN THÁNG(A)<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-red-color">+1</mark>, TỔNG(F) NHÓM THEO THÁNG(A)<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-red-color">+1</mark>",1)
Công thức này sẽ loại bỏ các hàng trống:
=TRUYVAN(A1:F,"CHỌN THÁNG(A)+1, TỔNG(F) <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-red-color">NƠI A KHÔNG PHẢI NULL</mark> NHÓM THEO THÁNG(A)+1",1)
Bây giờ chúng ta chỉ còn việc làm cho các nhãn gọn gàng. Chúng ta có thể sử dụng mệnh đề LABEL cho việc này.
=TRUYVAN(A1:F,"CHỌN THÁNG(A)+1, TỔNG(F) NƠI A KHÔNG PHẢI NULL NHÓM THEO THÁNG(A)+1 <mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-red-color">NHÃN THÁNG(A)+1 'THÁNG', TỔNG(F)'TỔNG'</mark>",1)
Bạn có thể thích: Thứ tự mệnh đề chính xác trong TRUY VẤN Google Sheets là gì?
Chúng ta đã tạo một báo cáo tổng kết theo tháng cơ bản trong Google Sheets. Nó chỉ chứa hai cột: Cột tháng và cột tổng.
Bước 2: Nhóm theo tháng và sản phẩm
Để nhóm theo sản phẩm, hãy bao gồm nó trong cả các mệnh đề CHỌN và NHÓM THEO. Dưới đây là công thức cuối cùng:
=TRUYVAN(A1:F,"CHỌN THÁNG(A)+1,<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-red-color">B</mark>, TỔNG(F) NƠI A KHÔNG PHẢI NULL NHÓM THEO THÁNG(A)+1,<mark style="background-color:rgba(0, 0, 0, 0)" class="has-inline-color has-vivid-red-color">B</mark> NHÃN THÁNG(A)+1 'THÁNG', TỔNG(F)'TỔNG'",1)
Chúng ta đã tạo một báo cáo tổng kết theo tháng sử dụng TRUY VẤN trong Google Sheets.
Đếm, Trung bình, Giá trị nhỏ nhất và lớn nhất trong báo cáo tổng kết theo tháng trong Google Sheets
Khi bạn tạo báo cáo tổng kết theo tháng bằng hàm TRUY VẤN, bạn có thể sử dụng các hàm tổng hợp khác ngoài hàm SUM.
Ví dụ, bạn có thể sử dụng các hàm tổng hợp TRUNG BÌNH, ĐẾM, TỐI ĐA và TỐI THIỂU của TRUY VẤN để tính trung bình, số lượng, giá trị lớn nhất và giá trị nhỏ nhất trong một nhóm dữ liệu.
Ví dụ, để tạo một báo cáo tổng kết theo tháng của doanh số trung bình các sản phẩm, bạn có thể sử dụng công thức sau:
=TRUYVẤN(A1:F,"CHỌN THÁNG(A)+1,B, TRUNG BÌNH(F) NƠI A KHÔNG PHẢI NULL NHÓM THEO THÁNG(A)+1,B NHÃN THÁNG(A)+1 'THÁNG', TRUNG BÌNH(F)'TỔNG'",1)
Kết luận
Khi thay thế TRUY VẤN data bằng dữ liệu đã được nhập, chẳng hạn như một công thức IMPORTRANGE, bạn phải thay thế các định danh cột trong câu lệnh query bằng các số cột.
Bạn sẽ sử dụng Col1 để đại diện cho cột đầu tiên trong dữ liệu đã nhập, Col2 cho cột thứ hai, v.v.
Dưới đây là một công thức ví dụ:
=TRUYVẤN(IMPORTRANGE("URL","Sheet1!A1:F"),"CHỌN THÁNG(Col1)+1,Col2, TRUNG BÌNH(Col6) NƠI Col1 KHÔNG PHẢI NULL NHÓM THEO THÁNG(Col1)+1,Col2 NHÃN THÁNG(Col1)+1 'THÁNG', TRUNG BÌNH(Col6)'TỔNG'",1)
Chúng ta đã tạo một báo cáo tổng kết theo tháng sử dụng số tháng để nhóm. Nếu bạn muốn sử dụng tên tháng thay vì số, bạn có thể sử dụng hàm EOMONTH trong TRUY VẤN data.
Bạn có thể tìm thấy những hướng dẫn hữu ích sau:
