Công thức chung
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Tóm lược
Để FILTER và trích xuất n giá trị đầu tiên hoặc cuối cùng (tức là 3 giá trị đầu tiên, 5 giá trị đầu tiên, v.v.), bạn có thể sử dụng hàm FILTER cùng với INDEX và SEQUENCE. Trong ví dụ được hiển thị, công thức trong D5 là:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
trong đó dữ liệu là dải ô được đặt tên B5: B15.
Giải trình
Làm việc từ trong ra ngoài, chúng tôi sử dụng hàm SEQUENCE để tạo giá trị số hàng cho INDEX như sau:
SEQUENCE(3,1,1,1)
Chúng tôi đang yêu cầu SEQUENCE cho một mảng 3 hàng x 1 cột, bắt đầu từ 1, với giá trị bước là 1. Kết quả là một mảng như sau:
(1;2;3)
được trả về trực tiếp cho hàm INDEX dưới dạng đối số row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Để xây dựng mảng cho INDEX, chúng tôi sử dụng hàm FILTER để truy xuất danh sách các mục nhập không trống từ dữ liệu phạm vi đã đặt tên (B5: B15) như sau:
FILTER(data,data"")
Đối số mảng là dữ liệu và đối số bao gồm là dữ liệu biểu thức "". Điều này có thể được dịch theo nghĩa đen là "trả về giá trị từ dữ liệu phạm vi được đặt tên nơi các giá trị trong dữ liệu không trống". Kết quả là một mảng có 9 giá trị như sau:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Giá trị thông báo liên quan đến hai ô trống đã bị loại bỏ. Mảng này được trả về hàm INDEX dưới dạng đối số mảng của nó.
Cuối cùng, INDEX trả về các giá trị thứ 1, thứ 2 và thứ 3 từ mảng được FILTER trả về:
("Atlanta";"Chicago";"Dallas")
N giá trị cuối cùng
Để nhận n giá trị cuối cùng với FILTER, bạn sử dụng cùng một cấu trúc công thức, với các giá trị đầu vào cho SEQUENCE được sửa đổi để tạo mảng "n cuối cùng" gồm các số hàng. Ví dụ: để nhận 3 giá trị không trống cuối cùng trong ví dụ được hiển thị, bạn có thể sử dụng công thức như sau:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Thủ thuật chính ở đây là đếm các mục không trống trong dữ liệu phạm vi được đặt tên như sau:
SUM(--(data""))
Chúng tôi sử dụng một âm kép để buộc các giá trị TRUE FALSE thành 1s và 0s, sau đó sử dụng hàm SUM để tính số lượng. Kết quả được trả về dưới dạng đối số bắt đầu bên trong SEQUENCE. Chúng tôi cung cấp -1 cho bước lùi lại từ đầu.
Chúng tôi cũng bao hàm SORT xung quanh SEQUENCE để mảng được trả về là (7; 8; 9) chứ không phải (9; 8; 7). Điều này đảm bảo rằng các giá trị được trả về theo thứ tự xuất hiện trong dữ liệu nguồn.
