Xin 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 trượt trong Google Sheets và đồng thời loại bỏ ô trống và căn chỉnh đúng vị trí. Điều này sẽ giúp bạn tính toán chính xác dữ liệu trong một khoảng thời gian nhất định.
Xử lý ô trống trong tính toán trung bình trượt: Hiện rõ vấn đề
Giả sử rằng bạn muốn tính trung bình trượt 3 ngày cho một tập dữ liệu giá cổ phiếu. Cột A chứa ngày tháng và cột B chứa giá cổ phiếu tương ứng. Cột C biểu diễn tính toán trung bình trượt và đồng thời nhấn mạnh vấn đề ô trống.
Trong tình huống này, những ngày trong cột A tuân theo thứ tự liên tiếp, nhưng giá cổ phiếu lịch sử chỉ có sẵn vào các ngày trong tuần, dẫn đến sự xuất hiện của các ô trống trong cột B.
Mặc dù công thức TRUNG BÌNH (AVERAGE) ở ô C9 có phạm vi 3, nhưng nó bỏ qua hai ô trống, dẫn đến sai số trong tính toán trung bình trượt 3 ngày. Ngoài ra, chúng ta muốn loại bỏ kết quả tương ứng với ô trống.
Để minh họa điều này, tôi đã thêm công thức TRUNG BÌNH (AVERAGE) sau vào ô C4, và đã được sao chép xuống:
=AVERAGE(B2:B4)Bạn cũng có thể sử dụng công thức mảng sau trong ô C2:
=MAKEARRAY(ROWS(B2:B), 1, lambda(r, c, IFERROR(AVERAGE(CHOOSEROWS(B2:B, SEQUENCE(3, 1, r-3+1))))))
Bạn có thể tìm hiểu về công thức mảng này trong bài hướng dẫn trước của tôi có tiêu đề “Tính toán trung bình n kỳ di động trong Google Sheets”.
Cả hai công thức này không trả về kết quả chính xác do ô trống trong cột B. Chúng được thiết kế để sử dụng với một phạm vi không chứa ô trống.
Công thức mảng tính trung bình trượt loại bỏ ô trống và căn chỉnh kết quả
Dựa trên ví dụ trên, bạn có thể áp dụng công thức mảng sau vào ô C2:
=MAP(B2:B, LAMBDA(cats, IF(cats="", ,IF(COUNTA(FILTER(B2:cats, B2:cats<>""))<3,, AVERAGE(CHOOSEROWS(FILTER(B2:cats, B2:cats<>""), SEQUENCE(3, 1, -1, -1)))))))Bạn có thể truy cập dữ liệu mẫu và công thức tại đây.
Đây là trung bình trượt 3 ngày loại bỏ ô trống. Để chuyển đổi sang trung bình n ngày, bạn chỉ cần thay thế số 3 (xuất hiện hai lần trong công thức) bằng số 5, ví dụ: từ 3 ngày sang 5 ngày.
Công thức này loại bỏ ô trống trong tính toán trung bình trượt và trả lại kết quả bỏ qua các ô trống.
Công thức này hoàn toàn khác biệt so với công thức mảng trước đó (công thức MAKEARRAY), được mã hóa cho các tập dữ liệu không chứa ô trống.
Hãy khám phá cách công thức này loại bỏ ô trống trong tính toán trung bình trượt, đảm bảo sự đếm chính xác các giá trị trong mỗi khoảng thời gian trượt, và quan sát cách nó bỏ qua các hàng trống.
Giải thích công thức
Hàm MAP trong công thức cung cấp một cách mở rộng cho kết quả công thức không phải là mảng.
Để đạt được trung bình trượt mà loại bỏ ô trống và duy trì căn chỉnh, chúng ta sẽ trước tiên khám phá công thức không phải là mảng, nên nhập vào ô C2.
Sau đó, bạn có thể sao chép dán nó xuống để áp dụng trung bình trượt cho toàn bộ phạm vi. Khi bạn hiểu công thức không phải là mảng này, việc hiểu phần MAP sẽ dễ dàng hơn rất nhiều.
Đây là công thức không phải là mảng trong ô C2:
=IF($B$2:B2="", , IF(COUNTA(FILTER($B$2:B2, $B$2:B2<>""))<3,, AVERAGE(CHOOSEROWS(FILTER($B$2:B2, $B$2:B2<>""), SEQUENCE(3, 1, -1, -1)))))Khi bạn kéo công thức này xuống, phạm vi $B$2:B2 điều chỉnh như sau:
- Trong ô C3:
$B$2:B3 - Trong ô C4:
$B$2:B4 - Trong ô C5:
$B$2:B5 - Trong ô C6:
$B$2:B6 - Trong ô C7:
$B$2:B7 - Trong ô C8:
$B$2:B8 - Trong ô C9:
$B$2:B9 - …
Để hiểu cách hoạt động, hãy xem xét những gì xảy ra trong ô C9.
Trung bình trượt bằng cách loại bỏ ô trống
Hãy phân tích từng phần và xem xét những gì xảy ra trong ô C9.
- Phần 1:
FILTER($B$2:B9, $B$2:B9<>"")
Công thức “CHỌN($B$2:B9, $B$2:B9<>””)” loại bỏ các ô trống, trả về những ô không trống trong phạm vi B2:B9 – tức là giá cổ phiếu trong B2:B9 mà không có ô trống.
- Phần 2:
Vì chúng ta tính trung bình trượt 3 ngày, nên chúng ta cần ba giá trị gần đây nhất trong phạm vi này. Để thực hiện việc này, chúng ta sử dụng hàm CHỌN_HÀNG_CONSOLE. Đây là phần liên quan trong công thức:
CHOOSEROWS(FILTER($B$2:B9, $B$2:B9<>""), SEQUENCE(3, 1, -1, -1))- Phần 3:
Hàm TRUNG BÌNH (AVERAGE) tính trung bình của ba giá trị này:
AVERAGE(CHOOSEROWS(FILTER($B$2:B9, $B$2:B9<>""),SEQUENCE(3, 1, -1, -1)))Đó là lý thuyết của trung bình trượt loại bỏ ô trống.
Công thức trong ô C2 bao gồm hai kiểm tra logic đảm bảo căn chỉnh đúng cho kết quả ở các hàng:
IF(COUNTA(FILTER($B$2:B2, $B$2:B2<>""))<3,
Kiểm tra này kiểm tra xem bộ lọc có ba giá trị không. Nếu nó đánh giá là đúng, công thức sẽ trả về trung bình; nếu không, nó sẽ để ô trống. Kiểm tra logic này loại trừ kết quả ở hai hàng đầu tiên cho trung bình trượt 3 ngày, bốn hàng đầu tiên cho trung bình trượt 5 ngày, và cứ tiếp tục như vậy.
IF($B$2:B2="",
Kiểm tra logic này kiểm tra giá trị cổ phiếu trong cột B. Nếu có giá trị, công thức tính trung bình trượt; nếu không, công thức để ô trống. Kiểm tra này giúp công thức bỏ qua các hàng trống.
Vậy là xong! Bài viết của tôi đến đây đã kết thúc. Cảm ơn bạn đã dành thời gian và quan tâm.
