Trong bài đăng này của Học Excel Online, bạn sẽ học cách tính trung bình động trong Excel cưc nhanh và đơn giản, với việc sử dụng hàm Average . Những trường hợp nào cần sử dụng để tính trung bình động trong N ngày, tuần, tháng hoặc năm qua và cách thêm xu hướng trung bình động vào đồ thị Excel.  

Đường trung bình là gì?  

Trong một vài bài viết gần đây, chúng ta đã xem xét kỹ cách tính trung bình trong Excel. Nếu bạn đang theo dõi blog của chúng ta, bạn đã biết cách tính giá trị trung bình bình thường và những hàm nào cần sử dụng để tìm đường trung bình có trọng số. Trong hướng dẫn hôm nay, chúng ta sẽ thảo luận về hai kỹ thuật cơ bản để tính toán đường trung bình trong Excel.

Nói chung, đường trung bình (còn được gọi là rolling average, running average hoặc moving mean) có thể được định nghĩa là một loạt các giá trị trung bình cho các tập hợp con khác nhau của cùng một tập dữ liệu. 

Nó thường được sử dụng trong thống kê, dự báo thời tiết và kinh tế được điều chỉnh theo mùa để hiểu các xu hướng tiềm ẩn. Trong giao dịch chứng khoán, đường trung bình động là một chỉ số thể hiện giá trị trung bình của một chứng khoán trong một khoảng thời gian nhất định. Trong kinh doanh, một thực tế phổ biến là tính toán đường trung bình của doanh số bán hàng trong 3 tháng gần nhất để xác định xu hướng gần đây. 

Ví dụ, trung bình động của nhiệt độ ba tháng có thể được tính bằng cách lấy trung bình của nhiệt độ từ tháng 1 đến tháng 3, sau đó là trung bình của nhiệt độ từ tháng 2 đến tháng 4, sau đó của tháng 3 đến tháng 5, v.v.  

Tồn tại các loại đường trung bình khác nhau như đơn giản (còn được gọi là số học), hàm mũ, biến, tam giác và trọng số. Trong hướng dẫn này, chúng ta sẽ xem xét đường trung bình động đơn giản được sử dụng phổ biến nhất.

Tính toán đường trung bình động đơn giản trong Excel 

Nhìn chung, có hai cách để tính đường trung bình động đơn giản trong Excel – bằng cách sử dụng các công thức và tùy chọn đường xu hướng. Các ví dụ sau đây chứng minh cả hai kỹ thuật.  Mình cũng đã dành 2 bài viết đề cập sâu hơn về cách tính trung bình động trong Excel:

Hướng dẫn cách tính trung bình động trong Excel phần 1
Hướng dẫn cách tính trung bình động trong Excel phần 2

Ví dụ 1: Tính toán đường trung bình trong một khoảng thời gian nhất định 

Một đường trung bình động đơn giản có thể được tính toán nhanh chóng với hàm AVERAGE. Giả sử bạn có danh sách nhiệt độ trung bình hàng tháng trong cột B và bạn muốn tìm trung bình động trong 3 tháng (như trong hình trên). 

Viết công thức AVERAGE thông thường cho 3 giá trị đầu tiên và nhập nó vào hàng tương ứng với giá trị thứ 3 từ trên cùng (ô C4 trong ví dụ này), sau đó sao chép công thức xuống các ô khác trong cột:

=AVERAGE(B2:B4)

Bạn có thể sửa cột bằng tham chiếu tuyệt đối (như $B2) nếu muốn, nhưng hãy đảm bảo sử dụng tham chiếu hàng tương đối (không có dấu $) để công thức điều chỉnh đúng cho các ô khác. Hãy nhớ rằng trung bình được tính bằng cách cộng các giá trị rồi chia tổng cho số giá trị được tính trung bình, bạn có thể xác minh kết quả bằng cách sử dụng công thức 

=SUM(B2:B4)/3

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 

Giả sử bạn có một danh sách dữ liệu, ví dụ: số liệu bán hàng hoặc báo giá cổ phiếu và bạn muốn biết mức trung bình của 3 tháng qua tại bất kỳ thời điểm nào. Đối với điều này, bạn cần một công thức sẽ tính lại giá trị trung bình ngay sau khi bạn nhập giá trị cho tháng tiếp theo. Hàm Excel nào có khả năng làm điều này? AVERAGE cũ kết hợp với OFFSET và COUNT.  

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

Trong đó N là số ngày/ tuần/ tháng/ năm qua để tính vào giá trị trung bình. Bạn không chắc chắn về cách sử dụng công thức trung bình động này trong trang tính Excel của mình? Ví dụ sau sẽ làm cho mọi thứ rõ ràng hơn. 

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

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

Và bây giờ, chúng ta hãy cố gắng hiểu công thức trung bình động trong Excel này thực sự đang làm gì.  

• Hàm COUNT hàm COUNT (B2: B100) đếm có bao nhiêu giá trị đã được nhập vào cột B. Chúng ta bắt đầu đếm trong B2 vì hàng 1 là tiêu đề cột.  
• Hàm OFFSET lấy ô B2 (tham số thứ nhất) làm điểm bắt đầu và bù trừ số lượng (giá trị do hàm COUNT trả về) bằng cách di chuyển 3 hàng lên trên (-3 trong tham số thứ 2). Kết quả là nó trả về tổng giá trị trong một phạm vi bao gồm 3 hàng (3 trong tham số thứ 4) và 1 cột (1 trong đối số cuối cùng), là 3 tháng gần nhất mà chúng ta muốn.  
• Cuối cùng, tổng trả về được chuyển đến hàm AVERAGE để tính toán đường trung bình.  

Mẹo: Nếu bạn đang làm việc với các trang tính có thể cập nhật liên tục trong đó các hàng mới có khả năng được thêm vào trong tương lai, hãy đảm bảo cung cấp đủ số hàng cho hàm COUNT để chứa các mục nhập mới tiềm năng. Sẽ không thành vấn đề nếu bạn bao gồm nhiều hàng hơn thực tế cần thiết miễn là bạn có ô đầu tiên đúng, hàm COUNT sẽ loại bỏ tất cả các hàng trống. 

Như bạn có thể nhận thấy, bảng trong ví dụ này chỉ chứa dữ liệu trong 12 tháng, nhưng phạm vi B2: B100 được cung cấp cho COUNT, chỉ để ở phía lưu 🙂

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

Nếu bạn muốn tính giá trị trung bình động cho N ngày, tháng, năm, v.v. gần nhất trong cùng một hàng, bạn có thể điều chỉnh công thức Offset theo cách này: 

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

Giả sử B2 là số đầu tiên trong hàng và bạn muốn bao gồm 3 số cuối cùng trong giá trị trung bình, công thức sẽ như sau  shape: 

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

Tạo biểu đồ trung bình động trong Excel 

Nếu bạn đã tạo biểu đồ cho dữ liệu của mình, việc thêm đường xu hướng trung bình động cho biểu đồ đó chỉ mất vài giây. Đối với điều này, chúng ta sẽ sử dụng tính năng Excel Trendline của Excel và các bước chi tiết theo sau. 

Đối với ví dụ này, tôi đã tạo biểu đồ cột 2-D (Insert tab > Charts group) cho dữ liệu bán hàng của chúng ta:

Và bây giờ, chúng ta muốn “hình dung” đường trung bình động trong 3 tháng.  

1. Trong Excel 2013, hãy chọn biểu đồ, chuyển đến tab Design tab > Chart Layouts Group và nhấp vào  Add Chart Element > Trendline > More Trendline Options…

   

Trong Excel 2010 và Excel 2007, đi tới Layout > Trendline > More Trendline Options.

Mẹo: Nếu bạn không cần chỉ định các chi tiết như khoảng thời gian hoặc tên của đường trung bình, bạn có thể nhấp vào Design > Add Chart Element > Trendline > Moving Average để có kết quả ngay lập tức.  

1. Ngăn Format Trendline sẽ mở ở bên phải trang tính của bạn trong Excel 2013 và hộp thoại tương ứng sẽ bật lên trong Excel 2010 và 2007. 

Trên ngăn Format Trendline, bạn bấm vào biểu tượng Trendline Options, chọn Moving Average và chỉ định khoảng thời gian trung bình động trong hộp Period:

1. Đóng ngăn Trendline và bạn sẽ thấy đường xu hướng trung bình động được thêm vào biểu đồ của bạn:

   

Để tinh chỉnh biểu đồ của mình, bạn có thể chuyển sang tab Fill & Line or Effects trên ngăn Format Trendline và làm việc với các tùy chọn khác nhau như loại đường, màu sắc, chiều rộng, v.v.

Để phân tích dữ liệu, bạn có thể muốn thêm một vài đường xu hướng trung bình động với các khoảng thời gian khác nhau để xem xu hướng phát triển như thế nào. Hình sau đây cho thấy các đường xu hướng trung bình động 2 tháng (xanh lục) và 3 tháng (đỏ gạch):

Đó là tất cả về tính toán trung bình động trong Excel bằng hàm Average. Tôi cảm ơn bạn đã đọc và mong được gặp bạn vào tuần sau với những bài viết lý thú từ Học Excel Online