Chào các bạn! Hôm nay tôi sẽ hướng dẫn các bạn cách tính trung bình theo quý trong Google Sheets. Chúng ta có thể sử dụng ba phương pháp: QUERY, Pivot Table và AVERAGEIFS. Cả ba phương pháp đều có thể giúp bạn tính trung bình theo quý mà không cần sử dụng cột trợ giúp. Tuy nhiên, phương pháp AVERAGEIFS yêu cầu bạn sử dụng một cột trợ giúp chứa các ngày được chuyển đổi thành quý.
Nếu dữ liệu của bạn kéo dài qua nhiều năm, bạn cũng có thể sửa đổi các công thức và Pivot Table để tính trung bình được nhóm theo từng năm và quý. Tôi sẽ bao gồm những gợi ý này trong hướng dẫn này.
Sử dụng hàm QUERY để tính trung bình theo quý
Đầu tiên, chúng ta cần nhập dữ liệu mẫu vào Google Sheets như sau:
A B
Sales dates Sales amounts
01/01/2023 100
02/01/2023 150
...Tiếp theo, chúng ta sẽ sử dụng hàm QUERY để tính trung bình theo quý. Công thức sẽ như sau:
=QUERY(A1:B, "SELECT QUARTER(A), AVG(B) WHERE YEAR(A) = 2023 GROUP BY QUARTER(A)", 1)
Lưu ý rằng công thức trên phù hợp với cú pháp của hàm QUERY:
QUERY(data, query, [headers])Trong đó:
- data: A1:B
- query: “SELECT QUARTER(A), AVG(B) WHERE YEAR(A) = 2023 GROUP BY QUARTER(A)”
Đây là công thức giúp chúng ta chọn quý của ngày trong cột A và tính trung bình của các giá trị tương ứng trong cột B. Điều kiện WHERE YEAR(A) = 2023 giúp lọc dữ liệu chỉ gồm những dòng có năm trong cột A là 2023. Đồng thời, GROUP BY QUARTER(A) cần thiết để nhóm các giá trị theo từng quý riêng biệt.
Điều chỉnh công thức QUERY để tính trung bình theo năm và quý
Để tính trung bình theo năm và quý, chúng ta cần thực hiện ba thay đổi trong công thức QUERY đã cung cấp ở trên:
- Thay thế điều kiện lọc WHERE YEAR(A) = 2023 bằng WHERE A IS NOT NULL.
- Thay thế SELECT QUARTER(A) bằng SELECT YEAR(A), QUARTER(A).
- Thay thế GROUP BY QUARTER(A) bằng GROUP BY YEAR(A), QUARTER(A).
Công thức điều chỉnh sẽ như sau:
=QUERY(A1:B, "SELECT YEAR(A), QUARTER(A), AVG(B) WHERE A IS NOT NULL GROUP BY YEAR(A), QUARTER(A)", 1)
Sử dụng Pivot Table để tính trung bình theo quý
Trong giai đoạn đầu của Google Sheets, tính năng nhóm ngày trong Pivot Table không khả dụng. Tuy nhiên, sau đó, các nhà phát triển đã bổ sung tính năng này.
Để tính trung bình theo quý bằng Pivot Table, bạn có thể thực hiện các bước sau:
- Nhấp vào “Insert” > “Pivot Table”.
- Nhập “A:B” vào trường “Data range”.
- Chọn “Existing Sheet” và nhập “D1” vào trường bên dưới.
- Nhấp “Create”.
- Trong bảng điều khiển Pivot Table, kéo và thả cột “Date” dưới “Rows”.
- Kéo và thả cột “Sales” dưới “Values” và chọn “AVERAGE” trong phần “Summarize by”.
- Kéo và thả cột “Date” dưới “Filters”. Dưới “Status”, nhấp vào dropdown, chọn “Filter by conditions”, sau đó chọn “Custom formula is” và nhập công thức sau: =YEAR(Date)=2023.
- Nhấp “OK”.
- Chuột phải vào bất kỳ ngày nào trong cột D trong báo cáo Pivot Table. Từ menu ngữ cảnh, chọn “Create pivot date group” sau đó chọn “Quarter”.
Để tính trung bình theo năm và quý, bạn cần thực hiện hai điều chỉnh:
- Chuột phải vào Q1, Q2, Q3, hoặc Q4 trong cột D, sau đó chọn “Create pivot date group” và chọn “Year-Quarter”.
- Nhấp vào biểu tượng bút chì ở góc dưới cùng bên trái của Pivot Table để mở bảng điều khiển bên đầu. Cuộn xuống cuối cùng và trong phần FILTERS, thay thế công thức tùy chỉnh trước đó bằng công thức sau: =DATEVALUE(Date)>0.
Sử dụng hàm AVERAGEIFS để tính trung bình theo quý
Phương pháp này yêu cầu sử dụng một cột trợ giúp, khác với hai phương pháp trước. Trong ô C2, sử dụng công thức sau để gán quý cho các ngày:
=ArrayFormula(IFERROR("Q"&INT((MONTH(DATEVALUE(A2:A))+2)/3)))Công thức dạng mảng này sẽ trả về quý cho tất cả các ngày trong các hàng. Phần INT((MONTH(DATEVALUE(A2:A))+2)/3) trong công thức chuyển đổi tháng thành số quý (1, 2, 3 hoặc 4). DATEVALUE được sử dụng trong công thức để xử lý các ô trống, đảm bảo nó trả về lỗi cho các ô trống. Nếu một ô trống, hàm MONTH sẽ trả về số tháng là 12 và công thức sẽ trả về số quý là 4.
Tiếp theo, chúng ta sẽ tính các quý duy nhất được sắp xếp theo thứ tự tăng dần bằng cách sử dụng công thức sau trong ô E2:
=SORT(UNIQUE(C2:C))Sau đó, chúng ta sẽ sử dụng công thức AVERAGEIFS sau đây trong ô F2:
=ArrayFormula(AVERAGEIFS($B$2:$B, $C$2:$C, E2, YEAR($A$2:$A), 2023))Công thức này sẽ tính trung bình các giá trị trong phạm vi B2:B dựa trên các điều kiện sau:
- $C$2:$C là cột chứa thông tin quý (cột C).
- E2 là giá trị quý duy nhất được chọn.
- YEAR($A$2:$A) là cột chứa các năm (cột A).
- 2023 là năm bạn muốn tính trung bình.
Kéo điều chỉnh xanh (fill handle) ở góc dưới cùng bên phải của ô F2 để điền các ô còn lại với trung bình theo quý.
Chọn phương pháp tốt nhất từ ba lựa chọn
Pivot Table là phương pháp linh hoạt nhất trong ba phương pháp nêu trên, cho phép dễ dàng chuyển đổi giữa các báo cáo tổng hợp theo quý, năm-quý, tháng, năm và quý-năm.
Tôi không khuyến nghị sử dụng AVERAGEIFS vì nó có nhiều bước và yêu cầu thực hiện cẩn thận.
Đối với người mới bắt đầu, Pivot Table là lựa chọn được ưu tiên. Nếu bạn muốn tìm hiểu các hàm, hãy chọn AVERAGEIFS.
Tôi ưa thích QUERY. Hãy chọn QUERY nếu bạn không cần tùy chỉnh tiêu đề kết quả. Tuy nhiên, nếu tùy chỉnh là quan trọng, hãy xem xét khám phá mệnh đề LABEL.
Tài liệu tham khảo
Nếu bạn quan tâm đến việc khám phá thêm về các tính toán dựa trên quý ngoài việc tính trung bình theo quý, hãy xem qua các hướng dẫn sau:
- Group Dates in Pivot Table in Google Sheets (Month, Quarter, and Year)
- Formula to Group Dates by Quarter in Google Sheets
- Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets
- Extract Quarter from a Date in Google Sheets – Formula Options
- Query Quarter Function in Non-Calendar Fiscal Year Data (Google Sheets)
- Convert Dates To Fiscal Quarters in Google Sheets
- Current Quarter and Previous Quarter Calculations in Google Sheets
Vậy là tôi đã hướng dẫn cho các bạn cách tính trung bình theo quý trong Google Sheets bằng ba phương pháp khác nhau. Hy vọng các bạn đã hiểu rõ và có thể áp dụng thành công vào công việc của mình. Nếu có thêm bất kỳ câu hỏi nào, hãy để lại bình luận dưới đây. Chúc các bạn thành công! Crawlan.com
