Cách tính trung bình động trong Excel

Trong bài viết này, Học Excel sẽ hướng dẫn các bạn cách tính nhanh trung bình động đơn giản trong Excel, sử dụng hàm để tính đường trung bình trong N ngày/tuần/tháng/năm trước, và cách thêm đường trung bình động vào biểu đồ Excel.

Trung bình động là gì?

Nhìn chung, trung bình động được định nghĩa là một chuỗi các giá trị trung bình từ các tập hợp giá trị khác nhau trong cùng một bộ dữ liệu.

Trung bình động thường được sử dụng trong thống kê, dự báo chu kỳ thay đổi kinh tế và dự báo thời tiết để hiểu được xu hướng của chúng. Trong giao dịch chứng khoán, trung bình động là một chỉ số thể hiện giá trị trung bình của chứng khoán qua các thời kỳ. Trong kinh doanh, đó là một nghiệp vụ tính trung bình doanh thu của 3 tháng trước để dự đoán xu hướng gần đây.

Ví dụ, trung bình động của nhiệt độ trong ba tháng có thể tính bằng cách tính nhiệt độ trung bình của tháng 1 đến tháng 3, sau đó tính nhiệt độ trung bình của tháng 2 tới tháng 4, rồi tháng 3 tới tháng 5…

Có nhiều kiểu trung bình động khác nhau như trung bình động giản đơn, trung bình động mũ, trung bình động biến thiên, trung bình động ba bên và trung bình động gia quyền. Trong bài viết này, chúng ta sẽ tập trung vào loại hay được sử dụng nhất, đó là trung bình động giản đơn.

Cách tính trung bình động giản đơn trong Excel:

Có 2 cách tính trung bình động giản đơn trong Excel – bằng công thức và bằng tùy chọn khuynh hướng. Các ví dụ sau đây sẽ minh họa cho cả hai kỹ thuật này.

Ví dụ 1. Tính trung bình động qua một thời kỳ cụ thể

Trung bình động giản đơn có thể được tính bằng hàm AVERAGE. Giả sử bạn có một danh sách trung bình nhiệt độ hàng tháng trong cột B, và bạn muốn tìm trung bình động cho ba tháng (như hình trên)

Viết công thức AVERAGE bình thường cho 3 giá tri đầu tiên và nhập nó vào ô thứ 3 từ trên đếm xuống (ví dụ ô C4), sau đó sao chép công thức sang các ô khác trong cột: =AVERAGE(B2:B4)

Bạn có thể cố định các ô (như ô B2) nếu bạn muốn, nhưng cũng hãy sử dụng các tham chiếu hàng không cố định để công thức được điều chỉnh phù hợp cho các ô khác nhau.

Hãy nhớ rằng trung bình cộng được tính bằng cách tính tổng các giá trị sau đó chia cho số các giá trị được tính trung bình, bạn có thể xác nhận kết quả bằng công thức SUM: =SUM(B2:B4)/3

Xem thêm: 3 cách tính trung bình trên Excel

Ví dụ 2. Tính trung bình động cho N ngày/tuần/tháng/năm cuối trong một cột

Giả sử bạn có một danh sách các dữ liệu, cụ thể là doanh số bán hàng hay giá cổ phiếu, và bạn muốn biết trung bình của ba tháng cuối tại một thời điểm bất kỳ. Để thực hiện điều này, bạn cần một công thức tính toán lại trung bình ngay sau khi nhập vào giá trị cho tháng tiếp theo. Hàm AVERAGE lồng ghép với hàm OFFSET và COUNT.

=AVERAGE(OFFSET(first cell, COUNT(entire range)-N,0,N,1))

N là số ngày/tuần/tháng/năm trước.

Giả sử các giá trị trung bình được tính từ hàng 2 cột B, công thức sẽ như sau: =AVERAGE(OFFSET(B2,COUNT(B2:B100)-3,0,3,1))

Và bây giờ, tôi sẽ giải thích các thành phần của công thức này rõ hơn:

