Hướng dẫn cách tính đường trung bình động trong Excel đơn giản

Chắc hẳn bạn đã không còn xa lạ với cách tính giá trị trung bình trong Excel bằng các hàm AVERAGE? Vậy nhưng bạn đã biết cách tính đường trung bình động chưa?

Trong bài viết ngày hôm nay, Gitiho sẽ hướng dẫn các bạn cách tìm trung bình động nhanh chóng trên trang tính của mình. Ngoài ra, chúng ta cũng sẽ tìm hiểu cách tạo biểu đồ để trực quan dữ liệu đã tính.

Giới thiệu về đường trung bình động (Moving Average)

Trước khi thực hành cách tính và vẽ biểu đồ đường trung bình động trong Excel, chúng ta hãy cùng ôn lại các kiến thức về trung bình động và các loại trung bình động nhé.

Moving Average là gì?

Để hiểu về đường trung bình động, hay còn gọi là Moving Average, chúng ta sẽ dựa trên thuật ngữ giá trị trung bình. Nếu như trung bình của một dãy số là giá trị nằm ở vị trí trung tâm của dãy số đó, thì trung bình động được hiểu là giá trị trung bình dựa trên các biến động của giá trị thêm bớt vào dãy số trong một khoảng thời gian nhất định.

Đường trung bình động là một thuật ngữ quen thuộc đối với ngành tài chính, đặc biệt là đối với với các giao dịch chứng khoán. Nó đóng vai trò là chỉ báo để tìm hiểu phương hướng vận động của  thị trường dựa trên tỷ giá trong quá khứ

Ngoài ra, giá trị trung bình động này còn được sử dụng trong ngành thống kê với mục đích dự báo chu kỳ thay đổi kinh tế, và trong thời tiết để diễn giải và dự báo xu hướng thời tiết.

Các loại đường trung bình động

Trên thực tế, có 2 loại đường trung bình động được sử dụng phổ biến:

• Simple Moving Average (SMA): Đường trung bình động đơn giản thể hiện giá trung bình trong một khoảng thời gian
• Exponential Moving Average (EMA): Đường trung bình động lũy thừa đánh giá các tỷ giá hiện tại quan trọng hơn các tỷ giá được ghi nhận trong quá khứ

Về cách tính, đường trung bình động đơn giản (SMA) được tính bằng cách lấy trung bình cộng giá động cửa trong khoảng thời gian cần tính, còn đường trung bình động lũy thừa được tính kèm theo trọng số của các giá trị.

Dựa vào sự khác biệt trong phương thức tính toán, chúng ta có thể thấy SMA nhìn chung sẽ giúp đưa ra giá trị trung bình nhanh hơn EMA.

Mục đích sử dụng đường trung bình động

Để giải thích về lý do đường trung bình động đóng vai trò là công cụ phân tích kỹ thuật thông dụng trong giới tài chính, chúng ta cần đề cập đến tính trực quan của nó. Thông qua các hình dạng được thể hiện trên đường trung bình động, chúng ta có thể dễ dàng nhìn thấy và diễn giải xu hướng của giá.

Bên cạnh đó, đường trung bình động thường được kết hợp với các chỉ báo khác để bù trừ cho các nhược điểm của nhau.

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

Sau khi đã ôn lại các kiến thức về đường trung bình động, trong phần này, hãy cùng Gitiho thực hành cách tính các loại chỉ báo này trên Excel nhé.

Cách tính SMA bằng Data Analysis ToolPak

Excel đã thiết lập sẵn một add-in trong hệ thống giúp chúng ta tính toán đường trung bình động đơn giản (SMA). Đó là add-in Data Analysis ToolPak. Mình sẽ hướng dẫn bạn cách kích hoạt và sử dụng add-in này cho dữ liệu trên trang tính.

• Bước 1: Đi đến thẻ File > Options > Add-ins > Manage: Excel Add-ins > Go

 

• Bước 2: Hộp thoại Add-ins hiện ra. Tích chọn ô Analysis TookPak > OK.

Sau khi kích hoạt thành công add-in Analysis ToolPak, bạn sẽ thấy trong thẻ Data xuất hiện thêm một nhóm Analysis chứa công cụ Data Analysis.

Bây giờ chúng ta sẽ tìm hiểu cách tính đường trung bình động thông qua một ví dụ đơn giản dựa trên bảng dữ liệu dưới đây.

 

Hãy thực hiện các bước dưới đây để tính số liệu trung bình động trong vòng 4 ngày đối với từng dòng dữ liệu tương ứng:

• Bước 1: Đi đến thẻ Data > nhóm Analysis > Data Analysis.
• Bước 2: Hộp thoại Data Analysis hiện ra. Chọn Moving Average và nhấn OK.

