Hãy cùng tìm hiểu cách tính trung bình cộng chạy trong Google Sheets bằng cách sử dụng công thức Mảng. Chúng ta có thể sử dụng hàm AVERAGE để tính trung bình cộng chạy hoặc, chúng ta có thể nói, trung bình di động (MA). MA là giá trị trung bình được cập nhật liên tục. Xin lưu ý rằng trung bình trượt đơn giản (SMA) là khác biệt.
SMA là trung bình của một tập hợp giá trị đã cho trong n số ngày trong quá khứ. Bởi vì một trung bình cộng chạy cần tính toán lặp lại, chúng ta có thể phải sử dụng nhiều hơn một công thức AVERAGE. Nhưng có các hàm như SUMIF, MMULT và DAVERAGE, mà chúng ta có thể sử dụng để viết công thức mảng trung bình cộng chạy trong Google Sheets.
Cập nhật: Bây giờ chúng ta có thể sử dụng Lambda Helper Functions để viết một công thức mảng trung bình di động trong Google Sheets. Tôi đã thêm giải pháp đó vào bài viết này.
Giới thiệu
Trung bình cộng (trung bình số học) của một tập dữ liệu là một con số duy nhất biểu thị một giá trị trung tâm trong tập dữ liệu này. Chúng ta có thể nhận được giá trị này bằng cách cộng tất cả các số trong tập dữ liệu và chia cho số lượng tất cả các số trong tập dữ liệu.
Giả sử số nhân viên tham gia vào bốn cuộc họp hàng tuần gần đây trong văn phòng của bạn là 5, 4, 4 và 6.
Trung bình = (5 + 4 + 4 + 6) / 4 = 19 / 4 = 4,75.
Chúng ta có thể tính toán nó bằng cách sử dụng công thức AVERAGE dưới đây trong Google Sheets.
=average(5,4,4,6)
Giá trị trung bình này được cho là sẽ thay đổi khi cuộc họp hàng tuần mới được tổ chức vào tuần tới.
Hãy tưởng tượng rằng số nhân viên tham gia lần này là năm.
Trung bình = (5 + 4 + 4 + 6 + 5) / 5 = 24 / 5 = 4,8.
=average(5,4,4,6,5)
Có hay không có ý thức của bạn, bạn đã tham gia vào việc tính trung bình cộng chạy, và bây giờ chúng ta hãy xem làm thế nào để thực hiện điều đó trong Google Sheets.
Cách tính trung bình cộng chạy trong Google Sheets
Chúng ta có thể sử dụng hai loại công thức để tính trung bình cộng chạy trong Google Sheets. Công thức đơn giản nhất là công thức không sử dụng mảng.
1. Công thức không sử dụng mảng
Dựa trên tập dữ liệu mẫu sau đây, các ngày của các cuộc họp hàng tuần nằm ở cột A và số người tham dự nằm ở cột B.
Để tính trung bình cộng chạy trong Google Sheets, chúng ta có thể sử dụng công thức dưới đây trong ô C2 và kéo xuống bằng cách kéo thả cánh tay điền vào như được hiển thị dưới đây.
=average($B$2:B2)
Công thức sẽ bỏ qua bất kỳ ô trống nào trong phạm vi B2: B8 nhưng sẽ tính số 0 (số không).
2. Trung bình chạy (trung bình di động) sử dụng công thức mảng trong Google Sheets
Tôi có bốn ví dụ về công thức mảng trung bình chạy trong Google Sheets. Bạn sẽ không thấy ví dụ thứ tư trên ảnh chụp màn hình. Đó là công thức Lambda, tôi đã thêm sau đó.
Trong một tập dữ liệu nhỏ, tất cả chúng đều hoạt động tốt. Nhưng công thức mảng trung bình chạy đầu tiên và thứ tư có khả năng xử lý tốt hơn với các tập dữ liệu lớn. Tùy thuộc vào bạn chọn cái nào.
Chú ý: Tôi đã mở phạm vi trong tất cả các công thức mảng trung bình chạy. Hãy đóng nó lại, ví dụ: B2: B đến B2:B100, để cải thiện hiệu suất của chúng.
SUMIF – Công thức mảng trung bình MA 1
=ArrayFormula( if(B2:B="",, sumif(row(B2:B),"<="&row(B2:B),B2:B)/ifna(vlookup(row(B2:B),{filter(row(B2:B),B2:B<>""); sequence(counta(B2:B),1)},2,0)) ) )
MMULT – Công thức mảng trung bình MA 2
=ArrayFormula( if(B2:B="",, MMULT(IF(ROW(B2:B)>=TRANSPOSE(ROW(B2:B))=TRUE,1,0),n(B2:B))/ifna(vlookup(row(B2:B),{filter(row(B2:B),B2:B<>""); sequence(counta(B2:B),1)},2,0)) ) )
DAVERAGE – Công thức mảng trung bình MA 3
=ArrayFormula( if(B2:B="",, DAVERAGE( transpose({B2:B,TRANSPOSE(if(ROW(B2:B) <= TRANSPOSE(ROW(B2:B)),B2:B,))}), sequence(rows(B2:B),1), {if(,,);if(,,)} ) ))
BYROW LHF – Công thức mảng trung bình MA 4 (Mới!)
=byrow(B2:B,lambda(r,if(r="",,average(filter(B2:B,row(B2:B)<=row(r))))))
Giải thích công thức
Đối với công thức mảng trung bình di động 1 (SUMIF) và 2 (MMULT), có hai phần chính.
Phần 1:
Phần táo bạo (phần 1) trong các công thức trả về tổng tích lũy của mỗi điểm dữ liệu trong phạm vi B2: B.
Bạn có thể tìm thấy chi tiết trong tiêu đề phụ “Công thức Tổng số chạy dựa trên Mảng trong Google Sheets” trong hướng dẫn của tôi có tiêu đề Công thức Tổng số Chạy Thông Thường và Dựa Trên Mảng trong Google Sheets.
Phần 2:
Phần thứ hai, chuỗi sau phần táo bạo phần 1, trả về các số từ 1 đến ‘n’ và bỏ qua các ô trống như được mô tả ở đây – Bỏ qua các hàng trống trong đánh số tuần tự trong Google Sheets.
Trong quá trình lập trình, tôi tuân theo cú pháp dưới đây.
phần_1 / phần_2 = trung_bình_cộng_chạy
Vậy còn công thức mảng 3 (DAVERAGE)?
Công thức mảng trung bình chạy DAVERAGE là một ví dụ về tính linh hoạt của Google Sheets.
Trước đây, chúng ta đã sử dụng các chức năng cơ sở dữ liệu DMIN và DMAX để trả về min và max chạy trong Google Sheets.
Tôi đã sử dụng một trong số các công thức đó ở đây và thay thế DMIN/DMAX bằng DAVERAGE.
Chờ đã! Tôi cũng đã thực hiện một thay đổi khác không bắt buộc nào.
Đó là gì?
Phần táo bạo trong công thức DAVERAGE trên cho trung bình chạy trả về các mẫu 1-12-123-1234.
Tôi đã sử dụng một công thức khác để tạo ra mẫu đó trong hai hướng dẫn DMIN/DMAX đã đề cập ở trên.
Lambda (Công thức mảng 4) trong trung bình chạy:
Bộ lọc sau sẽ trả về giá trị đầu tiên trong hàng đầu tiên.
=filter(B2:B,row(B2:B)<=row(B2))
Chúng ta phải sử dụng công thức sau để có giá trị đầu tiên và thứ hai.
=filter(B2:B,row(B2:B)<=row(B3))
Sử dụng Lambda BYROW, chúng ta tăng B2 lên B3, B4, B5, và cứ thế trong mỗi hàng.
AVERAGE trả về trung bình của chúng trong mỗi hàng.
Mẫu_số_11221Tài nguyên liên quan
- Trung bình phần trăm trên giá trị trong Google Sheets.
- Công thức mảng trung bình qua các hàng trong Google Sheets.
- Công thức mảng trả về trung bình của mỗi N ô trong Google Sheets.
- Cách tính trung bình di động đơn giản trong Google Sheets.
- Trung bình di động có trọng số trong Google Sheets (Công thức tùy chọn).
- Trung bình theo tháng trong Google Sheets (Công thức tùy chọn).
- Trung bình mỗi hàng trong phạm vi động trong Google Sheets.
Chúng ta vừa tìm hiểu cách tính trung bình cộng chạy trong Google Sheets bằng cách sử dụng công thức mảng. Hãy áp dụng các công thức này vào công việc của bạn và tận hưởng tiện ích của tính năng này!
Nguồn: Crawlan.com
