Excel

Công thức trung bình động

Moving Average Formula

Công thức Excel: Công thức trung bình độngTóm lược

Để tính toán đường trung bình di chuyển 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 tài liệu tham khảo tương đối. Trong ví dụ được hiển thị, công thức trong E7 là:



cách thêm một chuỗi trong excel
 
= AVERAGE (C5:C7)

Khi công thức được sao chép xuống, nó sẽ tính toán mức 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 linh hoạt hơn tùy chọn dựa trên Chức năng 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 đường trung bình càng lớn, thì quá trình diễn ra càng trơn tru hơn, vì có nhiều điểm dữ liệu hơn được bao gồm trong mỗi điểm trung bình được tính toán.

Giải trình

Tất cả 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 của 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 bắt buộc:

 
= AVERAGE (C5:C7) // 5-day average = AVERAGE (C5:C11) // 7-day average

Tất cả các công thức sử dụng một tài liệu tham khảo tương đối cho phạm vi được cung cấp cho chức năng 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:

Ví dụ về biểu đồ trung bình động

Dữ liệu không đầy đủ

Nếu bạn bắt đầu các công thức trong đầu tiên hàng của bảng, một số 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 bên trên hàng đầu tiên của dữ liệu:

Vấn đề về phạm vi trung bình động

Đâ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)+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)+1 // relative row number

Trong hàng 5, kết quả là 1, trong 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 giá trị # N / A cho đến khi đạt đến chu kỳ hoàn chỉnh đầu tiên.

Trung bình động với # n / a vì không đủ dữ liệu

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à sẽ 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ố khoảng thời gian có thể thay đổi. Hình thức chung là:

 
= AVERAGE ( OFFSET (A1,0,0,-n,1))

trong đó n là số thời kỳ cần bao gồm trong mỗi giá trị trung bình. Như trên, 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 xây dựng 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ách tìm kiếm trong bảng tính excel

Đường trung bình động với hàm OFFSET

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.

nếu ngày ở giữa hai ngày

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 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 bằng 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ư được thấy bên dưới:

Trung bình động với chức năng OFFSET và phạm vi giới hạn

 
= AVERAGE ( OFFSET (C5,0,0,-( MIN ( ROW ()- ROW ($C)+1,n)),1))

Điều này trông khá đáng sợ, nhưng thực ra 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)+1,n)

Bên trong MIN, giá trị đầu tiên là số hàng tương đối , được tính toán với:

 
 ROW ()- ROW ($C)+1 // relative row number..1,2,3, etc.

Giá trị thứ hai 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:

Biểu đồ trung bình động với chức năng OFFSET

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, 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.

Tệp đính kèm Tập tin trung bình động exceljet.xlsx Tác giả Dave Bruns


^