Công thức Excel: Tính trung bình 3 giá trị số cuối cùng -

Công thức chung

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))

Tóm lược

Để tính trung bình 3 giá trị số cuối cùng trong một dải ô, bạn có thể sử dụng công thức mảng dựa trên sự kết hợp của các hàm để nạp n giá trị số cuối cùng vào hàm AVERAGE. Trong ví dụ được hiển thị, công thức trong D6 là:

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))

trong đó "dữ liệu" là phạm vi được đặt tên B5: B13.

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.

Giải trình

Hàm AVERAGE sẽ tính toán trung bình của các số được trình bày trong một mảng, vì vậy hầu như tất cả công việc trong công thức này là tạo ra một mảng gồm 3 giá trị số cuối cùng trong một phạm vi. Làm việc từ trong ra ngoài, hàm IF được sử dụng để "lọc" các giá trị số:

IF(ISNUMBER(data),ROW(data))

Hàm ISNUMBER trả về TRUE cho các giá trị số và FALSE cho các giá trị khác (bao gồm cả khoảng trống) và hàm ROW trả về số hàng, do đó, kết quả của thao tác này là một dãy số tương ứng với các mục nhập số:

(5;6;FALSE;8;9;10;FALSE;12;13)

Mảng này đi vào hàm LARGE với hằng số mảng (1,2,3) cho k. LARGE tự động bỏ qua các giá trị FALSE và trả về một mảng có 3 số lớn nhất, tương ứng với 3 hàng cuối cùng có giá trị số:

(13,12,10)

Mảng này đi vào hàm LOOKUP dưới dạng giá trị tra cứu. Mảng tra cứu được cung cấp bởi hàm ROW và mảng kết quả là dải ô có tên "dữ liệu":

LOOKUP((13,12,10), ROW(data), data))

LOOKUP sau đó trả về một mảng chứa các giá trị tương ứng trong "dữ liệu", được đưa vào AVERAGE:

=AVERAGE((100,92,90))

Xử lý ít giá trị hơn

Nếu số lượng giá trị số giảm xuống dưới 3, công thức này sẽ trả về lỗi #NUM vì LARGE sẽ không thể trả về 3 giá trị theo yêu cầu. Một cách để xử lý điều này là thay thế hằng số mảng được mã hóa cứng (1,2,3) bằng một mảng động được tạo bằng cách sử dụng INDIRECT như sau: