Hướng dẫn tính giá trị trung bình động dùng Excel

Bạn đọc sẽ được Siêu Marketing chỉ dẫn phương pháp tính giá trị trung bình động một cách dễ dàng trong Excel qua bài viết này. Bài viết hướng dẫn sử dụng hàm tính giá trị trung bình của N ngày/tuần/tháng/năm liền kề trước đó, đồng thời giới thiệu cách chèn đường trung bình động vào đồ thị trong Excel.

Khái niệm trung bình động

Bản chất, trung bình động là chuỗi giá trị trung bình được tính từ nhiều tập dữ liệu khác nhau trong một dữ liệu lớn.

Trung bình động thường được ứng dụng trong lĩnh vực thống kê, đánh giá dự báo các chu kỳ biến động trong kinh tế và dự báo thời tiết để nhận diện ra xu hướng. Đối với lĩnh vực chứng khoán, nó thể hiện sự biến đổi trung bình của giá cổ phiếu theo từng quãng thời gian. Trong mảng kinh doanh, việc dùng trung bình động hữu ích trong việc tính gia quyền doanh thu 3 tháng gần nhất nhằm dự báo xu thế phát triển.

Chẳng hạn, việc xác định trung bình động nhiệt độ của ba tháng liên tiếp có thể được thực hiện qua việc tính trung bình nhiệt độ từ tháng Một đến tháng Ba, sau đó là từ tháng Hai đến tháng Tư, tiếp tục là từ tháng Ba đến tháng Năm, và cứ tiếp tục như vậy…

Moving average

Có nhiều loại trung bình động khác như trung bình động đơn giản, trung bình động hàm mũ, trung bình động thay đổi theo thời gian, trung bình động tam phân, và trung bình động có trọng số. Tuy nhiên, bài viết này sẽ tập trung vào hình thức phổ biến nhất là trung bình động đơn giản.

Phương thức tính trung bình động đơn giản với Excel

Trong Excel, có hai phương thức để tính trung bình động đơn giản – thông qua việc sử dụng công thức hoặc chức năng xu hướng. Dưới đây là những ví dụ minh hoạ cho cả hai phương pháp này.

Ví dụ 1: Tính trung bình động theo kỳ hạn nhất định

Trung bình động đơn giản có thể tính bằng hàm AVERAGE. Xét trường hợp bạn có danh sách nhiệt độ trung bình hàng tháng tại cột B và bạn muốn tìm trung bình động cho ba tháng (như minh hoạ ở hình trên)

Đơn giản chỉ cần áp dụng công thức AVERAGE thông thường cho ba giá trị ban đầu và điền vào ô số ba tính từ đầu bảng xuống (ví dụ, ô C4), rồi kéo công thức này xuống toàn bộ cột: =AVERAGE(B2:B4)

Có thể cố định một trong các ô (như ô B2) theo nhu cầu, nhưng cũng nên sử dụng tham chiếu không cố định để công thức có thể tự động điều chỉnh phù hợp khi được áp dụng cho các ô khác.

Đáng nhớ rằng trung bình cộng được xác định thông qua việc chia tổng giá trị cho số lượng giá trị được tính toán trung bình, và bạn có thể kiểm chứng điều này qua công thức SUM: =SUM(B2:B4)/3

Excel formulas to calculate moving average for 3 months

Xem thêm: 3 phương pháp tính giá trị trung bình trên Excel

Ví dụ 2: Tính trung bình động cho N ngày/tuần/tháng/năm gần đây trong một cột

Xét bạn có danh sách dữ liệu, như là doanh số bán hàng hoặc giá cổ phiếu, và bạn muốn biết trung bình ba tháng gần nhất tại bất kỳ thời điểm nào. Lúc này, công thức cần phải tính lại trung bình mỗi khi bạn nhập giá trị mới cho tháng mới. Hàm AVERAGE kết hợp với OFFSETCOUNT sẽ làm nhiệm vụ này.

=AVERAGE(OFFSET(first cell, COUNT(entire range)-N,0,N,1))

Với N là số ngày/tuần/tháng/năm cần xem xét gần nhất.

Giả định rằng giá trị trung bình bắt đầu từ hàng 2 cột B, công thức sẽ ra là: =AVERAGE(OFFSET(B2,COUNT(B2:B100)-3,0,3,1))

Finding moving average for a the last N days / weeks / months/ years

Hãy cùng phân tích kỹ hơn từng phần của công thức này:

  • Hàm COUNT “COUNT(B2:B100)” sẽ tính số lượng các giá trị nhập vào trong cột B. Việc đếm được bắt đầu từ ô B2 do ô B1 phục vụ làm tiêu đề.
  • Hàm OFFSET bắt đầu lấy dữ liệu từ ô B2 (đối số đầu tiên), sau đó dịch lên trên 3 dòng theo số lượng dòng được xác định bởi hàm COUNT (trong trường hợp là -3 – đối số thứ hai của hàm OFFSET). Nhờ đó, công thức tổng hợp 3 giá trị mới nhất nằm trong phạm vi 3 hàng (được chỉ định bằng đối số thứ ba là 3) và 1 cột (đối số cuối cùng là 1 trong hàm OFFSET) để tìm ra tổng của 3 tháng gần nhất mà chúng ta cần.
  • Hàm AVERAGE sau đó sẽ lấy tổng vừa tính được để tìm ra giá trị trung bình động.

