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.

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

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

Moving Average là gì?

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

• 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ứ

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.

• 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

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.

• 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

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!