Công thức chung
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Tóm lược
Để tính trung bình của 5 giá trị dữ liệu cuối cùng trong một dải cột, bạn có thể sử dụng hàm AVERAGE cùng với các hàm COUNT và OFFSET. Trong ví dụ được hiển thị, công thức trong F6 là:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Giải trình
Hàm OFFSET có thể được sử dụng để tạo phạm vi động bằng cách sử dụng ô 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 đều là tùy chọn, xác định số hàng và cột mà phạm vi cuối cùng bao gồm. Chúng tôi muốn OFFSET trả về một phạm vi bắt đầu ở mục nhập cuối cùng và mở rộng "ngược" nên chúng tôi cung cấp các đối số như sau:
tham chiếu - tham chiếu bắt đầu là D5 - ô ngay bên phải công thức và ô đầu tiên trong phạm vi giá trị mà chúng tôi đang làm việc.
Hàng - chúng tôi sử dụng 0 cho đối số hàng, vì chúng tôi muốn giữ nguyên trong cùng một hàng.
cột - đối với đối số cột, chúng tôi sử dụng hàm COUNT để đếm tất cả các giá trị trong phạm vi, sau đó trừ đi 5. Điều này sẽ dịch chuyển đầu của phạm vi 5 cột sang bên trái.
chiều cao - chúng tôi sử dụng 1 vì chúng tôi muốn phạm vi 1 hàng là kết quả cuối cùng.
width - chúng tôi sử dụng 5, vì chúng tôi muốn phạm vi cuối cùng có 5 cột.
Đối với công thức trong C5, OFFSET trả về phạm vi cuối cùng là F5: J5. Điều này đi vào hàm AVERAGE trả về giá trị trung bình của 5 giá trị trong phạm vi
Ít hơn 5 giá trị
Nếu có ít hơn 5 giá trị, công thức sẽ trả về lỗi tham chiếu vòng tròn, vì phạm vi sẽ mở rộng trở lại ô chứa công thức. Để tránh lỗi này, bạn có thể điều chỉnh công thức như sau:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Ở đây chúng tôi sử dụng hàm MIN để "bắt" các tình huống có ít hơn 5 giá trị và sử dụng số lượng thực tế khi có.
