Hướng dẫn sử dụng Hàm Average để tính Trung bình động trong Excel – Học Excel Online

Qua bài viết này của Siêu Marketing, bạn sẽ được biết cách thức tính toán nhanh và dễ dàng trung bình động sử dụng hàm Average trong Excel. Chúng tôi sẽ giới thiệu bạn những tình huống cần áp dụng tính trung bình động cho N ngày, tuần, tháng, hay năm đã qua và phương pháp để thêm trend trung bình động vào biểu đồ Excel.

Khái niệm Đường trung bình 

Chúng ta đã xét đến phương pháp tính trung bình trong Excel qua một số bài viết trước. Nếu bạn là người theo dõi đều đặn blog của chúng tôi, bạn sẽ là người am hiểu cách thức tính giá trị trung bình thông thường cũng như các hàm được dùng để xác định đường trung bình có trọng số. Trong bài hướng dẫn ngày hôm nay, chúng tôi sẽ phân tích hai phương pháp cơ bản trong việc tính đường trung bình trong Excel.

Đường trung bình (hay còn gọi là rolling average, running average hoặc moving mean) có thể được hiểu như là dãy giá trị trung bình được tính cho các phần tử con đa dạng của một bộ dữ liệu cố định.

Đường trung bình thường xuyên được ứng dụng trong các công việc thống kê, dự báo thời tiết và kinh tế học biến động theo mùa để nhận diện những xu hướng ẩn giấu. Trong lĩnh vực giao dịch chứng khoán, đường trung bình động được biết đến như là chỉ báo của giá trị trung bình cổ phiếu trên một quãng thời gian cụ thể. Trên phương diện kinh doanh, việc tính đường trung bình cho doanh số bán hàng 3 tháng gần đây là hành động thông dụng để phân tích xu hướng mới nhất.

1- Tính trung bình động trong Excel bằng hàm average
1- Tính trung bình động trong Excel bằng hàm average

Ví dụ cho việc tính trung bình động của nhiệt độ hàng ba tháng là lấy trung bình của nhiệt độ từ tháng Một đến tháng Ba, tiếp sau là trung bình từ tháng Hai đến tháng Tư, và sau đó là từ tháng Ba đến tháng Năm, và cứ thế tiếp tục.

Các loại đường trung bình khác nhau được biết đến bao gồm: đường trung bình đơn giản (hay gọi là số học), đường trung bình hàm mũ, biến đổi, tam giác và có trọng lượng. Trong bài này, chúng ta sẽ chú trọng vào đường trung bình động đơn giản, loại được sử dụng phổ thông nhất.

Quy trình để tính toán đường trung bình động đơn giản trong Excel 

Thông thường, có hai phương thức để tính đường trung bình động đơn giản trong Excel: qua việc dùng các công thức cùng cấu hình đường xu hướng. Hai ví dụ sau minh họa cho cả hai kỹ thuật này.  Bạn cũng có thể tham khảo thêm 2 bài viết chi tiết về cách tính trung bình động trong Excel mà tôi đã chuẩn bị:

Series hướng dẫn tính trung bình động trong Excel – Phần 1
Series hướng dẫn tính trung bình động trong Excel – Phần 2

Ví dụ 1: Tính đường trung bình động trong khoảng thời gian cụ thể 

Để tính một đường trung bình động đơn giản chóng vánh, hãy dùng hàm AVERAGE. Cứ giả định rằng bạn có một dãy nhiệt độ trung bình hàng tháng trong cột B, và bạn cần tìm trung bình động của ba tháng (giống như trong hình ảnh minh họa). 

Bạn chỉ cần soạn ra công thức AVERAGE thông thường cho ba giá trị đầu tiên và điền nó vào dòng tương ứng với giá trị thứ ba (trong ví dụ này là ô C4), rồi kéo công thức xuống dọc theo các dòng khác của cột đó:

=AVERAGE(B2:B4)

Tham chiếu cột có thể được giữ cố định (như $B2) nếu bạn muôn, song đảm bảo rằng tham chiếu dòng được giữ thay đổi tương đối (không đặt dấu $) để công thức có thể tự điều chỉnh một cách chính xác cho những dòng còn lại. Lưu ý là giá trị trung bìnhBằng cách lấy tổng của các số liệu và phân chia số đó cho tổng số giá trị tính, bạn có thể kiểm tra lại bằng phương trình sau:

=SUM(B2:B4)/3

2- Tính trung bình động trong Excel bằng hàm average
2- Tính trung bình động trong Excel bằng hàm average

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

Xét một danh sách dữ liệu, như doanh số hoặc giá cổ phiếu, và bạn cần biết giá trị trung bình trong 3 tháng gần đây tại thời điểm nào. Để làm điều này, bạn cần một phép tính nào đó có thể cập nhật giá trị trung bình ngay sau khi bạn thêm giá trị mới cho tháng tiếp theo. Chức năng nào của Excel có thể thực hiện được việc này? Chính là sự kết hợp giữa AVERAGE, OFFSET và COUNT.  

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

N, ở đây, là số ngày/ tuần/ tháng/ năm bạn muốn tính giá trị trung bình. Bạn không chắc làm thế nào để áp dụng phép tính trung bình di động này trong bảng Excel của mình? Dưới đây là một ví dụ sẽ làm mọi điều trở nên rõ ràng hơn. 

Giả sử giá trị trung bình nằm cột B từ hàng 2, công thức như sau: 

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

3- Tính trung bình động trong Excel bằng hàm average
3- Tính trung bình động trong Excel bằng hàm average

