Trong bài viết hướng dẫn tính tổng theo tháng của tôi, một trong số độc giả của tôi đã hỏi về công thức tính trung bình theo tháng trong Google Sheets. Vì vậy, tôi đang viết bài này như là câu trả lời cho anh ta. Bài viết này cũng hy vọng sẽ giúp các độc giả khác muốn tìm hiểu những điều mới trong Google Sheets.
Để tính trung bình theo tháng trong Google Sheets, chúng ta có thể sử dụng AVERAGEIF, FILTER + AVERAGE và QUERY.
Average theo Tháng trong Google Sheets (Công thức không phải mảng)
Như tôi đã đề cập ở trên, có hai loại công thức không phải mảng mà bạn sẽ tìm thấy bên dưới. Cả hai công thức đều cho mục đích khác nhau.
Bạn có thể tìm hiểu thêm thông tin chi tiết về kết quả công thức bên dưới.
Công thức Averageif
Trong dãy ô B2: B7, nhập các ngày 25/11/2020, 26/11/2020, 1/12/2020, 6/12/2020 và 21/12/2020.
Trong cột tiếp theo ở các ô tương ứng, tức là trong dãy ô C2: C7, nhập các số 5, 4, 5, 6, 5 và 11.
Chúng ta có thể sử dụng hàm AVERAGEIF như dưới đây để trả về giá trị trung bình theo tháng trong Google Sheets.
Để tính trung bình cho tháng 11, chúng ta có thể sử dụng công thức dưới đây.
=ArrayFormula(IFERROR(averageif(month($B$2:$B),11,$C$2:$C)))
Mặc dù không phải là công thức mảng, nhưng chúng ta đã sử dụng hàm ArrayFormula vì chúng ta đã sử dụng hàm MONTH trong một phạm vi trong công thức.
Đối với trung bình của tháng 12, thay thế tiêu chí 11 trong công thức bằng 12.
ArrayFormula(IFERROR(averageif(month($B$2:$B),12,$C$2:$C)))
Trong việc sử dụng thực tế, bạn có thể chỉ định các tiêu chí trong dải E2: E13 như bên dưới, sau đó sử dụng công thức dưới đây trong ô F2, rồi kéo xuống.
ArrayFormula(IFERROR(averageif(month($B$2:$B),E2,$C$2:$C)))
Mặc dù công thức trên dễ đọc ngay cả với người mới học, nhưng nó có một nhược điểm!
Ở trên có gì?
Trong công thức AVERAGEIF ở trên, chúng ta không thể bao gồm phần năm.
Ý nghĩa là, nếu dữ liệu của bạn có giá trị trong tháng 1 năm 2020 và tháng 1 năm 2021, công thức trên chỉ xem xét phần tháng.
Lý do, công thức AVERAGEIF chỉ hỗ trợ một tiêu chí duy nhất. Vì vậy, chúng ta không thể bao gồm phần năm trong công thức.
Vậy chúng ta có thể sử dụng AVERAGEIFS không?
Không! Bởi vì tiêu chí tháng và năm nằm ở cùng một cột ngày. Theo kiến thức của tôi, AVERAGEIFS không hỗ trợ điều này.
Nếu bạn muốn bao gồm tháng và năm trong phép tính trung bình theo tháng trong Google Sheets, hãy sử dụng Tìm kiếm + Kết hợp trung bình như bên dưới.
Trung bình theo Tháng và Năm bằng cách Sử dụng Tìm kiếm và Trung bình.
Tôi sẽ sửa dữ liệu để chứa giá trị trong nhiều năm.
Trước đây, dữ liệu nằm trong B2: C6. Phạm vi dữ liệu mới là B2: C12.
Đây là các bước để viết công thức trả về giá trị trung bình theo tháng và năm trong Google Sheets.
Hãy trước tiên xem xét số tháng 2 (Tháng Hai) trong ô E2 và năm 2020 trong ô F2.
Sử dụng Filter, chúng ta nên lấy trước tiên phạm vi C2: C được lọc trong phạm vi tháng của Tháng Hai 2020 trong dải ô B2: B.
=Filter($C$2:$C,month($B$2:$B)=E2,year($B$2:$B)=F2)
Công thức Filter trên lọc các giá trị theo tiêu chí đã cho.
Đặt nó trong hàm AVERAGE như bên dưới.
=average(filter($C$2:$C,month($B$2:$B)=E2,year($B$2:$B)=F2))
Chúng ta có thể sử dụng công thức Filter + Average trên để trả lại giá trị trung bình theo tháng (Tháng Hai) và Năm (2020) trong ô G2.
Kéo công thức này xuống G5, và voila!
Nếu bạn đang tìm kiếm một công thức mảng để tính trung bình theo tháng và năm trong Google Sheets, không có nghi ngờ gì nữa, chức năng Query không có thay thế.
Như bạn có thể thích: Làm thế nào để Sum, Avg, Count, Max và Min trong Google Sheets Query.
Nhưng nhiều người dùng Google Sheets dường như do dự sử dụng truy vấn vì họ có thể nghĩ rằng nó hơi phức tạp. Ngược lại, từ kinh nghiệm của riêng mình, tôi thấy Query rất dễ sử dụng và đọc.
Để tính trung bình theo tháng và năm, chúng ta có thể sử dụng công thức Query ‘không được định dạng’ bên dưới.
=query({B2:C},”Select month(Col1)+1,year(Col1),avg(Col2) where Col1 is not null group by month(Col1)+1,year(Col1) order by year(Col1)”,0)
Tôi gọi nó là ‘không được định dạng’ vì tôi chưa tùy chỉnh nhãn mặc định được trả về bởi Query trong hàng đầu tiên của kết quả. Hãy xem hình ảnh bên dưới (E2: G2).
Giải thích Công thức:
Hãy để tôi giải thích các phần của công thức Truy vấn để bạn có thể dễ dàng đọc và sử dụng chúng.
Mục CLAUSE SELECT:-
Select month (Col1) + 1,year (Col1),Phần công thức Truy vấn này trả về các giá trị trong các cột kết quả E (tháng) và F (năm).
Tại sao tôi sử dụng month (Col1) + 1 thay vì sử dụng month (Col1) để trả về số tháng?
Đó là một câu hỏi tự nhiên mà bất kỳ độc giả nào cũng có thể đặt. Lý do là số tháng được trả về bởi Truy vấn là 0-11, không phải là 1-12.
Phần tự động hóa AVG:
AVG Aggregation Part:
avg (Col2)
Tất nhiên, nó là thực hiện phần tổng trung bình trong cột thứ hai trong dải số.
Mục CLAUSE WHERE:
where Col1 is not null
Chúng ta đã sử dụng dải không giới hạn B2: C, không phải là dải hữu hạn B2: C12, trong Truy vấn trung bình theo tháng và năm bên trên. Vì vậy, sẽ có nhiều hàng trống.
Công thức trên loại trừ các hàng trống trong phạm vi.
Mục CLAUSE GROUP BY:
group by month (Col1) + 1,year (Col1)
Chúng ta muốn nhóm theo tháng và năm.
Mục CLAUSE ORDER BY:
order by year (Col1)
Để sắp xếp theo năm.
Nếu bạn muốn, bạn có thể bao gồm MỆNH ĐỀ LABEL để sửa đổi các nhãn trong kết quả.
Ví dụ:
=query({B2:C},”Select month(Col1)+1,year(Col1),avg(Col2) where Col1 is not null group by month(Col1)+1,year(Col1) order by year(Col1) label month(Col1)+1’Tháng'”,0)
Trung bình theo Tháng trên Tên Tháng trong Google Sheets (Truy vấn)
Trong tất cả các ví dụ trên, công thức trả về số tháng, không phải văn bản tên tháng.
Nhiều người trong số bạn có thể muốn lấy tên tháng thay vì số tháng trong kết quả công thức trung bình theo tháng và năm trong Google Sheets.
Chúng ta có thể làm điều đó bằng cách sử dụng một thủ thuật Truy vấn đơn giản. Đây là logic.
Chúng ta sẽ chuyển đổi các ngày thành các ngày cuối tháng. Vì vậy, thay vì nhóm các tháng, chúng ta có thể nhóm cột ngày trực tiếp.
Điều đó tránh việc sử dụng hàm month trong Truy vấn.
Xem Truy vấn bên dưới (chỉ đọc cho đến công thức tiếp theo).
=Query({ArrayFormula(if(B2:B=””,,eomonth(B2:B,0))),C2:C},”Select Col1,avg(Col2) where Col1 is not null group by Col1″,0)
Ghi chú: Nếu bạn sử dụng công thức trên, bạn sẽ thấy các giá trị ngày trong ô E3: E6, không phải là ngày, như được hiển thị trong hình ảnh. Tôi đã định dạng các giá trị ngày thành ngày từ menu Định dạng > Số > Ngày. Điều này nhằm giúp bạn hiểu mục đích của việc chuyển đổi cuối tháng.
Chúng ta có thể sử dụng đoạn mã FORMAT trong Truy vấn trên để định dạng các giá trị cột 1 (E3: E6) thành tháng và năm như bên dưới.
=Query({ArrayFormula(if(B2:B=””,,eomonth(B2:B,0))),C2:C},”Select Col1,avg(Col2) where Col1 is not null group by Col1 format Col1’mmmm-yyyy'”,0)
Tôi hy vọng bạn hiểu cách tính trung bình theo tháng trong Google Sheets.
Cảm ơn bạn đã theo dõi, hãy thưởng thức!
Tài nguyên:
- Xem trung bình của hàng hiển thị trong Google Sheets.
- Xem trung bình của N giá trị cuối cùng trong Google Sheets.
- Đường trung bình trong biểu đồ trong Google Sheets – Đường và Cột.
- Công thức mảng trung bình qua các hàng trong Google Sheets.
- Làm thế nào để sử dụng Regexmatch trong Averageif trong Google Sheets.
