Công thức chung
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Tóm lược
Để tính trung bình của 5 điểm dữ liệu gần đây nhất, bạn có thể sử dụng hàm AVERAGE cùng với các hàm COUNT và OFFSET. Bạn có thể sử dụng phương pháp này để tính trung bình N điểm dữ liệu gần đây nhất: 3 ngày qua, 6 lần đo gần đây nhất, v.v. Trong ví dụ minh họa, công thức trong F6 là:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Lưu ý: giá trị âm cho chiều cao sẽ không hoạt động trong Google trang tính. Xem bên dưới để biết thêm thông tin.
Giải trình
Hàm OFFSET có thể được sử dụng để xây dựng phạm vi hình chữ nhật động dựa trên tham chiếu bắt đầu và các hàng, cột, chiều cao và chiều rộng đã cho. Các đối số hàng và cột có chức năng giống như "hiệu số" từ tham chiếu bắt đầu. Đối số chiều cao và chiều rộng (cả hai tùy chọn), xác định số hàng và cột mà phạm vi cuối cùng bao gồm. Đối với ví dụ này, OFFSET được cấu hình như sau:
- tham chiếu = C3
- hàng = COUNT (A: A)
- cols = 0
- chiều cao = -5
- width = (không được cung cấp)
Tham chiếu bắt đầu được cung cấp là C3 ô phía trên dữ liệu thực tế. Vì chúng tôi muốn OFFSET trả về một phạm vi bắt nguồn từ mục nhập cuối cùng trong cột C, chúng tôi sử dụng hàm COUNT để đếm tất cả các giá trị trong cột C để có được độ lệch hàng cần thiết. COUNT chỉ đếm các giá trị số, vì vậy tiêu đề trong hàng 3 sẽ tự động bị bỏ qua.
Với 8 giá trị số trong cột C, công thức OFFSET giải quyết thành:
OFFSET(C3,8,0,-5)
Với các giá trị này, OFFSET bắt đầu ở C3, bù lại 8 hàng thành C11, sau đó sử dụng -5 để mở rộng phạm vi hình chữ nhật lên "ngược" 5 hàng để tạo phạm vi C7: C11.
Cuối cùng, hàm OFFSET trả về phạm vi C7: C11 cho hàm AVERAGE, hàm này tính giá trị trung bình của các giá trị trong phạm vi đó.
Excel so với Trang tính
Một điều kỳ lạ với công thức này là nó sẽ không hoạt động với Google Trang tính, vì hàm OFFSET trong Trang tính sẽ không cho phép giá trị âm cho các đối số chiều cao hoặc chiều rộng. Tài liệu Excel cũng cho biết chiều cao hoặc chiều rộng không được âm nhưng có vẻ như các giá trị âm đã hoạt động tốt trong Excel kể từ những năm 1990.
Để tránh các giá trị chiều cao hoặc chiều rộng âm, bạn có thể sử dụng công thức như sau:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Chú ý C4 là tham chiếu bắt đầu trong trường hợp này. Hình thức chung là:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
trong đó A1 là ô đầu tiên trong các số bạn muốn tính trung bình.