Lưu ý. Khi bạn làm việc với bảng tính có hàng được cập nhật thường xuyên, bạn phải đảm bảo hàm COUNT đủ chỗ để chứa dữ liệu mới. Điều này không phải là vấn đề nếu bạn thêm các cột mới sau ô tính toán đầu tiên, vì hàm COUNT sẽ loại trừ các hàng trống.

Trong trường hợp ví dụ, bảng chỉ chứa dữ liệu cho 12 tháng, tuy nhiên chúng ta vẫn đặt cho hàm COUNT một phạm vi rộng hơn là B2:B100.

Ví dụ 3. Cách tính trung bình động cho N giá trị mới nhất trong một dãy

Nếu mục tiêu là tính trung bình động cho N ngày/tháng/năm gần nhất trên cùng một hàng, chỉ việc điều chỉnh công thức OFFSET như sau:

=AVERAGE(OFFSET(ô đầu tiên,0,COUNT(phạm vi)-N,1,N))

Với giả định là ô B2 chứa số liệu đầu tiên của hàng và bạn muốn tính trung bình động cho ba số liệu cuối cùng, công thức được viết:

=AVERAGE(OFFSET(B2,0,COUNT(B2:N2)-3,1,3))

 Getting moving average for the last N values in a row

Xây dựng đường trung bình động trong biểu đồ Excel

Việc thêm đường trung bình động vào biểu đồ Excel là quá trình thực hiện nhanh chóng, nếu đã có sẵn biểu đồ. Chúng tôi sẽ chỉ bạn cách sử dụng chức năng Excel Trendline theo các bước dưới đây.

Ví dụ này diễn giải việc tạo một biểu đồ doanh số bán hàng loại 2D (mục Insert > Charts)

Bây giờ, chúng ta bắt đầu vẽ biểu đồ trung bình động cho ba tháng gần đây.

  1. Trong Excel 2013, hãy chọn biểu đồ, sau đó điều hướng đến tab Design, chọn nhóm Chart Layouts > Add Chart Element > Trendline > More Trendline Options…

To add a moving average trendline, click more Trendline Options

Trong Excel 2010 và 2007, bạn có thể tìm đến Layout > Trendline > More Trendline Options…

Lưu ý. Nếu không cần đến những chi tiết như thời gian hay tên biểu đồ, bạn có thể dễ dàng thêm trung bình động bằng cách nhấp vào Design > Add Chart Element > Trendline > Moving Average để nhanh chóng có được kết quả.

  1. Tại menu Format Trendline ở bên phải trang tính trong Excel 2013, hoặc hộp thoại tương ứng trong Excel 2010 và 2007 sẽ mở ra.

Trên bảng Format Trendline, hãy nhấp

Click vào icon Trendline Options¸ lựa chọn Moving Average sau đó nhập số kỳ trong hộp Period:

Chọn tuỳ chọn Moving Average và nhập vào số kỳ cho trung bình động.

  1. Tắt bảng Trendline và đường trung bình động xẽ xuất hiện trên đồ thị như dưới đây:

Bạn có thể tuỳ chỉnh đồ thị bằng cách mở tab Fill & Line hoặc Effects tại bảng Format Trendline và điều chỉnh diverse tùy chọn như kiểu đường, màu sắc, độ dày…

Để nghiên cứu dữ liệu một cách sâu hơn, bạn có thể muốn áp dụng nhiều đường trung bình động ở các khoảng thời gian khác nhau để đối chiếu các xu hướng. Hình sau minh họa đường trung bình động cho 2 tháng (màu xanh lá) và 3 tháng (màu đỏ).

Nguồn: Ablebits, được biên tập và dịch bởi Siêu Marketing.

Bạn có thấy là rất nhiều kiến thức quan trọng đã được chia sẻ? Tất cả những thông tin này bạn có thể nắm bắt thông qua khóa học SM90 – Excel từ cơ bản đến nâng cao từ Siêu Marketing. Khóa học này sẽ giúp bạn tổ chức lại kiến thức một cách rõ ràng, chi tiết và bạn không cần lo lắng về hạn chế thời gian học, cho phép bạn học mọi lúc, tra cứu kiến thức khi cần thiết. Hiện tại Siêu Marketing đang có chương trình ưu đãi lớn cho các học viên khi đăng ký khóa học này. Xem thêm thông tin tại địa chỉ: sieumarketing.com

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop