Tính Trung bình động mũ (EMA) trong Excel: Hướng dẫn từng bước rõ ràng

Trung bình động mũ (Exponential Moving Average - EMA) được sử dụng rộng rãi cho dự báo dữ liệu tương lai và làm mượt biến động giá cổ phiếu. Nếu bạn muốn biết EMA là gì và cách tính trong Excel, đây chính là nơi phù hợp. Bài viết này sẽ chỉ cho bạn 3 phương pháp phù hợp để tính EMA trong Excel.

Trung bình động mũ (EMA)

Trung bình động mũ (EMA), còn gọi là trung bình động có trọng số mũ, là một loại trung bình động שנותן trọng số lớn hơn cho dữ liệu gần đây so với trung bình động đơn giản. Do đó, làm mượt trong trường hợp EMA trong Excel cao hơn trung bình động đơn giản. Tầm quan trọng của dữ liệu gần nhất giảm dần khi khoảng thời gian kéo dài. Công thức tính EMA là:

EMA_t= α × Y_t-1+ (1-α) × EMA_t-1

Trong đó:

EMA_t = Trung bình động mũ của kỳ hiện tại
α = Hệ số làm mượt (Smoothing multiplier). α có thể là bất kỳ số nào từ 0 đến 1. Giá trị α càng lớn, trọng số cho dữ liệu quá khứ càng lớn. Phạm vi lý tưởng của α nằm giữa 0.1 đến 0.3. α cũng có thể tính bằng công thức: α = 2/(n+1); ở đây, n là số kỳ.

Giả sử chúng ta có bộ dữ liệu sau với dữ liệu bán hàng 12 tháng năm 2020. Bây giờ chúng ta muốn dự báo dữ liệu bán hàng cho tháng 1 năm 2021 sử dụng Trung bình động mũ.

3 Phương pháp tính Trung bình động mũ trong Excel

1. Sử dụng Toolpak Phân tích dữ liệu (Data Analysis Toolpak) để tính EMA

Chúng ta có thể áp dụng Trung bình động mũ để dự báo dữ liệu tương lai bằng Data Analysis ToolPak. Trước tiên, bạn phải thêm ToolPak này vào Excel.

Đi đến tab Tệp (File) và chọn Tùy chọn (Options).
Cửa sổ Tùy chọn Excel sẽ mở ra. Đi đến tab Tiện ích mở rộng (Add-ins) và nhấp vào Chuyển đến (Go).
Hộp Tiện ích mở rộng sẽ hiện ra. Chọn Analysis ToolPak và nhấp OK.
Đi đến tab Dữ liệu (Data) và nhấp vào Phân tích dữ liệu (Data Analysis).
Chọn Làm mũ exponensial (Exponential Smoothing) và nhấp OK.
Trong cửa sổ Làm mũ exponensial:
- Chọn ô chứa dữ liệu bán hàng trong hộp Dải đầu vào (Input Range) (bao gồm ô trống của kỳ dự báo). Nhập giá trị α trong hộp Hệ số giảm dần (Damping factor). Ví dụ này lấy α = 0.3.
- Chọn Dải đầu ra (Output Range) (nơi bạn muốn tính EMA).
- Nếu muốn xem biểu đồ biểu diễn EMA và dữ liệu thực tế, chọn hộp Đầu ra biểu đồ (Chart Output).
- Cuối cùng, nhấp OK.

Kết quả: Excel sẽ đưa ra EMA của dữ liệu trong dải bán hàng đã chọn và bạn sẽ có dự báo bán hàng cho tháng 1 năm 2021.

Lưu ý: EMA cho tháng 2 giống với doanh số tháng 1. Excel Analysis ToolPak đơn giản giả định EMA đầu tiên bằng dữ liệu kỳ trước, còn gọi là Phương pháp Naive cho dự báo.

Excel cũng sẽ hiển thị biểu đồ so sánh Dữ liệu thực tế và Dự báo EMA đã tính.

2. Tính Trung bình động mũ trong Excel Thủ công

Thay vì dùng Analysis ToolPak, chúng ta có thể tính EMA bằng cách chèn thủ công công thức tính EMA. Để tính EMA cho tất cả các kỳ, chúng ta cần biết EMA cho kỳ đầu tiên. Có thể tính bằng nhiều phương pháp như naive, trung bình động đơn giản, trung bình động 3 kỳ, v.v. Ví dụ này dùng phương pháp trung bình động 3 kỳ (3MA).

Để tính trung bình động 3 kỳ:

Nhập công thức sau vào hàng tháng 4 (ô E8):

=(D5+D6+D7)/3

Công thức trả về trung bình doanh số 3 năm trước. Sau đó, kéo ô E8 để lấy 3MA cho tất cả các kỳ.

Bây giờ cần α để tính EMA. Xác định α bằng công thức:

=2/(12+1)

Lấy n = 12 vì có 12 kỳ trong bộ dữ liệu.

Chèn công thức sau để xác định EMA cho kỳ thứ 4:

=(1-$C$3)*D7+$C$3*E8

Công thức cho EMA kỳ đầu tiên, dùng 3MA kỳ này làm EMA kỳ trước.

Bây giờ đã có EMA cho năm đầu tiên, có thể dễ dàng tính EMA cho tất cả các kỳ.

Nhập công thức sau vào ô F9:

=(1-$C$3)*D8+$C$3*F8

Ở đây, $C$3 chứa giá trị α, D8 là doanh số kỳ trước và F8 là EMA kỳ trước.

Kéo ô E9 đến cuối bộ dữ liệu.

Kết quả: Bạn nhận được EMA cho tất cả các kỳ bao gồm kỳ dự báo (Tháng 1 năm 2021).

Đọc thêm: Cách tạo Trung bình động trong Biểu đồ Excel (4 Phương pháp)

3. Sử dụng VBA để tính EMA

Trung bình động mũ cũng dùng để làm mượt biến động giá cổ phiếu. Phương pháp này cho thấy cách dùng EMA để làm mượt giá cổ phiếu qua nhiều kỳ bằng cách tạo hàm tùy chỉnh dùng Microsoft Visual Basic Application (VBA).

Khi làm mượt giá cổ phiếu, giá kỳ hiện tại dùng thay cho dữ liệu kỳ trước trong công thức EMA. Công thức trở thành:

EMA_t= α × P + (1-α) × EMA_t-1

Ở đây, P là giá kỳ hiện tại.

Công thức cũng có thể viết như sau:

EMA_t = α × P + (1 – α) × EMA_t-1

Giả sử bộ dữ liệu có giá trung bình cổ phiếu qua các tháng trong năm. Chúng ta sẽ xây dựng hàm tùy chỉnh dùng VBA để tính EMA giá cổ phiếu các tháng.

Chuột phải vào tên trang tính và chọn Xem mã (View Code).
Cửa sổ Mã VBA sẽ mở ra.
Nhập mã sau vào cửa sổ này:

Private Sub Workbook_Open() AddUDF End Sub

Mã này tạo Macro sẽ thêm mã tùy chỉnh vào trang tính của Sổ làm việc Excel.

Chuột phải vào tên trang tính từ bảng điều khiển bên trái của cửa sổ VBA và đi đến Chèn > Mô-đun (Insert > Module).
Cửa sổ Mô-đun VBA sẽ mở ra. Chèn mã sau vào cửa sổ Mô-đun:

Sub AddUDF() Application.MacroOptions macro:="EMA", _ Description:="Returns the Exponential Moving Average." & Chr(10) & Chr(10) & _ "Select last period's EMA or last period's price if current period is the first." & Chr(10) & Chr(10) & _ "Followed by current price and n." & Chr(10) & Chr(10) & Chr(10) & _ "The decay factor of the exponential moving average is calculated as alpha=2/(n+1)", _ Category:="Technical Indicators" End Sub Public Function EMA(EMAYesterday, price, n) alpha = 2 / (n + 1) EMA = alpha * price + (1 - alpha) * EMAYesterday End Function

Mã này tạo hàm tùy chỉnh tên EMA (EMAYesterday, price, n).

Đóng cửa sổ VBA.

Để tính EMA dùng hàm tùy chỉnh này, cần biết EMA kỳ đầu tiên. Ví dụ này dùng phương pháp naive, nên EMA kỳ đầu tiên bằng giá cổ phiếu kỳ đó.

Chèn công thức vào ô D6:

=EMA(D5,C6,12)

Ở đây, D5 là EMA kỳ trước, C6 là giá kỳ hiện tại và 12 là số kỳ.

Nhấn ENTER.
Kéo ô D6 đến cuối bộ dữ liệu.

Kết quả: Bạn nhận được EMA cho tất cả các kỳ.

Đọc thêm: Cách tính Trung bình trong Excel (Bao gồm tất cả tiêu chí)

Kết luận

Hy vọng giờ bạn đã biết cách tính trung bình động mũ trong Excel. Nếu có thắc mắc, hãy để lại bình luận.