Bây giờ, chúng ta cùng tìm hiểu cách thức mà công thức trung bình di động này làm việc trong Excel.  

  • Count sau khi sử dụng hàm COUNT (B2: B100), ta được biết số giá trị đã nhập vào trong cột B. Ta bắt đầu từ B2 bởi vì hàng 1 là tiêu đề.  
  • Công dụng của hàm OFFSET là để xác định ô bắt đầu là B2 (tham số đầu tiên) và dịch chuyển theo số lượng mà hàm COUNT quyết định bằng cách di chuyển lên trên 3 hàng (-3 là tham số thứ hai). Kết quả của việc này là việc trả về một dãy gồm 3 hàng (tham số thứ tư) và 1 cột (đối số cuối cùng), tương ứng với 3 tháng mới nhất mà chúng ta muốn xem xét.  
  • Cuộc tính cuối cùng này sau đó được sử dụng bởi hàm AVERAGE để tính giá trị trung bình động.  

Lời khuyên: Nếu bạn đang xử lý một bảng tính Excel có thể liên tục nhận được cập nhật và thêm vào hàng mới trong tương lai, bạn nên chắc chắn bạn đã cho COUNT một số hàng phù hợp để có thể chứa những mục mới. Việc gồm nhiều hàng hơn mức cần thiết không phải là vấn đề miễn là ô đầu tiên của bạn là chính xác, bởi vì COUNT sẽ bỏ qua những hàng trống. 

Bảng trong ví dụ đã nêu chỉ có thông tin của 12 tháng nhưng chúng ta cung cấp phạm vi từ B2 đến B100 cho COUNT, chỉ để an toàn rằng bạn sẽ không thiếu hụt nếu có thêm dữ liệu

Ví dụ 3: Lấy trung bình động cho N giá trị cuối cùng trong một hàng 

Bạn cũng có thể tính giá trị trung bình động cho N giá trị cuối cùng trong một hàng bằng cách điều chỉnh công thức Offset như sau: 

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

Giả

Nếu B2 chính là số đầu tiên trên dòng và bạn hy vọng tích hợp 3 số cuối cùng vào tính toán giá trị trung bình, công thức bạn cần dùng sẽ ra sao 

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

4- Tính giá trị trung bình di động trong Excel qua hàm average
4- Tính giá trị trung bình di động trong Excel qua hàm average

Xây dựng đồ thị cho trung bình di động trong Excel 

Chỉ mất vài giây để bổ sung đường xu hướng trung bình di động vào đồ thị đã có sẵn với dữ liệu của bạn, sử dụng tính năng Trendline của Excel và quy trình chi tiết dưới đây. 

Dưới đây là ví dụ với một biểu đồ cột 2-D được tạo ra từ nhóm Bảng chọn chèn > Nhóm biểu đồ cho dữ liệu bán hàng của chúng ta:

5- Tạo đồ thị trung bình động trong Excel với hàm average
5- Tạo đồ thị trung bình động trong Excel với hàm average

Giờ đây, ta mong muốn phản ánh đường trung bình trong 3 tháng dưới dạng hình ảnh.  

  1. Lựa chọn biểu đồ trong Excel 2013, điều hướng đến tab Thiết kế > Nhóm bố cục biểu đồ và click vào mục  Thêm phần tử biểu đồ > Xu hướng > Lựa chọn xu hướng thêm
    6- Áp dụng trung bình động trong Excel thông qua hàm average
    6- Áp dụng trung bình động trong Excel thông qua hàm average

Trong Excel 2010 và 2007, bạn sẽ đến mục Bố cục > Xu hướng > Thêm lựa chọn xu hướng.

Lời khuyên: Để ngay lập tức có được kết quả mà không cần nhập chi tiết như khoảng thời gian hay tên của đường xu hướng, bạn có thể thực hiện từ Thiết kế > Thêm phần tử biểu đồ > Xu hướng > Trung bình động.   

  1. Ngay lập tức, ô Định dạng đường xu hướng sẽ hiện ra ở bên phải trong Excel 2013 hoặc một hộp thoại đồng nghĩa xuất hiện trong Excel 2010 và 2007. 

Bạn cần chọn biểu tượng Lựa chọn đường xu hướng trên bảng Định dạng đường xu hướng, chọn Trung bình động và điền vào khoảng thời gian mong muốn tại hộp Kỳ:

7- Tính trung bình động trong Excel bằng hàm average
7- Tính trung bình động trong Excel bằng hàm average
  1. Khi bạn đã đóng ngăn Trendline, đường trung bình động sẽ hiện diện rõ ràng trên biểu đồ:
    8- Tính trung bình động trong Excel bằng hàm average
    8- Tính trung bình động trong Excel bằng hàm average

Để điều chỉnh biểu đồ theo ý muốn, hãy truy cập vào tabFill & Line hoặc Effects có trong ngăn Format Trendline để khám phá thêm các lựa chọn như kiểu đường, màu sắc, độ dày và các thông số khác.

9- Tính trung bình động trong Excel bằng hàm average
9- Tính trung bình động trong Excel bằng hàm average

Nếu bạn cần phân tích dữ liệu một cách sâu sắc hơn, có thể xem xét thêm vào một số đường trung bình động với những khoảng thời gian biến đổi, để quan sát sự thay đổi của xu hướng theo thời gian. Hình dưới đây thể hiện sự so sánh giữa đường trung bình động của 2 tháng (màu xanh lục) và 3 tháng (màu đỏ gạch).

10- Tính trung bình động trong Excel bằng hàm average
10- Tính trung bình động trong Excel bằng hàm average

Đây là toàn bộ nội dung hướng dẫn về cách tính trung bình động trong Excel sử dụng hàm Average. Cảm ơn bạn đã theo dõi, và hẹn gặp lại vào tuần sau với nhiều nội dung thú vị khác từ Siêu Marketing.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop