Tóm lược
Để tính toán đường trung bình động hoặc lăn bánh, bạn có thể sử dụng một công thức đơn giản dựa trên hàm AVERAGE với các tham chiếu tương đối. Trong ví dụ được hiển thị, công thức trong E7 là:
=AVERAGE(C5:C7)
Khi công thức được sao chép xuống, nó sẽ tính toán đường trung bình động trong 3 ngày dựa trên giá trị bán hàng cho ngày hiện tại và hai ngày trước đó.
Dưới đây là một tùy chọn linh hoạt hơn dựa trên hàm OFFSET xử lý các khoảng thời gian thay đổi.
Giới thiệu về đường trung bình động
Đường trung bình động (còn được gọi là đường trung bình luân phiên) là mức trung bình dựa trên các tập hợp con dữ liệu tại các khoảng thời gian nhất định. Tính toán trung bình tại các khoảng thời gian cụ thể giúp làm mịn dữ liệu bằng cách giảm tác động của các biến động ngẫu nhiên. Điều này giúp bạn dễ dàng xem các xu hướng tổng thể hơn, đặc biệt là trong biểu đồ. Khoảng thời gian được sử dụng để tính toán trung bình động càng lớn, thì quá trình diễn ra càng trơn tru hơn, vì nhiều điểm dữ liệu hơn được bao gồm trong mỗi mức trung bình được tính toán.
Giải trình
Các công thức hiển thị trong ví dụ đều sử dụng hàm AVERAGE với tham chiếu tương đối được thiết lập cho từng khoảng thời gian cụ thể. Đường trung bình động 3 ngày trong E7 được tính bằng cách cung cấp cho AVERAGE một phạm vi bao gồm ngày hiện tại và hai ngày trước đó như sau:
=AVERAGE(C5:C7) // 3-day average
Mức trung bình 5 ngày và 7 ngày được tính theo cùng một cách. Trong mỗi trường hợp, phạm vi cung cấp cho AVERAGE được mở rộng để bao gồm số ngày cần thiết:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Tất cả các công thức sử dụng tham chiếu tương đối cho phạm vi được cung cấp cho hàm AVERAGE. Khi các công thức được sao chép xuống cột, phạm vi thay đổi ở mỗi hàng để bao gồm các giá trị cần thiết cho mỗi giá trị trung bình.
Khi các giá trị được vẽ trong biểu đồ đường, hiệu ứng làm mịn là rõ ràng:
Dữ liệu không đầy đủ
Nếu bạn bắt đầu công thức ở hàng đầu tiên của bảng, một vài công thức đầu tiên sẽ không có đủ dữ liệu để tính giá trị trung bình hoàn chỉnh, vì phạm vi sẽ mở rộng phía trên hàng dữ liệu đầu tiên:
Điều này có thể là một vấn đề hoặc không, tùy thuộc vào cấu trúc của trang tính và liệu điều quan trọng là tất cả các giá trị trung bình đều dựa trên cùng một số giá trị. Hàm AVERAGE sẽ tự động bỏ qua các giá trị văn bản và các ô trống, vì vậy nó sẽ tiếp tục tính giá trị trung bình với ít giá trị hơn. Đây là lý do tại sao nó "hoạt động" trong E5 và E6.
Một cách để chỉ rõ không đủ dữ liệu là kiểm tra số hàng hiện tại và hủy bỏ bằng #NA khi có ít hơn n giá trị. Ví dụ: đối với mức trung bình trong 3 ngày, bạn có thể sử dụng:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Phần đầu tiên của công thức chỉ tạo một số hàng "chuẩn hóa", bắt đầu bằng 1:
ROW()-ROW($C$5)+1 // relative row number
Ở hàng 5, kết quả là 1, ở hàng 6 kết quả là 2, v.v.
Khi số hàng hiện tại nhỏ hơn 3, công thức trả về # N / A. Nếu không, công thức trả về giá trị trung bình động như trước đây. Điều này bắt chước hành vi của phiên bản Analysis Toolpak của Đường trung bình động, xuất ra # N / A cho đến khi đạt đến chu kỳ hoàn chỉnh đầu tiên.
Tuy nhiên, khi số khoảng thời gian tăng lên, cuối cùng bạn sẽ dùng hết các hàng phía trên dữ liệu và không thể nhập phạm vi bắt buộc bên trong AVERAGE. Ví dụ: bạn không thể thiết lập mức trung bình động trong 7 ngày với trang tính như được hiển thị vì bạn không thể nhập phạm vi kéo dài 6 hàng trên C5.
Các khoảng thời gian thay đổi với OFFSET
Một cách linh hoạt hơn để tính toán đường trung bình động là với hàm OFFSET. OFFSET có thể tạo một phạm vi động, có nghĩa là chúng ta có thể thiết lập một công thức trong đó số chu kỳ có thể thay đổi. Hình thức chung là:
=AVERAGE(OFFSET(A1,0,0,-n,1))
trong đó n là số kỳ cần bao gồm trong mỗi giá trị trung bình. Như trên, hàm OFFSET trả về một dải ô được chuyển vào hàm AVERAGE. Dưới đây, bạn có thể thấy công thức này đang hoạt động, trong đó "n" là phạm vi được đặt tên E2. Bắt đầu từ ô C5, OFFSET tạo một phạm vi mở rộng trở lại các hàng trước đó. Điều này được thực hiện bằng cách sử dụng chiều cao bằng n âm. Khi E5 được thay đổi thành một số khác, đường trung bình động sẽ tính lại trên tất cả các hàng:
Công thức trong E5, được sao chép xuống, là:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Giống như công thức gốc ở trên, phiên bản có OFFSET cũng sẽ gặp vấn đề không đủ dữ liệu trong một vài hàng đầu tiên, tùy thuộc vào số khoảng thời gian được đưa ra trong E5.
Trong ví dụ được hiển thị, các giá trị trung bình được tính toán thành công vì hàm AVERAGE tự động bỏ qua các giá trị văn bản và ô trống và không có giá trị số nào khác trên C5. Vì vậy, trong khi phạm vi được chuyển vào AVERAGE trong E5 là C1: C5, chỉ có một giá trị là trung bình, 100. Tuy nhiên, khi các khoảng thời gian tăng lên, OFFSET sẽ tiếp tục tạo một phạm vi mở rộng phía trên đầu dữ liệu, cuối cùng chạy vào đầu trang tính và trả về lỗi #REF.
Một giải pháp là "giới hạn" kích thước của phạm vi với số điểm dữ liệu có sẵn. Điều này có thể được thực hiện bằng cách sử dụng hàm MIN để hạn chế số lượng được sử dụng cho chiều cao như hình dưới đây:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Điều này trông khá đáng sợ, nhưng thực ra lại khá đơn giản. Chúng tôi đang giới hạn chiều cao được truyền vào OFFSET với hàm MIN:
MIN(ROW()-ROW($C$5)+1,n)
Bên trong MIN, giá trị đầu tiên là một số hàng tương đối, được tính bằng:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Giá trị thứ hai được trao cho MIN là số chu kỳ, n. Khi số hàng tương đối nhỏ hơn n, MIN trả về số hàng hiện tại thành OFFSET cho chiều cao. Khi số hàng lớn hơn n, MIN trả về n. Nói cách khác, MIN chỉ đơn giản trả về giá trị nhỏ hơn trong hai giá trị.
Một tính năng hay của tùy chọn OFFSET là n có thể dễ dàng thay đổi. Nếu chúng ta thay đổi n thành 7 và vẽ biểu đồ kết quả, chúng ta sẽ có một biểu đồ như sau:
Lưu ý: Một điều khác biệt với các công thức OFFSET ở trên là chúng sẽ không hoạt động trong Google Trang tính, bởi vì hàm OFFSET trong Trang tính sẽ không cho phép giá trị âm cho chiều cao hoặc chiều rộng. Bảng tính đính kèm có các công thức giải pháp thay thế cho Google trang tính.