• Bước 3: Hộp thoại Moving Average hiện ra. Điền thông tin vào các ô tương ứng:
  • Input Range - Dữ liệu đầu vào: $B$2:$B$11
  • Interval - Khoảng thời gian cần tính: 4
  • Output Range - Dữ liệu đầu ra: $C$2:$C$11

Sau đó nhấn OK để Excel tiến hành tính đường trung bình động cho dữ liệu đã nhập.

Bạn sẽ nhận được kết quả của cột Trung bình (cột C) như trong hình dưới đây:

Với 3 ngày đầu tiên, do không có đủ dữ liệu theo yêu cầu tính trung bình 4 ngày nên Excel trả về lỗi #N/A. Công thức yêu cầu tối thiểu 4 con số đại diện cho 4 giá trị, nghĩa là công thức chỉ được áp dụng cho ngày 4 trở đi.

Vậy là chúng ta đã hoàn thành cột đường trung bình động cho nhiệt độ trong vòng 4 ngày gần nhất. Sau khi đã có kết quả, bạn hãy sử dụng Format Cells để định dạng lại số liệu sao cho vừa ý mình nhất nhé.

Xem thêm: Hướng dẫn sử dụng Format Cells trong Excel để giải bài thi MOS

Cách vẽ biểu đồ đường trung bình động đơn giản

Một điểm cực kì tiện lợi của add-in Analysis ToolPak là nó cho phép bạn tạo biểu đồ đường trung bình động ngay ở hộp thoại nhập dữ liệu Moving Average, tương ứng với bước 3 trong hướng dẫn tính trung bình 4 ngày gần nhất trong phần trên.

Tại hộp thoại Moving Average, bạn hãy tích chọn ô Chart Output trong phần Output Options để Excel tự động tạo một biểu đồ cho dữ liệu được tính toán.

Nhấn OK và nhận kết quả như trong hình dưới đây thôi!

Trong biểu đồ này, bạn có thể thấy đường màu xanh hiển thị số liệu về Nhiệt độ (cột B), còn đường màu cam đại diện cho các giá trị trung bình chúng ta đã tính.

Để chuyển dữ liệu về dạng biểu đồ hợp lý hơn, chúng ta sẽ sử dụng biểu đồ đường cho cột dữ liệu Nhiệt độ (cột B), sau đó tạo một đường xu hướng để biểu thị các giá trị trên đường trung bình động (cột C).

Bước 1: Vẽ biểu đồ cột

Đầu tiên, bạn hãy chọn toàn bộ dữ liệu cột A và cột B dưới dòng tiêu đề (A2:B11). Sau đó đi đến thẻ Insert > nhóm Charts > biểu tượng biểu đồ cột > Clustered Column.

Bước 2: Điều chỉnh dữ liệu đầu vào của biểu đồ Excel

Sau khi đã có một biểu đồ dựa trên dữ liệu cột Ngày (cột A) và cột Nhiệt độ (cột B), bạn vẫn có thể thay đổi hoặc cập nhật dữ liệu đầu vào của biểu đồ.

Hãy nhấn chuột phải tại biểu đồ Excel và chọn Select Data.

Hộp thoại Select Data Source hiện lên. Đây là nơi quản lý dữ liệu đầu vào của biểu đồ. Bạn có thể thêm bớt hoặc điều chỉnh phạm vi dữ liệu cần thể hiện trên biểu đồ của mình tại đây.

Mình sẽ thay đổi tên sê-ri bằng cách nhấn vào sê-ri cần thao tác và nhấn Edit. Cách xóa một sê-ri cũng diễn ra tương tự. Còn trong trường hợp bạn muốn thêm một sê-ri mới thì chỉ cần nhấn Add.

Hộp thoại Edit Series hiện ra. Tại đây, mình sẽ điền tên của sê-ri là Nhietdo tương ứng với tên cột B, đồng thời kiểm tra phạm vi dữ liệu tương ứng B2:B11. Sau khi đã hoàn thành, nhấn OK để đóng hộp thoại và áp dụng các thay đổi cho biểu đồ Excel.

Bước 3: Thêm đường trung bình Trendline

Sau khi đã hoàn thiện các giá trị hiển thị trên biểu đồ Excel, chúng ta sẽ thêm một đường trung bình động để thể hiện xu hướng tăng giảm của nhiệt độ trong thời gian 4 ngày gần nhất.

Nhấn biểu tượng dấu cộng cạnh biểu đồ và tích ô Trendline.
 

Bước 2: Thiết lập Trendline

Excel đã vẽ một đường xu hướng trên biểu đồ Excel của bạn. Tuy nhiên, đây không phải là thứ chúng ta cần. Để điều chỉnh đường về dạng đường trung bình động như yêu cầu, hãy nhấn chuột phải tại đường xu hướng và chọn Format Trendline.

 

Cửa sổ Format Trendline hiện lên bên phải giao diện trang tính Excel. Lúc này, bạn hãy tích chọn ô Moving Average và thiết lập tham số period = 4 đại diện cho 4 ngày gần nhất cần tính trung bình nhiệt độ.

Đồng thời, hãy tích vào ô Automatic tại phần Trendline Name nằm ngay phía dưới.

Ngoài ra, bạn có thể thay đổi màu sắc, kiểu nét của đường trung bình cộng trong cửa sổ Format Trendline. Hãy nhấn vào biểu tượng thùng sơn và tự thiết kế Trendline theo ý muốn của mình nhé. Bạn hoàn toàn có thể thực hiện thao tác tương tự với các yếu tố khác của biểu đồ Excel nữa đấy.

Cách tính SMA và EMA bằng công thức

Add-in Data Analysis TookPak trong hướng dẫn ở phần trên chỉ có thể giúp chúng ta tính đường trung bình động đơn giản (SMA). Trong trường hợp cần tính đường trung bình động lũy thừa (EMA), chúng ta sẽ phải dựa vào các công thức. Tất nhiên, bạn vẫn có thể tính SMA bằng công thức hàm AVERAGE như bình thường.

Cách tính SMA bằng công thức

Tương tự như ví dụ ở phần trên về bảng diễn biến nhiệt độ, thay vì sử dụng add-in Data Analysis ToolPak, hãy cùng xem chúng ta sẽ tính đường trung bình động bằng công thức như thế nào nhé.

Vì đang tính trung bình cho 4 giá trị nên chúng ta sẽ bỏ trống 3 ô đầu của cột C. Chúng ta sẽ sử dụng công thức hàm AVERAGE cho ô C4 như sau:

=AVERAGE(B2:B4)

Trong đó: B2:B4 là 4 giá trị đưa vào phép tính trung bình

Sau đó, chúng ta sẽ sử dụng AutoFill kéo công thức tại ô C4 xuống toàn bộ các ô phía dưới của cột Trung bình (cột C). Kết quả đường trung bình động được tính như trong bảng dưới đây:

Cách tính EMA bằng công thức

So với đường trung bình động đơn giản (SMA), cách tính đường trung bình động lũy thừa (EMA) phức tạp hơn vì chúng ta cần xét đến cả tỷ trọng của các giá trị.

Công thức tính EMA tổng quát như sau:

EMA [hôm nay] = (Giá trị [hôm nay] x K) + (EMA [hôm qua] x (1 - K))

Trong đó:

• K = 2/(N+1)
• N là chu kỳ EMA
• Giá [hôm nay] là giá trị mới nhất của dữ liệu
• EMA [hôm qua] là giá trị EMA trước đó.
• EMA [hôm nay] là giá trị EMA hiện tại.

Dựa trên công thức này, chúng ta sẽ xây dựng công thức tính EMA trên Excel. Với ví dụ bảng dữ liệu nhiệt độ như ở các phần trên, chúng ta sẽ tính EMA cho 4 giá trị Nhiệt độ gần nhất bằng một phép tính thông thường.

Tại ô C2, điền công thức:

=B3*(2/5)+C2*(1-2/5)

Trong đó:

• B3 là giá trị hôm nay
• 2/5 là K=2/N+1
• C2 là giá trị EMA [hôm qua]
• 1-2/5 là 1-K

Sau đó, chúng ta sẽ sử dụng AutoFill kéo công thức tại ô C4 xuống toàn bộ các ô phía dưới của cột Trung bình (cột C). Kết quả đường trung bình động được tính như trong bảng dưới đây:

Xem thêm: Giới thiệu 15 hàm tài chính trong Excel cơ bản nhất (Phần 1)

Tổng kết

Như vậy, trong bài viết ngày hôm nay, chúng ta đã tìm hiểu cách sử dụng add-in Data Analysis ToolPak và các công thức thông thường để tính đường trung bình động SMA và EMA trong Excel. Các bạn có thể áp dụng các phương thức hợp lý cho dữ liệu trên trang tính của mình.

Hy vọng bài viết đã đem đến kiến thức bổ ích khi các bạn làm việc trên phần mềm Excel. Để tìm hiểu thêm các thủ thuật hay ho khác, hãy theo dõi và tìm đọc các bài viết của chúng mình trên blog Gitiho nhé.

Gitiho xin cảm ơn bạn đọc và hẹn gặp lại trong các bài viết tiếp theo!