Emma có một danh sách các số trong một trang tính (giả sử trong cột A) được thêm vào hàng tuần. Cô ấy cần tính giá trị trung bình của 12 số cuối cùng trong cột. Cô ấy tự hỏi làm thế nào cô ấy có thể làm điều này và có giá trị trung bình luôn phản ánh 12 số cuối cùng, ngay cả khi cô ấy tiếp tục cộng các số mỗi tuần.

Giả sử rằng không có khoảng trống nào trong phạm vi số của bạn, bạn có thể tính giá trị trung bình của 12 số cuối cùng với công thức sau:

=AVERAGE(OFFSET(A1,COUNTA(A:A)-12,0,12,1))

Tất nhiên, công thức này phải được đặt trong một số ô không có trong cột A. Nó sử dụng hàm COUNTA để tìm ra bao nhiêu ô chứa nội dung nào đó trong cột A. Nếu có 100 ô được sử dụng trong cột A, điều này có nghĩa là bạn kết thúc với một công thức được đánh giá theo cách này:

=AVERAGE(OFFSET(A1,100-12,0,12,1))

Tất nhiên, 100 trừ 12 là 88 và con số này được sử dụng làm hiệu số từ ô bắt đầu (A2) để nói rằng phạm vi được tính trung bình phải bắt đầu từ A89 và kéo dài xuống 12 ô. Điều đó có nghĩa là mức trung bình cuối cùng là cho phạm vi A89: A100. Khi có nhiều số hơn được thêm vào cuối cột A, công thức luôn phản ánh 12 số cuối cùng.

Công thức sẽ trả về lỗi nếu cột A có ít hơn 12 hàng giá trị dữ liệu trong đó. Để phù hợp với khả năng đó, bạn có thể muốn thay đổi công thức một chút:

=AVERAGE(OFFSET(A1,COUNTA(A:A)-MIN(COUNTA(A:A),12),0,MIN(COUNTA(A:A),12),1))

Thay vì sử dụng giá trị cố định và nhanh của 12 hàng, hàm MIN (ở hai vị trí) trả về số hàng tối thiểu hoặc số hàng thực tế là 12. Vì vậy, nếu trang tính của bạn chỉ có số trong các ô A1: A5, Hàm MIN sẽ đảm bảo rằng công thức chỉ tính trung bình 5 giá trị đó.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10278) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: