Trong đời học đường, bạn đã học về trung bình và cách tính. Công thức tính trung bình rất đơn giản: cộng tất cả giá trị trong dữ liệu và chia cho tổng số giá trị. Trung bình động (Moving average) là loại trung bình khác có ứng dụng thực tế. Nó áp dụng trong nhiều lĩnh vực như chứng khoán, bán hàng, v.v.
Trung bình động có 3 loại: Đơn giản (Simple), Trọng số (Weighted), và Mũ (Exponential). Bài viết này giải thích cách tính cả 3 loại trong Excel.
Đọc thêm: Cách dùng hàm MIN, MAX, và AVERAGE trong Excel.
Cách tính Trung bình động trong Excel
Chúng ta sẽ tính trung bình động bằng 2 phương pháp. Phương pháp 1 dùng công cụ tích hợp sẵn của Excel, phương pháp 2 dùng công thức.
1. Sử dụng Bộ công cụ Phân tích dữ liệu (Data Analysis ToolPak)
Phương pháp này dùng Bộ công cụ Phân tích dữ liệu trong Excel để tính trung bình động đơn giản. Excel có sẵn công cụ tính SMA. Data Analysis ToolPak là tiện ích bổ sung (add-in), có thể không có sẵn. Dưới đây là các bước cài đặt:
- Nhấn vào "Tệp" (File).
- Chọn "Tùy chọn" (Options).
- Mở hộp thoại, nhấn "Tiện ích bổ sung" (Add-Ins).
- Trên bảng điều khiển bên phải, menu thả xuống ở dưới cùng. Chọn "Tiện ích bổ sung Excel" (Excel Add-ins) và nhấn "Đi" (Go).
- Chọn "Analysis ToolPak" và nhấn "OK".
- Các bước trên cài đặt công cụ Phân tích dữ liệu. Xem trong tab "Dữ liệu" (Data).
Đọc thêm: Cách tính phần trăm chênh lệch giữa hai số trong Excel.
Bây giờ sẵn sàng tính SMA. Để minh họa, tạo dữ liệu mẫu nhiệt độ biến đổi 10 ngày đầu tháng.
Làm theo các bước sau để tính SMA dùng Data Analysis ToolPak:
- Nhấn tab "Dữ liệu" (Data) chọn "Phân tích dữ liệu" (Data Analysis) (xem bước 6).
- Hộp thoại nhỏ mở ra, chọn "Trung bình động" (Moving Average) và nhấn OK. Đây là SMA đơn giản.
- Nhập khoảng dữ liệu đầu vào (Input range). Với dữ liệu mẫu, dữ liệu từ ô B2 đến B11, nên nhập B2:B11. Sau đó nhập "Khoảng" (Interval). Trong khoảng đầu ra (Output range), nhập địa chỉ ô muốn kết quả. Chọn E2. Nhấn OK.
- Kết quả xuất hiện. Ba hàng đầu hiển thị lỗi #N/A. Do nhập 4 ở "Khoảng", nghĩa là SMA 4 ngày.
Đọc thêm: Cách tính Lãi suất đơn giản trong Excel.
Phương pháp 1 chỉ tính được SMA đơn giản. Phương pháp 2 sẽ tính cả 3 loại dùng công thức.
2. Tính Trung bình động Đơn giản (SMA) bằng công thức
Dùng cùng dữ liệu mẫu.
- Để tính SMA 4 ngày, nhập công thức sau ở ô nằm trên hàng 5 (Ngày 4). Sau khi nhập, nhấn "Enter."
=AVERAGE(B2:B5)
- Di chuột đến góc dưới bên phải ô đã chọn. Khi con trỏ thành biểu tượng "Cộng" (Plus), giữ chuột trái và kéo đến ô E11. Sao chép công thức cho các ô khác.
- Nhận kết quả.
Đọc thêm: Cách tính diện tích Hình chữ nhật, Tam giác, hoặc Hình tròn trong Excel.
3. Tính Trung bình động Trọng số (WMA) bằng công thức
Tính WMA. Dùng cùng dữ liệu mẫu. Để tính WMA, cần có trọng số gán cho các giá trị. Công thức WMA:
WMA = [(Giá trị mới nhất * trọng số) + (Giá trị trước * trọng số) + ...] / (Tổng tất cả trọng số)
Tính WMA 3 điểm với 70% trọng số cho giá trị mới nhất, 20% cho giá trị trước đó, 10% cho giá trị trước đó nữa. Theo dữ liệu này, nhập công thức sau ở ô E4:
=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)
Hai ô đầu để trống vì tính WMA 3 điểm. Kéo ô xuống ô cuối như tính SMA. Nhận kết quả.
Đọc thêm: Cách tính Trung vị (Median) trong Excel.
4. Tính Trung bình động Mũ (EMA) bằng công thức
Để tính EMA, cần giá trị EMA đầu tiên (tính từ SMA) và hệ số trọng số/độ mượt (K). Công thức cơ bản EMA:
EMA = Giá trị mới nhất * K + Giá trị EMA hôm qua * (1 - K)
- Dùng bảng đã tính SMA. Ví dụ K = 0.3. Giá trị EMA đầu tiên = giá trị SMA đầu tiên. Tính SMA 4 ngày và EMA.
- Áp dụng công thức sau cho ô F6:
=B6*$G$1+F5*(1-$G$1)
Lưu ý: khóa ô G trong công thức vì cần cùng giá trị K cho tất cả tính toán EMA.
- Kéo ô để sao chép công thức cho các ô còn lại.
Xong! Đã tính xong 3 loại trung bình động trong MS Excel. Hy vọng bạn thích bài viết.
Có thể quan tâm: Cách tính Điểm trung bình (GPA) trong Excel.