• Hàm COUNT “COUNT(B2:B100)” đếm có bao nhiêu giá trị được nhập vào cột B. Chúng ta bắt đầu đếm từ ô B2 vì ô B1 là tiêu đề.
• Hàm OFFSET lấy giá trị ở ô B2 (tham số thứ nhất) là ô bắt đầu, sau đó tịnh tiến theo giá trị được trả về bởi hàm COUNT 3 dòng lên phía trên (trong trường hợp này: -3 – tham số thứ 2 của hàm offset). Kết quả là, công thức tính tổng của 3 giá trị trong vùng gồm 3 dòng (3 – ở trong tham số thứ 3 của hàm offset) và 1 cột (1 – tham số cuối cùng của hàm OFFSET), kết quả này chính là tổng của 3 tháng cuối cùng mà chúng ta muốn
• Cuối cùng, kết quả tổng được hàm AVERAGE sử dụng để tính trung bình động.

Chú ý.  Nếu bạn làm việc với trang tính luôn cập nhật các hàng mới, hãy đảm bảo hàm COUNT có đủ số hàng để chứa dữ liệu mới. đó không phải là vấn đề bạn chèn nhiều cột hơn bình thường ngay sau khi bạn có ô tính đầu tiên, bởi thế nào hàm COUNT cũng loại bỏ tất cả các hàng rỗng

Trong ví dụ, bảng dữ liệu chỉ chứa dữ liệu trong 12 tháng, nhưng chúng ta đã trừ hao cho hàm COUNT vùng dữ liệu B2:B100.

Ví dụ 3. Tính trung bình động cho N giá trị cuối trong một hàng

Nếu bạn muốn tính trung bình động cho N ngày/tháng/năm cuối trong cùng một hàng, bạn chỉ cần điều chỉnh công thức OFFSET như sau:

=AVERAGE(OFFSET(first cell,0,COUNT(range)-N,1,N,))

Giả sử ô B2 chứa số liệu đầu tiên trong hàng, và bạn muốn tính trung bình động cho 3 số liệu cuối hàng, công thức sẽ như thế này:

=AVERAGE(OFFSET(B2,0,COUNT(B2:N2)-3,1,3))

Học Excel tại nhà với các chuyên gia

Tạo đường trung bình động trong biểu đồ Excel

Nếu bạn đã tạo một biểu đồ, việc thêm đường trung bình động cho biểu đồ rất nhanh chóng. Chúng tôi sẽ hướng dẫn bạn sử dụng tính năng Excel Trendline theo các bước sau đây.

Trong ví dụ này, chúng tôi tạo biểu đồ doanh số bán hàng 2D (thẻ Insert > Charts)

Sau đây, chúng tôi sẽ vẽ biểu đồ trung bình động trong 3 tháng.

1. Trong Excel 2013, chọn biểu đồ, đến thẻ Design, đển nhóm Chart Layouts > Add Chart Element > Trendline > More Trendline Options…

Trong Excel 2010 và 2007, đến Layout > Trendline > More Trendline Options…

Chú ý. Nếu bạn không cần các chi tiết như khoảng thời gian hoặc tên biểu đồ, bạn có thể nhấp vào Design > Add Chart Element > Trendline > Moving Average để có kết quả ngay lập tức.

1. Bảng Format Trendline hiển thị phía bên tay phải trang tính 2013 và hộp thoại tương ứng sẽ xuất hiện trong Excel 2010 và 2007.

Trên bảng Format Trendline, nhấp vào biểu tượng Trendline Options¸chọn Moving Average và nhập khoảng thời gian vào hộp Period:

1. Đóng bảng Trendline, đường trung bình động sẽ được thêm vào biểu đồ như sau:

Để chỉnh sửa biểu đồ của mình, mở thẻ Fill & Line hoặc Effects trong bảng Format Trendline và điều chỉnh các tùy chọn khác nhau như kiểu đường viền, màu, độ rộng…

Để phân tích sữ liệu tốt hơn, bạn có thể muốn thêm một vài đường trung bình động của nhiều thời kỳ khác nhau để so sánh các xu hướng.. Hình minh họa sau thể hiện đường trung bình động 2 tháng (màu xanh lá) và đường 3 tháng (màu đỏ)

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.

Xem ngay: Tài liệu Excel cơ bản