Cách tính trung bình 5 giá trị cuối cùng của cột được nhập số mới liên tục

Khi làm việc với Excel, chúng ta có thể tính nhanh được giá trị trung bình của 5 ô cuối cùng của cột bằng hàm Average. Nhưng theo thời gian cập nhật nếu ta muốn tính số mới sau khi bảng dữ liệu được cập nhật, và kết quả trung bình sẽ được tự động thay đổi theo bảng dữ liệu mới luôn được cập nhật. Hay nói cách khác, làm sao để luôn có kết quả trung bình của 5 số cuối cùng trong cột của bảng dữ liệu, kể cả khi ta cập nhật dữ liệu liên tục. Trong bài này Gitiho sẽ hướng dẫn các bạn công thức nhanh nhất để thực hiện:

Công thức tính trung bình 5 giá trị cuối cùng của cột kể cả khi dữ liệu được cập nhật liên tục

Công thức dưới đây sẽ giúp bạn giải quyết vấn đề này, hãy làm theo các bước sau:

Nhập công thức sau đây vào ô trống:

=IF(COUNT(A:A),AVERAGE(INDEX(A:A,LARGE(IF(ISNUMBER(A1:A1000),ROW(A1:A1000)),MIN(5,COUNT(A1:A1000)))):A1000),"no data")

Trong đó:

A:A là cột chứa các giá trị mà ta cần.

Số 5 thể hiện giá trị n cuối cùng.

Sau đó nhấn tổ hợp phím Ctrl + Shift + Enter để lấy kết quả. Giống như hình minh họa dưới đây:

Và bây giờ khi ta nhập vào các số mới đằng sau các dữ liệu cũ thì giá trị trung bình cũng được thay đổi theo. Bạn có thể thấy điều này trong hình minh họa dưới đây:

Chú ý:

Nếu trong cột của chúng ta có chứa giá trị 0, bạn muốn loại trừ giá trị 0 khỏi 5 số cuối cùng thì công thức trên sẽ không dùng được. Ở đây chúng ta có một công thức khác để bạn tính trung bình 5 giá trị cuối cùng trừ số 0, hãy nhập công thức sau:

=AVERAGE(SUBTOTAL(9,OFFSET(A1:A1000,LARGE(IF(A1:A1000>0,ROW(A1:A1000)-MIN(ROW(A1:A1000))),ROW(INDIRECT("1:5"))),0,1)))

Sau đó nhấn tổ hợp phím Ctrl + Shift + Enter để lấy kết quả bạn cần, giống như hình dưới đây:

Có thể bạn sẽ cần:

- Cách tính trung bình các ô chỉ có giá trị (loại bỏ 0 hoặc ô trống) trong Excel

- Cách tính trung bình các ô hoặc danh sách đã lọc trong Excel

Để biết tất tần tật về Excel, sở hữu ngay seris Excel tuyệt đỉnh:

1. MOSE - Chinh phục MOS EXCEL 2013 cùng Nimbus

2. VBAG01 - Tuyệt đỉnh VBA - Viết code trong tầm tay

3. EXG01 - Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

4. EXG02 - Thủ thuật Excel cập nhật hàng tuần cho dân văn phòng

5. EXG04 - Kỹ năng báo cáo - Tư duy tổ chức dữ liệu trên Excel

6. Ebook Tuyệt đỉnh Excel - Khai phá 10 kỹ thuật ứng dụng Excel mà đại học không dạy bạn