Đường trung bình trượt theo cấp số nhân được sử dụng rộng rãi để dự báo dữ liệu trong tương lai và làm dịu chuyển động giá cổ phiếu. Nếu bạn muốn biết đường trung bình theo cấp số nhân là gì và làm thế nào bạn có thể tính toán điều này trong Excel, thì đây là nơi phù hợp với bạn. Trong bài viết này, tôi sẽ chỉ cho bạn 3 phương pháp phù hợp để tính Trung bình trượt theo cấp số nhân trong Excel.
Đường trung bình trượt theo cấp số nhân (EMA)
Đường trung bình trượt theo cấp số nhân ( EMA ), còn được gọi là đường trung bình động có trọng số theo cấp số nhân, là một loại đường trung bình động điều đó mang lại nhiều trọng lượng hơn cho dữ liệu gần đây so với đường trung bình động đơn giản. Do đó, việc làm mịn trong trường hợp đường trung bình động theo cấp số nhân trong excel cao hơn so với đường trung bình động đơn giản. Tầm quan trọng của dữ liệu gần đây nhất giảm khi khoảng thời gian dài ra. Công thức tính EMA là,
EMA t =α xY t-1 + (1-α) x EMA t-1
Ở đâu, EMA t =Đường trung bình động theo cấp số nhân của giai đoạn hiện tại
α =Làm mịn hệ số nhân. α có thể là bất kỳ số nào trong khoảng từ 0 đến 1. Giá trị của alpha càng nhiều thì dữ liệu quá khứ càng có trọng số. Phạm vi lý tưởng của α nằm trong khoảng 0,1 đến 0,3. α cũng có thể được tính theo công thức sau, α =2 / (n + 1); ở đây, n là số khoảng thời gian.
Giả sử, chúng tôi có tập dữ liệu sau, trong đó chúng tôi có dữ liệu bán hàng của 12 tháng vào năm 2020. Bây giờ chúng tôi muốn dự báo dữ liệu bán hàng cho tháng 1 năm 2021 bằng cách sử dụng Trung bình động theo cấp số nhân.
3 phương pháp để tính trung bình trượt theo cấp số nhân trong Excel
1. Công cụ phân tích dữ liệu để tính trung bình trượt theo cấp số nhân
Chúng tôi có thể áp dụng đường trung bình theo cấp số nhân để dự báo dữ liệu trong tương lai bằng cách sử dụng Công cụ phân tích dữ liệu . Để làm được điều đó, trước tiên, bạn phải thêm ToolPak này trong Excel của bạn.
➤ Đi tới Tệp và chọn Tùy chọn .
Nó sẽ mở Tùy chọn Excel cửa sổ. Bây giờ,
➤ Đi tới Phần bổ trợ và nhấp vào Bắt đầu
Nó sẽ mở Phần bổ trợ hộp.
➤ Kiểm tra trên Analysis ToolPak và nhấp vào OK .
Bây giờ,
➤ Đi tới Dữ liệu và nhấp vào Phân tích dữ liệu .
Nó sẽ mở Phân tích dữ liệu cửa sổ.
➤ Chọn Làm mịn theo cấp số nhân và nhấp vào OK .
Bây giờ, trong Làm mịn theo cấp số nhân cửa sổ,
➤ Chọn các ô có dữ liệu bán hàng trong Phạm vi đầu vào (bao gồm ô trống của khoảng thời gian dự báo). Chèn giá trị của α trong hộp Hệ số giảm chấn.
Đối với ví dụ này, chúng tôi đã lấy α như 0,3.
Sau đó,
➤ Chọn Phạm vi đầu ra (nơi bạn muốn EMA của mình tính toán).
Nếu bạn muốn xem biểu diễn đồ họa của EMA và dữ liệu thực tế,
➤ Chọn hộp Đầu ra biểu đồ .
Cuối cùng,
➤ Nhấp vào OK .
Do đó, Excel sẽ đưa ra EMA dữ liệu của bạn trong doanh số bán hàng đã chọn và bạn sẽ nhận được dự đoán về doanh số bán hàng cho tháng 1 năm 2021.
Nếu quan sát, bạn sẽ thấy rằng EMA cho tháng Hai cũng giống như doanh số bán hàng cho tháng Giêng. Excel ToolPak Phân tích chỉ đơn giản giả định rằng EMA đầu tiên bằng với dữ liệu của kỳ trước, còn được gọi là Phương pháp ngây thơ để 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 được tính toán.
2. Tính trung bình trượt theo cấp số nhân trong Excel theo cách thủ công
Thay vì sử dụng Analysis ToolPak chúng tôi có thể tính toán Đường trung bình trượt theo cấp số nhân bằng cách chèn công thức cho EMA theo cách thủ công phép tính. Để tính toán EMA cho tất cả các khoảng thời gian, chúng tôi cần biết EMA cho thời kỳ đầu tiên. Chúng ta có thể tính toán điều này bằng cách sử dụng nhiều phương pháp khác nhau, chẳng hạn như đường trung bình đơn giản, đường trung bình động đơn giản, đường trung bình động 3 kỳ, v.v. Trong ví dụ này, chúng ta sẽ sử dụng phương pháp đường trung bình động 3 kỳ ( 3MA ).
Để tính toán đường trung bình động 3 kỳ,
➤ Nhập công thức sau vào hàng của tháng 4 (ô E8 ),
=(D5+D6+D7)/3 Công thức sẽ trả về giá trị trung bình của doanh số bán hàng ba năm trước đó. Sau đó,
➤ Kéo ô E8 để có được 3MA cho tất cả các khoảng thời gian.
Bây giờ, chúng ta sẽ cần α để tính toán EMA . Trong thời gian này, chúng tôi sẽ xác định α sử dụng công thức sau,
=2/(12+1) Chúng tôi đã lấy n =12 vì chúng tôi có 12 khoảng thời gian trong tập dữ liệu của mình.
Sau đó,
➤ Chèn công thức sau để xác định EMA cho thời kỳ thứ tư,
=(1-$C$3)*D7+$C$3*E8 Công thức sẽ cung cấp cho EMA trong khoảng thời gian đầu tiên mà chúng tôi đã sử dụng 3MA của thời kỳ này như các giai đoạn trước EMA .
Bây giờ, chúng tôi đã có EMA trong năm đầu tiên, vì vậy chúng tôi có thể dễ dàng tính toán EMA cho tất cả các khoảng thời gian.
➤ Nhập công thức sau vào ô F9 ,
=(1-$C$3)*D8+$C$3*F8 Đây, $ C $ 3 chứa giá trị của α , D8 là giảm giá của kỳ trước và F8 là EMA của kỳ trước .
Cuối cùng,
➤ Kéo ô E9 vào cuối tập dữ liệu của bạn.
Kết quả là bạn sẽ nhận được EMA cho tất cả các giai đoạn bao gồm cả giai đoạn 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 để gửi EMA
Đường trung bình trượt theo cấp số nhân cũng được sử dụng để làm trơn tru chuyển động giá cổ phiếu. Trong phương pháp này, chúng ta sẽ thấy cách chúng ta có thể sử dụng Đường trung bình trượt theo hàm mũ để làm mượt biến động giá cổ phiếu qua nhiều thời kỳ bằng cách tạo một hàm tùy chỉnh bằng cách sử dụng Microsoft Visual Basic Application (VBA) .
Trong thời gian làm dịu chuyển động giá cổ phiếu, giá kỳ hiện tại được sử dụng thay cho dữ liệu của kỳ trước trong EMA công thức tính. Vì vậy, công thức trở thành,
EMA t =α x P + (1-α) x EMA t-1
Ở đây, P là giá thời kỳ hiện tại.
Công thức cũng có thể được viết theo cách này,
EMA t =α x P + (1 - α) x EMA t-1
Giả sử, tại thời điểm này trong tập dữ liệu của chúng tôi, chúng tôi có giá trung bình của một cổ phiếu qua các tháng khác nhau trong năm. Bây giờ, chúng tôi sẽ xây dựng một công thức tùy chỉnh bằng cách sử dụng VBA để tính toán đường trung bình động theo cấp số nhân của giá cổ phiếu trong các tháng khác nhau.
➤ Đầu tiên, nhấp chuột phải vào tên trang tính và chọn Xem mã .
Nó sẽ mở Mã VBA cửa sổ.
➤ Nhập mã sau vào cửa sổ này,
Private Sub Workbook_Open()
AddUDF
End SubMã sẽ tạo một Macro sẽ thêm mã tùy chỉnh trong trang tính này của Sổ làm việc Excel.
Sau đó,
➤ Nhấp chuột phải vào tên trang tính từ bảng điều khiển bên trái của VBA cửa sổ và đi tới Chèn> Mô-đun .
Nó sẽ mở Mô-đun VBA cửa sổ. Bây giờ,
➤ Chèn mã sau vào Mô-đun cửa sổ,
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 FunctionMã sẽ tạo một chức năng tùy chỉnh có tên EMA (EMAY Hôm qua, giá, n)
Bây giờ,
➤ Đóng VBA cửa sổ.
Bây giờ, để tính toán EMA bằng cách sử dụng chức năng tùy chỉnh này, chúng tôi cần biết EMA của kỳ đầu tiên.
Trong ví dụ này, chúng tôi sẽ sử dụng phương pháp ngây thơ. Vì vậy, EMA của thời kỳ đầu tiên sẽ bằng với giá cổ phiếu của thời kỳ này.
Sau đó,
➤ Chèn công thức vào ô D6 ,
=EMA(D5,C6,12) Đây, D5 là EMA của kỳ trước , C6 là giá của giai đoạn hiện tại và 12 là số khoảng thời gian.
➤ Nhấn ENTER .
Kết quả là bạn sẽ nhận được EMA của khoảng thời gian đó.
Bây giờ,
➤ Kéo ô D6 vào cuối tập dữ liệu của bạn.
Kết quả là bạn sẽ nhận được EMA cho tất cả các khoảng thời gian.
Đọc thêm: Cách tính trung bình trong Excel (Bao gồm tất cả các tiêu chí)
Kết luận
Tôi hy vọng bây giờ bạn biết cách tính toán đường trung bình động theo cấp số nhân trong Excel. Nếu bạn có bất kỳ loại nhầm lẫn nào, vui lòng để lại bình luận.
Các bài viết có liên quan
- Cách Tính Trung bình, Tối thiểu và Tối đa trong Excel (4 Cách Dễ dàng)
- Tính toán Vlookup AVERAGE trong Excel (6 Cách Nhanh)
- Trung bình Đang chạy:Cách Tính bằng Hàm Trung bình (…) của Excel